Wie man Power Query und Power Pivot in Excel wie ein Profi nutzt

Wenn Sie ein wahrer Power-User von Tabellenkalkulationen werden möchten, müssen Sie die Funktionen Power Query und Power Pivot in Excel kennenlernen. Während Sie mit Excel allein viel erreichen können, werden Sie durch die Nutzung dieser integrierten “Power”-Funktionen zu einem fortgeschrittenen professionellen Excel-Nutzer. In diesem Leitfaden lernen Sie, wie Sie Power Query verwenden, um potenziell Hunderte von verschiedenen Dateien mit Millionen von Zeilen zu importieren, und Power Pivot, um komplexe Analysen auf den massiven Datenmodellen zu erstellen, die Sie importieren.

Inhaltsverzeichnis

• 1. Daten aus mehreren Dateien importieren
• 2. Die importierten Dateien und Zeilen filtern
• 3. Gefilterte Daten in ein Datenmodell laden
• 4. Daten mit PivotTable analysieren
• 5. Ihre Daten mit PowerChart präsentieren
• Häufig gestellte Fragen

Möchten Sie Ihre Arbeit vor neugierigen Blicken schützen? Erfahren Sie, wie Sie ein Passwort für Ihre Excel-Arbeitsmappe hinzufügen.

1. Daten aus mehreren Dateien importieren

Mit Power Query können Sie Daten aus verschiedenen Dateien in einem einzigen Ordner importieren. Seien Sie jedoch vorsichtig, da Sie sicherstellen sollten, dass alle Daten dasselbe Format haben. Halten Sie die Anzahl der Spalten und die Spaltenüberschriften gleich und stellen Sie sicher, dass der Datentyp für die Werte in jeder Spalte konsistent ist.

Für diesen Leitfaden verwenden wir fünf Dateien, die etwa 100 Millionen Zeilen Daten von Kaggle enthalten. Laden Sie sie herunter, wenn Sie mitmachen möchten, oder verwenden Sie Ihre eigenen Daten. Vor diesem Hintergrund sind hier die Schritte zum Importieren der Daten:

1. Klicken Sie auf die Registerkarte “Daten” im Menüband.
2. Wählen Sie “Daten abrufen -> Aus Datei -> Aus Ordner” und navigieren Sie zu Ihrem Ordner und wählen Sie ihn aus.

1. Sie sollten ein neues Fenster sehen. In unserem Fall haben wir fünf Testdateien, die nach ihrer Größe benannt sind: “adult10m” hat 10 Millionen Zeilen Daten, “adult1m” hat 1 Million Zeilen, “adult100k” hat 100.000 Zeilen und so weiter. Klicken Sie auf “Daten transformieren”, um den Power Query-Editor zu öffnen.

2. Die importierten Dateien und Zeilen filtern

Im Power Query-Editor können Sie Filter anwenden, um nur die Dateien zu importieren, die den von Ihnen festgelegten Kriterien entsprechen, was sehr nützlich ist, um Datenquellen zu eliminieren, die Sie nicht möchten.

1. Wir haben vier Dateien ohne Dateierweiterung, aber eine Datei mit der Erweiterung .DATA. Lassen Sie uns die seltsame entfernen, indem wir auf den Pfeil neben “Erweiterung” klicken.

1. Wir sehen eine Liste aller Dateierweiterungen unserer Dateien. In diesem Beispiel sind dies nur (leer) und .data. Deaktivieren Sie die Erweiterungen, die Sie nicht möchten.

1. Jeder Filter, den wir angewendet haben, wird im Abschnitt “Angewandte Schritte” angezeigt.
2. Wir können auch nach Dateinamen filtern, indem wir auf den Pfeil neben “Name” klicken.

1. Angenommen, wir möchten alle Dateien herausfiltern, die ein “k” enthalten, wie “adult100k”, weil wir nicht mit kleinen Dateien arbeiten möchten, die weniger als eine Million Zeilen enthalten. Wir können einen Textfilter anwenden. Es gibt viele Optionen, aber für unsere Zwecke ist der Filter “Enthält nicht” der richtige.

1. Geben Sie den Text ein, den Sie im Dateinamen nicht sehen möchten.

