So vergleichen Sie zwei Spalten und finden fehlende Werte in Tabellenkalkulationen

Vorbereiten Ihrer Daten für einen sauberen Vergleich

Vor dem Vergleichen stellen Sie sicher, dass beide Spalten sauber und konsistent sind. Entfernen Sie überflüssige Leerzeichen mit der TRIM-Funktion, prüfen Sie auf führende Nullen oder versteckte Zeichen und entscheiden Sie, ob der Vergleich groß-/kleinschreibungssensitiv sein soll. Das alphabetische Sortieren jeder Spalte ist nicht strikt erforderlich, kann aber helfen, Ergebnisse später visuell zu überprüfen.

Leere Zellen können zu irreführenden Ergebnissen führen. Entscheiden Sie vorab, wie Sie sie behandeln: als fehlende Werte, als legitime Daten oder als auszuschließende Elemente. Wenn Duplikate in einer Spalte vorhanden sind, müssen diese möglicherweise separat behandelt werden, da ein einzelnes fehlendes Element mehrfach auftreten und die Zählung verzerren kann.

1. Kopieren Sie beide Spalten in benachbarte Spalten (z.B. Spalte A und Spalte B) in einem einzigen Blatt.
2. Verwenden Sie die TRIM-Funktion in einer Hilfsspalte, um versehentliche Leerzeichen zu entfernen: =TRIM(A1).
3. Konvertieren Sie Datentypen bei Bedarf (z.B. Zahlen, die als Text gespeichert sind). Verwenden Sie die Funktionen VALUE oder TEXT.
4. Beschriften Sie die Vergleichsspalten deutlich und sichern Sie eine Sicherungskopie der Originaldaten.

Verwenden von VLOOKUP oder XLOOKUP zum Finden fehlender Werte

VLOOKUP und XLOOKUP können testen, ob ein Wert aus der ersten Spalte in der zweiten vorhanden ist. XLOOKUP ist in Google Sheets und in aktuellen Excel-Versionen verfügbar; ältere Excel-Installationen können VLOOKUP, MATCH oder INDEX-MATCH verwenden.

Für eine Zugehörigkeitsprüfung geben Sie den Suchwert selbst zurück und verwenden das 'nicht gefunden'-Verhalten der Funktion oder IFERROR, um ‚Fehlt‘ anzuzeigen. Diese Formeln geben ein Ergebnis pro Eingabezeile zurück und beweisen keine Eins-zu-eins-Beziehung, wenn doppelte Schlüssel vorhanden sind.

• VLOOKUP: =IFERROR(VLOOKUP(A2, B$2:B$100, 1, FALSE), "Missing")
• XLOOKUP: =IFERROR(XLOOKUP(A2, B$2:B$100, B$2:B$100), "Missing")
• Ein #N/A-Fehler bedeutet, dass der Wert nicht gefunden wurde; IFERROR wandelt ihn in eine lesbare Bezeichnung um.

=IFERROR(VLOOKUP(A2, B$2:B$100, 1, FALSE), "Missing")

=IFERROR(XLOOKUP(A2, B$2:B$100, B$2:B$100), "Missing")

Verwenden von INDEX-MATCH für mehr Flexibilität

INDEX-MATCH ist eine flexible Alternative zu VLOOKUP, da der Rückgabebereich nicht rechts vom Suchbereich liegen muss. Es ist auch in Arbeitsmappen nützlich, die kein XLOOKUP haben.

Für eine Markierung fehlender Werte reicht MATCH allein; umschließen Sie es mit ISNA oder IFERROR. Die Leistung hängt von der Arbeitsmappe, der Bereichsgröße, dem Formeldesign und der Excel-Version ab, gehen Sie also nicht davon aus, dass ein Suchmuster immer schneller ist.

• Formel: =IFERROR(INDEX(B$2:B$100, MATCH(A2, B$2:B$100, 0)), "Missing")
• Die MATCH-Funktion gibt die relative Position eines Werts in einem Bereich zurück.
• INDEX gibt den tatsächlichen Wert aus der zweiten Spalte an dieser Position zurück. Wenn nicht gefunden, gibt IFERROR "Missing" zurück.

=IFERROR(INDEX(B$2:B$100, MATCH(A2, B$2:B$100, 0)), "Missing")

Bedingte Formatierung zum Hervorheben von Unterschieden

Für eine visuelle Übersicht kann die bedingte Formatierung Zellen in einer Spalte hervorheben, die in der anderen nicht vorkommen. Diese Methode ist nützlich, wenn Sie fehlende Werte auf einen Blick erkennen möchten, ohne Hilfsspalten hinzuzufügen. Sowohl Excel als auch Google Sheets unterstützen dies mit benutzerdefinierten Formeln.

Wenden Sie eine Regel basierend auf einer Formel an. Um beispielsweise Werte in Spalte A hervorzuheben, die in Spalte B nicht gefunden werden, wählen Sie A2:A100 aus und geben Sie eine COUNTIF- oder MATCH-Formel ein, die WAHR zurückgibt, wenn der Wert fehlt.

1. Wählen Sie den Bereich in Spalte A aus, den Sie formatieren möchten (z.B. A2:A100).
2. Gehen Sie zu Start > Bedingte Formatierung > Neue Regel (Excel) oder Format > Bedingte Formatierung (Sheets).
3. Wählen Sie 'Formel zur Ermittlung der zu formatierenden Zellen verwenden'.
4. Geben Sie eine Formel wie =COUNTIF(B$2:B$100, A2)=0 ein und legen Sie eine Füllfarbe fest.
5. Bestätigen und anwenden. Zellen in Spalte A, die in Spalte B fehlen, werden hervorgehoben.

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

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

