Kombinieren basierend auf gemeinsamen Spalten - Excel-Tipps

David aus Florida stellt die heutige Frage:

Ich habe zwei Arbeitsmappen. Beide haben die gleichen Daten in Spalte A, aber die verbleibenden Spalten sind unterschiedlich. Wie kann ich diese beiden Arbeitsmappen zusammenführen?

Ich fragte David, ob es möglich ist, dass eine Arbeitsmappe mehr Datensätze enthält als die andere. Und die Antwort lautet Ja. Ich fragte David, ob das Schlüsselfeld in jeder Datei nur einmal erscheint. Die Antwort lautet auch ja. Heute werde ich dies mit Power Query lösen. Die Power Query-Tools finden Sie in Windows-Versionen von Excel 2016+ im Abschnitt Get & Transform auf der Registerkarte Data. Wenn Sie über Windows-Versionen von Excel 2010 oder Excel 2013 verfügen, können Sie das Power Query-Add-In für diese Versionen herunterladen.

Hier ist Davids Arbeitsmappe 1. Es enthält Produkt und dann drei Datenspalten.

Das erste Arbeitsbuch

Hier ist Davids Arbeitsmappe 2. Sie enthält den Produktcode und weitere Spalten. In diesem Beispiel gibt es zusätzliche Produkte in Arbeitsmappe2, aber die Lösungen funktionieren, wenn eine der Arbeitsmappen zusätzliche Spalten enthält.

Die zweite Arbeitsmappe

Hier sind die Schritte:

Wählen Sie Daten, Daten abrufen, Aus Datei, Aus Arbeitsmappe:

Laden Sie Daten aus einer Datei
Navigieren Sie zur ersten Arbeitsmappe und klicken Sie auf OK
Wählen Sie im Navigator-Dialogfeld das Arbeitsblatt links aus. (Auch wenn es nur ein Arbeitsblatt gibt, müssen Sie es auswählen.) Die Daten werden rechts angezeigt.
Öffnen Sie im Navigator-Dialogfeld die Dropdown-Liste Laden und wählen Sie Laden in …
Wählen Sie Nur Verbindung erstellen und drücken Sie OK.
Wiederholen Sie die Schritte 1 bis 5 für die zweite Arbeitsmappe.

Stellen Sie eine Verbindung zur Arbeitsmappe her
Wenn Sie beide Arbeitsmappen erstellt haben, sollten im Bereich "Abfragen und Verbindungen" rechts auf Ihrem Excel-Bildschirm zwei Verbindungen angezeigt werden.

Verbindungen zu beiden Arbeitsmappen
Fahren Sie mit den Schritten zum Zusammenführen der Arbeitsmappen fort:
Daten, Daten abrufen, Abfragen kombinieren, zusammenführen.

Führen Sie zwei Abfragen mit unterschiedlichen Spalten zusammen
Wählen Sie im oberen Dialogfeld im Dialogfeld "Zusammenführen" die erste Abfrage aus.
Wählen Sie in der zweiten Dropdown-Liste im Dialogfeld "Zusammenführen" die zweite Abfrage aus.
Klicken Sie in der oberen Vorschau auf die Überschrift Produkt (dies ist das Schlüsselfeld. Beachten Sie, dass Sie zwei oder mehr Schlüsselfelder mit Strg + Klicken mehrfach auswählen können.)
Klicken Sie in der zweiten Vorschau auf die Überschrift Produktcode.
Öffnen Sie den Verbindungstyp und wählen Sie Vollständig Außen (Alle Zeilen von beiden).

Schritte 8 - 12 hier dargestellt
OK klicken. Die Datenvorschau zeigt die zusätzlichen Zeilen nicht an und zeigt nur "Tabelle" wiederholt in der letzten Spalte.

Das sieht nicht vielversprechend aus
Beachten Sie, dass in der Überschrift für DavidTwo ein "Erweitern" -Symbol angezeigt wird. Klicken Sie auf dieses Symbol.
Optional, aber ich deaktiviere immer "Ursprünglichen Spaltennamen als Präfix verwenden". OK klicken.

