Vorteile von Pivot by Data Model

Beim Erstellen einer Pivot-Tabelle in Excel befindet sich im allerersten Dialogfeld, in dem wir aufgefordert werden, den Anfangsbereich festzulegen und einen Ort zum Einfügen der Pivot-Tabelle auszuwählen, darunter ein unauffälliges, aber sehr wichtiges Kontrollkästchen – Fügen Sie diese Daten dem Datenmodell hinzu (Fügen Sie diese Daten hinzu zum Datenmodell) und etwas höher der Schalter Verwenden Sie das Datenmodell dieses Buchs (Datenmodell dieser Arbeitsmappe verwenden):

Leider verstehen viele Anwender, die sich schon lange mit Pivot-Tabellen auskennen und diese erfolgreich in ihrer Arbeit einsetzen, manchmal die Bedeutung dieser Optionen nicht wirklich und verwenden sie nie. Und vergebens. Schließlich bietet uns das Erstellen einer Pivot-Tabelle für das Datenmodell einige sehr wichtige Vorteile gegenüber der klassischen Excel-Pivot-Tabelle.

Bevor wir uns diese „Brötchen“ jedoch näher ansehen, wollen wir zunächst verstehen, was dieses Datenmodell tatsächlich ist.

Was ist ein Datenmodell

Datenmodell (abgekürzt als MD oder DM = Data Model) ist ein spezieller Bereich innerhalb einer Excel-Datei, in dem Sie tabellarische Daten speichern können – eine oder mehrere Tabellen, die auf Wunsch miteinander verknüpft sind. Tatsächlich handelt es sich dabei um eine kleine Datenbank (OLAP-Würfel), die in eine Excel-Arbeitsmappe eingebettet ist. Im Vergleich zur klassischen Speicherung von Daten in Form von regulären (oder intelligenten) Tabellen auf Excel-Blättern selbst hat das Datenmodell mehrere wesentliche Vorteile:

• Tabellen können bis zu sein 2 Milliarden Zeilen, und eine Excel-Tabelle kann etwas mehr als 1 Million aufnehmen.
• Trotz der gigantischen Größe wird die Verarbeitung solcher Tabellen (Filtern, Sortieren, Berechnungen darauf, Erstellen von Zusammenfassungen usw.) durchgeführt sehr schnell Viel schneller als Excel selbst.
• Mit den Daten im Modell können Sie zusätzliche (falls gewünscht sehr komplexe) Berechnungen durchführen integrierte DAX-Sprache.
  
• Alle in das Datenmodell geladenen Informationen sind sehr stark komprimiert Verwenden eines speziellen integrierten Archivierers und erhöhen die Größe der ursprünglichen Excel-Datei eher moderat.
  

Das Modell wird von einem speziellen Add-In verwaltet und berechnet, das in Microsoft Excel integriert ist – PowerPivotworüber ich schon geschrieben habe. Um es zu aktivieren, auf der Registerkarte Entwickler klicken Sie auf COM-Add-Ins (Entwickler – COM-Add-Ins) und markieren Sie das entsprechende Kästchen:

Wenn Registerkarten Entwickler (Entwickler)Sie können es nicht auf dem Band sehen, Sie können es durch einschalten Datei – Optionen – Menüband einrichten (Datei – Optionen – Menüband anpassen). Wenn Sie im oben gezeigten Fenster in der Liste der COM-Add-Ins kein Power Pivot haben, dann ist es nicht in Ihrer Version von Microsoft Office 🙁 enthalten

Auf der angezeigten Registerkarte Power Pivot befindet sich eine große hellgrüne Schaltfläche Management (Verwalten), wenn Sie darauf klicken, öffnet sich das Power Pivot-Fenster über Excel, wo wir den Inhalt des Datenmodells des aktuellen Buchs sehen:

Ein wichtiger Hinweis am Rande: Eine Excel-Arbeitsmappe kann nur ein Datenmodell enthalten.

Tabellen in das Datenmodell laden

Um Daten in das Modell zu laden, verwandeln wir die Tabelle zunächst in eine dynamische „intelligente“ Tastenkombination Ctrl+T und geben Sie ihm auf der Registerkarte einen benutzerfreundlichen Namen Bauherr (Design). Dies ist ein erforderlicher Schritt.

Dann können Sie eine der drei Methoden zur Auswahl verwenden:

