Сводная таблица в Microsoft Excel¶
Сводная таблица — инструмент Microsoft Excel для интерактивного обобщения, группировки и анализа табличных данных. Позволяет автоматически выполнять агрегатные вычисления (суммы, средние, подсчёты, максимумы и минимумы) по выбранным полям без формул. Является одним из основных средств анализа данных в Excel и входит в состав надстройки Power Pivot.
¶Назначение
Сводная таблица применяется, когда необходимо быстро обобщить большой массив данных: продажи по регионам, расходы по статьям, результаты замеров по категориям. В отличие от формул с функциями ВПР, СУММЕСЛИМН и подобными, сводная таблица не требует написания выражений — пользователь размещает поля в области макета, а Excel выполняет вычисления автоматически.
Ключевые возможности:
- группировка строк и столбцов по датам, числовым диапазонам, текстовым значениям;
- переключение между видами агрегатов (сумма, количество, среднее, доля);
- фильтрация данных через раскрывающиеся списки и срезы;
- обновление при изменении исходного диапазона;
- построение сводных графиков на основе сводной таблицы.
¶Исходные данные
Для построения сводной таблицы требуется плоская таблица (список) с заголовками в первой строке и данными ниже. Требования к исходным данным:
| Требование | Пояснение |
|---|---|
| Заголовки в первой строке | Каждый столбец должен иметь уникальное имя |
| Нет объединённых ячеек | Объединённые ячейки в заголовках ломают структуру |
| Нет пустых строк и столбцов | Пустые строки разбивают диапазон на части |
| Одна строка — одна запись | Данные не должны быть «свёрнуты» в итогах |
| Единый формат данных | Даты — как даты, числа — как числа |
Если исходная таблица содержит итоговые строки, перед созданием сводной их следует удалить или вынести за пределы диапазона.
¶Создание сводной таблицы
Пошаговый порядок действий в Excel 2016 и более новых версиях:
- Выделить ячейку внутри исходной таблицы.
- Перейти на вкладку Вставка → кнопка Сводная таблица.
- В диалоговом окне подтвердить диапазон данных и выбрать место размещения — на новом листе или в указанной ячейке текущего листа.
- Нажать ОК — справа появится область полей сводной таблицы.
- Перетащить поля в четыре области макета:
- Строки — по ним происходит группировка записей;
- Столбцы — позволяют разбить показатель на категории;
- Значения — агрегируемые данные (по умолчанию — сумма для чисел, количество для текста);
- Фильтры — глобальные фильтры над всей таблицей.
В старых версиях 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.
