Сводные таблицы Excel: пошаговое руководство

Перед вами список продаж на тысячи строк, и начальник спрашивает: «какой товар в каком месяце продался на какую сумму?» Можно мучиться с формулами и по одному выставлять фильтры; но в Excel есть инструмент, который отвечает на тот же вопрос за несколько кликов: сводная таблица. Она суммирует данные в любой разбивке, которую вы выберете, перетаскиванием, не удаляя и не меняя исходные данные.

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

Что такое сводная таблица и для чего она нужна?

Сводная таблица — интерактивный итоговый отчёт, который работает поверх ваших исходных данных. Она показывает вычисления, такие как сумма, среднее, количество, максимальное или минимальное значение, в разбивке по строкам и столбцам. Она не трогает исходные данные; она лишь создаёт «представление». Так из одного и того же набора данных за секунды вы получаете отчёты по регионам, по товарам или по месяцам.

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

Шаг 1: подготовьте данные для сводной таблицы

Чтобы сводная таблица работала правильно, данные должны быть аккуратными. Перед началом проверьте следующее:

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

Очень полезно выделить диапазон данных и преобразовать его в таблицу Excel с помощью Ctrl+T. Когда вы добавляете новые строки в таблицу, исходный диапазон сводной таблицы расширяется автоматически, и ручное обновление не требуется.

Шаг 2: вставьте сводную таблицу

Щёлкните любую ячейку внутри ваших данных.
На верхней вкладке Insert (Вставка) нажмите кнопку PivotTable (Сводная таблица).
В открывшемся окне проверьте, что исходный диапазон выбран правильно.
Выберите создание на новом листе; это не даст исходным данным и отчёту перемешаться.
Нажмите OK. Появятся пустая область и панель «PivotTable Fields» (Поля сводной таблицы) справа.

Шаг 3: разместите поля в нужных местах

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

Область	Что делает	Пример
Rows (Строки)	Перечисляются вертикально слева	Название товара
Columns (Столбцы)	Перечисляются рядом как заголовки вверху	Месяц или регион
Values (Значения)	Вычисленная числовая сводка	Сумма объёма продаж
Filters (Фильтры)	Выпадающий список вверху для фильтрации всей таблицы	Торговый представитель

Например, если в ваших данных о продажах есть столбцы «Товар», «Регион», «Дата» и «Сумма», перетащите «Товар» в Rows, «Регион» в Columns, а «Сумма» в Values. Таблица покажет общие продажи каждого товара по регионам. Когда вы переставляете поля, таблица мгновенно перестраивается; можно свободно экспериментировать, поскольку данные не повреждаются.

Шаг 4: измените расчёт значений

По умолчанию Excel суммирует числовые поля и считает текстовые. Чтобы это изменить, щёлкните поле в области Values и выберите Value Field Settings (Параметры полей значений). Здесь доступны, например, такие варианты:

Sum (Сумма), Count (Количество), Average (Среднее), Max (Максимум), Min (Минимум)
Show values as (Показать значения как): процент от общего итога, процент от итога по строке, разница с предыдущим периодом и подобные вычисления
Number format (Числовой формат): денежный формат, количество знаков после запятой и подобные настройки отображения

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

Шаг 5: сгруппируйте даты

Если вы хотите свести ежедневные данные по месяцам, кварталам или годам, добавьте поле даты в Rows. В большинстве версий Excel автоматически разбивает даты на год, квартал и месяц. Если автоматическая группировка не произошла, щёлкните правой кнопкой любую ячейку в столбце дат и используйте пункт Group (Группировать). Если вы выберете месяц и год, январь разных лет не будет смешиваться.

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

Шаг 6: добавьте срез и шкалу времени

Выпадающие списки в области Filters работают, но неудобны. Срезы — более наглядная и быстрая альтернатива. Щёлкнув внутри сводной таблицы, выберите Insert Slicer (Вставить срез) на вкладке PivotTable Analyze (Анализ сводной таблицы) и отметьте поле, например регион или товар. На экране появятся кнопки; при нажатии на одну таблица фильтруется по этому критерию. Можно даже собрать панель отчёта, подключив несколько сводных таблиц к одному срезу. Для полей дат можно добавить Timeline (Шкала времени) — прокручиваемый фильтр по месяцам или кварталам.

