So vergleichen Sie zwei Spalten in Excel (für Übereinstimmungen und Unterschiede)

Video ansehen - Vergleichen Sie zwei Spalten in Excel auf Übereinstimmungen und Unterschiede

Die eine Abfrage, die ich häufig bekomme, lautet: „Wie vergleiche ich zwei Spalten in Excel?“.

Dies kann auf viele verschiedene Arten erfolgen, und die zu verwendende Methode hängt von der Datenstruktur und den Wünschen des Benutzers ab.

Sie möchten beispielsweise zwei Spalten vergleichen und alle übereinstimmenden Datenpunkte (die sich in beiden Spalten befinden) oder nur die Unterschiede (wo sich ein Datenpunkt in einer Spalte und nicht in der anderen befindet) usw. suchen oder hervorheben.

Da ich so oft danach gefragt werde, habe ich beschlossen, dieses umfangreiche Tutorial zu schreiben, um die meisten (wenn nicht alle) möglichen Szenarien abzudecken.

Wenn Sie dies nützlich finden, geben Sie es an andere Excel-Benutzer weiter.

Beachten Sie, dass die in diesem Tutorial gezeigten Techniken zum Vergleichen von Spalten nicht die einzigen sind.

Basierend auf Ihrem Datensatz müssen Sie möglicherweise die Methode ändern oder anpassen. Die Grundprinzipien würden jedoch die gleichen bleiben.

Wenn Sie der Meinung sind, dass diesem Tutorial etwas hinzugefügt werden kann, lassen Sie es mich im Kommentarbereich wissen

Vergleichen Sie zwei Spalten für eine genaue Zeilenübereinstimmung

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

Unten ist ein Datensatz, in dem ich überprüfen muss, ob der Name in Spalte A in Spalte B gleich ist oder nicht.

Wenn es eine Übereinstimmung gibt, brauche ich das Ergebnis als „TRUE“, und wenn nicht, dann brauche ich das Ergebnis als „FALSE“.

Die folgende Formel würde dies tun:

=A2=B2

Beispiel: Zellen in derselben Zeile vergleichen (mit der WENN-Formel)

Wenn Sie ein anschaulicheres Ergebnis erhalten möchten, können Sie eine einfache IF-Formel verwenden, um „Übereinstimmung“ bei gleichen Namen und „Nichtübereinstimmung“ bei unterschiedlichen Namen zurückzugeben.

=WENN(A2=B2,"Übereinstimmung","Nichtübereinstimmung")

Hinweis: Falls Sie beim Vergleich die Groß-/Kleinschreibung beachten möchten, verwenden Sie die folgende IF-Formel:

=WENN(GENAU(A2,B2),"Übereinstimmung","Nichtübereinstimmung")

Mit der obigen Formel würden „IBM“ und „ibm“ als zwei verschiedene Namen betrachtet und die obige Formel würde „Mismatch“ 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 abzurufen), können Sie dies mithilfe der bedingten Formatierung tun.

Hier sind die Schritte, um dies zu tun:

1. Wählen Sie den gesamten Datensatz aus.
2. Klicken Sie auf die Registerkarte "Startseite".
3. Klicken Sie in der Gruppe Stile auf die Option „Bedingte Formatierung“.
4. Klicken Sie im Dropdown-Menü auf „Neue Regel“.
5. Klicken Sie im Dialogfeld "Neue Formatierungsregel" auf "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen".
6. Geben Sie im Formelfeld die Formel ein: =$A1=$B1
7. Klicken Sie auf die Schaltfläche Format und geben Sie das Format an, das Sie auf die übereinstimmenden Zellen anwenden möchten.
8. OK klicken.

Dadurch werden alle Zellen hervorgehoben, deren Namen in jeder Zeile gleich sind.

Vergleichen Sie zwei Spalten und markieren Sie Übereinstimmungen

Wenn Sie zwei Spalten vergleichen und übereinstimmende Daten hervorheben möchten, können Sie die Duplizierungsfunktion in der bedingten Formatierung verwenden.

Beachten Sie, dass sich dies von dem unterscheidet, was wir beim Vergleich der einzelnen Zeilen gesehen haben. In diesem Fall werden wir keinen zeilenweisen Vergleich durchführen.

Beispiel: Zwei Spalten vergleichen und übereinstimmende Daten hervorheben

Häufig erhalten Sie Datensätze mit Übereinstimmungen, die sich jedoch möglicherweise nicht in derselben Zeile befinden.

Etwas wie unten gezeigt:

Beachten Sie, dass die Liste in Spalte A größer ist als die in B. Auch einige Namen sind in beiden Listen vorhanden, jedoch 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:

1. Wählen Sie den gesamten Datensatz aus.
2. Klicken Sie auf die Registerkarte Startseite.
3. Klicken Sie in der Gruppe Stile auf die Option „Bedingte Formatierung“.
4. Bewegen Sie den Cursor auf die Option Regeln zum Hervorheben von Zellen.
5. Klicken Sie auf Werte duplizieren.
6. Stellen Sie im Dialogfeld Werte duplizieren sicher, dass „Duplizieren“ ausgewählt ist.
7. Geben Sie die Formatierung an.
8. OK klicken.

Die obigen Schritte würden Ihnen das Ergebnis wie unten gezeigt liefern.