Behandeln von Duplikaten in einer oder beiden Spalten

Duplikate können den Vergleich trüben. Wenn Ihre primäre Liste mehrere desselben Werts enthält, behandelt eine naive Formel jedes Vorkommen als separates Element und markiert möglicherweise denselben fehlenden Wert mehrfach. Ebenso verursachen Duplikate in der Suchliste keine Fehler, können aber zu unerwarteten Ergebnissen führen, wenn Sie eine Eins-zu-eins-Zuordnung erwarten.

Um Duplikate zu behandeln, entscheiden Sie zunächst, ob sie sinnvoll sind. Wenn sie ignoriert werden sollen, verwenden Sie COUNTIF, um Duplikate vor dem Vergleich zu markieren. Entfernen oder aggregieren Sie dann doppelte Zeilen. Alternativ können Sie für das Finden fehlender eindeutiger Werte eine eindeutige Liste als primäre Spalte verwenden.

1. Fügen Sie neben Ihrer primären Liste eine Hilfsspalte mit =COUNTIF(A$2:A2, A2)>1 ein, um Duplikate zu markieren.
2. Filtern oder sortieren Sie, um doppelte Einträge zu überprüfen und gegebenenfalls zu entfernen.
3. Verwenden Sie die UNIQUE-Funktion (Excel 365/2021, Google Sheets), um eine deduplizierte Liste für den Vergleich zu erstellen.
4. Führen Sie dann die Vergleichsformeln gegen die eindeutige Liste aus.

=COUNTIF(A$2:A2, A2)>1

Umgang mit leeren Zellen

Leere Zellen in beiden Spalten erfordern eine sorgfältige Handhabung. In vielen Formeln wird eine leere Zelle als Null oder leere Zeichenfolge behandelt, was zu falschen Übereinstimmungen oder Fehlt-Markierungen führen kann. Wenn beispielsweise beide Spalten in derselben Zeile Leerstellen haben, könnte VLOOKUP sie als gleichwertig betrachten, obwohl eine Leerstelle möglicherweise keinen gültigen Wert darstellt.

Um Leerstellen zu verwalten, bereiten Sie die Daten vor, indem Sie Leerstellen durch einen Platzhalter (z.B. "LEER") ersetzen oder eine IF-Bedingung verwenden, um leere Zellen vom Vergleich auszuschließen. Alternativ können Sie Formeln verwenden, die explizit mit ISBLANK auf Leerstellen prüfen.

1. Entscheiden Sie sich für eine Strategie: Behandeln Sie Leerstellen als zu vergleichenden gültigen Wert oder schließen Sie sie aus.
2. Wenn Sie Leerstellen als gültig behandeln, ersetzen Sie Leerstellen durch einen konsistenten Platzhalter: =IF(A1="", "LEER", A1).
3. Wenn Sie ausschließen, filtern Sie Zeilen, in denen eine Spalte leer ist, vor dem Vergleich heraus.
4. Verwenden Sie eine IF-Bedingung in Ihrer Formel: =IF(OR(A2="", B2=""), "Ausschließen", primärer_Vergleich).

=IF(A2="", "BLANK", A2)

Validieren Ihrer Ergebnisse und Vermeiden von Fehlalarmen

Validierung ist entscheidend. Fehlalarme können durch versteckte Leerzeichen, Groß-/Kleinschreibungsunterschiede oder Datentypunterschiede entstehen. Überprüfen Sie nach dem Anwenden Ihres Vergleichs stichprobenartig eine Auswahl markierter Elemente manuell. Verwenden Sie Filter, um fehlende Werte zu isolieren und zu bestätigen, dass sie nicht aufgrund von Formatierungsproblemen vorhanden sind.

Ein praktischer Validierungsschritt ist die Verwendung einer Pivot-Tabelle, um Vorkommen jedes Werts in der primären Spalte zu zählen und mit den Zählungen in der Suchspalte zu vergleichen. Prüfen Sie auch einige Einträge, indem Sie mit Strg+F oder Suchen nach dem genauen Text in der Quellspalte suchen. Diese zusätzliche Überprüfung stellt sicher, dass Ihre Fehlt-Liste korrekt ist, bevor Sie Maßnahmen ergreifen.

1. Fügen Sie eine temporäre Spalte hinzu, die Leerzeichen entfernt und mit UPPER und TRIM in eine konsistente Groß-/Kleinschreibung umwandelt.
2. Vergleichen Sie eine Stichprobe markierter Einträge manuell: Markieren Sie einen, kopieren Sie den Wert und suchen Sie in der Suchspalte.
3. Verwenden Sie eine Pivot-Tabelle: Zählen Sie jeden Wert in der primären Spalte und auch in der Suchspalte, dann vergleichen Sie die Zählungen.
4. Wenn Sie ein Werkzeug wie CompareTwoLists verwenden, beachten Sie, dass es vollständig in Ihrem Browser läuft und übereinstimmende und nicht übereinstimmende Sätze klar anzeigt.

=UPPER(TRIM(A2))

=UPPER(TRIM(B2))

=IF(COUNTIF($D$2:$D$100,C2)>0,"Match","Missing")