Wie verwendet man SVERWEIS, um zwei Listen in separaten Arbeitsblättern zu vergleichen?

SVERWEIS zum Vergleichen von zwei Listen in separaten Arbeitsblättern mit Formeln

Ein praktischer und direkter Ansatz, um Listen, die sich in verschiedenen Excel-Arbeitsblättern befinden, zu vergleichen, ist die Verwendung der SVERWEIS-Funktion. Diese Methode hilft Ihnen, alle Namen, die sowohl in „Namen-1“ als auch in „Namen-2“ gefunden werden, effizient zu extrahieren oder zu markieren:

1. Wählen Sie im Tabellenblatt „Namen-1“ eine Zelle neben Ihren Listen-Daten (z.B. Zelle B2) und geben Sie die folgende Formel ein:

=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)

Drücken Sie dann Enter. Wenn der Name in der aktuellen Zeile in „Namen-2“ existiert, gibt die Formel den Namen zurück; wenn nicht, wird ein #NV-Fehler angezeigt. Siehe das folgende Beispiel:

2. Kopieren Sie die Formel nach unten, indem Sie den Ausfüllkästchen ziehen, um jeden Namen in „Namen-1“ mit allen Namen in „Namen-2“ zu vergleichen. Übereinstimmende Einträge zeigen den Namen an, während nicht gefundene Einträge einen Fehlerwert anzeigen:

Hinweise:

1. Für mehr Klarheit können Sie diese alternative Formel verwenden, um "Ja" oder "Nein"-Indikatoren für Übereinstimmungen zurückzugeben:

=IF(ISNA(VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)), "No", "Yes")

Diese Formel zeigt "Ja" für Namen an, die in beiden Blättern vorhanden sind, und "Nein" für Namen, die nur in „Namen-1“ vorkommen:

2. Wenn Sie diese Formeln verwenden, ersetzen Sie A2 durch die erste Zelle Ihrer Liste, „Namen-2“ durch den Namen des Referenzblatts und passen Sie $A$2:$A$19 so an, dass es dem tatsächlichen Datenbereich in Ihrem Arbeitsblatt entspricht. Denken Sie daran, dass Bereiche mit den richtigen Zeilennummern beginnen und enden müssen, um sicherzustellen, dass alle Ihre Daten berücksichtigt werden.

3. Tipps zur Verwendung: Wenn Sie #NV-Fehler sehen, wo Übereinstimmungen erwartet wurden, überprüfen Sie sorgfältig auf mögliche Probleme, die durch zusätzliche Leerzeichen, Unterschiede in der Datenformatierung (Text vs. Zahl) oder Tippfehler in Ihren Listen verursacht werden könnten. Verwenden Sie TRIM oder CLEAN in einer Hilfsspalte, um die Daten bei Bedarf zu bereinigen.

4. Um versehentliche Überschreibungen zu vermeiden, sollten Sie vor der Anwendung von Massenformeln Ihre Daten sichern. Außerdem können Sie nach dem Vergleich Filter auf Ihre Formelergebnisspalte anwenden, um schnell alle Übereinstimmungen oder eindeutigen Elemente anzuzeigen.

SVERWEIS zum Vergleichen von zwei Listen in separaten Arbeitsblättern mit Kutools für Excel

Wenn Sie Kutools für Excel haben, kann die Funktion „Gleiche & unterschiedliche Zellen auswählen“ Ihnen helfen, zwei Listen aus verschiedenen Arbeitsblättern schnell zu vergleichen und gemeinsame Namen zwischen diesen beiden Blättern auszuwählen oder zu markieren – alles ohne komplexe Formeln einzugeben. Diese Methode ist besonders effektiv, wenn Sie große Datenmengen behandeln oder ein visuelles, farbkodiertes Ergebnis wünschen, das leicht zu interpretieren ist.

Nach der Installation von Kutools für Excel führen Sie die folgenden Schritte aus, um Ihre Listen einfach zu vergleichen:

1. Gehen Sie zur Registerkarte Kutools, klicken Sie dann auf Auswählen > Gleiche & unterschiedliche Zellen auswählen, wie unten gezeigt:

2. Im geöffneten Dialogfeld „Gleiche & unterschiedliche Zellen auswählen":

(1.) Wählen Sie unter „Werte finden in“ den Bereich aus „Namen-1“, den Sie vergleichen möchten;

(2.) Wählen Sie unter „Entsprechend“ den Bereich aus „Namen-2“, mit dem Sie vergleichen möchten;

