Zwei Listen ohne Duplikate zusammenführen

Eine klassische Situation: Sie haben zwei Listen, die zu einer zusammengeführt werden müssen. Darüber hinaus können in den Anfangslisten sowohl eindeutige als auch übereinstimmende Elemente vorhanden sein (sowohl zwischen den Listen als auch innerhalb), aber bei der Ausgabe müssen Sie eine Liste ohne Duplikate (Wiederholungen) erhalten:

Schauen wir uns traditionell verschiedene Möglichkeiten an, um ein so häufiges Problem zu lösen – von primitiv „auf der Stirn“ bis hin zu komplexer, aber elegant.

Methode 1: Entfernen Sie Duplikate

Sie können das Problem auf einfachste Weise lösen – kopieren Sie die Elemente beider Listen manuell in eine und wenden Sie das Tool dann auf die resultierende Menge an. Duplikate entfernen aus der Registerkarte Datum (Daten – Duplikate entfernen):

Diese Methode funktioniert natürlich nicht, wenn sich die Daten in den Orderbüchern häufig ändern – Sie müssen den gesamten Vorgang nach jeder Änderung erneut wiederholen. 

Methode 1a. Pivot-Tabelle

Diese Methode ist in der Tat eine logische Fortsetzung der vorherigen. Wenn die Listen nicht sehr groß sind und die maximale Anzahl der darin enthaltenen Elemente im Voraus bekannt ist (z. B. nicht mehr als 10), können Sie zwei Tabellen durch direkte Links zu einer kombinieren, rechts eine Spalte mit Einsen hinzufügen und Erstellen Sie eine Übersichtstabelle basierend auf der resultierenden Tabelle:

Wie Sie wissen, ignoriert die Pivot-Tabelle Wiederholungen, sodass wir bei der Ausgabe eine kombinierte Liste ohne Duplikate erhalten. Die Hilfsspalte mit 1 wird nur benötigt, weil Excel Übersichtstabellen erstellen kann, die mindestens zwei Spalten enthalten.

Wenn die ursprünglichen Listen geändert werden, gehen die neuen Daten über direkte Links in die kombinierte Tabelle, aber die Pivot-Tabelle muss manuell aktualisiert werden (Rechtsklick – Aktualisieren & Speichern). Wenn Sie keine spontane Neuberechnung benötigen, ist es besser, andere Optionen zu verwenden.

Methode 2: Array-Formel

Sie können das Problem mit Formeln lösen. In diesem Fall erfolgt die Neuberechnung und Aktualisierung der Ergebnisse automatisch und sofort, unmittelbar nach Änderungen in den ursprünglichen Listen. Der Einfachheit halber geben wir unseren Listen Namen. Liste 1 и Liste 2Verwendung von Name Manager Tab Formel (Formeln – Namensmanager – Erstellen):

Nach der Benennung sieht die benötigte Formel wie folgt aus:

Auf den ersten Blick sieht es gruselig aus, aber in Wirklichkeit ist alles nicht so gruselig. Lassen Sie mich diese Formel mit der Tastenkombination Alt+Enter um mehrere Zeilen erweitern und mit Leerzeichen einrücken, wie wir es beispielsweise hier getan haben:

Die Logik hier ist die folgende:

• Die Formel INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) wählt alle eindeutigen Elemente aus der ersten Liste aus. Sobald sie aufgebraucht sind, beginnt sie, einen #NV-Fehler auszugeben:

  

• Die Formel INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) extrahiert auf die gleiche Weise die eindeutigen Elemente aus der zweiten Liste.
• Ineinander verschachtelt realisieren zwei IFERROR-Funktionen nacheinander die Ausgabe der eindeutigen aus der Liste-1 und dann aus der Liste-2.

Beachten Sie, dass dies eine Matrixformel ist, dh sie muss nach dem Tippen in eine nicht gewöhnliche Zelle eingegeben werden Enter, aber mit einer Tastenkombination Ctrl+Shift+Enter und kopieren (ziehen) Sie dann mit einem Rand nach unten zu den untergeordneten Zellen.

In der englischen Version von Excel sieht diese Formel so aus:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), "") 