Erweitern Sie die Felder aus Arbeitsmappe 2
Die Ergebnisse werden in dieser Vorschau angezeigt:

Alle Datensätze aus beiden Arbeitsmappen
Verwenden Sie in Power Query Home, Close & Load.

Hier ist die schöne Funktion: Wenn sich die zugrunde liegenden Daten in einer der Arbeitsmappen ändern, können Sie auf das Symbol Aktualisieren klicken, um neue Daten in die Ergebnisarbeitsmappe zu ziehen.

Wiederholen Sie die Schritte 1 bis 16, indem Sie auf dieses Aktualisierungssymbol klicken.

Hinweis

Das Symbol für Aktualisieren ist normalerweise ausgeblendet. Ziehen Sie den linken Rand des Bereichs "Abfragen und Verbindungen" nach links, um das Symbol anzuzeigen.

Schau Video

Video-Transkript

Lernen Sie Excel aus Podcast, Episode 2216: Kombinieren Sie zwei Arbeitsmappen basierend auf einer gemeinsamen Spalte.

Hey, willkommen zurück bei Netcast, ich bin Bill Jelen. Die heutige Frage stammt von David, der an meinem Seminar in Melbourne, Florida, für das Space Coast Chapter der IIA teilgenommen hat.

David hat zwei verschiedene Arbeitsmappen, in denen Spalte A beiden gemeinsam ist. Also, hier ist Arbeitsmappe 1, hier ist Arbeitsmappe 2 - beide haben Produktcode. Dieser hat Elemente, die der erste nicht hat, oder umgekehrt, und David möchte alle Spalten kombinieren. Wir haben hier also drei Spalten und hier vier Spalten. Ich habe beide in dieselbe Arbeitsmappe eingefügt, falls Sie die Arbeitsmappe herunterladen, um mitzuarbeiten. Nehmen Sie jedes davon, verschieben Sie es in eine eigene Arbeitsmappe und speichern Sie es.

Okay, um diese Dateien zu kombinieren, verwenden wir Power Query. Power Query ist in Excel 2016 integriert. Wenn Sie die Windows-Version 10 oder 13 verwenden, können Sie zu Microsoft gehen und Power Query herunterladen. Sie können von einer neuen leeren Arbeitsmappe mit einem leeren Arbeitsblatt ausgehen. Sie werden diese Datei speichern - Speichern unter, wissen Sie, vielleicht Arbeitsmappe, um die Ergebnisse der kombinierten Dateien .xlsx anzuzeigen. In Ordung? Und wir werden zwei Abfragen durchführen. Wir gehen zu Daten, Daten abrufen, Aus Datei, Aus Arbeitsmappe und wählen dann die erste Datei aus. Wählen Sie in einer Vorschau das Blatt mit Ihren Daten aus, und wir müssen nichts mit diesen Daten tun. Öffnen Sie einfach das Ladefeld und wählen Sie Laden in, Nur Verbindung erstellen, und klicken Sie auf OK. Perfekt. Jetzt werden wir das für das zweite Element wiederholen - Daten, Aus Datei,Wählen Sie in einer Arbeitsmappe DavidTwo aus, wählen Sie den Blattnamen aus und öffnen Sie dann die Last Laden in, Nur Verbindung erstellen. Sie werden hier in diesem Bereich sehen, wir haben beide Verbindungen vorhanden. In Ordung.

Nun die eigentliche Arbeit - Daten, Daten abrufen, Abfragen kombinieren, Zusammenführen und dann im Dialogfeld Zusammenführen die Option DavidOne, DavidTwo auswählen. Dieser nächste Schritt ist völlig unintuitiv. Du musst das machen. Wählen Sie die Spalte (n) gemeinsam aus - also Produkt und Produkt. In Ordung. Und dann seien Sie hier sehr vorsichtig mit dem Join-Typ. Ich möchte alle Zeilen von beiden, weil eine möglicherweise eine zusätzliche Zeile hat und ich das sehen muss, und dann klicken wir auf OK. In Ordung. Und hier ist das erste Ergebnis. Es sieht nicht so aus, als hätte es funktioniert. Es sieht nicht so aus, als hätte es die zusätzlichen Elemente hinzugefügt, die in Datei 2 enthalten waren. Und wir haben diese Spalte 5 - sie ist jetzt null. Ich werde mit der rechten Maustaste auf Spalte 5 klicken und sagen: Entfernen Sie diese Spalte. Öffnen Sie also dieses Erweiterungssymbol und deaktivieren Sie dieses Kontrollkästchen für Ursprünglichen Spaltennamen als Präfix verwenden und BAM! Es klappt. Also die zusätzlichen Elemente, die in Datei 2 waren, die nicht in Datei 1 sind,erscheinen.

