Как сделать сводную таблицу в Excel: пошагово на примере продаж

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

Что понадобится: правильно подготовленные данные

Сводная таблица работает со списком: каждая строка — одна запись (одна продажа), каждый столбец — один признак. Проверьте перед началом:

• у каждого столбца есть заголовок, и он не повторяется;
• нет пустых столбцов и пустых строк внутри списка;
• нет объединённых ячеек (они ломают сводную);
• в столбце с числами лежат именно числа, а не текст. Текстовые числа обычно прижаты к левому краю ячейки.

Пример: столбцы «Дата», «Менеджер», «Товар», «Категория», «Количество», «Сумма». Строк может быть сколько угодно: от десятка до десятков тысяч.

Пошагово: создаём сводную таблицу

1. Встаньте в любую ячейку списка. Выделять весь диапазон не обязательно: Excel определит его сам.
2. Откройте вкладку «Вставка» и нажмите «Сводная таблица». В Excel 2016 она называется так же; в некоторых версиях в меню нужно выбрать «Из таблицы или диапазона».
3. В окне проверьте диапазон и выберите, где разместить сводную: «На новый лист» (обычно удобнее) или «На существующий лист».
4. Нажмите «ОК». Появится пустая заготовка и панель «Поля сводной таблицы» справа.
5. Перетащите поле «Менеджер» в область «Строки», а поле «Сумма» в область «Значения».

Сразу видна таблица: по каждому менеджеру сумма продаж и общий итог внизу. Если вместо суммы Excel показал количество, откройте настройки поля значений (стрелка справа от названия → «Параметры полей значений») и выберите «Сумма».

Как поменять разрез

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

• Добавьте «Категория» в «Столбцы»: получится матрица «менеджер × категория».
• Перенесите «Дата» в «Строки»: Excel сгруппирует даты по месяцам, кварталам и годам (при необходимости кликните правой кнопкой по дате и выберите «Группировать»).
• Поместите «Товар» в «Фильтры»: над таблицей появится выпадающий список, и сводная покажет только выбранный товар.
• Добавьте поле «Количество» в «Значения» второй раз, чтобы видеть рядом и сумму, и штуки.

Формат чисел меняется в тех же «Параметрах полей значений» кнопкой «Числовой формат».

Что можно считать, кроме суммы

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

Осторожность нужна с «Количеством» и «Количеством чисел»: первое считает все непустые записи, второе — только числа.

Фильтры и срезы

Кроме области «Фильтры», можно использовать срезы. Встаньте в сводную и на вкладке «Анализ сводной таблицы» нажмите «Вставить срез», отметьте поле, например «Категория». Появится окошко с кнопками: нажимаете нужную категорию, и сводная тут же перестраивается. Для нескольких значений зажмите Ctrl. Срезы удобны для показа отчёта другим людям, которым не хочется разбираться в списках.

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

Сводная не пересчитывается сама при правке исходных данных. Если вы поменяли цифру или дописали строки:

1. Встаньте в сводную таблицу.
2. На вкладке «Анализ сводной таблицы» нажмите «Обновить» (или Alt+F5).

Новые строки попадут в расчёт, только если они вошли в диапазон источника. Поэтому исходный список лучше сначала превратить в «умную» таблицу через Ctrl+T: диапазон растёт вместе с данными, и достаточно обновить сводную. Если диапазон остался старым, измените его командой «Изменить источник данных».

Если не получилось

Что видите	Что сделать
«Имя поля сводной таблицы недопустимо»	В каком-то столбце нет заголовка или в диапазоне есть пустой столбец; заполните заголовки
Итог показывает количество вместо суммы	В столбце есть текст; замените его числами и смените операцию на «Сумма»
Новые строки не видны	Нажмите «Обновить»; если не помогло, измените источник данных
Даты не группируются	В столбце есть текст вместо дат или пустые ячейки
В строках одни и те же значения по-разному	Лишние пробелы или разный регистр; почистите исходные данные

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

• Хранят в списке промежуточные итоги. Они попадают в расчёт, и сумма удваивается. Итоговых строк в исходных данных быть не должно.
• Правят числа в самой сводной таблице. Ячейки значений менять нельзя: правьте исходный список.
• Забывают обновить после исправления. Сводная показывает старое, и это можно принять за ошибку данных.
• Оставляют пустые ячейки в столбце-признаке. Они превращаются в группу «(пусто)»; лучше заполнить их заранее или удалить пустые строки.

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

Разбор примера: продажи по менеджерам и месяцам

Возьмём список из 500 строк: дата, менеджер, товар, категория, количество, сумма. Задача: понять, кто и в каком месяце продал больше всего.

1. Встаньте в список и выберите «Вставка → Сводная таблица → На новый лист».
2. В «Строки» перетащите «Менеджер», в «Столбцы» — «Дата». Excel сам сгруппирует даты по месяцам (в некоторых версиях нужно один раз щёлкнуть правой кнопкой по дате и выбрать «Группировать → Месяцы»).
3. В «Значения» перетащите «Сумма».
4. Общие итоги по строкам и столбцам появятся автоматически: они называются «Общий итог».
5. Отсортируйте по убыванию итога: щёлкните правой кнопкой по любому значению в столбце «Общий итог» и выберите «Сортировка → От максимального к минимальному».

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

Как оформить сводную

На вкладке «Конструктор» есть готовые стили и макеты. Полезны три настройки: «Макет отчёта → Показать в табличной форме» (удобнее читать и копировать), «Общие итоги» (можно отключить лишние) и «Пустые строки» (добавляют разрыв между группами). Числовой формат сумм задают через «Параметры полей значений → Числовой формат»: разделитель тысяч делает большие числа читаемыми.

Как копировать данные из сводной

Скопируйте нужную область и вставьте её через Ctrl+Alt+V с вариантом «Значения»: получится обычная таблица без связи с источником. Так удобно отправлять результат в письме. Если вставить без этого, останется сводная таблица, которая может показывать ошибки при открытии у другого человека.

Ограничения сводной таблицы

Сводная не умеет складывать значения по условиям «ИЛИ» и хуже работает с очень сложной логикой. Для таких задач нужны формулы или запросы. Зато для большинства повседневных вопросов вроде «сколько», «по каким группам», «что выросло» её хватает без единой формулы.