Tutorial zu Power Pivot für Excel: Häufigste Anwendungsfälle und Beispiele

Zusammenfassung

Laden Sie den Datensatz hier herunter, um dem Tutorial zu folgen.

Des Kaisers neue Kleider: Power Pivot-Tutorial

In den verschiedenen Bereichen und Sub-Sub-Bereichen, die Finanzen, Finanzanalysen, Finanzmärkte und Finanzinvestitionen umfassen, ist Microsoft Excel der König. Mit der Ankunft und dem exponentiellen Wachstum von Big Data, angetrieben durch Jahrzehnte der Datenaggregation und -akkumulation, dem Aufkommen billiger Cloud-Speicher und dem Aufstieg des Internets der Dinge – dh E-Commerce, soziale Medien und die Vernetzung von Geräten – Excel’s Legacy-Funktionen und -Fähigkeiten wurden an ihre Grenzen gebracht.

Genauer gesagt haben die Infrastruktur- und Verarbeitungsbeschränkungen der älteren Excel-Generation, wie z. B. das Zeilenlimit von 1.048.576 Zeilen oder die unvermeidliche Verlangsamung der Verarbeitung bei großen Datensätzen, Datentabellen und miteinander verbundenen Tabellenkalkulationen, die Verwendbarkeit als effektives Big-Data-Tool verringert. Im Jahr 2010 fügte Microsoft Excel jedoch eine neue Dimension namens Power Pivot hinzu. Power Pivot bot Excel Business-Intelligence- und Business-Analytics-Funktionen der nächsten Generation in seiner Fähigkeit, nahezu unbegrenzte Datensätze ohne Beeinträchtigung der Verarbeitungsgeschwindigkeit zu extrahieren, zu kombinieren und zu analysieren. Trotz seiner Veröffentlichung vor acht Jahren wissen die meisten Finanzanalysten jedoch immer noch nicht, wie man Power Pivot verwendet, und viele wissen nicht, dass es überhaupt existiert.

In diesem Artikel zeige ich Ihnen, wie Sie Power Pivot verwenden, um häufige Excel-Probleme zu überwinden, und werfe anhand einiger Beispiele einen Blick auf weitere wichtige Vorteile der Software. Dieses Power Pivot-Tutorial soll als Leitfaden dafür dienen, was Sie mit diesem Tool erreichen können, und am Ende werden einige Beispielanwendungsfälle untersucht, in denen sich Power Pivot oft als unschätzbar erweist.

Was ist Power Pivot und warum ist es nützlich?

Power Pivot ist eine Funktion von Microsoft Excel, die als Add-In für Excel 2010 und 2013 eingeführt wurde und jetzt eine native Funktion für Excel 2016 und 365 ist. Wie Microsoft erklärt, ermöglicht Power Pivot für Excel „das Importieren von Millionen von Zeilen von Daten aus mehreren Datenquellen in eine einzige Excel-Arbeitsmappe, erstellen Sie Beziehungen zwischen heterogenen Daten, erstellen Sie berechnete Spalten und Kennzahlen mithilfe von Formeln, erstellen Sie PivotTables und PivotCharts und analysieren Sie die Daten dann weiter, damit Sie zeitnahe Geschäftsentscheidungen treffen können, ohne IT-Unterstützung zu benötigen. ”

Die primäre Ausdruckssprache, die Microsoft in Power Pivot verwendet, ist DAX (Data Analysis Expressions), obwohl andere in bestimmten Situationen verwendet werden können. Wie Microsoft erklärt, ist „DAX eine Sammlung von Funktionen, Operatoren und Konstanten, die in einer Formel oder einem Ausdruck verwendet werden können, um einen oder mehrere Werte zu berechnen und zurückzugeben. Einfacher ausgedrückt hilft Ihnen DAX dabei, neue Informationen aus Daten zu erstellen, die bereits in Ihrem Modell vorhanden sind.“ Zum Glück für diejenigen, die bereits mit Excel vertraut sind, werden DAX-Formeln vertraut aussehen, da viele der Formeln eine ähnliche Syntax haben (z. B. SUM , AVERAGE , TRUNC ).

Zur Verdeutlichung können die Hauptvorteile der Verwendung von Power Pivot gegenüber einfachem Excel wie folgt zusammengefasst werden:

