База данных эксель конспект

Обновлено: 07.07.2024

Аннотация: Цель работы: научиться использовать электронные таблицы Excel для создания списков, сортировки данных в списке, фильтрации данных. Содержание работы: Создание базы данных (списка) в Excel. Сортировка данных в списке. Фильтрация данных в списке с использованием Афтофильтра. Фильтрация данных в списке с использованием Расширенного фильтра. Задание множественного критерия сравнения и вычисляемого критерия. Просмотр записей, поиск и фильтрация данных списка с помощью форм данных.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Понятие о списке (базе данных Excel)

Электронные таблицы Excel можно использовать для организации работы с небольшими реляционными базами данных. В этом случае электронную таблицу называют списком или базой данных Excel (рис. 5.1 рис. 5.1) и используют соответствующую терминологию:

строка списка – запись базы данных;
столбец списка – поле базы данных.

Название столбца может занимать только одну ячейку и при работе с таблицей как с базой данных называется именем поля. Все ячейки строки с именами полей образуют область имен полей, которая занимает только одну строку. Данные всегда располагаются, начиная со следующей строки после области имен полей. Весь блок ячеек с данными называют областью данных.

Список содержит фиксированное количество полей (столбцов), определяющих структуру записи базы данных (строки).
Верхняя строка списка содержит имена полей (названия столбцов).
Имя поля может состоять из нескольких слов любого алфавита. Обязательное требование – размещение в одной ячейке.

Список (база данных Excel) – электронная таблица, в которой строки (записи) имеют фиксированную структуру, а имена столбцов (полей) занимают одну строку.

Для размещения имени поля списка в одной ячейке (рис.5.1 рис. 5.1) необходимо:

выделить ячейку или всю строку, где будут располагаться имена полей;
ввести команду Главная и выбрать вкладку Выравнивание
на вкладке установить следующие параметры:
по горизонтали: по значению;
по вертикали: по верхнему краю или по центру;
отображение: установить флажок переносить по словам.

Над записями списка можно выполнять различные операции обработки, команды вызова которых сгруппированы в меню Данные.

Для того чтобы электронная таблица воспринималась системой как список, необходимо соблюдать описанные выше правила и перед выполнением операций обработки установить курсор внутри этой таблицы. В этом случае при вводе команды обработки из меню Данные весь список (имена полей и записи, см. рис.5.1) будет выделены темным цветом. Когда список сформирован неверно или нужно работать с частью области списка, область списка надо выделить вручную с помощью мыши.

Excel предоставляет возможности для работы с базами данных различных форматов, которые при открытии в среде Excel автоматически преобразуются в список. Такое преобразование называют импортом. Данные в электронную таблицу можно включить не только путем импорта из "чужой" базы данных, но и посредством запросов данных, адресованных тому или иному серверу баз данных. Такие запросы формируются специальной программой MS Query , вызываемой по команде Данные, Внешние данные. Создать запрос. Результат запроса возвращается в электронную таблицу в виде списка.

Сортировка данных в списке

Сортировка данных является базовой операцией любой таблицы и выполняется командой Данные, Сортировка с установкой необходимых параметров. Целью сортировки является упорядочивание данных. Сортировка осуществляется на том же листе.

Особенно важно осуществлять сортировку в списке, так как многие операции группировки данных, которые доступны из меню Данные, можно использовать только после проведения операции сортировки.

В среде Excel предусмотрены три уровня сортировки, которые определяются в диалоговом окне Сортировка диапазона (рис. 5.2 рис. 5.2, а) параметром Сортировать по.

Рис. 5.2. Диалоговые окна для операции сортировки: а – сортировка диапазона; б – параметры сортировки

Сначала осуществляется сортировка в столбце 1-го уровня, затем сортируются одинаковые записи 1-го столбца по столбцу 2-го уровня, затем сортируются одинаковые записи 2-го столбца по столбцу 3-го уровня.

В том же окне устанавливается порядок сортировки в столбцах – по возрастанию или убыванию. При сортировке по возрастанию упорядочение идет следующим образом – от меньшего к большему, по алфавиту или в хронологическом порядке дат, но при этом имеет место приоритет: числа, текст, логические значения, значения ошибок, пустые ячейки. Сортировка по убыванию использует обратный порядок (исключение – пустые ячейки, которые располагаются в конце списка),

