Zusammenstellen von Tabellen aus verschiedenen Excel-Dateien mit Power Query

Formulierung des Problems

Schauen wir uns eine schöne Lösung für eine der Standardsituationen an, mit denen die meisten Excel-Benutzer früher oder später konfrontiert sind: Sie müssen schnell und automatisch Daten aus einer großen Anzahl von Dateien in einer endgültigen Tabelle sammeln. 

Angenommen, wir haben den folgenden Ordner, der mehrere Dateien mit Daten aus Zweigstädten enthält:

Die Anzahl der Dateien spielt keine Rolle und kann sich in Zukunft ändern. Jede Datei hat ein Blatt mit dem Namen Saleswo sich die Datentabelle befindet:

Die Anzahl der Zeilen (Ordnungen) in den Tabellen ist natürlich unterschiedlich, aber die Anzahl der Spalten ist überall einheitlich.

Aufgabe: Sammeln von Daten aus allen Dateien in einem Buch mit anschließender automatischer Aktualisierung beim Hinzufügen oder Löschen von Stadtdateien oder Zeilen in Tabellen. Nach der endgültigen konsolidierten Tabelle können dann beliebige Berichte, Pivot-Tabellen, Filter-Sortierungsdaten usw. erstellt werden. Hauptsache sammeln können.

Wir wählen Waffen aus

Für die Lösung benötigen wir die neueste Version von Excel 2016 (die notwendige Funktionalität ist bereits standardmäßig integriert) oder frühere Versionen von Excel 2010-2013 mit installiertem kostenlosem Add-In Power Query von Microsoft (hier herunterladen). Power Query ist ein superflexibles und superleistungsfähiges Tool zum Laden von Daten in Excel von der Außenwelt, zum anschließenden Strippen und Verarbeiten. Power Query unterstützt fast alle vorhandenen Datenquellen – von Textdateien über SQL bis hin zu Facebook 🙂

Wenn Sie kein Excel 2013 oder 2016 haben, können Sie nicht weiterlesen (nur ein Scherz). In älteren Excel-Versionen ist eine solche Aufgabe nur durch die Programmierung eines Makros in Visual Basic (was für Anfänger sehr schwierig ist) oder durch monotones manuelles Kopieren (das lange dauert und Fehler erzeugt) zu bewältigen.

Schritt 1. Importieren Sie eine Datei als Beispiel

Lassen Sie uns zunächst beispielhaft Daten aus einer Arbeitsmappe importieren, damit Excel „die Idee aufgreift“. Erstellen Sie dazu eine neue leere Arbeitsmappe und…

• Wenn Sie Excel 2016 haben, öffnen Sie die Registerkarte Datum und dann Abfrage erstellen – Aus Datei – Aus Buch (Daten – Neue Abfrage – Aus Datei – Aus Excel)
• Wenn Sie Excel 2010-2013 mit installiertem Power Query-Add-In haben, öffnen Sie die Registerkarte Power Query und darauf auswählen Aus Datei – Aus Buch (Aus Datei — Aus Excel)

Gehen Sie dann in dem sich öffnenden Fenster zu unserem Ordner mit Berichten und wählen Sie eine der Stadtdateien aus (es spielt keine Rolle, welche, da sie alle typisch sind). Nach ein paar Sekunden sollte das Navigator-Fenster erscheinen, in dem Sie das benötigte Blatt (Sales) auf der linken Seite auswählen müssen, und sein Inhalt wird auf der rechten Seite angezeigt:

Wenn Sie auf die Schaltfläche in der unteren rechten Ecke dieses Fensters klicken Herunterladen (Belastung), dann wird die Tabelle sofort in ihrer ursprünglichen Form in das Blatt importiert. Für eine einzelne Datei ist das gut, aber wir müssen viele solcher Dateien laden, also gehen wir etwas anders vor und klicken auf die Schaltfläche Korrektur (Bearbeiten). Danach sollte der Power Query-Abfrageeditor in einem separaten Fenster mit unseren Daten aus dem Buch angezeigt werden:

Dies ist ein sehr leistungsfähiges Werkzeug, mit dem Sie die Tabelle auf die von uns benötigte Ansicht „fertigstellen“ können. Selbst eine oberflächliche Beschreibung aller seiner Funktionen würde etwa hundert Seiten umfassen, aber wenn Sie es ganz kurz fassen, können Sie mit diesem Fenster:

