Как изменить сводную таблицу. Создание и группировка временных рядов

В настоящей заметке представлена коллекция простых и изящных инструментов работы со сводными таблицами в Excel. То, что по-английски называется tips & tricks. Выделите время и ознакомьтесь с приводимыми здесь советами. Кто знает, может быть, вы наконец-то найдете ответ на долго мучивший вас вопрос?

Совет 1. Автоматическое обновление сводных таблиц

Иногда требуется, чтобы сводные таблицы обновлялись автоматически. Предположим, вы создали сводную таблицу для менеджера. Вряд ли вы сможете регулярно обновлять ее, разве что менеджер допустит вас к своему ноутбуку. Можно включить автоматическое обновление сводной таблицы, которое будет выполняться всякий раз при открытии книги:

Щелкните правой кнопкой мыши на сводной таблице и в контекстном меню выберите пункт Параметры сводной таблицы .
В появившемся диалоговом окне Параметры сводной таблицы выберите вкладку Данные .
Установите флажок Обновить при открытии файла .

Рис. 1. Включите опцию Обновить при открытии файла

Флажок Обновить при открытии файла следует устанавливать для каждой сводной таблицы отдельно.

Скачать заметку в формате или , примеры в формате (файл содержит код VBA).

Совет 2. Одновременное обновление всех сводных таблиц книги

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

Способ 1. Можно выбрать для каждой сводной таблицы, входящей в состав рабочей книги, настройку, задающую автоматическое обновление при открытии книги (подробнее см. Совет 1).

Способ 3. Воспользуйтесь кодом VBA для обновления всех сводных таблиц в рабочей книге по требованию. Данный подход предусматривает использование метода RefreshAll объекта Workbook. Для использования этой методики создайте новый модуль и введите следующий код:

Sub Refresh_All()

ThisWorkbook.RefreshAll

Совет 3. Сортировка элементов данных в произвольном порядке

На рис. 2 показан заданный по умолчанию порядок отображения регионов в сводной таблице. Регионы отсортированы в алфавитном порядке: Запад, Север, Средний Запад и Юг. Если ваши корпоративные правила требуют, чтобы сначала отображался регион Запад, а затем - регионы Средний Запад, Север и Юг, выполните ручную сортировку. Просто введите Средний Запад в ячейку С4 и нажмите клавишу Enter . Порядок сортировки регионов изменится.

Совет 4. Преобразование сводной таблицы в жестко заданные значения

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

Для преобразования части сводной таблицы выполните следующие действия:

Выделите копируемые данные сводной таблицы, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать (или наберите на клавиатуре Ctrl+C).
Щелкните правой кнопкой мыши в произвольном месте рабочего листа и в контекстном меню выберите команду Вставить (или наберите Ctrl+V).

Если нужно преобразовать всю сводную таблицу, выполните следующие действия:

Выделите всю сводную таблицу, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать . Если сводная не содержит область ФИЛЬТРЫ, то для выделения области сводной таблицы можно воспользоваться клавиатурным сокращением Ctrl+Shift+*.
Щелкните правой кнопкой мыши в произвольном месте листа и в контекстном меню выберите параметр Специальная вставка .
Выберите параметр Значения и щелкните ОК .

Перед преобразованием сводной таблицы целесообразно удалить промежуточные итоги, поскольку они не слишком нужны в автономном наборе данных. Чтобы удалить все промежуточные итоги пройдите по меню Конструктор -> Промежуточные итоги -> Не показывать промежуточные итоги. Для удаления конкретных промежуточных итогов щелкните правой кнопкой мыши на ячейке, в которой эти итоги вычисляются. Выберите в контекстном меню пункт Параметры поля и в диалоговом окне Параметры поля в разделе Итоги выберите переключатель Нет . После щелчка на кнопке ОК промежуточные итоги будут удалены.

Совет 5. Заполнение пустых ячеек в полях СТРОКИ

После преобразования сводной таблицы на листе отображаются не только значения, но и вся структура данных сводной таблицы. Например, данные, показанные на рис. 3, были получены на основе сводной таблицы с макетом в табличной форме.

Рис. 3. Использовать эту преобразованную сводную таблицу без заполнения пустых ячеек в левой части проблематично