При наличии заголовков столбцов (имен полей) их следует исключить из области, подлежащей сортировке, установкой флажка переключателя Идентифицировать поля по подписям.

Кнопка выводит диалоговое окно "Параметры сортировки", в котором задаются дополнительные установки сортировки (рис. 5.2 б рис. 5.2): с учетом регистра или без учета; по столбцам или по строкам; порядок сортировки – обычный или специальный, выбранный из предлагаемого списка. Этот список можно сформировать самостоятельно с помощью Сервис, Параметры, вкладка Списки.

Установить курсор в области списка
Выполнить команду Данные, Сортировка
Указать порядок и направление сортировки для каждого ключа сортировки
Нажать кнопку и выбрать параметры сортировки (порядок по первому ключу, учет регистра, направление сортировки – по строкам или по столбцам)

Выполнить команду Сервис, Параметры, вкладка Списки
Нажать кнопку
Сформировать элементы списка

Выполнить команду Сервис, Параметры, вкладка Списки
Выделить в окне Списки начало редактируемого списка
Перейти к элементам списка и отредактировать их (добавить, удалить, отредактировать)
Нажать кнопку

Выполнить команду Сервис, Параметры, вкладка Списки
Выделить в окне Списки начало редактируемого списка
Нажать кнопку

Фильтрация данных в списке – это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные > Фильтр.

Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командой Данные> Форма.

Автофильтрация

Команда Данные, Фильтр, Автофильтр для каждого столбца строит список значений, который используется для задания условий фильтрации (рис. 5.3 рис. 5.3). В каждом столбце появляется кнопка списка, нажав которую можно ознакомиться со списком возможных критериев выбора.

По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:

все – выбираются все записи без ограничений;
первые 10 – данный пункт позволяет во вновь появляющемся диалоговом окне "Наложение условия по списку" (рис. 5.4 рис. 5.4) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;
значения – будут выбраны только те записи, которые в данном столбце содержат указанное значение;
условие – выбираются записи по формируемому пользователем условию в диалоговом окне "Пользовательский фильтр" (рис. 5.5 рис. 5.5).

Условие для отбора записей по конкретным значениям в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ.

Каждая часть условия включает:

оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), =п* – отобрать все записи, которые содержат код предмета, начинающийся с буквы п;
>= п1 И п1 – отобрать все записи, которые не содержат кода предмета п1.

Можно задать условия отбора для нескольких столбцов независимо друг от друга, фильтрация записей выполняется по всем условиям одновременно. Все записи, не прошедшие через фильтр, будут скрыты. Отфильтрованные записи можно выделить и скопировать в другое место, удалить. Отмена результата фильтрации и возврат к исходному состоянию списка производятся повторным вводом команды Данные, Автофильтр.

Расширенный фильтр

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

критерий сравнения;
вычисляемый критерий.

Обычно критерий фильтрации формируется в нескольких столбцах, и тогда его называют множественным критерием.

Важной особенностью этого режима является необходимость формирования заранее, до выполнения самой команды фильтрации, специального блока (области) для задания сложных поисковых условий, называемых областью критерия (диапазоном условия).

Технология использования расширенного фильтра состоит из двух этапов:

этап 1 – формирование области критериев поиска;
этап 2 – фильтрация записей списка.

Этап 1. Формирование диапазона условий для расширенного фильтра. Область критериев поиска содержит строку имен столбцов и произвольное число строк для задания поисковых условий.

Рекомендуется скопировать первую строку с именами полей из области списка в область, где будет формироваться критерий отбора записей (на тот же или другой лист, в другую рабочую книгу). Далее ненужные имена столбцов из диапазона условий можно удалить.

Критерий сравнения формируется при соблюдении следующих требований:

состав столбцов области критериев определяется столбцами, по которым задаются условия фильтрации записей;
имена столбцов области критериев должны точно совпадать с именами столбцов исходного списка;
ниже имен столбцов располагаются критерии сравнения типа:
точного значения;
значения, формируемого с помощью операторов отношения;
шаблона значения, включающего символы * и (или) ?.