• Damit können Sie Hunderte von Millionen Datenzeilen importieren und bearbeiten, wo Excel eine harte Beschränkung von etwas mehr als einer Million Zeilen hat.
• Es ermöglicht Ihnen, Daten aus mehreren Quellen in eine einzige Quellarbeitsmappe zu importieren, ohne mehrere Quellblätter erstellen zu müssen, die unter Versionskontroll- und Übertragbarkeitsproblemen leiden.
• Damit können Sie die importierten Daten manipulieren, analysieren und Schlussfolgerungen ziehen, ohne Ihren Computer im Schneckentempo zu verlangsamen.
• Damit können Sie die Daten mit PivotCharts und Power BI visualisieren.

In den folgenden Abschnitten werde ich die oben genannten Punkte durchgehen und Ihnen zeigen, wie hilfreich Power Pivot für Excel sein kann.

So verwenden Sie Power Pivot

1) Importieren großer Datensätze

Wie bereits erwähnt, betrifft eine der größten Einschränkungen von Excel die Arbeit mit extrem großen Datensätzen. Glücklicherweise kann Excel jetzt weit über die Grenze von einer Million Zeilen direkt in Power Pivot laden.

Um dies zu demonstrieren, habe ich für einen Sportartikelhändler mit neun verschiedenen Produktkategorien und vier Regionen einen Beispieldatensatz mit den Umsätzen von zwei Jahren generiert. Das resultierende Dataset besteht aus zwei Millionen Zeilen.

Mithilfe der Registerkarte „ Daten “ im Menüband habe ich eine neue Abfrage aus der CSV-Datei erstellt (siehe Erstellen einer neuen Abfrage weiter unten). Diese Funktionalität hieß früher PowerQuery, war aber ab Excel 2016 und 365 enger in die Registerkarte „Daten“ von Excel integriert.

Erstellen einer neuen Abfrage

Von einer leeren Arbeitsmappe in Excel bis zum Laden aller zwei Millionen Zeilen in Power Pivot dauerte es ungefähr eine Minute! Beachten Sie, dass ich eine leichte Datenformatierung durchführen konnte, indem ich die erste Zeile zu den Spaltennamen machte. In den letzten Jahren hat sich die Power Query-Funktionalität von einem Excel-Add-In zu einem eng integrierten Teil der Registerkarte „Daten“ auf der Symbolleiste erheblich verbessert. Power Query kann Ihre Daten durch seine Suite von Optionen und seine eigene Sprache, M.

2) Importieren von Daten aus mehreren Quellen

Einer der anderen Hauptvorteile von Power Pivot für Excel ist die Möglichkeit, Daten aus mehreren Quellen einfach zu importieren. Zuvor haben viele von uns mehrere Arbeitsblätter für unsere verschiedenen Datenquellen erstellt. Häufig umfasste dieser Prozess das Schreiben von VBA-Code und das Kopieren/Einfügen aus diesen unterschiedlichen Quellen. Glücklicherweise können Sie mit Power Pivot Daten aus verschiedenen Datenquellen direkt in Excel importieren, ohne auf die oben genannten Probleme stoßen zu müssen.

Mit der Abfragefunktion in Abbildung 1 können wir aus einer der folgenden Quellen ziehen:

• Microsoft Azure
• SQL Server
• Teradaten
• Facebook
• Zwangsversteigerung
• JSON-Dateien
• Excel-Arbeitsmappen
• …und viele mehr

Darüber hinaus können mehrere Datenquellen entweder in der Abfragefunktion oder im Power Pivot-Fenster kombiniert werden, um Daten zu integrieren. Beispielsweise können Sie Produktionskostendaten aus einer Excel-Arbeitsmappe und tatsächliche Verkaufsergebnisse von SQL Server über die Abfrage in Power Pivot abrufen. Von dort aus können Sie die beiden Datensätze kombinieren, indem Sie die Produktionschargennummern abgleichen, um Bruttomargen pro Einheit zu erzielen.

3) Arbeiten mit großen Datensätzen

Ein weiterer wichtiger Vorteil von Power Pivot für Excel ist die Möglichkeit, große Datasets zu manipulieren und damit zu arbeiten, um relevante Schlussfolgerungen und Analysen zu ziehen. Ich werde unten einige gängige Beispiele durchgehen, um Ihnen einen Eindruck von der Leistungsfähigkeit des Tools zu vermitteln.