In Ordung. In der heutigen Datei sieht es so aus, als ob diese Produktcode-Spalte besser ist als diese Produktspalte, da sie zusätzliche Zeilen enthält. Aber es könnte einen Tag in der Zukunft geben, an dem Arbeitsmappe 1 Dinge enthält, die Arbeitsmappe 2 nicht hat. Also werde ich beide dort lassen, und ich werde keine Nullen loswerden, denn obwohl diese Zeile unten völlig null zu sein scheint, könnte es in Zukunft eine Situation geben, in der Wir haben hier ein paar Nullen, weil etwas fehlt. In Ordung? Also, endlich, Close & Load, und wir haben unsere sechzehn Zeilen.

Nehmen wir jetzt in Zukunft an, dass sich etwas ändert. Okay, also kehren wir zu einer dieser beiden Dateien zurück und ändern die Klasse für Apple auf 99, und lassen Sie uns sogar etwas Neues einfügen und diese Arbeitsmappe speichern. In Ordung. Und wenn wir möchten, dass unsere Zusammenführungsdatei aktualisiert wird, kommen Sie hierher. Achten Sie darauf, dass Sie beim ersten Mal das Aktualisierungssymbol nicht sehen können. Sie müssen diese Leiste greifen und darüber ziehen . Und wir werden aktualisieren und 17 Zeilen laden, die Wassermelone erscheint, der Apfel wechselt zu 99 - es ist eine schöne Sache. Möchten Sie mehr über Power Query erfahren? Kaufen Sie dieses Buch von Ken Puls und Miguel Escobar, M ist für (DATA) MONKEY. Ich werde dich auf den neuesten Stand bringen.

Zusammenfassung heute: David aus Florida hat zwei Arbeitsbücher, die er kombinieren möchte; Beide haben die gleichen Felder in Spalte A, aber die anderen Spalten sind alle unterschiedlich. Eine Arbeitsmappe enthält möglicherweise zusätzliche Elemente, die nicht in der anderen enthalten sind, und David möchte diese. In beiden Dateien sind keine Duplikate enthalten. Wir werden Power Query verwenden, um dies zu lösen. Beginnen Sie also in einer neuen leeren Arbeitsmappe auf einem leeren Arbeitsblatt. Sie werden drei Abfragen ausführen, zuerst eine: Daten, Aus Datei, Arbeitsmappe und dann Nur in erstellte Verbindung laden. Das Gleiche gilt für die zweite Arbeitsmappe. Wählen Sie dann Daten, Daten abrufen, Zusammenführen, wählen Sie die beiden Verbindungen aus, wählen Sie die Spalte aus, die in beiden gemeinsam ist - in meinem Fall Produkt -, und wählen Sie dann unter Verbindungstyp die vollständige Verbindung aus alles aus der Datei 1, alles aus Datei 2. Und dann ist das Schöne, wenn sich die zugrunde liegenden Daten ändern,Sie können die Abfrage einfach aktualisieren.

Um die Arbeitsmappe aus dem heutigen Video herunterzuladen, besuchen Sie die URL in der YouTube-Beschreibung.

Nun, hey, ich möchte wie David, dass er zu meinem Seminar erschienen ist. Ich möchte Ihnen dafür danken, dass Sie vorbeischauen. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.

Laden Sie die Excel-Datei herunter

So laden Sie die Excel-Datei herunter: combin-based-on-common-column.xlsx

Power Query ist ein erstaunliches Tool in Excel.

Excel-Gedanke des Tages

Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken: