Базы данных в Excel

Выбрать Данные – Форма.

Щелкнуть на кнопке Критерии в появившейся форме, после чего очистятся все поля формы.

Ввести требуемые критерии поиска в соответствующие поля формы.

Нажать кнопку Далее или  клавишу Enter , чтобы начать поиск.

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

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

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

Для редактирования значения поля в текущей записи необходимо перейти в него, нажимая клавиши <Tab> или <Shift+Tab> (или с помощью  мыши) , и ввести новое значение. Для  очистки поля целиком выделить его  и нажать клавишу <Del>.

Для удаления записи из БД щелкнуть на кнопке Удалить в окне формы  данных. При этом, однако, следует  помнить, что невозможно восстановить удаленную таким образом запись с помощью команды Отменить. Поэтому Excel выдаст окно предупреждения с таким  сообщением:« Запись, выведенная на экран, будет удалена ». Можно  подтвердить свое решение об удалении записи, щелкнув на кнопке ОК , или  отменить, щелкнув на кнопке Отмена .

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

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

1.3. Обработка данных в  БД.

Любая информация должна быть упорядочена. Хорошая БД – в том  числе. Каждая БД имеет некоторый  предпочтительный порядок поддержания  и просмотра записей. Записи можно  расположить, например, в алфавитном порядке фамилий или названий фирм. Для определения рейтинга студентов  список группы удобно расположить по убыванию среднего балла. Однако при  добавлении новых записей Excel включает их в самый конец БД, добавляя новые строки и нарушая прежний  порядок. Это не единственная проблема с упорядочиванием записей, так  как всегда может возникнуть необходимость  в другом, особенном порядке. Другими  словами, при работе с данными  требуется гибкость упорядочения записей  для различных целей. Процесс  упорядочения БД называется сортировкой.

Для сортировки элементов  в БД необходимо выполнить такие  действия:

Щелкнуть на любой ячейке БД.

Выбрать команду Сортировка из меню Данные, в результате чего Excel раскроет диалоговое окно Сортировка диапазона.

Щелкнуть на стрелке в  группе «Сортировать по» и выбрать  главный ключ сортировки ( поле, по которому должна быть выполнена сортировка ).

Выбрать восходящее «по возрастанию» или нисходящее «по убыванию»  упорядочение , щелкнув по переключателю  справа .

Если нужно выполнить  еще одно упорядочивание внутри первого, щелкнуть на стрелке в следующей  группе «Затем по» и выбрать второе поле сортировки и затем – восходящий или нисходящий порядок.

При необходимости дальнейшего  иерархического упорядочения выбрать  поле и порядок упорядочения в  последнем списке «В последнюю очередь, по» .

Для отказа от выбора полей  и порядка сортировки достаточно , не выходя из этого диалогового  окна , снова открыть список полей  и выбрать «( не сортировать )».

Щелкнуть на ОК или нажать Enter.

Excel отсортирует отмеченные  записи. При необходимости отказаться  от ошибочной сортировки следует  выбрать Правка – Отменить  Сортировку или нажать <Ctrl+Z> для  восстановления записей БД в  прежнем порядке.

№ Фамилия Имя Отчество Высш. матем. Информ. История Укр. Физика Ср.балл Стипен

4  Грант Роман Семенович    4             5         5                    5              4,75         15         

2  Бирюкова  Галина Олеговна    5       4         4                    5               4,5         15

3 Вовченко Анна Сергеевна    3       4         5                    4                 4          12

1АбдельгадирМуссаИбрагимович  4       5         3                    3               3,75            0

В приведенном примере  записи упорядочены по убыванию значений в поле Ср.балл. Прежний порядок  записей в БД можно проследить по первому полю «№».

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

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

Для активизации нужного  фильтра следует выполнить такие  действия:

Щелкнуть где-либо в БД.

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

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

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

отметить клетки;

выбрать команду Копировать из меню Правка (или нажать <Ctrl+C>);

переместить табличный курсор в первую ячейку таблицы на новом  листе, щелкнув по ярлычку «Лист2»  в нижней части экрана;

нажать Enter.

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

№  Фамилия  Имя  Отчество  Высш.матем. Информ.  ИсторияУкр.   Физика    Ср.балл    Стипе

2  Бирюкова  Галина  Олеговна      5             4            4           5     4,5        15

4  Грант  Анатолий  Семенович      4             5            5            5    4,75        15

Так выглядит БД, отфильтрованная  по полю «Стипендия» и значению 15, т.е. содержащая записи, у которых  в этом поле стоит значение 15. В  этот список вошли только студенты, получающие стипендию в размере 15 гр.

