Hauptvorteile des PowerPivot Data Analysis Excel-Add-Ins. Datenverarbeitung mit PowerPivot Powerpivot für den industriellen Einsatz

Excel Power Pivot ist ein Tool, mit dem Sie Business-Intelligence-Systeme erstellen können. In diesem Artikel betrachten wir die Methodik zur Ermittlung desselben Indikators aus dem Bereich des Management-Reportings – zunächst auf Basis von Excel-Formeln, dann mit Excel Power Pivot, um alle seine Möglichkeiten und Vorteile zu bewerten.

Was ist Power Pivot?

Excel Power Pivot ist ein Tool, mit dem Benutzer mithilfe von Excel eigene Business-Intelligence-Systeme erstellen können – basierend auf tabellarischen Datenbanken.

Pivot-Tabellen, die zunächst in Excel integriert sind, erfordern in der Regel keine besonderen Kenntnisse und sind unersetzlich, wenn Sie visuelle Analysen in Excel benötigen. Mit Büchern können Sie Daten bei Bedarf in einer separaten Tabelle speichern. Darüber hinaus unterstützt Excel Operatoren für die Arbeit mit Datenarrays – ein sehr nützliches Toolkit für Analysen.

Das Excel Power Pivot-Add-In funktioniert nur in Office 2010 und höher und verfügt über eine Reihe von Funktionen:

• führt alle Berechnungen direkt im RAM des Computers durch, was eine hohe Systemleistung sowie die Fähigkeit zur Verarbeitung großer Datenmengen ermöglicht (die Größe der Quelltabellen kann Millionen von Zeilen erreichen);
• ermöglicht Ihnen die Erstellung von Pivot-Tabellen auf Ihrer Basis. Der Einsatzwert von Excel PowerPivot für Finanziers und Analysten liegt darin, dass Sie mit Hilfe des Add-Ins nun selbst solche Informationsmengen verarbeiten können, die bisher den Einsatz spezialisierter Software (DBMS) und die Einbindung qualifizierter IT-Spezialisten erforderten ;
• enthält eine Datenanalyse-Ausdruckssprache (Data Analysis eXpression, abgekürzt als DAX), die für die Entwicklung neuer Geschäftslogikregeln entwickelt wurde. Einfach ausgedrückt kann es als eine erweiterte Version von Array-Formeln betrachtet werden. DAX-Sprachformeln wurden speziell so entwickelt, dass sie der Excel-Syntax so nahe wie möglich kommen. Obwohl DAX-Formeln den Standardfunktionen von Excel-Arbeitsblättern ähneln, werden sie nur innerhalb der Power Pivot-Shell geschrieben. Die DAX-Sprache verfügt über ein breites Spektrum an Funktionen und Informationen zu verschiedenen Verwendungsmöglichkeiten sind im Internet verfügbar.

Wie die Praxis zeigt, hängt die Beliebtheit einer bestimmten Technologie oft nicht vom Grad und der Vielfalt der damit gelösten Probleme ab, sondern davon, wie effektiv und einfach Sie mit Ihrem Wissen und Ihrer Erfahrung darauf umsteigen können. In diesem Artikel werden Schemata zur Erstellung desselben Indikators aus dem Bereich des Management-Reportings besprochen – zunächst auf Basis von Excel-Formeln, dann unter Verwendung von DAX-Befehlen. Dadurch wird sichergestellt, dass die Arbeit mit Excel Power Pivot weitgehend der Arbeit mit normalen Excel-Tabellen ähnelt.

Als Beispiel wählen wir den Indikator „Monatliches Kundeneinkommensniveau“, der es uns ermöglicht, die Kontrahenten des Unternehmens nach dem Grad der Rentabilität zu ordnen. Mit seiner Hilfe wird deutlich, welcher Anteil des Gesamtumsatzes auf Kunden unterschiedlicher Art entfällt, ihre quantitative Verteilung in Gruppen, die Dynamik der Veränderungen im Kundenstamm usw. Die Berechnung eines solchen Indikators ist jedoch mit gewissen Schwierigkeiten verbunden:

1. Innerhalb eines Kalenderzeitraums kann ein Kunde mehrere Dienstleistungen gleichzeitig in Anspruch nehmen (mehrere Waren kaufen) – daher ist es zur Berechnung der Gesamtrentabilität erforderlich, die Einnahmen aus allen für den Kunden im analysierten Zeitraum erbrachten Dienstleistungen zu summieren;
2. Abhängig von der Höhe des berechneten Einkommens muss der Kunde einer der vorgegebenen Gruppen zugeordnet werden.

Unter Berücksichtigung der Besonderheiten der Berechnungsbedingungen ist der Indikator „Profitability Level“ ein gutes Beispiel für die Untersuchung der Logik der Befehlsausführung, die der DAX-Sprache zugrunde liegt.

Referenz

Verwenden von Array-Formeln

Erinnern wir uns daran, dass es zusätzlich zu den üblichen Arbeitsblattfunktionen (ein Array von Eingabeargumenten – ein Ausgabewert, der in eine Tabellenzelle geschrieben wird) eine separate Klasse von Funktionen gibt, die Array-Formeln genannt werden (ein Array von Eingabeargumenten – ein Array). der Ausgabewerte). Solche Operatoren werden sofort für einen Zellbereich eingegeben. In diesem Fall hängt der Wert eines bestimmten Elements nicht nur von der allen gemeinsamen Komponente (einer einzelnen Berechnungsformel) ab, sondern auch von seiner Position im Array (dem sogenannten Ausführungskontext). Der Ausführungskontext ist einer der Eckpfeiler, auf denen die Logik analytischer Berechnungen beruht. Daher erläutern wir im Folgenden kurz das Funktionsprinzip in Excel.

Schauen wir uns den Ausdruck an (siehe Abb. 1). Seine Eingabeparameter sind zwei Zellbereiche: „Zeile“, gelb hervorgehoben, und „Spalte“ mit blauer Füllung. In dem durch diese Bereiche begrenzten Bereich wird eine Array-Formel der folgenden Form eingeführt: (=Zeilenspalte).

Diese Formel ist eine kompakte Darstellung des Vorgangs des Hinzufügens von Elementpaaren aus den ursprünglichen Arrays. Wie aus der Abbildung hervorgeht, ist die Schreibweise für alle Bereichselemente aus dem Bereich gleich. In diesem Fall weichen die Werte in den Zellen der resultierenden Tabelle voneinander ab, was durch den Ausführungskontext erklärt wird. Beispielsweise befindet sich die in der Abbildung hervorgehobene Zelle E4 in der dritten Spalte und zweiten Zeile des Arrays, was dem zweiten Element des Zeilen-Arrays und dem dritten Element des Spalten-Arrays entspricht.

Die beschriebene Art der Berechnungsorganisation kommt einem nicht ganz vertraut vor, da es in der Formel keinen expliziten Hinweis darauf gibt, welche Zellen zur Addition ausgewählt werden sollen. Daher dauert es einige Zeit, diese Technik zu beherrschen. Aber nachdem der Benutzer diese Technik erlernt hat, wird er in der Lage sein, Ausdrücke für eine mehrdimensionale OLAP-Umgebung zu erstellen. Somit werden die Grundfunktionen des Excel-Programms um analytische Berechnungen basierend auf der Syntax von Arbeitsblattformeln erweitert.

Arbeiten mit Standard-Excel-Formeln

Nehmen wir an, wir verfügen über den Umsatzbericht eines Unternehmens, der von drei Analysten detailliert beschrieben wird (Kontrahenten, Arten von Dienstleistungen und Kalenderperioden).

In Abbildung 2 werden die Quelldaten in den Spalten „Unternehmen“, „Leistung“, „Zeitraum“, „Umsatz“ dargestellt. Wir möchten zwei neue berechnete Spalten „Revenue_Amount“ erstellen, in denen das Gesamteinkommen für einen Kunden in einem bestimmten Zeitraum berechnet wird, sowie „Revenue_Level“, das abhängig von der Ebene von „Revenue_Amount“ die Einkommensgruppe (Einkommen) bestimmt Die Pegel sind auf der rechten Seite von Abb. 2 angegeben.

