Wenn Sie häufig mit Excel arbeiten, sind Sie wahrscheinlich schon mit diesen Problemen konfrontiert worden: Sie müssen mehrere Tabellenblätter Ihrer Verkaufsabteilung in einem Masterblatt zusammenführen, um Ihre Analyse zu vereinfachen. Vielleicht möchten Sie auch mehrere Tabellenblätter aus dem Vertrieb in einem einzigen Workbook zusammenfassen. Die Funktion zum Kopieren und Einfügen ist für kleine Datensätze mit identischen Strukturen effektiv. Aber bei großen Datensätzen sind Fehler unvermeidlich. Welche Tools oder Befehle können Sie also verwenden, um Excel-Dateien einfach zusammenzuführen? Lesen Sie diesen Leitfaden, um es herauszufinden.
Excel-Blätter zusammenführen
Wie würden Sie die drei folgenden Excel-Blätter (Sheet 1, Sheet 2, und Sheet 3) zusammenführen?
Excel hat eigentlich keine eingebaute Funktion zum Zusammenführen von Blättern. Sie können die Blätter entweder manuell kopieren oder sie mit Coupler.io zusammenführen. Es handelt sich um eine Lösung zur Automatisierung von Berichten, die Daten aus über 60 Datenquellen automatisch nach einem Zeitplan importieren und kombinieren kann.
Schauen wir uns an, wie man mit Coupler.io Daten aus den drei oben genannten Blättern zusammenführt.
Schritt 1: Sammeln der Daten
Im folgenden Formular haben wir als Quelle und Ziel Microsoft Excel ausgewählt. Klicken Sie auf Proceed, um zu beginnen
Verbinden Sie in den Quelleneinstellungen Ihr Microsoft-Konto, wählen Sie die Excel-Datei und die 3 Blätter aus, die Sie zusammenführen möchten.
Schritt 2: Daten umwandeln
In diesem Schritt sehen Sie eine Vorschau der zusammengeführten Daten aus den 3 Blättern. Außerdem können Sie Ihre Daten mit den folgenden Optionen organisieren und transformieren.
- Unnötige Spalten umbenennen, neu anordnen und ausblenden
- Filtern und sortieren Sie Ihre Daten
- Erstellen neuer Spalten mit benutzerdefinierten Formeln und Berechnungen
Sobald die Daten fertig sind, gehen Sie zu den Zieleinstellungen. Wählen Sie die Datei und die Excel-Tabelle aus, in die Sie die kombinierten Daten exportieren möchten.
Sie können diese konsolidierten Daten auch an andere Ziele wie BigQuery, Looker Studio, Google Sheets und andere für weitere Analysen und Visualisierungen senden. Sehen Sie sich weitere Integrationen mit Excel an.
Schritt 3: Transfer planen
Schalten Sie die Schaltfläche Automatic data refresh ein. Geben Sie die Details der Datenübertragung an – Intervall, Wochentage, Zeiteinstellungen und Zeitzone.
Die kombinierten Daten der drei Blätter sehen wie folgt aus.
Ebenso können Sie mehrere Google Sheets zu einem einzigen zusammenfassen.
Excel-Blätter ohne Duplikate zusammenführen
Excel bietet keine eingebaute Funktion, um Blätter zusammenzuführen und Duplikate mit einem Klick zu entfernen. Sie können jedoch entweder Blätter kopieren und einfügen oder sie mit Coupler.io konsolidieren und dann mittels „Remove Duplicate“ doppelte Einträge entfernen.
Lassen Sie uns zum Beispiel doppelte Einträge (d. h. die roten und blauen Zellen) aus dem untenstehenden Blatt Data I entfernen.
- Um die Duplikate in den zusammengeführten Daten zu entfernen, gehen Sie auf die Registerkarte Data tab => Data Tools => Remove Duplicates.
- Deaktivieren Sie im Feld Remove Duplicates die Option My data has headers, um doppelte Überschriften zu entfernen. Aktivieren oder deaktivieren Sie dann im Abschnitt Columns die Spalten, die doppelte Werte enthalten (in unserem Fall haben wir alle Spalten aktiviert). Klicken Sie auf OK.
- Klicken Sie auf OK in dem Feld, das Sie über die Anzahl der entfernten Duplikate informiert. Wie Sie in der Abbildung unten sehen können, wurden 3 doppelte Werte entfernt, und wir haben jetzt nur noch eindeutige Werte.
Zusammenführen von zwei Excel-Blättern anhand einer Spalte
Dazu gibt es zwei Methoden.
- Verwendung von Coupler.io
- Verwendung von Power Query
Wir werden sehen, wie diese Methoden funktionieren und welche für Sie am besten geeignet ist.
Methode 1: Coupler.io
Angenommen, Sie haben zwei Excel-Tabellen in derselben Datei mit den Blattnamen Finance Table und Sales Table. Sie möchten die Daten aus diesen beiden Tabellen auf der Grundlage der Spalte „Product ID“ zusammenführen.
Die Quelle und das Ziel sind im Formular vorausgewählt. Klicken Sie auf Proceed, um fortzufahren.
Verbinden Sie in den Quelleneinstellungen Ihr Microsoft-Konto, wählen Sie die Excel-Datei und das Blatt, das Sie kombinieren möchten. In unserem Fall haben wir das Blatt „Finance Table“ gewählt.
Klicken Sie auf Connect one more source. Wählen Sie die Quellanwendung als Excel aus, verbinden Sie Ihr Konto und wählen Sie das zweite Blatt „Sales Table“ aus Ihrer Excel-Datei.
Nachdem Sie die Excel-Blätter verbunden haben, gehen Sie zum nächsten Schritt über. Hier sehen Sie zwei Optionen.
- Append – Kombinieren von Daten aus mehreren Quellen mit ähnlicher Struktur, wobei die Spalten nach Namen abgeglichen werden.
- Join – Zusammenführen von Daten aus mehreren Quellen mit unterschiedlichen Strukturen unter Verwendung einer eindeutigen Bezeichnungsspalte.
Wählen Sie die Verknüpfung und geben Sie als Schlüsselspalte die Product ID in beiden Blättern an.
Sobald Sie fortfahren, sehen Sie eine Vorschau der verknüpften Daten aus den beiden Excel-Blättern auf der Grundlage der Schlüsselspalte „Product ID“.
Geben Sie in den Zieleinstellungen an, wohin die Daten geladen werden sollen. Sobald Sie den Importer ausgeführt haben, können Sie die zusammengeführten Daten aus zwei Tabellenblättern (Finance table und Sales table) anhand der Spalte „Product ID“ sehen.
Methode 2: Power Query
Sie können Power Query verwenden, um Excel-Blätter auf der Grundlage einer gemeinsamen Spalte zusammenzuführen. Power Query ist als kostenloses Add-in für Excel 2010 und 2013 verfügbar, während es ab Excel 2016 eine integrierte Funktion ist.
Lassen Sie uns zwei Tabellenblätter (Finance Table und Sales Table) mit einer übereinstimmenden Spalte (Product ID) konsolidieren, wie in den folgenden Screenshots zu sehen:
Schritt 1: Power Query-Verbindungen erstellen
- Bevor wir unsere Tabellenblätter konsolidieren, müssen wir separate Power Query-Verbindungen für unsere Tabellenblätter Finance Table und Sales Table erstellen. Beginnen wir mit dem Blatt Sales Table. Wählen Sie die Zelle aus, die Sie zusammenführen möchten. Gehen Sie auf der Registerkarte Data zum Abschnitt Get & Transform und klicken Sie auf From table.
- Aktivieren oder deaktivieren Sie im anschließenden Feld Create Table das Kontrollkästchen My table has headers. In diesem Lernprogramm lassen wir das Häkchen gesetzt, da unsere Tabelle Kopfzeilen hat. Klicken Sie dann auf OK.
- Klicken Sie im resultierenden Power Query Editor auf Close and Load. Wählen Sie in der Dropdown-Liste Close and Load To… aus.
- Wählen Sie im Dialogfeld Load To die Option Only Create Connection. Klicken Sie auf Load.
- Auf der rechten Seite Ihres Excel-Workbooks öffnet sich ein Feld Workbook Queries mit dem Namen Ihrer Tabelle/ihres Bereichs. Wiederholen Sie den Vorgang auch für andere Tabellen (in diesem Fall die Finance table).
- Nach dem Erstellen einer separaten Verbindung für das Blatt Finance table sollte das Feld Workbook Queries beide Verbindungen enthalten, wie unten dargestellt:
Schritt 2: Zusammenführen der Blätter
Markieren Sie ein Blatt, gehen Sie auf Data => New Query => Combined Queries => Merge, wie im folgenden Bild gezeigt:
- Fügen Sie im Feld Merge die Finance Table und Sales Table zur ersten bzw. zweiten Dropdown-Liste hinzu. Wählen Sie in beiden Tabellen die übereinstimmende Spalte aus (in diesem Fall die Product ID). Sobald Sie die passende Spalte ausgewählt haben, wird sie grün.
- Lassen Sie im Ausklappmenü Join kind die Standardoption unverändert. Klicken Sie auf Ok.
Nach Abschluss der Schritte der Merge fügt der Power Query Editor eine zusätzliche Spalte hinzu (Sales Table). Die zusätzliche Spalte enthält jedoch nur Table. In den nächsten Schritten zeigen wir Ihnen, wie Sie das Problem beheben können.
Schritt 3: Füllen Sie die „Sales Table“ aus
- Klicken Sie auf den zweiseitigen Pfeil neben der Sales Table. Wählen Sie im folgenden Dialogfeld die Spalte(n) aus, die Sie aus Ihrer zweiten Tabelle (in diesem Fall die Sales Table. ) kopieren möchten.
- Lassen Sie in dem unten gezeigten Dialogfeld das Kontrollkästchen Expand markiert. Wählen Sie die Spalte(n), die Sie aus der zweiten Tabelle hinzufügen möchten. In unserem Fall haben wir Items und Amount ($) ausgewählt. Deaktivieren Sie das Kontrollkästchen Use original column name as prefix, wenn Sie nicht möchten, dass der Spalte der Tabellenname vorangestellt wird, von dem sie abgeleitet ist. Klicken Sie auf OK.
Nach der Zusammenführung enthält Ihre neue Tabelle Datensätze aus beiden Tabellen.
Schritt 4: Importieren Sie die zusammengeführten Daten in Excel
- Wählen Sie im Feld Power Query Editor die Option Close and Load To… aus dem Ausklappmenü „Close and Load“. Klicken Sie im Feld Load to auf Table und wählen Sie, ob Sie Ihre Tabelle als New worksheet oder Existing worksheet speichern möchten (wir haben die Tabelle als neues Workbook gespeichert). Klicken Sie dann auf Load.
In Excel wird unsere zusammengeführte Tabelle auf diese Weise in einem einzigen Blatt angezeigt:
Excel-Blätter aus verschiedenen Dateien zusammenführen
Das Zusammenführen von Tabellenblättern aus verschiedenen Dateien kann recht knifflig sein, vor allem, wenn Sie mit großen Datensätzen zu tun haben. An dieser Stelle kommen Excel-Makros ins Spiel. Makros sind Codes, mit denen sich Aufgaben in Excel automatisieren lassen. Visual Basic for Applications (VBA) ist eine Möglichkeit, Excel-Makros zu erstellen. Wir werden zum Beispiel den VBA-Code verwenden, um Blätter aus verschiedenen Excel-Dateien zusammenzuführen.
Um den Zugriff zu erleichtern, empfehlen wir Ihnen, Ihre Excel-Dateien im selben Ordner zusammenzufassen. Hier sehen Sie Screenshots der Excel-Dateien, die wir zusammenführen werden.
Schritt 1: Makro in das Workbook einfügen
- Gehen Sie in Ihrem neuen Workbook auf die Registerkarte Developer tab => Code => Visual Basic, um den Visual Basic Editor (VBE) zu öffnen. Alternativ können Sie auch Alt + F11 unter Windows (oder Fn + Opt + F11 bei Mac) verwenden, um den VBE zu öffnen.
- Sobald die VBE-Box geöffnet ist, klicken Sie mit der rechten Maustaste auf das Tabellenblatt, das Sie zusammenführen möchten, und fügen Sie den folgenden Code ein:
Sub mergeFiles()
'Merges all files in a folder to a main file.
'Define variables:
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet
Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
'Allow the user to select multiple workbooks
tempFileDialog.AllowMultiSelect = True
numberOfFilesChosen = tempFileDialog.Show
'Loop through all selected workbooks
For i = 1 To tempFileDialog.SelectedItems.Count
'Open each workbook
Workbooks.Open tempFileDialog.SelectedItems(i)
Set sourceWorkbook = ActiveWorkbook
'Copy each worksheet to the end of the main workbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet
'Close the source workbook
sourceWorkbook.Close
Next i
End Sub
- Um Ihr Makro auszuführen, wählen Sie im Code-Fenster das Benutzerformular Ausführen/Unterstützen (F5), wie in der folgenden Abbildung gezeigt. Alternativ können Sie auch F5 drücken.
- Wählen Sie im Desktop-Explorer die Excel-Dateien aus, aus denen Sie Ihre Tabellen kombinieren möchten. Für dieses Tutorial haben wir uns entschieden, unsere Excel-Dateien (Finance Table und Sales Table) im gleichen Ordner (Personal Library) zusammenzufassen. Klicken Sie dann auf Open.
Wir haben Excel-Blätter aus verschiedenen Excel-Dateien erfolgreich in einem neuen Workbook zusammengeführt.
Die Verwendung von VBA zum Zusammenführen mehrerer Blätter ist kein Kinderspiel. Daher können Sie dafür Coupler.io verwenden. Damit können Sie Daten aus verschiedenen Dateien ohne Kodierung zusammenführen und unterwegs organisieren.
Was ist die beste Lösung?
Excel-Makros eignen sich perfekt für das Zusammenführen von Excel-Blättern aus verschiedenen Dateien für Benutzer, die wissen, wie man programmiert. Alles, was Sie tun müssen, ist, den entsprechenden Code zu schreiben und ihn bei Bedarf auszuführen. Power Query ist auch sehr nützlich, um Excel-Blätter mit gemeinsamen Spalten zusammenzuführen.
Aber wenn das Schreiben von Code nicht Ihr Ding ist und Sie nicht zwischen verschiedenen Tools jonglieren wollen, um Excel-Tabellen zusammenzuführen, dann ist Coupler.io Ihre All-in-One-Lösung ohne Code. Sie kombinieren mehrere Excel-Dateien und -Blätter automatisch nach einem benutzerdefinierten Zeitplan.
Bevor Sie sich für eine Zusammenführungsoption in Excel entscheiden, sollten Sie die Struktur, den Inhalt und das Volumen Ihrer Daten berücksichtigen. Und nun los, Excel! ?