(3.) Wählen Sie im Abschnitt „Basierend auf“ „Jede Zeile“, um Zeilen separat zu vergleichen;

(4.) Wählen Sie im Abschnitt „Suchen“ „Gleiche Werte“, um übereinstimmende Namen zu identifizieren und hervorzuheben;

(5.) Optional können Sie eine Hintergrund- oder Schriftfarbe festlegen, um die Ergebnisse hervorzuheben und Übereinstimmungen visuell hervorzuheben.

3. Klicken Sie auf OK, und Sie sehen ein Meldungsfenster, das zeigt, wie viele übereinstimmende Zellen gefunden und hervorgehoben wurden. Alle Namen, die in beiden Listen vorkommen, werden ausgewählt und visuell betont, was weitere Überprüfung oder Änderung vereinfacht:

Klicken Sie hier, um Kutools für Excel jetzt herunterzuladen und kostenlos zu testen!

Praktische Tipps: Wenn Ihre Arbeitsblätter große Datensätze enthalten, können Sie nach der Markierung die Filterfunktion verwenden, um nur die Übereinstimmungen schnell zu überprüfen. Überprüfen Sie außerdem vor dem Vergleich, dass Ihre Bereichsauswahl korrekt ausgerichtet ist und keine Kopfzeilen enthält, es sei denn, dies ist beabsichtigt, da Fehlanpassungen die Ergebnisse beeinträchtigen können.

In seltenen Fällen, wenn die Funktion nicht die erwarteten Ergebnisse liefert, überprüfen Sie, ob beide Listen auf die gleiche Weise formatiert sind (z.B. beide als Text, ohne versteckte führende/nachfolgende Leerzeichen), da Formatunterschiede dazu führen können, dass Übereinstimmungen übersehen werden.

Bedingte Formatierung mit Formel über mehrere Blätter

Wenn Sie keine Formeln in Spalten schreiben oder Add-ins verwenden möchten, können Sie die bedingte Formatierung mit einer benutzerdefinierten Formel verwenden, um übereinstimmende Namen in einem Blatt basierend auf den Daten eines anderen Blatts visuell zu identifizieren. Diese Methode ist unkompliziert und erfordert kein VBA, liefert aber keine separate Ergebnisliste – sie formatiert lediglich Übereinstimmungen für eine schnelle visuelle Überprüfung.

Anwendbare Szenarien: Diese Lösung ist ideal für Benutzer, die einen unaufdringlichen, visuellen Hinweis auf übereinstimmende Werte wünschen und die Struktur des Arbeitsblatts nicht ändern möchten. Die Einschränkung ist, dass Regeln für bedingte Formatierung keine andere Arbeitsmappe direkt referenzieren können, und Formelreferenzen über Blätter funktionieren nur innerhalb derselben Datei.

Schritte:

1. Wählen Sie in „Namen-1“ den Bereich aus, auf den Sie die Hervorhebung anwenden möchten (z.B. A2:A19).

2. Gehen Sie zu Start > Bedingte Formatierung > Neue Regel > Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen.

3. Geben Sie im Formelfeld die folgende Formel ein:

=COUNTIF('Names-2'!$A$2:$A$19,A2)>0

Dies überprüft, ob der Wert in A2 von „Namen-1“ irgendwo in „Namen-2!A2:A19“ vorkommt.

4. Klicken Sie auf Format, um eine Hervorhebungsfarbe auszuwählen, und klicken Sie dann auf OK, um die Regel anzuwenden. Übereinstimmungen werden automatisch im ausgewählten Bereich hervorgehoben.

Praktische Tipps: Sie können die Bereiche je nach Ihren tatsächlichen Daten anpassen, und der COUNTIF-Schritt kann mit Filtern kombiniert werden, um sich nur auf hervorgehobene Zellen zu konzentrieren. Stellen Sie sicher, dass sich beide Arbeitsblätter in derselben Arbeitsmappe befinden, wenn Sie Querverweise zwischen Blättern einrichten, da Excel keine bedingten Formatierungsregeln unterstützt, die externe Dateien referenzieren.

Fehlerhinweise: Wenn die Hervorhebungen nicht wie erwartet erscheinen, überprüfen Sie Ihre Zellbereichsauswahl und Querverweise zwischen Blättern auf Fehler. Stellen Sie sicher, dass keine führenden/nachfolgenden Leerzeichen oder Formatinkonsistenzen vorliegen, die Übereinstimmungen verhindern könnten. Verwenden Sie bei Bedarf TRIM in einer Hilfsspalte, um die Listen für einen genauen Vergleich zu bereinigen.