Правила формирования множественного критерия:

Если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И .
Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Пример1. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами:

1-й способ. Множественный критерий сравнения- все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка- оператор сравнения со значением константы.

2-й способ. Множественный критерий сравнения – все условия (точные значения полей) находятся в одной строке, столбец Оценка используется дважды, связка И.

3-й способ. Множественный критерий сравнения – условия (точные значения полей) записаны в двух строках, связка ИЛИ.

Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение ИСТИНА или ЛОЖЬ.

Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (числа, текст, дата, логическая константа), операторов отношения.

Внимание! Имя столбца, содержащего формулу вычисляемого критерия, должно отличаться от имени столбца в списке.

Пример2. Выбрать записи о сдаче экзаменов студентами группы 133 с оценкой ниже общего среднего балла или записи с оценкой 5:

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ – результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому:

Основные операции обработки записей списка: последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка.

Добавление кнопки "Форма" на панель быстрого доступа

Щелкните стрелку, расположенную рядом с панелью быстрого доступа, и выберите пункт Другие команды.

В поле Выбрать команды из выберите пункт Все команды.

В списке выберите кнопку Форма Кнопка "Форма" и нажмите кнопку Добавить.

При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей – названия столбцов списка.

Для просмотра записей используется полоса прокрутки либо кнопки или , выводится индикатор номера записи. При просмотре записей возможно их редактирование. Поля, не содержащие формул, доступны для редактирования, вычисляемые или защищенные поля не редактируются. Корректировку текущей записи с помощью кнопки можно отменить.

Для создания новой записи нажимается кнопка , выполняется заполнение пустых полей экранной формы; для перехода между полями формы используются курсор мыши, либо клавиша .

При повторном нажатии кнопки сформированная запись добавляется в конец списка. Для удаления текущей записи нажимается кнопка . Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.

С помощью экранной формы задаются критерии сравнения. Для этого нажимается кнопка , форма очищается для ввода условий поиска в полях формы с помощью кнопки , а название кнопки заменяется на название . После ввода критериев сравнения нажимаются кнопки или для просмотра отфильтрованных записей в нужном направлении. При просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка , для выхода из формы – кнопка .

Работа с таблицей как с базой данных Подготовила преподаватель Монгуш М.Л.

Цели занятия Познакомиться с базами данных в Excel Научиться создавать базу данных в программе Excel Научиться обрабатывать базу данных

План Основные понятия Правила ввода данных в базу данных в среде Excel Способы ввода данных в списки Работа со списками

Основные понятия База данных – это средство хранения, упорядочения и поиска информации . В базе данных ячейка – это поле . Ячейки одной строки представляют собой запись . Заголовки столбцов называются Именами полей . Базу данных в электронной таблице называют списком .

Правила ввода информации в базы данных в Excel Имена полей : вводить имена полей необходимо в первую строку. При этом не должно быть пустых строк между строкой заголовков и первой записью. имя поля (заголовок столбца) может состоять из нескольких слов, но обязательно размещенных в одной ячейке Записи : каждая запись вводится в отдельную строку. Между записями не должно быть незаполненных строк. Один тип : информация в ячейках одного столбца должна быть однотипна.

Способы ввода данных Списки можно вводить как в режиме таблицы , так и при помощи формы . Форма - это окно диалога, предназначенное для удобного ввода, удаления и поиска данных в списках. Для создания полей в форме Excel использует заголовки списка. Для отображения формы на экране необходимо выбрать Данные→Форма .

Ф ункции при работе со списком фильтрация , автофильтрация сортировка . В первом случае можно вывести строки, удовлетворяющие некоторому критерию. Сортировка – это упорядочение списка по значениям одного или нескольких полей . (По алфавиту, например).

Выполнение сортировки : выделить любую ячейку списка; выполнить команду Данные – Сортировка ; указать ключи и порядок сортировки в ДО; нажать ОК.

Фильтрация записей: выделить любую ячейку списка; выполнить команду Данные – Фильтр

Закончите предложения - я сегодня узнал … - я сегодня научился … - мне на уроке было интересно… - мне было сложно …