1. Scrollen Sie ein wenig nach rechts, bis Sie die Spalten “Datum geändert” und “Datum erstellt” sehen können. Lassen Sie uns filtern, um nur Dateien auszuwählen, die in einem bestimmten Zeitraum erstellt wurden. Klicken Sie auf den Pfeil neben “Datum erstellt”.

1. Wählen Sie “Datum/Zeit-Filter -> Zwischen”.

1. Geben Sie Zeitwerte für die beiden Bedingungen ein und bestätigen Sie mit “OK”.

1. Wir haben das Filtern unserer Daten auf Dateiebene abgeschlossen.
2. Lassen Sie uns unsere verbleibenden Dateien kombinieren, damit wir die Daten selbst filtern können. Klicken Sie auf die doppelten Pfeile neben der Überschrift “Inhalt”.

1. Jetzt, da Sie alle Werte für einzelne Zeilen sehen können, hat jede Zeile eine zusätzliche Spalte, “Source.Name”, die angibt, aus welcher Datei sie stammt.

1. Um unsere Daten weiter zu verbessern: Da unsere CSV-Dateien ursprünglich keine Spaltenüberschriften hatten, können Sie mit der rechten Maustaste auf einige der Spaltenüberschriften klicken und “Umbenennen” auswählen, um ihnen einen geeigneten Namen zu geben.

1. Filter funktionieren hier auch. Wir können einen Zahlenbereichsfilter anwenden. Klicken Sie beispielsweise auf den Pfeil neben der Spaltenüberschrift “Alter” und wählen Sie “Zahlenfilter -> Größer als”.

1. Geben Sie eine Zahl wie 30 ein und klicken Sie auf “OK”. Alle Zeilen mit einem Alter unter der angegebenen Zahl werden ausgeschlossen.

Tipp: Wenn Sie mit Tabellenkalkulationen in Microsoft Excel arbeiten, kann es nützlich sein zu wissen, wie man eine Spalte verschiebt.

3. Gefilterte Daten in ein Datenmodell laden

Sie können weiterhin die Daten bereinigen und filtern, aber lassen Sie uns fortfahren und sie in ein Datenmodell laden, um mit der Analyse zu beginnen.

1. Beenden Sie Power Query, indem Sie auf “Schließen & Laden -> Schließen & Laden in” klicken.
2. Wählen Sie im sich öffnenden Popup “Nur Verbindung erstellen” und aktivieren Sie “Diese Daten zum Datenmodell hinzufügen”.

1. Warten Sie, bis die Daten geladen sind. Dies kann einige Minuten dauern, wenn Sie eine große Menge an Daten haben.

Verwendung des Power Pivot-Datenmodells in Excel

1. Nachdem das Laden der Daten abgeschlossen ist, können wir Änderungen (wie das Hinzufügen neuer Spalten) am Datenmodell vornehmen, indem wir auf “Daten -> Datenwerkzeuge -> Datenmodell verwalten” klicken.

1. Sie sollten das Power Pivot-Fenster sehen. Lassen Sie uns eine Spalte mit einer Data Analysis Expressions (DAX)-Formel hinzufügen. DAX-Formeln sind sehr ähnlich zu den Excel-Formeln, die Sie wahrscheinlich bereits kennen. Scrollen Sie nach rechts, bis Sie die letzte Spalte Ihrer Daten sehen, und klicken Sie dann auf “Spalte hinzufügen”.
2. Lassen Sie uns eine Formel für diese Spalte schreiben, die eine einfache Berechnung auf jede Zeile anwendet. Im Vergleich zu Excel-Formeln ist die DAX-Syntax etwas anders. Zum Beispiel können wir eine Spalte mit dem Namen “Beschreibung” hinzufügen und die Formel =CONCATENATE([Column9],[Column10]) eingeben. Beachten Sie die Verwendung von eckigen Klammern ([ ]) und den Zugriff über die Spaltenüberschriften.

1. Wenn Sie möchten, können Sie auch zusätzliche Änderungen wie das Umbenennen oder Entfernen von Spalten vornehmen, indem Sie mit der rechten Maustaste auf die Spaltenüberschrift klicken.

4. Daten mit PivotTable analysieren

