Сводные таблицы
Цель работы: научиться создавать и применять сводные таблицы
при работе с данными
Содержание работы:
1 Мастер сводных таблиц
2 Построение макета сводной таблицы
3 Работа со сводной таблицей
Мастер сводных таблиц
Сводная таблица ( или свод) – этотаблица итогов различных видов, составленная по данным из нескольких таблиц. Сводная таблица составляется в виде отчёта, в котором данные сгруппированы по блокам, для каждого блока и всего свода по какому-либо полю вычисляется заданная функция – среднее, сумма и др., причём таких полей может быть несколько. В качестве таблиц-источников могут выступать списки, другие сводные таблицы, базы данных внешние и внутренние, отдельные блоки данных электронной таблицы Excel и др. Сводная таблица обеспечивает различные способы агрегирования информации. Простейший свод составляется по одной таблице.
Пример 1 Дан список "Экзаменационная ведомость" (рис.11.1).
Рисунок 11.1 Список "Экзаменационная ведомость"
Требуется построить сводную таблицу, в которой строками являются № группы, Фамилия И.О. и № зачётной книжки, а столбцами – Коды преподавателей. В ячейках на их пересечении нужно расположить оценки, которые каждый студент получил у принимавшего экзамен преподавателя и вычислить средний балл для каждого преподавателя.
Сводная таблица строится в виде отчёта с помощью Мастера сводных таблиц, который вызывается командами вкладки Вставка \ Сводная таблица\. Сводная таблица.
В окне Создание сводной таблицы (рис. 11.2) нужно ввести следующую информацию:
1Указание вида источника сводной таблицы:
§ использование списка (базы данных Excel);
§ использование внешнего источника данных;
§ использование нескольких диапазонов консолидации;
§ использование данных из другой сводной таблицы.
Рисунок 11.2 Составление сводной таблицы
В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Наиболее распространён вариант использования внутренних списков.
2Указание диапазона ячеек, содержащего исходные данные. Список (база данных Ехсеl) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде:
[имя_книги] имя_листа! диапазон ячеек
(параметр в квадратных скобках необязателен)
Если предварительно курсор установлен в список, для которого строится сводная таблица, то интервал ячеек будет указан автоматически. Данные из других документов (внешние базы данных, другие таблицы и их диапазоны) указываются через кнопку Выбрать подключение…, появляется окно, в котором выбирается диск, каталог и файл рабочей книги, вводится имя рабочего листа и диапазон либо имя блока ячеек.
3 Выбор места расположения и параметров сводной таблицы.
Можно выбрать место расположения отчёта сводной таблицы, установив переключатель На новый лист или На существующий лист, для которого необходимо задать диапазон размещения.
Построение макета сводной таблицы.
После нажатия кнопки ОК будет сформирована сводная таблица со стандартным именем СводнаяТаблица1 и список её полей (рис. 11.3). Если список полей не вывелся, нужно щелкнуть правой мышью по имени СводнаяТаблица1 и выбрать команду Показать список полей.
Структура сводной таблицы состоит из следующих полей и областей, определяемых в макете (рис.11.3):
Поля для добавления в отчёт – для размещения полей, значения которых обеспечивают отбор записей в сводную таблицу. На странице может быть размещено несколько полей, между которыми устанавливается иерархия связи - сверху вниз, определять страницу необязательно. Значок списка в правом верхнем углу позволяет задать расположение полей и областей в столбик (как на рис. 11.3), рядом, только поля, только области и др.
Рисунок 11.3 Макет отчёта сводной таблицы
Область Фильтр отчёта –здесь размещаются поля, по которым подводятся итоги, согласно выбранной функции, в нашем случае - Оценки. Область нужно определять обязательно.
Область Названия столбцов— поля размещаются сверху вниз, обеспечивая группировку данных сводной таблицы по иерархии полей. Для примера 1 в область перетаскивается поле Код преп. Если существует область страницы или строки, то определять столбец необязательно.
Область Названия строк- поля размещаются сверху вниз, обеспечивая
группировку данных таблицы по иерархии полей. Сюда помещаем поля № группы, Фамилия И.О. и № зач. кн. Если существует область страницы или столбца, то определять строку необязательно.
Область ∑ Значения по умолчанию появляется вариант Сумма по полю Оценка. При необходимости использования другой функции в качестве подведения итогов свода нужно щелкнуть мышью по строке Сумма по полю Оценка и в раскрывающемся меню выбрать опцию Параметры полей значений. В результате появится окно (рис. 11.4), во вкладке Операция можно выбрать одну из 12 функций (количество, среднее, максимум, произведение, …, дисперсия), во вкладке Дополнительные вычисления – задать доли, нарастающий итог и пр. – см. табл. 1. При использовании функции сравнения (Отличие, Доля, Приведенное отличие) выбирается Поле и Элемент, с которым будет производиться сравнение. Список Поле содержит поля сводной таблицы, с которым связаны базовые данные для пользовательского вычисления. Список Элементсодержит значения поля, участвующего в пользовательском вычислении.
Таблица 1Виды дополнительных функций в полях области данных
| Функция | Результат |
| Отличие | Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках, поле и элемент |
| Доля | Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элементам. |
| Приведенное отличие | Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в стеках поле и элемент, нормированной к значению этого элемента |
| С нарастающим итогом в поле | Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге |
| Доля от суммы по строке | Значения ячеек области данных отображаются в Процентах от итога строки |
| Доля от суммы по столбцу | Значения ячеек области данных отображаются в Процентах от итога столбца |
| Доля от общей суммы | Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы |
| Индекс | При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог))/((Итог строки) *(Итог столбца) |
В окне можно также задать числовой формат – общий, денежный, процентный и др.
Рисунок 11.4 Окно Параметры полей значений
Для выполнения задания примера 1 в окне списка полей для добавления в отчёт нужно поставить галочки во всех полях, затем перетащить мышью поля № группы, Фамилия И.О. и № зач. кн. в раздел Названия строк, поле Код преп – в раздел Названия столбцов, а поле Оценки – в Фильтр отчёта. В области ∑ Значения по умолчанию стоит вариант Сумма по полю Оценка, поэтому нужно щелкнуть мышью по этой строке и в появившемся окне во вкладке Операции заменить Сумма на Среднее (рис. 11.4).
Как видно из рис. 11.5, сводная таблица содержит три раздела, название каждого раздела (№ группы), средний бал по каждому преподавателю и общий итог по разделу приводятся в его первой строке.
В последней строке таблицы приводится общий итог по всем разделам свода.
Данныевсводной таблице сгруппированы по строкам (группировкав производится автоматически), при этом данные могут быть свёрнуты - развернуты как по строкам, так и по разделам.
В областях строка или столбец каждое поле размещается только один раз. Поля, помещённые в область для столбцов, остаются столбцами, а поля, помещённые в область для строк, разворачиваются в строку. На их пересечении формируются группы и получаются итоговые (сводные) значения в области для данных — группировочные поля. В области Фильтр отчётамогут находиться поля произвольных типов, одно и то же поле может многократно размещаться в области данных. Для каждого такого поля в области ∑ Значения задается вид функции и выполняется необходимая настройка.
Для изменения структуры сводной таблицы выполняется перемещение полей из одной области в другую (добавление новых, удаление существующих полей, изменение местонахождения поля). Для сводных таблиц существен порядок следования полей (слева направо, сверху вниз), изменяется порядок следования полей также путем их перемещения.
Рисунок 11.5 Исходная таблица, сводная таблица и список её полей.
После завершения построения сводной таблицы могут измениться исходные данные. Для обновления данных сводкой таблицы в этом случае следует выполнить команду Данные\ Обновить все.
Для изменения структуры уже построенной сводной таблицы курсор устанавливается в область сводной таблицы, повторно выполняется команда Вставка\ Сводная таблица.
Контрольные вопросы
1 Что называется сводной таблицей, сводом?
2 Для чего предназначены сводные таблицы?
3 Что помещается в областях для строк, столбцов, данных макета?
4 Как задать в сводной таблице вид вычислительного итога Сумма, Максимум, Произведение?
5 Как обновить данные в сводной таблице?
6 Как изменить структуру сводной таблицы (добавить или изменить поля строк, столбцов, данных?
Задание
Для таблицы своего варианта из лабораторной работы 12 "Списки в MS Excel. Сортировка и фильтрация данных" построить две сводные таблицы. Поля, помещаемые в области строк, столбцов и данных выбрать самостоятельно.