Сводные таблицы в Excel: как сделать пошагово и что с ними можно делать

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

Шаг 1. Подготовьте данные

Сводная таблица строится из обычного списка. Чтобы она работала без сюрпризов, проверьте три вещи:

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

Удобнее всего превратить список в «умную» таблицу: выделите любую ячейку и нажмите Ctrl+T. Тогда новые строки сами попадут в сводную после обновления.

Шаг 2. Создайте сводную таблицу

1. Встаньте в любую ячейку списка.
2. Выберите «Вставка → Сводная таблица».
3. Excel сам определит диапазон. Выберите «На новый лист» и нажмите ОК.

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

Шаг 3. Разложите поля

В нижней части панели четыре области:

• Строки: по чему группируем вниз. Например, «Менеджер».
• Столбцы: по чему разбиваем вправо. Например, «Город».
• Значения: что считаем. Например, «Сумма заказа». Числа по умолчанию суммируются.
• Фильтры: что отбираем для всего отчёта. Например, «Канал продаж».

Перетащите «Менеджер» в Строки, «Сумма заказа» в Значения, и вы получите продажи по каждому менеджеру с общим итогом внизу.

Шаг 4. Настройте расчёт

• Вместо суммы количество или среднее. Щёлкните по полю в области «Значения» → «Параметры полей значений» → выберите «Количество» или «Среднее».
• Доля от итога. Там же, на вкладке «Дополнительные вычисления», выберите «% от общей суммы».
• Сортировка. Щёлкните правой кнопкой по любому значению → «Сортировка → по убыванию», и лидеры окажутся сверху.

Шаг 5. Сгруппируйте даты

Если поле с датой положить в Строки, Excel может вывести каждую дату отдельно. Щёлкните правой кнопкой по любой дате в сводной → «Группировать» → отметьте «Месяцы» и «Годы». Получится отчёт по месяцам без лишних формул.

Шаг 6. Обновите после изменения данных

Сводная таблица не пересчитывается сама. Когда данные поменялись, щёлкните по ней правой кнопкой → «Обновить» или нажмите Alt+F5. Если исходник был «умной» таблицей (Ctrl+T), новые строки тоже подтянутся.

Частые проблемы

• Суммы считаются как «Количество». В столбце с суммами есть текст или пустые ячейки. Найдите их и исправьте, затем обновите сводную.
• Не группируются даты. Часть дат записана текстом. Приведите столбец к формату даты.
• В отчёте «(пусто)». В исходных данных есть пустые ячейки в поле, которое стоит в Строках. Заполните их или отфильтруйте «(пусто)».

Что дальше

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

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