Nachdem Sie mit den Änderungen am Datenmodell fertig sind, ist es Zeit, die Daten in einem Excel-Arbeitsblatt mit PivotTable zu analysieren.

1. Klicken Sie im Menüband des Power Pivot-Fensters auf “Start -> PivotTable”, um das Fenster “PivotTable erstellen” zu öffnen.

1. Wählen Sie “Neues Arbeitsblatt” und klicken Sie auf “OK”.

1. Das PivotTable-Felder-Panel wird in einem neuen Blatt im Hauptfenster von Excel geöffnet. Im Feld unter der Suchleiste sollten Sie Ihre Datenquellen aufgelistet sehen, in diesem Beispiel “kaggle_adult_csv”. Klicken Sie darauf, um es zu erweitern und alle Spalten anzuzeigen.

1. Lassen Sie uns eine Analyse durchführen, bei der wir die Anzahl jeder “Beschreibung” in unseren Daten zählen. Wir können die Spalten, mit denen wir arbeiten möchten, auswählen, indem wir das Kontrollkästchen neben ihren Namen aktivieren. Wählen Sie “Beschreibung” und beachten Sie, wie der Fokus automatisch in den Bereich “Zeilen” unten verschoben wird. Dies ist Power Pivot, das seine Intelligenz demonstriert – es kann den geeigneten Bereich für die Auswahl genau erraten. Da wir mit einer leeren PivotTable beginnen, ist es sinnvoll, dass unsere erste Auswahl Zeilen ist.

1. Ziehen Sie die ausgewählte “Beschreibung”-Spalte in den leeren Raum in “Werte”. Beachten Sie, dass Power Pivot erneut korrekt annimmt, dass wir eine “Anzahl der Beschreibung” aufgrund des Datentyps Text wünschen. Außerdem wurde unsere Tabelle mit einer Spalte aktualisiert, die die Anzahl jedes Vorkommens jedes “Beschreibung”-Wertes bereitstellt.

1. Wir können eine weitere Dimension zu unserem Zählen hinzufügen. Ziehen Sie beispielsweise die “Alter”-Spalte in den leeren “Spalten”-Bereich, um mehr Details zur Tabelle hinzuzufügen, indem Sie die Anzahl der Beschreibungen nach Alter zählen.

1. Sie können auch die Möglichkeit zur Filterauswahl zur Tabelle hinzufügen, indem Sie die Spalte “WorkClass” in den “Filter”-Bereich ziehen. Beachten Sie die neue interaktive Zelle, die über unserer Tabelle erscheint.

1. Klicken Sie auf den Pfeil neben “Alle” und wählen Sie “Nie gearbeitet”. Dies ändert die Tabelle so, dass nur Personen mit einer WorkClass von “Nie gearbeitet” in die Berechnungen einbezogen werden. Sie können den Filter jederzeit ändern, um die Tabelle aus einer anderen Perspektive zu betrachten.

Haben Sie viele Duplikate in Ihrer Excel-Arbeitsmappe? Erfahren Sie, wie Sie Duplikate finden und entfernen und Ihre Tabellenkalkulationen bereinigen.

5. Ihre Daten mit PowerChart präsentieren

Sie können weitere Arbeitsblätter erstellen, um Ihre Daten auf verschiedene Weise zu analysieren. Lassen Sie uns diesmal ein visuell beeindruckenderes Ergebnis mit PowerChart erstellen.

1. Kehren Sie zum Fenster “Power Pivot für Excel” zurück. Wenn es geschlossen ist, können Sie es über “Daten -> Datenwerkzeuge -> Datenmodell verwalten” aus dem Excel-Fenster öffnen.
2. Klicken Sie auf “PivotTable -> PivotChart -> Neues Arbeitsblatt”.

1. Klicken Sie im rechten Bereich auf die Datenquelle (“kaggle_adult_csv”), um sie zu erweitern und alle Spalten anzuzeigen.
2. Ziehen Sie die Spalten aus der Datenquelle in einen der vier Bereiche unten, um das Diagramm zu erstellen. Verschieben Sie die “OccupationType”-Spalte in den Bereich “Achse (Kategorien)”.

