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

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

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

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

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

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

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

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

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

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

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

Важно:

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

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

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

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

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

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

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

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

Внимание:

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

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