Kombinieren von Daten aus mehreren Arbeitsmappen in Excel (mit Power Query)

Power Query kann eine große Hilfe sein, wenn Sie mehrere Arbeitsmappen in einer einzigen Arbeitsmappe kombinieren möchten.

Angenommen, Sie verfügen über die Verkaufsdaten für verschiedene Regionen (Ost, West, Nord und Süd). Sie können diese Daten aus verschiedenen Arbeitsmappen mit Power Query in einem einzigen Arbeitsblatt kombinieren.

Wenn Sie diese Arbeitsmappen an verschiedenen Orten/Ordnern haben, ist es eine gute Idee, alle diese in einen einzigen Ordner zu verschieben (oder eine Kopie zu erstellen und diese Arbeitsmappenkopie in denselben Ordner zu legen).

Ich habe also zunächst vier Arbeitsmappen in einem Ordner (wie unten gezeigt).

In diesem Tutorial behandle ich nun drei Szenarien, in denen Sie die Daten aus verschiedenen Arbeitsmappen mit Power Query kombinieren können:

• Jede Arbeitsmappe enthält die Daten in einer Excel-Tabelle, und alle Tabellennamen sind gleich.
• Jede Arbeitsmappe enthält die Daten mit demselben Arbeitsblattnamen. Dies kann der Fall sein, wenn in allen Arbeitsmappen ein Blatt mit dem Namen "Zusammenfassung" oder "Daten" vorhanden ist und Sie alle diese kombinieren möchten.
• Jede Arbeitsmappe hat viele Blätter und Tabellen, und Sie möchten bestimmte Tabellen/Blätter kombinieren. Diese Methode kann auch hilfreich sein, wenn Sie Tabellen/Blätter kombinieren möchten, die keinen einheitlichen Namen haben.

Sehen wir uns an, wie Sie Daten aus diesen Arbeitsmappen in jedem Fall kombinieren.

Jede Arbeitsmappe enthält die Daten in einer Excel-Tabelle mit der gleichen Struktur

Die folgende Technik würde funktionieren, wenn Ihre Excel-Tabellen auf die gleiche Weise strukturiert sind (gleiche Spaltennamen).

Die Anzahl der Zeilen in jeder Tabelle kann variieren.

Machen Sie sich keine Sorgen, wenn einige der Excel-Tabellen zusätzliche Spalten haben. Sie können eine der Tabellen als Vorlage auswählen (oder als „Schlüssel“, wie Power Query es nennt) und Power Query würde es verwenden, um alle anderen Excel-Tabellen damit zu kombinieren.

Falls in anderen Tabellen zusätzliche Spalten vorhanden sind, werden diese ignoriert und nur die in der Vorlage/dem Schlüssel angegebenen werden kombiniert. Wenn die von Ihnen ausgewählte Vorlagen-/Schlüsseltabelle beispielsweise 5 Spalten hat und eine der Tabellen in einer anderen Arbeitsmappe 2 zusätzliche Spalten hat, werden diese zusätzlichen Spalten ignoriert.

Jetzt habe ich vier Arbeitsmappen in einem Ordner, die ich kombinieren möchte.

Unten ist eine Momentaufnahme der Tabelle, die ich in einer der Arbeitsmappen habe.

Hier sind die Schritte zum Kombinieren der Daten aus diesen Arbeitsmappen in einer einzelnen Arbeitsmappe (als einzelne Tabelle).

1. Wechseln Sie zur Registerkarte Daten.
2. Klicken Sie in der Gruppe Abrufen und Transformieren auf das Dropdown-Menü Neue Abfrage.
3. Bewegen Sie den Cursor auf „Aus Datei“ und klicken Sie auf „Aus Ordner“.
4. Geben Sie im Dialogfeld Ordner den Dateipfad des Ordners ein, der die Dateien enthält, oder klicken Sie auf Durchsuchen und suchen Sie den Ordner.
5. OK klicken.
6. Klicken Sie im sich öffnenden Dialogfeld auf die Schaltfläche Kombinieren.
7. Klicken Sie auf „Kombinieren und laden“.
8. Wählen Sie im sich öffnenden Dialogfeld „Dateien kombinieren“ die Tabelle im linken Bereich aus. Beachten Sie, dass Power Query Ihnen die Tabelle aus der ersten Datei anzeigt. Diese Datei würde als Vorlage (oder Schlüssel) fungieren, um andere Dateien zu kombinieren. Power Query würde nun in anderen Arbeitsmappen nach „Tabelle 1“ suchen und sie mit dieser kombinieren.
9. OK klicken.

