Сводные таблицы в Excel: что это такое и как их сделать с нуля

В статье разберём всё по порядку: как подготовить данные, создать сводную таблицу шаг за шагом, настроить поля, добавить срезы, обновить источник, объединить несколько листов и разобраться с ВПР.

Что такое сводная таблица в Excel

Сводная таблица (Pivot Table) — интерактивный отчёт, который собирает данные из исходной таблицы, группирует по выбранным критериям и проводит нужные вычисления. Вручную писать формулы не нужно.

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

Три ключевых характеристики сводных таблиц Excel:

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

Кто и когда придумал сводные таблицы

В 1986 году разработчик Пито Салас работал в Lotus Development Corporation над системой управления данными. Он заметил: пользователи постоянно переставляют строки и столбцы вручную, пытаясь увидеть данные с разных сторон. Так появилась Lotus Improv — первое приложение со сводными таблицами (коммерческая версия — с 1991 года). Microsoft адаптировал идею и добавил инструмент в Excel в 1994 году. Сегодня знание сводных таблиц — обязательное требование в вакансиях уровня «продвинутый пользователь Excel» наравне с ВПР.

Для чего нужны сводные таблицы в Excel

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

Три категории задач, которые закрывают сводные таблицы:

• Формирование отчёта — превращает сырые данные в структурированную таблицу с итогами.
• Корректировка отображения — меняет разрез данных без изменения источника.
• Сложные расчёты — суммы, средние, проценты, доли — без единой ручной формулы.

Где применяются сводные таблицы: 5 сценариев

Инструмент работает в самых разных профессиональных задачах:

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

Во всех сценариях сводный отчёт строится из одних и тех же исходных данных — разные вопросы, разная структура.

Как подготовить данные для сводной таблицы

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

Правило 1 — заголовки столбцов обязательны. Каждый столбец должен иметь заголовок в первой строке. Без заголовков Excel не распознает поля и не построит конструктор.

Правило 2 — никаких пустых ячеек и строк. Пустая строка в середине диапазона обрывает данные: Excel остановится на ней и не увидит нижние записи.

Правило 3 — единый формат в столбце. Если в столбце «Сумма» окажется текстовая ячейка, поле не попадёт в область «Значения» и не просуммируется.

Подготовьте диапазон исходной таблицы один раз — дальше работайте без правок.

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

Четыре шага — от исходных данных до готового отчёта. В примере используем таблицу продаж со столбцами «Менеджер», «Товар», «Цена», «Дата».

Важное правило: не ставьте галочки напротив полей в конструкторе — Excel расставит их сам, обычно неудобно. Перетаскивайте поля вручную в нужные области.

Шаги 1–2: открыть диалог и выбрать диапазон

Шаг 1. Кликните на любую ячейку внутри таблицы данных. Перейдите на вкладку «Вставка» → нажмите кнопку «Сводная таблица». Откроется диалоговое окно создания.

Шаг 2. Excel автоматически подсветит диапазон данных — убедитесь, что выделены все строки, включая заголовки. Выберите «Новый лист» как место размещения: так исходные данные не перемешаются с результатами работы со сводной таблицей.

Шаги 3–4: настройка полей и работа с результатом

Шаг 3. Справа откроется панель конструктора с четырьмя областями. Перетащите «Менеджер» в «Строки», «Товар» — в «Столбцы», «Цена» — в «Значения». Вверху — список всех полей из исходника.

Шаг 4. Сводная таблица готова: данные сгруппированы по менеджерам и товарам, в ячейках — суммы продаж, внизу — итоговая строка. Структуру меняйте здесь же, не возвращаясь к источнику.

Как работают поля сводной таблицы

Конструктор — правая панель Excel, которая появляется при клике на любую ячейку сводной таблицы. Вверху — список полей (заголовки столбцов исходника). Снизу — четыре области для работы со сводной таблицей.

Область «Значения»: вычисления и % от суммы

Числовые поля по умолчанию суммируются, текстовые — считаются (количество записей). Тип вычисления можно изменить: правый клик на значении → «Параметры поля значений» → выбрать среднее, максимум, минимум.

Чтобы показать процент от общей суммы: правый клик → «Дополнительные вычисления» → «% от общей суммы». Удобно для расчёта доли каждого менеджера или категории товаров — без дополнительных столбцов в сводной таблице.

Строки, Столбцы и Фильтры: визуализация и управление

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

Столбцы работают аналогично по горизонтали — создают матрицу данных.

Фильтры добавляют панель над таблицей: выбираешь категорию — сводная таблица перестраивается. Строки со значком «+/–» сворачиваются для компактного вида.

Срезы и временная шкала: визуальные фильтры данных

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

Мультиселект — несколько категорий одновременно: удержите Ctrl на Windows или Cmd на macOS и нажимайте нужные кнопки. Сброс среза — клик на иконку фильтра в правом верхнем углу панели.

Временная шкала фильтрует данные по датам: «Анализ» → «Вставить временную шкалу». Гранулярность — год, квартал, месяц или день. Ограничение: минимальный выбираемый диапазон равен году, поэтому для точного поиска конкретного дня удобнее использовать стандартный фильтр в сводной таблице Excel.

Вычисляемые поля: кастомные метрики в сводной таблице

Вычисляемое поле — новый столбец внутри сводной таблицы с кастомной формулой. Исходные данные не меняются; расчёт применяется по всем строкам автоматически.

Путь: «Анализ сводной таблицы» → «Поля, элементы и наборы» → «Вычисляемое поле». В диалоге задайте имя нового столбца и формулу — используйте названия полей из списка.

Примеры:

• Премия = Цена × 0,05 — бонус менеджера с каждой сделки.
• Расходы команды = Бюджет × 0,03 — доля операционных затрат.

