Сводные таблицы — это инструмент отображения данных в интерактивном виде. Они позволяют перевести нескончаемые строки и колонки с данными в удобочитаемый презентабельный вид. Вы можете группировать пункты, например, объединить регионы страны по округам, фильтровать полученные результаты, изменять внешний вид и вставлять специальные формулы, которые будут выполнять новые расчеты.
Сводные таблицы получили такое название от своей возможности интерактивного перетаскивания полей, что позволяет динамически изменять внешний вид, давая вам совершенно новый ракурс, используя тот же источник данных. Обратите внимание, что при этом исходные данные сами по себе не меняются и не зависят от того, какой вид отображения вы выберете. Поэтому сводные таблицы идеально подходят для создания дашбордов.
Структура сводной таблицы
Сводная таблица состоит из четырех областей: Фильтры, Колонны, Строки и Значения. В зависимости от того, куда вы разместите данные, внешний вид сводной таблицы будет меняться. Давайте рассмотрим функцию каждой из областей более подробно.
Область значений
В этой области происходят все расчеты исходных данных. На рисунке область значений выделена красным прямоугольником. На этом примере здесь отображены основные итоговые показатели, разбитые по федеральным округам.
Как правило, в это поле перетаскиваются данные, которые необходимо рассчитать — итоговая площадь территории, средний доход на душу населения и т.д.
Область строк
Исходные данные перенесенные в это поле, размешаются в левой части сводной таблицы и представляют из себя уникальные значения этого поля. Как правило область строк имеет хотя бы одно поле, хотя возможно его наличие без полей вовсе. На рисунке помечена желтым.
Сюда обычно помещают данные, которые необходимо сгруппировать и категорировать, например, название округа или продуктов.
Область столбцов
Область столбцов содержит заголовки, которые находятся в верхней части сводной таблицы (помечено зеленым). В этом примере область столбцов содержит уникальный список основных показателей округа.
Область столбцов идеально подходит для создания матрицы данных или указания временного тренда.
Область фильтров
В верхней части сводной таблицы находится необязательная область фильтров с одним или более полем (на рисунке коричневый). В примере установлен фильтр на диапазоны доходов населения страны.
В зависимости от выбора фильтра меняется внешний вид сводной таблицы. Если вы хотите, изолировать или, наоборот, сконцентрироваться на конкретных данных, вам необходимо поместить данные в это поле.
Создание сводной таблицы
Теперь, когда вы имеете представление о структуре сводной таблицы, можем приступить к ее созданию.
Вы можете скачать файл с примером создания сводной таблицы.
- Щелкните на любой ячейке, находящейся внутри таблицы с исходными данными (те, которые вы будете использовать для создания сводной таблицы)
- Перейдите к вкладке Вставка —> Таблица -> Сводная таблица, как показано на рисунке.
- В появившемся диалоговом окне Создание сводной таблицы определяем источник данных и место, где мы хотим разместить сводную таблицу. Обратите внимание, что по умолчанию excel поместит отчет на новый лист в текущей рабочей книге. Чтобы изменить местоположение, выберите на существующий лист и укажите необходимый диапазон.
- Щелкаем OK.
На данном этапе вы создали пустой отчет сводной таблицы на ново листе.
Макет сводной таблицы
Слева от пустой сводной таблицы вы увидите диалоговое окно Поля сводной таблицы, как показано на рисунке.
Вы можете добавлять необходимы поля в сводную таблицу простым перетаскиванием диапазонов с названиями в одну из четырех областей сводной таблицы — Фильтры, Колонны, Строки или Значения.
Обратите внимание, что если диалоговое окно Поля сводной таблицы не появилось, щелкните правой кнопкой мыши на любом месте сводной таблицы и выберите Показать список полей.
Теперь, прежде чем приступить к перетаскиванию полей, необходимо определиться, что мы хотим увидеть. Ответ на этот вопрос даст представление, какое поле в какую область поместить.
В нашем примере мы хотим увидеть основные показатели регионов, сгруппированных по округам. Для этого необходимо добавить поле Федеральный округ и Регион в область Строками. А поля Площадь территории, Численность населения и Денежные доходы в область Значения.
В списке с полями для добавления в отчет, ставим галочку напротив поля Федеральный округ. Теперь в области Строки и в сводной таблице появились данные поля.
В списке с полями для добавления в отчет, ставим галки напротив основных показателей
Обратите внимание, что если мы ставим галки напротив полей с текстовыми значениями, excel по умолчанию помещает эти значения в область строк, с числовыми значениями — в область значений.
Мы построили простую сводную таблицу, в которой отображены основные показатели федеральных округов и нам не надо предпринимать дополнительных действия для модификации исходных данных.
Изменение сводной таблицы
Одним из удивительных свойств сводных таблиц является возможность добавления неограниченного количества полей для анализа. К примеру, вы хотите посмотреть территорию округа в целом и каждого региона в отдельности. Для этого щелкните в любом месте сводной таблицы, чтобы вызвать диалоговое окно Поля сводной таблицы ипереместите поле Регион в область Строки. Посмотрите как изменилась ваша таблица.
Использование фильтров в сводной таблице
Часто нам необходимо создать отчет для разных типов данных, к примеру, проанализировать только конкретные округи. Вместо того, чтобы тратить время на изменения исходных данных, воспользуемся областью фильтры. Перетащите поле Федеральный округ в область Фильтры. Теперь вы можете менять внешний вид сводной таблицы, задав фильтр на нужном округе.
Обновление сводной таблицы
Со временем исходные данные изменяются, к ним добавляются новые строки и колонки. Для обновления сводной таблицы выпользуйте командой Обновить, для этого щёлкните правой кнопкой мыши на любом месте таблицы и выберите Обновить.
Бывают ситуации, когда структура исходных данных меняется, к примеру, вам необходимо добавить новые строки в таблицу с данными. Этот тип изменений повлияет на диапазон источника данных, и об этом необходимо сообщить сводной таблице. Простое обновление не сработает, в данном случае необходимо расширить диапазон источника данных.
Щелкаем левой кнопкой мыши в любом месте сводной таблицы. Идем во вкладку Работа со сводными таблицами -> Анализ —> Источник данных.
В появившемся диалоговом окне Изменить источник данных сводной таблицы задаем изменившийся диапазон данных.
Итог
В данной статье был рассмотрен пример создания простейшей сводной таблицы, с помощью которого мы сможем производить более точную и детальную оценку необходимых параметров.
Данная возможность является одним из мощнейших инструментов excel, не забывайте про него, когда столкнетесь с необходимостью серьезного анализа.