Обратите внимание на то, что поля Регион и Рынок сбыта сохраняет ту же структуру строк, которая присуща при нахождении этих данных в области СТРОКИ сводной таблицы. В Excel 2013 существует быстрый способ заполнения ячеек в области СТРОКИ значениями. Кликните в области сводной таблицы, после чего пройдите по меню Конструктор -> Макет отчета -> (рис. 4). После этого можно преобразовать сводную таблицу в значения, в результате чего вы получите таблицу данных без пробелов.

Рис. 4. После применения команды Повторять все подписи элементов заполняются все пустые ячейки

Совет 6. Ранжирование числовых полей сводной таблицы

В процессе сортировки и ранжирования полей, содержащих большое количество элементов данных, не всегда легко определить числовой ранг анализируемого элемента данных. Более того, если сводная таблица будет преобразована в значения, назначенный каждому элементу данных числовой ранг, отображенный в целочисленном поле, значительно облегчит анализ созданного набора данных. Откройте сводную таблицу, подобную показанной на рис. 5. Обратите внимание на то, что один и тот же показатель - Сумма по полю Объем продаж - отображается дважды. Щелкните правой кнопкой мыши на втором экземпляре показателя и в контекстном меню выберите команду Дополнительные вычисления -> Сортировка от максимального к минимальному (рис. 6.)

После создания ранга можно настроить подписи полей и форматирование (рис. 14.9). В результате будет получен красивый ранжированный отчет.

Совет 7. Уменьшение размера отчета сводной таблицы

При формировании отчета сводной таблицы Excel создает снимок данных и сохраняет его в кеше сводной таблицы. Кеш сводной таблицы представляет собой специальную область памяти, в которой хранится копия источника данных для ускорения доступа. Другими словами, Excel создает копию данных, а затем хранит ее в кеше, связанном с рабочей книгой. Кеш сводной таблицы обеспечивает оптимизацию рабочего процесса. Любые изменения, внесенные в сводную таблицу, такие как изменение расположения полей, добавление новых полей либо сокрытие каких-либо элементов, выполняются быстрее, а требования к системным ресурсам оказываются гораздо скромнее. Основной недостаток кеша сводной таблицы заключается в том, что в результате его применения практически вдвое увеличивается размер файла рабочей книги при каждом создании сводной таблицы «с нуля».

Удаляйте исходные данные. Если рабочая книга содержит исходный набор данных и сводную таблицу, размер ее файла увеличивается вдвое. Поэтому можете спокойно удалить исходные данные, и это совершенно не отразится на функциональности вашей сводной таблицы. После удаления исходных данных не забудьте сохранить сжатую версию файла рабочей книги. После удаления исходных данных можно использовать сводную таблицу в обычном режиме. Единственная проблема заключается в невозможности обновления сводной таблицы из-за отсутствия исходных данных. Если же вам понадобятся исходные данные, щелкните дважды на пересечении строки и столбца в области общих итогов (на рис. 7 это ячейка В18). При этом Excel выгружает содержимое кеша сводных таблиц на новый рабочий лист.

Совет 8. Создание автоматически развертываемого диапазона данных

Наверняка вы не раз сталкивались с ситуациями, когда приходилось ежедневно обновлять отчеты сводных таблиц. Необходимость в этом чаще всего возникает тогда, когда в источник данных постоянно добавляются новые записи. В таких случаях придется повторно определить используемый ранее диапазон, прежде чем новые записи будут добавлены в новую сводную таблицу. Повторное определение исходного диапазона данных для сводной таблицы не представляет особого труда, но, когда этим приходится заниматься часто, подобная процедура становится весьма утомительной.

Решение проблемы заключается в том, чтобы преобразовать исходный диапазон данных в таблицу еще до создания сводной таблицы. Благодаря таблицам Excel можно создать именованный диапазон, который может автоматически расширяться либо сужаться в зависимости от объема находящихся в нем данных. Также можно связать любой компонент, диаграмму, сводную таблицу либо формулу с диапазоном, в результате чего у вас появится возможность отслеживать изменения в наборе данных.

Для реализации описанной методики выделите исходные данные, а затем щелкните на значке таблицы, находящемся на вкладке Вставка (рис. 8) или нажмите Ctrl+T (Т английское). Щелкните ОК в открывшемся окне. Обратите внимание на то, что, хотя диапазон исходных данных в сводной таблице переопределять не нужно, но при добавлении исходных данных в диапазон в сводной таблице все равно придется щелкнуть на кнопке Обновить .

