So vergleichen Sie zwei Spalten in Excel (mit VLOOKUP & IF)

Wenn Sie mit Daten in Excel arbeiten, müssen Sie früher oder später Daten vergleichen. Dies kann der Vergleich von zwei Spalten oder sogar von Daten in verschiedenen Blättern / Arbeitsmappen sein.

In diesem Excel-Tutorial zeige ich Ihnen verschiedene Methoden, um zwei Spalten in Excel zu vergleichen und nach Übereinstimmungen oder Unterschieden zu suchen.

Es gibt mehrere Möglichkeiten, dies in Excel zu tun, und in diesem Tutorial zeige ich Ihnen einige davon (z. B. den Vergleich mit der VLOOKUP-Formel oder der IF-Formel oder der bedingten Formatierung).

Also lass uns anfangen!

Vergleichen Sie zwei Spalten (nebeneinander)

Dies ist die grundlegendste Art des Vergleichs, bei dem Sie eine Zelle in einer Spalte mit der Zelle in derselben Zeile in einer anderen Spalte vergleichen müssen.

Angenommen, Sie haben ein Dataset wie unten gezeigt und möchten einfach überprüfen, ob der Wert in Spalte A in einer bestimmten Zelle im Vergleich zum Wert in der benachbarten Zelle gleich (oder unterschiedlich) ist.

Natürlich können Sie dies tun, wenn Sie einen kleinen Datensatz haben, wenn Sie einen großen haben, können Sie eine einfache Vergleichsformel verwenden, um dies zu tun. Und denken Sie daran, es besteht immer die Möglichkeit menschlicher Fehler, wenn Sie dies manuell tun.

Lassen Sie mich Ihnen ein paar einfache Möglichkeiten zeigen, dies zu tun.

Vergleichen Sie nebeneinander mit dem Gleichheitszeichenoperator

Angenommen, Sie haben den folgenden Datensatz und möchten wissen, welche Zeilen die übereinstimmenden Daten und welche Zeilen unterschiedliche Daten enthalten.

Unten finden Sie eine einfache Formel zum Vergleichen von zwei Spalten (nebeneinander):

=A2=B2

Die obige Formel gibt Ihnen TRUE, wenn beide Werte gleich sind, und FALSE, falls dies nicht der Fall ist.

Wenn Sie nun alle übereinstimmenden Werte kennen müssen, wenden Sie einfach einen Filter an und zeigen Sie nur alle WAHREN Werte an. Und wenn Sie alle Werte wissen möchten, die unterschiedlich sind, filtern Sie alle Werte, die FALSCH sind (wie unten gezeigt):

Vergleichen Sie nebeneinander mit der IF-Funktion

Eine andere Methode, mit der Sie zwei Spalten vergleichen können, ist die IF-Funktion.

Dies ähnelt der obigen Methode, bei der wir den Operator gleich (=) verwendet haben, mit einem zusätzlichen Vorteil. Wenn Sie die IF-Funktion verwenden, können Sie den Wert auswählen, den Sie erhalten möchten, wenn Übereinstimmungen oder Unterschiede vorliegen.

Wenn beispielsweise eine Übereinstimmung vorliegt, können Sie den Text „Übereinstimmung“ oder einen Wert wie 1 abrufen. In ähnlicher Weise können Sie bei einer Nichtübereinstimmung die Formel so programmieren, dass Sie den Text „Nichtübereinstimmung“ oder eine 0 oder leere Zelle erhalten.

Unten ist die IF-Formel, die ‚Match‘ zurückgibt, wenn die beiden Zellen den Zellenwert haben, und ‚Not a Match‘, wenn der Wert unterschiedlich ist.

=IF(A2=B2,"Match","Not a Match")

Die obige Formel verwendet dieselbe Bedingung, um zu überprüfen, ob die beiden Zellen (in derselben Zeile) übereinstimmende Daten haben oder nicht (A2 = B2). Da wir jedoch die IF-Funktion verwenden, können wir sie bitten, einen bestimmten Text zurückzugeben, falls die Bedingung Wahr oder falsch ist.

