version af denne artikel oversat af Marisela Ordaz Download datasættet her for at følge med i selvstudiet.
På tværs af de forskellige felter og under-underfelter, der spænder over finans, finansiel analyse, finansielle markeder og finansiel investering, er Microsoft Excel konge. Med ankomsten og den eksponentielle vækst af big data, drevet af årtiers dataaggregering og -akkumulering, fremkomsten af billig cloud-lagring og fremkomsten af tingenes internet – dvs. e-handel, sociale medier og enheders sammenkobling – Excels ældre funktionalitet og muligheder er blevet presset til deres grænser.
Mere specifikt reducerede den ældre generation af Excels infrastruktur og behandlingsbegrænsninger såsom rækkegrænsen på 1.048.576 rækker eller uundgåelig behandlingsnedgang, når det drejer sig om store datasæt, datatabeller og indbyrdes forbundne regneark, dets anvendelighed som et effektivt big data-værktøj. I 2010 tilføjede Microsoft imidlertid en ny dimension til Excel, kaldet Power Pivot. Power Pivot tilbød næste generations business intelligence og business analytics-funktionalitet til Excel i sin evne til at udtrække, kombinere og analysere næsten ubegrænsede datasæt uden forringelse af behandlingshastigheden. På trods af dens udgivelse for otte år siden, ved de fleste finansanalytikere stadig ikke, hvordan man bruger Power Pivot, og mange ved ikke, at den overhovedet eksisterer.
I denne artikel vil jeg vise dig, hvordan du bruger Power Pivot til at overvinde almindelige Excel-problemer og tage et kig på yderligere nøglefordele ved softwaren ved hjælp af nogle eksempler. Denne Power Pivot-tutorial er beregnet til at tjene som en guide til, hvad du kan opnå med dette værktøj, og i slutningen vil den udforske nogle eksempler på brugssituationer, hvor Power Pivot ofte viser sig at være uvurderlig.
Power Pivot er en funktion i Microsoft Excel, der blev introduceret som et tilføjelsesprogram til Excel 2010 og 2013, og er nu en indbygget funktion til Excel 2016 og 365. Som Microsoft forklarer, gør Power Pivot til Excel "er i stand til at importere millioner af rækker af data fra flere datakilder til en enkelt Excel-projektmappe, opret relationer mellem heterogene data, opret beregnede kolonner og mål ved hjælp af formler, byg pivottabeller og pivotdiagrammer, og analyser derefter dataene yderligere, så du kan træffe rettidige forretningsbeslutninger uden at kræve it-assistance. ”
Det primære udtrykssprog, som Microsoft bruger i Power Pivot, er DAX (Data Analysis Expressions), selvom andre kan bruges i specifikke situationer. Igen, som Microsoft forklarer, "DAX er en samling af funktioner, operatorer og konstanter, der kan bruges i en formel eller et udtryk til at beregne og returnere en eller flere værdier. Sagt mere enkelt hjælper DAX dig med at skabe ny information ud fra data, der allerede er i din model." Heldigvis for dem, der allerede er bekendt med Excel, vil DAX-formler se velkendte ud, da mange af formlerne har en lignende syntaks (f.eks. SUM , AVERAGE , TRUNC ).
For klarhedens skyld kan de vigtigste fordele ved at bruge Power Pivot vs. grundlæggende Excel opsummeres som følgende:
I de følgende afsnit vil jeg gennemgå hvert af ovenstående og vise dig, hvordan Power Pivot til Excel kan være nyttigt.
Som tidligere nævnt, er en af de største begrænsninger ved Excel at arbejde med ekstremt store datasæt. Heldigvis for os kan Excel nu indlæse langt over grænsen på en million rækker direkte i Power Pivot.
For at demonstrere dette genererede jeg et eksempeldatasæt med to års salg til en sportsforhandler med ni forskellige produktkategorier og fire regioner. Det resulterende datasæt er to millioner rækker.
Brug af Data fanen på båndet, oprettede jeg en Ny forespørgsel fra CSV-filen (se Oprettelse af en ny forespørgsel under). Denne funktion hed tidligere PowerQuery, men fra Excel 2016 og 365 var den mere tæt integreret i fanen Data i Excel.
Fra en tom projektmappe i Excel til at indlæse alle to millioner rækker i Power Pivot tog det omkring et minut! Bemærk, at jeg var i stand til at udføre noget let dataformatering ved at promovere den første række til at blive kolonnenavnene. I løbet af de sidste par år er Power Query-funktionaliteten blevet væsentligt forbedret fra et Excel-tilføjelsesprogram til en tæt integreret del af fanen Data på værktøjslinjen. Power Query kan pivotere, udjævne, rense og forme dine data gennem sin suite af muligheder og sit eget sprog, M.
En af de andre nøglefordele ved Power Pivot til Excel er muligheden for nemt at importere data fra flere kilder. Tidligere oprettede mange af os flere arbejdsark til vores forskellige datakilder. Ofte involverede denne proces at skrive VBA-kode og kopiere/indsætte fra disse forskellige kilder. Heldigvis for os giver Power Pivot dig dog mulighed for at importere data fra forskellige datakilder direkte til Excel uden at skulle støde ind i ovennævnte problemer.
Ved at bruge forespørgselsfunktionen i bilag 1 kan vi hente fra enhver af følgende kilder:
Yderligere kan flere datakilder kombineres enten i Query-funktionen eller i Power Pivot-vinduet for at integrere data. For eksempel kan du trække produktionsomkostningsdata fra en Excel-projektmappe og faktiske salgsresultater fra SQL-serveren gennem forespørgslen til Power Pivot. Derfra kan du kombinere de to datasæt ved at matche produktionsbatchnumre for at producere bruttomargener pr. enhed.
En anden vigtig fordel ved Power Pivot til Excel er evnen til at manipulere og arbejde med store datasæt for at drage relevante konklusioner og analyser. Jeg vil gennemgå et par almindelige eksempler nedenfor for at give dig en fornemmelse af værktøjets kraft.
Excel-junkier vil uden tvivl være enige i, at pivottabeller både er en af de mest nyttige, og samtidig en af de mest frustrerende opgaver, vi udfører. Frustrerende, især når det kommer til at arbejde med større datasæt. Heldigvis giver Power Pivot til Excel os nemt og hurtigt at oprette pivottabeller, når vi arbejder med større datasæt.
På billedet nedenfor med titlen Oprettelse af foranstaltninger , læg mærke til, hvordan Power Pivot-vinduet er adskilt i to ruder. Den øverste rude har dataene, og den nederste rude rummer målene. Et mål er en beregning, der udføres på tværs af hele datasættet. Jeg har indtastet et mål ved at skrive i den fremhævede celle.
Total Sales:=SUM('Accounting Data'[Amount])
Dette opretter et nyt mål, der summerer på tværs af kolonnen Beløb. På samme måde kan jeg skrive et andet mål i cellen nedenfor
Average Sales:=AVERAGE('Accounting Data'[Amount])
Derfra kan du se, hvor hurtigt det er at oprette en velkendt pivottabel på et stort datasæt.
Som finansanalytikere, der bruger Excel, bliver vi dygtige til at bruge indviklede formler til at bøje teknologien efter vores vilje. Vi mestrer VLOOKUP , SUMIF , og endda den frygtede INDEX(MATCH()) . Men ved at bruge Power Pivot kan vi smide meget af det ud af vinduet.
For at demonstrere denne funktionalitet oprettede jeg en lille referencetabel, hvor jeg tildelte hver kategori til en type. Ved at vælge "Tilføj til datamodel" indlæses denne tabel i Power Pivot (se Tilføjelse af en brugeroprettet tabel til en Power Pivot-model ovenfor).
Jeg har også oprettet en datotabel til brug med vores datasæt (se Oprettelse af en datotabel under). Power Pivot til Excel gør det nemt at oprette en datotabel hurtigt for at konsolidere efter måneder, kvartaler og ugedage. Brugeren kan også oprette en mere tilpasset datotabel til at analysere efter uger, regnskabsår eller enhver organisationsspecifikke grupperinger.
Udover mål er der en anden type beregning:beregnede kolonner. Excel-brugere vil være komfortable med at skrive disse formler, da de minder meget om at skrive formler i datatabeller. Jeg har oprettet en ny beregnet kolonne nedenfor (se Oprettelse af en beregnet kolonne nedenfor), som sorterer regnskabsdatatabellen efter beløb. Salg under 50 USD er mærket "Small", og alle andre er mærket "Large". Føles formlen ikke intuitiv?
Vi kan derefter oprette en relation mellem regnskabsdatatabellens kategorifelt og kategoritabellens kategorifelt ved hjælp af diagramvisningen. Derudover kan vi definere en relation mellem regnskabsdatatabellens salgsdatofelt og kalendertabellens datofelt.
Nu uden nogen SUMIF eller VLOOKUP funktioner, der er nødvendige, kan vi oprette en pivottabel, der beregnede det samlede salg efter år og type, med en slicer for Transaktionsstørrelse.
Eller vi kan oprette et diagram over det gennemsnitlige salg for hver dag i ugen ved hjælp af den nye kalendertabel.
Selvom dette diagram ser simpelt ud, er det imponerende, at det tog mindre end ti sekunder at skabe en konsolidering over to millioner rækker af data uden at tilføje en ny kolonne til salgsdataene.
Selvom vi er i stand til at udføre alle disse konsoliderede rapporteringsscenarier, kan vi altid bore ned i de individuelle linjeposter. Vi beholder vores meget detaljerede data.
Indtil videre er det meste af de analyser, jeg har vist, relativt ligefremme beregninger. Nu vil jeg demonstrere nogle af de mere avancerede funktioner på denne platform.
Når vi undersøger økonomiske resultater, ønsker vi ofte at sammenligne det med en sammenlignelig tidsramme fra det foregående år. Power Pivot har nogle indbyggede tidsintelligensfunktioner.
Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))
YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())
Hvis jeg f.eks. blot tilføjer to mål ovenfor til tabellen Regnskabsdata i Power Pivot, kan jeg producere følgende pivottabel med nogle få klik.
Som finansanalytiker er et problem, jeg ofte skal løse, problemet med uoverensstemmende granulariteter. I vores eksempel vises de faktiske salgsdata på kategoriniveau, men lad os udarbejde et budget, der kun er på sæsonniveau. For at fremme dette misforhold vil vi udarbejde et kvartalsbudget, selv gennem salgsdataene er daglige.
Med Power Pivot til Excel er denne inkonsistens let at løse. Ved at oprette to yderligere referencetabeller eller dimensionstabeller i databasenomenklaturen kan vi nu oprette de passende relationer til at analysere vores faktiske salg i forhold til de budgetterede beløb.
I Excel samles følgende pivottabel hurtigt.
Yderligere kan vi definere nye mål, der beregner variansen mellem faktisk salg og budgetteret salg som nedenfor:
Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1
Ved at bruge dette mål kan vi vise variansen på en pivottabel.
Lad os endelig undersøge salg i en bestemt kategori som en procentdel af alt salg (f.eks. kategoribidrag til det samlede salg) og salg i en bestemt kategori som en procentdel af alt salg af samme type (f.eks. kategoribidrag til sæsonbestemt type) salg). Jeg oprettede de to mål nedenfor:
Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'))
Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))
Disse foranstaltninger kan nu implementeres i en ny pivottabel:
Bemærk, hvordan beregningerne udføres ved begge kategorien og sæsontypeniveauet. Jeg elsker, hvor hurtigt og ubesværet disse beregninger udføres på et så stort datasæt. Dette er blot nogle få eksempler på Power Pivot's elegance og rene regnekraft.
En anden fordel er, at filstørrelser krymper. Den oprindelige filstørrelse var 91 MB, og nu er den under 4 MB. Det er en komprimering på 96 % af den originale fil.
Hvordan sker dette? Power Pivot bruger xVelocity-motoren til at komprimere dataene. Enkelt sagt gemmes dataene i kolonner i stedet for rækker. Denne lagringsmetode gør det muligt for computeren at komprimere duplikerede værdier. I vores eksempeldatasæt er der kun fire regioner, der gentages over alle to millioner rækker. Power Pivot til Excel kan mere effektivt gemme disse data. Resultatet er, at for data, der har mange gentagne værdier, koster det meget mindre at gemme disse data.
En ting at bemærke er, at jeg brugte hele dollarbeløb i dette eksempeldatasæt. Hvis jeg havde inkluderet to decimaler for at afspejle cents, ville komprimeringseffekten mindskes til stadig imponerende 80 % af den oprindelige filstørrelse.
Power Pivot-modeller kan også skaleres til hele virksomheden. Lad os sige, at du bygger en Power Pivot-model, der begynder at få mange brugere i organisationen, eller at dataene vokser til ti millioner rækker, eller begge dele. På dette tidspunkt ønsker du måske ikke, at tredive forskellige brugere skal opdatere modellen eller foretage ændringer. Modellen kan problemfrit konverteres til SSAS Tabular. Alle tabeller og relationer bibeholdes, men nu kan du styre opdateringsfrekvensen, tildele roller (f.eks. skrivebeskyttet, læse og behandle) til forskellige brugere og kun implementere en lille Excel-frontend, som linker til tabelmodellen. Resultatet er, at dine brugere derefter kunne få adgang til den implementerede tabelmodel med en lille projektmappe, men ikke have adgang til formlerne og målene.
En af mine kunders konstante ønsker er, at jeg laver rapportering, der er i overensstemmelse med et nøje defineret layout. Jeg har klienter, der anmoder om specifikke kolonnebredder, RGB-farvekoder og foruddefinerede skrifttypenavne og -størrelser. Overvej følgende dashboard:
Hvordan udfylder vi salgstallene uden at generere pivottabeller, hvis alt vores salg er placeret med Power Pivot til Excel? Brug af CUBE-formler! Vi kan skrive CUBE-formler i enhver Excel-celle, og den vil udføre beregningen ved hjælp af Power Pivot-modellen, vi allerede har konstrueret.
For eksempel indtastes følgende formel i cellen under "2016 Samlet salg:"
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[2016]")
Den første del af formlen, fremhævet med gult, henviser til navnet på Power Pivot-modellen. Generelt er det normalt ThisWorkbookDataModel til nyere versioner af Power Pivot til Excel. Delen med grønt definerer, at vi ønsker at bruge målet Samlet salg. Delen i blå instruerer Excel til kun at filtrere efter rækker, der har en salgsdato med et år svarende til 2016.
Bag kulisserne har Power Pivot konstrueret en OLAP-kube (Online Analytical Processing) med data, beregnede kolonner og mål. Dette design giver Excel-brugeren mulighed for derefter at få adgang til dataene ved at hente direkte med CUBE-funktionerne. Ved hjælp af CUBE-formler har jeg været i stand til at konstruere fuldstændige regnskaber, der er i overensstemmelse med foruddefinerede layouts. Denne funktion er et af højdepunkterne ved at bruge Power Pivot til Excel til finansiel analyse.
En anden fordel ved Power Pivot til Excel er, at du hurtigt kan tage enhver Power Pivot-projektmappe, du bygger, og hurtigt konvertere den til en Power BI-model. Ved at importere Excel-projektmappen direkte til Power BI Desktop-appen eller Power BI Online kan du analysere, visualisere og dele dine data med alle i din organisation. I bund og grund er Power BI Power Pivot, PowerQuery og SharePoint samlet i én. Nedenfor har jeg oprettet et dashboard ved at importere den tidligere Power Pivot til Excel-projektmappe til Power BI-skrivebordsapplikationen. Bemærk, hvor interaktiv grænsefladen er:
En stor ting ved Power BI er Natural Language Q&A. For at demonstrere uploadede jeg Power BI-modellen til min online Power BI-konto. Fra hjemmesiden kan jeg stille spørgsmål, og Power BI konstruerer den passende analyse, mens jeg skriver:
Denne type forespørgselsevne gør det muligt for brugeren at stille spørgsmål til datamodellen og interagere med dataene på en nemmere måde end i Excel.
En anden fordel ved Power BI er, at udviklerne hos Microsoft konstant udgiver opdateringer til det. Nye funktioner, mange efterspurgte af brugere, skubbes ud hver måned. Det bedste af det hele er, at det er en problemfri overgang fra Power Pivot til Excel. Så den tid, du har investeret i at lære DAX-formlerne, kan implementeres i Power BI! For analytikeren, der har brug for at dele sin analyse med mange brugere på forskellige enheder, kan Power BI være værd at udforske.
Når du kommer i gang, er der et par bedste fremgangsmåder, som du bør følge.
Den første er at omhyggeligt beslutte, hvad der skal importeres i første omgang. Vil du nogensinde bruge sælgerens hjemmeadresse? Skal jeg kende min kundes e-mailadresse i forbindelse med denne projektmappe? Hvis målet er at samle dataene i et dashboard, vil nogle af de data, der er tilgængelige, ikke være nødvendige for disse beregninger. At bruge tid på at kurere de data, der kommer ind, vil i høj grad afhjælpe problemer og hukommelsesbrug senere, når dit datasæt udvides.
En anden bedste praksis er at huske, at Power Pivot ikke er Excel. I Excel er vi vant til at lave beregninger ved hele tiden at udvide vores arbejdsark til højre. Power Pivot til Excel behandler dataene mest effektivt, hvis vi begrænser dette ønske om åbenbar skæbne. I stedet for løbende at oprette beregnede kolonner til højre for dine data, kan du lære at skrive mål i den nederste rude. Denne vane vil sikre mindre filstørrelser og hurtigere beregninger.
Til sidst vil jeg foreslå at bruge almindelige engelske navne til mål. Det tog mig lang tid at adoptere denne her. Jeg brugte de første par år på at finde på navne som SumExpPctTotal , men da andre mennesker begyndte at bruge de samme projektmapper, havde jeg en masse at forklare. Nu, når jeg starter en ny projektmappe, bruger jeg målnavne som Expense Line Item as Percent of Total Expenses . Selvom navnet er længere, er det meget nemmere for en anden at bruge.
I denne artikel har jeg kun præsenteret en håndfuld af de måder, hvorpå Power Pivot til Excel giver dig mulighed for at tage et vigtigt skridt ud over almindelig vanilje Excel. Jeg tænkte, at det ville være nyttigt at fremhæve nogle virkelige brugssager, hvor jeg har fundet ud af, at Power Pivot til Excel er yderst nyttig.
Her er nogle:
Som finansanalytikere er vi forpligtet til at udføre komplekse beregninger på stadigt voksende datasæt. Da Excel allerede er standardanalyseværktøjet, er Power Pivot-indlæringskurven nem, og mange af funktionerne afspejler Excels oprindelige funktioner.
Med brugen af CUBE-funktioner smelter Power Pivot til Excel problemfrit ind i dine eksisterende Excel-projektmapper. Den beregningsmæssige effektivitetsgevinst kan ikke overses. Forudsat en 20 % hurtigere behandlingshastighed, hvilket er konservativt, kan finansanalytikeren, der bruger seks timer om dagen i Excel, spare 300 timer om året!
Derudover kan vi nu analysere datasæt, der er meget større, end vi tidligere kunne med vores traditionelle Excel. Med modeller, der er designet effektivt, kan vi nemt have 10 gange den mængde data, som vi tidligere var tilladt i traditionel Excel, samtidig med at vi bevarer hurtig analytisk smidighed. Med muligheden for at konvertere modellerne fra Power Pivot til SSAS Tabular er mængden af data, der kan behandles 100-1.000 gange, hvad vi kan opnå i Excel.
Power Pivot til Excels evne til at udføre lynhurtige beregninger på store mængder data og stadig bevare evnen til at dykke ned i detaljerne kan transformere økonomiske analyser fra klodsede regneark til moderne projektmapper.
Hvis du er interesseret i at prøve Power Pivot til Excel, er der nogle nyttige materialer nedenfor til at komme i gang.
Collie, R., &Singh, A. (2016). Power Pivot og Power BI:Excel-brugervejledningen til DAX, Power Query, Power BI og Power Pivot i Excel 2010-2016. USA:Hellig makro! Bøger.
Ferrari, A., &Russo, M. (2015). Den definitive guide til DAX:Business intelligence med Microsoft Excel, SQL Server Analysis Services og Power BI. USA:Microsoft Press, USA.
19 topaktier for en svag amerikansk dollar
Skalering af et økosystem på 500 mia. USD+:Enterprise Use Cases for Ethereum Layer 2-løsninger
Amerikanernes topmål - og største frygt - for de næste 4 år
De 5 bedste byer for Robocalls - og hvordan man stopper de irriterende beskeder
Amerikanernes top 7 pensionsprioriteter for Biden og Kongressen