Урок-практикум "Создание базы данных в Excel и работа с ней". Разработка содержит конспект урока, раздаточный материал для учеников, лист Excel с заданием.

Практическая работа

Создание базы данных в Excel и работа с ней.

В электронной таблице Excel создать базу данных, внести в нее представленные ниже записи.

Заполнить пустые столбцы формулами, получить результат во всех ячейках.

Внести информацию о курсе доллара.

Примечание. Используйте абсолютную ссылку так, чтобы изменение курса доллара влекло за собой автоматический пересчет цены в рублях.

Год издания

Кол-во страниц

Область знаний

Цена, $

Выручка, руб.

Курс $

Олимпиады по информатике

Красноярское кн. изд-во

Найти подготовленный мною файл (С:\Книжная ярмарка \ Книги.х ls ).

Осуществить сортировку записей в базе данных; Выполнить команду меню Данные, Сортировка.

Указать, как следует выполнять сортировку: по возрастанию или по убыванию.

Для работы с базой данных в Excel имеется специальная форма, которая позволяет просматривать записи, добавлять новые, удалять записи, осуществлять поиск. Для работы с формой надо выполнить команду Данные, Форма. Остановимся на кнопке Критерии и выполним следующее задание:

Работа с кнопкой Критерии.

Сколько на ярмарке было представлено книг по математике?

Сколько было представлено книг стоимостью больше 100 рублей?

Сколько всего было представлено книг по информатике?

Работа с таблицей, использование автосуммирования.

Каково общее количество книг, представленных на ярмарке?

Сколько на ярмарке было продано книг и на какую сумму?

6. Подумайте, как наиболее наглядно представить информацию о том, книги какого автора пользовались наибольшим спросом?

Практическая работа

Создание базы данных в Excel и работа с ней.

знакомство с функциями ЭТ по созданию баз данных и работе с ней;

закрепление знаний по работе с формулами в ЭТ и соответствующих умений.

развивающие – развитие операционного мышления, понятийного мышления;

воспитательная – воспитание информационной культуры (поиск информации, обмен между приложениями Windows ).

Мы продолжаем работу с электронными таблицами Excel . Вам уже известны многие из вычислительных возможностей Excel . Но помимо них эта программа может использоваться и для решения других задач. Сегодня мы познакомимся с функциями Excel по созданию базы данных и работе с ней. Кроме того, мы закрепим приемы работы в Excel и использование абсолютной ссылки.

Изучать новый материал и закреплять ранее изученный мы будем, решая задачи, которые могут возникнуть в следующей реальной ситуации: предположим, нам предложили поучаствовать в подготовке книжной ярмарки и различных мероприятий, связанных с нею.

Первая задача, которая стоит перед нами, — необходимо представить всю информацию о книгах в электронном виде, т. е. создать базу данных.

Что это такое? Запишите в тетради: база данных — это совокупность информации об однотипных объектах, некое хранилище данных.

База данных состоит из записей. Запись — это информация об одном объекте.

В нашем случае объектами являются книги. Но существуют базы данных с информацией о железнодорожных билетах, ценных бумагах, сотрудниках некоторой организации. Школьный журнал — это тоже база данных.

Карточка-задание 1.

В электронной таблице Excel создать базу данных, внести в нее представленные ниже записи.

Заполнить пустые столбцы формулами, получить результат во всех ячейках.

Внести информацию о курсе доллара.

Год издания

Кол-во страниц

Область знаний

Цена, $

Выручка, руб.

Курс $

Олимпиады по информатике

Красноярское кн. изд-во

В таблице, которую вы переносили с карточки в файл Excel , — три записи, т. е. информация о трех книгах. Каждая запись состоит из полей. Поле — это одна из характеристик объекта. В таблице это названия столбцов.

Проанализируем поля нашей базы данных. В процессе работы ярмарки для ее организаторов необходима следующая информация - количество поставленных экземпляров каждого наименования, количество проданных экземпляров, цена каждой книги в долларах и рублях и, наконец, выручка от продажи книг. Обратите внимание на то, что есть столбцы, значения в ячейках которых появятся в результате расчета по формулам (используются относительная и абсолютная ссылки). Базы данных, которые можно представить в виде таблицы (столбцы и строки), называются реляционными. Именно такие удобно создавать в Excel .