После нажатия «Ок» столбец появится в таблице и обновится вместе с данными при следующем обновлении источника.

Как обновить данные в сводной таблице Excel

Сводная таблица не обновляется автоматически — нужно сделать это вручную. Два сценария:

Изменились значения в исходных данных: кликните на любую ячейку сводной таблицы → «Анализ» → «Обновить». Таблица пересчитается с учётом новых данных.

Расширился диапазон (добавились новые строки или столбцы): «Анализ» → «Изменить источник данных» → выделите новый диапазон, включая добавленные данные → «Ок». Только после этого новые записи войдут в сводную таблицу Excel.

Как объединить данные из нескольких листов в одну сводную таблицу

Стандартная сводная таблица работает с одним источником. Чтобы объединить данные из нескольких листов, понадобится модель данных Excel. Четыре шага:

1. Превратите каждый лист-источник в умную таблицу: Вставка → Таблица, задайте понятное имя («Продажи», «Клиенты»).
2. При создании сводной таблицы поставьте галочку «Добавить в модель данных».
3. В конструкторе перейдите в раздел «Все» — там появятся обе умные таблицы с полями.
4. Создайте отношение по общему столбцу: «Создать отношение» → указать таблицы и связующий столбец.

Ключевое условие: общий столбец с одинаковыми значениями в обеих таблицах обязателен для связи.

ВПР и сводные таблицы в Excel: разница и совместное применение

В вакансиях уровня «продвинутый пользователь Excel» ВПР и сводные таблицы почти всегда упоминаются вместе. Это разные инструменты с разными задачами.

Синтаксис ВПР: =ВПР(искомое_значение; диапазон; номер_столбца; [тип_поиска])

Совместное применение: ВПР обогащает исходные данные, добавляя нужный столбец из другой таблицы, — после этого сводная таблица агрегирует обогащённый массив в готовый отчёт.

Сводные таблицы в Google Таблицах

GoogleТаблицы поддерживают сводные таблицы с похожим интерфейсом. Путь: «Вставка» → «Создать сводную таблицу» → выбрать диапазон и лист → откроется «Редактор сводной таблицы».

Отличия от Excel: облачный инструмент, бесплатный, без необходимости установки. Нет модели данных для объединения нескольких источников. Подходит для командной работы и быстрого анализа без лицензии Office.

Хотите разобраться с Excel на уровне аналитика данных? В рамках федерального проекта «Активные меры содействия занятости» можно пройти программу «Специалист по аналитике и базам данных в информационных системах» — 256 часов, с нуля, за счёт государства. Смотрите каталог доступных программ.

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

Что такое сводная таблица в Excel?

Инструмент анализа данных, автоматически группирующий, суммирующий и визуализирующий большие массивы без ручных формул. Создаётся через «Вставка → Сводная таблица», управляется четырьмя областями конструктора: Значения, Строки, Столбцы, Фильтры. Концепцию разработал Пито Салас в 1986 году; Microsoft добавил инструмент в Excel в 1994 году.

Для чего нужны сводные таблицы в Excel?

Для быстрого анализа больших объёмов данных: расчёта выручки по менеджерам, аналитики маркетинговых каналов, бюджетирования IT-проектов, анализа закупок. Ключевое преимущество — наглядный отчёт из тысяч строк за несколько кликов без единой ручной формулы.

Как обновить данные в сводной таблице?

Два сценария: если изменились значения — «Анализ сводной таблицы» → «Обновить». Если расширился диапазон (добавились строки или столбцы) — «Анализ» → «Изменить источник данных» → выделить новый диапазон → «Ок».

Чем ВПР отличается от сводной таблицы?

ВПР ищет конкретное значение в столбце и возвращает данные из другой ячейки — подходит для точечных запросов. Сводная таблица группирует и агрегирует массив — подходит для аналитики. Их часто применяют вместе: ВПР подготавливает данные, сводная таблица их обобщает.

Как соединить данные из двух листов Excel в одну сводную таблицу?

Четыре шага: (1) каждый лист — в умную таблицу (Вставка → Таблица); (2) при создании сводной таблицы — галочка «Добавить в модель данных»; (3) конструктор → раздел «Все» → раскрыть обе таблицы; (4) «Создать отношение» по общему столбцу.

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

«Анализ сводной таблицы» → «Вставить срез» → выбрать поле. Появится панель кнопок-категорий рядом с таблицей. Мультиселект: Ctrl (Windows) / Cmd (macOS). Сброс — клик на иконку фильтра в правом верхнем углу среза.

Какие задания помогут освоить сводные таблицы с нуля?

Задание 1 (базовый уровень): таблица продаж за месяц → сводная таблица с группировкой по менеджерам → процент каждого от общей суммы. Задание 2 (средний уровень): объединить данные двух листов через модель данных → проанализировать загрузку команд по кварталам.

Как создать вычисляемое поле в сводной таблице?

«Анализ сводной таблицы» → «Поля, элементы и наборы» → «Вычисляемое поле». В диалоге ввести имя нового столбца и формулу (например, Премия = Цена × 0,05). После нажатия «Ок» столбец добавится в таблицу и будет обновляться вместе с данными источника.

Можно ли освоить сводные таблицы бесплатно с нуля?

Базовый уровень осваивается за несколько часов самостоятельной практики по пошаговым инструкциям. Для системной подготовки с выходом на уровень аналитика данных существуют программы с государственным финансированием — без оплаты из собственного кармана.

Где научиться работать с ВПР и сводными таблицами Excel?

Оба навыка — стандарт требований в вакансиях «продвинутый пользователь Excel». Программа «Специалист по аналитике и базам данных в информационных системах» (256 ч, с нуля, государственное финансирование) покрывает Excel и инструменты аналитики. Зарплата выпускников — от 120 000 ₽.