VBA-Code – Automatischer Listenvergleich und Hervorhebung oder Extrahieren von Übereinstimmungen

Für Benutzer, die mit Makros vertraut sind, bietet die Verwendung von VBA-Code eine hochflexible und automatisierte Möglichkeit, zwei Listen in separaten Arbeitsblättern zu vergleichen. Dieser Ansatz ermöglicht es Ihnen, übereinstimmende Namen zu markieren oder die übereinstimmenden Werte an einen neuen Ort zu extrahieren, was besonders nützlich ist, wenn Sie große Datenmengen verarbeiten oder schnelle Aktualisierungen benötigen, wenn sich Ihre Listen ändern.

Anwendbare Szenarien: Diese Lösung ist besonders effektiv, wenn Sie Vergleiche wiederholt ausführen, sehr große Datensätze verarbeiten, Berichte automatisieren oder weiter anpassen möchten, wie Übereinstimmungen verarbeitet oder präsentiert werden. Während VBA-Kenntnisse erforderlich sind, profitieren Sie von vollständiger Automatisierung und Kontrolle. Ein Nachteil ist, dass Makros in der Arbeitsmappe aktiviert sein müssen, was aufgrund von Sicherheitseinstellungen in manchen Umgebungen nicht erlaubt sein könnte.

So führen Sie das Makro aus, um Übereinstimmungen in „Namen-1“ zu markieren, falls diese in „Namen-2“ vorhanden sind:

1. Klicken Sie auf Entwicklertools > Visual Basic, um das Fenster Microsoft Visual Basic for Applications zu öffnen. Klicken Sie im Fenster auf Einfügen > Modul und fügen Sie den folgenden Code in das neue Modul ein:

Sub HighlightMatchingNames()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim cell As Range
    Dim matchFound As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws1 = Worksheets("Names-1")
    Set ws2 = Worksheets("Names-2")
    
    Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
    
    ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Interior.ColorIndex = xlNone
    
    For Each cell In rng1
        Set matchFound = ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row).Find( _
            What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not matchFound Is Nothing And cell.Value <> "" Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

2. Klicken Sie im VBA-Editor auf die Schaltfläche, um den Code auszuführen. Dieses Makro scannt die Namen in Spalte A des Arbeitsblatts „Namen-1“ und markiert die Zelle in „Namen-1“ mit gelber Füllfarbe, wenn ein Name auch in Spalte A des Arbeitsblatts „Namen-2“ vorkommt. Vor dem neuen Vergleich werden alle vorherigen Markierungen im Bereich gelöscht.

Problembehandlung: Wenn keine Zellen markiert sind, überprüfen Sie, ob beide Arbeitsblätter exakt „Namen-1“ und „Namen-2“ heißen und dass Ihre Datenbereiche in A2 beginnen. Stellen Sie sicher, dass Makros aktiviert sind und weder Arbeitsblatt geschützt noch gefiltert ist. Dieser Ansatz kann leicht angepasst werden; zum Beispiel können Sie die Markierungsfarbe ändern oder den Code anpassen, um übereinstimmende Ergebnisse in ein anderes Blatt oder eine andere Spalte zu kopieren.

Zusammenfassung und Empfehlungen: Abhängig von Ihren Anforderungen und Ihrem technischen Komfortlevel können Sie zwischen integrierten Formellösungen, Makroautomatisierung, intelligenten Add-ins wie Kutools oder einfacher Visualisierung mit bedingter Formatierung wählen. Bei der Verwendung von Formeln oder VBA sollten Sie Ihre Daten immer auf überflüssige Leerzeichen oder inkonsistente Formatierung überprüfen, die häufig Fehlerquellen sind. Sichern Sie Ihre Daten vor dem Durchführen von Massenänderungen, insbesondere wenn Sie Makros oder Add-ins zum ersten Mal verwenden. Wenn Sie Probleme wie nicht aktualisierende Formeln oder falsche Übereinstimmungen feststellen, überprüfen Sie relative/absolute Bereichsfehler und überprüfen Sie die Arbeitsblattnamen. Indem Sie die Methode auswählen, die zu Ihrem Workflow passt, können Sie effektiv und effizient Listen über verschiedene Blätter in Excel hinweg vergleichen.