versie van dit artikel vertaald door Marisela Ordaz Download de dataset hier om samen met de tutorial te volgen.
Over de verschillende velden en sub-subvelden die financiën, financiële analyse, financiële markten en financieel beleggen omvatten, is Microsoft Excel koning. Met de komst en exponentiële groei van big data, echter aangedreven door tientallen jaren van gegevensaggregatie en accumulatie, de komst van goedkope cloudopslag en de opkomst van het internet der dingen, d.w.z. e-commerce, sociale media en de onderlinge verbondenheid van apparaten, is Excel's legacy functionaliteit en mogelijkheden zijn tot het uiterste gedreven.
Meer specifiek hebben de infrastructuur en verwerkingsbeperkingen van de oudere generatie Excel, zoals de rijlimiet van 1.048.576 rijen, of de onvermijdelijke vertraging van de verwerking bij grote datasets, datatabellen en onderling verbonden spreadsheets, de bruikbaarheid ervan als een effectief hulpmiddel voor big data verminderd. In 2010 voegde Microsoft echter een nieuwe dimensie toe aan Excel, genaamd Power Pivot. Power Pivot bood Excel de volgende generatie functionaliteit voor business intelligence en bedrijfsanalyse, omdat het bijna onbeperkte datasets kan extraheren, combineren en analyseren zonder dat de verwerkingssnelheid wordt aangetast. Ondanks de release acht jaar geleden weten de meeste financiële analisten echter nog steeds niet hoe ze Power Pivot moeten gebruiken, en velen weten niet eens dat het bestaat.
In dit artikel laat ik u zien hoe u Power Pivot kunt gebruiken om veelvoorkomende Excel-problemen op te lossen en bekijk ik aan de hand van enkele voorbeelden aanvullende belangrijke voordelen van de software. Deze Power Pivot-zelfstudie is bedoeld als richtlijn voor wat u met deze tool kunt bereiken, en aan het einde zal het enkele voorbeelden van gebruikssituaties onderzoeken waarbij Power Pivot vaak van onschatbare waarde blijkt te zijn.
Power Pivot is een functie van Microsoft Excel die werd geïntroduceerd als een invoegtoepassing voor Excel 2010 en 2013, en is nu een native functie voor Excel 2016 en 365. Zoals Microsoft uitlegt, kunt u met Power Pivot voor Excel "miljoenen rijen importeren van gegevens uit meerdere gegevensbronnen in een enkele Excel-werkmap, maak relaties tussen heterogene gegevens, maak berekende kolommen en metingen met behulp van formules, bouw draaitabellen en draaigrafieken en analyseer de gegevens vervolgens verder zodat u tijdig zakelijke beslissingen kunt nemen zonder dat IT-hulp nodig is. ”
De primaire expressietaal die Microsoft gebruikt in Power Pivot is DAX (Data Analysis Expressions), hoewel andere in specifieke situaties kunnen worden gebruikt. Nogmaals, zoals Microsoft uitlegt:"DAX is een verzameling functies, operators en constanten die in een formule of uitdrukking kunnen worden gebruikt om een of meer waarden te berekenen en te retourneren. Eenvoudiger gezegd, DAX helpt u nieuwe informatie te creëren uit gegevens die al in uw model aanwezig zijn.” Gelukkig voor degenen die al bekend zijn met Excel, zullen DAX-formules er bekend uitzien, aangezien veel van de formules een vergelijkbare syntaxis hebben (bijv. SUM , AVERAGE , TRUNC ).
Voor de duidelijkheid:de belangrijkste voordelen van het gebruik van Power Pivot versus standaard Excel kunnen als volgt worden samengevat:
In de volgende secties zal ik elk van de bovenstaande punten doornemen en laten zien hoe Power Pivot voor Excel nuttig kan zijn.
Zoals eerder vermeld, heeft een van de belangrijkste beperkingen van Excel betrekking op het werken met extreem grote datasets. Gelukkig voor ons kan Excel nu ruim boven de limiet van één miljoen rijen rechtstreeks in Power Pivot laden.
Om dit aan te tonen, heb ik een voorbeelddataset gegenereerd met een omzet van twee jaar voor een detailhandelaar in sportartikelen met negen verschillende productcategorieën en vier regio's. De resulterende dataset is twee miljoen rijen.
De Gegevens gebruiken tabblad op het lint, heb ik een Nieuwe zoekopdracht . gemaakt uit het CSV-bestand (zie Een nieuwe query maken onderstaand). Deze functionaliteit heette vroeger PowerQuery, maar was vanaf Excel 2016 en 365 strakker geïntegreerd in het tabblad Gegevens van Excel.
Van een lege werkmap in Excel tot het laden van alle twee miljoen rijen in Power Pivot, het duurde ongeveer een minuut! Merk op dat ik wat lichte gegevensopmaak kon uitvoeren door de eerste rij te promoten om de kolomnamen te worden. In de afgelopen jaren is de Power Query-functionaliteit enorm verbeterd van een Excel-invoegtoepassing tot een nauw geïntegreerd onderdeel van het tabblad Gegevens op de werkbalk. Power Query kan uw gegevens draaien, afvlakken, opschonen en vormgeven via zijn reeks opties en zijn eigen taal, M.
Een van de andere belangrijke voordelen van Power Pivot voor Excel is de mogelijkheid om eenvoudig gegevens uit meerdere bronnen te importeren. Voorheen hebben velen van ons meerdere werkbladen gemaakt voor onze verschillende gegevensbronnen. Vaak omvatte dit proces het schrijven van VBA-code en het kopiëren/plakken van deze verschillende bronnen. Gelukkig voor ons stelt Power Pivot u in staat om gegevens uit verschillende gegevensbronnen rechtstreeks in Excel te importeren zonder de bovengenoemde problemen tegen te komen.
Met behulp van de Query-functie in Bewijsstuk 1, kunnen we uit elk van de volgende bronnen halen:
Verder kunnen meerdere gegevensbronnen worden gecombineerd in de Query-functie of in het Power Pivot-venster om gegevens te integreren. U kunt bijvoorbeeld productiekostengegevens uit een Excel-werkmap halen en werkelijke verkoopresultaten van SQL-server via de query naar Power Pivot. Van daaruit kunt u de twee datasets combineren door productiebatchnummers te matchen om brutomarges per eenheid te produceren.
Een ander belangrijk voordeel van Power Pivot voor Excel is de mogelijkheid om grote datasets te manipuleren en ermee te werken om relevante conclusies en analyses te trekken. Ik zal hieronder enkele veelvoorkomende voorbeelden doornemen om u een idee te geven van de kracht van de tool.
Excel-junkies zullen het er ongetwijfeld mee eens zijn dat draaitabellen zowel een van de nuttigste als tegelijkertijd een van de meest frustrerende taken zijn die we uitvoeren. Frustrerend vooral als het gaat om het werken met grotere datasets. Gelukkig kunnen we met Power Pivot voor Excel gemakkelijk en snel draaitabellen maken wanneer we met grotere gegevenssets werken.
In de onderstaande afbeelding, getiteld Maatregelen creëren , merk op hoe het Power Pivot-venster is opgedeeld in twee deelvensters. Het bovenste deelvenster bevat de gegevens en het onderste deelvenster bevat de metingen. Een meting is een berekening die over de gehele dataset wordt uitgevoerd. Ik heb een maat ingevoerd door in de gemarkeerde cel te typen.
Total Sales:=SUM('Accounting Data'[Amount])
Hiermee wordt een nieuwe meetwaarde gemaakt die optellen in de kolom Bedrag. Op dezelfde manier kan ik een andere maat typen in de cel hieronder
Average Sales:=AVERAGE('Accounting Data'[Amount])
Bekijk vanaf daar hoe snel het is om een bekende draaitabel te maken op een grote dataset.
Als financiële analisten die Excel gebruiken, worden we bedreven in het gebruik van ingewikkelde formules om de technologie naar onze hand te zetten. We beheersen VLOOKUP , SUMIF , en zelfs de gevreesde INDEX(MATCH()) . Door Power Pivot te gebruiken, kunnen we veel daarvan echter uit het raam gooien.
Om deze functionaliteit te demonstreren, heb ik een kleine referentietabel gemaakt waarin ik elke categorie aan een type heb toegewezen. Door 'Toevoegen aan gegevensmodel' te kiezen, wordt deze tabel in Power Pivot geladen (zie Een door de gebruiker gemaakte tabel toevoegen aan een Power Pivot-model hierboven).
Ik heb ook een datumtabel gemaakt om te gebruiken met onze dataset (zie Een datumtabel maken onderstaand). Power Pivot voor Excel maakt het gemakkelijk om snel een datumtabel te maken om te consolideren op maanden, kwartalen en dagen van de week. De gebruiker kan ook een meer aangepaste datumtabel maken om te analyseren op weken, fiscale jaren of organisatiespecifieke groeperingen.
Naast metingen is er nog een ander type berekening:berekende kolommen. Excel-gebruikers zullen deze formules gemakkelijk kunnen schrijven, omdat ze erg lijken op het schrijven van formules in datatabellen. Ik heb hieronder een nieuwe berekende kolom gemaakt (zie Een berekende kolom maken hieronder) die de tabel Boekhoudkundige gegevens sorteert op Bedrag. Verkopen onder de $ 50 krijgen het label 'Klein' en alle andere worden 'Groot' genoemd. Voelt de formule niet intuïtief aan?
Met behulp van de diagramweergave kunnen we vervolgens een relatie leggen tussen het veld Categorie van de tabel Boekhoudgegevens en het veld Categorie van de tabel. Bovendien kunnen we een relatie definiëren tussen het veld Verkoopdatum van de tabel Boekhoudkundige gegevens en het veld Datum van de tabel Kalender.
Nu, zonder SUMIF of VLOOKUP functies nodig hebben, kunnen we een draaitabel maken die de totale verkoop per jaar en type berekent, met een slicer voor transactiegrootte.
Of we kunnen een grafiek maken van de gemiddelde verkoop voor elke dag van de week met behulp van de nieuwe kalendertabel.
Hoewel deze grafiek er eenvoudig uitziet, is het indrukwekkend dat het minder dan tien seconden kostte om een consolidatie van meer dan twee miljoen gegevensrijen te maken, zonder een nieuwe kolom aan de verkoopgegevens toe te voegen.
Hoewel we al deze geconsolideerde rapportagescenario's kunnen uitvoeren, kunnen we altijd inzoomen op de afzonderlijke regelitems. We behouden onze zeer gedetailleerde gegevens.
Tot nu toe zijn de meeste analyses die ik heb laten zien relatief eenvoudige berekeningen. Nu wil ik enkele van de meer geavanceerde mogelijkheden van dit platform demonstreren.
Wanneer we financiële resultaten onderzoeken, willen we deze vaak vergelijken met een vergelijkbaar tijdsbestek van het voorgaande jaar. Power Pivot heeft een aantal ingebouwde tijdintelligentiefuncties.
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())
Als ik bijvoorbeeld slechts twee maten hierboven toevoeg aan de tabel met boekhoudgegevens in Power Pivot, kan ik de volgende draaitabel met een paar klikken maken.
Een probleem dat ik als financieel analist vaak moet oplossen, is dat van niet-overeenkomende granulariteiten. In ons voorbeeld worden de werkelijke verkoopgegevens weergegeven op categorieniveau, maar laten we een budget opstellen dat alleen op seizoensniveau is. Om deze mismatch te vergroten, zullen we een kwartaalbudget opstellen, zelfs via de verkoopgegevens dagelijks.
Met Power Pivot voor Excel is deze inconsistentie eenvoudig op te lossen. Door twee extra referentietabellen of dimensietabellen in de databasenomenclatuur te maken, kunnen we nu de juiste relaties creëren om onze werkelijke verkopen te analyseren ten opzichte van de gebudgetteerde bedragen.
In Excel komt de volgende draaitabel snel samen.
Verder kunnen we nieuwe metingen definiëren die het verschil tussen werkelijke verkoop en gebudgetteerde verkoop berekenen, zoals hieronder:
Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1
Met deze meting kunnen we de variantie op een draaitabel weergeven.
Laten we tot slot de verkoop in een bepaalde categorie bekijken als een percentage van alle verkopen (bijv. categoriebijdrage aan de totale verkoop), en de verkoop in een bepaalde categorie als een percentage van alle verkopen van hetzelfde type (bijv. categoriebijdrage aan seizoens-type verkoop). Ik heb de twee onderstaande maten gemaakt:
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]))
Die maatregelen kunnen nu worden ingezet in een nieuwe draaitabel:
Let op hoe de berekeningen worden uitgevoerd bij beide het categorie- en seizoenstypeniveau. Ik vind het geweldig hoe snel en moeiteloos deze berekeningen worden uitgevoerd op zo'n grote dataset. Dit zijn slechts enkele voorbeelden van de elegantie en pure rekenkracht van Power Pivot.
Een ander voordeel is dat de bestandsgrootte kleiner wordt. De oorspronkelijke bestandsgrootte was 91 MB en is nu minder dan 4 MB. Dat is een compressie van 96% van het originele bestand.
Hoe gebeurde dit? Power Pivot gebruikt de xVelocity-engine om de gegevens te comprimeren. In eenvoudige bewoordingen worden de gegevens opgeslagen in kolommen in plaats van rijen. Met deze opslagmethode kan de computer dubbele waarden comprimeren. In onze voorbeeldgegevensset zijn er slechts vier regio's die worden herhaald over alle twee miljoen rijen. Power Pivot voor Excel kan deze gegevens efficiënter opslaan. Het resultaat is dat het voor gegevens met veel herhalende waarden veel minder kost om deze gegevens op te slaan.
Een ding om op te merken is dat ik bedragen in hele dollars heb gebruikt in deze voorbeeldgegevensset. Als ik twee decimalen had toegevoegd om centen weer te geven, zou het compressie-effect verminderen tot een nog steeds indrukwekkende 80% van de oorspronkelijke bestandsgrootte.
Power Pivot-modellen kunnen ook schaalbaar zijn voor de hele onderneming. Stel dat u een Power Pivot-model bouwt dat veel gebruikers in de organisatie begint te krijgen, of dat de gegevens groeien tot tien miljoen rijen, of beide. Op dit moment wilt u misschien niet dat dertig verschillende gebruikers het model vernieuwen of wijzigingen aanbrengen. Het model kan naadloos worden omgezet in SSAS Tabular. Alle tabellen en relaties blijven behouden, maar u kunt nu de verversingsfrequentie regelen, rollen toewijzen (bijv. alleen-lezen, lezen en verwerken) aan verschillende gebruikers en alleen een kleine Excel-front-end implementeren die naar het tabellarische model linkt. Het resultaat is dat uw gebruikers dan met een kleine werkmap toegang kunnen krijgen tot het geïmplementeerde Tabular-model, maar geen toegang hebben tot de formules en metingen.
Een van de constante verzoeken van mijn klanten is dat ik rapportages maak die voldoen aan een strikt gedefinieerde lay-out. Ik heb klanten die specifieke kolombreedtes, RGB-kleurcodes en vooraf gedefinieerde lettertypenamen en -groottes vragen. Overweeg het volgende dashboard:
Hoe vullen we de verkoopcijfers in zonder draaitabellen te genereren als al onze verkopen zijn ondergebracht bij Power Pivot voor Excel? CUBE-formules gebruiken! We kunnen CUBE-formules schrijven in elke Excel-cel en het zal de berekening uitvoeren met behulp van het Power Pivot-model dat we al hebben geconstrueerd.
De volgende formule wordt bijvoorbeeld getypt in de cel onder "Totale verkoop 2016:"
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[2016]")
Het eerste deel van de formule, geel gemarkeerd, verwijst naar de naam van het Power Pivot-model. Over het algemeen is dit meestal ThisWorkbookDataModel voor nieuwere versies van Power Pivot voor Excel. Het gedeelte in groen definieert dat we de maat Totale omzet willen gebruiken. Het blauwe gedeelte geeft Excel de opdracht om alleen te filteren op rijen die een Verkoopdatum hebben met een jaar gelijk aan 2016.
Achter de schermen heeft Power Pivot een Online Analytical Processing (OLAP)-kubus gebouwd met de gegevens, berekende kolommen en metingen. Met dit ontwerp kan de Excel-gebruiker vervolgens toegang krijgen tot de gegevens door rechtstreeks op te halen met de CUBE-functies. Met behulp van CUBE-formules heb ik volledige financiële overzichten kunnen samenstellen die voldoen aan vooraf gedefinieerde lay-outs. Deze mogelijkheid is een van de hoogtepunten van het gebruik van Power Pivot voor Excel voor financiële analyse.
Een ander voordeel van Power Pivot voor Excel is dat u snel elke Power Pivot-werkmap die u maakt, kunt gebruiken en snel kunt converteren naar een Power BI-model. Door de Excel-werkmap rechtstreeks in de Power BI Desktop-app of Power BI Online te importeren, kunt u uw gegevens analyseren, visualiseren en delen met iedereen in uw organisatie. In wezen is Power BI Power Pivot, PowerQuery en SharePoint in één. Hieronder heb ik een dashboard gemaakt door de vorige Power Pivot voor Excel-werkmap te importeren in de Power BI-desktoptoepassing. Merk op hoe interactief de interface is:
Een groot voordeel van Power BI is de Natural Language Q&A. Ter demonstratie heb ik het Power BI-model geüpload naar mijn online Power BI-account. Vanaf de website kan ik vragen stellen en Power BI maakt de juiste analyse terwijl ik typ:
Met dit type querymogelijkheid kan de gebruiker vragen stellen over het gegevensmodel en op een eenvoudigere manier met de gegevens communiceren dan in Excel.
Een ander voordeel van Power BI is dat de ontwikkelaars bij Microsoft er voortdurend updates voor uitbrengen. Nieuwe functies, vaak op verzoek van de gebruiker, worden maandelijks uitgebracht. Het beste van alles is dat het een naadloze overgang is van Power Pivot voor Excel. Dus de tijd die je hebt geïnvesteerd om de DAX-formules te leren, kan worden ingezet in Power BI! Voor de analist die zijn analyse met veel gebruikers op verschillende apparaten moet delen, is Power BI misschien het onderzoeken waard.
Als je eenmaal aan de slag bent gegaan, zijn er een paar praktische tips die je moet volgen.
De eerste is om zorgvuldig te beslissen wat u in de eerste plaats wilt importeren. Gebruikt u ooit het huisadres van de verkoper? Moet ik het e-mailadres van mijn klant weten in het kader van dit werkboek? Als het doel is om de gegevens te aggregeren in een dashboard, dan zijn sommige van de beschikbare gegevens niet nodig voor die berekeningen. Tijd besteden aan het beheren van de gegevens die binnenkomen, zal problemen en geheugengebruik later aanzienlijk verminderen wanneer uw gegevensset wordt uitgebreid.
Een andere best practice is om te onthouden dat Power Pivot geen Excel is. In Excel zijn we gewend om berekeningen te maken door onze werkbladen voortdurend naar rechts uit te breiden. Power Pivot voor Excel verwerkt de gegevens het meest efficiënt als we dit verlangen naar een duidelijk lot beperken. In plaats van voortdurend berekende kolommen rechts van uw gegevens te maken, kunt u in het onderste deelvenster meten leren schrijven. Deze gewoonte zorgt voor kleinere bestandsgroottes en snellere berekeningen.
Ten slotte zou ik willen voorstellen om gewone Engelse namen voor maten te gebruiken. Het kostte me veel tijd om deze te adopteren. Ik heb de eerste paar jaar namen verzonnen als SumExpPctTotal , maar toen andere mensen dezelfde werkboeken begonnen te gebruiken, had ik veel uit te leggen. Als ik nu een nieuwe werkmap start, gebruik ik meetwaarden zoals Expense Line Item as Percent of Total Expenses . Hoewel de naam langer is, is het veel gemakkelijker voor iemand anders om te gebruiken.
In dit artikel heb ik slechts een handvol manieren gepresenteerd waarop u met Power Pivot voor Excel een belangrijke stap verder kunt gaan dan gewoon vanille Excel. Ik dacht dat het nuttig zou zijn om enkele praktijkgevallen uit te lichten waarin ik heb ontdekt dat Power Pivot voor Excel buitengewoon nuttig is.
Hier zijn enkele:
Als financiële analisten moeten we complexe berekeningen uitvoeren op steeds groter wordende datasets. Aangezien Excel al het standaard analytische hulpmiddel is, is de Power Pivot-leercurve eenvoudig en veel van de functies weerspiegelen de oorspronkelijke functies van Excel.
Met het gebruik van CUBE-functies gaat Power Pivot voor Excel naadloos op in uw bestaande Excel-werkmappen. De winst van de computationele efficiëntie kan niet over het hoofd worden gezien. Uitgaande van een 20% snellere verwerkingssnelheid, wat conservatief is, kan de financieel analist die zes uur per dag in Excel doorbrengt, 300 uur per jaar besparen!
Bovendien kunnen we nu datasets analyseren die veel groter zijn dan voorheen met onze traditionele Excel. Met modellen die efficiënt zijn ontworpen, kunnen we gemakkelijk 10x de hoeveelheid gegevens hebben die we eerder in traditionele Excel hadden toegestaan, terwijl we de snelle analytische flexibiliteit behouden. Met de mogelijkheid om de modellen van Power Pivot naar SSAS Tabular te converteren, is de hoeveelheid gegevens die kan worden verwerkt 100-1000 keer wat we in Excel kunnen bereiken.
Het vermogen van Power Pivot voor Excel om bliksemsnelle berekeningen uit te voeren op grote hoeveelheden gegevens en toch de mogelijkheid te behouden om in de details te duiken, kan financiële analyse transformeren van onhandige spreadsheets naar moderne werkmappen.
Als u Power Pivot voor Excel wilt uitproberen, vindt u hieronder enkele nuttige materialen om u op weg te helpen.
Collie, R., &Singh, A. (2016). Power Pivot en Power BI:de Excel-gebruikershandleiding voor DAX, Power Query, Power BI en Power Pivot in Excel 2010-2016. Verenigde Staten:Heilige Macro! Boeken.
Ferrari, A., &Russo, M. (2015). De definitieve gids voor DAX:Business intelligence met Microsoft Excel, SQL Server Analysis Services en Power BI. Verenigde Staten:Microsoft Press, VS.
19 topaandelen voor een zwakke Amerikaanse dollar
Een ecosysteem van $ 500 miljard + schalen:zakelijke use-cases voor Ethereum Layer 2-oplossingen
De belangrijkste doelen van Amerikanen - en de grootste angsten - voor de komende 4 jaar
De 5 beste steden voor Robocalls - en hoe u de vervelende berichten kunt stoppen
Top 7 pensioenprioriteiten van Amerikanen voor Biden en Congres
Top geldvoorspellingen voor 2022
De WACC schatten voor de waardering van een privébedrijf:een zelfstudie