После копирования отфильтрованных  записей можно восстановить отображение  всех записей БД, щелкнув на кнопке списка, по которому выполнялась фильтрация (кнопка выделена голубым цветом ), и  выбрав в раскрывшемся списке пункт  «(Все )».

При необходимости сузить круг поиска можно продолжить фильтрацию , выбрав значение из раскрывающегося  списка другого поля. Excel предоставляет  возможность просмотреть только первые 10 записей с помощью опции  Первые 10 команды Автофильтр. Эту  опцию целесообразно использовать при работе с числовыми полями для вывода на экран только десяти записей ( например, с наибольшими  или наименьшими значениями в  данном поле). Более того, с помощью  этой опции можно вывести на экран  любое количество записей, указанное  пользователем в диалоговом окне «Наложение условия по списку», которое  появляется в ответ на выбор указанной  опции.

Дополнительно к фильтрации БД по записям, содержащим определенное значение в поле, можно создавать  собственные автофильтры, позволяющие  фильтровать БД по записям с более  общим критерием, таким как , например, фамилии, начинающиеся с буквы «А», или значения средних баллов в  пределах от 4 до 5. Для создания собственного фильтра нужно:

щелкнуть на кнопке раскрывающегося  списка в названии поля ;

выбрать опцию Условие ;

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

Так для выбора списка студентов, фамилии которых начинаются с  буквы «А» необходимо в первой строке диалогового окна Пользовательский автофильтр щелкнуть «равно» и ввести в текстовое окно «А*» (без кавычек ). Получим:

Для отбора только студентов, имеющих средний балл в пределах от 4 до 5 , нужно задать условие : «больше  или равно» 4 «и» «меньше или  равно» 5 (в кавычках – операторы, которые следует выбрать, а 4 и 5 нужно  набрать в текстовых полях ) .

Одним из наиболее мощных средств Excel по работе с БД являются сводные  таблицы , которые полезны как  для анализа, так и для обобщения  информации, хранящейся в БД, на рабочих  листах, во внешних файлах. Сводные  таблицы позволяют выводить информацию с различной степенью детализации. Для создания сводных таблиц в Excel имеется специальный инструмент Мастер сводных таблиц , в зависимости  от версии позволяющий выполнять  работу в 3 или в 4 шага.

Разумеется, создавать сводные  таблицы имеет смысл только по БД, содержащим значительный объем  информации. Расширим первоначальную БД хотя бы до 15 записей и введем новое поле «Группа». Упорядочим список по алфавиту и скорректируем порядок, т.е. данные в поле «№».

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

Выполнить команды Данные ––Сводная таблица .

В первом диалоговом окне «  Мастер сводных таблиц – шаг 1 из 4 » установить переключатель в  положение, определяющее, где находятся  данные для сводной таблицы: в  нашем случае следует щелкнуть на первом положении переключателя  « В списке или базе данных Microsoft Excel ».

Нажать кнопку Далее, в  результате чего появится второе диалоговое окно «Мастер сводных таблиц- шаг 2 из 4».

В поле «Диапазон» указать, в каком диапазоне находятся  исходные данные для сводной таблицы ( в нашем случае это $A$1:$K$16 ) и нажать кнопку Далее.

В третьем диалоговом окне «Мастер сводных таблиц – шаг 3 из 4» необходимо указать структуру  сводной таблицы, т.е. определить, данные какого поля должны использоваться в  качестве заголовков строк и каких  – в качестве заголовков столбцов, для чего перетащить их названия , представленные в окне в виде кнопок, в соответствующие  области «Строка» ( в нашем случае –«Группа» ) и «Столбец» (в нашем  случае – ничего ).

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

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

В диалоговом окне « Мастер сводных таблиц – шаг 4 из 4 » нужно  задать некоторые параметры, определяющие вид сводной таблицы:

в поле окна «Поместить таблицу  в» указать адрес левой верхней  ячейки таблицы на текущем листе  или оставить поле пустым , чтобы  таблица разместилась в начале нового рабочего листа;

указать название сводной  таблицы в поле «Название таблицы»;

установить флажки «Общие итоги по столбцам» и «Общие итоги  по строкам», если нужно ;

для создания дополнительной копии данных установить флажок «Сохранить данные с макетом таблицы»;

установить флажок «Автоматически форматировать таблицу» для использования  средств автоформата Excel.

Для вывода сводной таблицы  на экран после этого щелкнуть на кнопке Готово.

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

Сумма по полю Стипендия  

Группа Фамилия        Всего

219                             15

221           Абдельгадир       0