Бюджеты составляются на верхнем уровне - доход по линейке продуктов по регионам по месяцам. Фактические данные накапливаются медленно с течением времени - счет за счетом, позиция за позицией. Сравнивать небольшой бюджетный файл с объемными фактическими данными всегда было мучительно. Мне нравится этот трюк от Роба Колли, он же PowerPivotPro.com.
Чтобы настроить пример, у вас есть таблица бюджета из 54 строк: 1 строка в месяц на регион для каждого продукта.
Файл счета-фактуры находится на уровне детализации: пока в этом году 422 строки.
В мире нет функции ВПР, которая позволила бы вам сопоставить эти два набора данных. Но благодаря Power Pivot (также известной как модель данных в Excel 2013+) это становится легко.
Вам необходимо создать крошечные столики, которые я называю «объединителями», чтобы связать два больших набора данных.
В моем случае Product, Region и Date являются общими для двух таблиц. Таблица Product - это крошечная таблица с четырьмя ячейками. То же для региона. Создайте каждый из них, скопировав данные из одной таблицы и используя команду «Удалить дубликаты».
Календарную таблицу справа было сложнее создать. Данные бюджета имеют одну строку за месяц, всегда приходящуюся на конец месяца. Данные счета показывают ежедневные даты, обычно будние дни. Итак, мне пришлось скопировать поле даты из обоих наборов данных в один столбец, а затем удалить дубликаты, чтобы убедиться, что представлены все даты. Затем я =TEXT(J4,"YYYY-MM")создал столбец «Месяц» из ежедневных дат.
Если у вас нет полной надстройки Power Pivot, вам необходимо создать сводную таблицу из таблицы бюджета и установить флажок «Добавить эти данные в модель данных».
Как обсуждалось в предыдущем совете, по мере добавления полей в сводную таблицу вам нужно будет определить шесть отношений. Хотя вы могли сделать это за шесть посещений диалогового окна «Создание взаимосвязи», я запустил надстройку Power Pivot и использовал представление диаграммы для определения шести взаимосвязей.
Вот ключ к выполнению всей этой работы: вы можете использовать числовые поля из бюджета и фактического. Но если вы хотите отобразить в сводной таблице регион, продукт или месяц, они должны быть взяты из объединяемых таблиц!
Вот сводная таблица с данными из пяти таблиц. Столбец A исходит от соединителя области. Строка 2 поступает из средства объединения календаря. Слайсер продукта взят из модуля объединения продуктов. Числа бюджета взяты из таблицы бюджета, а фактические числа взяты из таблицы счета-фактуры.
Это работает, потому что объединяющие таблицы применяют фильтры к таблицам бюджета и фактов. Это прекрасный метод, показывающий, что Power Pivot предназначен не только для больших данных.
