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

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

Что уже умеем

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

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

Что вы узнаете в рамках статьи

В этом уроке вы:

• поймёте, что такое сводная таблица и какие задачи она решает;
• создадите свою первую сводную таблицу из диапазона данных;
• разберётесь в четырёх областях редактора: строки, столбцы, значения, фильтры;
• научитесь менять функцию расчёта: сумма, среднее, количество, доля от итога;
• узнаете, как сводка обновляется и что делать с «задвоенными» строками.

Ключевые термины

• Сводная таблица — отчёт, который группирует строки исходных данных и считает по ним итоги.
• Исходные данные — таблица со строкой заголовков, на основе которой строится сводка.
• Строки и столбцы — области редактора: поля, значения которых станут строками и столбцами отчёта.
• Значения — область редактора: числовое поле, которое считается, и функция расчёта.
• Сводная функция — способ расчёта: SUM, AVERAGE, COUNT, MIN, MAX.
• Фильтр сводной таблицы — условие, сужающее набор строк, попадающих в отчёт.

Учебные данные

Занесите на лист выгрузку продаж. В работе таких строк будет много, нам для проверки хватит семи:

Обратите внимание на структуру: одна строка — одна сделка, у каждого столбца осмысленное название, пустых строк внутри нет. Это главное требование сводной таблицы. Диапазон наших данных — A1:D8.

Создаём первую сводную таблицу

Выделите A1:D8, откройте меню «Вставка» и выберите «Сводная таблица». В окне подтвердите диапазон и выберите «Новый лист» — так данные и отчёт не будут мешать друг другу. Нажмите «Создать».

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

Соберём отчёт «сколько продал каждый менеджер в каждом месяце». Нажмите «Добавить» рядом с областью «Строки» и выберите «Менеджер». Затем «Добавить» рядом с «Столбцы» и выберите «Месяц». И наконец «Добавить» рядом со «Значения», выберите «Сумма» и убедитесь, что функция расчёта стоит SUM.

[Скриншот: лист со сводной таблицей и открытый редактор справа с заполненными областями «Строки», «Столбцы», «Значения»]

Вот что получится на листе:

Сверьтесь с этими числами. У Анны в январе две сделки, 1200 и 500, что даёт 1700; Иван в феврале продал на 950 и 700, то есть 1650. Семь строк выгрузки свернулись в три строки отчёта, причём Таблицы сами нашли, что менеджеров двое и месяцев тоже два, — вручную этот список вы не задавали. Добавьте в исходные данные строку с третьим менеджером, и он появится в отчёте сам.

Как читать четыре области редактора

Логика редактора понятна, если помнить, за что отвечает каждая область.

Самое полезное здесь — свобода перестановки. Уберите «Месяц» из столбцов и поставьте туда «Регион»: тот же набор данных покажет уже не динамику, а географию продаж. Поменяйте «Менеджер» и «Регион» местами — получите отчёт, где регионы в строках, а менеджеры в столбцах. Ни одна формула не переписывается, и за эту гибкость сводные таблицы ценят.

В области «Строки» можно указать два поля сразу. Добавьте под «Менеджер» ещё и «Регион» — отчёт станет двухуровневым: под каждым менеджером появятся его регионы с подытогами. Порядок полей задаёт вложенность, как уровни в расширенной сортировке.

Меняем функцию расчёта и добавляем фильтр

По умолчанию сводная таблица считает сумму, но это лишь один из вариантов. Щёлкните по названию поля в области «Значения» и раскройте список «Суммировать по»: там доступны SUM, COUNT, COUNTA, AVERAGE, MIN, MAX.

Поставьте AVERAGE — вместо общей суммы увидите средний чек: у Анны в январе 850, у Ивана 800. Поставьте COUNT — получите количество сделок вместо денег. Это разные отчёты из одних данных, и переключение занимает щелчок.

Рядом есть ещё один список — «Показать как». Выберите «Процент от общей суммы», и вместо рублей появятся доли: доля Анны в выручке составит примерно 59,5 процента. Так удобно показывать структуру, не считая проценты отдельным столбцом. А если добавить поле «Сумма» дважды и задать первой копии SUM, а второй AVERAGE, в отчёте появятся обе метрики рядом.

Теперь фильтр. Нажмите «Добавить» рядом с областью «Фильтры», выберите «Регион» и в появившемся списке снимите галочку с «Юг». Отчёт пересчитается только по северным сделкам:

Фильтр сводной таблицы, в отличие от обычного фильтра, не скрывает строки на исходном листе — он влияет только на этот отчёт.

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

Частые ошибки

• Данные без строки заголовков. Сводная таблица не поймёт, как назвать поля, и вместо «Менеджер» вы увидите первую сделку. Держите одну строку с названиями столбцов.
• Объединённые ячейки в исходных данных. Сводка либо не создастся, либо посчитает неверно. Уберите объединения на листе с данными; оформлять красиво можно готовый отчёт.
• Задвоенные категории вроде «Анна» и «Анна ». Лишний пробел или другой регистр превращают одного менеджера в двух. Приведите текст в порядок функцией TRIM.
• Строить сводку по диапазону, где часть строк отфильтрована. Сводная берёт все строки диапазона, включая скрытые, — не удивляйтесь расхождению с тем, что на экране.

Частые вопросы

• Чем сводная таблица лучше набора формул SUMIF?
  Она не требует заранее знать список категорий и переписывать формулы при появлении новых значений, а структуру отчёта можно менять перетаскиванием полей.
• Можно ли построить диаграмму по сводной таблице?
  Да, выделите область сводки и действуйте как в прошлом уроке: «Вставка», затем «Диаграмма». График будет обновляться вместе с отчётом.
• Как убрать итоги по строкам и столбцам?
  В редакторе у каждого поля есть галочка «Показать итоги». Снимите её, если общие суммы лишние.

Итоги

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

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