Журнал

Excel для бизнеса: как строить сводные таблицы

Гайд от аналитика в LABA.

cov.excel-5f2295e59848c383161119.jpg

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

С помощью сводных таблиц можно моментально изменять способ анализа:

Работа с этими таблицами интуитивно понятна.

Рекомендуем почитать:

pr-5ef5f2343c3ad969216628.jpg

Excel для начинающих: 10 базовых функций программы

Читать

Как должны выглядеть источники данных

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

Допустим, у нас есть исходный файл с данными затрат на разные виды товаров. Они разбиты на категории, суммы затрат, даты, города, количество и другие виды данных.

На основании этих данных и будем строить сводный отчет.

Сначала убедитесь, что исходные данные правильно оформлены:

#1. Каждый столбец должен содержать единый тип данных — даты должны находиться в поле с датами и иметь формат дат. В поле «Наименование» не должно быть информации о городе покупки.

#2. Не желательно оставлять пустые строки. Сводная таблица будет построена, но визуально наличие строк «(пусто)» неприятно. Их можно убрать с помощью фильтров.

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

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

Выделите любую ячейку уже «умной» таблицы и дайте ей подходящее имя.

Как создать сводную таблицу

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

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

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

Пройдемся по пунктам:

#1. Выбор таблицы, на основании которой мы хотим построить сводный отчет.

#2. Использование внешних источников данных — это могут быть подключения через встроенную в Excel (начиная с версии 2013 года) среду Power Query или с помощью других надстроек. В нашем случае нет потребности использовать эту функцию.

#3. Выбираем лист, где будем создавать таблицу.

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

Нажимаем «ОК». Таблица создана.

Разбиваем макет на блоки и разбираемся, что где находится.

#1. «Анализ сводной таблицы». Панель инструментов, в которой можно найти дополнительные функции обработки таблицы и форматирования данных.

#2. «Конструктор». Здесь можно подобрать другой стиль таблицы или создать свой, настроить тип отображения данных, итоговые поля и другие полезные функции.

#3. Местоположение сводной таблицы.

#4. Настройки отображения списка полей таблицы.

#5. Быстрый поиск столбцов или расчетов.

#6. Столбцы и расчеты таблицы.

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

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

#9. Блок строк. Уникальные значения выбранного поля данных. Работают по принципу блока столбцов, только в крайнем левом столбце.

#10. Блок значений. Главная часть сводной таблицы, в которой выполняются расчеты путем агрегации данных.

Чтобы построить таблицу, перетащим столбцы с исходными данными в нужные нам блоки.

Обратите внимание:

#1. При перетаскивании поля «Дата» в блок столбцов, даты автоматически группируются по месяцам — в раскрывающемся списке сводной таблицы можно посмотреть значения в разрезе дат.

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

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

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

В меню «Параметры поля значений» также можно:

#3. Поиграем с фильтрами — выберем город «Киев» и категорию «Продукты питания», свернем в строках наименование товаров (правая кнопка мыши на значок «-» слева от названия подкатегории в сводной таблице).

Дополнительные функции

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

Рассмотрим, что есть на вкладке «Анализ сводной таблицы».

#1. Здесь можно дать имя сводной таблице, настроить основные параметры отображения данных и выбрать дополнительные настройки при печати.

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

Теперь при раскрытии уровня «Подкатегория» значения строк промежуточных итогов пустые, а значения свернутых — остаются.

#3. Функция группировки позволяет объединять выборочные значения в группу.

#4. Блок «Фильтр» позволяет вставлять различные срезы в таблицу и подключаться к фильтрам сводных таблиц. Создадим временную шкалу для фильтрования данных.

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

#5. Обновление данных: кнопка «Обновить» обновляет значения активной сводной таблицы, а «Обновить все» — всех таблиц, которые присутствуют в книге.

#6. В блоке «Действия» выполняются действия по перемещению и очистке таблицы (фильтров).

#7. Инструмент «Поля, элементы и наборы» позволяет создавать дополнительные вычисления в таблице.

Весь бизнес-контент в удобном формате. Интервью, кейсы, лайфхаки корп. мира — в нашем телеграм-канале. Присоединяйтесь!

#8. В блоке «Сервис» на основании данных таблицы можно построить сводную диаграмму.

#9. Блок «Показать» позволяет скрывать/отображать список полей таблицы, кнопки развертывания и заголовки.