Dadurch wird das Endergebnis (kombinierte Daten) in Ihr aktives Arbeitsblatt geladen.

Beachten Sie, dass Power Query zusammen mit den Daten automatisch den Arbeitsmappennamen als erste Spalte der kombinierten Daten hinzufügt. Auf diese Weise können Sie verfolgen, welche Daten aus welcher Arbeitsmappe stammen.

Falls Sie die Daten zuerst bearbeiten möchten, bevor Sie sie in Excel laden, wählen Sie in Schritt 6 „Kombinieren und bearbeiten“. Dadurch wird das Endergebnis im Power Query-Editor geöffnet, in dem Sie die Daten bearbeiten können.

Ein paar Dinge zu wissen:

• Wenn Sie eine Excel-Tabelle als Vorlage auswählen (in Schritt 7), verwendet Power Query die Spaltennamen in dieser Tabelle, um die Daten aus anderen Tabellen zu kombinieren. Wenn andere Tabellen zusätzliche Spalten haben, werden diese ignoriert. Falls diese anderen Tabellen keine Spalte haben, die sich in Ihrer Vorlagentabelle befindet, würde Power Query dafür einfach "null" setzen.
• Die Spalten müssen nicht in derselben Reihenfolge sein, da Power Query Spaltenüberschriften verwendet, um Spalten zuzuordnen.
• Da Sie Table1 als Schlüssel ausgewählt haben, sucht Power Query in allen Arbeitsmappen nach Table1 und kombiniert diese. Falls keine Excel-Tabelle mit demselben Namen (Tabelle1 in diesem Beispiel) gefunden wird, gibt Power Query eine Fehlermeldung aus.

Hinzufügen neuer Dateien zum Ordner

Nehmen wir uns nun eine Minute Zeit und verstehen, was wir mit den obigen Schritten gemacht haben (was nur ein paar Sekunden gedauert hat).

Wir haben die Daten aus vier verschiedenen Arbeitsmappen in wenigen Sekunden in einer einzigen Tabelle zusammengefasst, ohne auch nur eine der Arbeitsmappen zu öffnen.

Aber das ist nicht alles.

Die wahre Stärke von Power Query besteht darin, dass Sie jetzt, wenn Sie dem Ordner weitere Dateien hinzufügen, keinen dieser Schritte wiederholen müssen.

Sie müssen lediglich die neue Arbeitsmappe in den Ordner verschieben, die Abfrage aktualisieren, und die Daten aus allen Arbeitsmappen in diesem Ordner werden automatisch kombiniert.

Wenn ich im obigen Beispiel beispielsweise eine neue Arbeitsmappe hinzufüge - ‘Mitte-West.xlsx’ in den Ordner und aktualisieren Sie die Abfrage, wird mir sofort das neue kombinierte Dataset angezeigt.

So aktualisieren Sie eine Abfrage:

• Klicken Sie mit der rechten Maustaste auf die Excel-Tabelle, die Sie in das Arbeitsblatt geladen haben, und klicken Sie auf Aktualisieren.
• Klicken Sie mit der rechten Maustaste auf die Abfrage im Bereich "Arbeitsmappenabfrage" und klicken Sie auf Aktualisieren
• Gehen Sie zur Registerkarte Daten und klicken Sie auf Aktualisieren.

Jede Arbeitsmappe enthält die Daten mit demselben Arbeitsblattnamen

Falls Sie die Daten nicht in einer Excel-Tabelle haben, aber alle Blattnamen (aus denen Sie die Daten kombinieren möchten) gleich sind, können Sie die in diesem Abschnitt gezeigte Methode verwenden.

Es gibt ein paar Dinge, bei denen Sie vorsichtig sein müssen, wenn es sich nur um tabellarische Daten und nicht um eine Excel-Tabelle handelt.

• Die Arbeitsblattnamen sollten gleich sein. Dadurch kann Power Query Ihre Arbeitsmappen durchgehen und die Daten aus den Arbeitsblättern kombinieren, die in jeder Arbeitsmappe denselben Namen haben.
• Bei Power Query muss die Groß-/Kleinschreibung beachtet werden. Dies bedeutet, dass ein Arbeitsblatt mit dem Namen „Daten“ und „Daten“ als unterschiedlich betrachtet wird. Ebenso werden eine Spalte mit der Überschrift „Store“ und eine mit „Store“ als unterschiedlich betrachtet.
• Es ist zwar wichtig, die gleichen Spaltenüberschriften zu haben, aber nicht die gleiche Reihenfolge. Wenn Spalte 2 in „East.xlsx“ Spalte 4 in „West.xlsx“ ist, wird sie von Power Query korrekt zugeordnet, indem die Header zugeordnet werden.