• unnötige Daten, leere Zeilen, Zeilen mit Fehlern herausfiltern
• Daten nach einer oder mehreren Spalten sortieren
• Wiederholungen loswerden
• Sticky-Text nach Spalten teilen (durch Trennzeichen, Anzahl der Zeichen usw.)
• Text ordnen (zusätzliche Leerzeichen entfernen, Groß-/Kleinschreibung korrigieren usw.)
• Konvertieren Sie Datentypen auf jede erdenkliche Weise (verwandeln Sie Zahlen wie Text in normale Zahlen und umgekehrt)
• Tabellen transponieren (rotieren) und zweidimensionale Kreuztabellen in flache erweitern
• Fügen Sie der Tabelle zusätzliche Spalten hinzu und verwenden Sie Formeln und Funktionen darin, indem Sie die in Power Query integrierte M-Sprache verwenden.
• ...

Lassen Sie uns beispielsweise eine Spalte mit dem Textnamen des Monats zu unserer Tabelle hinzufügen, damit es später einfacher ist, Pivot-Tabellenberichte zu erstellen. Klicken Sie dazu mit der rechten Maustaste auf die Spaltenüberschrift Datumund wählen Sie den Befehl aus Spalte duplizieren (Doppelte Spalte), und klicken Sie dann mit der rechten Maustaste auf die Überschrift der doppelten Spalte, die angezeigt wird, und wählen Sie Befehle aus Transformieren – Monat – Monatsname:

Für jede Zeile sollte eine neue Spalte mit den Textnamen des Monats gebildet werden. Durch Doppelklick auf eine Spaltenüberschrift können Sie diese umbenennen Datum kopieren zu einem angenehmeren Monat, z.B.

Wenn das Programm in einigen Spalten den Datentyp nicht ganz richtig erkannt hat, dann können Sie ihm helfen, indem Sie auf das Formatsymbol links neben jeder Spalte klicken:

Mit einem einfachen Filter können Sie Zeilen mit Fehlern oder Leerzeilen sowie unnötige Manager oder Kunden ausschließen:

Darüber hinaus sind alle durchgeführten Transformationen im rechten Bereich fixiert, wo sie jederzeit rückgängig gemacht (Kreuz) oder ihre Parameter geändert werden können (Getriebe):

Leicht und elegant, nicht wahr?

Schritt 2. Lassen Sie uns unsere Anfrage in eine Funktion umwandeln

Um anschließend alle durchgeführten Datentransformationen für jedes importierte Buch zu wiederholen, müssen wir unsere erstellte Anfrage in eine Funktion umwandeln, die dann wiederum auf alle unsere Dateien angewendet wird. Dies zu tun ist eigentlich sehr einfach.

Wechseln Sie im Abfrage-Editor zur Registerkarte Ansicht und klicken Sie auf die Schaltfläche Erweiterter Editor (Ansicht – Erweiterter Editor). Es sollte sich ein Fenster öffnen, in dem alle unsere vorherigen Aktionen in Form von Code in der M-Sprache geschrieben werden. Bitte beachten Sie, dass der Pfad zu der Datei, die wir für das Beispiel importiert haben, im Code fest codiert ist:

Nehmen wir nun ein paar Anpassungen vor:

Ihre Bedeutung ist einfach: die erste Zeile (Dateipfad)=> verwandelt unsere Prozedur in eine Funktion mit einem Argument Dateipfad, und unten ändern wir den festen Pfad auf den Wert dieser Variablen. 

Alle. Klicke auf Endziel und sollte das sehen:

Haben Sie keine Angst, dass die Daten verschwunden sind – eigentlich ist alles in Ordnung, alles sollte so aussehen 🙂 Wir haben erfolgreich unsere benutzerdefinierte Funktion erstellt, bei der der gesamte Algorithmus zum Importieren und Verarbeiten von Daten gespeichert wird, ohne an eine bestimmte Datei gebunden zu sein . Es bleibt, ihm einen verständlicheren Namen zu geben (z Daten bekommen) in der Leiste rechts im Feld Vorname und du kannst ernten Startseite — Schließen und herunterladen (Home — Schließen und Laden). Bitte beachten Sie, dass der Pfad zu der Datei, die wir für das Beispiel importiert haben, im Code fest codiert ist. Sie kehren zum Hauptfenster von Microsoft Excel zurück, aber rechts sollte ein Panel mit der erstellten Verbindung zu unserer Funktion erscheinen:

Schritt 3. Sammeln aller Dateien

