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

Как быстро проанализировать первичные данные с помощью традиционного офисного программного пакета

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

Павел Куратник,
заместитель главного редактора
журнала «Справочник экономиста»

Подготовка данных

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

Для создания СТ требуется диапазон исходных данных (таблица), на основе которого будет сформирована СТ. Данные листа Excel должны быть в виде списка с заголовком столбца в первой строке. Остальные строки должны содержать аналогичные объекты в одном столбце.

Данные для анализа можно загрузить с учетной программы, например 1С. В некоторых случаях их нужно несколько изменить, но в основном данные сразу готовы для анализа. Например, чтобы Excel обрабатывал дату, может понадобиться функция «ДАТАЗНАЧ», которая превращает дату, хранящуюся в виде текста, на порядковый номер, который Excel воспринимает как дату. Эта функция пригодится, если есть даты в текстовом формате, которые нужно отфильтровать, отсортировать или отформатировать в виде дат или использовать в вычислениях с датами. В некоторых случаях при обработке данных можно воспользоваться функцией «ЗНАЧЕН», которая превратит в число строку текста, отражающую число. Иногда, например при обработке документов, можно воспользоваться командой: Данные → Текст по колонкам. Кроме того, могут быть полезны такие функции, как «ПСТР», «НАЙТИ», «ЛЕВСИМВ», «ПРАВСИМВ».

Прежде чем создавать сводные таблицы в Excel 2010, необходимо преобразовать источник данных в таблицу с помощью команды: Вставка → Таблицы → Таблица. Опция «Таблица с заголовками» должна оставаться включенной. Зачем это нужно? Использование таблиц Excel значительно упрощает поддержку источника данных СТ. В таблицах Excel 2010 при добавлении столбца или строки вся новая информация автоматически попадает в СТ. Но, если СТ базируется на неотформатированном источнике данных, новые строки или столбцы могут быть не определены. Поэтому нужно будет либо каждый раз определять их вручную, либо создавать динамическую область в окне имен. Иначе СТ будет содержать неправильные данные и результаты.

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

Рассмотрим на условном примере, как быстро проанализировать данные. Например, предприятие реализует строительные материалы с четырех собственных складов в Киеве, Черкассах, Житомире и Виннице. В качестве исходной информации имеем отчет по продажам продукции за IV квартал 2012 года, содержищий данные по реализации 700 наименований товаров. Необходимо получить разнообразную итоговую информацию по городам, месяцам, товарным группам. Отчеты должны быть простыми и информативными.

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

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

В окне «Список полей» отметим поля, необходимые для создания СТ. Например, выберем «Месяц», «Группа товара», «Выручка, грн», «Склад». Поля будут автоматически добавляться в СТ, их можно перетаскивать и менять местами, при этом СТ будет меняться автоматически (см. рис. 2). В результате за несколько секунд вы получите отчет о результатах продаж по месяцам, городам и группам товаров.

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

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

Фильтрация и срезы

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

Чтобы создать срез для текущей сводной таблицы, выполните следующие команды: Параметры → Сортировка и фильтр → Вставить срез. Например, построим СТ по месяцам и складам и добавим срезы по производителям и наименованиям товаров. Выберем в срезе определенного производителя, например KNAUF. При этом в соседнем срезе увидим, как сразу же в верхнюю его часть автоматически подтянутся выделенные заливкой наименования продукции именно этого производителя. Наименования других производителей будут расположены ниже и без заливки. Если выделить одно или несколько наименований, увидим в СТ результаты их продажи по месяцам и городам (см. рис. 3).

Срез можно отформатировать. Для этого выберите «Срез». На ленте появится панель «Инструменты для среза» с вкладкой «Параметры». На этой вкладке в группе «Стили срезов» выберите стиль. Если срез больше не нужен, его можно отключить от отчета СТ или удалить.

Вычисления в сводных таблицах

Если в разделе «Значение» добавить поле, Excel автоматически определит функцию, на основе которой будут проводиться итоговые вычисления. Программа применяет в основном операцию суммирования, которая складывает все значения, имеющиеся в поле. Чтобы изменить тип вычислений, предложенный Excel, нужно в разделе «Значение» списка полей СТ нажать кнопку соответствующего поля и выбрать команду «Параметры полей значений». В диалоговом окне на вкладке «Операция» выберите функцию для вычисления итоговых значений. Выберем функцию «Количество» вместо функции «Сумма». В результате увидим, сколько раз была продана продукция фирмы KNAUF в каждом городе по месяцам. Кроме того, с помощью срезов можно выбрать другого производителя или вообще подключить другой срез и получить необходимую информацию (см. рис. 4).

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

Дополнительные вычисления

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

Поменяв местами склад и месяц, можно увидеть другую картину — каков вклад каждого месяца в каждом городе.

Если выбрать опцию «% суммы по родительской строке», итог всех строк будет равен 100 % (см. рис. 5).

Интересна также опция функции «Дополнительные вычисления» — «Отличие». Если в двух списках «Поле» и «Элемент» выбрать, например, «Месяц» и «Назад», получим СТ, в которой будет показана динамика продаж по каждой товарной группе по сравнению с предыдущим месяцем. Аналогичные результаты, но в относительных величинах можно получить, выполнив те же действия, но вместо функции «Отличие» выбрав «Приведенное отличие». Также в окне «Элементы» можно выбрать не «Назад», а любой из месяцев, тогда этот месяц будет базовым, а в остальных месяцах будут отражаться отклонения по сравнению с выбранным базовым месяцем.

В Excel 2010 опция «Дополнительные вычисления» дополнена такими функциями, как «% суммы по родительской строке», «% суммы по родительскому столбцу», «% родительской суммы», «% накопительного итога», «Сортировать от минимального к максимальному» и «Сортировать от максимального к минимальному».

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