Рассмотрим, какие операции можно осуществлять с информацией, хранящейся в базе данных. Записи можно сортировать, удалять, вводить новые. Кроме того, в базе данных можно осуществлять поиск по запросу.

Конечно, выполнять все эти действия с тремя записями, которые вы ввели, выполняя первое задание, по меньшей мере, неинтересно и ненаглядно. Поэтому то, что вы сейчас ввели, нужно будет добавить к уже имеющимся записям. На ваших компьютерах имеется файл Книги. xls , в котором подготовлена необходимая информация. Вам нужно будет выполнить следующее задание:

Найти подготовленный мною файл (С:\Книжная ярмарка \ Книги.х ls — путь для поиска файла записывается на доске) Прил.

Карточка-задание 2.

Осуществить сортировку записей в базе данных;

Выполнить команду меню Данные, Сортировка.

Указать, как следует выполнять сортировку: по возрастанию или по убыванию.

Учитель рассказывает о назначении кнопок, имеющихся в окне Форма, подробно останавливается на кнопке Критерии и предлагает учащимся выполнить следующее задание:

Карточка-задание 3.

Добавить в базу данных новые записи, используя форму, - внести информацию о книгах, имеющихся в кабинете информатики (3-5 записей).

Карточка-задание 4.

Работа с кнопкой Критерии.

Сколько на ярмарке было представлено книг по математике?

Сколько было представлено книг стоимостью больше 100 рублей?

Сколько всего было представлено книг по делопроизводству?

Работа с таблицей, использование автосуммирования.

Каково общее количество книг, представленных на ярмарке?

Сколько на ярмарке было продано книг и на какую сумму?

В дни работы ярмарки проводились и другие мероприятия: семинар библиотечных работников, презентация новых книг, выставка печатной продукции, встреча книголюбов и др.

После проведения какого-то большого мероприятия, особенно в крупных городах и регионах, подводятся итоги, собираются и обобщаются статистические данные, необходимые для лучшего проведения будущих мероприятий, для более эффективной рекламы и т. д. В частности, устроители нашей книжной ярмарки хотят иметь информацию о посетителях и участниках всех мероприятий. И наша следующая задача — создать соответствующую структуру базы данных.

Карточка-задание 5.

На новом листе ЭТ создать структуру базы данных, обеспечивающей выполнение следующих запросов (т. е. важно правильно задать поля базы):

Посмотреть список посетителей-студентов.

Посмотреть список посетителей с высшим образованием

Посмотреть список посетителей, пришедших по пригласительным билетам.

Посмотреть, в какие регионы благодаря этой ярмарке поступили книги.

Посмотреть, какие мероприятия пользовались большей популярностью.

Итак, вы создали структуру базы данных, но остается открытой задача: как эту информацию собрать для базы данных?

Ребята могут предложить различные варианты решения этой проблемы. Вместе с учениками нужно прийти к выводу, что можно подготовить анкету для участников.

Цель: Рассмотреть этапы создания, способы ввода и методы обработки баз данных в MS Excel.

План.

1. Определение базы данных в Excel.
2. Ввод данных в список.
3. Работа с записями.
4. Поиск записей.
5. Сортировка данных в списках (самостоятельная работа).
6. Создание пользовательского списка (самостоятельная работа).
7. Фильтрация.
8. Отбор данных с помощью операторов (самостоятельная работа).
9. Использование при фильтрации данных символов подстановки (самостоятельная работа).
10. Выполнение более сложного отбора.

Ход лекции.