Das Schwierigste liegt hinter uns, das Angenehme und Leichte bleibt. Gehen Sie zur Registerkarte Daten – Abfrage erstellen – Aus Datei – Aus Ordner (Daten – Neue Abfrage – Aus Datei – Aus Ordner) oder, wenn Sie Excel 2010-2013 haben, ähnlich wie auf der Registerkarte Power Query. Geben Sie im angezeigten Fenster den Ordner an, in dem sich alle unsere Quellstadtdateien befinden, und klicken Sie darauf OK. Der nächste Schritt sollte ein Fenster öffnen, in dem alle in diesem Ordner (und seinen Unterordnern) gefundenen Excel-Dateien und Details für jede von ihnen aufgelistet werden:

Klicken Sie auf Kostenlos erhalten und dann auf Installieren. Change (Bearbeiten) und wieder gelangen wir in das vertraute Fenster des Abfrageeditors.

Jetzt müssen wir unserer Tabelle mit unserer erstellten Funktion eine weitere Spalte hinzufügen, die die Daten aus jeder Datei „zieht“. Gehen Sie dazu auf die Registerkarte Spalte hinzufügen – Benutzerdefinierte Spalte (Spalte hinzufügen — Benutzerdefinierte Spalte hinzufügen) und geben Sie im angezeigten Fenster unsere Funktion ein Daten bekommen, wobei als Argument der vollständige Pfad zu jeder Datei angegeben wird:

Nach dem Klicken auf OK Die erstellte Spalte sollte unserer Tabelle rechts hinzugefügt werden.

Löschen wir nun alle unnötigen Spalten (wie in Excel mit der rechten Maustaste – Entfernen), wobei nur die hinzugefügte Spalte und die Spalte mit dem Dateinamen übrig bleiben, da es nützlich ist, diesen Namen (genauer gesagt die Stadt) in den Gesamtdaten für jede Zeile zu haben.

Und jetzt der „Wow-Moment“ – klicken Sie auf das Symbol mit den eigenen Pfeilen in der oberen rechten Ecke der hinzugefügten Spalte mit unserer Funktion:

… deaktivieren Verwenden Sie den ursprünglichen Spaltennamen als Präfix (Originalspaltenname als Präfix verwenden)und klicken auf OK. Und unsere Funktion lädt und verarbeitet die Daten aus jeder Datei, folgt dem aufgezeichneten Algorithmus und sammelt alles in einer gemeinsamen Tabelle:

Für vollständige Schönheit können Sie auch die .xlsx-Erweiterungen aus der ersten Spalte mit Dateinamen entfernen – durch Standardersetzung durch „nichts“ (Rechtsklick auf die Spaltenüberschrift – Ersatz) und benennen Sie diese Spalte um in Stadt. Korrigieren Sie auch das Datenformat in der Spalte mit dem Datum.

Alle! Klicke auf Home – Schließen und laden (Startseite — Schließen & Laden). Alle von der Abfrage für alle Städte gesammelten Daten werden im Format „intelligente Tabelle“ in die aktuelle Excel-Tabelle hochgeladen:

Die erstellte Verbindung und unsere Montagefunktion müssen in keiner Weise separat gespeichert werden – sie werden wie gewohnt zusammen mit der aktuellen Datei gespeichert.

Zukünftig reicht es bei Änderungen im Ordner (Hinzufügen oder Entfernen von Städten) oder in Dateien (Ändern der Anzahl der Zeilen) aus, mit der rechten Maustaste direkt auf die Tabelle oder auf die Abfrage im rechten Bereich zu klicken und die auszuwählen Befehl Aktualisieren & Speichern (Aktualisierung) – Power Query baut alle Daten in wenigen Sekunden neu auf.

PS

Änderung. Nach den Updates vom Januar 2017 hat Power Query gelernt, Excel-Arbeitsmappen selbst zu sammeln, dh es muss keine separate Funktion mehr erstellt werden – es geschieht automatisch. Somit entfällt der zweite Schritt aus diesem Artikel und der gesamte Prozess wird merklich einfacher:

1. Auswählen Anfrage erstellen – Aus Datei – Aus Ordner – Ordner auswählen – OK
2. Nachdem die Dateiliste angezeigt wird, drücken Sie Change
3. Erweitern Sie im Fenster „Abfrage-Editor“ die Spalte „Binär“ mit einem Doppelpfeil, und wählen Sie den Blattnamen aus, der aus jeder Datei entnommen werden soll

Und das ist alles! Lied!

• Umgestaltung der Kreuztabelle in eine flache Kreuztabelle, die zum Erstellen von Pivot-Tabellen geeignet ist
• Erstellen eines animierten Blasendiagramms in Power View
• Makro zum Zusammenfügen von Blättern aus verschiedenen Excel-Dateien zu einem