Mittel

Excel-Junkies werden zweifellos zustimmen, dass PivotTables sowohl eine der nützlichsten als auch gleichzeitig eine der frustrierendsten Aufgaben sind, die wir ausführen. Frustrierend vor allem bei der Arbeit mit größeren Datensätzen. Glücklicherweise können wir mit Power Pivot für Excel PivotTables einfach und schnell erstellen, wenn wir mit größeren Datensätzen arbeiten.

Beachten Sie in der Abbildung unten mit dem Titel Measures erstellen , wie das Power Pivot-Fenster in zwei Bereiche unterteilt ist. Der obere Bereich enthält die Daten und der untere Bereich enthält die Kennzahlen. Eine Kennzahl ist eine Berechnung, die für den gesamten Datensatz durchgeführt wird. Ich habe eine Kennzahl eingegeben, indem ich in die markierte Zelle eingetippt habe.

 Total Sales:=SUM('Accounting Data'[Amount])

Dadurch wird eine neue Kennzahl erstellt, die über die Spalte „Betrag“ summiert wird. Auf ähnliche Weise kann ich eine andere Kennzahl in die Zelle darunter eingeben

 Average Sales:=AVERAGE('Accounting Data'[Amount])

Maßnahmen erstellen

Beobachten Sie von dort aus, wie schnell es geht, eine vertraute PivotTable für einen großen Datensatz zu erstellen.

Erstellen einer PivotTable

Dimensionstabellen

Als Finanzanalysten, die Excel verwenden, werden wir geschickt darin, komplizierte Formeln zu verwenden, um die Technologie unserem Willen anzupassen. Wir beherrschen VLOOKUP , SUMIF und sogar den gefürchteten INDEX(MATCH()) . Durch die Verwendung von Power Pivot können wir jedoch vieles davon aus dem Fenster werfen.

Hinzufügen einer benutzerdefinierten Tabelle zu einem Power Pivot-Modell

Um diese Funktionalität zu demonstrieren, habe ich eine kleine Referenztabelle erstellt, in der ich jeder Kategorie einen Typ zugeordnet habe. Durch Auswahl von „Zum Datenmodell hinzufügen“ wird diese Tabelle in Power Pivot geladen (siehe Hinzufügen einer benutzerdefinierten Tabelle zu einem Power Pivot-Modell oben).

Ich habe auch eine Datumstabelle zur Verwendung mit unserem Datensatz erstellt (siehe Erstellen einer Datumstabelle weiter unten). Power Pivot für Excel erleichtert das schnelle Erstellen einer Datumstabelle, um sie nach Monaten, Quartalen und Wochentagen zu konsolidieren. Der Benutzer kann auch eine benutzerdefiniertere Datumstabelle erstellen, um sie nach Wochen, Geschäftsjahren oder organisationsspezifischen Gruppierungen zu analysieren.

Erstellen einer Datumstabelle

Berechnete Spalten

Neben Kennzahlen gibt es noch eine andere Berechnungsart: berechnete Spalten. Excel-Benutzer werden diese Formeln bequem schreiben können, da sie dem Schreiben von Formeln in Datentabellen sehr ähnlich sind. Ich habe unten eine neue berechnete Spalte erstellt (siehe Erstellen einer berechneten Spalte unten), die die Buchhaltungsdatentabelle nach Betrag sortiert. Verkäufe unter 50 US-Dollar werden als „Klein“ und alle anderen als „Groß“ gekennzeichnet. Fühlt sich die Formel nicht intuitiv an?

Erstellen einer berechneten Spalte

Beziehungen

Anschließend können wir mithilfe der Diagrammansicht eine Beziehung zwischen dem Feld „Kategorie“ der Tabelle „Buchhaltungsdaten“ und dem Feld „Kategorie“ der Tabelle „Kategorie“ erstellen. Außerdem können wir eine Beziehung zwischen dem Feld „Verkaufsdatum“ der Tabelle „Buchhaltungsdaten“ und dem Feld „Datum“ der Tabelle „Kalender“ definieren.

Beziehungen definieren

Jetzt können wir, ohne dass SUMIF oder VLOOKUP Funktionen erforderlich sind, eine PivotTable erstellen, die den Gesamtumsatz nach Jahr und Typ mit einem Slicer für die Transaktionsgröße berechnet.

