Сравнение бюджета и фактических данных с помощью СТЕПЕНЬ (Power) Pivot и сводных таблиц

В этой статье мы разберем, как выполнить сравнение бюджета и фактических данных в Excel, используя мощь СТЕПЕНЬ (Power) Pivot и сводных таблиц. Этот метод позволяет анализировать разнородные данные без сложных формул.

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

Ни одна функция, включая ВПР, не справится с сопоставлением таких разных наборов данных. Однако благодаря СТЕПЕНЬ (Power) Pivot (также известной как Модель данных в Excel 2013+) эта задача становится выполнимой.

Создание связующих таблиц

Ключевой шаг — создание небольших связующих таблиц ("джойнеров"), которые свяжут две основные таблицы. В нашем случае общими полями являются Продукт, Регион и Дата.

1. Создайте таблицу Продуктов, скопировав данные из одной из основных таблиц и используя инструмент Удалить дубликаты.
2. Аналогично создайте таблицу Регионов.
3. Создайте таблицу Календаря, объединив все уникальные даты из обеих основных таблиц. Для группировки по месяцам используйте функцию =ТЕКСТ(И4;"ГГГГ-ММ") (=ТЕКСТ(J4;"ГГГГ-ММ")).

Настройка сводной таблицы и связей

Если у вас нет полноценного надстройки СТЕПЕНЬ (Power) Pivot, создайте сводную таблицу из таблицы Бюджета и отметьте опцию Добавить эти данные в модель данных.

Затем необходимо определить шесть связей между таблицами. Это можно сделать через диалоговое окно Создать связь или в режиме диаграммы СТЕПЕНЬ (Power) Pivot.

Важно:

Числовые поля можно брать непосредственно из таблиц Бюджета и Фактов. Однако если вы хотите отобразить в сводной таблице Регион, Продукт или Месяц, эти поля должны браться из связующих таблиц.

Бонус: создание расчетных мер с помощью DAX

Язык DAX позволяет создавать расчетные поля (меры) прямо в сводной таблице.

1. На вкладке СТЕПЕНЬ (Power) Pivot выберите Меры → Новая мера. Или щелкните правой кнопкой мыши по имени таблицы в списке полей сводной таблицы и выберите Добавить меру.
2. Задайте имя, например, Отклонение. В формуле укажите, например, =[Сумма Выручки]-[Сумма Бюджета] (=[Сумма Выручки]-[Сумма Бюджета]). Не забудьте задать числовой формат.
3. Вы можете создавать вложенные меры. Например, Процент отклонения может использовать ранее созданную меру Отклонение: =[Отклонение]/[Сумма Бюджета] (=[Отклонение]/[Сумма Бюджета]).

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

Бонус: текст в области значений сводной таблицы

С помощью функции CONCATENATEX в DAX можно выводить текстовые значения в область данных сводной таблицы.

1. Убедитесь, что ваши данные добавлены в модель.
2. Создайте меру с формулой: =CONCATENATEX(VALUES(ТАБКД1[КОДСИМВ]);ТАБКД1[КОДСИМВ];", ") (=CONCATENATEX(VALUES(Table1[Code]);Table1[Code];", ")). Функция VALUES гарантирует отсутствие дубликатов.
3. Перетащите созданную меру в область значений.

Внимание:

Ячейка Excel не может содержать более 32 767 символов. При работе с большими наборами данных итоги, сформированные CONCATENATEX, могут превысить этот лимит. В этом случае сводная таблица перестанет обновляться без вывода ошибки. Решение — отключить итоги или изменить логику расчета.

Использование СТЕПЕНЬ (Power) Pivot для сравнения бюджета и фактических данных открывает новые возможности для анализа, выходящие далеко за рамки простого сопоставления чисел.