• Drücken Sie die Taste Zum Modell hinzufügen (Zum Datenmodell hinzufügen) Tab PowerPivot Tab Startseite (Home).
  
• Mannschaften auswählen Einfügen – PivotTable (Einfügen – Pivot-Tabelle) und aktivieren Sie das Kontrollkästchen Fügen Sie diese Daten dem Datenmodell hinzu (Diese Daten zum Datenmodell hinzufügen). In diesem Fall wird gemäß den in das Modell geladenen Daten auch sofort eine Pivot-Tabelle erstellt.
  
• Auf der Registerkarte Erweitert Datum (Datum) Klicken Sie auf die Schaltfläche Aus Tabelle/Bereich (Aus Tabelle/Bereich)um unsere Tabelle in den Power Query-Editor zu laden. Dieser Weg ist der längste, aber wenn Sie möchten, können Sie hier zusätzliche Datenbereinigungen, Bearbeitungen und alle Arten von Transformationen durchführen, in denen Power Query sehr stark ist.

  Dann werden die gekämmten Daten durch den Befehl in das Modell hochgeladen Home — Schließen und laden — Schließen und laden in… (Home — Schließen&Laden — Schließen&Laden nach…). Wählen Sie im sich öffnenden Fenster die Option aus Stellen Sie einfach eine Verbindung her (Nur Verbindung herstellen) und vor allem ein Häkchen setzen Fügen Sie diese Daten dem Datenmodell hinzu (Diese Daten zum Datenmodell hinzufügen).
  

Wir erstellen eine Zusammenfassung des Datenmodells

Um ein zusammenfassendes Datenmodell zu erstellen, können Sie einen von drei Ansätzen verwenden:

• Knopf drücken Übersichtstabelle (Pivot-Tabelle) im Power Pivot-Fenster.
• Wählen Sie Befehle in Excel aus Einfügen – PivotTable und wechseln Sie in den Modus Verwenden Sie das Datenmodell dieses Buchs (Einfügen – Pivot-Tabelle – Datenmodell dieser Arbeitsmappe verwenden).
• Mannschaften auswählen Einfügen – PivotTable (Einfügen – Pivot-Tabelle) und aktivieren Sie das Kontrollkästchen Fügen Sie diese Daten dem Datenmodell hinzu (Diese Daten zum Datenmodell hinzufügen). Die aktuelle „intelligente“ Tabelle wird in das Modell geladen und eine Übersichtstabelle wird für das gesamte Modell erstellt.

Nachdem wir nun herausgefunden haben, wie Daten in das Datenmodell geladen und eine Zusammenfassung darauf erstellt werden, wollen wir die Vorteile und Vorteile untersuchen, die uns dies bietet.

Vorteil 1: Beziehungen zwischen Tabellen ohne Verwendung von Formeln

Eine regelmäßige Zusammenfassung kann nur mit Daten aus einer Quelltabelle erstellt werden. Wenn Sie mehrere davon haben, z. B. Verkauf, Preisliste, Kundenverzeichnis, Vertragsregister usw., müssen Sie zunächst die Daten aller Tabellen mit Funktionen wie VLOOKUP zu einer zusammenführen (SVERWEIS), INDEX (INDEX), AUSGESETZTER (PASSEN), SUMMESLIMN (SUMME) und dergleichen. Das ist langwierig, mühsam und treibt Ihr Excel mit einer großen Datenmenge in einen „Gedanken“.

Bei einer Zusammenfassung des Datenmodells ist alles viel einfacher. Es reicht aus, Beziehungen zwischen Tabellen einmal im Power Pivot-Fenster einzurichten – fertig. Dazu auf der Registerkarte PowerPivot Drücken Sie den Knopf Management (Verwalten) und dann im erscheinenden Fenster – die Schaltfläche Diagrammansicht (Diagrammansicht). Es bleibt übrig, gemeinsame (Schlüssel-)Spaltennamen (Felder) zwischen Tabellen zu ziehen, um Verknüpfungen zu erstellen:

Danach können Sie in der Zusammenfassung für das Datenmodell beliebige Felder aus allen zugehörigen Tabellen in den Zusammenfassungsbereich (Zeilen, Spalten, Filter, Werte) werfen – alles wird automatisch verknüpft und berechnet:

Vorteil 2: Zählen Sie eindeutige Werte