PivotTable mit Beziehungen

Oder wir können mithilfe der neuen Kalendertabelle ein Diagramm der durchschnittlichen Verkäufe für jeden Wochentag erstellen.

PivotChart mit Beziehungen

Obwohl dieses Diagramm einfach aussieht, ist es beeindruckend, dass es weniger als zehn Sekunden dauerte, um eine Konsolidierung über zwei Millionen Datenzeilen zu erstellen, ohne den Verkaufsdaten eine neue Spalte hinzuzufügen.

Während wir all diese konsolidierten Berichtsszenarien durchführen können, können wir immer noch die einzelnen Einzelposten aufschlüsseln. Wir bewahren unsere hochgradig granularen Daten auf.

Erweiterte Funktionen

Bisher sind die meisten Analysen, die ich gezeigt habe, relativ einfache Berechnungen. Nun möchte ich einige der fortgeschritteneren Fähigkeiten dieser Plattform demonstrieren.

Zeitintelligenz

Wenn wir Finanzergebnisse untersuchen, möchten wir sie oft mit einem vergleichbaren Zeitraum aus dem Vorjahr vergleichen. Power Pivot verfügt über einige integrierte Zeitintelligenzfunktionen.

 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())

Wenn ich beispielsweise nur zwei Kennzahlen oben zur Buchhaltungsdatentabelle in Power Pivot hinzufüge, kann ich die folgende PivotTable mit wenigen Klicks erstellen.

Zeitintelligenz-PivotTable

Nicht übereinstimmende Granularitäten

Als Finanzanalyst muss ich oft das Problem der nicht übereinstimmenden Granularitäten lösen. In unserem Beispiel werden die tatsächlichen Verkaufsdaten auf Kategorieebene angezeigt, aber erstellen wir ein Budget, das nur auf saisonaler Ebene liegt. Um diese Diskrepanz zu fördern, werden wir ein vierteljährliches Budget erstellen, auch wenn die Verkaufsdaten täglich sind.

Nicht übereinstimmende Granularitäten – Budgettabelle

Mit Power Pivot für Excel lässt sich diese Inkonsistenz leicht lösen. Indem wir zwei zusätzliche Referenztabellen oder Dimensionstabellen in der Datenbanknomenklatur erstellen, können wir jetzt die entsprechenden Beziehungen erstellen, um unsere tatsächlichen Verkäufe mit den budgetierten Beträgen zu vergleichen.

Nicht übereinstimmende Granularitäten – Beziehungen

In Excel kommt die folgende PivotTable schnell zusammen.

Nicht übereinstimmende Granularitäten – Budget vs. tatsächliche Ergebnisse

Darüber hinaus können wir neue Kennzahlen definieren, die die Abweichung zwischen den tatsächlichen Verkäufen und den budgetierten Verkäufen wie folgt berechnen:

 Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

Mit dieser Kennzahl können wir die Varianz in einer PivotTable anzeigen.

Nicht übereinstimmende Granularitäten – Abweichungsergebnisse

Prozent von Gesamt

Betrachten wir schließlich den Umsatz in einer bestimmten Kategorie als Prozentsatz aller Verkäufe (z. B. Beitrag der Kategorie zum Gesamtumsatz) und den Umsatz in einer bestimmten Kategorie als Prozentsatz aller Verkäufe desselben Typs (z. B. Beitrag der Kategorie zum Saisontyp). Verkauf). Ich habe die beiden folgenden Maßnahmen erstellt:

 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]))

Diese Kennzahlen können jetzt in einer neuen PivotTable bereitgestellt werden:

Prozent von Gesamt

Beachten Sie, wie die Berechnungen sowohl auf Kategorie- als auch auf saisonaler Typebene durchgeführt werden. Ich finde es toll, wie schnell und mühelos diese Berechnungen für einen so großen Datensatz durchgeführt werden. Dies sind nur einige Beispiele für die Eleganz und die schiere Rechenleistung von Power Pivot.

Kompression

Ein weiterer Vorteil ist, dass die Dateigröße schrumpft. Die ursprüngliche Dateigröße betrug 91 MB und liegt jetzt unter 4 MB. Das ist eine Komprimierung von 96 % der Originaldatei.

Dateigrößen

