version av denna artikel översatt av Marisela Ordaz Ladda ned datamängden här för att följa med självstudien.
Över de olika fälten och underdelfälten som spänner över finans, finansiell analys, finansiella marknader och finansiella investeringar är Microsoft Excel kung. Med ankomsten och den exponentiella tillväxten av big data, dock driven av årtionden av dataaggregering och ackumulering, tillkomsten av billig molnlagring och framväxten av sakernas internet – det vill säga e-handel, sociala medier och enheternas sammankopplingar – Excels äldre funktionalitet och kapacitet har pressats till sina gränser.
Mer specifikt, den äldre generationens Excels infrastruktur och bearbetningsbegränsningar, såsom radgränsen på 1 048 576 rader, eller oundviklig nedgång i bearbetningen när det gäller stora datamängder, datatabeller och sammanlänkade kalkylblad, minskade dess användbarhet som ett effektivt big data-verktyg. Under 2010 lade Microsoft dock till en ny dimension till Excel, kallad Power Pivot. Power Pivot erbjöd nästa generations affärsintelligens och affärsanalysfunktioner till Excel i dess förmåga att extrahera, kombinera och analysera nästan obegränsade datauppsättningar utan försämring av bearbetningshastigheten. Trots att den släpptes för åtta år sedan vet de flesta finansanalytiker fortfarande inte hur man använder Power Pivot, och många vet inte att den ens existerar.
I den här artikeln kommer jag att visa dig hur du använder Power Pivot för att övervinna vanliga Excel-problem och ta en titt på ytterligare viktiga fördelar med programvaran med några exempel. Denna Power Pivot-handledning är tänkt att fungera som en guide för vad du kan uppnå med det här verktyget, och i slutet kommer den att utforska några exempel på användningsfall där Power Pivot ofta visar sig vara ovärderlig.
Power Pivot är en funktion i Microsoft Excel som introducerades som ett tillägg till Excel 2010 och 2013 och är nu en inbyggd funktion för Excel 2016 och 365. Som Microsoft förklarar gör Power Pivot för Excel "möjlig att importera miljontals rader av data från flera datakällor till en enda Excel-arbetsbok, skapa relationer mellan heterogena data, skapa beräknade kolumner och mått med hjälp av formler, bygga pivottabeller och pivotdiagram och analysera sedan data ytterligare så att du kan fatta affärsbeslut i rätt tid utan att behöva IT-hjälp. ”
Det primära uttrycksspråket som Microsoft använder i Power Pivot är DAX (Data Analysis Expressions), även om andra kan användas i specifika situationer. Återigen, som Microsoft förklarar, "DAX är en samling funktioner, operatorer och konstanter som kan användas i en formel eller ett uttryck för att beräkna och returnera ett eller flera värden. Enklare uttryckt hjälper DAX dig att skapa ny information från data som redan finns i din modell." Lyckligtvis för de som redan är bekanta med Excel kommer DAX-formler att se bekanta ut, eftersom många av formlerna har en liknande syntax (t.ex. SUM , AVERAGE , TRUNC ).
För tydlighetens skull kan de viktigaste fördelarna med att använda Power Pivot kontra grundläggande Excel sammanfattas som följande:
I följande avsnitt går jag igenom vart och ett av ovanstående och visar hur Power Pivot för Excel kan vara till hjälp.
Som tidigare antytts är en av de största begränsningarna i Excel att arbeta med extremt stora datamängder. Lyckligtvis för oss kan Excel nu ladda långt över gränsen på en miljon rader direkt i Power Pivot.
För att demonstrera detta genererade jag ett exempel på en datauppsättning av två års försäljning för en återförsäljare av sportartiklar med nio olika produktkategorier och fyra regioner. Den resulterande datamängden är två miljoner rader.
Använda Data fliken på menyfliksområdet skapade jag en Ny fråga från CSV-filen (se Skapa en ny fråga Nedan). Denna funktion hette tidigare PowerQuery, men från och med Excel 2016 och 365 var den mer integrerad i fliken Data i Excel.
Från en tom arbetsbok i Excel till att ladda alla två miljoner rader i Power Pivot, det tog ungefär en minut! Lägg märke till att jag kunde utföra lite lätt dataformatering genom att marknadsföra den första raden för att bli kolumnnamn. Under de senaste åren har Power Query-funktionen förbättrats avsevärt från ett Excel-tillägg till en tätt integrerad del av fliken Data i verktygsfältet. Power Query kan pivotera, platta, rensa och forma din data genom sin uppsättning alternativ och sitt eget språk, M.
En av de andra viktiga fördelarna med Power Pivot för Excel är möjligheten att enkelt importera data från flera källor. Tidigare skapade många av oss flera kalkylblad för våra olika datakällor. Ofta involverade denna process att skriva VBA-kod och kopiera/klistra in från dessa olika källor. Lyckligtvis för oss låter Power Pivot dig dock importera data från olika datakällor direkt till Excel utan att behöva stöta på problemen som nämns ovan.
Genom att använda frågefunktionen i Bilaga 1 kan vi hämta från någon av följande källor:
Vidare kan flera datakällor kombineras antingen i Query-funktionen eller i Power Pivot-fönstret för att integrera data. Du kan till exempel hämta produktionskostnadsdata från en Excel-arbetsbok och faktiska försäljningsresultat från SQL-servern genom frågan till Power Pivot. Därifrån kan du kombinera de två datamängderna genom att matcha produktionsbatchnummer för att producera bruttomarginaler per enhet.
En annan viktig fördel med Power Pivot för Excel är förmågan att manipulera och arbeta med stora datamängder för att dra relevanta slutsatser och analyser. Jag ska gå igenom några vanliga exempel nedan för att ge dig en känsla av kraften i verktyget.
Excel-junkies kommer utan tvekan att hålla med om att pivottabeller är både en av de mest användbara och samtidigt en av de mest frustrerande uppgifterna vi utför. Frustrerande särskilt när det gäller att arbeta med större datamängder. Lyckligtvis tillåter Power Pivot för Excel oss att enkelt och snabbt skapa pivottabeller när vi arbetar med större uppsättningar data.
I bilden nedan, med titeln Skapa åtgärder , lägg märke till hur Power Pivot-fönstret är uppdelat i två rutor. Den övre rutan har data och den nedre rutan innehåller åtgärderna. Ett mått är en beräkning som utförs över hela datamängden. Jag har angett ett mått genom att skriva i den markerade cellen.
Total Sales:=SUM('Accounting Data'[Amount])
Detta skapar ett nytt mått som summerar över kolumnen Belopp. På samma sätt kan jag skriva ett annat mått i cellen nedan
Average Sales:=AVERAGE('Accounting Data'[Amount])
Därifrån kan du se hur snabbt det går att skapa en välbekant pivottabell på en stor datamängd.
Som finansanalytiker som använder Excel blir vi skickliga på att använda invecklade formler för att böja tekniken efter vår vilja. Vi behärskar VLOOKUP , SUMIF , och till och med den fruktade INDEX(MATCH()) . Men genom att använda Power Pivot kan vi kasta mycket av det ut genom fönstret.
För att demonstrera denna funktion skapade jag en liten referenstabell där jag tilldelade varje kategori till en typ. Genom att välja "Lägg till i datamodell" läses den här tabellen in i Power Pivot (se Lägga till en användarskapad tabell till en Power Pivot-modell ovan).
Jag skapade också en datumtabell att använda med vår datauppsättning (se Skapa en datumtabell Nedan). Power Pivot för Excel gör det enkelt att snabbt skapa en datumtabell för att konsolidera efter månader, kvartal och veckodagar. Användaren kan också skapa en mer anpassad datumtabell för att analysera efter veckor, räkenskapsår eller organisationsspecifika grupperingar.
Förutom mått finns det en annan typ av beräkning:beräknade kolumner. Excel-användare kommer att vara bekväma med att skriva dessa formler, eftersom de påminner mycket om att skriva formler i datatabeller. Jag har skapat en ny beräknad kolumn nedan (se Skapa en beräknad kolumn nedan) som sorterar redovisningsdatatabellen efter belopp. Försäljning under 50 USD är märkt "Small" och alla andra är märkta som "Large". Känns inte formeln intuitiv?
Vi kan sedan skapa en relation mellan bokföringsdatatabellens kategorifält och kategoritabellens kategorifält med hjälp av diagramvyn. Dessutom kan vi definiera en relation mellan bokföringsdatatabellens försäljningsdatumfält och kalendertabellens datumfält.
Nu utan någon SUMIF eller VLOOKUP funktioner som behövs, kan vi skapa en pivottabell som beräknar total försäljning per år och typ, med en slicer för transaktionsstorlek.
Eller så kan vi skapa ett diagram över genomsnittlig försäljning för varje dag i veckan med den nya kalendertabellen.
Även om det här diagrammet ser enkelt ut, är det imponerande att det tog mindre än tio sekunder att skapa en konsolidering över två miljoner rader med data, utan att lägga till en ny kolumn i försäljningsdata.
Samtidigt som vi kan utföra alla dessa konsoliderade rapporteringsscenarier kan vi alltid gå ner i de enskilda raderna. Vi behåller vår mycket detaljerade data.
Hittills är det mesta av de analyser jag har visat relativt enkla beräkningar. Nu vill jag demonstrera några av de mer avancerade funktionerna hos den här plattformen.
När vi granskar ekonomiska resultat vill vi ofta jämföra det med en jämförbar tidsram från föregående år. Power Pivot har några inbyggda 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())
Om jag till exempel bara lägger till två mått ovan i tabellen Redovisningsdata i Power Pivot kan jag skapa följande pivottabell med några få klick.
Som finansanalytiker är ett problem jag ofta måste lösa problemet med bristande granulariteter. I vårt exempel visas den faktiska försäljningsdatan på kategorinivå, men låt oss förbereda en budget som bara är på säsongsnivå. För att främja denna oöverensstämmelse kommer vi att förbereda en kvartalsbudget, även om försäljningsdata är dagligen.
Med Power Pivot för Excel är denna inkonsekvens lätt att lösa. Genom att skapa ytterligare två referenstabeller, eller dimensionstabeller i databasnomenklaturen, kan vi nu skapa lämpliga relationer för att analysera vår faktiska försäljning mot de budgeterade beloppen.
I Excel samlas följande pivottabell snabbt.
Vidare kan vi definiera nya mått som beräknar variansen mellan faktisk försäljning och budgeterad försäljning enligt nedan:
Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1
Med detta mått kan vi visa variansen på en pivottabell.
Låt oss slutligen undersöka försäljningen i en viss kategori som procent av all försäljning (t.ex. kategoribidrag till den totala försäljningen) och försäljning i en viss kategori som procent av all försäljning av samma typ (t.ex. kategoribidrag till säsongstyp). försäljning). Jag skapade de två måtten nedan:
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]))
Dessa åtgärder kan nu distribueras i en ny pivottabell:
Lägg märke till hur beräkningarna utförs vid båda kategorin och säsongstypnivån. Jag älskar hur snabbt och enkelt dessa beräkningar utförs på en så stor datamängd. Det här är bara några exempel på elegansen och ren beräkningskraft hos Power Pivot.
En annan fördel är att filstorlekarna krymper. Den ursprungliga filstorleken var 91 MB, och nu är den under 4 MB. Det är en komprimering av 96 % av originalfilen.
Hur går det till? Power Pivot använder xVelocity-motorn för att komprimera data. Enkelt uttryckt lagras data i kolumner snarare än rader. Denna lagringsmetod låter datorn komprimera dubbletter av värden. I vår exempeldatauppsättning finns det bara fyra regioner som upprepas över alla två miljoner rader. Power Pivot för Excel kan mer effektivt lagra denna data. Resultatet är att för data som har många återkommande värden kostar det mycket mindre att lagra denna data.
En sak att notera är att jag använde hela dollarbelopp i denna exempeldatauppsättning. Om jag hade inkluderat två decimaler för att återspegla cent, skulle komprimeringseffekten minska till fortfarande imponerande 80 % av den ursprungliga filstorleken.
Power Pivot-modeller kan också vara skalbara för hela företaget. Låt oss säga att du bygger en Power Pivot-modell som börjar få många användare i organisationen, eller så växer data till tio miljoner rader, eller båda. Vid det här laget kanske du inte vill att trettio olika användare uppdaterar modellen eller gör ändringar. Modellen kan sömlöst konverteras till SSAS Tabular. Alla tabeller och relationer behålls, men nu kan du styra uppdateringsfrekvensen, tilldela roller (t.ex. skrivskyddad, läs och bearbeta) till olika användare och distribuera endast ett litet Excel-gränssnitt som länkar till tabellmodellen. Resultatet är att dina användare sedan kan komma åt den distribuerade tabellmodellen med en liten arbetsbok, men inte ha tillgång till formlerna och måtten.
En av mina kunders ständiga önskemål är att jag skapar rapportering som följer en strikt definierad layout. Jag har klienter som begär specifika kolumnbredder, RGB-färgkoder och fördefinierade teckensnittsnamn och storlekar. Tänk på följande instrumentpanel:
Hur fyller vi i försäljningssiffrorna utan att generera pivottabeller om all vår försäljning är inrymd med Power Pivot för Excel? Använder CUBE-formler! Vi kan skriva CUBE-formler i vilken Excel-cell som helst och den kommer att utföra beräkningen med Power Pivot-modellen som vi redan har konstruerat.
Till exempel skrivs följande formel i cellen under "Total försäljning 2016:"
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[2016]")
Den första delen av formeln, markerad i gult, hänvisar till namnet på Power Pivot-modellen. I allmänhet är det vanligtvis ThisWorkbookDataModel för nyare versioner av Power Pivot för Excel. Delen i grönt anger att vi vill använda måttet Total försäljning. Delen i blått instruerar Excel att filtrera efter endast rader som har ett försäljningsdatum med ett år lika med 2016.
Bakom kulisserna har Power Pivot konstruerat en OLAP-kub (Online Analytical Processing) med data, beräknade kolumner och mått. Denna design gör att Excel-användaren sedan kan komma åt data genom att hämta direkt med CUBE-funktionerna. Med hjälp av CUBE-formler har jag kunnat konstruera fullständiga finansiella rapporter som överensstämmer med fördefinierade layouter. Denna förmåga är en av höjdpunkterna med att använda Power Pivot för Excel för finansiell analys.
En annan fördel med Power Pivot för Excel är att du snabbt kan ta vilken Power Pivot-arbetsbok du bygger och snabbt konvertera den till en Power BI-modell. Genom att importera Excel-arbetsboken direkt till Power BI Desktop-appen eller Power BI Online kan du analysera, visualisera och dela dina data med vem som helst i din organisation. I huvudsak är Power BI Power Pivot, PowerQuery och SharePoint samlade i ett. Nedan har jag skapat en instrumentpanel genom att importera den tidigare Power Pivot för Excel-arbetsboken till Power BI-skrivbordsapplikationen. Lägg märke till hur interaktivt gränssnittet är:
En bra sak med Power BI är Natural Language Q&A. För att demonstrera laddade jag upp Power BI-modellen till mitt Power BI-konto online. Från webbplatsen kan jag ställa frågor och Power BI konstruerar lämplig analys medan jag skriver:
Denna typ av frågeförmåga gör det möjligt för användaren att ställa frågor om datamodellen och interagera med data på ett enklare sätt än i Excel.
En annan fördel med Power BI är att utvecklarna på Microsoft ständigt släpper uppdateringar till den. Nya funktioner, många efterfrågade av användare, skjuts ut varje månad. Det bästa av allt är att det är en sömlös övergång från Power Pivot för Excel. Så den tid du investerade i att lära dig DAX-formlerna kan användas i Power BI! För analytikern som behöver dela sin analys till många användare på olika enheter kan Power BI vara värt att utforska.
När du väl kommit igång finns det några bästa metoder som du bör följa.
Det första är att i första hand bestämma vad som ska importeras. Kommer du någonsin att använda säljarens hemadress? Behöver jag känna till min kunds e-postadress i samband med denna arbetsbok? Om målet är att aggregera data till en instrumentpanel, kommer en del av den data som är tillgänglig inte att behövas för dessa beräkningar. Att spendera tid på att kurera in data som kommer in kommer att avsevärt lindra problem och minnesanvändning senare när din datauppsättning expanderar.
En annan bästa praxis är att komma ihåg att Power Pivot inte är Excel. I Excel är vi vana vid att skapa beräkningar genom att hela tiden utöka våra kalkylblad till höger. Power Pivot för Excel bearbetar data mest effektivt om vi begränsar denna önskan om uppenbart öde. Istället för att kontinuerligt skapa beräknade kolumner till höger om din data, lär du dig att skriva mått i den nedre rutan. Denna vana kommer att säkerställa mindre filstorlekar och snabbare beräkningar.
Slutligen skulle jag föreslå att du använder vanliga engelska namn för mått. Den här tog mig lång tid att adoptera. Jag tillbringade de första åren med att hitta på namn som SumExpPctTotal , men när andra människor började använda samma arbetsböcker hade jag mycket att förklara. Nu, när jag startar en ny arbetsbok, använder jag måttnamn som Expense Line Item as Percent of Total Expenses . Även om namnet är längre är det mycket lättare för någon annan att använda.
I den här artikeln har jag bara presenterat en handfull sätt på vilka Power Pivot för Excel låter dig ta ett viktigt steg bortom vanlig vanilj Excel. Jag tänkte att det skulle vara användbart att lyfta fram några verkliga användningsfall där jag har funnit att Power Pivot för Excel är extremt användbart.
Här är några:
Som finansanalytiker måste vi utföra komplexa beräkningar på ständigt växande datamängder. Eftersom Excel redan är standardverktyget för analys är Power Pivot-inlärningskurvan enkel, och många av funktionerna speglar Excels inbyggda funktioner.
Med hjälp av CUBE-funktioner smälter Power Pivot för Excel sömlöst in i dina befintliga Excel-arbetsböcker. Beräkningseffektiviteten kan inte förbises. Om man antar en 20 % snabbare bearbetningshastighet, vilket är konservativt, kan finansanalytikern som spenderar sex timmar om dagen i Excel spara 300 timmar om året!
Dessutom kan vi nu analysera datauppsättningar som är mycket större än vad vi tidigare kunde med vår traditionella Excel. Med modeller designade effektivt kan vi enkelt ha 10 gånger den mängd data som vi tidigare tilläts i traditionell Excel, samtidigt som vi bibehåller snabb analytisk smidighet. Med möjligheten att konvertera modellerna från Power Pivot till SSAS Tabular är mängden data som kan bearbetas 100–1 000 gånger vad vi kan uppnå i Excel.
Power Pivot för Excels förmåga att utföra blixtsnabba beräkningar på stora mängder data och ändå behålla förmågan att dyka in i detaljerna kan förvandla finansiell analys från klumpiga kalkylblad till moderna arbetsböcker.
Om du är intresserad av att testa Power Pivot för Excel, nedan är några användbara material för att komma igång.
Collie, R., &Singh, A. (2016). Power Pivot och Power BI:Excel-användarguiden till DAX, Power Query, Power BI och Power Pivot i Excel 2010-2016. USA:Heligt makro! Böcker.
Ferrari, A., &Russo, M. (2015). Den definitiva guiden till DAX:Business Intelligence med Microsoft Excel, SQL Server Analysis Services och Power BI. USA:Microsoft Press, USA.
19 toppaktier för en svag amerikansk dollar
Skala ett ekosystem till $500 miljarder+:Företagsanvändningsfall för Ethereum Layer 2-lösningar
Amerikanernas främsta mål – och största rädslor – för de kommande fyra åren
De fem bästa städerna för Robocalls - och hur man stoppar de irriterande meddelandena
Amerikanernas topp 7 pensionsprioriteringar för Biden och kongressen
De bästa pengarna förutsägelser för 2022
Uppskattning av WACC för värdering av privata företag:en handledning
Python och ekonomi – Förstärk dina kalkylblad