Sobald Sie die Formelergebnisse in einer separaten Spalte haben, können Sie die Daten schnell filtern und Zeilen mit übereinstimmenden Daten oder Zeilen mit nicht übereinstimmenden Daten abrufen.

Markieren Sie Zeilen mit übereinstimmenden Daten (oder anderen Daten)

Eine weitere gute Möglichkeit, die Zeilen mit übereinstimmenden Daten (oder anderen Daten) schnell zu überprüfen, besteht darin, diese Zeilen mithilfe der bedingten Formatierung hervorzuheben.

Sie können beides tun – markieren Sie Zeilen, die denselben Wert in einer Zeile haben, sowie den Fall, wenn der Wert unterschiedlich ist.

Angenommen, Sie haben ein Dataset wie unten gezeigt und möchten alle Zeilen hervorheben, in denen der Name gleich ist.

Im Folgenden finden Sie die Schritte zum Verwenden der bedingten Formatierung zum Hervorheben von Zeilen mit übereinstimmenden Daten:

1. Wählen Sie den gesamten Datensatz aus (mit Ausnahme der Kopfzeilen)
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Stile auf Bedingte Formatierung
4. Klicken Sie in den angezeigten Optionen auf ‚Neue Regel‘
5. Klicken Sie im Dialogfeld „Neue Formatierungsregel“ auf die Option -„Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen‘
6. Geben Sie im Feld ‚Werte formatieren, bei denen diese Formel wahr ist‘ die Formel ein: =$A2=$B2
7. Klicken Sie auf die Schaltfläche Format
8. Klicken Sie auf die Registerkarte ‚Füllen‘ und wählen Sie die Farbe aus, in der Sie die Zeilen mit demselben Wert in beiden Spalten hervorheben möchten
9. Klicken Sie auf OK

Die obigen Schritte würden sofort die Zeilen hervorheben, in denen der Name dasselbe in beiden Spalten A und B (in derselben Zeile). Und wenn der Name anders ist, werden diese Zeilen nicht hervorgehoben.

Wenn Sie zwei Spalten vergleichen und Zeilen mit unterschiedlichen Namen hervorheben möchten, verwenden Sie die folgende Formel im Dialogfeld bedingte Formatierung (in Schritt 6).

=$A2<>$B2

Wie funktioniert das?

Wenn wir die bedingte Formatierung mit einer Formel verwenden, werden nur die Zellen hervorgehoben, in denen die Formel wahr ist.

Wenn wir $A2=$B2 verwenden, wird jede Zelle (in beiden Spalten) überprüft und festgestellt, ob der Wert in einer Zeile in Spalte A dem in Spalte B entspricht oder nicht.

Falls es eine genaue Übereinstimmung ist, wird es in der angegebenen Farbe hervorgehoben, und falls es nicht übereinstimmt, wird es nicht.

Das Beste an der bedingten Formatierung ist, dass Sie keine Formel in einer separaten Spalte verwenden müssen. Wenn Sie die Regel auf ein Dataset anwenden, bleibt es dynamisch. Dies bedeutet, dass die bedingte Formatierung entsprechend angepasst wird, wenn Sie einen Namen im Dataset ändern.

Vergleichen Sie zwei Spalten mit VLOOKUP (Übereinstimmende / unterschiedliche Daten finden)

In den obigen Beispielen habe ich Ihnen gezeigt, wie Sie zwei Spalten (oder Listen) vergleichen, wenn wir nur Zellen nebeneinander vergleichen.

In Wirklichkeit wird dies selten der Fall sein.

In den meisten Fällen haben Sie zwei Spalten mit Daten und müssen herausfinden, ob ein Datenpunkt in einer Spalte in der anderen Spalte vorhanden ist oder nicht.

In solchen Fällen können Sie kein einfaches Gleichheitszeichen oder gar eine IF-Funktion verwenden.

Du brauchst etwas Mächtigeres …