Wie kommt es dazu? Power Pivot verwendet die xVelocity-Engine, um die Daten zu komprimieren. Einfach ausgedrückt werden die Daten in Spalten statt in Zeilen gespeichert. Diese Speichermethode ermöglicht es dem Computer, doppelte Werte zu komprimieren. In unserem Beispieldatensatz gibt es nur vier Regionen, die sich über alle zwei Millionen Zeilen wiederholen. Power Pivot für Excel kann diese Daten effizienter speichern. Das Ergebnis ist, dass es bei Daten mit vielen sich wiederholenden Werten viel weniger kostet, diese Daten zu speichern.

Zu beachten ist, dass ich in diesem Beispieldatensatz ganze Dollarbeträge verwendet habe. Wenn ich zwei Dezimalstellen eingefügt hätte, um Cents wiederzugeben, würde der Komprimierungseffekt auf immer noch beeindruckende 80 % der ursprünglichen Dateigröße abnehmen.

SSAS-Tabelle

Power Pivot-Modelle können auch für das gesamte Unternehmen skalierbar sein. Nehmen wir an, Sie erstellen ein Power Pivot-Modell, das anfängt, viele Benutzer in der Organisation zu gewinnen, oder die Daten wachsen auf zehn Millionen Zeilen an, oder beides. An diesem Punkt möchten Sie vielleicht nicht, dass dreißig verschiedene Benutzer das Modell aktualisieren oder Änderungen vornehmen. Das Modell kann nahtlos in SSAS Tabular umgewandelt werden. Alle Tabellen und Beziehungen bleiben erhalten, aber jetzt können Sie die Aktualisierungshäufigkeit steuern, verschiedenen Benutzern Rollen zuweisen (z. B. Nur-Lesen, Lesen und Verarbeiten) und nur ein kleines Excel-Frontend bereitstellen, das mit dem tabellarischen Modell verknüpft ist. Das Ergebnis ist, dass Ihre Benutzer dann mit einer kleinen Arbeitsmappe auf das bereitgestellte tabellarische Modell zugreifen könnten, aber keinen Zugriff auf die Formeln und Kennzahlen haben.

4) Datenvisualisierung und -analyse

CUBE-Formeln

Einer der ständigen Wünsche meiner Kunden ist, dass ich Reportings erstelle, die einem fest definierten Layout entsprechen. Ich habe Kunden, die bestimmte Spaltenbreiten, RGB-Farbcodes und vordefinierte Schriftartnamen und -größen anfordern. Betrachten Sie das folgende Dashboard:

CUBE-Formeln eingebettet

Wie füllen wir die Verkaufszahlen aus, ohne PivotTables zu generieren, wenn alle unsere Verkäufe in Power Pivot für Excel untergebracht sind? Mit CUBE-Formeln! Wir können CUBE-Formeln in jede Excel-Zelle schreiben und die Berechnung wird mit dem bereits erstellten Power Pivot-Modell durchgeführt.

Beispielsweise wird die folgende Formel in die Zelle unter „Gesamtumsatz 2016“ eingegeben:

=CUBEVALUE(" Dieses Arbeitsmappen-Datenmodell "," [Maßnahmen].[Gesamtumsatz] "," [Kalender].[Jahr].[2016] ")

Der erste Teil der Formel, gelb hervorgehoben, bezieht sich auf den Namen des Power Pivot-Modells. Im Allgemeinen ist dies normalerweise ThisWorkbookDataModel für neuere Versionen von Power Pivot für Excel. Der grüne Teil definiert, dass wir die Kennzahl Total Sales verwenden möchten. Der blaue Teil weist Excel an, nur nach Zeilen zu filtern, die ein Verkaufsdatum mit einem Jahr gleich 2016 haben.

Hinter den Kulissen hat Power Pivot einen OLAP-Cube (Online Analytical Processing) mit den Daten, berechneten Spalten und Kennzahlen erstellt. Dieses Design ermöglicht es dem Excel-Benutzer, auf die Daten zuzugreifen, indem er sie direkt mit den CUBE-Funktionen abruft. Mit CUBE-Formeln war ich in der Lage, vollständige Finanzberichte zu erstellen, die vordefinierten Layouts entsprechen. Diese Funktion ist einer der Höhepunkte bei der Verwendung von Power Pivot für Excel für Finanzanalysen.