Hinweis: Bei der Duplikatsregel für die bedingte Formatierung wird die Groß-/Kleinschreibung nicht beachtet. „Apfel“ und „Apfel“ werden also als gleich betrachtet und als Duplikate hervorgehoben.

Beispiel: Zwei Spalten vergleichen und nicht übereinstimmende Daten hervorheben

Falls Sie die Namen hervorheben möchten, die in einer Liste vorhanden sind und in der anderen nicht, können Sie auch hierfür die bedingte Formatierung verwenden.

1. Wählen Sie den gesamten Datensatz aus.
2. Klicken Sie auf die Registerkarte Startseite.
3. Klicken Sie in der Gruppe Stile auf die Option „Bedingte Formatierung“.
4. Bewegen Sie den Cursor auf die Option Regeln zum Hervorheben von Zellen.
5. Klicken Sie auf Werte duplizieren.
6. Stellen Sie im Dialogfeld „Werte duplizieren“ sicher, dass „Eindeutig“ ausgewählt ist.
7. Geben Sie die Formatierung an.
8. OK klicken.

Dadurch erhalten Sie das Ergebnis wie unten gezeigt. Es hebt alle Zellen hervor, die einen Namen haben, der in der anderen Liste nicht vorhanden ist.

Vergleichen Sie zwei Spalten und finden Sie fehlende Datenpunkte

Wenn Sie feststellen 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 gezeigt und möchten Unternehmen identifizieren, die in Spalte A, aber nicht in Spalte B vorhanden sind.

Dazu kann ich die folgende SVERWEIS-Formel verwenden.

=ISERROR(SVERWEIS(A2,$B$2:$B$10,1,0))

Diese Formel verwendet die SVERWEIS-Funktion, um zu überprüfen, ob ein Firmenname in A in Spalte B vorhanden ist oder nicht. Wenn es vorhanden ist, wird dieser Name aus Spalte B zurückgegeben, andernfalls wird ein #N/A-Fehler zurückgegeben.

Diese Namen, die den Fehler #N/A zurückgeben, fehlen in Spalte B.

Die ISERROR-Funktion würde TRUE zurückgeben, wenn das SVERWEIS-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 das Gleiche auch mit der MATCH-Funktion tun;

=NOT(ISNUMBER(MATCH(A2,$B$2:$B$10,0)))

Hinweis: Ich persönlich bevorzuge die Match-Funktion (oder die Kombination von INDEX/MATCH) anstelle von SVERWEIS. Ich finde es flexibler und leistungsfähiger. Den Unterschied zwischen Vlookup und Index/Match können Sie hier nachlesen.

Vergleichen Sie zwei Spalten und ziehen Sie die übereinstimmenden Daten

Wenn Sie zwei Datensätze haben und Elemente in einer Liste mit der anderen vergleichen und den passenden Datenpunkt abrufen möchten, müssen Sie die Nachschlageformeln 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.

Unten ist die Formel, die dies tut:

=SVERWEIS(D2,$A$2:$B$14,2,0)

oder

=INDEX($A$2:$B$14,MATCH(D2,$A$2:$A$14,0),2)

Beispiel: Abrufen der übereinstimmenden Daten (teilweise)

Falls Sie einen Datensatz erhalten, bei dem sich die Namen in den beiden Spalten geringfügig unterscheiden, funktioniert die Verwendung der oben gezeigten Nachschlageformeln nicht.

Diese Nachschlageformeln benötigen eine genaue Übereinstimmung, um das richtige Ergebnis zu liefern. Es gibt eine ungefähre Übereinstimmungsoption in der SVERWEIS- oder MATCH-Funktion, die hier jedoch nicht verwendet werden kann.

Angenommen, Sie haben den unten gezeigten Datensatz. Beachten Sie, dass es in Spalte 2 unvollständige Namen gibt (z. B. JPMorgan statt JPMorgan Chase und Exxon statt ExxonMobil).

In einem solchen Fall können Sie eine teilweise Suche verwenden, indem Sie Platzhalterzeichen verwenden.

Die folgende Formel liefert in diesem Fall das richtige Ergebnis:

=SVERWEIS("*"&D2&"*",$A$2:$B$14,2,0)

oder

=INDEX($A$2:$B$14,MATCH("*"&D2&"*",$A$2:$A$14,0),2)

Im obigen Beispiel ist das Sternchen (*) ein Platzhalterzeichen, das eine beliebige Anzahl von Zeichen darstellen kann. Wenn der Nachschlagewert auf beiden Seiten damit flankiert wird, würde jeder Wert in Spalte 1, der den Nachschlagewert in Spalte 2 enthält, als Übereinstimmung betrachtet.

Zum Beispiel wäre *Exxon* eine Übereinstimmung mit ExxonMobil (da * eine beliebige Anzahl von Zeichen darstellen kann).

Die folgenden Excel-Tipps und -Tutorials könnten Ihnen auch gefallen:

• So vergleichen Sie zwei Excel-Tabellen (für Unterschiede)
• So markieren Sie leere Zellen in Excel.
• Markieren Sie JEDE andere REIHE in Excel.
• Excel Advanced Filter: Eine vollständige Anleitung mit Beispielen.
• Markieren Sie Zeilen basierend auf einem Zellenwert in Excel.