Открыть сервисСервис

Сводная таблица в Microsoft Excel

Сводная таблица — инструмент Microsoft Excel для интерактивного обобщения, группировки и анализа табличных данных. Позволяет автоматически выполнять агрегатные вычисления (суммы, средние, подсчёты, максимумы и минимумы) по выбранным полям без формул. Является одним из основных средств анализа данных в Excel и входит в состав надстройки Power Pivot.

Назначение

Сводная таблица применяется, когда необходимо быстро обобщить большой массив данных: продажи по регионам, расходы по статьям, результаты замеров по категориям. В отличие от формул с функциями ВПР, СУММЕСЛИМН и подобными, сводная таблица не требует написания выражений — пользователь размещает поля в области макета, а Excel выполняет вычисления автоматически.

Ключевые возможности:

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

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

Для построения сводной таблицы требуется плоская таблица (список) с заголовками в первой строке и данными ниже. Требования к исходным данным:

ТребованиеПояснение
Заголовки в первой строкеКаждый столбец должен иметь уникальное имя
Нет объединённых ячеекОбъединённые ячейки в заголовках ломают структуру
Нет пустых строк и столбцовПустые строки разбивают диапазон на части
Одна строка — одна записьДанные не должны быть «свёрнуты» в итогах
Единый формат данныхДаты — как даты, числа — как числа

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

Создание сводной таблицы

Пошаговый порядок действий в Excel 2016 и более новых версиях:

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

В старых версиях Excel (2007 и ранее) команда находилась на вкладке Данные → Сводная таблица и сводная диаграмма.

Настройка полей значений

По умолчанию Excel применяет функцию СУММА для числовых полей и СЧЁТ для текстовых. Чтобы изменить агрегат, достаточно дважды щёлкнуть по полю в области «Значения» и выбрать Параметры поля значений. Доступные функции включают:

  • Сумма, Счёт, Среднее, Максимум, Минимум;
  • Стандартное отклонение и дисперсия;
  • Количество уникальных значений (в Excel 2016 и новее);
  • Процент от итога, процент от общего итога, процент от строки/столбца.

Отдельно настраиваются формат чисел и подпись поля — например, «Сумма продаж, тыс. руб.».

Группировка данных

Щелчок правой кнопкой по значению в строке или столбце сводной таблицы даёт команду Группировать. Типичные сценарии:

  • По датам — Excel автоматически группирует по годам, кварталам, месяцам и дням;
  • По числам — создаются интервалы (биннинг), например «0–100», «101–200»;
  • По тексту — ручная группировка нескольких значений в одну категорию.

Для отмены группировки используется команда Разгруппировать.

Обновление и связь с источником

Сводная таблица хранит кэш данных. При изменении исходного диапазона пользователь должен обновить таблицу — правый щелчок → Обновить или сочетание клавиш Alt+F5. Если исходные данные расширяются за пределы исходной выделенной области, диапазон источника следует изменить через Анализ сводной таблицы → Изменить источник данных.

В Excel 2016 и новее источником может служить объект «Таблица» (Ctrl+T) — в этом случае диапазон расширяется автоматически.

Сводные графики

На основе сводной таблицы строится сводная диаграмма — команда Анализ сводной таблицы → Сводная диаграмма. Диаграмма автоматически связана с таблицей: изменение фильтров или полей макета сразу отражается на графике. Поддерживаются все стандартные типы диаграмм Excel — столбчатые, линейчатые, круговые, точечные и другие.

Срезы и таймлайны

Начиная с Excel 2010, для сводных таблиц доступны срезы — визуальные фильтры в виде кнопок, размещаемых на листе. Срезы поддерживают множественный выбор и работают с несколькими сводными таблицами одновременно. Для полей с датами применяется таймлайн — фильтр в виде ползунка с выбором периода.

Ограничения

Сводная таблица не предназначена для:

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

Вычисляемое поле создаётся через Анализ сводной таблицы → Поля, элементы и наборы → Вычисляемое поле и позволяет задать формулу в терминах полей сводной таблицы.

Историческая справка

Сводные таблицы появились в Excel 5.0 (1993 год) и изначально назывались «PivotTable» по аналогии с одноимённым инструментом Lotus 1-2-3. В русскоязычной версии Excel закрепился перевод «сводная таблица». С 2010 года в состав Office включена технология Power Pivot, расширяющая возможности сводных таблиц до работы с несколькими связанными таблицами и моделями данных.

Источники

  • Microsoft Support. Создание сводной таблицы для анализа данных листа.
  • Microsoft Support. Параметры и возможности сводных таблиц.
  • Гриценко Н. Н. Excel 2019. Полный курс. — М.: Бином, 2019.
  • Коган В. М. Microsoft Excel: полный справочник. — СПб.: Питер, 2020.
Заметили ошибку или не согласны с информацией в статье? Напишите нам support@bfometr.ru