Совет 9. Сравнение обычных таблиц с помощью сводной таблицы

Если вы выполняете сравнительный анализ двух различных таблиц, удобно воспользоваться сводной таблицей, что существенно сэкономит время. Предположим, имеются две таблицы, в которых отображаются сведения о заказчиках за 2011 и 2012 годы (рис. 9). Небольшие размеры этих таблиц приведены здесь исключительно в качестве примеров. На практике используются таблицы, имеющие гораздо большие размеры.

В процессе сравнения создается одна таблица, на основе которой создается сводная таблица. Убедитесь в том, что у вас имеется способ пометить данные, относящиеся к этим таблицам. В рассматриваемом примере для этого используется столбец Фискальный год (рис. 10). После объединения двух таблиц воспользуйтесь полученным комбинированным набором данных для создания новой сводной таблицы. Отформатируйте сводную таблицу таким образом, чтобы в качестве тега таблицы (идентификатор, указывающий на происхождение таблицы) использовалась область столбцов сводной таблицы. Как показано на рис. 11, годы находятся в области столбцов, а сведения о заказчиках - в области строк. В области данных содержатся объемы продаж для каждого заказчика.

Совет 10. Автоматическая фильтрация сводной таблицы

Как известно, в сводных таблицах нельзя применять автофильтры. Тем не менее существует трюк, позволяющий включить автофильтры в сводную таблицу. Принцип использования этой методики заключается в том, чтобы поместить указатель мыши справа от последнего заголовка сводной таблицы (ячейка D3 на рис. 12), а затем перейдите на ленту и выбрать команду Данные -> Фильтр . Начиная с этого момента в вашей сводной таблице появляется автофильтр! Например, вы сможете выбрать всех заказчиков с уровнем транзакций выше среднего. С помощью автофильтров в сводную таблицу добавляется дополнительный уровень аналитики.

Совет 11. Преобразование наборов данных, отображаемых в сводных таблицах

Наилучший макет для исходных данных, преобразованных в сводную таблицу, - это табличный макет. Этому виду макета присущи следующие признаки: отсутствуют пустые строки либо столбцы, каждый столбец имеет заголовок, каждому полю соответствуют значения в каждой строке, а столбцы не содержат повторяющихся групп данных. На практике часто встречаются наборы данных, напоминающие то, что показано на рис. 13. Как видите, названия месяцев отображаются в строке вдоль верхнего края таблицы, выполняя двойную функцию - подписей столбцов и фактических данных. В сводной таблице, созданной на основе подобной таблицы, это приведет к тому, что придется управлять 12 полями, каждое из которых представляет отдельный месяц.

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

Шаг 1. Объединение всех полей, не относящихся к области столбцов, в один столбец. Для создания сводных таблиц с несколькими консолидированными диапазонами следует создать единственный столбец размерности. В рассматриваемом примере все, что не относится к полю месяца, рассматривается как размерность. Поэтому поля Рынок сбыта и Описание услуги следует объединить в один столбец. Для объединения полей в один столбец просто введите формулу, которая выполняет конкатенацию этих двух полей, используя точку с запятой в качестве разделителя. Присвойте новому столбцу имя. Введенная формула отображается в строке формул (рис. 14).

Рис. 14. Результат конкатенации столбцов Рынок сбыта и Описание услуги

После создания конкатенированного столбца преобразуйте формулы в значения. Для этого выделите только что созданный столбец, нажмите Ctrl+C, после чего выполните команду Вставить -> Специальная вставка -> Значения . Теперь можно удалить столбцы Рынок сбыта и Описание услуги (рис. 15).

Рис. 15. Удалены столбцы Рынок сбыта и Описание услуги

Шаг 2. Создание сводной таблицы с несколькими диапазонами консолидации. Теперь нужно вызвать знакомый многим пользователям по предыдущим версиям Excel мастер сводных таблиц и диаграмм. Для вызова этого мастера нажмите комбинацию клавиш Alt+D+P. К сожалению, эта комбинация клавиш предназначена для англоязычной версии Excel 2013. В русскоязычной версии ей соответствует комбинация клавиш Alt+Д+Н. Но она по неизвестным мне причинам не работает. Тем не менее, можно вывести старый добрый мастер сводных таблиц на панель быстрого доступа, см. . После запуска мастера установите переключатель В нескольких диапазонах консолидации . Кликните Далее . Установите переключатель Создать поля страницы и щелкните Далее . Определите рабочий диапазон и кликните Готово (подробнее см. ). Вы создадите сводную таблицу (рис. 16).

