Лабораторная работа 7. Сводные таблицы
Сводные таблицы используются для группировки данных по одному или нескольким критериям, при этом производятся различные расчеты по этим данным: суммирование, нахождение среднего и т.д. Для их создания пользуются мастером сводных таблиц, вызвав его по опции Данные - Сводная таблица. Можно рассматривать сводную таблицу как средство группирования данных по различным вторичным ключам с последующими расчетами по требуемым параметрам.
Создание сводной таблицы
При создании сводной таблицы используются данные из списка. Затем необходимо выполнить следующие действия:
а) выделить ячейку списка или базы данных,
б) выбрать команды Сводная таблица – Сводная таблица в меню Таблицы вкладки Вставка,
в) следуя инструкциям мастера сводных таблиц:
выбрать диапазон исходных данных. Это может быть либо вся таблица, либо ее часть. При этом необходимо не забывать, что сводная таблица составляется для не менее 2-х столбцов. Возможно выделение ячеек исходных данных до вызова данной опции, тогда Мастер сводной таблицы будет считать их исходными. В противном случае он выделяет всю таблицу, но это не окончательно. Вы можете, нажав на кнопку в конце строки Таблица или диапазон, сами выделить необходимую область для создания сводной таблицы. Здесь же указывается, где следует разместить отчет сводной таблицы. Нажать кнопку ОК;
справа появится окно, используемое для проектирования сводной таблицы. Оно поделено на две части: вверху указан список полей исходной таблицы, на основании которых формируется сводная таблица, внизу показаны области сводной таблицы, формирование которых и позволяет строить нужные выборки;
перетащить кнопки, расположенные вверху и обозначающие названия полей, в нужные области сводной таблицы. В полях Названия строк и Названия столбцов могут находиться несколько кнопок (или признаков). Обязательно перетащите одну или несколько кнопок в поле Значения, где производятся итоговые вычисления. Если перетаскивается символьное данное, определяется число элементов; если перетаскивается числовое данное, определяется сумма элементов1. Нажать кнопку Далее.
полученная сводная таблица появляется слева на листе. Её можно откорректировать по названию граф, вводя в нужные ячейки нужные названия. Если структура полученной таблицы не удовлетворяет пользователя, можно её поменять, отбуксировав имя поля из области сводной таблицы в список полей;
если нужно заменить вычисляемую функцию в области Значения, в меню в поле Значения в опции Параметры полей значений выбирают нужную функцию.
Пусть исходный список имеет вид:
 .
Сформируем сводную таблицу, определив средний балл за сессию по каждому студенту. Для этого при формировании сводной таблицы переместим в область Названия строк поле Фамилия, в поле Значения – поле Оценка. При этом предлагается подсчет суммы по этому полю. Меняем операцию на Среднее. В итоге (выполнив несложные остальные шаги) получим:
ФИО
|
Среднее по полю оценка
|
Иванов
|
3,333333333
|
Сидоров
|
2,5
|
Петров
|
4
|
Общий итог
|
3,166666667
|
б) сформируем сводную таблицу для определения максимального балла по каждой из дисциплин. Для этого в поле Названия строк поместим поле Дисциплина, в поле Значения – Оценка и изменим операцию на Максимум. Получим в итоге:
Дисциплина
|
Максимум по полю оценка
|
Алгебра
|
5
|
Информатика
|
4
|
Физика
|
5
|
Общий итог
|
5
|
в) сформируем сводную таблицу для определения среднего балла по каждой дисциплине с дополнительной возможностью фильтрации результатов в соответствии со списком студентов. Для этого в поле Фильтр поместим поле Фамилия, в поле Названия строк – Дисциплина, в поле Значения – Оценка и изменим операцию на Среднее. Получим:
фамилия
|
Сидоров
|
|
|
Дисциплины
|
Среднее по полю оценка
|
Алгебра
|
5
|
Информатика
|
2
|
Физика
|
3
|
Общий итог
|
3,333333333
|
Здесь виден фильтр (строка фамилия), позволяющий просматривать весь список студентов и по каждому видеть результат (очевидно, средний балл для студента по одной дисциплине есть не что иное как оценка по этой дисциплине). Там же есть опция Все, позволяющая получать агрегированный результат по всей группе студентов. Общий итог показывает средний балл за сессию по одному (на нашем рисунке) или для всех студентов.
г) сформируем сводную таблицу, группирующую студентов по дисциплинам и по каждому студенту выводящую его балл. Для этого в поле Названия строк поместим сначала поле Дисциплина, затем – Фамилия. В поле Значения поместим поле Оценка, причем операцию можно назначить, например, - Среднее. Получим результат:
Дисциплины
|
Среднее по полю оценка
|
Алгебра
|
4
|
Иванов
|
3
|
Петров
|
4
|
Сидоров
|
5
|
Информатика
|
3
|
Иванов
|
3
|
Петров
|
4
|
Сидоров
|
2
|
Физика
|
4
|
Иванов
|
4
|
Петров
|
5
|
Сидоров
|
3
|
Общий итог
|
3,666666667
|
Очевидно, кроме заказанной информации сформированы промежуточные итоги для каждой дисциплины и по всей группе.
д) сформируем сводную таблицу, содержащую в строках информацию о дисциплинах, в столбцах – о фамилиях студентов, а на пересечении строк и столбцов – оценки. Для этого поместим поле Дисциплина в поле Названия строк, поле Фамилия – в поле Названия столбцов, поле Оценка – в поле Значения (операция - Максимум). Получим результат:
Максимум по полю Оценка
|
фамилии
|
|
|
|
Дисциплины
|
Иванов
|
Петров
|
Сидоров
|
Общий итог
|
Алгебра
|
3
|
4
|
5
|
5
|
Информатика
|
3
|
4
|
2
|
4
|
Физика
|
4
|
5
|
3
|
5
|
Общий итог
|
4
|
5
|
5
|
5
|
Необходимо помнить, что в вычисляемых полях подведение итогов данных всегда производится с помощью итоговой функции «Сумма». Изменение итоговой функции для вычисляемого поля невозможно.
Задание
Создать исходный список, содержащий следующие столбцы: Имя, Пол, Адрес (можно ограничиться городом), Возраст, Степень родства, Профессия, Трудовой стаж. Заполнить список данными (15 строк) по своим родственникам.
Построить сводные таблицы в соответствии с заданиями и по возможности разместить их на одном листе с исходной таблицей:
а) определить средний трудовой стаж у женщин;
б) решить ту же задачу для женщин, живущих в Калининграде;
в) определить суммарный трудовой стаж по каждой профессии;
г) определить суммарный трудовой стаж по каждой профессии отдельно для мужчин и женщин;
д) определить по каждой группе родственников с одинаковой степенью родства их средний возраст;
е) определить число родственников, имеющих одинаковые имена (по каждому имени отдельно).
Показать результат преподавателю.
|
