Сводная таблица за 3 минуты с нуля

B2 ЗАЧЕМ

День формул против трёх минут мышкой

Задача: 12 000 строк продаж, нужна выручка по менеджерам в разрезе месяцев, плюс доля каждого в общем итоге. Через формулы это СУММЕСЛИМН на каждую пару «менеджер × месяц», ручной список уникальных значений и пересчёт всего при новых данных.

Сводная делает это перетаскиванием четырёх полей. И, в отличие от формул, не ломается, когда в списке появляется тринадцатый менеджер.

Ячейка B7 · руками

×Список уникальных менеджеров собирается вручную
×СУММЕСЛИМН копируется в 60 ячеек с риском сдвига диапазона
×Новый месяц — переделывать шапку
×Разрез «по регионам» = ещё один такой же отчёт

→

Ячейка C7 · сводная

✓Уникальные значения собираются сами
✓Ноль формул — четыре поля в четыре зоны
✓Новые данные → «Обновить» → готово
✓Другой разрез — перетащить поле, 5 секунд

=C7-B7 → отчёт из 2 дней превращается в 20 минут, а его переделка — в 5 секунд

B9 ПОДГОТОВКА

90% проблем со сводной — это проблемы исходной таблицы

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

Правило

Почему это важно

Одна строка = одна запись

Никаких «шапок разделов» и подытогов внутри данных — сводная посчитает их как обычные строки и удвоит суммы.

Заголовки — одна строка, без пустых

Пустой заголовок = Excel откажется строить сводную. Двухэтажная шапка превращается в кашу.

Никаких объединённых ячеек

Объединение оставляет значение только в первой ячейке, остальные пустые — категория теряется.

Числа — числами, даты — датами

Число как текст не суммируется, а дата как текст не группируется по месяцам.

Месяцы — в строки, а не в столбцы

Если у вас 12 столбцов «Янв, Фев…», это уже отчёт, а не данные. Сводной нужен столбец «Дата».

D9 · комментарий

Правило 5 — самое неочевидное. Таблица «менеджеры в строках, месяцы в столбцах» кажется удобной, но для сводной это тупик. Разворачивает такую таблицу обратно в плоский список Power Query за пару кликов — команда «Отменить свёртывание столбцов».

Сделайте из диапазона умную таблицу

Перед вставкой сводной выделите данные и нажмите Ctrl+T. Это одно действие решает главную боль: диапазон станет расширяться сам, и добавленные строки попадут в сводную после обновления. Без этого придётся каждый раз лезть в «Источник данных» и править границы.

Порядок действийA1:F12000

Ctrl+T    → умная таблица, диапазон растёт сам
Конструктор → Имя таблицы → «Продажи»  ← понятное имя вместо Таблица1
Вставка → Сводная таблица → источник: Продажи

Дальше источник никогда не придётся переуказывать — сколько бы строк ни добавилось.

B16 СБОРКА

Три минуты: от списка до отчёта

Встаньте в любую ячейку таблицыВставка → Сводная таблица → На новый лист. Excel сам подхватит границы, если это умная таблица.
Перетащите «Менеджер» в зону СтрокиСлева появится список уникальных менеджеров. Вручную его собирать больше не нужно.
Перетащите «Дата» в зону СтолбцыExcel обычно сам сворачивает даты в месяцы и кварталы. Если нет — это делается за два клика, см. ниже.
Перетащите «Сумма» в зону ЗначенияОтчёт готов. Excel по умолчанию поставит «Сумма по полю» для чисел.
Перетащите «Регион» в зону ФильтрыНаверху появится выпадающий список — теперь тот же отчёт можно смотреть по любому региону.

Зона

Что туда кладут

Строки

Категории, по которым режем: менеджер, товар, канал. Их может быть несколько — получится вложенность.

Столбцы

Второй разрез: чаще всего период. Больше двух полей сюда класть не стоит — отчёт станет нечитаемым.

Значения

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

Фильтры

Разрез «весь отчёт целиком»: год, регион, юрлицо. Для интерактивности лучше срезы — о них ниже.

Сводная — это первый шаг. Дальше начинаются дашборды.

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

Открыть доступ на 48 часов →

B24 ПРИЁМЫ

