Excel Data Model (Power Pivot) lar deg koble et stort detaljert datasett med fakta til et budsjett på toppnivå ved å bruke snekketabeller.
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 dataene fra Actuals har vært vondt for alltid. Jeg elsker dette trikset fra Rob Collie, aka PowerPivotPro.com.
For å sette opp eksemplet har du en budsjettabell med 54 rader: en 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.
Se på video
- Du har et lite ovenfra og ned budsjettdatasett
- Du ønsker å sammenligne med et datasett fra undersiden
- Det faktiske kan komme fra et fakturaregister
- Datamodellen lar deg sammenligne disse datasettene i forskjellige størrelser
- Lag begge datasettene til en Ctrl + T-tabell
- For hvert tekstfelt du vil rapportere av, opprett en snekketabell
- Kopier verdiene og fjern duplikater
- For datoer kan du inkludere datoer fra begge tabeller og konvertere til månedsslutt
- Gjør snekkerne til Ctrl + T-tabeller
- Valgfritt, men nyttig å nevne alle fem bordene
- Lag en pivottabell fra Budget og velg datamodellen
- Bygg en pivottabell ved hjelp av Budget og Actual fra de opprinnelige tabellene
- Alle andre felt må komme fra sammenføyningstabellene
- Legg til skiver etter produkt
- Lag tre forhold fra budsjett til deltakere
- Lag tre forhold fra faktisk til deltakere
- I morgen: hvordan det er lettere å bygge relasjoner med Power Pivot og DAX Formulas
Videoutskrift
Lær Excel fra podcast, episode 2016 - Top-Down Budget vs Bottom-Up Actuals!
Hei, jeg podcaster hele denne boken, klikk på "i" øverst til høyre og følg spillelisten.
Hei, jeg kommer til å avbryte dette, dette er Bill Jelen fra 15 minutter fra nå. Jeg skjønner at dette er en utrolig lang podcast, og du blir fristet til å klikke deg gjennom den, men la meg bare gi deg denne korte delen av den. Hvis du er i Excel 2013, og du noen gang har hatt et lite budsjettbord og en massiv faktatabell, og du må kartlegge dem sammen, er dette en utrolig ny evne som vi har i Excel 2013, som ikke mange har forklart , og du vet sannsynligvis ikke om det. Hvis dette er deg, er du i 2013, og du må kartlegge disse to datasettene, ta deg tid, kanskje i dag, kanskje i morgen, kanskje legge den til overvåkningslisten, det er verdt det, det er en fantastisk teknikk.
OK, her er hva vi har, på venstre side har vi et budsjett, dette budsjettet, det er gjort på øverste nivå, ovenfra og ned, rett for hver produktlinje, for hver region, for hver måned, det er et budsjett . Ikke mange poster her, antall på 55, på høyre side prøver vi å sammenligne dette med fakta. Faktisk kommer fra et fakturaregister, så vi har region, produkt og inntekt, men de er individuelle fakturaer, mye mer data her, vi er allerede halvveis i året, og jeg har allerede 423 poster. Ok, så hvordan tilordner du disse 55 til disse 423? Kan være vanskelig å gjøre med VLOOKUP, du må oppsummere først, men heldigvis i Excel 2013 gjør datamodellen dette veldig, veldig enkelt. Det vi trenger for å la dette store, massive bordet kommunisere med dette lille bordet, er mellommenn, jeg kaller dem snekkere.Små små bord, produkt, region og kalender, vi kommer til å delta i budsjettet til disse tre tabellene, vi skal bli med på det faktiske til disse tre bordene, og mirakuløst vil pivottabellen fungere. Greit, så her gjør vi det.
Først må jeg lage sammenleggere, så jeg tar dette produktfeltet fra kolonne A, og jeg kopierer det til kolonne F, og deretter Data, Fjern duplikater, klikker OK, og vi sitter igjen med en liten liten tabell, 1 overskrift 3 rader. Samme for region, ta regionene, Ctrl + C, gå over til kolonne G, Lim inn, Fjern duplikater, klikk OK, 3 rader 1 overskrift, ok. Nå for datoene er datoene ikke de samme, disse er månedssluttdatoer, de lagres faktisk som månedssluttdatoer, og dette er hverdager. Jeg skal ta begge lister, Ctrl + C den andre listen og lime den inn her, Ctrl + V, så skal jeg ta den kortere listen, kopiere den og lime den ned nedenfor, ok. Og det er veldig irriterende at selv om disse er lagret som datoer, ser de ut som måneder, og Fjern duplikater vil ikke se dem som de samme.Så før jeg bruker Fjern duplikater, må jeg endre det til en kort dato. Velg dataene, Data, Fjern duplikater, klikk OK, og deretter litt sortering her for å få det til å fungere.
OK, nå vil jeg ikke rapportere etter daglig dato, så jeg skal legge til en kolonne her, en oppslagskolonne som sier måned, og dette vil være lik EOMONTH den datoen,, 0, som får oss ut til slutten av måneden. Det vil formatere det som kort dato, og kopiere det ned, ok. Nå må vi gjøre hver av disse til en Ctrl + T-tabell, så herfra Ctrl + T, bordet mitt har overskrifter, vakkert. De små, det skjønner ikke at det er overskrifter der oppe, så vi må sørge for å merke av det og Ctrl + T, ok, og de kaller disse tabellene Tabell1, Tabell2, Tabell3, veldig kjedelige navn, ikke sant? Så jeg skal gi nytt navn til disse og kalle det BudTable, ProdTable, RegTable, min CalTable, og deretter ActTable, ok.
Vi starter fra den aller første tabellen, og forresten skal vi ikke bruke PowerPivot i dag, vi skal gjøre alt dette med datamodellen. Så, Excel 2013 eller nyere, har du denne innsatsen, pivottabellen. Vi kommer til å merke av for "Legg til disse dataene i datamodellen", klikk OK, og vi får feltlisten vår med den magiske Alt-knappen, som jeg velger blant alle fem tabellene i arbeidsboken, Faktisk, Budsjett, Kalender, Produkt, Region. Ok, så tallene kommer fra budsjettbordet, jeg legger budsjettet der inne, og fra den faktiske tabellen legger jeg det faktiske der inne, men så er det tingen for resten av pivottabellen. Alle andre tekstfelt som vi skal plassere i radområdet eller kolonneområdet eller som skiver, de må komme fra snekkerne, de må komme fra de tabellene mellom tabellene.
Greit, så fra kalendertabellen tar vi det månedsfeltet og legger det over toppen, vi kommer til å ignorere andre forhold akkurat nå. Jeg skal skape relasjoner, men jeg vil skape dem på en gang. Og Region-tabellen, legg regionene ned på siden. Jeg kunne lagt ned produktene, men jeg skal faktisk bruke produktbordet som en skiver, så Analyser, Sett inn skiver, igjen må du gå til Alt hvis du ikke har brukt produktbordet ennå. Så gå til Alle, så ser du at produktet er tilgjengelig for å lage som skiver fra produktene, slik. Greit nå, på dette punktet har vi ikke opprettet relasjoner, så alle disse tallene er feil. Og forholdene vi må lage, vi må lage tre tabeller fra denne lille budsjettbordet, en til produktene, en til regioner, en til kalenderen,det er 3 forhold. Og så må vi opprette relasjoner fra den faktiske tabellen til produktområdet i kalenderen, så totalt 6 tabeller. Og ja, dette ville definitivt være lettere hvis vi hadde PowerPivot, men vi gjør ikke eller la oss anta at vi ikke gjør det.
Og så skal jeg bruke den gammeldagse måten, Opprett dialogen her, der vi har Budsjett-tabellen til venstre, og vi skal bruke Region-feltet og relatere det til Region-tabellen, Region-feltet . OK, 1/6 er opprettet. Jeg velger Opprett, igjen fra Budsjett-tabellen går vi til Produktet, og kobler det deretter til Produkttabellen, til Produktet, klikker du OK. Fra Budsjett-tabellen datofeltet går vi til kalendertabellen, og skjebne-feltet, klikker OK, vi er halvveis der, ok. Fra Actuals-tabellen går vi Region, til Region-tabellen, klikker OK, fra Actuals-tabellen til Produktet, og fra Actuals-tabellen til Kalenderen. Jeg skal faktisk ta verdiene og få det til å gå ned på siden, ok. Design, rapportoppsett, vis i tabellform for å få en visning som jeg foretrekker, gjenta alle elementetiketter, ok,dette er helt fantastisk! Nå har vi denne lille lille tabellen, en 50-talls poster i denne tabellen med hundrevis av poster, og vi har laget en enkelt pivottabell takket være datamodellen. For hver hvor vi kan se budsjettet, kan vi se inntektene, den er delt ut etter region, den er delt ut etter måned, og den kan deles ut etter produkt.
Nå kom dette konseptet til meg fra Rob Collie som driver Power Pivot Pro, og Rob har laget mange bøker der ute, hans siste er "Power Pivot and Power BI". Jeg tror denne faktisk var i "Power Pivot Alchemy" -boken, det var den jeg så dette, og jeg sa "Vel dette, selv om jeg ikke har millioner av rader å rapportere gjennom Power Pivot, er dette en som har gjort en STOR forskjell i livet mitt, har to datasett med uoverensstemmende størrelser, og trenger å rapportere fra dem begge. " Vel, dette eksemplet og mange andre er i denne boka, jeg vil til slutt få hele bokpodcasten, som ser ut som om det kommer til å ta to og en halv måned. Men du kan få hele boka i dag, samtidig, gå dit, kjøpe boken, $ 10 for e-boken, $ 25 for trykkboken, og du kan ha alle disse tipsene samtidig.
Ok, en veldig lang episode her: vi har et lite ovenfra og ned budsjett og en bunn opp. Faktisk, de er forskjellige størrelser, men bruker datamodellen i Excel 2013 … Og forresten, hvis du er i 2010, kan du i teorien, gjør dette ved å få Power Pivot-tillegget, og gå gjennom alle disse trinnene tilbake i 2010. Lag begge datasettene til en Ctrl + T-tabell, og bli med i tabellene for alt du vil rapportere om, i radetikett, eller kolonnetiketten eller skivene, så kopier disse verdiene over og Fjern duplikater for datoene. Jeg tok faktisk verdier fra begge bordene, fordi det var noen unike verdier i hver, og så brukte jeg EOMONTH for å komme meg ut der, få disse snekkerbordene til å være kontrollerte tabeller. Det er valgfritt, men jeg kalte alle de fem tabellene, for lettere når du setter opp disse relasjonene, i stedet for å bli kalt Table1,Tabell2, Tabell3.
Start så fra Budsjett-tabellen, Sett inn, pivottabell, merk av i datamodellen, og bygg deretter en pivottabell ved hjelp av budsjett og faktisk. Alt annet kommer fra sammenføyningstabellene, så Region og Måned i rad- og kolonneområdet, skiver kom fra produkttabellen. Og så måtte vi lage 3 forhold fra budsjettet til deltakerne, 3 forhold fra det faktiske til deltakerne, og vi har en fantastisk pivottabell. Nå i morgen tar vi en titt på å bruke Power Pivot-fanen og lager noen ekstra beregninger. Så alt dette er mulig, det er når vi vil sette inn et beregnet felt, det er da du må betale ekstra $ 2 i måneden for å få Pro Plus-versjonen av Office 365.
Vel hei, takk til Rob Collie fra Power Pivot Pro for dette tipset, og takk til deg for at du var innom, vi sees neste gang for nok en netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2016.xlsx
