Статья: Работа с базами данных в MS Excel

--PAGE_BREAK--Для активизации нужного фильтра следует выполнить такие действия:
Ø Щелкнуть где-либо в БД.
Ø Выбрать Данные – Фильтр – Автофильтр, в результате чего 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 команды Автофильтр. Эту опцию целесообразно использовать при работе с числовыми полями для вывода на экран только десяти записей ( например, с наибольшими или наименьшими значениями в данном поле). Более того, с помощью этой опции можно вывести на экран любое количество записей, указанное пользователем в диалоговом окне «Наложение условия по списку», которое появляется в ответ на выбор указанной опции.
 №
Фамилия
 Имя
Отчество
Высш матем.
Информ.
История Укр.
Физика
Ср.балл
Стипендия
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
В данном примере приведены наибольшие 5 записей, отфильтрованные по полю Ср.балл.
Дополнительно к фильтрации БД по записям, содержащим определенное значение в поле, можно создавать собственные автофильтры, позволяющие фильтровать БД по записям с более общим критерием, таким как, например, фамилии, начинающиеся с буквы «А», или значения средних баллов в пределах от 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».
Excel предоставляет возможность подвести промежуточные и окончательные итоги по полям БД, вставив необходимые строки для подсчитанных сумм. Кроме того, Excel может отображать БД в режиме структуры, что позволяет выводить на экран только промежуточные итоги.
Добавление итогов осуществляется после предварительной сортировки БД по нужному полю (например, по номеру группы) следующей последовательностью действий:
Группа
Данные
Всего
219
Среднее по полю Высш.матем.
4
Среднее по полю Информ.
3,666666667
Среднее по полю История Украины
3,333333333
Среднее по полю Физика
4
221
Среднее по полю Высш.матем.
3,666666667
Среднее по полю Информ.
4,666666667
Среднее по полю История Украины
4,333333333
Среднее по полю Физика
4
223
Среднее по полю Высш.матем.
4
Среднее по полю Информ.
3,666666667
Среднее по полю История Украины
4
Среднее по полю Физика
4
235
Среднее по полю Высш.матем.
3,333333333
Среднее по полю Информ.
3,666666667
Среднее по полю История Украины
4,333333333
Среднее по полю Физика
3,333333333
241
Среднее по полю Высш.матем.
3,666666667
Среднее по полю Информ.
4,333333333
Среднее по полю История Украины
4
Среднее по полю Физика
4
Добавление итогов осуществляется после предварительной сортировки БД по нужному полю (например, по номеру группы) следующей последовательностью действий:
Ø Выбрать Данные –- Итоги, в результате чего БД будет автоматически выделена и на экране появится диалоговое окно « Промежуточные итоги ».
Ø В строке «При каждом изменении в » указать поле, при изменении значения которого следует подводить промежуточный итог, для чего щелкнуть на стрелке справа и выбрать нужное название поля ( например, «Группа» для нашей БД).
Ø В строке «Операция», щелкнув на стрелке справа, выбрать в появившемся перечне возможных операций необходимую ( например, «Среднее» ).
Ø Для выполнения той же операции с данными в других полях необходимо отметить их названия, щелкнув в списке строки «Добавить итоги по»(например, названия всех предметов и поля Ср.балл ).
Ø Убедиться, что параметр «Заменить текущие итоги» установлен и щелкнуть на кнопке ОК.
    продолжение
--PAGE_BREAK--