Sehen wir uns nun an, wie Sie schnell Daten aus verschiedenen Arbeitsmappen kombinieren können, bei denen der Arbeitsblattname gleich ist.

In diesem Beispiel habe ich einen Ordner mit vier Dateien.

In jeder Arbeitsmappe habe ich ein Arbeitsblatt mit dem Namen „Daten“, das die Daten im folgenden Format enthält (beachten Sie, dass dies keine Excel-Tabelle ist).

Hier sind die Schritte zum Kombinieren von Daten aus mehreren Arbeitsmappen in einem einzigen Arbeitsblatt:

1. Wechseln Sie zur Registerkarte Daten.
2. Klicken Sie in der Gruppe Abrufen und Transformieren auf das Dropdown-Menü Neue Abfrage.
3. Bewegen Sie den Cursor auf „Aus Datei“ und klicken Sie auf „Aus Ordner“.
4. Geben Sie im Dialogfeld Ordner den Dateipfad des Ordners ein, der die Dateien enthält, oder klicken Sie auf Durchsuchen und suchen Sie den Ordner.
5. OK klicken.
6. Klicken Sie im sich öffnenden Dialogfeld auf die Schaltfläche Kombinieren.
7. Klicken Sie auf „Kombinieren und laden“.
8. Wählen Sie im sich öffnenden Dialogfeld „Dateien kombinieren“ im linken Bereich „Daten“. Beachten Sie, dass Power Query Ihnen den Arbeitsblattnamen aus der ersten Datei anzeigt. Diese Datei würde als Schlüssel/Vorlage fungieren, um andere Dateien zu kombinieren. Power Query geht jede Arbeitsmappe durch, sucht das Blatt mit dem Namen „Daten“ und kombiniert all diese.
9. OK klicken. Jetzt geht Power Query jede Arbeitsmappe durch, sucht darin nach dem Arbeitsblatt mit dem Namen "Daten" und kombiniert dann alle diese Datensätze.

Dadurch wird das Endergebnis (kombinierte Daten) in Ihr aktives Arbeitsblatt geladen.

Falls Sie die Daten zuerst bearbeiten möchten, bevor Sie sie in Excel laden, wählen Sie in Schritt 6 „Kombinieren und bearbeiten“. Dadurch wird das Endergebnis im Power Query-Editor geöffnet, in dem Sie die Daten bearbeiten können.

Jede Arbeitsmappe enthält die Daten mit unterschiedlichen Tabellennamen oder Blattnamen

Manchmal erhalten Sie möglicherweise keine strukturierten und konsistenten Daten (z. B. Tabellen mit demselben Namen oder Arbeitsblätter mit demselben Namen).

Angenommen, Sie erhalten die Daten von jemandem, der diese Datasets erstellt, die Arbeitsblätter jedoch als Ostdaten, Westdaten, Norddaten und Süddaten benannt hat.

Oder die Person hat möglicherweise Excel-Tabellen erstellt, jedoch mit anderen Namen.

In solchen Fällen können Sie Power Query weiterhin verwenden, müssen dies jedoch mit einigen zusätzlichen Schritten tun.