Четыре приёма, которые превращают таблицу в отчёт

1. Группировка дат по месяцам и кварталам

Правый клик по любой дате в сводной → Группировать → отметьте «Месяцы» и «Кварталы». Excel свернёт 365 дат в 12 месяцев. Если пункт неактивен — в столбце дат затесался текст или пустая ячейка.

2. Проценты вместо ручного деления

Не считайте доли формулами рядом со сводной — они слетят при первом же обновлении. Положите «Сумму» в Значения второй раз и поменяйте представление.

Правый клик по значению → Дополнительные вычисленияC5

% от общей суммы        → доля каждой строки в итоге
% от суммы по родителю  → доля внутри своей категории
Отличие                 → прирост к прошлому периоду
С нарастающим итогом    → накопление по месяцам

Всё это пересчитывается само при обновлении. Ручные формулы рядом со сводной — нет.

3. Срезы вместо выпадающих фильтров

Анализ сводной → Вставить срез. Получаются кнопки-фильтры, по которым видно текущий выбор. Один срез можно подключить сразу к нескольким сводным (Подключения к отчётам) — так собирается дашборд, где все таблицы фильтруются одной кнопкой.

4. Обновление — то, о чём забывают все

Сводная не пересчитывается сама при изменении данных. Нужно нажать Alt+F5 (обновить эту) или Ctrl+Alt+F5 (обновить все). Чтобы не забывать: правый клик по сводной → Параметры → «Обновить при открытии файла».

⚠ ЧАСТАЯ ПОТЕРЯ ВРЕМЕНИ

Если после добавления строк сводная их не видит, проверьте источник: скорее всего, данные лежат в обычном диапазоне $A$1:$F$500, а не в умной таблице. Обновление тут не поможет — диапазон физически не включает новые строки.

B32 ОШИБКИ

Шесть причин, по которым сводная «врёт»

Симптом

Причина и лечение

Вместо суммы — количество

Числа записаны как текст. Проверьте выравнивание, прогоните «Текст по столбцам» или умножьте на 1.

Даты не группируются

В столбце есть текст или пустые ячейки. Отфильтруйте столбец и найдите чужаков.

Один менеджер в двух строках

Лишние пробелы или разный регистр: «Иванов » и «Иванов». Лечится СЖПРОБЕЛЫ в исходнике.

Итог больше реального

В данные попали строки подытогов из исходного отчёта. Уберите их из источника.

Новые данные не появились

Забыли обновить или источник — фиксированный диапазон. Ctrl+T и Alt+F5.

В фильтре старые значения

Кэш сводной помнит удалённые позиции. Параметры → «Сохранять элементы, удалённые из источника» → Нет.

F19 · комментарий

Обратите внимание: пять причин из шести — не про сводную, а про качество исходных данных. Поэтому опытные пользователи тратят на подготовку таблицы больше времени, чем на сам отчёт. Отчёт-то собирается за три минуты.

Лист · FAQ ЧАСТЫЕ ВОПРОСЫ

Разворачиваете строку — читаете ответ

1Можно ли ссылаться на ячейку сводной обычной формулой?−

Можно, но Excel подставит ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — формула привяжется к содержимому, а не к адресу, и переживёт перестроение отчёта. Если нужна именно ссылка на адрес, отключите: Анализ → Параметры → снять «Создать GetPivotData».

2Сводная на 500 тысяч строк — потянет?+

Лист Excel вмещает 1 048 576 строк, так что формально да. Но файл станет тяжёлым. На таких объёмах правильнее грузить данные в модель данных (галочка «Добавить в модель данных» при создании сводной) — там работает движок Power Pivot и сжатие, а исходник на листе держать не нужно.

3Как собрать сводную сразу по нескольким файлам?+

Через Power Query: он склеит файлы из папки в одну таблицу, а сводная построится уже поверх результата. Это отдельный разбор — Power Query: собрать данные из 10 файлов.

4Почему сводная показывает «(пусто)»?+

В исходных данных есть строки с пустой категорией. Это не баг, а честный сигнал: у части записей не заполнено поле. Лечится в источнике, а не в сводной — скрывать «(пусто)» фильтром означает молча потерять эти суммы из итога.