1. Ziehen Sie “OccupationType” erneut und lassen Sie es diesmal im Bereich “Werte” fallen. Sie werden sofort bemerken, dass ein Balkendiagramm automatisch generiert wird. Es zeigt die Anzahl jedes Berufstyps an.

1. Sie können auch die “Ethnicity”-Spalte in den Bereich “Legende (Serien)” ziehen. Sie werden sofort ein detaillierteres Diagramm sehen, das vergleicht, wie viele von jeder Ethnie in jedem Beruf vertreten sind.

1. Als nächstes können wir Filter auf unsere Legende anwenden, um unser Diagramm zu vereinfachen und nur bestimmte Werte zu betrachten. Bewegen Sie die Maus über “Ethnicity”, damit ein Pfeil auf der rechten Seite erscheint. Klicken Sie auf den Pfeil.

1. Klicken Sie auf die Kontrollkästchen neben den Werten, die Sie entfernen möchten, und lassen Sie nur zwei übrig.

1. Lassen Sie uns etwas Neues ausprobieren. Klicken Sie im Abschnitt “Werte” auf “Anzahl von OccupationType” und dann auf “Feld entfernen”. Ziehen Sie die “Alter”-Spalte, um sie zu ersetzen.

1. Sie werden feststellen, dass der Wert automatisch als “Summe des Alters” definiert wird, aber das ist in der realen Welt nicht sehr nützlich. Lassen Sie uns das ändern. Klicken Sie darauf und wählen Sie “Wertfeldeinstellungen”.

1. Da das Alter eine Zahl ist, gibt es viele Berechnungen, die wir auf diesen Abschnitt anwenden können. Versuchen Sie, “Durchschnitt” auszuwählen.

1. Die Visualisierung wird aktualisiert, um die durchschnittlichen Alterswerte für jeden Beruf nach bestimmten Ethnien (die wir gefiltert haben) anzuzeigen.

Möchten Sie Ihre Excel-Grundlagen auffrischen? Wir haben einen umfassenden Leitfaden zu allen Tastenkombinationen für Excel.

Häufig gestellte Fragen

Was sind die Ursprünge von Power Pivot?

Microsoft führte Power Pivot als separates Add-On ein, das von Microsofts SQL Server 2008 R2 bereitgestellt wurde, der 2010 veröffentlicht wurde. Damals wurde es “PowerPivot” ohne Leerzeichen genannt. 2013 wurde es in “Power Pivot” umbenannt. Es wurde erst ab 2016 zu einer integrierten Excel-Funktion.

Was sind Data Analysis Expressions und wie unterscheiden sie sich von Excel-Formeln?

Data Analysis Expressions ist eine Formel-Sprache, die von Power Pivot in Excel und von Power BI verwendet wird. Im Wesentlichen ist es die Art und Weise, wie Sie Formeln für Berechnungen in den oben genannten Anwendungen schreiben: die Aussagen, die Syntax und die Funktionen, die Sie verwenden.

Der Hauptvorteil von DAX gegenüber Excel-Formeln besteht darin, dass es dafür ausgelegt ist, auf aggregierten Daten zu arbeiten. Standard-Excel-Formeln können Berechnungen zeilenweise durchführen (z. B. die Summe mehrerer Werte in einer Zeile berechnen), aber DAX-Formeln können spaltenweise arbeiten (z. B. die Anzahl eines bestimmten Wertes in einer Spalte zählen) oder sogar auf einer gesamten Tabelle.

Wie behebe ich den Fehler “Diese Tabelle wurde von einer Abfrage erstellt. Um diese Tabelle zu ändern, ändern Sie stattdessen die Abfrage”?

Dieser Fehler in Excel Power Pivot tritt auf, wenn Sie versuchen, ein Datenmodell innerhalb von Power Pivot zu ändern, das ursprünglich über Power Query geladen wurde. Das können Sie nicht tun, daher müssen Sie es über die ursprüngliche Methode des Ladens ändern: Power Query.

Klicken Sie auf der rechten Seite von Excel auf das Symbol “Abfragen & Verbindungen” und suchen Sie dann Ihre Abfrage-Datenquelle, um das Fenster zu öffnen, in dem Sie die Tabelle ändern können.

Bildnachweis: Pexels. Alle Screenshots von Brandon Li.