Лабораторная работа 8

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

Цель работы – изучение средств создания и модификации сводных таблиц.

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

1. Создание сводной таблицы

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

а) выделить ячейку списка или базы данных,

б) выбрать команду Сводная таблица в меню Данные,

в) следуя инструкциям мастера сводных таблиц:

• указать, где находятся исходные данные (как правило, в списке). Нажать кнопку Далее;

• выбрать диапазон исходных данных. Это может быть либо вся таблица, либо ее часть. При этом необходимо не забывать, что сводная таблица составляется для не менее 2-х столбцов. Возможно выделение ячеек исходных данных до вызова данной опции, тогда Мастер сводной таблицы будет считать их исходными. В противном случае он выделяет всю таблицу, но это не окончательно. Вы можете, нажав на кнопку в конце строки «Диапазон», сами выделить необходимую область для создания сводной таблицы, либо на кнопку Обзор - тогда можно выбрать другой документ с исходными данными. Нажать кнопку Далее. Откроется очередное диалоговое окно, понятия которого иллюстрируются на рис. 2;

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

Рис. 2 Части сводной таблицы

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

• указать, где должна быть создана таблица. Если на новом листе, то мастер автоматически отведет для сводной таблицы новый лист в начале книги. Если на уже существующем, то его необходимо указать по правилам Microsoft Excel. Затем нажать на кнопку Готово, и сводная таблица создана.

Пусть исходный список имеет вид:

.

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

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

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

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

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

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

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

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

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