PowerBI

Ein weiterer Vorteil von Power Pivot für Excel besteht darin, dass Sie jede von Ihnen erstellte Power Pivot-Arbeitsmappe schnell in ein Power BI-Modell konvertieren können. Indem Sie die Excel-Arbeitsmappe direkt in die Power BI Desktop-App oder Power BI Online importieren, können Sie Ihre Daten analysieren, visualisieren und mit jedem in Ihrer Organisation teilen. Im Wesentlichen ist Power BI Power Pivot, PowerQuery und SharePoint in einem. Unten habe ich ein Dashboard erstellt, indem ich die vorherige Power Pivot für Excel-Arbeitsmappe in die Power BI-Desktopanwendung importiert habe. Beachten Sie, wie interaktiv die Schnittstelle ist:

PowerBI

Eine großartige Sache bei Power BI sind die Fragen und Antworten in natürlicher Sprache. Zur Veranschaulichung habe ich das Power BI-Modell in mein Online-Power BI-Konto hochgeladen. Auf der Website kann ich Fragen stellen und Power BI erstellt während der Eingabe die entsprechende Analyse:

Fragen und Antworten zur natürlichen Sprache

Diese Art der Abfragefunktion ermöglicht es dem Benutzer, Fragen zum Datenmodell zu stellen und einfacher als in Excel mit den Daten zu interagieren.

Ein weiterer Vorteil von Power BI ist, dass die Entwickler bei Microsoft ständig Updates veröffentlichen. Neue Funktionen, von denen viele von Benutzern angefordert wurden, werden monatlich veröffentlicht. Das Beste ist, dass es ein nahtloser Übergang von Power Pivot für Excel ist. Die Zeit, die Sie in das Erlernen der DAX-Formeln investiert haben, kann also in Power BI eingesetzt werden! Für den Analysten, der seine Analyse mit vielen Benutzern auf unterschiedlichen Geräten teilen muss, kann Power BI eine Erforschung wert sein.

Empfohlene Vorgehensweise

Sobald Sie begonnen haben, gibt es ein paar Best Practices, die Sie befolgen sollten.

Die erste besteht darin, nachdenklich zu entscheiden, was überhaupt importiert werden soll. Werden Sie jemals die Privatadresse des Verkäufers verwenden? Muss ich im Zusammenhang mit dieser Arbeitsmappe die E-Mail-Adresse meines Kunden kennen? Wenn das Ziel darin besteht, die Daten in einem Dashboard zusammenzufassen, werden einige der verfügbaren Daten für diese Berechnungen nicht benötigt. Wenn Sie Zeit damit verbringen, die eingehenden Daten zu kuratieren, werden Probleme und die Speichernutzung später, wenn Ihr Datensatz erweitert wird, erheblich verringert.

Eine weitere bewährte Methode besteht darin, sich daran zu erinnern, dass Power Pivot nicht Excel ist. In Excel sind wir es gewohnt, Berechnungen zu erstellen, indem wir unsere Arbeitsblätter ständig nach rechts erweitern. Power Pivot für Excel verarbeitet die Daten am effizientesten, wenn wir diesen Wunsch nach manifestem Schicksal einschränken. Anstatt fortlaufend berechnete Spalten rechts neben Ihren Daten zu erstellen, lernen Sie, Kennzahlen in den unteren Bereich zu schreiben. Diese Gewohnheit sorgt für kleinere Dateigrößen und schnellere Berechnungen.

Abschließend würde ich vorschlagen, einfache englische Namen für Maßnahmen zu verwenden. Bei diesem habe ich lange gebraucht, um es zu adoptieren. Ich verbrachte die ersten paar Jahre damit, Namen wie SumExpPctTotal zu erfinden, aber als andere Leute anfingen, dieselben Arbeitsmappen zu verwenden, musste ich viel erklären. Wenn ich jetzt eine neue Arbeitsmappe beginne, verwende ich Kennzahlnamen wie Expense Line Item as Percent of Total Expenses . Obwohl der Name länger ist, ist er für jemand anderen viel einfacher zu verwenden.

Anwendungsfälle aus der Praxis

In diesem Artikel habe ich nur eine Handvoll Möglichkeiten vorgestellt, wie Sie mit Power Pivot für Excel einen wichtigen Schritt über das einfache Excel hinausgehen können. Ich dachte, es wäre nützlich, einige reale Anwendungsfälle hervorzuheben, in denen ich festgestellt habe, dass Power Pivot für Excel äußerst nützlich ist.