Eine normale Pivot-Tabelle gibt uns die Möglichkeit, eine von mehreren integrierten Berechnungsfunktionen auszuwählen: Summe, Durchschnitt, Anzahl, Minimum, Maximum usw. In der Zusammenfassung des Datenmodells wird dieser Standardliste eine sehr nützliche Funktion hinzugefügt, um die zu zählen Anzahl eindeutiger (sich nicht wiederholender Werte). Mit seiner Hilfe können Sie zum Beispiel ganz einfach die Anzahl der einzigartigen Waren (Sortiment) zählen, die wir in jeder Stadt verkaufen.

Klicken Sie mit der rechten Maustaste auf das Feld – Befehl Optionen für Wertfelder und auf der Registerkarte Produktion Auswählen Anzahl verschiedener Elemente (Eindeutige Zählung):

Vorteil 3: Benutzerdefinierte DAX-Formeln

Manchmal müssen Sie in Pivot-Tabellen verschiedene zusätzliche Berechnungen durchführen. Bei regulären Zusammenfassungen erfolgt dies über berechnete Felder und Objekte, während bei der Datenmodellzusammenfassung Kennzahlen in einer speziellen DAX-Sprache (DAX = Data Analysis Expressions) verwendet werden.

Um eine Maßnahme zu erstellen, wählen Sie auf der Registerkarte aus PowerPivot Befehl Maßnahmen – Maßnahme erstellen (Maßnahmen — Neue Maßnahme) oder klicken Sie einfach mit der rechten Maustaste auf die Tabelle in der Pivot-Felderliste und wählen Sie sie aus Maß hinzufügen (Maß hinzufügen) im Kontextmenü:

Stellen Sie in dem sich öffnenden Fenster Folgendes ein:

• Tabellennamewo die erstellte Kennzahl gespeichert wird.
• Name messen – ein beliebiger Name für das neue Feld, den Sie verstehen.
• Beschreibung - Optional.
• Formel – das Wichtigste, denn hier geben wir entweder manuell ein oder klicken auf die Schaltfläche fx und wählen Sie aus der Liste eine DAX-Funktion aus, die das Ergebnis berechnen soll, wenn wir dann unser Measure in den Values-Bereich werfen.
• Im unteren Teil des Fensters können Sie sofort das Zahlenformat für die Kennzahl in der Liste einstellen Kategorie.
  

Die DAX-Sprache ist nicht immer einfach zu verstehen, weil sie nicht mit einzelnen Werten, sondern mit ganzen Spalten und Tabellen operiert, also ein gewisses Umdenken nach den klassischen Excel-Formeln erfordert. Es lohnt sich jedoch, denn die Leistungsfähigkeit seiner Fähigkeiten bei der Verarbeitung großer Datenmengen ist schwer zu überschätzen.

Vorteil 4: Benutzerdefinierte Feldhierarchien

Beim Erstellen von Standardberichten müssen Sie beispielsweise häufig dieselben Kombinationen von Feldern in einer bestimmten Reihenfolge in Pivot-Tabellen werfen Jahr-Quartal-Monat-Tag, oder Kategorie-Produkt, oder Land-Stadt-Kunde usw. In der Zusammenfassung des Datenmodells lässt sich dieses Problem leicht lösen, indem Sie Ihr eigenes erstellen Hierarchien — benutzerdefinierte Feldsätze.

Wechseln Sie im Power Pivot-Fenster mit der Schaltfläche in den Diagrammmodus Diagrammansicht Tab Startseite (Start — Diagrammansicht), mit auswählen Ctrl gewünschten Felder und klicken Sie mit der rechten Maustaste darauf. Das Kontextmenü enthält den Befehl Hierarchie erstellen (Hierarchie erstellen):

Die erstellte Hierarchie kann umbenannt und die benötigten Felder mit der Maus hineingezogen werden, damit sie später in einem Zug in die Zusammenfassung geworfen werden können:

Vorteil 5: Benutzerdefinierte Schablonen

Um die Idee des vorherigen Absatzes fortzusetzen, können Sie in der Zusammenfassung des Datenmodells auch Ihre eigenen Sätze von Elementen für jedes Feld erstellen. Beispielsweise können Sie aus der gesamten Liste der Städte ganz einfach nur diejenigen auswählen, die in Ihrem Zuständigkeitsbereich liegen. Oder sammeln Sie nur Ihre Kunden, Ihre Waren etc. in einem speziellen Set.