Zur Vereinfachung und Klarheit nachfolgender Berechnungen wandeln wir die Quelldaten sofort in den „Tabellen“-Modus um. Dazu nutzen wir den entsprechenden Befehl auf der Registerkarte „Einfügen“ und geben ihr einen eigenen Namen „Fact_Table“.

Unser Ziel. Berechnen Sie für jede Zeile der Faktentabelle das Gesamteinkommen, das dem Kunden und dem Monat entspricht. Beispielsweise wurden „Kunde A“ im Februar 2013 drei Leistungen im Umfang von 15, 15 und 25 Einheiten erbracht. Daher muss in jeder Zeile, die sich auf einen bestimmten Zeitraum bezieht, derselbe Wert eingegeben werden – 55 Einheiten (15 + 15 + 25). Dieser Vorgang wird in zwei Schritten durchgeführt.

Bühne 1. Unter allen Zeilen der Faktentabelle werden diejenigen gefiltert, deren Attributwerte „Firma“ und „Zeitraum“ mit der aktuellen Zeile übereinstimmen.

Stufe 2. Die Werte des Attributs „Einkommen“ im resultierenden gefilterten Satz werden summiert.

Für diese Aufgabe ist die SUMIFS-Funktion gut geeignet, mit der Sie Bereiche über mehrere Bedingungen gleichzeitig summieren können. Die Hauptfrage ist, wie diese Formel in eine berechnete Spalte eingegeben wird. Schließlich muss in Tabellen eine beliebige Formel für alle Zeilen gleichzeitig angegeben werden. Wir müssen nur einen definieren, jedoch so, dass sich sein Ausführungskontext abhängig von den Parametern der aktiven Zeile ändert.

Geben wir Ausdruck 1 in eine beliebige Zeile der Spalte „Income_Amount“ ein. Drücken Sie einfach die Eingabetaste, dann passt er in alle Zeilen der Tabelle.

