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

ЛЕКЦИЯ 9. СВОДНЫЕ ТАБЛИЦЫ План 1. 2. 3. 4. 5. 6. Сводная таблица. Основные понятия......................................................... 1 Создание сводных таблиц ............................................................................ 2 Построение сводных диаграмм .................................................................. 6 Одновременное построение сводных таблиц и диаграмм ..................... 7 Фильтрация данных: срезы и временные шкалы ..................................... 8 Контрольные вопросы .................................................................................. 9 1. Сводная таблица. Основные понятия Сводные таблицы представляют собой одно из самых удобных и уникальных инструментов, которые имеет Excel. Они отлично подходят для хранения больших наборов данных и создания подробных сводок. Сводная таблица – это динамическая таблица итоговых данных, извлеченных или рассчитанных на основе информации, содержащейся в базе данных или большой таблице данных. Сводные таблицы являются интерактивными и позволяют создавать динамические перекрестные таблицы с легко изменяемой структурой, данные в которых обобщаются по нескольким измерениям. Получение итоговых данных для больших таблиц является крайне трудоемкой задачей, и ее решение без использования сводных таблиц во многих случаях просто невозможно. Кроме того, с помощью сводной таблицы можно подсчитать промежуточные итоги с любым необходимым уровнем детализации. Фактически построение сводной таблицы представляет собой − процесс получения из исходной таблицы другой, меньшей по размеру, − получение таблицы с другим расположением строк и столбцов, что открывает перед пользователем существенные возможности. При создании сводной таблицы данные должны удовлетворять следующим требованиям: − быть в форме прямоугольной таблицы: это может быть таблица, созданная на текущем листе и имеющая имя, или это может быть диапазон листа, или внешняя база данных; − каждая строка должна иметь значения во всех своих столбцах, поскольку отсутствие данных приводит к неоднозначной интерпретации получаемых результатов; − не должно быть объединенных ячеек и пустых строк. Данные, по которым строится сводная таблица, можно разделить на два вида: 1 категории, представляющие собой названия столбцов и служащие для их идентификации; − данные — это значения исходной таблицы, которые либо суммируются, либо определяется их среднее значение, либо считается их количество. Выбрать тип вычисления для данных можно в установке значений полей. Кроме перечисленных требований к данным, исходная таблица должна обязательно иметь название каждого столбца, в противном случае сводная таблица правильно не построится. Единственным недостатком сводных таблиц является то, что они не обновляются автоматически при изменении исходных данных. Для обновления сводных таблиц при изменении исходных данных большой таблицы нужно щелкнуть на кнопке Обновить на вкладке Данные в − разделе Запросы и подключения. Удобство использования продемонстрировать на примере. сводных таблиц проще всего 2. Создание сводных таблиц Сводные таблицы эффективно строятся для исходных данных, содержащих 5000 или даже 50 000 строк. Однако для ясности понимания возьмем простую таблицу, содержащую 21 строку (включая названия полей) и 4 столбца. В каждой строке этой таблицы указаны фамилия студента, названия предмета, по которому он пропускал занятия, дата, когда были получены сведения о пропусках и количество пропущенных часов: Рисунок 1 – Исходная таблица 2 Создадим для такой таблицы несколько сводных таблиц. Пусть исходная таблица находится на листе 1 в диапазоне A1:D21. Для построения сводной таблицы выполним следующее: 1) выделим свободную ячейку на листе, начиная с которой необходимо разместить сводную таблицу. На вкладке Вставка в группе Таблицы выбираем команду Сводная Таблица . Откроется окно диалога создания сводной таблицы; 2) установим в верхнем поле этого окна адрес диапазона исходной таблицы, в данном примере это Лист1!$A$1:$D$21; 3) выберем место размещения сводной таблицы на существующем или отдельном листе и установим ссылку на левую верхнюю ячейку диапазона, в данном примере это Лист1!$F$1(ячейка F1 на листе Лист1 (риc. 2)); Рисунок 2 – Окно диалога создания сводной таблицы 4) щелкнем OK, и на рабочем листе рядом с исходной таблицей появится пустой макет сводной таблицы (риc. 3). Построение сводной таблицы очень просто, необходимо просто перетаскивать нужные поля таблицы в нужные области макета будущей таблицы (риc. 3). Построение сводной таблицы 1. Возьмем в качестве строк фамилии, а в качестве столбцов — названия предметов, тогда на пересечении строки и столбца можно разместить количество пропущенных студентом часов по данному предмету (если у него были пропуски). Автоматически будет также вычислено итоговое значение пропусков для каждого студента и каждого предмета. Установив фильтр для дат, пользователь имеет возможность делать выборку о пропусках на любую дату или совокупности нужных дат (риc. 4). 3 Рисунок 3 – Макет сводной таблицы Рисунок 4 – Сводная таблица 1 (по студентам и предметам) На этом же рисунке выполнена визуализация данных сводной таблицы. Для этого нужно на вкладке ленты Главная выбрать команду Условное форматирование, щелкнуть на ней и затем из Гистограммы выбрать подходящий цвет градиентного или сплошного заполнения. Построение сводной таблицы 2. Теперь создадим другую сводную таблицу, поменяв местами строки и столбцы: пусть в качестве строк будут названия предметов, а в качестве столбцов — фамилии студентов, что иногда оказывается более удобным при построении отчета. Никакие другие средства (кроме программирования) не дают такой простой возможности манипуляции строками и столбцами электронной таблицы (риc. 5). Рисунок 5 – Сводная таблица 2 (по предметам и студентам) 4 Построение сводной таблицы 3. Если потребуется сводная таблица, показывающая, сколько часов в определенном месяце пропустил каждый студент и по скольким предметам, то надо перенести в область Строки — Фамилии, а в область Значения — Количество часов и Предметы, Месяцы (Даты) – в Фильтры. Для данных Предметы автоматически вычисляется количество пропущенных предметов. На риc. 6 во втором и третьем столбцах вычислено количество пропущенных предметов и часов в октябре. Месяц выбран с помощью фильтра в верхней строке таблицы. Рисунок 6 – Сводная таблица 3 (по студентам, количеству пропусков и предметов) Вычисление значений в сводной таблице. Ранее было сказано, что вычислить для данных можно не только сумму и количество, но и среднее значение. Выбрать тип вычисления для данных можно в установке значений полей. Для этого области Значения окна настройки сводной таблицы нужно щелкнуть по полю, для которого следует изменить значение, выбрать Параметры полей значений (рис. 7) и выбрать тип вычисления, который должен быть использован в сводной таблице (рис. 8). Рисунок 7 – Изменение типа вычисления Опишем другой способ изменения параметров поля. В сводной таблице встаем в любую ячейку с данными, для которых хотим изменить параметр значений. Затем выбираем вкладку Работа со сводными таблицами – Анализ – группа Активное поле – Параметры поля и в отрывшемся окне выбрать тип вычисления, который должен быть использован в сводной таблице (рис. 8). 5 Рисунок 8 – Выбор типа вычисления На рисунке 9 показан результат изменения типа вычисления среднего количества пропущенных часов за весь отчетный период. Рисунок 9 – Сводная таблица 3 (по студентам, среднему значению пропусков и количеству предметов) 3. Построение сводных диаграмм Построим сводную диаграмму для сводной таблицы 1 (рис. 10). Для этого поместите табличный курсор в любую ячейку сводной таблицы 1 и выполните команду: − Вставка, группа Диаграммы – Сводная диаграмма или − Работа со сводными таблицами – Анализ, группа команд Сервис – Сводная диаграмма. 6 Рисунок 10 – Сводная диаграмма 1 (с группировкой) В данном примере, возможно, имеет смысл изменить тип диаграммы с группировки на накопление (рис. 11). Для этого выполните команду Работа со сводными таблицами – Конструктор, группа команд Тип – Гистограмма с накоплением. Рисунок 11 – Сводная диаграмма 1 (с накоплением) 4. Одновременное построение сводных таблиц и диаграмм Построение сводной таблицы и сводной диаграммы 4. При выполнении команды: вкладка Вставка – группа команд Диаграммы – Сводная таблица и Сводная диаграмма. Excel одновременно создаст сводную таблицу и сводную диаграмму. Алгоритм построения сводной диаграммы отличается от построения сводной таблицы названием областей: строки – ось, столбцы – Условные обозначения (рис. 12) 7 Рисунок 12 – Области построения сводной таблицы и диаграммы Результат одновременного построения диаграммы и таблицы по фамилиям и датам показан на рисунке 13. Обратите внимание, что автоматически программа произвела группировку дат по месяцам. Рисунок 13 – Сводные таблица и диаграмма 4 (по студентам и датам) 5. Фильтрация данных: срезы и временные шкалы При работе со сводными таблицами иногда требуется дополнительная гибкость, чтобы можно было быстро отфильтровать данные в небольшую часть сводной таблицы. Инструменты Срез и Временная шкала – быстрые и удобные средства для визуальной фильтрации данных. Очень удобно работать со сводной таблицей, мгновенно изменяя ее с помощью срезов и временных шкал, которые можно установить рядом с ней для каждого поля. Для вставки срезов и временных шкал поместите табличный курсор в любую ячейку сводной таблицы и выполните команду Работа со сводными таблицами – Анализ, группа команд Фильтр – Срез (Временная шкала). Результат вставки срезов по фамилии и предмету показан на рисунке 14. Срезы имеют кнопки, которые можно нажимать для фильтрации данных. В отличии от обычных фильтров они остаются видимыми и тем самым всегда можно увидеть, какие поля показаны или скрыты в отфильтрованной сводной таблице. 8 Рисунок 14 – Вставка срезов (по студентам и предметам) Результат вставки временной шкалы показан на рисунке 15. Чтобы быстрого изменения фрагмента, соответствующего определенному периоду времени, на Временной шкале следует использовать ползунок. Рисунок 15 – Вставка временной шкалы 6. Контрольные вопросы 1. Что такое сводная таблица? 2. Какой должна быть исходная таблица, на основе которой можно построить сводную таблицу? 3. Что такое данные и категории сводной таблицы? 4. Как строится сводная таблица? 5. Перечислите области в окне настройки сводной таблицы. 6. Для чего используется область «ФИЛЬТРЫ» при настройке сводной таблицы. 7. Перечислите операции, доступные для сведения данных в поле сводной таблицы. 8. Как строится сводная диаграмма? 9. Перечислите области в окне настройки сводной таблицы. 10. Как можно быстро отфильтровать данные в сводной таблице? 11. Как к сводной таблице добавить срезы и временные шкалы? 9