Zwei Listen in Excel vergleichen: Ein praktischer Leitfaden

Bereiten Sie Ihre Listen für den Vergleich vor

Bevor Sie eine Vergleichsformel anwenden, stellen Sie sicher, dass beide Listen sauber und einheitlich formatiert sind. Zusätzliche Leerzeichen, führende oder nachgestellte Apostrophe und nicht druckbare Zeichen können falsche Nichtübereinstimmungen verursachen. Die Excel-Funktionen TRIM und CLEAN beseitigen die meisten dieser Probleme.

Wenn Ihre Listen innerhalb derselben Spalte Duplikate enthalten, entscheiden Sie, ob Sie jede Instanz oder nur eindeutige Werte vergleichen möchten. Das Entfernen interner Duplikate vermeidet oft irreführende Ergebnisse, insbesondere beim Zählen von Übereinstimmungen. Verwenden Sie die Funktion 'Duplikate entfernen' oder eine Hilfsspalte mit COUNTIF, um Duplikate vor dem listenübergreifenden Vergleich zu kennzeichnen.

1. Kopieren Sie jede Liste in eine eigene Spalte (z. B. Liste1 in Spalte A, Liste2 in Spalte B), beginnend mit Zeile 1.
2. Wählen Sie jede Spalte aus und führen Sie den Befehl 'Duplikate entfernen' auf der Registerkarte 'Daten' aus, wenn Sie nur eindeutige Einträge vergleichen möchten.
3. Wenden Sie =TRIM(A1) in einer neuen Spalte an und fügen Sie die Werte ein, um unerwünschte Leerzeichen zu entfernen, und ersetzen Sie dann die ursprünglichen Daten durch die bereinigte Version.

• Erstellen Sie immer eine Sicherungskopie Ihrer Rohdaten, bevor Sie Transformationen anwenden.
• Verwenden Sie =CLEAN(A1), um nicht druckbare Zeichen zu entfernen, die häufig aus anderen Systemen importiert werden.

=TRIM(A1)

=CLEAN(A1)

COUNTIF verwenden, um fehlende oder zusätzliche Elemente zu identifizieren

COUNTIF ist eine der einfachsten Methoden, um zu überprüfen, ob jedes Element einer Liste in der anderen vorkommt. Die Formel zählt das Vorkommen eines Werts in einem angegebenen Bereich. Ein Ergebnis von Null bedeutet, dass das Element fehlt; jede positive Zahl bedeutet, dass es mindestens einmal vorhanden ist.

Sie können eine Hilfsspalte erstellen, die für jede Zeile „Übereinstimmung“ oder „Nur in Liste1“ zurückgibt und Ihnen so ein klares Bild von Überschneidungen und Abweichungen liefert.

1. Stellen Sie sicher, dass sich beide Listen in den Spalten A und B befinden. Fügen Sie neben Liste1 eine neue Spalte C ein.
2. Geben Sie in Zelle C1 ein: =IF(COUNTIF(B:B,A1)=0, "Only in List1", "Match"). Ziehen Sie die Formel nach unten, um die gesamte Liste1 abzudecken.
3. Wiederholen Sie den Vorgang für Liste2 in Spalte D, um zu sehen, welche Elemente nur in Liste2 vorhanden sind.

• COUNTIF unterscheidet nicht zwischen Groß- und Kleinschreibung. Wenn die Groß-/Kleinschreibung wichtig ist, verwenden Sie den Ansatz mit SUMPRODUCT und EXACT (siehe Abschnitt 5).
• Bei großen Bereichen kann COUNTIF Ihre Arbeitsmappe verlangsamen; erwägen Sie, den Bereich auf die tatsächlich verwendeten Daten zu beschränken.

=IF(COUNTIF(B:B, A1)>0, "Match", "Only in List1")

XLOOKUP verwenden, um zugehörige Daten zu verknüpfen

Wenn Sie ein zugehöriges Feld und nicht nur ein Ja/Nein-Ergebnis zurückgeben müssen, kann XLOOKUP einen Bereich durchsuchen und den entsprechenden Wert aus einem anderen zurückgeben. Es ist in Microsoft 365 und aktuellen unbefristeten Excel-Versionen wie Excel 2021 und höher verfügbar; ältere Installationen benötigen möglicherweise INDEX-MATCH oder VLOOKUP.