Ausdruck 1:
SUMIFS([Umsatz]; [Unternehmen]; Fact_Table[[#Diese Zeile];[Unternehmen]]; [Periode]; Fact_Table[[#Diese Zeile]; [Zeitraum]])

Schauen wir uns an, wie die Formel funktioniert. Jedes Mal, wenn Sie (in jeder Zeile) die SUMIFS-Funktion aufrufen, wird die gesamte Faktentabelle (ihre Spalte „Umsatz“) auf einmal verwendet. Bei jeder Iteration werden jedoch nur die Zeilen summiert, die den Attributwerten der aktuellen Zeile entsprechen. Zu diesem Zweck wurde der Formel ein spezielles Argument hinzugefügt – [# Diese Zeile], das als Link zur aktuellen Zeile fungiert. Insbesondere für Zeilen, die sich auf „Kunde, A“ und Februar 2013 beziehen, ist der Wert des Parameters [[#Diese Zeile];[Firma]] gleich „Kunde, A“ und [[#Diese Zeile]; [Zeitraum]] – „01.02.2013“.

Deshalb haben wir alle notwendigen Berechnungen mit nur einer Formel beschrieben. In diesem Fall ändern sich die Eingabeargumente der Formel dynamisch abhängig von der Stelle, an der sie aufgerufen wird.

Beachten Sie, dass sich das dargestellte Aufzeichnungsformat erheblich vom Standard-Excel-Modus unterscheidet, bei dem Eingabeparameter explizit in Formeln angegeben werden. Beispielsweise können wir eine Faktentabelle wieder in einen regulären Bereich konvertieren (In Bereich konvertieren auf der Registerkarte „Tabellentools“). Dann hat beispielsweise Ausdruck 1 für Zeile 5 die Form von Ausdruck 2:

Ausdruck 2:
=SUMIFS(Regular_Range!$D$2:$D$37; Regular_Range!$A$2:$A$37; Regular_Range!$A5; Regular_Range!$C$2:$C$37; Regular_Range!$C5)

Für Zeile 6 wird der Ausdruck identisch aussehen, mit dem einzigen Unterschied, dass statt $A5 $A6 steht (und statt $C5 - $C6). In diesem Fall stimmt das Ergebnis der Berechnung von Ausdruck 2 mit dem Wert von Ausdruck 1 in der entsprechenden Zeile überein, sodass wir sie als identisch betrachten können.

Jetzt müssen wir das monatliche Einkommen der Kunden in Gruppen einteilen. Basierend auf dem zuvor berechneten integralen Rentabilitätsindikator müssen wir die entsprechende Zeile in einer externen (relativ zur Faktentabelle) Tabelle auswählen. Dieser Vorgang ist in Excel einfach durchzuführen, da es über viele Operatoren zum Arbeiten mit Arrays und Bereichen verfügt.

Insbesondere die INDEX-Funktion ermöglicht es uns, auf einfache Weise einen Wert aus einem zweidimensionalen Array in jeder Arbeitsmappenzelle zu erhalten; wir müssen nur die entsprechende Objektkennung kennen. Schreiben wir Ausdruck 3 in die Spalte „Income_Level“:

Ausdruck 3:
=INDEX(Levels[Level]; MATCH(Fact_Table[[#This row]; [Revenue_Amount]]; Levels[Start]))

Die in Ausdruck 3 dargestellte Formel wird wie Ausdruck 1 im aktuellen Kontext der Faktentabelle ausgeführt – sie verwendet einen Verweis auf die aktive Zeile. Darüber hinaus greifen wir mit der INDEX-Funktion auf ein externes Objekt zu – die Tabelle „Levels“. Darin wird mit der SEARCH-Funktion nach einer passenden Zeichenfolge gesucht.

Beachten wir noch einmal, wie es uns gelungen ist, Daten aus einer externen Tabelle zu erhalten: Wir haben eine geeignete Funktion ausgewählt und darin als Argument den Bezeichner des gewünschten Objekts angegeben.

Arbeiten mit Excel Power Pivot

Schauen wir uns an, wie Power Pivot die im ersten Teil des Artikels beschriebenen Vorgänge ausführt.

Excel Power Pivot installieren. Zunächst muss das Add-on installiert werden (Sie können es kostenlos von der offiziellen Microsoft-Website herunterladen). Im Excel-Menüband sollte eine neue Registerkarte „Power Pivot“ erscheinen. Wenn das Add-In nicht sofort aktiviert wird, müssen Sie es manuell aktivieren: „Excel-Optionen“ – „Add-Ins“, wählen Sie in der Liste „Verwalten“ „COM-Add-Ins“ – „Los“. Jetzt müssen Sie nur noch das Kontrollkästchen neben „Power Pivot für Excel“ aktivieren. Das Fenster ist nur in Dokumenten im XLSX-Format aktiv.

Aufbereitung der Ausgangsdaten. Die PowerPivot-Datenbank unterstützt eine Vielzahl von Quellen. Insbesondere können Sie Daten aus einem MS Access- oder SQL Server-DBMS laden. In diesem Fall kann die Menge der verarbeiteten Informationen mehrere Millionen Zeilen betragen. Für normale Benutzer ist jedoch die Möglichkeit, Quellinformationen auf Blättern in einer normalen Excel-Arbeitsmappe zu speichern, wertvoller.

In dem Beispiel, das wir im Artikel betrachten, werden nur zwei Tabellen verwendet – eine Faktentabelle sowie eine kleine Hilfsreferenztabelle mit Rentabilitätsniveaus. Um sie zum Power Pivot-Modell hinzuzufügen, müssen Sie nur zwei Bedingungen erfüllen:

1. Platzieren Sie die Tabellen auf separaten Blättern des Buches. Beachten Sie, dass sich diese Anforderung vom Standard-Excel-Betriebsmodus unterscheidet, der die Platzierung einer unbegrenzten Anzahl von Tabellen, die verschiedene Entitäten beschreiben, auf einem Blatt ermöglicht.

2. Speichern Sie Tabellen getrennt von der zusammenfassenden Berichtsdatei. Bei Bedarf können Tabellen mit Daten sogar in verschiedenen Excel-Arbeitsmappen platziert werden – Power Pivot kann mehrere parallele offene Verbindungen zu Datenquellen eines bestimmten Typs unterstützen.

Beim Kopieren von Quelldaten in neue Blätter empfiehlt es sich, diesen sofort aussagekräftige Namen zuzuweisen. Wenn Sie anschließend Daten in Power Pivot importieren, werden die ursprünglichen Objektnamen zu den Namen der entsprechenden Tabellen im Modell.

Als nächstes erstellen Sie eine neue Excel-Arbeitsmappe, die wir „Originaldaten“ nennen. Platzieren wir eine Tabelle mit Anfangsdaten auf einem Blatt namens „Table_Facts“ und auf einem anderen Blatt „Levels“ – eine Referenztabelle mit Rentabilitätsniveaus.

Nun erstellen wir eine weitere Excel-Arbeitsmappe, rufen darin das PowerPivot-Fenster auf und importieren darin Daten aus der Datei „Originaldaten“. Um Daten zu importieren, müssen Sie im Menü „Datei“ den Befehl „Externe Daten aus anderen Quellen abrufen“ und dann den Typ „Excel-Datei“ aufrufen und den Datenimport-Assistenten ausführen.

In der ersten Phase seiner Arbeit geben wir den physischen Speicherort der Datendatei an. Wir empfehlen Ihnen, sofort die Option „Erste Zeile für Spaltenüberschriften verwenden“ zu aktivieren – dann erhalten PowerPivot-Spalten automatisch die Spaltennamen aus der Excel-Arbeitsmappe.

Im zweiten Schritt wählen wir aus, welche Tabellen im Modell verwendet werden sollen. In unserem Fall sollten wir beide auswählen.

Als Ergebnis der durchgeführten Vorgänge sollten die Quelltabellen im Power Pivot-Fenster erscheinen (siehe Abb. 3). Sie können zwischen ihnen wechseln, indem Sie die entsprechende Registerkarte in der unteren linken Ecke des Fensters auswählen.

In jeder Tabelle können Sie zwei Arten von Objekten erstellen: berechnete Spalten und berechnete Felder.

Berechnete Spalten fungieren als Dimensionen in einer PivotTable – sie enthalten Werte, die dann in den Zeilen- oder Spaltenbereichen des Berichtslayouts platziert werden.

Berechnete Felder – Kennzahlen, also aggregierte Indikatoren, die im Berichtsarbeitsbereich platziert werden – werden im Berechnungsbereich definiert; im Power Pivot-Datenansichtsfenster wird dieser Bereich unterhalb der Tabellendatenebene platziert.

In diesem Artikel gehen wir nicht auf die Probleme beim Erstellen benutzerdefinierter Felder ein, sondern erwägen lediglich das Hinzufügen neuer berechneter Spalten zum Modell. In Power Pivot fungieren sie als vollständiges Analogon zu berechneten Spalten in Excel-Tabellen. Insbesondere wird für alle Elemente einer Spalte immer eine einzige Formel in der DAX-Sprache definiert, die im Model Builder-Formelfenster eingegeben wird.

Wie bei regulären Tabellen werden DAX-Formeln im Zeilenkontext ausgeführt. Im Gegensatz zu Tabellen ist es jedoch nicht erforderlich, den Parameter [# This line] explizit anzugeben. Einfach ausgedrückt: Auch ohne das Schreiben des Parameters [# This line] in eine DAX-Formel können Sie davon ausgehen, dass er darin vorhanden ist, und ihn in Berechnungen verwenden. Obwohl diese Funktion DAX-Formeln weniger visuell macht, ermöglicht sie das kompaktere Schreiben von Ausdrücken.

Unter Berücksichtigung der aufgeführten Merkmale der Arbeit mit DAX-Formeln erstellen wir eine neue Spalte „Rendite“ mit der folgenden Formel (Ausdruck 4):

Ausdruck 4:
=CALCULATE(sum([Summe]); ALLEXCEPT("Fact_Table"; "Fact_Table"[Firma]; "Fact_Table"[Zeitraum]))

Schauen wir uns das Funktionsprinzip von Ausdruck 4 an.

Zunächst erhält dieser Operator für jede Zeile der Faktentabelle als Eingabe eine temporäre Tabelle, die mit der Faktentabelle selbst übereinstimmt. Durch den aktuellen Kontext definierte Filter werden dann auf diese Staging-Tabelle angewendet. Es enthält Zeilen, deren Attribute mit den Werten der aktuellen Zeile übereinstimmen: [[#Diese Zeile]; [Firma]], [[#Diese Zeile]; [Service]], [[#Diese Zeile]; [Punkt]], [[#Diese Zeile]; [Summe]].

Der ALLEXCEPT-Ausdruck („Fact_Table“; „Fact_Table“ [Company]; „Fact_Table“ [Period]) erfordert jedoch, dass die aktuellen Kontextfilter für alle Spalten außer [Company] und [Period] gelöscht werden. Es stellt sich heraus, dass wir in der Zwischentabelle nur die Zeilen belassen, deren Attributwerte [Firma] und [Zeitraum] mit der aktuellen Zeile übereinstimmen. In der so gefilterten Tabelle wird das Attribut [Sum] ausgewählt, dessen Werte dann summiert werden.

Auf den ersten Blick mag die Formel zu verwirrend erscheinen, in Wirklichkeit gibt sie jedoch nur die Berechnungen von Ausdruck 1 wieder, den wir ganz am Anfang des Artikels zitiert haben. Die Werte in der Spalte „Ertrag“ sind eine eindeutige Bestätigung, da sie mit der Spalte „Umsatzbetrag“ in Abb. übereinstimmen. 2.

Fügen wir nun der Tabelle eine weitere berechnete Spalte hinzu – „Technical_Level“. Darin geben wir neben jedem in der ersten Stufe berechneten Betrag den numerischen Wert der Skala aus der Tabelle „Stufen“ an. Tatsächlich müssen wir Werte aus einer anderen Tabelle in der Basis „Fact_Table“ abrufen. Dazu genügt es, wie bei regulären Berechnungen in Excel, die entsprechende Funktion auszuwählen und ihr als Argument den Bezeichner des gewünschten Objekts zu übergeben.

Geben wir die folgende Formel in die Spalte „Technical_Level“ ein (Ausdruck 5):

Ausdruck 5:
=BERECHNEN(max("Levels"[Start]);filter(all("Levels"[Start]);"Levels"[Start]

Im dargestellten Ausdruck wird die Aggregationsfunktion MAX() auf die externe Tabelle „Levels“ angewendet. Zunächst wird es entsprechend den Einschränkungen des aktuellen Kontexts gefiltert – nur die Zeilen, die kleiner als die Zahl in der Spalte „Ertrag“ der aktiven Zeile sind, bleiben in der Tabelle übrig.

Zum Schluss fügen wir die zuletzt berechnete Spalte zu unserer Tabelle hinzu – „Dokh_Level“. Darin zeigen wir eine Zeile mit dem Namen des Levels aus der Tabelle „Levels“ an. Solche Operationen werden mit ausgeführt SVERWEIS-Funktionen in Excel und GLOOKUP (VLOOKUP und HLOOKUP). Die DAX-Sprache verfügt über eine Funktion namens LOOKUPVALUE, die dem VLOOKUP-Operator ähnelt.

Daher reicht es aus, in die Spalte „Income_Level“ einen einfachen Ausdruck der folgenden Form zu schreiben (Ausdruck 6):

Ausdruck 6:
=LOOKUPVALUE("Levels"[Level]; "Levels"[Start]; [Technical_Level])

Wir gehen zur letzten Phase der Erstellung eines zusammenfassenden Berichts über. Da wir die Nachschlagetabelle nur für offizielle Zwecke verwenden, sollten ihre Spalten nicht im Bericht angezeigt werden. Ebenso zeigt ein zusammenfassender Bericht möglicherweise nicht alle Faktentabellenspalten an, die Zwischenberechnungen enthalten.

Um den Analysesatz in Excel einzuschränken, verwenden wir den Befehl „Aus dem Satz der Client-Tools ausblenden“ (klicken Sie dazu einfach mit der rechten Maustaste auf die benötigte Spalte und wählen Sie das entsprechende Element im Kontextmenü aus). Ausgeblendete Spalten im Model Builder sind ausgegraut. Um zur Excel-Umgebung zu wechseln und mit dem resultierenden zusammenfassenden Bericht zu arbeiten, wählen Sie einfach den Befehl „Zur Excel-Arbeitsmappe wechseln“.

Leser können selbst überprüfen, ob ein auf Basis von PowerPivot erstellter zusammenfassender Bericht hinsichtlich seiner Funktionalität vollständig den Fähigkeiten einer regulären Pivot-Tabelle entspricht.

Hilfe zur Verwendung des PowerPivot-Add-Ins und der DAX-Sprache, einschließlich des Tutorials „Learn DAX Basics in 30 Minutes“ auf der offiziellen MS Office-Website, finden Sie online.

Aggregate sind eine Möglichkeit, Daten zu gruppieren, Daten zusammenzufassen und zusammenfassende Daten zu erstellen. Wenn Sie beginnen, mit Rohdaten aus Tabellen oder anderen Datenquellen zu arbeiten, sind die Daten häufig unstrukturiert, d. h., es handelt sich um viele detaillierte Daten, die in keiner Weise organisiert oder gruppiert sind. Dieser Mangel an Zusammenfassungen oder Struktur kann es schwierig machen, Muster in den Daten zu erkennen. Daher ist ein wichtiger Teil der Modellierung die Definition von Aggregaten, die Daten vereinfachen und verallgemeinern und Muster identifizieren, die die Lösung eines bestimmten Geschäftsproblems ermöglichen.

Die gängigsten Aggregate wie AVERAGE, Count, DistinctCount, Max, Min oder Sum können mithilfe der AutoSum-Funktion automatisch in einer Dimension erstellt werden. Andere Aggregattypen wie AVERAGEX, COUNTX, CountRows oder SUMX geben eine Tabelle zurück und erfordern eine Formel, die mit Data Analysis Expressions (DAX) erstellt wurde.

Aggregationen in Power Pivot verstehen

Auswählen von Gruppen für ein Gerät

Die aggregierte Verarbeitung gruppiert Daten nach Attributen wie Produkt, Preis, Region oder Datum und definiert dann eine Formel, die für alle Daten in der Gruppe funktioniert. Werden beispielsweise Summen für das Jahr erstellt, handelt es sich hierbei um ein Aggregat. Wenn Sie ein Verhältnis dieses Jahres zum Vorjahr erstellen und die Daten als Prozentsatz darstellen, handelt es sich um eine andere Art der Aggregation.

Die Methode zum Gruppieren von Daten wird durch die gestellte Geschäftsfrage bestimmt. Aggregate können beispielsweise die folgenden Fragen beantworten.

Zähler Wie viele Transaktionen wurden pro Monat abgeschlossen?

Durchschnittliche Werte Wie hoch sind die durchschnittlichen Verkaufszahlen jedes Vertriebsleiters in diesem Monat?

Minimal- und Maximalwerte Welche Vertriebsgebiete lagen gemessen an der verkauften Warenmenge unter den Top 5?

Um eine Berechnung zu erstellen, die diese Fragen beantwortet, müssen Sie über detaillierte Daten mit Zahlen verfügen, die gezählt oder summiert werden sollen, und diese numerischen Daten müssen in irgendeiner Beziehung zu den Gruppen stehen, die zum Sortieren der Ergebnisse verwendet werden.

Wenn die von Ihnen erhaltenen Daten keine Werte enthalten, die zur Gruppierung verwendet werden können (z. B. Produktkategorie oder geografische Region, in der sich das Geschäft befindet), können Sie Datengruppen erstellen, indem Sie Kategorien hinzufügen. Beim Erstellen von Gruppen in Excel müssen Sie die gewünschten Gruppen manuell eingeben oder aus den Spalten des Arbeitsblatts auswählen. In relationalen Systemen werden jedoch viele Hierarchien (z. B. Produktkategorien) in einer anderen Tabelle als der Tabelle gespeichert, in der die Fakten oder Werte gespeichert sind. Typischerweise wird eine Kategorietabelle mithilfe eines Schlüssels mit Faktendaten verknüpft. Angenommen, die Daten enthalten Produkt-IDs, aber keine Produktnamen oder -kategorien. Um eine Kategorie zu einem flachen Excel-Arbeitsblatt hinzuzufügen, müssen Sie die Spalte mit den Kategorienamen kopieren. Mit Power Pivot können Sie eine Tabelle mit Produktkategorien in Ihr Datenmodell importieren, eine Beziehung zwischen der Tabelle mit numerischen Daten und einer Liste mit Produktkategorien erstellen und dann die Kategorien zum Gruppieren der Daten verwenden. Weitere Informationen finden Sie unter Erstellen einer Beziehung zwischen Tabellen.

Auswählen einer Funktion für das Gerät

Nachdem Sie Gruppen definiert und hinzugefügt haben, müssen Sie entscheiden, welche mathematischen Funktionen für die Aggregation verwendet werden sollen. Oft wird das Wort „Aggregat“ als Synonym für die mathematischen oder statistischen Operationen verwendet, die bei Aggregaten verwendet werden, wie z. B. Summieren, Mitteln, Mitteln oder Zählen. Mit Power Pivot können Sie jedoch zusätzlich zu den Standardaggregationen in Power Pivot und Excel benutzerdefinierte Aggregationsformeln erstellen.

Wenn Sie beispielsweise dieselben Werte und Gruppierungen verwenden, die in früheren Fällen verwendet wurden, können Sie benutzerdefinierte Aggregationen erstellen, die die folgenden Fragen beantworten können.

Gefiltertes Zählen Wie viele Transaktionen gab es im Laufe des Monats, ohne die Wartungsperiode am Monatsende?

Verhältnisse unter Verwendung von Durchschnittswerten über einen bestimmten Zeitraum Wie hoch war der prozentuale Anstieg bzw. Rückgang des Umsatzes im Vergleich zum Vorjahreszeitraum?

Gruppierte Minimal- und Maximalwerte Welche Vertriebsbereiche waren für jede Produktkategorie bzw. für jede Verkaufsaktion führend?

Hinzufügen von Aggregaten zu Formeln und Pivot-Tabellen

Wenn Sie eine allgemeine Vorstellung davon haben, wie Sie Ihre Daten gruppieren möchten und mit welchen Werten Sie arbeiten möchten, können Sie eine Pivot-Tabelle erstellen oder Berechnungen in der Tabelle selbst erstellen. Power Pivot erweitert und verbessert die Fähigkeit von Excel, Aggregationen wie Summierung, Zählung oder Mittelung zu erstellen. In Power Pivot können Sie benutzerdefinierte Aggregationen entweder im Power Pivot-Fenster oder im Excel PivotTable-Bereich erstellen.

Gruppierungen zu einer PivotTable hinzufügen

Wenn Sie eine PivotTable entwerfen, werden Felder, die Gruppierungen, Kategorien oder Hierarchien darstellen, in den Abschnitt „Spalten und Zeilen“ der PivotTable gezogen, um Daten zu gruppieren. Felder mit numerischen Werten werden in den Wertebereich gezogen, damit sie gezählt, summiert und gemittelt werden können.

Wenn Sie Kategorien zu einer PivotTable hinzufügen, deren Daten nicht mit Faktendaten verknüpft sind, können Fehler oder unerwartete Ergebnisse auftreten. Normalerweise versucht Power Pivot, das Problem zu beheben, indem es automatisch Beziehungen erkennt und vorschlägt. Weitere Informationen finden Sie unter Arbeiten mit Beziehungen in Pivot-Tabellen.

Sie können Felder auch in Slicer ziehen, um bestimmte Datengruppen zur Anzeige auszuwählen. Mit Slicern können Sie Ergebnisse interaktiv in einer PivotTable gruppieren, sortieren und filtern.

Arbeiten mit Gruppierungen in einer Formel

Gruppierungen und Kategorien können auch zum Aggregieren von in Tabellen gespeicherten Daten verwendet werden, indem Beziehungen zwischen Tabellen erstellt und dann Formeln erstellt werden, die die Beziehungsdaten verwenden, um verwandte Werte zu finden.

Mit anderen Worten: Wenn Sie eine Formel erstellen möchten, die Werte nach Kategorien gruppiert, müssen Sie zunächst eine Beziehung verwenden, um die Tabelle mit den Detaildaten mit der Kategorietabelle zu verbinden, und dann die Formel erstellen.

Weitere Informationen zum Erstellen von Formeln mit Ersetzungen finden Sie unter Ersetzung in PowerPivot-Formeln.

Verwendung von Filtern in Einheiten

Eine neue Funktion in Power Pivot ist die Möglichkeit, Filter auf Datenspalten und -tabellen anzuwenden, nicht nur in der Benutzeroberfläche und in der PivotTable oder dem Diagramm, sondern auch in jeder Formel, die zur Berechnung von Aggregaten verwendet wird. Filter können in Formeln in berechneten Spalten und in s verwendet werden.

Beispielsweise können Sie in den neuen DAX-Aggregatfunktionen statt der Angabe von zu summierenden oder zu zählenden Werten eine ganze Tabelle als Argument angeben. Wenn auf eine bestimmte Tabelle keine Filter angewendet wurden, verarbeitet die Aggregatfunktion alle Werte in der angegebenen Tabellenspalte. In DAX können Sie jedoch einen dynamischen oder statischen Filter für eine Tabelle erstellen, sodass das Aggregat je nach Filterbedingung und aktuellem Kontext unterschiedliche Teilmengen der Daten bearbeitet.

Durch die Kombination von Bedingungen und Filtern in Formeln können Sie Aggregationen erstellen, die sich basierend auf den von den Formeln übergebenen Werten oder basierend auf den Zeilen- und Spaltenkopfauswahlen, die Sie in einer PivotTable treffen, ändern.

Weitere Informationen finden Sie unter Daten in Formeln filtern.

Vergleich von Excel-Aggregatfunktionen mit DAX-Aggregatfunktionen

Die folgende Tabelle listet einige der in Excel verfügbaren Standardaggregatfunktionen auf und bietet Links zur Implementierung dieser Funktionen in Power Pivot. Die DAX-Version dieser Funktionen ähnelt in vielerlei Hinsicht der Excel-Version, mit geringfügigen Unterschieden in der Syntax und der Handhabung einiger Datentypen.

Standard-Aggregatfunktionen

	Verwendung
	Gibt das arithmetische Mittel aller Zahlen in einer Spalte zurück.
	Die Funktion gibt den Durchschnitt (arithmetisch) aller Werte in der Spalte zurück. Verarbeitet Text und nicht numerische Werte.
	Die Funktion zählt die Anzahl der numerischen Werte in einer Spalte.
	Die Funktion zählt die Anzahl der nicht leeren Werte in einer Spalte.
	Gibt den größten numerischen Wert aus einer Spalte zurück.
	Die Funktion gibt den größten Wert aus der Menge der in der Tabelle ausgewerteten Ausdrücke zurück.
	Gibt den kleinsten numerischen Wert in einer Spalte zurück.
	Die Funktion gibt den kleinsten Wert aus einer Reihe von in der Tabelle ausgewerteten Ausdrücken zurück.
	Die Funktion fügt alle Zahlen zu einer Spalte hinzu.

DAX-Aggregatfunktionen

DAX enthält Aggregatfunktionen, mit denen Sie die Tabelle angeben können, für die aggregiert werden soll. Anstatt also einfach die Werte in einer Spalte zu addieren oder einen Durchschnitt zu bilden, können Sie mit diesen Funktionen einen Ausdruck erstellen, der die Daten für die Aggregation dynamisch identifiziert.

In der folgenden Tabelle sind die in DAX verfügbaren Aggregatfunktionen aufgeführt.

	Verwendung
	Die Funktion ermittelt das arithmetische Mittel für eine Reihe von Ausdrücken, die in einer Tabelle ausgewertet werden.
	Die Funktion zählt eine Reihe von Ausdrücken, die in einer Tabelle ausgewertet werden.
	Die Funktion zählt die Anzahl der leeren Werte in einer Spalte.
	Die Funktion zählt die Gesamtzahl der Zeilen in der Tabelle.
	Die Funktion zählt die Anzahl der Zeilen, die von einer verschachtelten Tabellenfunktion, beispielsweise einer Filterfunktion, zurückgegeben werden.
	Die Funktion gibt die Summe einer Reihe von Ausdrücken zurück, die in einer Tabelle ausgewertet werden.

Unterschiede zwischen DAX- und Excel-Aggregatfunktionen

Obwohl diese Funktionen dieselben Namen wie ihre Excel-Gegenstücke haben, verwenden sie die In-Memory-Analyse-Engine von Power Pivot und wurden für die Arbeit mit Tabellen und Spalten neu geschrieben. Sie können eine DAX-Formel nicht in einer Excel-Arbeitsmappe verwenden und umgekehrt. Sie können nur im Power Pivot-Fenster und in PivotTables basierend auf Power Pivot-Daten verwendet werden. Auch wenn die Funktionen dieselben Namen haben, kann das Verhalten leicht unterschiedlich sein. Weitere Informationen finden Sie in den einzelnen Funktionsreferenzen.

Auch die Art und Weise, wie Spalten in einem Aggregat berechnet werden, unterscheidet sich von der Art und Weise, wie Excel Aggregate verarbeitet. Ein Beispiel soll dies veranschaulichen.

Angenommen, Sie möchten die Summe der Werte in der Spalte „Betrag“ der Tabelle „Verkäufe“ erhalten, wofür Sie die folgende Formel erstellen:

SUM("Umsatz")

Im einfachsten Fall gibt die Funktion Werte aus einer einzelnen ungefilterten Spalte zurück und das Ergebnis ist das gleiche wie in Excel, das immer einfach die Werte in der Spalte „Betrag“ summiert. Power Pivot interpretiert die Formel jedoch als „Ermitteln Sie den Wert in der Spalte „Betrag“ für jede Zeile in der Tabelle „Umsätze“ und fügen Sie dann diese einzelnen Werte hinzu.“ Power Pivot berechnet jede Zeile, die aggregiert wird, berechnet einen einzelnen Skalarwert für jede Zeile und aggregiert diese Werte dann. Daher kann das Ergebnis einer Formel variieren, wenn Filter auf die Tabelle angewendet wurden oder wenn Werte aus anderen Aggregationen berechnet wurden, bei denen möglicherweise Filter verwendet wurden. Weitere Informationen finden Sie unter Kontext in DAX-Formeln.

DAX-Zeitlogikfunktionen

Zusätzlich zu den im vorherigen Abschnitt beschriebenen Tabellenaggregatfunktionen bietet DAX Aggregatfunktionen, die an bestimmten Datums- und Uhrzeitangaben ausgeführt werden, um eine integrierte Zeitmanipulationslogik bereitzustellen. Diese Funktionen verwenden Datumsbereiche, um zugehörige Werte abzurufen und zu aggregieren. Auch der Vergleich von Werten über Datumsbereiche hinweg ist möglich.

Die folgende Tabelle enthält Zeitlogikfunktionen, die für die statistische Verarbeitung verwendet werden können.

Weitere Funktionen im Abschnitt „Zeitlogikfunktion“ sind Funktionen, mit denen Sie Datumsangaben oder benutzerdefinierte Datumsbereiche zur Verwendung in einem Aggregat abrufen können. Beispielsweise können Sie die Funktion DATESINPERIOD verwenden, um einen Datumsbereich abzurufen und diesen Datumssatz als Argument für eine andere Funktion zu verwenden, um ein benutzerdefiniertes Aggregat zu berechnen, das nur diese Datumsangaben verwendet.

Power Pivot ist ein Add-In, mit dem sich tiefgreifende Analysen in Excel durchführen lassen. Das Add-In ist in einigen Office-Versionen integriert, aber standardmäßig nicht aktiviert.

Eine Liste der Versionen von Office, einschließlich Power Pivot, sowie eine Liste der Versionen, die nicht empfohlen werden, finden Sie im Artikel: Wo ist PowerPivot?

Nachfolgend erfahren Sie, wie Sie Power Pivot aktivieren, bevor Sie es zum ersten Mal verwenden.

Gehen Sie zur Registerkarte Datei > Optionen > Add-ons.

Auf dem Feld Kontrolle wählen COM-Add-Ins und drücke Gehen.

Aktivieren Sie das Kontrollkästchen Microsoft Office Power Pivot und drücken Sie die Taste OK. Wenn andere Versionen von Power Pivot installiert sind, werden diese ebenfalls in der Liste der COM-Add-Ins aufgeführt. Wählen Sie das Power Pivot-Add-In für Excel aus.

Die Registerkarte „Power Pivot“ wird im Menüband angezeigt.

Öffnen Sie das Power Pivot-Fenster.

Das Power Pivot-Fenster wird geöffnet. Hier können Sie auf die Schaltfläche „Externe Daten“ klicken, um den Tabellenimport-Assistenten zu verwenden, um die Daten beim Hinzufügen zur Datei zu filtern, Beziehungen zwischen Tabellen zu erstellen, die Daten mit Berechnungen und Ausdrücken anzureichern und daraus Pivot-Tabellen und Pivot-Diagramme zu erstellen.

Fehlerbehebung: Power Pivot-Band verschwindet

In einigen Fällen wird das Power Pivot-Menüband im Menü angezeigt, wenn Excel erkennt, dass sich das Stabilitäts-Add-In in Microsoft Excel befindet. Dies kann passieren, wenn Excel abstürzt, während das Power Pivot-Fenster geöffnet ist. Um zum Power Pivot-Menü zurückzukehren, gehen Sie wie folgt vor:

Wählen Datei > Optionen > Add-ons.

Auf dem Feld Kontrolle wählen Deaktivierte Objekte > Gehen.

Wählen Microsoft OfficePower Pivot und drücken Sie die Taste Anmachen.

Wenn Sie das Power Pivot-Menüband mit den oben genannten Schritten nicht wiederherstellen können oder das Menüband verschwindet, wenn Sie Excel schließen und erneut öffnen, gehen Sie wie folgt vor:

Excel schließen;

Öffnen Sie das Menü Start > Ausführen und geben Sie den Befehl regedit ein;

Erweitern Sie im Registrierungseditor den folgenden Abschnitt:

Für Excel 2013: HKEY_CURRENT_USER > Software > Microsoft > Büro > 15.0 > Benutzereinstellungen.

Für Excel 2016: HKEY_CURRENT_USER > Software > Microsoft > Büro > 16.0 > Benutzereinstellungen

Rechtsklick PowerPivotExcelAddin und dann klicken Löschen;

Kehren Sie zum Anfang des Registrierungseditors zurück.

Erweitern Sie den Abschnitt HKEY_CURRENT_USER > Software > Microsoft > Büro > Excel > Add-Ins;

Rechtsklick PowerPivotExcelClientAddIn.NativeEntry.1 und dann klicken Löschen;

Schließen Sie den Registrierungseditor.

Excel öffnen;

Aktivieren Sie das Add-on, indem Sie die Schritte am Anfang dieses Artikels befolgen.

In diesem Artikel gehen wir durch den Installationsprozess von PowerPivot für Excel und stellen die Hauptelemente dieses Add-Ins vor.

System Anforderungen

Zur Installation benötigen Sie eine 32- oder 64-Bit-Version von Excel 2010 oder älter, die auf dem Windows-Betriebssystem läuft.

Für den Download des PowerPivot-Add-Ins stehen zwei Versionen zur Verfügung: 32-Bit und 64-Bit.

Wenn Sie nicht sicher sind, welche Bittiefe Ihre Version hat, gehen Sie zu Excel und gehen Sie zur Registerkarte Datei -> Konto -> Über Excel. Im erscheinenden Dialogfenster finden Sie die Informationen, die Sie interessieren.

PowerPivot installieren

Für Excel 2010 müssen Sie das Installationspaket von der offiziellen Microsoft-Website herunterladen.

Stellen Sie sicher, dass alle Office-Programme geschlossen sind, und führen Sie das Installationsprogramm aus. Die Installation ist in wenigen Minuten abgeschlossen. Wenn Sie Excel zum ersten Mal starten, wird eine kurze Benachrichtigung angezeigt, dass Microsoft PowerPivot registriert. Bei späteren Starts werden Sie keine mit dem Add-In verbundene Verzögerung bemerken.

Für Excel 2013 muss nichts heruntergeladen werden, da PowerPivot bereits im Paket enthalten ist. Gehen Sie zur Registerkarte Datei -> Optionen -> Add-ons. Wählen Sie im Dialogfeld aus Add-ons COM –> Los. Aktivieren Sie im angezeigten Fenster das Kontrollkästchen neben Microsoft Office PowerPivot für Excel 2013 und drücke OK.

PowerPivot-Registerkarte

Nach der Installation sehen Sie auf der rechten Seite des Menübands eine neue PowerPivot-Registerkarte, die eine ungewöhnliche Reihe von Symbolen enthält. Einige davon sind in der PowerPivot-Anwendung dupliziert.

FensterPowerPivot. Ruft die PowerPivot-Anwendung auf.

Erstellen Sie eine Kennzahl. Nachdem Sie eine PivotTable erstellt haben, können Sie die DAX-Formelsprache verwenden, um ein berechnetes Feld zu erstellen. In PowerPivot werden diese Felder als Kennzahlen bezeichnet.

Erstellen Sie eine verknüpfte Tabelle. Es gibt zwei Möglichkeiten, Daten aus einer Excel-Tabelle in PowerPivot abzurufen. Kopieren Sie zunächst die Daten und fügen Sie sie zur Verarbeitung in der Anwendung ein. Zweitens: Erstellen einer Verbindung mit einem echten Tisch. Für diese Zwecke benötigen Sie dieses Symbol.

Liste der Felder. Die Liste der Felder für PowerPivot ist nicht genau dieselbe wie für PivotTables. Das Entwicklungsteam hat die OLAP-Feldliste in eine benutzerfreundlichere PowerPivot-Ansicht umgewandelt. Mit diesem Symbol können Sie es ausblenden.

Erkennung von Verbindungen. Mit diesem Schalter können Sie Beziehungen zwischen Tabellen automatisch erkennen.

PowerPivot-Fensterregisterkarten

Wenn Sie auf die Schaltfläche klicken PowerPivot-Fenster Auf der linken Seite des Menübands werden Sie zur PowerPivot-Anwendung weitergeleitet, die über vier Menübandregisterkarten verfügt.

Zum Anfang. Hier finden Sie Tools zum Importieren von Daten in PowerPivot, zum Erstellen von Pivot-Tabellen sowie Filter- und Sortiertools.

Konstrukteur. Tools zum Verwalten von Beziehungen zwischen Tabellen.

Verwandte Tabellen. Tools zum Verwalten von Links aus einer Excel-Tabelle.

Verwenden der Registerkarte „Datei“.

Alle Registerkartenelemente Datei mit Excel verknüpft. Klicke auf Speichern als Sie kehren zu Excel zurück und öffnen einen Speicherdialog. Klicke auf Schließen bringt Sie einfach zu Excel zurück.

Verwenden der Registerkarte „Startseite“.

Auf der rechten Seite der Registerkarte Zum Anfang Es gibt Tools zum Importieren von Daten in PowerPivot. Sie können die Zwischenablage verwenden, um eine neue Tabelle einzufügen oder Zeilen in einer vorhandenen Tabelle hinzuzufügen oder zu ersetzen. Sie können auch Daten aus Datenbanken, einem SharePoint-Bericht, einem ATOM-Datenfeed oder einer Textdatei importieren.

Auf der rechten Seite der Registerkarte Zum Anfang Es gibt Tools zum Anzeigen von Daten in FensterPowerPivot.

Verwenden der Registerkarte „Design“.

In der Registerkarte Konstrukteur Sie können neue Spalten hinzufügen oder Beziehungen zwischen Tabellen erstellen. Darüber hinaus können Sie in dieser Registerkarte DAX-Formeln eingeben und berechnete Spalten erstellen.

Verwenden der Registerkarte „Verknüpfte Tabellen“.

Diese Registerkarte ist nur verfügbar, wenn Sie eine mit Excel verknüpfte Tabelle haben. Hier können Sie die Symbole verwenden, um Daten zu aktualisieren oder den Aktualisierungsmodus zu wechseln: automatisch oder manuell.

/ Business Intelligence mit PowerPivot in Microsoft Excel

In Kontakt mit

Klassenkameraden

Kuleshova Olga, MST, leitender Dozent am Computer Training Center „Specialist“ der MSTU. N.E. Baumann

Geschäftsanalysen
mit PowerPivot in Microsoft Excel

Je neuer die Excel-Version, desto mehr Tools zur Datenanalyse. Schauen wir uns die Möglichkeiten der Analyse großer Datenmengen mithilfe des PowerPivot-Modells und PowerView-Berichten an

Tools wie PowerPivot allein und insbesondere in Kombination mit PowerView sind moderne Business Intelligence (BI)-Tools, die in der Lage sind, riesige Datenmengen sowohl aus einzelnen als auch aus mehreren Quellen zu speichern, zu verarbeiten und zu visualisieren.

Wo ist PowerPivot in Excel?

Die Möglichkeit, Daten mit PowerPivot zu analysieren, ist verfügbar:

• Benutzer von Microsoft Office 2013 Professional Plus oder Office 365 ProPlus;
• sowie Benutzer von Microsoft Excel 2010.

Power Pivot in Microsoft Excel 2013 ist ein Microsoft Office PowerPivot für Excel 2013-Add-In, das in Excel 2013 integriert, aber nicht aktiviert ist. Um also weiterhin mit PowerPivot arbeiten zu können, müssen Sie es in der Com-Add-Ins-Liste aktivieren.

Besitzer von Excel 2010 müssen zur Verwendung von PowerPivot zunächst die PowerPivot-Komponente (PowerPivot_for_Excel.msi) aus dem Microsoft Download Center herunterladen. Sie sollten auf die Bittiefe der Add-In-Version achten – 32 oder 64 Bit – und die von Ihnen benötigte auswählen.

Sie können das Add-In auch nicht verwenden, wenn Sie Excel 2010 installiert haben, Office Common Tools jedoch nicht installiert ist.

Das PowerPivot-Add-In ist eine entsprechend benannte Registerkarte (siehe Abbildung 1). Die Arbeit mit dem Modell beginnt durch Klicken auf die Schaltfläche „Verwalten“.

PowerPivot-Modellquellen

Quellen für PowerPivot können sein:

• Relationale Datenbanken: Microsoft SQL Server, Microsoft SQL Azure, Microsoft SQL Server Parallel Data Warehouse, Microsoft Access, Oracle, Teradata, Sybase, Informix, IBM DB2, andere (OLEDB/ODBC).
• Mehrdimensionale Quellen: Microsoft Analysis Services.
• Web-Datenfeeds.
• Textdateien: Excel-Dateien, Textdateien (*.csv oder *.txt).

Beim Abrufen externer Daten hat der Benutzer jederzeit die Möglichkeit, die Tabellen auszuwählen, aus denen Daten importiert werden sollen. Darüber hinaus kann dies entweder unabhängig oder mit Hilfe eines Programms erfolgen. Um verwandte Tabellen auszuwählen, müssen Sie eine Tabelle auswählen und dann den Befehl „Zugehörige Tabellen auswählen“ verwenden (siehe Abb. 2).

Wenn die Quelltabelle zu viele Daten enthält, aber nur ein bestimmter Teil analysiert werden muss, können Sie beim Datenimport eine Filterung durchführen: Wählen Sie die Tabelle aus und klicken Sie auf die Schaltfläche „Anzeigen und filtern“. Deaktivieren Sie im angezeigten Fenster die Spalten (Felder), die nicht in den Import einbezogen werden müssen, und legen Sie für jede Spalte ggf. die Bedingungen für die Datenauswahl mithilfe eines Filters fest, wie beim Arbeiten mit Tabellen in Excel. Während des Importvorgangs wird der Fortschritt des Vorgangs angezeigt und nach Abschluss wird ein Bericht über den Vorgang bereitgestellt – welche Tabellen mit welcher Zeilenanzahl und wie erfolgreich importiert wurden (siehe Abb. 3).

Der Importvorgang ist abgeschlossen. Das Ergebnis ist ein PowerPivot für Excel-Fenster, das Registerkarten mit den Namen der importierten Tabellen enthält: „Bestellungen“ und „Mitarbeiter“. Jede Quelle kann eine Anzahl von Datensätzen enthalten, die die Anzahl der Zeilen in der Excel-Tabelle deutlich übersteigt.

PowerPivot-Funktionen und -Funktionen

• Beim Durchführen eines Imports können Sie Spalten und Tabellen filtern und umbenennen. Jede Tabelle befindet sich auf einer separaten Seite (Registerkarte) des PowerPivot-Fensters.
• Sie können in PowerPivot keine einzelnen Zellendaten bearbeiten.
• Tabellen aus anderen Quellen können sofort importiert werden, wobei die Beziehungen erhalten bleiben, oder Beziehungen können direkt in PowerPivot an zwei Orten erstellt werden: in der Diagrammansicht oder im Fenster „Beziehungen erstellen“.
• Die Menge der analysierten Daten kann nur durch die Größe des Arbeitsspeichers begrenzt werden.
• PowerPivot bietet erweiterte Formeln in der Sprache Data Analysis Expression (DAX).
• Zur Arbeitserleichterung können Sie Datenunterordnungen in Form von Hierarchien aufbauen, um diese später in Berichten zu verwenden.
• Um den Zustand bestimmter Werte (Felder) zu analysieren, erstellt PowerPivot Key Performance Indicators (KPIs), die sowohl in PowerView-Berichten als auch in PivotTables verwendet werden können.

Berechnungen in PowerPivot-Quellen

Anstatt eine Wertespalte einzufügen oder zu importieren, wird eine DAX-Formel erstellt, die ihre Werte definiert. Sie können DAX-Formeln verwenden, um benutzerdefinierte Berechnungen in PowerPivot-Tabellen (berechnete Spalten) und Excel-Pivot-Tabellen (berechnete Felder) zu definieren. DAX umfasst einige der in Excel-Formeln verwendeten Funktionen sowie zusätzliche Funktionen für die Arbeit mit relationalen Daten und die Erstellung dynamischer Aggregationen.

Die folgenden Kategorien von DAX-Funktionen sind zu finden: Datum und Uhrzeit, Filter, logische, arithmetische und trigonometrische Funktionen, Statistik, Text, Informationen, Eltern-Kind. Zum größten Teil reproduzieren sie die Fähigkeiten ähnlicher Excel-Funktionen vollständig, sodass kein erneutes Erlernen erforderlich ist.

Es gibt jedoch einige kleine Unterschiede im Vergleich zu regulären Excel-Berechnungen: PowerPivot fügt keine schließenden Funktionsklammern hinzu und passt auch keine automatischen Klammern an. Daher wird die Berechnung durchgeführt, wenn die Funktion syntaktisch korrekt geschrieben ist, andernfalls kann die Formel nicht gespeichert oder verwendet werden.

Beim Erstellen von Formeln müssen Sie den Unterschied zwischen einem berechneten Feld und einer berechneten Spalte verstehen.

Ein berechnetes Feld ist eine Formel, die speziell für eine PivotTable (oder ein PivotChart) erstellt wurde, die PowerPivot-Daten verwendet. Berechnete Felder können auf Standardfunktionen wie Summen, Durchschnittswerten, Mindest- und Höchstwerten, Kardinalität und komplexeren Berechnungen basieren, die durch DAX-Formeln erstellt werden.

Das berechnete Feld wird im Wertebereich der PivotTable verwendet. Der Wert eines berechneten Felds ändert sich immer als Reaktion auf die Zeilen-, Spalten- und Filterauswahl. Wenn Sie ein berechnetes Feld hinzufügen, wird für jede Zelle im Wertebereich der PivotTable eine Formel erstellt. Das berechnete Feld wird zusammen mit der Quelldatentabelle gespeichert, erscheint in der Liste der Felder in der Pivot-Tabelle und steht allen Benutzern der Arbeitsmappe zur Verfügung.

Wenn Sie die Ergebnisse der Berechnungen in einem anderen Bereich der Pivot-Tabelle platzieren müssen, müssen Sie eine berechnete Spalte erstellen. Wie in Excel können Sie beim Erstellen einer Formel die automatische Vervollständigung von Formeln verwenden: Um Daten aus einer Tabelle auszuwählen, geben Sie den ersten Buchstaben der Tabelle ein; um einen Spaltennamen auszuwählen, geben Sie eine eckige Klammer ein und wählen Sie den gewünschten aus vorgeschlagene Liste der Spalten der aktuellen Tabelle.

Wenn Sie in PowerPivot mit Daten arbeiten, müssen Sie implizite und explizite berechnete Felder kennen.

Ein implizit berechnetes Feld wird von Excel erstellt, wenn Sie ein Feld in den Wertebereich der PivotTable-Feldliste ziehen. Dieses Feld wird von Excel automatisch erstellt und ist leicht zu erkennen: Wenn Sie ein solches Feld im Wertebereich platzieren, wird die Spaltensumme sofort zu seinem Namen hinzugefügt.

Ein implizit berechnetes Feld kann nur die Standardaggregatfunktionen verwenden: SUM, COUNT, MIN, MAX, DISTINCT, COUNT, AVG und das für diese Aggregation angegebene Datenformat. Implizit berechnete Felder können nur in der PivotTable oder dem Diagramm verwendet werden, für die sie erstellt wurden.

Explizite berechnete Felder können in allen PivotTable- oder PivotChart-, Arbeitsmappen- und PowerView-Berichten verwendet werden. Darüber hinaus können sie in KPIs umgewandelt oder formatiert werden. Das Erstellen eines KPI ist nur möglich, wenn ein explizit berechnetes Feld verwendet wird.

Beim Erstellen eines berechneten Felds in einem PowerPivot-Modell in einer Formel können Sie wie in Excel-Formeln bis zu 64 Verschachtelungsebenen von Funktionen verwenden, alles ist nach dem Prinzip aufgebaut:

Berechneter_Feldname:=Formel

PowerPivot-Modellberichte

Die Quelldaten des Modells sind im PowerPivot für Excel-Fenster enthalten und die Berichte selbst werden im Microsoft Excel-Fenster erstellt.

Berichte zu Datenmodellen können aus jeder der vorgeschlagenen Optionen erstellt werden: einer Pivot-Tabelle, einem Pivot-Diagramm, einem Diagramm und einer Tabelle (horizontal oder vertikal), zwei Diagrammen (horizontal oder vertikal), vier Diagrammen, einer flachen Pivot-Tabelle (siehe Abb. 4).

Nachdem Sie die Anzahl der in tabellarischer oder grafischer Form angezeigten Berichte ausgewählt haben, müssen Sie deren Inhalt generieren. Die Besonderheit bei der Erstellung dieser Berichte besteht darin, dass jeder Bereich unabhängig von den anderen ist, weil Sie können verschiedene Filter durchführen, die nur in einem bestimmten Bereich des Berichts wirksam werden und in einem anderen nicht. Beim Erstellen und Formatieren von Berichten gibt es keine Schwierigkeiten, da die Erstellung von Pivot-Tabellen und Diagrammen auf die gleiche Weise erfolgt wie die einfache Verwendung von Daten in einer Excel-Tabelle.

In PivotTable-Berichten können Sie Daten anhand eines KPI-Leistungsindikators auswerten. Key Performance Indicators auf der Ebene der Geschäftsprozesse ermöglichen Ihnen die Analyse und Steuerung der wesentlichen Punkte. Sie können Berichte erstellen und die visuelle Anzeige von Daten mithilfe der bedingten Formatierung selbst anpassen, müssen dies jedoch in jedem Bericht immer wieder tun (siehe Abb. 5). Durch das Erstellen eines KPI in PowerPivot kann dieser in jedem von Ihnen erstellten Bericht verwendet werden, der auf einer bestimmten Quelle basiert.

In allen erstellten Berichten können Sie Slices zum einfachen Filtern verwenden – schnell, bequem und visuell. Slices können entweder manuell oder mit den Befehlen „Vertikal ausrichten“ oder „Horizontal ausrichten“ formatiert und angeordnet werden. Die Arbeit mit Slicern ähnelt der Arbeit mit ihnen in regulären Pivot-Tabellen ab Excel 2010.

PowerView-Berichte

PowerView sind Berichte, die auch aus Daten einer oder mehrerer Tabellen unter Erstellung einer Beziehung erstellt werden können und Informationen sowohl in tabellarischer als auch in grafischer Form bereitstellen. In einem Bericht, der auf einem separaten Blatt erstellt wird, können Sie viele Variationen erstellen, und wenn Sie die Daten filtern, erhalten Sie eine hervorragende Datenvisualisierung.

Um einen Bericht zu erstellen, wählen Sie im Excel-Fenster den PowerView-Befehl auf der Registerkarte „Einfügen“. Um die gewünschten Felder zu Ihrem Bericht hinzuzufügen, müssen Sie sie in den Berichtsbereich oder in den Feldbereich unten im Power View-Feldbereich verschieben (ähnlich dem ROW-Bereich in PivotTable-Berichten). Wenn Sie ein Feld aus der Feldliste in den Bereich FRAGMENTIERUNGSREGEL ziehen, können Sie dann schnell Daten für dieses Feld auswählen (Filterung). Alle Daten im Bericht können sowohl einzeln als auch zusammen in tabellarischer und grafischer Form dargestellt werden.

Um Daten gleichzeitig in zwei Ansichten anzuzeigen, kopieren Sie die Tabelle an eine leere Stelle im Bericht und wählen Sie dann die gewünschte Anzeigeoption aus den verfügbaren aus: Tabelle, Balkendiagramm, Balkendiagramm oder anderes Diagramm (Kreis, Punktdiagramm). Durch Klicken auf ein Diagrammelement setzen Sie einen Filter nach diesem Wert, sowie in anderen Berichtsobjekten (siehe Abb. 6).

PowerView-Berichte sind dynamische Berichte. Das Filtern in einem Berichtselement wirkt sich sofort auf die Daten eines anderen Berichtselements aus, wenn diese auf Daten aus einer oder mehreren zugehörigen Tabellen basieren. Sie können auch einen Bericht erstellen, der auf Daten aus mehreren Tabellen basiert, die völlig unabhängig voneinander sind, Ihnen aber die gleichzeitige Analyse der Daten ermöglichen.

PowerView-Berichte bieten eine schnelle interaktive Ansicht von Daten, eine klare Visualisierung und verschiedene visuelle Möglichkeiten zur Datenpräsentation.

Inwiefern ähneln sich das PowerPivot-Datenmodell und die PowerView-Berichte?

Jeder Benutzer kann mit dem PowerPivot-Datenmodell arbeiten: Es ist nicht notwendig, viele spezielle Programme zu installieren und zu konfigurieren – alles auf einmal in einem Add-on; Daten werden dem Modell durch einfache Importvorgänge mit Aktualisierungsmöglichkeit hinzugefügt; ohne zusätzliche Kenntnisse über relationale Datenbanken können Sie Beziehungen zwischen Tabellen erstellen; Mithilfe bereits bekannter Formeln können Sie Berechnungen im Modell erstellen.

PowerView-Berichte: schnell aufgebaut, intuitiv, dynamisch. Dies sind Eigenschaften, die für einen Benutzer, der ständig mit großen Datenmengen konfrontiert ist, unerlässlich sind.

In Kontakt mit