Wie man zwei Spalten in Excel vergleicht (auf Übereinstimmungen und Unterschiede)
Video ansehen – Zwei Spalten in Excel auf Übereinstimmungen und Unterschiede vergleichen
Eine Frage, die ich oft gestellt bekomme, lautet: „Wie kann man zwei Spalten in Excel vergleichen?“.
Dies kann auf viele verschiedene Arten geschehen, und die zu verwendende Methode hängt von der Datenstruktur und den Anforderungen des Benutzers ab.
Zum Beispiel können Sie zwei Spalten vergleichen und alle übereinstimmenden Datenpunkte (die in beiden Spalten enthalten sind) oder nur die Unterschiede (wenn ein Datenpunkt in einer Spalte und nicht in der anderen enthalten ist) usw. finden oder hervorheben wollen.
Da ich so oft danach gefragt werde, habe ich beschlossen, diese umfangreiche Anleitung zu schreiben, um die meisten (wenn nicht alle) möglichen Szenarien abzudecken.
Wenn Sie diese Anleitung nützlich finden, geben Sie sie bitte an andere Excel-Benutzer weiter.
Dieses Lernprogramm deckt ab:
Bitte beachten Sie, dass die in diesem Lernprogramm gezeigten Techniken zum Vergleichen von Spalten nicht die einzigen sind.
Abhängig von Ihrem Datensatz müssen Sie die Methode möglicherweise ändern oder anpassen. Die Grundprinzipien bleiben jedoch gleich.
Wenn Sie der Meinung sind, dass diesem Tutorial etwas hinzugefügt werden kann, lassen Sie es mich im Kommentarbereich wissen
Zwei Spalten für exakte Zeilenübereinstimmung vergleichen
Dies ist die einfachste Form des Vergleichs. In diesem Fall müssen Sie einen zeilenweisen Vergleich durchführen und feststellen, welche Zeilen die gleichen Daten enthalten und welche nicht.
Beispiel: Zellen in derselben Zeile vergleichen
Nachfolgend ein Datensatz, bei dem ich prüfen muss, ob der Name in Spalte A in Spalte B derselbe ist oder nicht.
Wenn es eine Übereinstimmung gibt, brauche ich das Ergebnis als „WAHR“, und wenn es keine Übereinstimmung gibt, dann brauche ich das Ergebnis als „FALSCH“.
Die untenstehende Formel würde dies tun:
=A2=B2
Beispiel: Zellen in der gleichen Zeile vergleichen (mit WENN-Formel)
Wenn Sie ein aussagekräftigeres Ergebnis erhalten möchten, können Sie eine einfache WENN-Formel verwenden, um „Übereinstimmung“ zurückzugeben, wenn die Namen gleich sind, und „Nichtübereinstimmung“, wenn die Namen unterschiedlich sind.
=IF(A2=B2,"Match","Mismatch")
Hinweis: Wenn Sie beim Vergleich zwischen Groß- und Kleinschreibung unterscheiden möchten, verwenden Sie die folgende WENN-Formel:
=IF(EXACT(A2,B2),"Match","Mismatch")
Mit der obigen Formel würden „IBM“ und „ibm“ als zwei verschiedene Namen betrachtet und die obige Formel würde „Nicht übereinstimmend“ zurückgeben.
Beispiel: Zeilen mit übereinstimmenden Daten hervorheben
Wenn Sie die Zeilen mit übereinstimmenden Daten hervorheben möchten (anstatt das Ergebnis in einer separaten Spalte zu erhalten), können Sie dies mithilfe der bedingten Formatierung tun.
Hier sind die Schritte, um dies zu tun:
- Wählen Sie den gesamten Datensatz.
- Klicken Sie auf die Registerkarte ‚Start‘.
- Klicken Sie in der Gruppe ‚Formatvorlagen‘ auf die Option ‚Bedingte Formatierung‘.
- Klicken Sie in der Dropdown-Liste auf ‚Neue Regel‘.
- Klicken Sie im Dialogfeld ‚Neue Formatierungsregel‘ auf die Option ‚Eine Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen‘.
- Geben Sie in das Formelfeld die Formel ein: =$A1=$B1
- Klicken Sie auf die Schaltfläche „Format“ und geben Sie das Format an, das auf die übereinstimmenden Zellen angewendet werden soll.
- Klicken Sie auf „OK“.
Dadurch werden alle Zellen hervorgehoben, deren Namen in jeder Zeile gleich sind.
Zwei Spalten vergleichen und übereinstimmende Daten hervorheben
Wenn Sie zwei Spalten vergleichen und übereinstimmende Daten hervorheben möchten, können Sie die Duplikatfunktion in der bedingten Formatierung verwenden.
Beachten Sie, dass dies anders ist als der Vergleich der einzelnen Zeilen. In diesem Fall wird nicht Zeile für Zeile verglichen.
Beispiel: Zwei Spalten vergleichen und übereinstimmende Daten hervorheben
Oft gibt es Datensätze, bei denen es Übereinstimmungen gibt, die sich aber nicht in derselben Zeile befinden.
Wie unten gezeigt:
Beachte, dass die Liste in Spalte A größer ist als die in B. Außerdem sind einige Namen in beiden Listen vorhanden, aber nicht in derselben Zeile (z. B. IBM, Adobe, Walmart).
Wenn Sie alle übereinstimmenden Firmennamen hervorheben möchten, können Sie dies mithilfe der bedingten Formatierung tun.
Hier sind die Schritte, um dies zu tun:
- Wählen Sie den gesamten Datensatz aus.
- Klicken Sie auf die Registerkarte „Start“.
- Klicken Sie in der Gruppe „Formatvorlagen“ auf die Option „Bedingte Formatierung“.
- Halten Sie den Mauszeiger auf die Option „Zellregeln hervorheben“.
- Klicken Sie auf „Werte duplizieren“.
- Stellen Sie sicher, dass im Dialogfeld „Werte duplizieren“ „Duplizieren“ ausgewählt ist.
- Legen Sie die Formatierung fest.
- Klicken Sie auf OK.
Mit den obigen Schritten erhalten Sie das unten gezeigte Ergebnis.
Hinweis: Bei der bedingten Formatierung von doppelten Werten wird die Groß- und Kleinschreibung nicht berücksichtigt. Daher werden „Apfel“ und „Apfel“ als gleich angesehen und als Duplikate hervorgehoben.
Beispiel: Zwei Spalten vergleichen und nicht übereinstimmende Daten hervorheben
Wenn Sie die Namen hervorheben möchten, die in der einen Liste vorhanden sind und in der anderen nicht, können Sie auch hierfür die bedingte Formatierung verwenden.
- Markieren Sie den gesamten Datensatz.
- Klicken Sie auf die Registerkarte „Start“.
- Klicken Sie in der Gruppe „Formatvorlagen“ auf die Option „Bedingte Formatierung“.
- Fahren Sie mit dem Cursor über die Option „Zellregeln hervorheben“.
- Klicken Sie auf „Werte duplizieren“.
- Stellen Sie sicher, dass im Dialogfeld „Werte duplizieren“ „Eindeutig“ ausgewählt ist.
- Legen Sie die Formatierung fest.
- Klicken Sie auf OK.
So erhalten Sie das unten abgebildete Ergebnis. Es werden alle Zellen hervorgehoben, die einen Namen haben, der in der anderen Liste nicht vorhanden ist.
Zwei Spalten vergleichen und fehlende Datenpunkte finden
Wenn Sie herausfinden möchten, ob ein Datenpunkt aus einer Liste in der anderen Liste vorhanden ist, müssen Sie die Nachschlageformeln verwenden.
Angenommen, Sie haben einen Datensatz wie unten dargestellt und möchten Unternehmen identifizieren, die in Spalte A, aber nicht in Spalte B vorhanden sind,
Dazu kann ich die folgende VLOOKUP-Formel verwenden.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Diese Formel verwendet die VLOOKUP-Funktion, um zu prüfen, ob ein Unternehmensname in A in Spalte B vorhanden ist oder nicht. Wenn er vorhanden ist, wird dieser Name aus Spalte B zurückgegeben, andernfalls wird ein #N/A-Fehler zurückgegeben.
Diese Namen, die den #N/A-Fehler zurückgeben, sind diejenigen, die in Spalte B fehlen.
Die Funktion ISERROR würde TRUE zurückgeben, wenn das VLOOKUP-Ergebnis ein Fehler ist, und FALSE, wenn es kein Fehler ist.
Wenn Sie eine Liste aller Namen erhalten möchten, bei denen es keine Übereinstimmung gibt, können Sie die Ergebnisspalte filtern, um alle Zellen mit TRUE zu erhalten.
Sie können auch die MATCH-Funktion verwenden, um dasselbe zu tun;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Anmerkung: Ich persönlich bevorzuge die Verwendung der Match-Funktion (oder die Kombination von INDEX/MATCH) anstelle von VLOOKUP. Ich finde sie flexibler und leistungsfähiger. Den Unterschied zwischen Vlookup und Index/Match können Sie hier nachlesen.
Zwei Spalten vergleichen und die übereinstimmenden Daten abrufen
Wenn Sie zwei Datensätze haben und Elemente in einer Liste mit der anderen vergleichen und den übereinstimmenden Datenpunkt abrufen möchten, müssen Sie die Lookup-Formeln verwenden.
Beispiel: Abrufen der übereinstimmenden Daten (genau)
In der folgenden Liste möchte ich beispielsweise den Marktbewertungswert für Spalte 2 abrufen. Dazu muss ich diesen Wert in Spalte 1 nachschlagen und dann den entsprechenden Marktbewertungswert abrufen.
Nachfolgend die Formel, mit der dies möglich ist:
=VLOOKUP(D2,$A:$B,2,0)
oder
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Beispiel: Ziehen der übereinstimmenden Daten (teilweise)
Wenn Sie einen Datensatz erhalten, bei dem die Namen in den beiden Spalten nur geringfügig voneinander abweichen, wird die Verwendung der oben gezeigten Suchformeln nicht funktionieren.
Diese Suchformeln benötigen eine exakte Übereinstimmung, um das richtige Ergebnis zu liefern. Es gibt eine Option für ungefähre Übereinstimmungen in der VLOOKUP- oder MATCH-Funktion, die hier jedoch nicht verwendet werden kann.
Angenommen, Sie haben den unten abgebildeten Datensatz. Beachten Sie, dass es in Spalte 2 Namen gibt, die nicht vollständig sind (z.B. JPMorgan anstelle von JPMorgan Chase und Exxon anstelle von ExxonMobil).
In einem solchen Fall können Sie eine partielle Suche verwenden, indem Sie Platzhalterzeichen verwenden.
Die folgende Formel liefert in diesem Fall das richtige Ergebnis:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
oder
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
Im obigen Beispiel ist das Sternchen (*) ein Platzhalterzeichen, das für eine beliebige Anzahl von Zeichen stehen kann. Wenn der Suchwert auf beiden Seiten mit ihm flankiert wird, wird jeder Wert in Spalte 1, der den Suchwert in Spalte 2 enthält, als Übereinstimmung betrachtet.
Zum Beispiel wäre *Exxon* eine Übereinstimmung für ExxonMobil (da * eine beliebige Anzahl von Zeichen darstellen kann).
Die folgenden Excel-Tipps & Tutorials könnten Ihnen auch gefallen:
- Wie man zwei Excel-Blätter vergleicht (für Unterschiede)
- Wie man leere Zellen in Excel hervorhebt.
- Jede andere Zeile in Excel hervorheben.
- Excel Advanced Filter: A Complete Guide with Examples.
- Zeilen auf der Grundlage eines Zellwerts in Excel hervorheben.