Excel Data Model (Power Pivot) låter dig ansluta en stor detaljerad datauppsättning med fakta till en toppnivåbudget med hjälp av snickertabeller.
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: en 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.
Kolla på video
- Du har en liten budgetuppsättning uppifrån och ned
- Du vill jämföra med en nedre uppsättning faktiska datamängden
- Fakta kan komma från ett fakturaregister
- Datamodellen låter dig jämföra dessa datamängder av olika storlek
- Gör båda datauppsättningarna till en Ctrl + T-tabell
- Skapa en anslutningstabell för varje textfält som du vill rapportera av
- Kopiera värdena och ta bort dubbletter
- För datum kan du inkludera datum från båda tabellerna och konvertera till månadsslutet
- Gör kopplarna till Ctrl + T-tabeller
- Valfritt men användbart för att namnge alla fem tabeller
- Skapa en pivottabell från Budget och välj datamodell
- Bygg en pivottabell med hjälp av Budget och Actual från de ursprungliga tabellerna
- Alla andra fält måste komma från anslutningstabellerna
- Lägg till skivor efter produkt
- Skapa tre relationer från Budget till Joiners
- Skapa tre relationer från Actual till Joiners
- I morgon: hur det är lättare att bygga relationer med Power Pivot och DAX Formulas
Videoutskrift
Lär dig Excel från podcast, avsnitt 2016 - Top-Down Budget vs Bottom-Up Actuals!
Hej, jag podcastar hela denna bok, klicka på det "i" i det övre högra hörnet och följ spellistan.
Hej, jag kommer att avbryta detta, det här är Bill Jelen från 15 minuter från nu. Jag inser att det här är en otroligt lång podcast, och du är frestad att bara klicka igenom den, men låt mig bara ge dig det här korta av det. Om du är i Excel 2013 och du någonsin har haft en liten budgettabell och en massiv faktatabell och du behöver kartlägga dem tillsammans, är detta en fantastisk ny förmåga som vi har i Excel 2013, som inte många har förklarat , och du vet nog inte om det. Om det är du, är du 2013, och du måste kartlägga dessa två datamängder, ta dig tid, kanske idag, kanske imorgon, kanske lägga till den på bevakningslistan, det är värt det, det är en fantastisk teknik.
Okej, här är vad vi har, på vänster sida har vi en budget, den här budgeten, den är gjord på toppnivå, uppifrån och ner, rätt för varje produktlinje, för varje region, för varje månad, det finns en budget . Inte många poster här, räknar med 55, på höger sida försöker vi jämföra detta med fakta. Fakta kommer från ett fakturaregister, så vi har region, produkt och intäkter, men de är enskilda fakturor, mycket mer data här, vi är redan halvvägs genom året och jag har redan 423 poster. Okej, så hur mappar du dessa 55 till dessa 423? Det kan vara svårt att göra med VLOOKUP, du måste sammanfatta först, men tack och lov i Excel 2013 gör datamodellen detta riktigt, riktigt enkelt. Vad vi behöver för att låta detta stora massiva bord kommunicera med det här lilla bordet är mellanhänder, jag kallar dem snickare.Små små tabeller, produkt, region och kalender, vi kommer att gå med i budgeten till dessa tre tabeller, vi kommer att gå med i faktiska till dessa tre tabeller, och mirakulöst fungerar pivottabellen. Okej, så här gör vi det.
Först och främst måste jag skapa snickarna, så jag tar det här produktfältet från kolumn A och kopierar det till kolumn F och sedan Data, tar bort duplikat, klickar på OK och vi har en liten tabell 1 rubrik 3 rader. Samma sak för region, ta regionerna, Ctrl + C, gå över till kolumn G, klistra in, ta bort dubbletter, klicka på OK, 3 rader 1 rubrik, okej. Nu för datumen är datumen inte desamma, det här är månadsslutdatum, de lagras faktiskt som månadsslutdatum och dessa är vardagar. Jag ska ta båda listorna, Ctrl + C den andra listan och klistra in den här, Ctrl + V, då ska jag ta den kortare listan, kopiera den och klistra in den nedan, okej. Och det är väldigt irriterande att, även om dessa lagras som datum, de visas som månader, och Ta bort dubbletter kommer inte att se dem som samma.Så innan jag använder Ta bort dubbletter måste jag ändra det till ett kort datum. Välj data, Data, Ta bort dubbletter, klicka på OK och sedan lite sortera här för att få det att fungera.
Okej, nu vill jag inte rapportera efter dagligt datum, så jag ska lägga till en kolumn här, en uppslagskolumn som säger Månad, och det kommer att vara lika med EOMONTH det datumet,, 0, som får oss ut till i slutet av månaden. Det kommer att formatera det som kort datum och kopiera det ner, okej. Nu måste vi göra vart och ett av dessa till en Ctrl + T-tabell, så härifrån Ctrl + T, Mitt bord har rubriker, vackra. De små, det inser inte att det är rubriker där uppe, så vi måste se till att markera det och Ctrl + T, okej, och de kallar dessa tabeller Tabell1, Tabell2, Tabell3, riktigt tråkiga namn, eller hur? Så jag ska byta namn på dessa och kalla det BudTable, ProdTable, RegTable, min CalTable och sedan ActTable, okej.
Vi börjar från den allra första tabellen, och förresten kommer vi inte att använda PowerPivot idag, vi kommer att göra allt detta med datamodellen. Så, Excel 2013 eller nyare, du har denna insats, pivottabell, vi kommer att markera rutan för "Lägg till dessa data till datamodellen", klicka på OK, och vi får vår fältlista med den magiska allt-knappen, som låter jag väljer bland alla fem tabellerna i arbetsboken, Faktisk, Budget, Kalender, Produkt, Region. Okej, så siffrorna kommer från budgettabellen, jag lägger in budgeten där, och från den faktiska tabellen lägger jag den faktiska där, men då är det här för resten av pivottabellen. Alla andra textfält som vi ska placera i radområdet eller kolumnområdet eller som skivor, de måste komma från kopplarna, de måste komma från dessa tabeller mellan tabellerna.
Okej, så från kalendertabellen tar vi det månadsfältet och lägger det överst, vi kommer att ignorera andra relationer just nu. Jag kommer att skapa relationerna, men jag vill skapa dem på en gång. Och Regiontabellen, lägg regionerna åt sidan. Jag skulle kunna lägga produkter på sidan, men jag ska faktiskt använda produktbordet som en skivare, så Analysera, Insert Slicer, igen måste du gå till Alla om du inte har använt Produkttabellen ännu. Så gå till Alla, så ser du att produkten är tillgänglig att skapa som skivare från produkterna, så. Okej nu, vid den här tiden har vi inte skapat relationer, så alla dessa siffror är fel. Och de relationer vi måste skapa, vi måste skapa 3 tabeller från den här lilla budgettabellen, en till produkterna, en till regioner, en till kalendern,det är 3 relationer. Och sedan måste vi skapa relationer från tabellen Faktisk till produktregionen i kalendern, så totalt 6 tabeller. Och ja, detta skulle definitivt vara lättare om vi hade PowerPivot, men vi gör inte eller låt oss anta att vi inte gör det.
Och så ska jag använda det gammaldags sättet, dialogrutan Skapa här, där vi har budgettabellen till vänster, och vi kommer att använda Region-fältet och relatera det till Region-tabellen, Region-fältet . Okej, 1/6 är skapade. Jag väljer Skapa, igen från budgettabellen går vi till produkten och länkar sedan den till produkttabellen, till produkten och klickar på OK. Från budgettabellen datumfältet går vi till kalendertabellen och öde-fältet, klicka på OK, vi är halvvägs, okej. Från tabellen Actuals går vi Region till tabellen Region, klicka på OK, från tabellen Actuals till produkten och från tabellen Actuals till kalendern. Jag ska faktiskt ta värdena och få det att gå neråt, okej. Design, rapportlayout, visa i tabellform för att få en vy som jag föredrar, upprepa alla artikeletiketter, okej,det här är helt fantastiskt! Nu har vi den här lilla lilla tabellen, ett 50-tal poster i den här tabellen med hundratals poster, och vi har skapat en enda pivottabell tack vare datamodellen. För var och en där vi kan se budgeten kan vi se intäkterna, den är uppdelad efter region, den är uppdelad per månad och den kan delas ut efter produkt.
Nu kom detta koncept till mig från Rob Collie som driver Power Pivot Pro, och Rob har skapat många böcker där ute, hans senaste är "Power Pivot och Power BI". Jag tror att den här faktiskt var i "Power Pivot Alchemy" -boken, det var den jag såg den här och jag sa "Nåväl, även om jag inte har miljontals rader att rapportera via Power Pivot, är det en som skulle har gjort en ENORM skillnad i mitt liv, med två datamängder av felaktiga storlekar, och jag behöver rapportera från dem båda. ” Tja, detta exempel och många andra finns i den här boken, så småningom kommer jag att få hela bokpodcasten, som ser ut att det kommer att ta två och en halv månad. Men du kan få hela boken idag, samtidigt, gå dit, köp boken, $ 10 för e-boken, $ 25 för den tryckta boken, och du kan ha alla dessa tips på en gång.
Okej, en riktigt lång episod här: vi har en liten uppifrån-och-ned-budget och en botten upp Faktiskt, de är olika storlekar, men använder datamodellen i Excel 2013 … Och förresten om du är 2010 kan du , i teorin, gör detta genom att få Power Pivot-tillägget och gå igenom alla dessa steg tillbaka 2010. Gör båda datauppsättningarna till en Ctrl + T-tabell och gå sedan med i dina tabeller för allt du vill rapportera om, i radetikett eller kolumnetiketten eller skivorna, så kopiera dessa värden över och ta bort dubbletter för datumen. Jag tog faktiskt värden från båda tabellerna, eftersom det fanns några unika värden i var och en, och sedan använde jag EOMONTH för att komma ut där, göra dessa snickertabeller till kontrollerade tabeller. Det är valfritt, men jag namngav alla 5 tabellerna, för lättare när du skapar dessa relationer snarare än att kallas Table1,Tabell2, Tabell3.
Börja sedan från budgettabellen, Infoga, pivottabell, kryssa i rutan för datamodell och bygg sedan en pivottabell med hjälp av budget och faktiskt. Allt annat kommer från anslutningstabellerna, så Region och månad i rad- och kolumnområdet, skivor kom från produkttabellen. Och sedan var vi tvungna att skapa 3 relationer från budgeten till anslutarna, 3 relationer från faktiska till anslutarna, och vi har en fantastisk pivottabell. Nu imorgon tar vi en titt på fliken Power Pivot och skapar ytterligare beräkningar. Så allt detta är möjligt, det är när vi vill infoga ett beräknat fält, det är då du måste betala extra $ 2 per månad för att få Pro Plus-versionen av Office 365.
Tja hej, tack till Rob Collie från Power Pivot Pro för det här tipset, och tack till dig för att du kom förbi, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2016.xlsx