Шаг 3. Дважды щелкните на пересечении строки и столбца в строке общих итогов. На этом этапе в вашем распоряжении окажется сводная таблица (рис. 16), включающая несколько диапазонов консолидации, которая является практически бесполезной. Выберите ячейку, находящуюся на пересечении строки и столбца общих итогов, и дважды щелкните на ней (в нашем примере это ячейка N88). Вы получите новый лист, структура которого напоминает структуру, показанную на рис. 17. Фактически этот лист представляет собой транспонированную версию исходных данных.

Шаг 4. Разбиение столбца Строка на отдельные поля. Осталось разбить столбец Строка на отдельные поля (вернуться к изначальной структуре). Добавьте один пустой столбец сразу же после столбца Строка . Выделите столбец А, а затем перейдите на вкладку ленты Данные и щелкните на кнопке Текст по столбцам . На экране появится диалоговое окно Мастер распределения текстов по столбцам . На первом шаге выберите переключатель С разделителями и щелкните на кнопке Далее. В следующем шаге выберите переключатель точка с запятой и щелкните Готово . Отформатируйте текст, добавьте заголовок и превратите исходные данные в таблицу путем нажатия Ctrl+T (рис. 18).

Рис. 18. Этот набор данных идеально подходит для создания сводной таблицы (сравните с рис. 13)

Совет 12. Включение двух числовых форматов в сводную таблицу

А теперь рассмотрим ситуацию, когда нормализованный набор данных затрудняет построение удобной для анализа сводной таблицы. Примером может служить показанная на рис. 19 таблица, которая включает два разных показателя для каждого рынка сбыта. Обратите внимание на столбец D, который идентифицирует показатель.

Несмотря на то что эта таблица может служить примером неплохого форматирования, не все так хорошо. Обратите внимание на то, что одни показатели должны отображаться в числовом формате, а другие - в процентном. Но в исходной базе данных поле Значение имеет тип Double. При создании сводной таблицы на основе набора данных невозможно присвоить два разных числовых формата одному полю Значение . Здесь действует простое правило: одно поле соответствует одному числовому формату. Попытка назначить числовой формат полю, которому был присвоен процентный формат, приведет к тому, что процентные значения превратятся в обычные числа, которые завершаются знаком процента (рис. 20).

Для решения этой проблемы применяется пользовательский числовой формат, который любое значение, большее 1,5, форматирует как число. Если же значение меньше 1,5, оно форматируется как процент. В диалоговом окне Формат ячеек выберите вкладку (все форматы) и в поле Тип введите следующую форматирующую строку (рис. 21): [>=1,5]$# ##0; [<1,5]0,0%

Рис. 21. Примените пользовательский числовой формат, в котором любые числа, меньшие 1,5, форматируются как проценты

Полученный результат показан на рис. 22. Как видите, теперь каждый показатель отформатирован корректно. Конечно, приведенный в этом совете рецепт не универсален. Скорее, он указывает направление, в котором стоит экспериментировать.

Совет 13. Создание частотного распределения для сводной таблицы

Если вы когда-либо создавали частотные распределения с помощью функции ExcelЧастота , то, наверное, знаете, что это весьма непростая задача. Более того, после изменений диапазонов данных все приходится начинать сначала. В этом разделе вы научитесь создавать простые частотные распределения с помощью обычной сводной таблицы. Вначале создайте сводную таблицу, в области строк которой находятся данные. Обратите внимание на рис. 23, где в области строк находится поле Объем продаж .

Щелкните правой кнопкой мыши на любом значении в области строк и в контекстном меню выберите параметр Группировать . В диалоговом окне Группирование (рис. 24) определите значения параметров, определяющих начало, конец и шаг частотного распределения. Щелкните ОК.

Рис. 24. В диалоговом окне Группирование настройте параметры частотного распределения

Если в сводную таблицу добавить поле Заказчик (рис. 25), получим частотное распределение транзакций заказчиков относительно размера заказов (в долларах).

Рис. 25. Теперь в вашем распоряжении оказалось распределение транзакций заказчиков в соответствии с размерами заказов (в долларах)