… etwas, das genau in Vlookups Gasse liegt!

Lassen Sie mich Ihnen zwei Beispiele zeigen, in denen wir zwei Spalten in Excel mit der Funktion VLOOKUP vergleichen, um Übereinstimmungen und Unterschiede zu finden.

Vergleichen Sie zwei Spalten mit VLOOKUP und finden Sie Übereinstimmungen

Angenommen, wir haben einen Datensatz wie unten gezeigt, in dem wir einige Namen in den Spalten A und B haben.

Wenn Sie herausfinden müssen, welche Namen in Spalte B auch in Spalte A enthalten sind, können Sie die folgende VLOOKUP-Formel verwenden:

=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")

Die obige Formel vergleicht die beiden Spalten (A und B) und gibt Ihnen den Namen, falls der Name sowohl in Spalte B als auch in Spalte A enthalten ist, und gibt „Keine Übereinstimmung“ zurück, falls der Name in Spalte B und nicht in Spalte A enthalten ist.

Standardmäßig gibt die VLOOKUP-Funktion einen # N / A-Fehler zurück, falls keine genaue Übereinstimmung gefunden wird. Um den Fehler zu vermeiden, habe ich die VLOOKUP-Funktion in die IFERROR-Funktion eingefügt, sodass „Keine Übereinstimmung“ angezeigt wird, wenn der Name in Spalte A nicht verfügbar ist.

Sie können auch den umgekehrten Vergleich durchführen – um zu überprüfen, ob der Name sowohl in Spalte A als auch in Spalte B enthalten ist. Die folgende Formel würde:

=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")

Vergleichen Sie zwei Spalten mit VLOOKUP und finden Sie Unterschiede (fehlende Datenpunkte)

Im obigen Beispiel haben wir überprüft, ob die Daten in einer Spalte in einer anderen Spalte vorhanden waren oder nicht.

Sie können dasselbe Konzept auch verwenden, um zwei Spalten mit der Funktion VLOOKUP zu vergleichen und fehlende Daten zu finden.

Angenommen, wir haben einen Datensatz wie unten gezeigt, in dem wir einige Namen in den Spalten A und B haben.

Wenn Sie herausfinden müssen, welche Namen in Spalte B nicht in Spalte A enthalten sind, können Sie die folgende VLOOKUP-Formel verwenden:

=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")

Die obige Formel überprüft den Namen in Spalte B mit allen Namen in Spalte A. Falls es eine genaue Übereinstimmung findet, wird dieser Name zurückgegeben, und falls es keine genaue Übereinstimmung findet, wird der Fehler # N / A zurückgegeben.

Da ich daran interessiert bin, die fehlenden Namen in Spalte B und nicht in Spalte A zu finden, muss ich die Namen kennen, die den Fehler #N / A zurückgeben.

Aus diesem Grund habe ich die Funktion VLOOKUP in die Funktionen IF und ISERROR . Diese gesamte Formel gibt den Wert „Nicht verfügbar“ an, wenn der Name in Spalte A fehlt, und „Verfügbar“, wenn er vorhanden ist.

Um alle fehlenden Namen zu kennen, können Sie die Ergebnisspalte basierend auf dem Wert „Nicht verfügbar“ filtern.

Sie können auch die folgende MATCH-Funktion verwenden, um das gleiche Ergebnis zu erhalten:

=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")

Häufige Abfragen beim Vergleich von zwei Spalten

Im Folgenden finden Sie einige häufige Abfragen, die ich normalerweise erhalte, wenn Benutzer versuchen, Daten in zwei Spalten in Excel zu vergleichen.

Q1. Wie vergleiche ich mehrere Spalten in Excel in derselben Zeile für Übereinstimmungen? Zählen Sie auch die gesamten Duplikate.

Ans. Wir haben das Verfahren zum Vergleichen von zwei Spalten in Excel für dieselbe Zeile oben angegeben. Wenn Sie jedoch mehrere Spalten in Excel für dieselbe Zeile vergleichen möchten, lesen Sie das Beispiel

