Budgetter udføres på det øverste niveau - indtægter efter produktlinje efter region efter måned. Faktiske ophobes langsomt over tid - faktura for faktura, linjepost for linjepost. At sammenligne den lille budgetfil med de omfangsrige faktiske data har været en smerte for evigt. Jeg elsker dette trick fra Rob Collie, aka PowerPivotPro.com.
For at opsætte eksemplet har du en 54-række budget tabel: 1 række pr. Måned pr. Region pr. Produkt.
Fakturafilen er på detaljeringsniveau: 422 rækker hidtil i år.
Der er ingen VLOOKUP i verden, der nogensinde giver dig mulighed for at matche disse to datasæt. Men takket være Power Pivot (også kaldet datamodellen i Excel 2013+) bliver dette let.
Du skal oprette små små tabeller, som jeg kalder "joinere" for at forbinde de to større datasæt.
I mit tilfælde er produkt, region og dato fælles mellem de to tabeller. Produkttabellen er et lille firecellet bord. Ditto for region. Opret hver af disse ved at kopiere data fra en tabel og bruge Fjern duplikater.
Kalenderbordet til højre var faktisk sværere at oprette. Budgetdataene har en række pr. Måned og falder altid i slutningen af måneden. Fakturadataene viser daglige datoer, normalt hverdage. Så jeg måtte kopiere datofeltet fra begge datasæt til en enkelt kolonne og derefter fjerne dubletter for at sikre, at alle datoer er repræsenteret. Jeg brugte derefter til =TEXT(J4,"YYYY-MM")at oprette en månedskolonne ud fra de daglige datoer.
Hvis du ikke har det fulde Power Pivot-tilføjelsesprogram, skal du oprette en pivottabel fra Budget-tabellen og markere afkrydsningsfeltet Tilføj disse data til datamodellen.
Som diskuteret i det forrige tip, når du tilføjer felter til pivottabellen, bliver du nødt til at definere seks forhold. Mens du kunne gøre dette med seks besøg i dialogboksen Opret forhold, affyrede jeg min Power Pivot-tilføjelse og brugte diagrammet til at definere de seks forhold.
Her er nøglen til at få alt dette til at fungere: Du er fri til at bruge de numeriske felter fra Budget og fra Faktisk. Men hvis du vil vise region, produkt eller måned i pivottabellen, skal de komme fra snedkertabellerne!
Her er en pivottabel med data, der kommer fra fem tabeller. Kolonne A kommer fra Region snedker. Række 2 kommer fra kalendermedarbejder. Produktsnitteren kommer fra Product snedker. Budgetnumrene kommer fra budgettabellen, og de faktiske tal kommer fra fakturatabellen.
Dette fungerer, fordi sammenføjningstabellerne anvender filtre på budgettet og den faktiske tabel. Det er en smuk teknik og viser, at Power Pivot ikke kun er til big data.