1. Определение базы данных в Excel.
Одной из типичных задач, выполняемых с помощью электронных таблиц, является ведение списков — списков номеров телефонов, клиентов, торговых операций, материальных ценностей и т. д. Базы данных в Excel часто называют списком.
Конечно, списки Excel несравнимы по объему и возможностям с профессиональными системами управления базами данных. Но если объем информации невелик и стабилен (не имеет тенденции к бурному возрастанию), то наличие в Excel специальных функций и команд для управления такой базой упрощает работу. Microsoft Excel имеет самый богатый набор средств для работы со списками по сравнению с любой другой программой электронных таблиц, что позволяет легко создавать, вести и анализировать такого рода информацию.
Список — это ряд строк, в которых содержатся определенный набор данных (рис. 1).
Каждому такому множеству строк предшествует строка меток-идентификаторов. Преимущество списка заключается в возможности гибко им манипулировать: искать данные, удовлетворяющие определенным условиям, отбрасывая при этом остальные данные, ненужные в данный момент. Так, в списке персонала можно найти сотрудника по фамилии Василенко или всех, кто зарабатывает больше 5 тыс. в год. Можно также отсортировать список множеством способов — например, расположив его элементы в алфавитном порядке.

Рисунок 1 – Список данных в MS Excel.

Максимальный размер списка, создаваемого в Excel, ограничен размером одного рабочего листа. Он может содержать не более 256 полей и не более 65535 записей (одна строк предназначена для имен полей). Список такого размера требует огромного количества памяти и, иногда, его даже невозможно отобразить.
Существует несколько правил для создания списка:

Рабочий лист должен иметь свое уникальное имя.
Каждый столбец должен содержать информацию одного типа. В списке сотрудников, например, один столбец отводится для фамилии, второй для имени, третий для отчества, четвертый для даты приема на работу и т. д.
Одна или две верхние строки списка должны содержать заголовки, каждый из которых описывает содержимое расположенного ниже столбца.
Информация в списках должна иметь постоянную структуру.
Нельзя включать в список пустые строки и столбцы.
Лучше всего, если для списка отводится отдельный лист. Если это невозможно, то список должен быть отделен от других данных рабочего листа по крайней мере одной пустой строкой и одним пустым столбцом, чтобы программа могла автоматически определить границы списка.
Нельзя размещать данные слева или справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка.
Не следует вводить лишние пробелы или какие-либо знаки перед данными в ячейке.
Первую строку с именами полей желательно закрепить, выполнив команду Окно – Закрепить область.

Для работы с базами данных в Excel используется пункт меню Данные.

2. Ввод данных в список.
Для непосредственного ввода информации в список можно поступать стандартно: выделить ячейку, ввести данные и нажать клавишу Enter.

Для ввода данных можно воспользоваться формой. Форма данных похожа на карточку учета: для каждой записи базы данных выводится отдельная карточка. Для этого необходимо выделить только одну любую ячейку в списке, в противном случае Excel не сможет правильно представить в форме заголовки столбцов, и выполнить команду Данные – Форма.
Рисунок 2 – Окно Формы.

Tab
Shift+Tab
Enter
Esc
Ctrl+PgUp
Ctrl+PgDn
PgUp или кнопка Предыдущая
PgDn или кнопка Следующая
Полоса прокрутки

Перемещение к следующему полю
Перемещение к предыдущему полю
Конец ввода очередной записи
Выход из диалогового окна Форма
Перемещение к первой записи
Перемещение за последнюю запись
Перемещение к предыдущей записи
Перемещение к следующей записи
Перемещение от записи к записи

На изменения, которые можно внести в запись в диалоговом окне Форма, накладывается ряд ограничений. Новые записи можно добавлять только в конец списка. Кроме того, можно изменять значения только тех ячеек, которые содержат данные. Значения ячеек, содержащих формулы с названия полей выводятся как текст на сером фоне диалогового окна. Их изменить нельзя.
При добавлении новых записей можно не задумываться о порядке их размещения. Записи всегда можно отсортировать, чтобы разместить их в нужном порядке.
После добавления новой строки в список с помощью команды Форма Excel расширяет список вниз без воздействия на ячейки, находящиеся вне списка. Если при расширении списка его новые данные будут записываться на место существующих данных рабочего листа, Excel предупредит, что список нельзя расширить.
Если при работе используется Microsoft Access, то можно создать формы в Access для ввода данных в список Excel. По сравнению со стандартными формами Excel формы Access предоставляют некоторые дополнительные возможности. Например, при работе с формами Access можно задавать условия, используемые для проверки вводимых данных.