1. Wechseln Sie zur Registerkarte Daten.
2. Klicken Sie in der Gruppe Abrufen und Transformieren auf das Dropdown-Menü Neue Abfrage.
3. Bewegen Sie den Cursor auf „Aus Datei“ und klicken Sie auf „Aus Ordner“.
4. Geben Sie im Dialogfeld Ordner den Dateipfad des Ordners ein, der die Dateien enthält, oder klicken Sie auf Durchsuchen und suchen Sie den Ordner.
5. OK klicken.
6. Klicken Sie im sich öffnenden Dialogfeld auf die Schaltfläche Bearbeiten. Dadurch wird der Power Query-Editor geöffnet, in dem Sie die Details aller Dateien im Ordner sehen.
7. Halten Sie die Strg-Taste gedrückt und wählen Sie die Spalten „Inhalt“ und „Name“, klicken Sie mit der rechten Maustaste und wählen Sie „Andere Spalten entfernen“. Dadurch werden alle anderen Spalten mit Ausnahme der ausgewählten Spalten entfernt.
8. Klicken Sie im Menüband des Abfrageeditors auf „Spalte hinzufügen“ und dann auf „Benutzerdefinierte Spalte“.
9. Benennen Sie im Dialogfeld Benutzerdefinierte Spalte hinzufügen die neue Spalte als "Datenimport" und verwenden Sie die folgende Formel =Excel.Arbeitsmappe([INHALT]). Beachten Sie, dass bei dieser Formel die Groß-/Kleinschreibung beachtet wird und Sie sie genau so eingeben müssen, wie ich es hier gezeigt habe.
10. Jetzt sehen Sie eine neue Spalte, in der Table geschrieben ist. Lassen Sie mich nun erklären, was hier passiert ist. Sie haben Power Query die Namen der Arbeitsmappen bereitgestellt, und Power Query hat die Objekte wie Arbeitsblätter, Tabellen und benannte Bereiche aus jeder Arbeitsmappe (die sich derzeit in der Tabellenzelle befindet) abgerufen. Sie können auf den weißen Bereich neben dem Text Tabelle klicken und Sie sehen die Informationen unten. Da in diesem Fall nur eine Tabelle und ein Arbeitsblatt in jeder Arbeitsmappe vorhanden sind, können Sie nur zwei Zeilen sehen.
11. Klicken Sie oben in der Spalte „Datenimport“ auf das Doppelpfeil-Symbol.
12. Deaktivieren Sie im sich öffnenden Spaltendatenfeld das Kontrollkästchen "Originalspalte als Präfix verwenden" und klicken Sie dann auf OK.
13. Jetzt sehen Sie eine erweiterte Tabelle, in der Sie für jedes Objekt in der Tabelle eine Zeile sehen. In diesem Fall werden für jede Arbeitsmappe das Blattobjekt und das Tabellenobjekt separat aufgelistet.
14. Filtern Sie in der Spalte Art die Liste, um nur die Tabelle anzuzeigen.
15. Halten Sie die Strg-Taste gedrückt und wählen Sie die Spalte Name und Daten aus. Klicken Sie nun mit der rechten Maustaste und entfernen Sie alle anderen Spalten.
16. Klicken Sie in der Spalte Daten auf das Doppelpfeil-Symbol oben rechts im Datenkopf.
17. Klicken Sie im sich öffnenden Spaltendatenfeld auf OK. Dadurch werden die Daten in allen Tabellen kombiniert und in Power Query angezeigt.
18. Jetzt können Sie jede benötigte Transformation vornehmen, dann zur Registerkarte Start gehen und auf Schließen & Laden klicken.

Lassen Sie mich nun versuchen, schnell zu erklären, was wir hier gemacht haben. Da es keine Konsistenz in den Blattnamen oder Tabellennamen gab, haben wir die =Excel.Workbook-Formel verwendet, um alle Objekte der Arbeitsmappen in der Power Query abzurufen. Diese Objekte können Blätter, Tabellen und benannte Bereiche umfassen. Sobald wir alle Objekte aus allen Dateien hatten, haben wir diese gefiltert, um nur Excel-Tabellen zu berücksichtigen. Dann haben wir die Daten in den Tabellen erweitert und all dies kombiniert.

In diesem Beispiel haben wir die Daten gefiltert, um nur Excel-Tabellen zu verwenden (in Schritt 13). Wenn Sie Blätter und keine Tabellen kombinieren möchten, können Sie Blätter filtern.

Hinweis - Diese Technik liefert Ihnen die kombinierten Daten auch dann, wenn die Spaltennamen nicht übereinstimmen. Wenn Sie beispielsweise in East.xlsx eine falsch geschriebene Spalte haben, erhalten Sie am Ende 5 Spalten. Power Query füllt Daten in Spalten aus, wenn sie gefunden wird, und wenn sie keine Spalte finden kann, wird der Wert als "null" gemeldet.

Wenn Sie in einem der Tabellenarbeitsblätter zusätzliche Spalten haben, werden diese ebenfalls in das Endergebnis aufgenommen.

Wenn Sie jetzt mehr Arbeitsmappen erhalten, aus denen Sie Daten kombinieren müssen, kopieren Sie sie einfach in den Ordner und aktualisieren Sie die Power Query