Сводная таблица: создание и настройка полей
Содержание курса
Изменение функции агрегации и диагностика «Количества» вместо «Суммы»
По умолчанию Excel назначает функцию агрегации автоматически: для числовых полей — «Сумма», для текстовых — «Количество». Но это эвристика, и она может ошибаться.
Как изменить функцию. Есть два пути к одному диалогу:
- В панели полей щёлкнуть стрелку рядом с именем поля в области «Значения» → «Параметры поля значений».
- Правой кнопкой мыши по любой числовой ячейке в теле сводной таблицы → «Параметры поля значений».
В открывшемся окне — список функций: «Сумма», «Количество», «Среднее», «Максимум», «Минимум» и несколько других. Выбираем нужную, при желании меняем заголовок в поле «Пользовательское имя» (например, вместо «Среднее по полю Сумма» написать «Средний чек»), нажимаем ОК.
Ловушка: «Количество» для числового поля.
Если в столбце «Сумма» (который должен суммироваться) в сводной появился заголовок «Количество по полю Сумма» и в ячейках стоят маленькие целые числа — что-то пошло не так. Причина почти всегда одна: в исходном столбце есть хотя бы одна пустая ячейка или ячейка с текстом вместо числа. Excel видит неоднородный столбец и перестраховывается, выбирая «Количество».
Антипример: столбец «Сумма» содержит 299 чисел и одну ячейку с текстом «нет данных» — Excel может применить «Количество» ко всему полю; поскольку оно считает непустые значения, отчёт покажет 300 вместо денежного итога.
Порядок исправления:
- Вернуться в источник и проверить проблемные ячейки. Преобразуйте числовой текст в число; пропуск заполняйте нулём только если по смыслу он действительно означает нулевую сумму. Иначе оставьте пропуск или используйте согласованную метку, не искажая средние и другие показатели.
- Вернуться в сводную таблицу, щёлкнуть по ней правой кнопкой мыши и выбрать «Обновить», чтобы перечитать исправленные данные источника.
- Открыть «Параметры поля значений» и переключить функцию на «Сумма».
Если источник пока нельзя трогать, можно только исправить функцию через «Параметры» — но корень проблемы останется. Надёжнее чинить данные.
После выбора «Суммы» заголовок в сводной таблице обновится. Сверьте общий итог с суммой чисел в исправленном источнике при одинаковых условиях отбора. Если итоги расходятся, проверьте, что источник содержит нужные строки, суммы распознаны как числа, а сводная обновлена.
Сводная таблица: создание и настройка полей — задача по Excel