К плану
Урок

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

Мы переходим к одному из самых мощных инструментов аналитика. Вы уже изучили теорию бизнес-метрик и корреляцию, теперь пора применить их на практике. Сводная таблица (Pivot Table) мгновенно превращает тысячи строк «сырых» данных в понятные отчеты. Она помогает находить тренды и отвечать на вопросы бизнеса без написания сложных формул.

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

Сводная таблица работает по принципу «мусор на входе — мусор на выходе». Чтобы отчет не содержал ошибок, исходные данные должны соответствовать правилам табличного формата (Tabular Data):

  • Заголовки в первой строке. У каждой колонки должно быть уникальное название.
  • Никаких объединенных ячеек. Они ломают логику группировки данных.
  • Один тип данных в столбце. Если в колонке «Дата» появится текст «уточняется», Excel не сможет сгруппировать данные по месяцам.
  • Монолитный блок. Внутри таблицы не должно быть пустых строк или столбцов.

Чтобы анализ был динамичным, всегда преобразуйте обычный диапазон в «Умную таблицу» (сочетание клавиш Ctrl + T).

Анатомия сводной таблицы

Построение отчета — это распределение названий колонок по четырем зонам. Как показано в Схеме 1, взаимодействие этих зон определяет структуру документа.

  1. Строки (Rows): сюда перетаскивают объекты для анализа: категории товаров, имена менеджеров или города. Они отобразятся в левой части таблицы вертикально.
  2. Столбцы (Columns): подходят для сравнения параметров, например, временных периодов (годы, кварталы) или типов клиентов.
  3. Значения (Values): здесь происходят вычисления. Excel выполняет агрегацию: суммирует выручку, считает количество заказов или ищет средний чек.
  4. Фильтры (Filters): позволяют отсечь лишнее, например, вывести отчет только по одному конкретному филиалу.

Практический кейс: Анализ продаж

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

Исходные данные:

ДатаГородКатегорияВыручка
01.10.2025МоскваСмартфоны85 000
02.10.2025КазаньНоутбуки120 000

Действия:

  1. Выделите данные и нажмите Ctrl + T.
  2. Выберите «Вставка» -> «Сводная таблица».
  3. Перетащите Город в «Строки», Категория в «Столбцы», а Выручка в «Значения».

Результат: Вы получите матрицу, где на пересечении города и категории видна итоговая сумма.

Как делать не стоит: Не стройте сводную по отчету, где уже есть ручные промежуточные итоги. Excel воспримет строку «Итого» как обычную продажу и удвоит результат.

Настройка агрегации и форматов

Если в числовой колонке есть хотя бы одна пустая ячейка или текст, Excel может вместо суммы включить режим «Количество» (Count). В итоге вместо миллионов вы увидите в таблице единицы.

Чтобы это исправить, кликните правой кнопкой мыши по любому числу в сводной и выберите «Итоги по» -> «Сумма» (или «Среднее», если нужен средний показатель). 🧭

Детализация данных через Drill-down

Сводная таблица позволяет «провалиться» вглубь цифр. Если вы видите аномально высокую выручку в Казани и хотите понять, из чего она сложилась, используйте функцию Drill-down.

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

Ваше первое исследование:

  1. Возьмите любую таблицу с продажами.
  2. Превратите её в «Умную таблицу» (Ctrl + T).
  3. Создайте сводную таблицу и выясните: «Кто из сотрудников совершил наибольшее количество сделок?».
  4. Примените Drill-down к результату этого сотрудника, чтобы изучить его продажи.

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

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

Сохраните прогресс в Telegram.Продолжить в Telegram