Преимущество описанной методики заключается в том, что фильтр отчета сводной таблицы может применяться для интерактивной фильтрации данных, основанных на других столбцах, таких как Регион и Рынок сбыта . У пользователя также имеется возможность быстрой настройки интервалов частотного распределения путем щелчка правой кнопкой мыши на любом числе в области строк с последующим выбором параметра Группировать . Для наглядности представления может быть добавлена сводная диаграмма (рис. 26).

Совет 14. Использование сводной таблицы для распределения набора данных по листам книги

Аналитикам часто приходится создавать различные отчеты сводных таблиц для каждого региона, рынка сбыта, менеджера и т.п. Выполнение этой задачи обычно подразумевает длительный процесс копирования сводной таблицы на новый лист и последующее изменение поля фильтра с учетом соответствующего региона и менеджера. Этот процесс выполняется вручную и повторяется для каждого вида анализа. Но вообще-то создание отдельных сводных таблиц можно поручить Excel. В результате применения параметра автоматически создается отдельная сводная таблица для каждого элемента, находящегося в области полей фильтра. Для использования этой функции просто создайте сводную таблицу, включающую поле фильтра (рис. 27). Поместите курсор в любом месте сводной таблицы и на вкладке Анализ в группе команд Сводная таблица щелкните на раскрывающемся списке Параметры (рис. 28). Затем щелкните на кнопке Отобразить страницы фильтра отчета .

Рис. 28. Щелкните на кнопке Отобразить страницы фильтра отчета

В появившемся диалоговом окне (рис. 29) можно выбрать поле фильтра, для которого будут созданы отдельные сводные таблицы. Выберите подходящее поле фильтра и щелкните ОК .

Рис. 29. Диалоговое окно Отображение страниц фильтра отчета

Для каждого элемента поля фильтра будет создана сводная таблица, помещенная на отдельный лист (рис. 30). Обратите внимание на то, что ярлычки листов называются так же, как и элементы поля фильтра. Учтите, что параметр Отобразить страницы фильтра может применяться к полям фильтра поочередно.

Совет 15. Использование сводной таблицы для распределения набора данных по отдельным книгам

В совете 14 мы воспользовались специальной опцией для разделения сводных таблиц по рынкам сбыта на разных листах рабочей книги. Если же вам нужно разделить исходные данные по разным рынкам сбыта в отдельных книгах, можно воспользоваться небольшим кодом VBA. Для начала поместите поле, на основе которого будет выполняться фильтрация, в область полей фильтра. Поместите поле Объем продаж в область значений (рис. 31). Предлагаемый код VBA поочередно выбирает каждый элемент ФИЛЬТРА и вызывает функцию Показать детали , создавая новый лист с данными. Затем этот лист сохраняется в новой рабочей книге

Код VBA.

Sub ExplodeTable()

Dim PvtItem As PivotItem

Dim PvtTable As PivotTable

Dim strfield As PivotField

‘Изменение переменных в соответствии со сценарием

ConststrFieldName = " Рынок сбыта " ‘<—Изменение имени поля

Const strTriggerRange = " A4 " ‘<—Изменение диапазона триггера

‘Изменение названия сводной таблицы (при необходимости)

SetPvtTable = ActiveSheet.PivotTables(" PivotTable1 ") ‘<—Изменение названия сводной

‘Циклический обход каждого элемента выделенного поля

For Each PvtItem In PvtTable.PivotFields(strFieldName).PivotItems

PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name

Range(strTriggerRange).ShowDetail = True

‘Присваивание имени временному листу

ActiveSheet.Name = " TempSheet "

‘Копирование данных в новую книгу и удаление временного листа

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _

Filename:=ThisWorkbook.Path & " \ " & PvtItem.Name & " .xlsx "

ActiveWorkbook.Close

Sheets(" Tempsheet ").Delete

Application.DisplayAlerts = True

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

Const strFieldName. Имя поля, используемого для разделения данных. Другими словами, это поле, которое помещается в область фильтра/страниц сводной таблицы.
Const strTriggerRange. Ячейка триггера, в котором хранится единственное число из области данных сводной таблицы. В нашем случае ячейкой триггера является А4 (см. рис. 31).

В результате выполнения кода VBA данные для каждого рынка сбыта будут сохранены в отдельной книге.