Шаг 7: сводная диаграмма и визуальное оформление

Превратить итоговую таблицу в диаграмму можно одним действием: щёлкните внутри таблицы и выберите PivotChart (Сводная диаграмма) на вкладке PivotTable Analyze. Диаграмма меняется вместе со срезами и фильтрами. Для презентаций отчётов использование одного-двух типов диаграмм и простой таблицы усиливает сообщение. Внешний вид таблицы легко поменять, выбрав готовые стили на вкладке Design (Конструктор).

Вычисляемое поле и дополнительные советы

Иногда нужен расчёт, которого нет в данных; например, есть столбцы «Сумма» и «Количество», но нет цены за единицу. Вы можете определить новое поле, выбрав Calculated Field (Вычисляемое поле) в меню Fields, Items & Sets (Поля, элементы и наборы) на вкладке PivotTable Analyze (например, Сумма / Количество). Это поле существует только в сводной таблице и не меняет исходные данные.

Ещё несколько мелочей: если добавить в Rows более одного поля, вы получите иерархический вид (регион, а под ним товары). Используйте вкладку Design (Конструктор), чтобы сворачивать и разворачивать промежуточные итоги. Чтобы ширина столбцов не сбивалась при каждом обновлении, снимите флажок «Autofit column widths on update» (Автоподбор ширины столбцов при обновлении) в PivotTable Options (Параметры сводной таблицы). В больших таблицах, чтобы увидеть десять записей с наибольшими значениями, используйте опцию «Top 10» в фильтре подписей строк.

Обновление: что делать, когда данные меняются

Сводная таблица не обновляется автоматически при изменении исходных данных. Щёлкните таблицу правой кнопкой и выберите Refresh (Обновить), или используйте сочетание Alt+F5. Чтобы обновить все сводные таблицы в книге сразу, есть кнопка Refresh All (Обновить всё). Если вы хотите автоматическое обновление при открытии файла, можно поставить флажок «Refresh data when opening the file» (Обновлять данные при открытии файла) в PivotTable Options. Если вы сделали исходный диапазон таблицей Excel, новые строки включаются в таблицу при обновлении.

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

Пустой заголовок: столбец без заголовка не даёт создать сводную таблицу.
Забытое обновление: это самая частая причина, по которой таблица показывает старые данные.
Числа, записанные как текст: если числовой столбец считается как «Count», ячейки в текстовом формате; преобразуйте их в числа.
Добавление строки итогов в исходные данные: итог будет посчитан дважды.
Ввод текста внутри сводной таблицы: не меняйте значения внутри сводной таблицы вручную; они будут потеряны при обновлении.

Какая версия Excel нужна?

Сводная таблица доступна почти во всех настольных версиях Excel. Новые функции (автоматическая группировка дат, рекомендуемые сводные таблицы и так далее) различаются от версии к версии. Версия с бессрочной лицензией может быть достаточной: Office 2024 Unlimited или Office 2021 Unlimited включают базовые инструменты анализа — сводные таблицы, срезы и шкалы времени. Если вам нужны постоянно обновляемые функции и облачная синхронизация, могут подойти Office 365 на 1 год или однопользовательский Personalized Office 365. Пользователям Mac стоит смотреть версии для Mac, например Office 2024 BIND for MacOS. Все варианты можно увидеть в категории программных лицензий; на LisansX доставка цифровая, а объём указан на странице продукта.

Часто задаваемые вопросы

В чём разница между сводной таблицей и обычной таблицей?

Обычная таблица показывает исходные данные построчно. Сводная таблица суммирует эти данные, считая, усредняя или агрегируя их; вы решаете перетаскиванием, какое поле где появится.

Меняет ли сводная таблица мои исходные данные?

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

Можно ли создать сводную таблицу из данных в другом файле?

Да. Можно создавать сводные таблицы из разных источников, настроив внешнее подключение к данным или используя модель данных. Однако в начале проще всего перенести данные в ту же книгу.