Дополнительно к фильтрации БД по записям, содержащим определенное значение в поле, можно создавать собственные автофильтры, позволяющие фильтровать БД по записям с более общим критерием, таким как , например, фамилии, начинающиеся с буквы «А», или значения средних баллов в пределах от 4 до 5. Для создания собственного фильтра нужно:
- щелкнуть на кнопке раскрывающегося списка в названии поля ;
- выбрать опцию Условие ;
- в появившемся диалоговом окне Пользовательский автофильтр выбрать необходимый оператор сравнения в первой строке или в обеих строках, если условие составное , т.е. представляет собой результат логических операций типа «и» и «или»; в текстовые окна справа ввести значения (текст или число ), относительно которых должно проводиться сравнение значений в записях БД.
Так для выбора списка студентов, фамилии которых начинаются с буквы «А» необходимо в первой строке диалогового окна Пользовательский автофильтр щелкнуть «равно» и ввести в текстовое окно «А*» (без кавычек ). Получим:
| № | Фамилия | Имя | Отчество | Высш. матем. | Информ. | История Укр. | Физика | Ср.балл | Стипендия |
| 1 | Абдельгадир | Мусса | Ибрагимович | 4 | 5 | 3 | 3 | 3,75 | 0 |
| 5 | Антонова | Вера | Борисовна | 3 | 4 | 4 | 3 | 3,5 | 0 |
Для отбора только студентов, имеющих средний балл в пределах от 4 до 5 , нужно задать условие : «больше или равно» 4 «и» «меньше или равно» 5 (в кавычках – операторы, которые следует выбрать, а 4 и 5 нужно набрать в текстовых полях ) .
Получим:
| № | Фамилия | Имя | Отчество | Высш. матем | Информ. | История Укр. | Физика | Ср.балл | Стипендия |
| 2 | Бирюкова | Галина | Олеговна | 5 | 4 | 4 | 5 | 4,5 | 15 |
| 3 | Вовченко | Александра | Александровна | 3 | 4 | 5 | 4 | 4 | 12 |
| 4 | Грант | Анатолий | Семенович | 4 | 5 | 5 | 5 | 4,75 | 15 |
| 7 | Иванов | Иван | Иванович | 4 | 4 | 4 | 4 | 4 | 12 |
| 9 | Клочко | Георгий | Константинович | 5 | 4 | 3 | 4 | 4 | 12 |
Одним из наиболее мощных средств Excel по работе с БД являются сводные таблицы , которые полезны как для анализа, так и для обобщения информации, хранящейся в БД, на рабочих листах, во внешних файлах. Сводные таблицы позволяют выводить информацию с различной степенью детализации. Для создания сводных таблиц в Excel имеется специальный инструмент Мастер сводных таблиц , в зависимости от версии позволяющий выполнять работу в 3 или в 4 шага.
Разумеется, создавать сводные таблицы имеет смысл только по БД, содержащим значительный объем информации. Расширим первоначальную БД хотя бы до 15 записей и введем новое поле «Группа». Упорядочим список по алфавиту и скорректируем порядок, т.е. данные в поле «№».Получим такую БД:
| № | Фамилия | Имя | Отчество | Группа | Высш. матем. | Информ. | История Укр. | Физика | Ср.балл | Стипендия |
| 1 | Абдельгадир | Мусса | Ибрагимович | 219 | 4 | 5 | 3 | 3 | 3,75 | 0 |
| 2 | Антонова | Вера | Борисовна | 219 | 3 | 4 | 4 | 3 | 3,5 | 0 |
| 3 | Бирюкова | Галина | Олеговна | 219 | 5 | 4 | 4 | 5 | 4,5 | 15 |
| 4 | Борисова | Нина | Павловна | 221 | 5 | 5 | 4 | 5 | 4,75 | 15 |
| 5 | Вовченко | Александра | Александровна | 221 | 3 | 4 | 5 | 4 | 4 | 12 |
| 6 | Горец | Анатолий | Владимирович | 221 | 3 | 3 | 5 | 4 | 3,75 | 0 |
| 7 | Грант | Анатолий | Семенович | 223 | 4 | 5 | 5 | 5 | 4,75 | 15 |
| 8 | Дмитренко | Петр | Павлович | 223 | 3 | 4 | 3 | 3 | 3,25 | 0 |
| 9 | Дмитренко | Виталий | Игоревич | 223 | 4 | 4 | 5 | 4 | 4,25 | 12 |
| 10 | Замовский | Эдуард | Федорович | 235 | 3 | 3 | 4 | 3 | 3,25 | 0 |
| 11 | Иванов | Иван | Иванович | 235 | 4 | 4 | 4 | 4 | 4 | 12 |
| 12 | Клочко | Георгий | Константинович | 235 | 5 | 4 | 3 | 4 | 4 | 12 |
| 13 | Новиков | Олег | Валентинович | 241 | 3 | 4 | 3 | 3 | 3,25 | 0 |
| 14 | Прокопенко | Виталий | Викторович | 241 | 4 | 3 | 3 | 4 | 3,5 | 0 |
| 15 | Соловьев | Руслан | Анатольевич | 241 | 3 | 4 | 5 | 4 | 4 | 12 |
Для такой БД можно составить сводную таблицу стипендий или среднего балла по каждому предмету и по всем экзаменам для каждой группы отдельно. Последовательность действий при создании сводной таблицы должна быть следующей:
- Выполнить команды Данные ––Сводная таблица .
- В первом диалоговом окне « Мастер сводных таблиц – шаг 1 из 4 » установить переключатель в положение, определяющее, где находятся данные для сводной таблицы: в нашем случае следует щелкнуть на первом положении переключателя « В списке или базе данных Microsoft Excel ».
- Нажать кнопку Далее, в результате чего появится второе диалоговое окно «Мастер сводных таблиц- шаг 2 из 4».
- В поле «Диапазон» указать, в каком диапазоне находятся исходные данные для сводной таблицы ( в нашем случае это $A$1:$K$16 ) и нажать кнопку Далее.
- В третьем диалоговом окне «Мастер сводных таблиц – шаг 3 из 4» необходимо указать структуру сводной таблицы, т.е. определить, данные какого поля должны использоваться в качестве заголовков строк и каких – в качестве заголовков столбцов, для чего перетащить их названия , представленные в окне в виде кнопок, в соответствующие области «Строка» ( в нашем случае –«Группа» ) и «Столбец» (в нашем случае – ничего ).
- В этом же диалоговом окне в область «Данные» перетащить название поля (или полей ), данные которого подлежат обработке ( в нашем случае – «Стипендия» или «Средний балл» или названия всех предметов для определения среднего балла по каждому экзамену).
- Задать правило, по которому должна осуществляться обработка, щелкнув дважды по кнопке, перемещенной в область «Данные» , и выбрав в появившемся диалоговом окне «Вычисление поля сводной таблицы» нужную операцию ( для примера о стипендии – «Сумма», в других примерах – «Среднее» ), в результате чего в поле «Имя» появятся названия операции и поля, по которому она будет выполняться.
- В диалоговом окне « Мастер сводных таблиц – шаг 4 из 4 » нужно задать некоторые параметры, определяющие вид сводной таблицы:
- в поле окна «Поместить таблицу в» указать адрес левой верхней ячейки таблицы на текущем листе или оставить поле пустым , чтобы таблица разместилась в начале нового рабочего листа;
- указать название сводной таблицы в поле «Название таблицы»;
- установить флажки «Общие итоги по столбцам» и «Общие итоги по строкам», если нужно ;
- для создания дополнительной копии данных установить флажок «Сохранить данные с макетом таблицы»;
- установить флажок «Автоматически форматировать таблицу» для использования средств автоформата Excel.
- Для вывода сводной таблицы на экран после этого щелкнуть на кнопке Готово.
Для детализации данных сводной таблицы необходимо дважды щелкнуть на названии поля и в открывшемся диалоговом окне «Показать детали» указать , по какому полю необходимо выполнить детализацию.
| Сумма по полю Стипендия | ||
| Группа | Фамилия | Всего |
| 219 | 15 | |
| 221 | Абдельгадир | 0 |
| Вовченко | 12 | |
| Грант | 15 | |
| 221 Всего | 27 | |
| 223 | Горец | 0 |
| Иванов | 12 | |
| Клочко | 12 | |
| 223 Всего | 24 | |
| 235 | 12 | |
| 241 | 27 | |
| Общий итог | 105 |
В приведенной сводной таблице выполнена детализация по полю «Группа 221» и по полю «Группа 223», по остальным группам детализация не задавалась.
Следующая сводная таблица дает возможность проанализировать результаты экзаменов по каждому предмету и сравнить успехи групп , детализация здесь не выполнялась, но при необходимости это может быть сделано.
В Excel 2000 Мастер сводных таблиц предлагает выполнение тех же действий по созданию сводных таблиц , но только с помощью трех диалоговых окон. Создание структуры и задание параметров сводной таблицы выполняется после нажатия кнопок Макет и Параметры в диалоговом окне «Мастер сводных таблиц – шаг 3 из 3».