Заметка написана на основе книги Джелен, Александер. . Глава 14.

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

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

Структура сводной таблицы

Сводная таблица состоит из четырех областей: Фильтры, Колонны, Строки и Значения. В зависимости от того, куда вы разместите данные, внешний вид сводной таблицы будет меняться. Давайте рассмотрим функцию каждой из областей более подробно.

Область значений

В этой области происходят все расчеты исходных данных. На рисунке область значений выделена красным прямоугольником. На этом примере здесь отображены основные итоговые показатели, разбитые по федеральным округам.

Как правило, в это поле перетаскиваются данные, которые необходимо рассчитать – итоговая площадь территории, средний доход на душу населения и т.д.

Область строк

Исходные данные перенесенные в это поле, размешаются в левой части сводной таблицы и представляют из себя уникальные значения этого поля. Как правило область строк имеет хотя бы одно поле, хотя возможно его наличие без полей вовсе. На рисунке помечена желтым.

Сюда обычно помещают данные, которые необходимо сгруппировать и категорировать, например, название округа или продуктов.

Область столбцов

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

Область столбцов идеально подходит для создания матрицы данных или указания временного тренда.

Область фильтров

В верхней части сводной таблицы находится необязательная область фильтров с одним или более полем (на рисунке коричневый). В примере установлен фильтр на диапазоны доходов населения страны.

В зависимости от выбора фильтра меняется внешний вид сводной таблицы. Если вы хотите, изолировать или, наоборот, сконцентрироваться на конкретных данных, вам необходимо поместить данные в это поле.

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

Теперь, когда вы имеете представление о структуре сводной таблицы, можем приступить к ее созданию.

Вы можете скачать файл с примером

Щелкните на любой ячейке, находящейся внутри таблицы с исходными данными (те, которые вы будете использовать для создания сводной таблицы)
Перейдите к вкладке Вставка –> Таблица -> Сводная таблица, как показано на рисунке.

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

Щелкаем OK.

На данном этапе вы создали пустой отчет сводной таблицы на ново листе.

Макет сводной таблицы

Слева от пустой сводной таблицы вы увидите диалоговое окно Поля сводной таблицы , как показано на рисунке.

Вы можете добавлять необходимы поля в сводную таблицу простым перетаскиванием диапазонов с названиями в одну из четырех областей сводной таблицы – Фильтры, Колонны, Строки или Значения.

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

Теперь, прежде чем приступить к перетаскиванию полей, необходимо определиться, что мы хотим увидеть. Ответ на этот вопрос даст представление, какое поле в какую область поместить.

В нашем примере мы хотим увидеть основные показатели регионов, сгруппированных по округам. Для этого необходимо добавить поле Федеральный округ и Регион в область Строками . А поля Площадь территории, Численность населения и Денежные доходы в область Значения.

В списке с полями для добавления в отчет, ставим галочку напротив поля Федеральный округ. Теперь в области Строки и в сводной таблице появились данные поля.

В списке с полями для добавления в отчет, ставим галки напротив основных показателей

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

Мы построили простую сводную таблицу, в которой отображены основные показатели федеральных округов и нам не надо предпринимать дополнительных действия для модификации исходных данных.

Изменение сводной таблицы

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

Использование фильтров в сводной таблице

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

Обновление сводной таблицы

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

Бывают ситуации, когда структура исходных данных меняется, к примеру, вам необходимо добавить новые строки в таблицу с данными. Этот тип изменений повлияет на диапазон источника данных, и об этом необходимо сообщить сводной таблице. Простое обновление не сработает, в данном случае необходимо расширить диапазон источника данных.

Щелкаем левой кнопкой мыши в любом месте сводной таблицы. Идем во вкладку Работа со сводными таблицами -> Анализ –> Источник данных .

В появившемся диалоговом окне Изменить источник данных сводной таблицы задаем изменившийся диапазон данных.

Итог

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

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

Для создания Сводной таблицы на вкладке «Вставка » в группе «Таблицы » нажмите кнопку «Сводная таблица » (Рис.1).

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

