Excel-datamodel (Power Pivot) giver dig mulighed for at forbinde et stort detaljeret datasæt med faktiske forhold til et budget på øverste niveau ved hjælp af snedkertabeller.
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 konfigurere eksemplet har du en 54-række budget tabel: en 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 Fø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.
Se video
- Du har et lille top-down budget datasæt
- Du vil sammenligne med et datasæt, der viser bunden op
- Det faktiske kan komme fra et fakturaregister
- Datamodellen giver dig mulighed for at sammenligne disse datasæt med forskellig størrelse
- Lav begge datasæt til en Ctrl + T-tabel
- Opret en sammenføjningstabel for hvert tekstfelt, som du vil rapportere efter
- Kopier værdierne og fjern dubletter
- For datoer kan du inkludere datoer fra begge tabeller og konvertere til månedsslutningen
- Gør samlingerne til Ctrl + T-tabeller
- Valgfrit, men nyttigt at navngive alle fem tabeller
- Opret en pivottabel fra Budget, og vælg datamodellen
- Byg en pivottabel ved hjælp af Budget og Actual fra de oprindelige tabeller
- Alle andre felter skal komme fra sammenføjningstabellerne
- Tilføj udsnit efter produkt
- Opret tre forhold fra budget til deltagere
- Opret tre relationer fra faktisk til deltagere
- I morgen: hvordan det er lettere at opbygge relationer med Power Pivot og DAX Formulas
Videoudskrift
Lær Excel fra podcast, afsnit 2016 - Top-Down-budget vs Bottom-Up-fakta!
Hej, jeg podcaster hele denne bog, klik på det "i" øverst til højre og følg afspilningslisten.
Hej, jeg afbryder dette, dette er Bill Jelen fra 15 minutter fra nu. Jeg er klar over, at dette er en utrolig lang podcast, og du er fristet til bare at klikke lige igennem den, men lad mig bare give dig denne korte af den. Hvis du er i Excel 2013, og du nogensinde har haft en lille budgettabel og en massiv faktisk tabel, og du skal kortlægge dem sammen, er dette en forbløffende ny evne, som vi har i Excel 2013, som ikke mange mennesker har forklaret , og du ved sandsynligvis ikke om det. Hvis dette er dig, er du i 2013, og du skal kortlægge disse to datasæt, tag dig tid, måske i dag, måske i morgen, måske tilføj det til overvågningslisten, det er det værd, det er en fantastisk teknik.
Okay, her er hvad vi har, på venstre side har vi et budget, dette budget, det er gjort på øverste niveau, top-down, lige for hver produktlinje, for hver region, for hver måned, der er et budget . Ikke mange poster her, optælling af 55, på højre side forsøger vi at sammenligne dette med det faktiske. Faktikken kommer fra et fakturaregister, så vi har region, produkt og indtægter, men det er individuelle fakturaer, meget mere data her, vi er allerede halvvejs igennem året, og jeg har allerede 423 poster. Okay, så hvordan kortlægger du disse 55 til disse 423? Det kan være svært at gøre med VLOOKUP, du bliver nødt til at opsummere først, men heldigvis i Excel 2013 gør datamodellen dette virkelig, virkelig let. Hvad vi har brug for for at lade dette store massive bord kommunikere med dette lille bord, er mellemmænd, jeg kalder dem deltagere.Små små tabeller, produkt, region og kalender, vi går sammen med budgettet til disse tre tabeller, vi slutter os til de faktiske til disse tre tabeller, og mirakuløst fungerer pivottabellen. Okay, så her gør vi det.
Først og fremmest skal jeg oprette sammenføjningerne, så jeg tager dette produktfelt fra kolonne A, og jeg kopierer det til kolonne F, og derefter Data, Fjern duplikater, klik på OK, og vi har en lille lille tabel, 1 overskrift 3 rækker. Samme ting for Region, tag regionerne, Ctrl + C, gå over til kolonne G, Indsæt, Fjern duplikater, klik på OK, 3 rækker 1 overskrift, okay. Nu for datoerne er datoerne ikke de samme, disse er månedens slutdatoer, de er faktisk gemt som månedens slutdatoer, og disse er hverdage. Jeg tager begge lister, Ctrl + C den anden liste og indsætter den her, Ctrl + V, så tager jeg den kortere liste, kopierer den og indsætter den nedenunder, okay. Og det er virkelig irriterende, at selvom disse er gemt som datoer, vises de som måneder, og Fjern duplikater vil ikke se dem som de samme.Så før jeg bruger Fjern duplikater, skal jeg ændre det til en kort dato. Vælg disse data, data, fjern duplikater, klik på OK, og derefter en lille sortering her for at få dem til at fungere.
Okay, nu vil jeg ikke rapportere efter daglig dato, så jeg vil tilføje en kolonne her, en opslagskolonne, der siger måned, og dette vil være lig med EOMONTH den dato,, 0, som får os ud til i slutningen af måneden. Det vil formatere det som kort dato, og kopiere det ned, okay. Nu skal vi gøre hver af disse til en Ctrl + T-tabel, så herfra Ctrl + T, mit bord har overskrifter, smukke. De små, det er ikke klar over, at der er overskrifter deroppe, så vi skal sørge for at markere det og Ctrl + T, okay, og de kalder disse tabeller Tabel1, Tabel2, Tabel3, virkelig kedelige navne, ikke? Så jeg vil omdøbe disse og kalde det BudTable, ProdTable, RegTable, min CalTable og derefter ActTable, okay.
Vi starter fra den allerførste tabel, og forresten bruger vi ikke PowerPivot i dag, vi skal gøre alt dette med datamodellen. Så, Excel 2013 eller nyere, har du denne indsats, pivottabel, vi markerer afkrydsningsfeltet for "Tilføj disse data til datamodellen", klik på OK, og vi får vores feltliste med den magiske Alt-knap, der lader jeg vælger blandt alle fem tabeller i projektmappen, Faktisk, Budget, Kalender, Produkt, Region. Okay, så tallene kommer fra budgettabellen, jeg lægger budgettet derinde, og fra den faktiske tabel lægger jeg den faktiske derinde, men så er det her noget for resten af pivottabellen. Alle andre tekstfelter, som vi skal placere i rækkeområdet eller kolonneområdet eller som udsnit, de skal komme fra sammenføjningerne, de skal komme fra disse tabeller mellem tabellerne.
Okay, så fra kalendertabellen tager vi det månedlige felt og sætter det over toppen, vi vil ignorere andre forhold lige nu. Jeg vil skabe relationer, men jeg vil skabe dem på én gang. Og regionstabellen, læg regionerne ned på siden. Jeg kunne lægge produkterne ned ad siden, men jeg skal faktisk bruge produktbordet som en skiver, så Analyser, indsæt skiver, igen skal du gå til Alt, hvis du ikke har brugt produkttabellen endnu. Så gå til Alt, så ser du, at produktet er tilgængeligt for at oprette som skiver fra produkterne, sådan. Okay nu, på dette tidspunkt har vi ikke oprettet relationer, så alle disse tal er forkerte. Og de forhold, vi skal skabe, vi skal oprette 3 tabeller fra denne lille budgettabel, en til produkterne, en til regioner, en til kalender,det er 3 forhold. Og så skal vi oprette relationer fra den faktiske tabel til produktområdet i kalenderen, så i alt 6 tabeller. Og ja, dette ville helt sikkert være lettere, hvis vi havde PowerPivot, men vi antager ikke, eller lad os antage, at vi ikke har det.
Og så vil jeg bruge den gammeldags måde, Opret dialogen her, hvor vi har Budget-tabellen til venstre, og vi vil bruge Region-feltet og relatere det til Region-tabellen, Region-feltet . Okay, 1/6 er oprettet. Jeg vælger Opret, igen fra budgettabellen går vi til produktet og linker det derefter til produkttabellen til produktet, klik på OK. Fra budgettabellen datofeltet går vi til kalendertabellen, og skæbne-feltet, klik på OK, vi er halvvejs der, okay. Fra tabellen Actuals går vi Region til tabellen Region, klik på OK, fra tabellen Actuals til produktet og fra tabellen Actuals til kalenderen. Jeg vil faktisk tage værdierne og få det til at gå ned ad siden, okay. Design, rapportlayout, vis i tabelform for at få en visning, som jeg foretrækker, gentag alle elementetiketter, okay,dette er helt fantastisk! Nu har vi denne lille lille tabel, en 50-nogle poster i denne tabel med hundredvis af poster, og vi har oprettet en enkelt pivottabel takket være datamodellen. For hver, hvor vi kan se budgettet, kan vi se indtægterne, den er opdelt efter region, den er opdelt efter måned, og den kan udskæres efter produkt.
Nu kom dette koncept til mig fra Rob Collie, der driver Power Pivot Pro, og Rob har oprettet en masse bøger derude, hans seneste er "Power Pivot og Power BI". Jeg tror, at denne faktisk var i "Power Pivot Alchemy" -bogen, det var den, jeg så dette, og jeg sagde "Nå dette, selvom jeg ikke har millioner af rækker til at rapportere gennem Power Pivot, er det en der ville har gjort en Kæmpe forskel i mit liv, med to datasæt af uoverensstemmende størrelser og behov for at rapportere fra dem begge. ” Nå dette eksempel og mange andre findes i denne bog, jeg får til sidst hele bogpodcasten, der ser ud til at det tager to og en halv måned. Men du kan få hele bogen i dag, samme tid, gå der, købe bogen, $ 10 for e-bogen, $ 25 for den trykte bog, og du kan få alle disse tip på én gang.
Okay, en rigtig lang episode her: Vi har et lille top-down-budget og en bund op. Faktisk er de forskellige størrelser, men ved hjælp af datamodellen i Excel 2013 … Og forresten, hvis du er i 2010, kan du teoretisk, gør dette ved at få Power Pivot-tilføjelsesprogrammet, og gennemgå alle disse trin tilbage i 2010. Lav begge datasæt til en Ctrl + T-tabel, og slut dig derefter til dine tabeller for alt, hvad du vil rapportere om, i rækkeetiket eller kolonneetiketten eller skiverne, så kopier disse værdier over, og fjern duplikater til datoerne. Jeg tog faktisk værdier fra begge tabeller, fordi der var nogle unikke værdier i hver, og så brugte jeg EOMONTH til at komme derude, få disse snedkerborde til at være kontrollerede tabeller. Det er valgfrit, men jeg navngav alle 5 tabeller, for lettere når du opretter disse forhold, snarere end at blive kaldt tabel1,Tabel2, Tabel3.
Start så fra budgettabellen, indsæt, pivottabel, marker afkrydsningsfeltet for datamodel, og opbyg derefter en pivottabel ved hjælp af budget og faktisk. Alt andet kommer fra sammenføjningstabellerne, så Region og Måned i række- og kolonneområdet, udsnit kom fra produkttabellen. Og så var vi nødt til at oprette 3 relationer fra budgettet til deltagere, 3 relationer fra den faktiske til deltagerne, og vi har en fantastisk pivottabel. Nu i morgen ser vi på brugen af fanen Power Pivot og opretter nogle yderligere beregninger. Så alt dette er muligt, det er når vi vil indsætte et beregnet felt, det er når du skal betale de ekstra $ 2 om måneden for at få Pro Plus-versionen af Office 365.
Nå hej, tak til Rob Collie fra Power Pivot Pro for dette tip, og tak til dig for at du kom forbi, vi ses næste gang til endnu en netcast fra!
Download fil
Download eksempelfilen her: Podcast2016.xlsx