XLOOKUP gibt ein Ergebnis pro Suchformel zurück. Wenn die Suche fehlschlägt, kann das optionale Argument für 'Nicht gefunden' eine klare Bezeichnung wie 'Nicht gefunden' anzeigen. Verwenden Sie FILTER, Power Query oder einen strukturierten Join, wenn ein Schlüssel mehrere Datensätze zurückgeben muss.

1. Angenommen, Ihre Suchwerte befinden sich in Spalte A (Liste1) und die abzurufenden Daten in den Spalten B und C (Suchspalte B, Rückspaltspalte C).
2. Geben Sie in Spalte D ein: =XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100, "Not found"). Passen Sie die Bereiche an Ihre Daten an.
3. Kopieren Sie die Formel nach unten. Einträge, die „Nicht gefunden“ anzeigen, fehlen in Liste2 oder sind vorhanden, aber ohne passenden Datensatz.

• XLOOKUP verwendet standardmäßig eine genaue Übereinstimmung, was ideal für Listenvergleiche ist.
• Für ältere Excel-Versionen verwenden Sie INDEX-MATCH oder VLOOKUP(FALSCH) als Alternativen.

=XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100, "Not found")

=INDEX($C$2:$C$100, MATCH(A2, $B$2:$B$100, 0))

Bedingte Formatierung verwenden, um Unterschiede hervorzuheben

Die bedingte Formatierung ermöglicht es Ihnen, Zellen, die eine Regel erfüllen, farblich zu kennzeichnen, sodass Abweichungen sofort sichtbar werden, ohne zusätzliche Spalten hinzufügen zu müssen. Sie können Zellen in Liste1 hervorheben, die in Liste2 fehlen, oder umgekehrt.

Dieser Ansatz eignet sich am besten für die visuelle Prüfung, wenn Sie keine dauerhafte Markierung benötigen oder wenn Sie eine Arbeitsmappe mit Kollegen teilen, die keine zusätzlichen Formelspalten sehen möchten.

1. Wählen Sie den Bereich in Liste1 (z. B. A2:A100) aus. Klicken Sie auf der Registerkarte 'Start' auf 'Bedingte Formatierung' > 'Neue Regel'.
2. Wählen Sie „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ aus. Geben Sie ein: =COUNTIF($B$2:$B$100, $A2)=0
3. Klicken Sie auf 'Format' und wählen Sie eine Füllfarbe (z. B. Rot) für Zellen, die nur in Liste1 vorkommen, und klicken Sie dann auf 'OK'. Wiederholen Sie den Vorgang für Liste2, wobei Sie Liste1 als Bereich referenzieren.

• Verwenden Sie gemischte Bezüge ($A2), damit die Formel für jede Zeile korrekt angepasst wird.
• Sie können auch Duplikate zwischen zwei Listen hervorheben, indem Sie die Regel für dieselben Bereiche in =COUNTIF($B$2:$B$100, $A2)>0 ändern.

=COUNTIF($B$2:$B$100, $A2)=0

Behandeln von Groß-/Kleinschreibung und zusätzlichen Leerzeichen

Standardvergleichsformeln in Excel (COUNTIF, XLOOKUP, VLOOKUP) unterscheiden nicht zwischen Groß- und Kleinschreibung. Wenn Ihre Listen „Apple“ und „apple“ enthalten und Sie diese als unterschiedlich behandeln müssen, müssen Sie die EXACT-Funktion zusammen mit SUMPRODUCT oder der bedingten Formatierung verwenden.

Selbst bei Vergleichen ohne Berücksichtigung der Groß-/Kleinschreibung können nachgestellte Leerzeichen zu falschen Nichtübereinstimmungen führen. Wenden Sie immer TRIM auf beide Listen an, bevor Sie vergleichen, oder verschachteln Sie TRIM in Ihrer Formel.