В появившемся окне в поле «Выбрать таблицу » или автоматически выберется вся ваша таблица. Если вы хотите выбрать только часть таблицы для анализа, то нажмите кнопку в конце строки ввода и мышкой выделите нужную часть таблицы. Нужно обратить внимание на то, что верхняя строка выделенного диапазона обязательно должна содержать названия столбцов, для того, чтобы они могли в дальнейшем обрабатываться как названия полей по которым будет происходить формирования отчета Сводной таблицы. После выделения или редактирования вручную нажмите на кнопку в конце строки ввода еще раз. Сводную таблицу можно вывести на текущий лист, либо выбрать новый. Далее нажмите «OK » (Рис.2).

Рис. 2 Выбор диапазона сводной таблицы.

Теперь у вас открылся лист, в котором формируется сводная таблица. Справа от сводной таблицы находится список полей (Рис. 3). Выбирая поля, мы формируем сводную таблицу, причем порядок выбора важен.

Рис. 3 Новая сводная таблица.

В зависимости от того, какие поля будут выбраны первые, будет меняться облик сводной таблицы. Порядок выбора полей отображается внизу справа как «Название строк ».

Рис. 4 Сформированная сводная таблица.

Сводные таблицы в Excel позволяют проанализировать данные, которые находятся в одном большом диапазоне. Причем исходная таблица, которая будет использоваться для анализа, может быть создана как в Excel, так и в другой базе данных, или другом документе.

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

Теперь о требованиях, которых необходимо придерживаться при ее создании в Эксель. Исходные данные должны быть представлены в виде таблицы, у которой должно быть название для столбцов, то есть шапка. Для этих целей отлично подойдет создание умной таблицы в Excel . Также она не должна содержать пустые строки, столбцы и ячейки. Не должно быть скрытых строк/столбцов и объединенных ячеек.

Как создать

Теперь рассмотрим пример создания сводной таблицы. Предположим, у нас есть данные магазина одежды по продажам: какой продавец, какого числа, что из товаров продал и на какую сумму.

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

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

В открытой книге Excel создастся новый лист, на котором будет размещена, пока еще пустая сводная таблица.

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

Добавленные поля будут отмечены галочкой. В областях таблицы, Вы сможете менять их местами, чтобы добиться такого вида, который больше всего подойдет для анализа Ваших исходных данных.

Как расставить поля

Теперь давайте определимся, по какому принципу будем анализировать данные. Например, нужно узнать, какой именно продавец, продал какой товар в каждом месяце и на какую сумму.

Фильтровать данные по выбранному диапазону мы будем по продавцам. То есть — выберем продавца, а в таблице отобразятся проданные им товары. Зажимаем левой кнопкой мыши поле «Продавец» и перетягиваем его в область «Фильтр отчета» . Таблица изменилась, а добавленное поле теперь отмечено галочкой.

В качестве строк выберем «Товары» . Аналогичным образом перетягиваем нужное поле в область «Названия строк» .

Обратите внимание, в области можно вставлять по несколько полей. Например, в строках выберем товары и укажем цену. Товары стали выпадающими списками, в которых отображена цена. Если сначала указать цену, а потом товары, то выпадающим списком станет цена. Здесь порядок полей имеет значение.

Если бы у нас в исходной таблице был столбец «Единицы» . То данная сводная таблица показывала бы, какой продавец, в каком месяце, сколько продал единиц товара по определенной цене.

В область «Названия столбцов» перетянем поле «Дата» . Чтобы отобразить продажи не за каждый день, а, к примеру, по месяцам, кликните по любой дате правой кнопкой мыши и выберите из меню «Группировать» .

Таблица примет следующий вид.

Теперь в область «Значения» перетянем поле «Сумма» .

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

Чтобы это исправить, в сводной выделяем нужный диапазон ячеек и нажимаем правую кнопку мыши. дальше выберите из меню «Числовой формат» .

В следующем окне выбираем «Числовой» , можете поставить галочку в поле «Разделитель групп разрядов» и нажмите «ОК» .

Как работать с данными

После того, как мы учли все необходимые поля, можно начинать работать со сводной таблицей в Excel. Выбираем продавца: можете выбрать одного, нескольких или сразу всех, поставив галочку в пункте «Выделить несколько элементов» .

Также можете использовать фильтр для строк и столбцов. В примере это товары и месяцы. Например, поставив галочки в поле Костюм и Брюки, можно узнать на какую сумму их было продано всеми продавцами или конкретным продавцом.

