Сводная таблица в Excel — динамический инструмент, который группирует и агрегирует данные без ручных формул. Вы указываете исходный диапазон, расставляете поля по областям в панели справа — и за несколько кликов получаете структурированный отчёт из тысяч строк. Всё, что понадобится: Excel версии 2007 или новее (включая Microsoft 365) и таблица с заголовками. В этой инструкции разберём создание сводной таблицы пошагово: от подготовки данных до вычисляемых полей, срезов и связки с ВПР (функция поиска значений по справочнику).
Сводная таблица — динамический отчёт, который автоматически группирует, суммирует и визуализирует данные. Там, где формулы СУММЕСЛИ требуют отдельного выражения под каждую комбинацию условий, сводные таблицы в Excel строятся перетаскиванием полей. Результат — интерактивный отчёт, который перестраивается в один клик без изменения формул.
Сделайте следующий шаг в карьере
Подберите обучение, которое поможет освоить новые компетенции
Типичные сценарии: анализ продаж по менеджерам, ABC-анализ клиентов, сравнение маркетинговых каналов по эффективности, финансовые отчёты по периодам, HR-аналитика по отделам.
| Задача |
Без сводной (СУММЕСЛИ) |
Со сводной |
|---|---|---|
| Итоги по каждому менеджеру | Отдельная формула на каждого | Поле «Менеджер» в «Строки» |
| Разбивка по месяцам | Вложенный аргумент в формуле | Группировка одним кликом |
| Добавить новый разрез | Переписать все формулы | Перетащить поле |
| Фильтр по региону | Формула с тремя условиями | Срез или область «Фильтры» |
Сводная таблица работает ровно настолько хорошо, насколько качественны исходные данные. Пять обязательных правил:
Перед тем как сделать сводную таблицу, преобразуйте диапазон в умную таблицу: выделите данные и нажмите Ctrl+T. Умная таблица автоматически расширяет диапазон при добавлении новых строк — это снимает проблему устаревшего источника данных раз и навсегда.
Хотите уверенно работать с данными в Excel — строить сводные, писать формулы, визуализировать результаты? В ProfiFuture можно пройти обучение за 1 месяц с господдержкой — 75% стоимости компенсируется образовательной квотой. Смотрите программу Excel для работы с данными.
Ошибка 1: числа сохранены как текст — сводная считает их количество вместо суммирования. Исправление: выделить столбец → «Данные» → «Текст по столбцам» → «Готово».
Ошибка 2: несогласованные форматы дат (01.01.2024 и 2024-01-01 в одном столбце) — сводная не сможет сгруппировать по месяцам. Привести к единому формату через «Формат ячеек».
Ошибка 3: дублирующиеся записи — искажают агрегацию, суммы задваиваются.
Ошибка 4: непоследовательная терминология — «Мск» и «Москва» в одном поле. Сводная воспринимает их как два разных элемента и строит отдельные строки для каждого.
Панель «Поля сводной таблицы» — главный инструмент управления. Верхний блок отображает все заголовки из исходной таблицы; нижний разделён на четыре области: Строки, Столбцы, Значения, Фильтры. Принцип работы — перетаскиваете нужные поля в нужные области. Цепочка: подготовили данные → создали сводную → настроили области → добавили фильтры или срезы.
Кликните любую ячейку внутри исходной таблицы — это подсказывает Excel, какой диапазон использовать. Затем перейдите: «Вставка» → группа «Таблицы» → кнопка «Сводная таблица».
Если не знаете, с чего начать — используйте «Рекомендуемые таблицы» рядом с основной кнопкой. Excel предложит несколько готовых вариантов с преднастроенными полями, исходя из структуры ваших данных. Это быстрый способ понять, какие отчёты вообще можно построить.
После нажатия откроется диалоговое окно с выбором диапазона и места размещения.
Excel автоматически определяет предполагаемый диапазон — убедитесь, что он захватывает всю таблицу вместе с заголовками. При необходимости откорректируйте вручную.
Выбор места размещения:
Распространённый вопрос: обязательно ли размещать на новом листе? Нет — это вопрос удобства, а не ограничение системы. Нажмите ОК: Excel создаёт каркас сводной и открывает панель настроек справа.
Три ключевые области панели:
«Строки» — категориальные поля, которые формируют строки таблицы: Менеджер, Продукт, Регион. Вкладываете несколько полей — Excel строит иерархию с раскрывающимися уровнями.
«Столбцы» — временны́е или сравнительные поля: Месяц, Квартал, Год. Формируют кросс-табличное представление данных.
«Значения» — числовые поля для агрегации: Цена, Выручка, Количество. По умолчанию Excel суммирует числа и считает количество для текста. Чтобы изменить функцию: правый клик на значении в сводной → «Параметры поля значений» → выберите Среднее, Максимум, Минимум или другое. Там же через «Дополнительные вычисления» доступен «% от общей суммы».
Область «Фильтры» в панели создаёт выпадающее поле над сводной таблицей. По умолчанию скрыто, позволяет выбрать одно значение за раз — подходит для технических отчётов.
Срезы — визуальные кнопки прямо на листе, понятные любому, кто откроет файл. Создать: «Анализ сводной таблицы» → «Вставить срез» → отметить поля. Мультиселект — Ctrl+клик (на Mac — Cmd+клик). Сброс фильтра — иконка с воронкой рядом с заголовком среза.
Для дашбордов используйте срезы: они наглядны и не требуют объяснений. Область «Фильтры» удобнее в отчётах, где визуализация не нужна.
Если в исходных данных есть поле с датами, добавьте временну́ю шкалу: «Анализ» → «Вставить временну́ю шкалу» → выбрать поле типа «дата».
Гранулярность переключается в правом верхнем углу виджета: дни / месяцы / кварталы / годы. Выбираете период — сводная мгновенно пересчитывается.
Ограничение: для анализа конкретного дня временна́я шкала неудобна, лучше обычный фильтр. Оптимальное применение — помесячный и поквартальный анализ. Отличие от среза: шкала работает только с полями типа «дата».
Когда сводная построена, в ленте появляются две контекстные вкладки. «Анализ сводной таблицы» — центр управления: вычисляемые поля, срезы, шкалы, обновление, смена источника данных. «Конструктор» — внешний вид: стили, промежуточные итоги (сверху / снизу / выключены), макет (компактный, структурный, табличный). Там же создаётся сводная диаграмма через «Вставка» → «Диаграмма сводной таблицы» — она динамически связана со сводной и перестраивается при изменении полей.
Вычисляемое поле — формула внутри сводной, которая работает с агрегированными значениями строк. Создать: «Анализ» → «Поля, элементы и наборы» → «Вычисляемое поле» → задать имя и написать формулу.
Пример 1: поле «Премия» = Цена × 0,05 — автоматический расчёт 5% от продаж каждого менеджера без дополнительных столбцов в исходной таблице.
Пример 2: ДРР = Расходы / Выручка — доля рекламных расходов в разбивке по каналу или периоду. Полезно для маркетинговой аналитики.
Ограничение: вычисляемые поля работают только с числовыми полями. Ссылаться в формуле на другое вычисляемое поле нельзя.
Пора сменить профессию? Начните с понятного плана
Подберите программу под ваш опыт, цели и желаемый формат занятости
Группировка дат: правый клик на любой дате в области «Строки» → «Группировать» → выбрать период (месяцы, кварталы, годы). Удобно для анализа сезонности — сразу видно, какой квартал стабильно проседает.
Группировка чисел: выделить несколько элементов → «Группировать» → задать шаг. Например, разбить цены на диапазоны: 0–1 000, 1 001–5 000, 5 001+.
Условное форматирование на ячейках области «Значения»: «Главная» → цветовые шкалы или гистограммы. За секунду выделяете лидеров и аутсайдеров — визуально, без дополнительных столбцов и формул.
Эта связка нужна, когда в исходной таблице нет столбца для нужной группировки. Есть таблица продаж с кодом товара — но нет столбца «Категория». Построить сводную по категориям без этого поля невозможно.
Схема применения: ВПР подтягивает данные из справочника в исходную таблицу → обновлённая таблица с новым столбцом становится источником для сводной → сводная агрегирует данные по добавленной категории.
Синтаксис: =ВПР(A2; Справочник!$A:$B; 2; 0). Четыре аргумента: искомое значение, диапазон справочника, номер столбца с нужным результатом, точное совпадение (0).
Конкретный пример: таблица продаж содержит артикул товара, прайс — артикул и категорию. ВПР добавляет столбец «Категория» в каждую строку таблицы продаж. После этого строите сводную и анализируете выручку по категориям, а не только по артикулам.
Ограничение ВПР: не умеет искать влево от ключевого столбца — справочник должен быть организован так, чтобы ключ стоял в первом столбце. В Excel 365 эту задачу эффективнее решает функция XLOOKUP: ищет в любом направлении и не требует фиксированного порядка столбцов.
Важно различать два сценария — большинство инструкций их смешивают.
Сценарий 1: данные изменились в рамках текущего диапазона. Цифры исправили или дополнили, но таблица не расширилась за границы исходного диапазона. Достаточно: правый клик на сводной → «Обновить» (или сочетание клавиш Alt+F5).
Сценарий 2: добавлены новые строки за пределами диапазона. Диапазон, указанный при создании, новые строки не захватывает — «Обновить» не поможет. Нужно: «Анализ» → «Изменить источник данных» → выделить расширенный диапазон → ОК → затем «Обновить».
Решение навсегда: храните исходные данные в умной таблице (Ctrl+T). Диапазон расширяется автоматически при добавлении строк — достаточно только нажать «Обновить».
Дополнительно: правый клик → «Параметры сводной таблицы» → вкладка «Данные» → «Обновлять при открытии файла». Сводная будет актуальна каждый раз при открытии книги.
Если Excel недоступен, сводные таблицы есть и в Google Таблицах. Создание: «Вставка» → «Создать сводную таблицу» → выбрать диапазон и лист → «Редактор сводной таблицы» справа с теми же четырьмя областями: Строки, Столбцы, Значения, Фильтры.
Ключевые отличия от Excel: нет отдельного окна для вычисляемых полей, нет временно́й шкалы, нет стилей из «Конструктора». Зато — онлайн-коллаборация в реальном времени и бесплатный доступ без установки программ.
Вывод простой: для базового анализа и совместной работы Google Таблицы подходят. Для сложных вычислений, вычисляемых полей и профессионального оформления отчётов — Excel.
Три задания для закрепления навыка:
Задание 1 (базовый уровень): подсчитать выручку по каждому менеджеру. «Менеджер» → «Строки», «Выручка» → «Значения» (Сумма). Результат — таблица с итогами по каждому сотруднику.
Задание 2 (средний уровень): найти топ-3 продукта по регионам за конкретный год. «Продукт» → «Строки», «Регион» → «Столбцы», «Количество» → «Значения». Добавьте временну́ю шкалу для фильтрации по году.
Задание 3 (продвинутый уровень): рассчитать долю рекламных расходов по каналам. Создайте вычисляемое поле ДРР = Расходы / Выручка и разбейте результат по каналам в «Строках».
Для системного освоения Excel — от базовых таблиц до сводных, ВПР и работы с данными — в ProfiFuture есть программа Excel для работы с данными: около 1 месяца, 12 900 ₽, без профильного образования, с доступом к материалам 6 месяцев после окончания.
Для быстрого анализа больших массивов данных без написания формул: сгруппировать продажи по менеджерам, провести ABC-анализ клиентов, сравнить маркетинговые каналы по эффективности, подготовить финансовый отчёт по периодам. Один инструмент заменяет десятки формул СУММЕСЛИ. Результат — структурированный наглядный отчёт за несколько кликов.
Перетащите поле с датами в область «Строки» или «Столбцы» → правый клик на любой дате в сводной → «Группировать» → выберите «Месяцы» и при необходимости «Годы». Альтернатива: вкладка «Анализ» → «Вставить временну́ю шкалу» → переключить гранулярность на «Месяцы».
Кликните на любую ячейку сводной — в ленте появятся вкладки «Анализ сводной таблицы» и «Конструктор». Основные параметры: правый клик на сводной → «Параметры сводной таблицы». Там настраивают автообновление, формат пустых ячеек и макет отчёта.
Три способа: 1) Power Query — «Данные» → «Получить данные» → объединить запросы; 2) модель данных — при создании сводной включите «Добавить эти данные в модель данных»; 3) Мастер сводных таблиц (Alt+D+P) для консолидации нескольких диапазонов. Выбор зависит от версии Excel и сложности задачи.
Если данные изменились в рамках диапазона — нажмите Alt+F5 или правый клик → «Обновить». Если добавлены новые строки — «Анализ» → «Изменить источник данных» → расширить диапазон. Чтобы избежать этой проблемы навсегда: храните исходные данные в умной таблице (Ctrl+T).
Да. В диалоговом окне создания выберите «На существующем листе» и укажите ячейку для размещения. Рекомендуется новый лист — так исходные данные и сводная не перекрываются и легче переключаться между ними. Это не ограничение, а вопрос удобства.
Правый клик на значении в области «Значения» → «Дополнительные вычисления» → «% от общей суммы». Каждое значение пересчитается как доля от общего итога. Аналогично доступны «% от итога строки» и «% от итога столбца» для более гибкого анализа.
Область «Фильтры» — текстовое поле над таблицей, один выбор за раз, скрыто по умолчанию. Срезы — визуальные кнопки на листе, поддерживают мультиселект (Ctrl+клик) и подходят для интерактивных дашбордов. Временна́я шкала — специализированный срез исключительно для полей с датами.
На YouTube есть бесплатные видеоуроки — ищите «сводные таблицы Excel с нуля». Для системного освоения с практическими заданиями и обратной связью подойдёт онлайн-курс Excel для работы с данными: около 1 месяца, 12 900 ₽, без профильного образования, с доступом к материалам 6 месяцев.
Если в исходных данных нет нужного столбца, добавьте его через ВПР: =ВПР(A2; Справочник!$A:$B; 2; 0). ВПР извлечёт значение из справочной таблицы и добавит в каждую строку исходных данных. После этого стройте сводную уже по обогащённой таблице с новым полем.
Превратите интерес к теме в новую профессию
Оставьте заявку — поможем выбрать программу и расскажем об условиях обучения
Оставить заявку
