Budsjetter gjøres på toppnivå - inntekt etter produktlinje etter region etter måned. Faktisk akkumuleres sakte over tid - faktura for faktura, ordrelinje for ordrelinje. Å sammenligne den lille budsjettfilen med de omfattende faktiske dataene har vært en smerte for alltid. Jeg elsker dette trikset fra Rob Collie, aka PowerPivotPro.com.
For å sette opp eksemplet har du en budsjettabell med 54 rader: 1 rad per måned per region per produkt.
Fakturafilen er på detaljnivå: 422 rader hittil i år.
Det er ingen VLOOKUP i verden som noen gang lar deg matche disse to datasettene. Men takket være Power Pivot (aka datamodellen i Excel 2013+), blir dette enkelt.
Du må lage små små tabeller som jeg kaller "joiners" for å koble de to større datasettene.
I mitt tilfelle er produkt, region og dato felles mellom de to tabellene. Produktbordet er et lite firecelletabell. Ditto for Region. Opprett hver av disse ved å kopiere data fra en tabell og bruke Fjern duplikater.
Kalenderbordet til høyre var faktisk tøffere å lage. Budsjettdataene har en rad per måned, og faller alltid på slutten av måneden. Fakturadataene viser daglige datoer, vanligvis hverdager. Så jeg måtte kopiere datofeltet fra begge datasettene til en enkelt kolonne og deretter fjerne duplikater for å sikre at alle datoene er representert. Jeg pleide =TEXT(J4,"YYYY-MM")å lage en månedskolonne fra de daglige datoene.
Hvis du ikke har hele Power Pivot-tillegget, må du opprette en pivottabell fra Budsjett-tabellen og merke av for Legg til disse dataene i datamodellen.
Som diskutert i forrige tips, når du legger til felt i pivottabellen, må du definere seks forhold. Mens du kunne gjøre dette med seks besøk i dialogboksen Opprett forhold, fyrte jeg opp Power Pivot-tillegget mitt og brukte diagramvisningen til å definere de seks relasjonene.
Her er nøkkelen til å gjøre alt dette arbeidet: Du er fri til å bruke de numeriske feltene fra Budget og fra Actual. Men hvis du vil vise region, produkt eller måned i pivottabellen, må de komme fra snekkerbordene!
Her er en pivottabell med data som kommer fra fem tabeller. Kolonne A kommer fra Region snekker. Rad 2 kommer fra Calendar snekker. Produktskiveren er fra snekkerproduktet. Budsjettnumrene kommer fra budsjetttabellen, og de faktiske tallene kommer fra fakturatabellen.
Dette fungerer fordi snekketabellene bruker filtre på budsjettet og faktisk tabell. Det er en vakker teknikk og viser at Power Pivot ikke bare er for store data.