=IF(AND(A2=B2, A2=C2),"Full Match", "")

Hier haben wir Daten von Spalte A, Spalte B und Spalte C verglichen.

Um nun die Duplikate zu zählen, müssen Sie die Countif-Funktion verwenden.

=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")

Frage 2. Welchen Operator verwenden Sie für Übereinstimmungen und Unterschiede?

Ans. Unten sind die zu verwendenden Operatoren:

• Um Übereinstimmungen zu finden, verwenden Sie das Gleichheitszeichen (=)
• Um Unterschiede (Nichtübereinstimmungen) zu finden, verwenden Sie das Nicht-Gleichheitszeichen (<>)

Q3. Wie vergleiche ich zwei verschiedene Tabellen und ziehe übereinstimmende Daten?

Ans. Dazu können Sie die VLOOKUP-Funktion oder die INDEX & MATCH-Funktion verwenden. Um diese Sache besser zu verstehen, nehmen wir ein Beispiel.

Hier nehmen wir zwei Tabellen und wollen nun passende Daten ziehen. In der ersten Tabelle haben Sie einen Datensatz und in der zweiten Tabelle die Liste der Früchte und verwenden dann die übereinstimmenden Daten in einer anderen Spalte. Verwenden Sie zum Pull-Matching die Formel

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

Q4. Wie entferne ich Duplikate in Excel?

Ans. Um doppelte Daten zu entfernen, müssen Sie zuerst die doppelten Werte finden.

Um das Duplikat zu finden, können Sie verschiedene Methoden wie bedingte Formatierung, Vlookup, If-Anweisung und vieles mehr verwenden. Excel verfügt auch über ein integriertes Tool, mit dem Sie einfach die Daten auswählen und die Duplikate aus einer Spalte oder sogar mehreren Spalten entfernen können

Q5. Ich kann sehen, dass in beiden Spalten ein übereinstimmender Wert vorhanden ist. Die Formeln, die Sie oben geteilt haben, betrachten diese jedoch nicht als exakte Übereinstimmungen. Warum?

Ans: Excel betrachtet etwas als exakte Übereinstimmung, wenn jedes Zeichen einer Zelle der anderen entspricht. Es besteht eine hohe Wahrscheinlichkeit, dass in Ihrem Datensatz führende oder nachfolgende Leerzeichen vorhanden sind.

Obwohl diese Leerzeichen die Werte mit bloßem Auge immer noch gleich erscheinen lassen, sind diese für Excel unterschiedlich. Wenn Sie über einen solchen Datensatz verfügen, entfernen Sie diese Leerzeichen am besten (Sie können Excel-Funktionen wie TRIM verwenden).

Frage 7. So vergleichen Sie zwei Spalten, die das Ergebnis als WAHR ausgeben, wenn die ganzzahligen Werte aller ersten Spalten nicht kleiner als die ganzzahligen Werte der zweiten Spalte sind. Um dieses Problem zu lösen, benötige ich keine bedingte Formatierung, Vlookup-Funktion, If-Anweisung und andere Formeln. Ich brauche die Formel, um dieses Problem zu lösen.

Ans. Sie können die Array-Formel verwenden, um dieses Problem zu lösen.

Die Syntax lautet {=AND(H6:H12>I6:I12)}. Dies gibt Ihnen „True“ als Ergebnis, wenn der Wert von Spalte H größer als der Wert in Spalte I ist, sonst ist „False“ das Ergebnis.

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

• Vergleichen Sie zwei Spalten in Excel (für Übereinstimmungen und Unterschiede)
• So blenden Sie Spalten basierend auf dem Zellenwert in Excel aus
• So teilen Sie eine Spalte in Excel in mehrere Spalten auf
• So wählen Sie alternative Spalten in Excel aus (oder jede N-te Spalte)
• So fügen Sie eine gefilterte Spalte 9018>
• Beste Excel-Bücher (das macht Sie zu einem Excel-Profi in 2020)

Anzeige melden