Отчёты в стиле сводных таблиц с формулами

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

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

В этом уроке Вы разместите заголовки строк, заголовки столбцов и область данных с формулами SUMIFS, которые автоматически вычисляют каждое пересечение.

Мы будем использовать лист с именем Продажи и следующими столбцами: Регион в A, Квартал в B и Сумма в C; данные занимают строки с 2 по 500.

Нужный нам отчёт выглядит так:

• Подписи строк: каждый уникальный регион в столбце E.
• Подписи столбцов: Q1, Q2, Q3, Q4 в строке 1, от F до I.
• Область данных: общая сумма для каждой пары Регион — Квартал.

Каждая ячейка области данных отвечает на один вопрос: сколько этот регион продал в данном квартале?

Заголовки строк — это уникальные регионы. Используйте UNIQUE вместе с SORT, чтобы они разливались вниз по столбцу E и оставались упорядоченными.

Поместите эту формулу в E2:

Теперь регионы самостоятельно заполняют E2 и расположенные ниже ячейки. Как и в итоговых таблицах, этот список служит опорной точкой, на которую ссылается вся сетка.

Заголовки столбцов — это кварталы, расположенные в строке. Вы можете ввести Q1, Q2, Q3, Q4 вручную или разлить их по горизонтали, используя TRANSPOSE вместе с UNIQUE.

В ячейке F1 эта формула разместит уникальные кварталы в верхней строке:

TRANSPOSE преобразует вертикальный список в горизонтальный, поэтому столбец кварталов превращается в строку заголовков. Теперь обе оси сетки готовы.

Теперь заполните область данных. Каждой ячейке нужен итог для региона из её строки и квартала из её столбца. SUMIFS легко обрабатывает два условия.

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

Формула выбирает значения Суммы, где Регион совпадает с подписью слева, а Квартал — с заголовком сверху. Это одно пересечение сводной таблицы.

Знаки доллара позволяют заполнить всю сетку копированием одной формулы. Разберите смешанные ссылки:

• $E2 фиксирует столбец E, но позволяет изменять строку, поэтому каждая строка получает свой регион.
• F$1 фиксирует строку 1, но позволяет изменять столбец, поэтому каждый столбец получает свой квартал.
• $C$2:$C$500 полностью зафиксирован, поскольку диапазон данных не должен смещаться.

Скопируйте F2 по всем кварталам и вниз по всем регионам — каждая ячейка автоматически скорректирует ссылки.

Правильно создав формулу в F2, выделите эту ячейку и перетащите маркер заполнения вправо по столбцам кварталов, а затем вниз по строкам регионов. Excel изменит относительные части ссылок за Вас.

• В ячейке G2 будут указаны регион $E2 и квартал G$1.
• В ячейке F3 будут указаны регион $E3 и квартал F$1.

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

В настоящей сводной таблице есть общие итоги. Добавьте столбец Итог справа и строку Итог внизу, используя обычную SUM для каждой строки или столбца.

Чтобы получить итог строки для первого региона, поместите эту формулу в столбец после последнего квартала:

Для итога столбца сложите ячейки области данных этого квартала по всем строкам. Такие итоги по краям делают отчёт завершённым и позволяют читателям быстро проверить корректность чисел.

Если используемое Вами приложение поддерживает эту возможность, можно не копировать формулу, а передать ссылки на разлитые диапазоны непосредственно в SUMIFS. Используйте разлитые заголовки как условия.

Эта единственная формула получает итог для каждого пересечения региона и квартала:

Здесь E2# — вертикальный список регионов, а F1# — горизонтальный список кварталов. Excel объединяет их в полную сетку одной формулой. Способ с перетаскиванием совместим с большим числом версий, но этот вариант элегантнее и современнее.

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

Если итог строки региона находится в J2, а общий итог — в J10, введите:

Фиксация общего итога с помощью $J$10 позволяет заполнить формулу для всех регионов вниз, при этом деление всегда выполняется на один и тот же знаменатель. Задайте для столбца процентный формат — и читатели сразу увидят, какие регионы занимают наибольшую долю.

Несколько правил помогут сохранить надёжность сводного отчёта на формулах:

• Используйте целые расширенные диапазоны, например строки с 2 по 500, чтобы новые строки учитывались.
• Фиксируйте диапазоны данных с помощью абсолютных якорей $; изменяться должны только ссылки на заголовки.
• Оставляйте свободное место снизу и справа, чтобы разлитым заголовкам и итогам хватало пространства.

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

Проверьте, насколько хорошо Вы освоили смешанные ссылки, на которых работает сводный отчёт на формулах.

Вы воссоздали сводную таблицу, используя только формулы:

• UNIQUE вместе с SORT создали заголовки строк в разлитом столбце.
• TRANSPOSE разместила заголовки столбцов в строке.
• SUMIFS со смешанными ссылками $E2 и F$1 заполнила каждое пересечение — перетаскиванием или с помощью ссылок на разлитые диапазоны, таких как E2# и F1#.
• SUM добавила общие итоги по краям таблицы.

Вся сетка пересчитывается автоматически. Далее Вы сделаете панель мониторинга интерактивной с помощью раскрывающихся списков, управляющих показателями.