Бюджети складаються на найвищому рівні - дохід за товарними лініями за регіонами за місяцями. Факти з часом накопичуються повільно - рахунок-фактура, рядок-позиція. Порівняння невеликого бюджетного файлу з об’ємними Фактичними даними було болем назавжди. Мені подобається цей фокус від Роб Коллі, він же PowerPivotPro.com.
Щоб налаштувати приклад, у вас є 54-рядкова таблиця бюджету: 1 рядок на місяць для регіону на товар.
Файл рахунку-фактури знаходиться на рівні деталізації: наразі цього року 422 рядки.
У світі немає жодного VLOOKUP, який коли-небудь дозволив би вам зіставити ці два набори даних. Але завдяки Power Pivot (вона ж модель даних в Excel 2013+) це стає легко.
Вам потрібно створити крихітні маленькі таблиці, які я називаю «столярами», щоб зв’язати два більші набори даних.
У моєму випадку продукт, регіон і дата є спільними між цими двома таблицями. Таблиця Product - це крихітна таблиця з чотирьох комірок. Так само для регіону. Створіть кожен із них, скопіювавши дані з однієї таблиці та використовуючи Видалити дублікати.
Календарну таблицю праворуч насправді було складніше створювати. Дані бюджету мають один рядок на місяць, завжди припадаючи на кінець місяця. Дані рахунків-фактур відображають щоденні дати, як правило, будні. Отже, мені довелося скопіювати поле Дата з обох наборів даних в один стовпець, а потім видалити дублікати, щоб переконатися, що представлені всі дати. Потім я =TEXT(J4,"YYYY-MM")створював стовпець "Місяць" із щоденних дат.
Якщо у вас відсутній повний надбудова Power Pivot, вам потрібно створити зведену таблицю з таблиці бюджету та встановити прапорець Додати ці дані до моделі даних.
Як обговорювалося в попередній підказці, під час додавання полів у зведену таблицю вам доведеться визначити шість взаємозв’язків. Хоча ви могли зробити це за шість відвідувань діалогового вікна «Створення зв’язків», я запустив надбудову Power Pivot і використав режим діаграми для визначення шести зв’язків.
Ось ключ до виконання всієї цієї роботи: Ви можете вільно використовувати числові поля з Бюджету та Фактичного. Але якщо ви хочете показати регіон, товар або місяць у зведеній таблиці, вони повинні надходити із столярних таблиць!
Ось зведена таблиця з даними, що надходять із п’яти таблиць. Стовпець A надходить від столяра регіону. Рядок 2 надходить від столяра Календаря. Зріз продукту - від столяра продукту. Номери бюджету походять із таблиці Бюджет, а фактичні - з таблиці Рахунки.
Це працює, оскільки столярні таблиці застосовують фільтри до таблиці Бюджет та Фактична. Це прекрасна техніка, яка показує, що Power Pivot призначений не лише для великих даних.
