Если вам интересна эта статья, то вам точно понравится наш обширный список формул в Excel
Сводные таблицы — это мощный инструмент, который позволяет оперативно составлять отчеты из тысячи, сотни тысяч и даже миллионов строк.
С помощью сводных таблиц можно моментально изменять способ анализа:
- путем быстрого перемещения и изменения полей с данными
- применением различных фильтров
- группировкой данных и построением новых метрик расчета
Работа с этими таблицами интуитивно понятна.
Как должны выглядеть источники данных
Предположим, вам нужно построить помесячные суммарные затраты по подкатегориям затрат с такими параметрами:
- возможностью детализации данных по наименованию товара
- выбором фильтров категории товаров
- выбором города приобретения
- возможностью фильтровать таблицу с помощью временной шкалы
Допустим, у нас есть исходный файл с данными затрат на разные виды товаров. Они разбиты на категории, суммы затрат, даты, города, количество и другие виды данных.
На основании этих данных и будем строить сводный отчет.
Сначала убедитесь, что исходные данные правильно оформлены:
#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. Блок «Показать» позволяет скрывать/отображать список полей таблицы, кнопки развертывания и заголовки.
Хотите получать дайджест статей?