3. Работа с записями.
Записи в список добавляются и редактируются путем ввода данных непосредственно в ячейки, а удаляются - путем их выделения и выполнения команды Правка - Удалить. Однако эти же действия можно сделать и с помощью Формы.
Для того, чтобы отредактировать, удалить одну запись с помощью Формы, ее следует вначале найти, воспользовавшись соответствующими кнопками. Если необходимо удалить много записей, то Форму лучше не использовать, а выполнить автофильтрацию.
Для просмотра записи используются кнопки Следующая, Предыдущая или Полоса прокрутки.
Для удаления записи ее предварительно необходимо найти, затем нажать кнопку Удалить. Excel перед удалением записи еще раз запрашивает разрешение.
Для отмены изменений используется кнопка Восстановить.
Для завершения работы с Формой нажать кнопу Завершить или Esc.

Выполнить команду Данные - Форма.
Нажать кнопку Критерии.
Ввести условие поиска в соответствующие поля. Необходимо заполнить те поля, по которым будет осуществляться поиск.
Нажать Enter для того, чтобы вернуться к исходной Форме.

Если же надо организовать поиск на основе нескольких критериев, то в этом случае надо использовать логическую операцию И. Операция логическое И в Форме реализована по умолчанию: просто нужно несколько полей заполнить своими условиями. По смыслу эти условия должны выполняться одновременно, т.е. они должны быть связаны логическим И.
К сожалению, критерий с логическим ИЛИ в Форме указывать невозможно. Однако по таким критериям можно организовывать поиск с помощью команды Автофильтр.

Например, пусть необходимо найти всех студентов, у которых оценки по Математике и Физике больше 6 баллов. Критерий поиска приведен на рис. 4.
Рисунок 4 – Окно задания нескольких критериев.

5. Сортировка данных в списках (самостоятельная работа).

6. Создание пользовательского списка (самостоятельная работа).

7. Фильтрация.
Чаще всего нужно видеть все данные в таблице. Однако иногда желательно, чтобы на экране отображалась только их часть. Такая необходимость возникает тем чаше, чем больше таблица. Excel позволяет временно "отфильтровать" данные, отобрав для визуализации только те, которые удовлетворяют определенным условиям.
Для отбора данных на основе более сложных критериев, использование которых не предусмотрено при работе с Формой (и для того, чтобы проводить расчеты только с определенными данными), в Excel включена команда Автофильтр. С помощью этой команды определяются критерии, по которым будут отображаться только соответствующие этому критерию записи. Остальные записи Excel скрывает.
Для выполнения фильтрации в списке необходимо выполнить следующие действия:

Выделите в списке любую ячейку.
Выполнить команду Данные – Фильтр – Автофильтр.

При автофильтрации не происходит удаления данных из таблицы. Они лишь становятся временно невидимыми. Если потребуется снова сделать видимыми все записи, нужно выбрать из списка, с помощью которого осуществлялась фильтрация данных, пункт (Все).
Чтобы вывести на экран первые 10 значений определенного поля — или первые 8, или первые 2, и т.п. (можно выбрать просмотр от 1 до 500 элементов) — нужно выбрать из соответствующего списка пункт Первые 10. Это может понадобиться, например, чтобы найти 5 студентов, получивших самые высокие оценки. При этом появляется диалоговое окно Наложение условия по списку, показанное на рисунке 6.

Рисунок 6 – Окно наложения условия.

В левом окне можно выбрать необходимое количество значений. Можно также просмотреть не начало списка, а конец, выбрав в среднем списке не наибольших, а наименьших, а также не абсолютное количество элементов списка, а долю в процентах (выбирается в правом списке).
После фильтрации базы данных можно еще более сузить список, отфильтровав его по другому столбцу.
После того, как база данных отфильтрована, оставшиеся записи можно просматривать, редактировать, копировать или печатать, как и целые таблицы Excel.
Чтобы отменить действие автофильтра — при этом станут видимыми все записи и исчезнут элементы управления в виде кнопок с изображением указывающих вниз стрелок рядом с именами полей — необходимо еще раз выбрать из меню Данные команду Фильтр - Автофильтр.
8. Отбор данных с помощью операторов (самостоятельная работа).

