План-факт в Excel: как соединить дневные продажи и месячный план

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

Опишите одну строку каждой таблицы

В факте строка может означать продажу или итог подразделения за день. В плане — подразделение и месяц. Запишите эти определения перед созданием связей. Прямое объединение месячного плана с каждой продажей размножает план и делает обычную сумму неверной.

Подготовьте справочник подразделений с уникальным ключом и справочник месяцев с уникальным MonthKey, например 202611. Оба справочника связываются один-ко-многим с фактами. В продажах MonthKey получают из даты, в плане он приходит из периода.

Сравнивайте на месячной оси

Загрузите Sales и Plan отдельными таблицами в модель. В сводной используйте месяц из общего справочника и подразделение из общего справочника. Тогда фильтр проходит к обеим таблицам, а каждая мера суммирует свой источник.

Меры ниже предполагают подготовленную модель. Факт — сумма продаж, план — сумма месячных значений, выполнение — их отношение. Для детализации до дня понадобится отдельное правило распределения плана; месячная строка сама не задаёт дневную норму.

Actual := SUM(Sales[Amount])
Target := SUM(Plan[Amount])
Completion := DIVIDE([Actual], [Target])

Не показывайте месячный план как дневной

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

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

Проверьте маленькую модель

В учебном примере у отдела A две продажи 40 и 60, план 120. Факт 100, выполнение 83,33%. Прямой join дал бы две строки плана и сумму 240. Второй отдел нужен, чтобы проверить работу общего справочника и отсутствие смешения данных.

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

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

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

Соберите обновляемый отчёт на практике

В платном курсе вы свяжете дневные продажи и месячный план в одной модели Power Pivot. Финальный практикум посвящён план-факту подразделений, а контрольные задания требуют проверить числа после обновления. Это позволяет отработать разную детализацию источников на полноценной рабочей книге.

Откройте программу курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».

Начните с пробного урока о рабочей книге и первом запросе Power Query.

Для следующего этапа: объединение файлов из папки и обновление отчёта.