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

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

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

Сводная таблица читает данные из диапазона, у которого есть чёткая структура. Если структура нарушена, Excel либо откажется строить отчёт, либо построит его неверно. Перед вставкой стоит проверить диапазон по четырём пунктам.

Первая строка — уникальные заголовки. Каждый столбец должен иметь заголовок, и заголовки не должны повторяться. У обычного диапазона пустой заголовок может привести к ошибке «Недопустимое имя поля сводной таблицы», поэтому заполните все заголовки до вставки отчёта. Если сначала превратить диапазон в умную таблицу, Excel может создать служебное имя вроде «Столбец1», но его всё равно лучше заменить понятным.

Нет объединённых ячеек. Объединение — частая причина того, что данные «разъезжаются» при построении отчёта. Объединённую ячейку с заголовком Excel воспринимает неоднозначно; объединённые ячейки в строках данных создают пустоты.

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

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

Если диапазон оформлен как «умная таблица» (Ctrl+T), есть дополнительное преимущество: при добавлении новых строк в источник диапазон расширяется автоматически, и после команды «Обновить» сводная таблица подхватит свежие данные. Обычный статичный диапазон при росте данных придётся перепривязывать вручную.

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

Назначение сводной таблицы и роль четырёх областей

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

По сути Excel смотрит на каждую строку источника, распределяет её по нужным «корзинам» и считает итоги. Именно то, как разложены корзины, определяется четырьмя областями панели «Поля сводной таблицы».

Строки — сюда перетаскивают поле, по которому нужна вертикальная группировка. Например, поле «Категория товара» в строках даст вертикальный список категорий, и Excel подобьёт итоги по каждой из них.

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

Значения — поле, которое нужно агрегировать: сумма продаж, количество заказов, среднее время доставки. Excel применяет функцию агрегации (по умолчанию — сумма или количество) к числам, которые попали в одну «корзину».

Фильтры — поле, вынесенное над таблицей. Оно не делит отчёт на строки или столбцы, а ограничивает весь отчёт целиком: например, фильтр по «Региону» позволяет смотреть на данные только по одному региону.

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

Вставка сводной таблицы и размещение полей

Допустим, у нас есть таблица продаж с полями: «Менеджер», «Категория», «Регион», «Сумма» и «Дата». Данные чистые, заголовки уникальные — диапазон готов к работе.

Шаг 1. Щёлкнуть любую ячейку внутри данных — не нужно выделять весь диапазон вручную, Excel определит границы сам.

Шаг 2. Открыть вкладку «Вставка» → нажать кнопку «Сводная таблица».

Шаг 3. В диалоговом окне появится поле «Таблица или диапазон» — проверьте, что туда попало именно нужное: адрес вроде Продажи!$A$1:$E$300 или имя умной таблицы вроде Таблица1. Если граница захватила лишние строки или оборвалась раньше — исправьте вручную.

Шаг 4. Выбрать «Новый лист» — это безопаснее: отчёт окажется изолированно от источника. Нажать ОК.

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

Расстановка полей. Перетаскиваем «Категория» в «Строки» — в таблице появляется вертикальный список категорий. Добавляем «Регион» в «Столбцы» — каждый регион становится отдельным столбцом. Перетаскиваем «Сумма» в «Значения» — на пересечениях появляются числа. Добавляем «Менеджер» в «Фильтры» — над таблицей появляется выпадающий список; выбрав одного менеджера, мы видим только его данные.

Если теперь переместить «Регион» из «Столбцов» в «Строки» под «Категорию», структура изменится: каждая категория раскроется списком регионов. Excel пересчитает отчёт немедленно.

Важная деталь: одно поле можно поместить в «Значения» дважды. Например, «Сумма» первым экземпляром считает итог, вторым — среднее. Как именно — управляет настройка агрегации, о которой пойдёт речь дальше.

Изменение функции агрегации и диагностика «Количества» вместо «Суммы»

По умолчанию Excel назначает функцию агрегации автоматически: для числовых полей — «Сумма», для текстовых — «Количество». Но это эвристика, и она может ошибаться.

Как изменить функцию. Есть два пути к одному диалогу:

• В панели полей щёлкнуть стрелку рядом с именем поля в области «Значения» → «Параметры поля значений».
• Правой кнопкой мыши по любой числовой ячейке в теле сводной таблицы → «Параметры поля значений».

В открывшемся окне — список функций: «Сумма», «Количество», «Среднее», «Максимум», «Минимум» и несколько других. Выбираем нужную, при желании меняем заголовок в поле «Пользовательское имя» (например, вместо «Среднее по полю Сумма» написать «Средний чек»), нажимаем ОК.

Ловушка: «Количество» для числового поля.

Если в столбце «Сумма» (который должен суммироваться) в сводной появился заголовок «Количество по полю Сумма» и в ячейках стоят маленькие целые числа — что-то пошло не так. Причина почти всегда одна: в исходном столбце есть хотя бы одна пустая ячейка или ячейка с текстом вместо числа. Excel видит неоднородный столбец и перестраховывается, выбирая «Количество».

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

Исправление двухшаговое:

1. Вернуться в источник и проверить проблемные ячейки. Преобразуйте числовой текст в число; пропуск заполняйте нулём только если по смыслу он действительно означает нулевую сумму. Иначе оставьте пропуск или используйте согласованную метку, не искажая средние и другие показатели.
2. Открыть «Параметры поля значений» и переключить функцию на «Сумма».

Если источник пока нельзя трогать, можно только исправить функцию через «Параметры» — но корень проблемы останется. Надёжнее чинить данные.

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