1. Führen Sie eine groß-/kleinbuchstabensensitive Übereinstimmung für Liste1 mit Liste2 durch, indem Sie in C1 eingeben: =IF(SUMPRODUCT((EXACT(A2, $B$2:$B$100))*1)>0, "Match", "Different") und nach unten ziehen.
2. Alternativ verwenden Sie die bedingte Formatierung mit einer Regel basierend auf EXACT: =SUMPRODUCT((EXACT($A2, $B$2:$B$100))*1)=0
3. Um Leerzeichen zu behandeln, umschließen Sie jeden Bereichsverweis mit TRIM: =IF(COUNTIF($B$2:$B$100, TRIM(A2))=0, ...)

• EXACT unterscheidet zwischen Groß- und Kleinschreibung und berücksichtigt auch Leerzeichen, daher sollten Sie die Daten vorher bereinigen.
• Bei großen Listen kann SUMPRODUCT mit EXACT langsam sein; erwägen Sie die Verwendung einer Hilfsspalte mit einem Flag für exakte Übereinstimmung.

=IF(SUMPRODUCT((EXACT(A2, $B$2:$B$100))*1)>0, "Match", "No match")

Verstehen Sie die Grenzen von Excel bei großen oder komplexen Vergleichen

Ein Excel-Arbeitsblatt hat eine feste Zeilenbegrenzung, aber die praktische Grenze für einen Vergleich liegt oft niedriger. Die Größe der Arbeitsmappe, Formelbereiche, Neuberechnung, bedingte Formatierung, verfügbarer Arbeitsspeicher und die Geschwindigkeit des Geräts beeinflussen die Reaktionsfähigkeit.

Exakte Mitgliedschaftsprüfungen sind einfach. Fuzzy-Matching, Mehrspalten-Joins und wiederholbare Datenpipelines erfordern Power Query, spezielle Add-Ins, Skripte oder eine Datenbank. Ein Browser-Listen-Tool ist für einen gezielten exakten Vergleich nützlich, aber es ist keine Fuzzy-Matching-Engine und sollte ebenfalls mit einer repräsentativen Stichprobe getestet werden.

• Vermeiden Sie vollständige Spaltenverweise, wenn ein begrenzter Bereich oder eine Tabelle ausreicht.
• Testen Sie die tatsächliche Arbeitsmappe, anstatt sich auf eine generische Zeilenschwelle zu verlassen.
• Verwenden Sie Power Query, SQL oder Skripte, wenn die Aufgabe strukturierte Joins oder wiederholbare Automatisierung erfordert.

Wann Sie zu einem browserbasierten Listenvergleichstool wechseln sollten

Für einen einmaligen exakten Vergleich kann das Kopieren der beiden Wertespalten in ein spezielles Browser-Tool schneller sein als das Pflegen von Formeln in der Arbeitsmappe. CompareTwoLists kann gemeinsame Werte, Werte nur in der ersten Liste, Werte nur in der zweiten Liste oder die kombinierte Menge anzeigen.

Die Verarbeitung erfolgt auf der Seite, sodass eingefügte Listenwerte nicht auf den Server der Website hochgeladen werden. Der Vergleich ist wertbasiert: Er führt kein Fuzzy-Matching von Namen durch, verknüpft keine vollständigen Tabellenkalkulationsdatensätze und leitet keine zugehörigen Überschriften ab. Kehren Sie zu Excel, Power Query, SQL oder einem Dataframe zurück, wenn diese Fähigkeiten erforderlich sind.

1. Kopieren Sie nur die beiden Wertespalten, die verglichen werden sollen.
2. Fügen Sie sie in Compare Two Lists oder Compare Columns ein.
3. Wählen Sie das gemeinsame, nur in der ersten, nur in der zweiten oder das kombinierte Ergebnis.
4. Überprüfen Sie die Zeilenanzahlen und stichprobenartig mehrere Werte, bevor Sie die Ausgabe verwenden.

• Lokale Verarbeitung auf der Seite für eingefügte Werte
• Exakte Listenmitgliedschaft und Mengenoperationen
• Kein Fuzzy-Matching oder vollständige Datensatzverknüpfungen
• Die Leistung hängt vom Browser, Gerät und der Eingabe ab