Создание сводных диаграмм

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

1. Когда курсор находится на любой ячейке СТ, выполните следующие команды: Средства для сводных таблиц → Параметры → Сервис → Сводная диаграмма.

2. Выполните команды: Вставка → Таблицы → Сводная таблица → Сводная диаграмма.

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

Кстати, если для фильтрации добавить срезы, проведение анализа станет простым, наглядным и интересным.

Новое в сводных таблицах Excel 2013

Рассмотрим подробнее новые возможности, появившиеся в версии Excel 2013. Команды и примеры приведены для пробной версии Excel 2013, которую с середины ноября 2013 можно загрузить с русскоязычным интерфейсом.

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

После нажатия кнопки «Вставка», а затем «Рекомендуемые сводные таблицы» (Insert → Recommended PivotTables) (в скобках приведены команды для версии Excel 2013 Preview) открывается окно, в левой части которого приведены 10 образцов СТ, подходящие для заданного диапазона (см. рис. 7). Можно просмотреть каждую из них, а затем выбрать ту, которая больше всего подходит. Excel 2013 добавит сводную таблицу на новый лист и отразит список полей, с помощью которых можно изменить данные в отчете.

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

Если вы раньше использовали PowerPivot в Excel 2010, в новой версии программы вы сможете легко построить сводную таблицу по источникам данных. В Excel 2013 эта возможность появилась и в обычных СТ. Чтобы воспользоваться ею, нужно диапазоны, по которым строятся сводные таблицы, преобразовать в таблицы (Вставка → Таблица).

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

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

Рассмотрим первый вариант. Нажимаем: Вставка → Сводная таблица. В диалоговом окне «Создание сводной таблицы» в разделе «Выбрать данные для анализа» щелкните «Использовать внешний источник данных» и «Выбрать подключение».

На вкладке «Таблицы» будут доступны для выбора существующие таблицы. В нашем случае это «Table1» и «Table2». Выбрав любую из них по очереди (Открыть), нажимаем «Ок». После этого в пустом шаблоне списка полей сводной таблицы появятся две вкладки — «Активная» и «Все». Выбрав вкладку «Все», получим возможность выбирать поля из двух таблиц. Выбрав соответствующие поля, увидим необходимую нам сводную таблицу (см. рис. 8).

Второй вариант предполагает предварительное связывание простых таблиц. Конечно, диапазоны с данными должны быть отформатированы в виде таблицы. Нажав Данные → Отношения (Data → Relationship), попадаем в окно создания связей. С помощью кнопки «Создать» (New) свяжем две таблицы, например столбиком «№».

Затем выполним действия, аналогичные первому варианту: нажимаем Вставка → Сводная таблица. В диалоговом окне «Создание сводной таблицы» в разделе «Выбрать данные для анализа» щелкните «Использовать внешний источник данных» и «Выбрать подключения». На вкладке «Таблицы» будет доступна «Модель данных» (Tables in Workbook Data Model). Нажмите «Открыть» и «Ок». Дальнейшие действия аналогичны первому варианту.

Еще один способ, если вы создали простую сводную таблицу по одной таблице. Нажмите в списке полей «Другие таблицы» (More Tables). В результате появятся все доступные таблицы и, соответственно, их поля. Для правильного построения будет предложено создать связь между таблицами.

Временная шкала — это более удобный и визуальный срез. Если в данных, по которым строится сводная таблица, есть данные в формате времени, то построить временную шкалу очень легко. Этот инструмент позволяет фильтровать данные по годам, месяцам, кварталам или дням. На вкладке «Анализ» нажмите «Вставка временной шкалы» (Analyze → Insert Timeline) или Вставка → Временная шкала (Insert → Timeline) (см. рис. 9).

В сводных таблицах теперь можно быстро опускаться на уровень ниже, чтобы подробнее изучить конкретные цифры. Для этого нужно воспользоваться значком «Экспресс-просмотр» (Quick Explore), который появляется рядом с ячейками.

Для примера воспользуемся сводной таблицей, построенной на основе модели данных по группам товара и складам. Если установить курсор на неитоговое значение, строку или столбец, увидим что появляется значок с лупой «Экспресс-просмотр». Посмотрим, например, как сформировались данные по группе товара «профили для гипсокартона». Нажав на значок «Экспресс-просмотр», выбираем «Тип продукции», затем нажимаем «Детализация до» (Drill To) (см. рис. 10). В результате видим сводную таблицу по типам выбранного товара и складам, а группа товара переместится в фильтр сводной таблицы. При желании можно сверить итоговые цифры, они будут совпадать.

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

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

Построим круговую сводную диаграмму (Вставка → Сводная диаграмма), которая отражает выручку по группам товара. Выбрав одну из долей (в других типах диаграмм это может быть, например, столбец) и нажав на «Экспресс-просмотр», выберем «Детализация до группа товара». В результате получим диаграмму с отражением продаж по группам товара (см. рис. 11).

Вместо послесловия

Такой инструмент, как сводные таблицы, может быть полезен для специалистов, желающих получить итоговую информацию быстро и в наглядной форме. Причем с помощью срезов можно быстро фильтровать и выделять часть необходимых данных для анализа. Когда нужно не только анализировать данные, но и, например, планировать какие-то показатели, составлять бюджеты, возможно, вместо сводных таблиц более эффективно применять OLAP-кубы. Если необходимо проанализировать и составить отчет по большим объемам первичных данных (миллионы строк), удобно применять надстройку в Excel 2010 PowerPivot, которая быстро обрабатывает такие объемы данных (в Excel 2013 она уже установлена). Интересные результаты можно получить, построив интерактивные отчеты с помощью теперь уже стандартной надстройки в Excel 2013 Power View.

Дата подготовки
30.04.2014