Zwei Listen in Excel vergleichen: Unterschiede, Duplikate und Abweichungen finden

Zwei Exporte aus verschiedenen Systemen sollten dieselben Daten enthalten, tun es aber nicht. Die Personalliste aus der Buchhaltung hat 212 Einträge, die aus der Zeiterfassung 208. Welche vier fehlen? Oder: Zahlungen auf dem Konto gegen Rechnungen im Buchhaltungssystem. Von Hand mit dem Auge zu vergleichen, kostet Stunden und ist fehleranfällig.

Excel erledigt das in Minuten, wenn Sie wissen wie. Dieser Artikel zeigt die Methode mit SVERWEIS und ZÄHLENWENN, die typischen Stolperfallen und eine kostenlose Vorlage, die die Arbeit abnimmt.

Das Prinzip: Ein Schlüssel verbindet die Listen

Um zwei Listen zu vergleichen, brauchen Sie eine Spalte, die jeden Eintrag eindeutig identifiziert: eine Personalnummer, eine Rechnungsnummer, eine Kundennummer, eine E-Mail-Adresse. Das ist der Schlüssel. Ohne ihn raten Sie nur.

Der Vergleich beantwortet dann drei Fragen:

1. Welche Schlüssel stehen in beiden Listen?
2. Welche stehen nur in Liste A, welche nur in Liste B?
3. Bei welchen Schlüsseln, die in beiden stehen, weichen die übrigen Werte voneinander ab?

Schritt für Schritt mit Formeln

Nehmen wir an, Liste A steht auf dem Blatt „A“, Liste B auf dem Blatt „B“, der Schlüssel jeweils in Spalte A, ein Betrag in Spalte B.

Vorhanden in der anderen Liste? In Blatt A, Zelle C2:

=WENN(ZÄHLENWENN(B!$A:$A;A2)>0;"in beiden";"nur in A")

Dieselbe Formel mit vertauschten Blättern setzen Sie in Blatt B ein. Damit haben Sie die erste und zweite Frage beantwortet.

Abweichende Werte: Holen Sie den Betrag aus der anderen Liste und vergleichen:

=WENN(C2="in beiden";WENN(B2=SVERWEIS(A2;B!$A:$B;2;FALSCH);"gleich";"Wert weicht ab");"")

Duplikate: =WENN(ZÄHLENWENN($A$2:$A$500;A2)>1;"doppelt";""). Doppelte Schlüssel verfälschen jeden Abgleich, prüfen Sie sie zuerst.

Beispiel mit sechs Zeilen:

Der Schlüssel muss sauber sein

Neun von zehn Abgleichen scheitern nicht an der Formel, sondern an den Daten. Typische Probleme:

• Leerzeichen am Anfang oder Ende. „Müller“ und „Müller “ sind für Excel verschieden. Abhilfe: =GLÄTTEN(A2).
• Groß- und Kleinschreibung. ZÄHLENWENN unterscheidet sie nicht, manche andere Funktionen schon. Vereinheitlichen Sie mit =GROSS(A2).
• Umlaute. Der eine Export schreibt „Müller“, der andere „Mueller“. Mit =WECHSELN(GROSS(A2);"Ü";"UE") bringen Sie beides auf eine Form.
• Zahl oder Text. Eine Nummer als Text „1001“ ist nicht gleich der Zahl 1001. Wandeln Sie mit =WERT() oder =TEXT() um.
• Mehrfachschlüssel. Gibt es denselben Schlüssel zweimal, nimmt SVERWEIS den ersten Treffer.

Bauen Sie darum eine Hilfsspalte „bereinigter Schlüssel“ und vergleichen Sie nur diese.

Häufige Fehler

• Mit Namen statt Nummern vergleichen. Namen haben Schreibvarianten („H. Meier“, „Hans Meier“). Das findet kein Excel zuverlässig. Besser ist eine Nummer.
• Nur in eine Richtung prüfen. Wer nur A gegen B abgleicht, übersieht Einträge, die nur in B stehen.
• Ergebnis nicht festhalten. Kopieren Sie die Ergebnislisten als Werte (Einfügen, Werte) in ein eigenes Blatt, bevor Sie die Eingangsdaten ändern.
• Formeln über zu große Bereiche. Spalten wie A:A sind bequem, bremsen bei mehreren tausend Zeilen aber spürbar.

Kostenlose Vorlage: Abgleich zweier Listen

Wer das nicht selbst aufbauen möchte, nimmt die kostenlose Excel-Vorlage. Sie fügen die beiden Listen ein, die Vorlage zeigt, was in beiden vorkommt, was nur in einer, und wo der Schlüssel passt, die Werte aber abweichen.

• Listen A und B: Schlüssel und zwei weitere Werte, bis zu 5000 Zeilen in jeder.
• Automatische Bereinigung des Schlüssels: überflüssige Leerzeichen, Groß- und Kleinschreibung und Ö (wird wie OE behandelt).
• Ergebnis je Zeile: „In der anderen Liste vorhanden“, „Nur hier“ oder „Vorhanden, aber Werte weichen ab“, dazu die Markierung doppelter Schlüssel.
• Übersicht: gesamt, übereinstimmend, nur in A, nur in B, Abweichungen, Duplikate.
• Fertige Listen „Nur in A“, „Nur in B“ und „Abweichungen“ mit Zeilennummern.

Die Vorlage läuft ohne Makros und externe Verknüpfungen in Excel 2007 und neuer sowie in LibreOffice und ist auf Deutsch. Sie prüft auf vollständige Übereinstimmung des bereinigten Schlüssels – eine unscharfe Suche, die „H. Meier“ und „Hans Meier“ findet, gibt es nicht. Bei mehrfach vorkommenden Schlüsseln werden die Wiederholungen markiert, abgeglichen wird mit dem ersten Treffer in der anderen Liste.

FAQ

Geht der Vergleich auch ohne Formeln?

Für schnelle Fälle ja: Spalten markieren, Start, Bedingte Formatierung, Doppelte Werte hervorheben. Das zeigt Duplikate über beide Listen, liefert aber keine Abweichungen in den Werten.

Was ist besser: SVERWEIS oder XVERWEIS?

XVERWEIS ist flexibler und steht in neueren Excel-Versionen zur Verfügung. SVERWEIS funktioniert überall, auch in älteren Versionen und in LibreOffice, deshalb ist es für Dateien, die weitergegeben werden, die sicherere Wahl.

Wie vergleiche ich Listen mit mehreren Schlüsselspalten?

Fügen Sie die Spalten in einer Hilfsspalte zusammen, etwa =A2&"|"&B2, und nutzen Sie diese als Schlüssel.

Wie viele Zeilen sind realistisch?

Einige tausend Zeilen verarbeitet Excel problemlos. Bei sehr großen Datenmengen sind Power Query oder eine Datenbank besser geeignet.