В области «Значения» можно настроить параметры для поля. В примере выводится сумма значений: Рома в феврале продал рубашек на сумму 1 800.00. давайте посмотрим, сколько это штук. Кликаем левой кнопкой мышки по строке «Сумма по полю…» и выбираем из меню «Параметры полей значений» .

В следующем окне из списка выбираем «Количество» , выберите то, что подходит конкретно в вашем случае, и нажимаем «ОК» .

Теперь, смотря на значения, можно понять, что Рома в феврале продал две рубашки.

Теперь сделаем, чтобы общий фильтр для таблицы был по месяцам. Меняем области: в «Фильтр отчета» перетаскиваем поле «Дата» , в «Названия столбцов» – «Продавец» .

Такая сводная таблица покажет, на какую сумму, и каких, продал товаров каждый продавец за весь период или за определенный месяц.

Обратите также внимание на ленту. При выделении любой ячеек из сводной таблицы, на ней появляется вкладка «Работа со сводными таблицами» с двумя подвкладками «Параметры» и «Конструктор» .

В моей практике не раз возникала задача анализа данных ранее собранных в сводную таблицу. Допустим, что анализируя продажи, вы воспользовались исходными данными, представленными в следующем формате (табл. 1):

На основе таких данных в Excel несложно сформировать сводную таблицу, что-то типа (табл. 2):

Если же вы хотите распределить клиентов по объему продаж, чтобы получить сводную таблицу типа (табл. 3):

то вы столкнетесь с трудностями, так как создать такую таблицу на основе исходных данных (таблица 1), вам не удастся.

Скачать статью в формате примеры в формате или

Если это разовая процедура, то вы сначала создадите табл. 2, а потом на её основе создадите табл. 3. Но как быть, если вы хотите поддерживать табл. 3 в актуальном состоянии при изменении исходных данных (табл.1)!?

Мне не известны стандартные методы в Excel, позволяющие это сделать. К сожалению, опция в мастере сводных таблиц «создать таблицу на основе данных, находящихся… в другой сводной таблице», не подходит:

При использовании мастера новая сводная таблица «опирается» на те же исходные данные, что и первоначальная сводная таблица. Для удобства воспользуйтесь Excel-файлом с примером. Перейдите на лист «стандарт». На нем представлены детальные данные о продажах: номер и дата заказа, номенклатура, входящая в заказ, и количество товаров

Стандартным образом создайте сводную таблицу на основе этих данных, а затем запустите мастер сводных таблиц, выберите опцию «создать таблицу на основе данных, находящихся… в другой сводной таблице», нажмите «Далее»:

На шаге 2 выберите на основе, какой сводной таблицы вы создадите новую таблицу:

Видно, что вторая сводная «опирается» на те же данные, что и первая:

Можете поэкспериментировать, и убедиться, что набор данных второй сводной таблицы не зависит от вида первой сводной таблицы (то есть от того, какие поля и как мы выбрали в первой таблице), а зависит только от исходных данных (колонки А–D).

Фактически мы создали копию первой сводной таблицы. Так что стандартные методы Excel для решения нашей задачи не подходят. Применим маленькие хитрости.

Пример приведен в Excel-файле на листе «хитрость». Для начала создадим именованный динамический диапазон на основе исходных данных – «исх1» с использованием функции СМЕЩ (как это делать можно посмотреть ). Именованный диапазон избавит нас от проблем при добавлении исходных данных, и позволит актуализировать все сводные таблицы простым нажатием кнопки «Обновить»:

Создавая сводную таблицу, укажите, что исходные данные – это диапазон с именем «исх1»:

Для сводной таблицы отключите общие итоги:

Создайте именованный диапазон для сводной таблице, также с помощью функции СМЕЩ:

Вот зачем мы отключили итоги – чтобы они «не лезли» в этот диапазон!

Запустите мастер сводных таблиц (из панели быстрого доступа) и выберите «Создать таблицу на основе данных, находящихся… в списке или базе данных MS Excel», нажмите «Далее»:

На втором шаге, укажите диапазон, содержащий исходные данные для второй сводной таблицы – «св1», нажмите «Далее»:

Разместите вторую сводную рядом с первой:

Сгруппируйте индивидуальные значения в диапазоны, постройте сводную диаграмму:

Поэкспериментируйте с исходными данными, добавляя / удаляя строки. Достаточно последовательно обновить первую и вторую сводные таблицы, и все внесенные изменения будут учтены.