Hier sind einige:

• Analysieren Sie die Leistung eines großen Asset-Portfolios über unterschiedliche Zeiträume: Da Power Pivot für Excel es uns ermöglicht, Kennzahlen zu definieren, die einen Zeitraum mit einem vorherigen vergleichen, können wir schnell Quartal für Quartal, Jahr für Jahr, und die Leistung von Monat zu Monat auf laufender Basis, indem nur wenige Kennzahlen geschrieben werden.
• Fassen Sie Buchhaltungsdaten mit benutzerdefinierten Aggregationsebenen zusammen: Durch die Identifizierung jedes Einzelpostens im Hauptbuch nach Name, Kategorie und Finanzaufstellung können schnell Berichte erstellt werden, die die entsprechenden Einzelposten enthalten.
• Ketten können Verkäufe im gleichen Geschäft identifizieren: Anhand einer Tabelle, die abbildet, wann Geschäfte online gehen, können die Finanzergebnisse auf der Basis des gleichen Geschäfts verglichen werden.
• Lokalisieren Sie Über- und Unter-Performer im Verkauf: PivotTables können erstellt werden, die die Top-5-SKUs und die Top-5-SKUs nach Umsatz, Bruttomarge, Produktionszeitrahmen usw. hervorheben.
• Einzelhändler können Kalendertabellen definieren, die eine 4-4-5-Konfiguration verwenden: Mit einer benutzerdefinierten Datumstabelle kann ein Einzelhändler jeden Tag einfach einem bestimmten 4-4-5-Monat zuweisen, dann können die täglichen Verkaufsergebnisse in den entsprechenden Monat übertragen werden.

Von klobigen Tabellenkalkulationen zu modernen Arbeitsmappen

Als Finanzanalysten müssen wir komplexe Berechnungen mit ständig wachsenden Datensätzen durchführen. Da Excel bereits das standardmäßige Analysetool ist, ist die Lernkurve von Power Pivot einfach, und viele der Funktionen spiegeln die nativen Funktionen von Excel wider.

Durch die Verwendung von CUBE-Funktionen fügt sich Power Pivot für Excel nahtlos in Ihre vorhandenen Excel-Arbeitsmappen ein. Der Gewinn an Recheneffizienz ist nicht zu übersehen. Geht man von einer um 20 % schnelleren Verarbeitungsgeschwindigkeit aus, was konservativ ist, kann der Finanzanalyst, der sechs Stunden am Tag mit Excel verbringt, 300 Stunden pro Jahr einsparen!

Darüber hinaus können wir jetzt Datensätze analysieren, die viel größer sind als zuvor mit unserem herkömmlichen Excel. Mit effizient entworfenen Modellen können wir leicht die 10-fache Datenmenge haben, die uns zuvor in herkömmlichem Excel erlaubt war, während wir gleichzeitig eine schnelle analytische Agilität beibehalten. Mit der Möglichkeit, die Modelle von Power Pivot in SSAS Tabular zu konvertieren, ist die Datenmenge, die verarbeitet werden kann, 100- bis 1.000-mal so groß wie die, die wir in Excel erreichen können.

Die Fähigkeit von Power Pivot für Excel, blitzschnelle Berechnungen mit großen Datenmengen durchzuführen und dennoch die Möglichkeit zu behalten, in die Details einzutauchen, kann Finanzanalysen von klobigen Tabellenkalkulationen in moderne Arbeitsmappen verwandeln.

Wenn Sie daran interessiert sind, Power Pivot für Excel auszuprobieren, finden Sie unten einige nützliche Materialien für den Einstieg.

Nützliche Referenzen und Anleitungen

Collie, R., & Singh, A. (2016). Power Pivot und Power BI: Das Excel-Benutzerhandbuch zu DAX, Power Query, Power BI und Power Pivot in Excel 2010-2016. Vereinigte Staaten: Heiliges Makro! Bücher.

Ferrari, A., & Russo, M. (2015). Der definitive Leitfaden für DAX: Business Intelligence mit Microsoft Excel, SQL Server Analysis Services und Power BI. Vereinigte Staaten: Microsoft Press, USA.