Der Nachteil dieses Ansatzes ist, dass Array-Formeln die Arbeit mit der Datei merklich verlangsamen, wenn die Quelltabellen eine große Anzahl (mehrere hundert oder mehr) Elemente enthalten. 

Methode 3. Power-Abfrage

Wenn Ihre Quelllisten eine große Anzahl von Elementen haben, zum Beispiel mehrere Hundert oder Tausende, dann verwenden Sie statt einer langsamen Array-Formel besser einen grundlegend anderen Ansatz, nämlich die Power Query-Add-In-Tools. Dieses Add-In ist standardmäßig in Excel 2016 integriert. Wenn Sie Excel 2010 oder 2013 haben, können Sie es separat herunterladen und installieren (kostenlos).

Der Aktionsalgorithmus lautet wie folgt:

1. Öffnen Sie eine separate Registerkarte des installierten Add-Ons Power Query (wenn Sie Excel 2010-2013 haben) oder gehen Sie einfach auf die Registerkarte Datum (wenn Sie Excel 2016 haben).
2. Wählen Sie die erste Liste aus und drücken Sie die Taste Aus Tabelle/Bereich (Aus Bereich/Tabelle). Wenn Sie gefragt werden, ob Sie einen „intelligenten Tisch“ aus unserer Liste erstellen möchten, stimmen wir zu:

   

3. Das Fenster des Abfrageeditors wird geöffnet, in dem Sie die geladenen Daten und den Abfragenamen sehen können Tabelle 1 (Sie können es auf Ihre eigene ändern, wenn Sie möchten).
4. Doppelklicken Sie auf den Tabellenkopf (word Liste 1) und benennen Sie es in einen anderen um (z Personen). Was genau zu nennen ist, ist nicht wichtig, aber der erfundene Name muss im Gedächtnis behalten werden, weil. sie muss später beim Import der zweiten Tabelle erneut verwendet werden. Das Zusammenführen zweier Tabellen in der Zukunft funktioniert nur, wenn ihre Spaltenüberschriften übereinstimmen.
5. Erweitern Sie die Dropdown-Liste in der oberen linken Ecke schließen und herunterladen und wählen Sie Schließen und laden… (Schließen&Laden nach…):

   

6. Wählen Sie im nächsten Dialogfeld (es kann etwas anders aussehen – keine Sorge) aus Stellen Sie einfach eine Verbindung her (Nur Verbindung herstellen):

   

7. Wir wiederholen den gesamten Vorgang (Punkte 2-6) für die zweite Liste. Beim Umbenennen einer Spaltenüberschrift ist es wichtig, denselben Namen (People) wie in der vorherigen Abfrage zu verwenden.
8. Im Excel-Fenster auf der Registerkarte Datum oder auf der Registerkarte Power Query Auswählen Daten abrufen – Anforderungen kombinieren – Hinzufügen (Daten abrufen – Abfragen zusammenführen – Anhängen):

   

9. Wählen Sie im angezeigten Dialogfeld unsere Anfragen aus den Dropdown-Listen aus:

   

10. Als Ergebnis erhalten wir eine neue Abfrage, bei der zwei Listen miteinander verbunden werden. Es bleibt übrig, Duplikate mit der Schaltfläche zu entfernen Zeilen löschen – Duplikate entfernen (Zeilen löschen – Duplikate löschen):

    

11. Die fertige Abfrage kann auf der rechten Seite des Optionsfelds umbenannt werden, um ihr einen vernünftigen Namen zu geben (dies wird tatsächlich der Name der Ergebnistabelle sein) und alles kann mit dem Befehl in das Blatt hochgeladen werden schließen und herunterladen (Schließen&Laden):

    

Bei Änderungen oder Ergänzungen der ursprünglichen Listen genügt künftig ein Rechtsklick, um die Ergebnistabelle zu aktualisieren.

• So sammeln Sie mehrere Tabellen aus verschiedenen Dateien mit Power Query
• Extrahieren einzigartiger Elemente aus einer Liste
• So vergleichen Sie zwei Listen auf Übereinstimmungen und Unterschiede