Dazu auf der Registerkarte Pivot-Tabellenanalyse in der Dropdown-Liste Felder, Items und Sets Es gibt entsprechende Befehle (Analysieren – Fields, Items & Sets – Set basierend auf Zeilen-/Spaltenelementen erstellen):

In dem sich öffnenden Fenster können Sie beliebige Elemente gezielt entfernen, hinzufügen oder ihre Position ändern und die resultierende Menge unter einem neuen Namen speichern:

Alle erstellten Sätze werden im Bereich PivotTable-Felder in einem separaten Ordner angezeigt, von wo aus sie frei in die Zeilen- und Spaltenbereiche jeder neuen PivotTable gezogen werden können:

Vorteil 6: Tabellen und Spalten selektiv ausblenden

Das ist zwar ein kleiner, aber in manchen Fällen sehr angenehmer Vorteil. Durch einen Rechtsklick auf den Feldnamen oder auf die Tabellenregisterkarte im Power Pivot-Fenster können Sie den Befehl auswählen Aus dem Client-Toolkit ausblenden (Aus Client-Tools ausblenden):

Die ausgeblendete Spalte oder Tabelle verschwindet aus dem PivotTable-Feldlistenbereich. Es ist sehr praktisch, wenn Sie einige Hilfsspalten (z. B. berechnete oder Spalten mit Schlüsselwerten zum Erstellen von Beziehungen) oder sogar ganze Tabellen vor dem Benutzer verbergen müssen.

Vorteil 7. Erweiterter Drilldown

Wenn Sie in einer regulären Pivot-Tabelle auf eine beliebige Zelle im Wertebereich doppelklicken, zeigt Excel auf einem separaten Blatt eine Kopie des Quelldatenfragments an, das an der Berechnung dieser Zelle beteiligt war. Dies ist eine sehr praktische Sache, die offiziell Drill-Down genannt wird (in der Regel heißt es „fehlgeschlagen“).

In der Zusammenfassung des Datenmodells arbeitet dieses praktische Werkzeug subtiler. Wenn Sie sich auf eine beliebige Zelle mit dem für uns interessanten Ergebnis stellen, können Sie auf das Symbol mit einer Lupe klicken, die daneben auftaucht (es heißt Express-Trends) und wählen Sie dann ein beliebiges Feld in einer verwandten Tabelle aus, an dem Sie interessiert sind:

Danach kommt der aktuelle Wert (Model = Explorer) in den Filterbereich und die Zusammenfassung wird nach Ämtern aufgebaut:

Natürlich kann ein solches Verfahren viele Male wiederholt werden, um Ihre Daten konsequent in die Richtung zu vertiefen, die Sie interessiert.

Vorteil 8: Konvertieren Sie Pivot- in Cube-Funktionen

Wenn Sie eine beliebige Zelle in der Zusammenfassung für das Datenmodell auswählen und dann auf der Registerkarte auswählen Pivot-Tabellenanalyse Befehl OLAP Tools – Konvertieren in Formeln (Analysieren – OLAP-Tools – In Formeln konvertieren), dann wird die gesamte Zusammenfassung automatisch in Formeln umgewandelt. Jetzt werden die Feldwerte im Zeilen-Spalten-Bereich und die Ergebnisse im Wertebereich mithilfe der speziellen Cube-Funktionen aus dem Datenmodell abgerufen: CUBEVALUE und CUBEMEMBER:

Technisch bedeutet dies, dass wir es jetzt nicht mit einer Zusammenfassung zu tun haben, sondern mit mehreren Zellen mit Formeln, dh wir können mit unserem Bericht problemlos alle Transformationen vornehmen, die in der Zusammenfassung nicht vorhanden sind, z. B. neue Zeilen oder Spalten in die Mitte einfügen des Berichts, führen Sie zusätzliche Berechnungen innerhalb der Zusammenfassung durch, ordnen Sie sie beliebig an usw.

Dabei bleibt natürlich die Verbindung zu den Quelldaten erhalten und in Zukunft werden diese Formeln aktualisiert, wenn sich die Quellen ändern. Die Schönheit!

• Plan-Fakten-Analyse in einer Pivot-Tabelle mit Power Pivot und Power Query
• Pivot-Tabelle mit mehrzeiliger Kopfzeile
• Erstellen Sie mit Power Pivot eine Datenbank in Excel