Бюджетите се правят на най-високо ниво - приходите по продуктова линия по региони по месеци. Действителностите се натрупват бавно с течение на времето - фактура по фактура, ред по ред. Сравняването на малкия бюджетен файл с обемните Действителни данни е било болка завинаги. Обичам този трик от Роб Коли, известен още като PowerPivotPro.com.
За да настроите примера, имате 54-редова бюджетна таблица: 1 ред на месец за регион за продукт.
Файлът на фактурата е на ниво детайли: 422 реда до момента тази година.
Няма VLOOKUP в света, който някога ще ви позволи да съвпадате с тези два набора от данни. Но благодарение на Power Pivot (известен още като Модел на данни в Excel 2013+) това става лесно.
Трябва да създадете малки малки таблици, които аз наричам „дърводелци“, за да свържат двата по-големи набора от данни.
В моя случай Product, Region и Date са общи между двете таблици. Таблицата Product е малка таблица с четири клетки. Също така за Регион. Създайте всеки от тях, като копирате данни от една таблица и използвате Премахване на дубликати.
Календарната таблица вдясно беше по-трудна за създаване. Данните за бюджета имат един ред на месец, винаги попадащ в края на месеца. Данните на фактурите показват ежедневни дати, обикновено делнични дни. Така че, трябваше да копирам полето Date от двата набора от данни в една колона и след това да премахна дубликати, за да се уверя, че всички дати са представени. След това използвах =TEXT(J4,"YYYY-MM")да създавам колона Month от дневните дати.
Ако нямате пълната добавка Power Pivot, трябва да създадете обобщена таблица от таблицата Бюджет и да поставите отметка в квадратчето за Добавяне на тези данни към модела на данни.
Както беше обсъдено в предишния съвет, докато добавяте полета към обобщената таблица, ще трябва да дефинирате шест връзки. Въпреки че можете да направите това с шест посещения в диалоговия прозорец Създаване на връзка, аз активирах добавката си Power Pivot и използвах изгледа на диаграмата, за да дефинирам шестте връзки.
Тук е ключът към всичко това: Можете свободно да използвате цифровите полета от Бюджет и от Действително. Но ако искате да покажете Регион, Продукт или Месец в обобщената таблица, те трябва да идват от дърводелските таблици!
Ето една обобщена таблица с данни, идващи от пет таблици. Колона А идва от Регионалния дърводелец. Ред 2 идва от дърводелеца на Календар. Продуктовият слайсър е от Product joiner. Бюджетните номера идват от таблицата „Бюджет“, а действителните - от таблицата „Фактура“.
Това работи, защото дърводелските таблици прилагат филтри към таблицата Бюджет и Действително. Това е красива техника и показва, че Power Pivot не е само за големи данни.
