Budgetar görs på den högsta nivån - intäkter per produktlinje efter region per månad. Fakta ackumuleras långsamt över tiden - faktura för faktura, rad för rad. Att jämföra den lilla budgetfilen med de omfattande faktiska uppgifterna har varit en smärta för alltid. Jag älskar det här tricket från Rob Collie, aka PowerPivotPro.com.
För att ställa in exemplet har du en 54-radig budgettabell: 1 rad per månad per region och produkt.
Fakturafilen är på detaljnivå: 422 rader hittills i år.
Det finns ingen VLOOKUP i världen som någonsin låter dig matcha dessa två datamängder. Men tack vare Power Pivot (aka datamodellen i Excel 2013+) blir det enkelt.
Du måste skapa små små tabeller som jag kallar "joiners" för att länka de två större datamängderna.
I mitt fall är produkt, region och datum gemensamt mellan de två tabellerna. Produktbordet är ett litet fyrcellstabell. Ditto för Region. Skapa var och en av dem genom att kopiera data från en tabell och använda Ta bort dubbletter.
Kalendertabellen till höger var faktiskt tuffare att skapa. Budgetdata har en rad per månad och faller alltid i slutet av månaden. Fakturadatan visar dagliga datum, vanligtvis vardagar. Så jag var tvungen att kopiera fältet Datum från båda datamängderna till en enda kolumn och sedan ta bort dubbletter för att se till att alla datum är representerade. Jag brukade =TEXT(J4,"YYYY-MM")sedan skapa en månadskolumn från de dagliga datumen.
Om du inte har hela Power Pivot-tillägget måste du skapa en pivottabell från budgettabellen och markera kryssrutan Lägg till dessa data i datamodellen.
Som du diskuterade i föregående tips måste du definiera sex förhållanden när du lägger till fält i pivottabellen. Medan du kunde göra detta med sex besök i dialogrutan Skapa relation, avfyrade jag mitt Power Pivot-tillägg och använde diagrammet för att definiera de sex förhållandena.
Här är nyckeln till att allt detta fungerar: Du kan använda de numeriska fälten från Budget och Actual. Men om du vill visa region, produkt eller månad i pivottabellen måste de komma från snickertabellerna!
Här är en pivottabell med data som kommer från fem tabeller. Kolumn A kommer från regionens snickare. Rad 2 kommer från kalendermästaren. Produktskivaren är från snickaren. Budgetnumren kommer från budgettabellen och de faktiska numren kommer från fakturatabellen.
Detta fungerar eftersom kopplingstabellerna använder filter på budgeten och faktiska tabellen. Det är en vacker teknik och visar att Power Pivot inte bara är för big data.