9. Использование при фильтрации данных символов подстановки (самостоятельная работа).

Рисунок 7 – Критерии для сложного отбора

Для быстрого ввода уже существующих значений и критериев в диалоговом окне Пользовательский автофильтри можно выбрать их из раскрывающихся списков.
При вводе в диалоговом окне Пользовательский автофильтр двух критериев порядок их следования не имеет значения.

При задании критериев для примера был выбран режим И (рис. 8). Это означает, что Excel будет искать записи, удовлетворяющие обоим критериям одновременно. Если запись будет удовлетворять только одному критерию, то программа уберет ее с экрана.

Рисунок 8 – Окно выбора режима фильтрации.

Если нужно отобрать записи, удовлетворяющие хотя бы одному из указанных критериев, то в этом случае выбирается режим ИЛИ. Например, можно выбрать студентов, обучающихся на 11 баллов по Зарубежной или Украинской литературам.

Рисунок 9 – Результаты отбора.

Контрольные вопросы:

Что в MS Excel понимается под базой данных? (Определение БД - это база данных. )
Какие правила необходимо соблюдать при создании баз данных в MS Excel?
Как можно вводить новые данные в списки?
Можно ли изменять рассчитываемые значения в списке с помощью формы?
Какие операции можно выполнять над базами данных с помощью форм?
Какими способами можно отобрать данные в списке с помощью автофильтра?
Какие логические операции используются при фильтрации данных и в чем между ними разница?

Домашнее задание:
Дополнительный материал для самостоятельного изучения по следующим вопросам: сортировка данных в списках; создание пользовательского списка; отбор данных с помощью операторов; использование при фильтрации данных символы подстановки.
Записать параметры фильтрации для решения следующих задач:

В пакете Microsoft Office есть специальная программа для создания базы данных и работы с ними – Access. Тем не менее, многие пользователи предпочитают использовать для этих целей более знакомое им приложение – Excel. Нужно отметить, что у этой программы имеется весь инструментарий для создания полноценной базы данных (БД). Давайте выясним, как это сделать.

Процесс создания

База данных в Экселе представляет собой структурированный набор информации, распределенный по столбцам и строкам листа.

То есть, каркасом любой базы данных в Excel является обычная таблица.

Создание таблицы

Итак, прежде всего нам нужно создать таблицу.

На этом создание каркаса БД закончено.

Присвоение атрибутов базы данных

Для того, чтобы Excel воспринимал таблицу не просто как диапазон ячеек, а именно как БД, ей нужно присвоить соответствующие атрибуты.

Можно сказать, что после этого мы уже имеем готовую базу данных. С ней можно работать и в таком состоянии, как она представлена сейчас, но многие возможности при этом будут урезаны. Ниже мы разберем, как сделать БД более функциональной.

Сортировка и фильтр

Работа с базами данных, прежде всего, предусматривает возможность упорядочивания, отбора и сортировки записей. Подключим эти функции к нашей БД.

Сортировку можно проводить практически по любому параметру:

имя по алфавиту;
дата;
число и т.д.

Поиск

При наличии большой БД поиск по ней удобно производить с помощь специального инструмента.

Закрепление областей

Удобно при создании БД закрепить ячейки с наименованием записей и полей. При работе с большой базой – это просто необходимое условие. Иначе постоянно придется тратить время на пролистывание листа, чтобы посмотреть, какой строке или столбцу соответствует определенное значение.

Теперь наименования полей и записей будут у вас всегда перед глазами, как бы далеко вы не прокручивали лист с данными.

Выпадающий список

Теперь при попытке ввести данные в диапазон, где было установлено ограничение, будет появляться список, в котором можно произвести выбор между четко установленными значениями.

Конечно, Excel уступает по своим возможностям специализированным программам для создания баз данных. Тем не менее, у него имеется инструментарий, который в большинстве случаев удовлетворит потребности пользователей, желающих создать БД. Учитывая тот факт, что возможности Эксель, в сравнении со специализированными приложениями, обычным юзерам известны намного лучше, то в этом плане у разработки компании Microsoft есть даже некоторые преимущества.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Читайте также: