Ich habe vor kurzem eine Frage von einem Leser zum Kombinieren mehrerer Arbeitsblätter in derselben Arbeitsmappe in einem einzigen Arbeitsblatt erhalten.
Ich habe ihn gebeten, Power Query zu verwenden, um verschiedene Arbeitsblätter zu kombinieren, aber dann wurde mir klar, dass dies für jemanden, der Power Query neu ist, schwierig sein kann.
Also habe ich beschlossen, dieses Tutorial zu schreiben und die genauen Schritte zum Kombinieren mehrerer Blätter in einer einzigen Tabelle mit Power Query zu zeigen.
Unten ein Video, in dem ich zeige, wie Daten aus mehreren Blättern/Tabellen mit Power Query kombiniert werden:
Nachfolgend finden Sie schriftliche Anweisungen zum Kombinieren mehrerer Blätter (falls Sie geschriebenen Text dem Video vorziehen).
Hinweis: Power Query kann als Add-In in Excel 2010 und 2013 verwendet werden und ist ab Excel 2016 ein integriertes Feature. Abhängig von Ihrer Version können einige Bilder anders aussehen (die in diesem Tutorial verwendeten Bildaufnahmen stammen aus Excel 2016).
Kombinieren von Daten aus mehreren Arbeitsblättern mit Power Query
Beim Kombinieren von Daten aus verschiedenen Blättern mit Power Query ist es erforderlich, dass die Daten in einer Excel-Tabelle (oder zumindest in benannten Bereichen) vorliegen. Wenn sich die Daten nicht in einer Excel-Tabelle befinden, würde die hier gezeigte Methode nicht funktionieren.
Angenommen, Sie haben vier verschiedene Blätter - Ost, West, Nord und Süd.
Jedes dieser Arbeitsblätter enthält die Daten in einer Excel-Tabelle, und die Struktur der Tabelle ist konsistent (d. h. die Überschriften sind gleich).
Klicken Sie hier, um die Daten herunterzuladen und mitzumachen.
Diese Art von Daten lassen sich mit Power Query (das sehr gut mit Daten in Excel-Tabellen funktioniert) kombinieren.
Damit diese Technik am besten funktioniert, ist es besser, Namen für Ihre Excel-Tabellen zu haben (funktionieren Sie auch ohne, aber es ist einfacher zu verwenden, wenn die Tabellen benannt sind).
Ich habe den Tabellen folgende Namen gegeben: East_Data, West_Data, North_Data und South_Data.
Hier sind die Schritte zum Kombinieren mehrerer Arbeitsblätter mit Excel-Tabellen mithilfe von Power Query:
- Wechseln Sie zur Registerkarte Daten.
- Klicken Sie in der Gruppe Daten abrufen und transformieren auf die Option „Daten abrufen“.
- Gehen Sie zur Option "Aus anderen Quellen".
- Klicken Sie auf die Option "Leere Abfrage". Dadurch wird der Power Query-Editor geöffnet.
- Geben Sie im Abfrageeditor die folgende Formel in die Bearbeitungsleiste ein: = Excel.AktuelleArbeitsmappe(). Beachten Sie, dass bei den Power Query-Formeln die Groß-/Kleinschreibung beachtet werden muss, sodass Sie die genaue Formel wie erwähnt verwenden müssen (sonst erhalten Sie eine Fehlermeldung).
- Drücken Sie die Eingabetaste. Dadurch werden Ihnen alle Tabellennamen in der gesamten Arbeitsmappe angezeigt (es werden Ihnen auch die benannten Bereiche und/oder Verbindungen angezeigt, falls sie in der Arbeitsmappe vorhanden sind).
- [Optionaler Schritt] In diesem Beispiel möchte ich alle Tabellen kombinieren. Wenn Sie nur bestimmte Excel-Tabellen kombinieren möchten, können Sie auf das Dropdown-Symbol in der Namensüberschrift klicken und diejenigen auswählen, die Sie kombinieren möchten. Wenn Sie benannte Bereiche oder Verbindungen haben und nur Tabellen kombinieren möchten, können Sie auch diese benannten Bereiche entfernen.
- Klicken Sie in der Kopfzeile Inhaltszelle auf den Doppelpfeil.
- Wählen Sie die Spalten aus, die Sie kombinieren möchten. Wenn Sie alle Spalten kombinieren möchten, stellen Sie sicher, dass (Alle Spalten auswählen) aktiviert ist.
- Deaktivieren Sie die Option "Originalspaltennamen als Präfix verwenden".
- OK klicken.
Die obigen Schritte würden die Daten aus allen Arbeitsblättern in einer einzigen Tabelle kombinieren.
Wenn Sie genau hinsehen, finden Sie in der letzten Spalte (ganz rechts) den Namen der Excel-Tabellen (East_Data, West_Data, North_Data und South_Data). Dies ist eine Kennung, die uns sagt, welcher Datensatz aus welcher Excel-Tabelle stammt. Dies ist auch der Grund, warum ich gesagt habe, dass es besser ist, beschreibende Namen für die Excel-Tabellen zu haben.
Hier sind einige Änderungen, die Sie an den kombinierten Daten in Power Query selbst vornehmen können:
- Ziehen Sie die Spalte Name und platzieren Sie sie an den Anfang.
- Entfernen Sie "_Data" aus der Namensspalte (so bleiben Ost, West, Nord und Süd in der Namensspalte übrig). Klicken Sie dazu mit der rechten Maustaste auf die Kopfzeile Name und klicken Sie auf Werte ersetzen. Ersetzen Sie im Dialogfeld Werte ersetzen _Data durch ein Leerzeichen.
- Ändern Sie die Spalte Daten so, dass nur Datumsangaben (und nicht die Uhrzeit) angezeigt werden. Klicken Sie dazu auf die Spaltenüberschrift Datum, wechseln Sie zur Registerkarte „Transformieren“ und ändern Sie den Datentyp in Datum.
- Benennen Sie die Abfrage in ConsolidatedData um.
Da Sie nun die kombinierten Daten aus allen Arbeitsblättern in Power Query haben, können Sie sie in Excel laden – als neue Tabelle in einem neuen Arbeitsblatt.
Um dies zu tun. befolgen Sie die folgenden Schritte:
- Klicken Sie auf die Registerkarte „Datei“.
- Klicken Sie auf Schließen und laden in.
- Wählen Sie im Dialogfeld Daten importieren die Optionen Tabelle und Neues Arbeitsblatt aus.
- OK klicken.
Die obigen Schritte würden Daten aus allen Arbeitsblättern kombinieren und Ihnen diese kombinierten Daten in einem neuen Arbeitsblatt anzeigen.
Ein Problem, das Sie beheben müssen, wenn Sie diese Methode verwenden
Falls Sie die obige Methode verwendet haben, um alle Tabellen in der Arbeitsmappe zu kombinieren, haben Sie wahrscheinlich ein Problem.
Siehe die Anzahl der Zeilen der kombinierten Daten - 1304 (was richtig ist).
Wenn ich nun die Abfrage aktualisiere, ändert sich die Anzahl der Zeilen auf 2607. Aktualisieren Sie erneut und sie wird auf 3910 geändert.
Hier ist das Problem.
Jedes Mal, wenn Sie die Abfrage aktualisieren, werden alle Datensätze in den Originaldaten zu den kombinierten Daten hinzugefügt.
Hinweis: Dieses Problem tritt nur auf, wenn Sie Power Query zum Kombinieren verwendet haben ALLE EXCEL-TABELLE im Arbeitsbuch. Falls Sie bestimmte Tabellen zum Kombinieren ausgewählt haben, tritt dieses Problem nicht auf.Lassen Sie uns die Ursache dieses Problems verstehen und beheben.
Wenn Sie eine Abfrage aktualisieren, geht sie zurück und folgt allen Schritten, die wir zum Kombinieren der Daten unternommen haben.
In dem Schritt, in dem wir die Formel verwendet haben =Excel.AktuellesArbeitsbuch(), es gab uns eine Liste aller Tabellen. Dies funktionierte beim ersten Mal gut, da es nur vier Tische gab.
Aber wenn Sie aktualisieren, gibt es fünf Tabellen in der Arbeitsmappe – einschließlich der neuen Tabelle, die Power Query eingefügt hat, wo wir die kombinierten Daten haben.
Jedes Mal, wenn Sie die Abfrage aktualisieren, wird neben den vier Excel-Tabellen, die wir kombinieren möchten, auch die vorhandene Abfragetabelle zu den resultierenden Daten hinzugefügt.
Dies wird als Rekursion bezeichnet.
So lösen Sie dieses Problem.
Nachdem Sie =Excel.CurrentWorkbook() in die Power Query-Formelleiste eingefügt und die Eingabetaste gedrückt haben, erhalten Sie eine Liste mit Excel-Tabellen. Um sicherzustellen, dass Sie nur die Tabellen aus dem Arbeitsblatt kombinieren können, müssen Sie irgendwie nur diese Tabellen filtern, die Sie kombinieren möchten, und alles andere entfernen.
So stellen Sie sicher, dass Sie nur über die erforderlichen Tabellen verfügen:
- Klicken Sie auf das Dropdown-Menü und bewegen Sie den Cursor auf Textfilter.
- Klicken Sie auf die Option Enthält.
- Geben Sie im Dialogfeld Zeilen filtern _Data in das Feld neben der Option „enthält“ ein.
- OK klicken.
Möglicherweise sehen Sie keine Änderung in den Daten, aber dadurch wird verhindert, dass die resultierende Tabelle beim Aktualisieren der Abfrage erneut hinzugefügt wird.
Beachten Sie, dass wir in den obigen Schritten „_Daten“, um zu filtern, wie wir Tabellen auf diese Weise benannt haben. Aber was ist, wenn Ihre Tabellen nicht konsistent benannt sind. Was ist, wenn alle Tabellennamen zufällig sind und nichts gemeinsam haben.
Hier ist der Weg, dies zu lösen - verwenden Sie den Filter "ist nicht gleich" und geben Sie den Namen der Abfrage ein (in unserem Beispiel ConsolidatedData). Dadurch wird sichergestellt, dass alles gleich bleibt und die resultierende Abfragetabelle, die erstellt wird, herausgefiltert wird.
Abgesehen von der Tatsache, dass Power Query diesen gesamten Prozess des Kombinierens von Daten aus verschiedenen Blättern (oder sogar demselben Blatt) sehr einfach macht, ist ein weiterer Vorteil der Verwendung, dass es dynamisch wird. Wenn Sie einer der Tabellen weitere Datensätze hinzufügen und die Power Query aktualisieren, erhalten Sie automatisch die kombinierten Daten.Wichtiger Hinweis: In dem in diesem Tutorial verwendeten Beispiel waren die Header gleich. Falls die Überschriften unterschiedlich sind, kombiniert und erstellt Power Query alle Spalten in der neuen Tabelle. Wenn die Daten für diese Spalte verfügbar sind, werden sie angezeigt, andernfalls wird null angezeigt.
Die folgenden Power Query-Tutorials könnten Ihnen auch gefallen:
- Kombinieren Sie Daten aus mehreren Arbeitsmappen in Excel (mit Power Query).
- So entpivotieren Sie Daten in Excel mit Power Query (auch bekannt als Get & Transform)
- Abrufen einer Liste der Dateinamen aus Ordnern und Unterordnern (mit Power Query)
- Zusammenführen von Tabellen in Excel mit Power Query.
- So vergleichen Sie zwei Excel-Tabellen/-Dateien
