So vergleichen Sie zwei Spalten in Excel mit SVERWEIS

Wenn Sie in Excel mit großen Datensätzen arbeiten, müssen Sie möglicherweise zwei Spalten vergleichen, um Ähnlichkeiten oder Unterschiede zu finden.

SVERWEIS ist eine leistungsstarke Funktion, mit der Sie nach übereinstimmenden Daten zwischen zwei Spalten suchen können. Mit der Funktion können Sie in einer Spalte nach Werten suchen, die in einer anderen Spalte erscheinen.

In diesem Artikel erfahren Sie, wie Sie zwei Spalten mit VLOOKUP vergleichen, damit Sie Ihre Daten effizient analysieren können.

Inhaltsverzeichnis

• Grundlagen der Vlookup-Funktion
  • Vlookup-Funktion
• Vorbereiten Ihrer Daten für SVERWEIS
  • Formatieren der Daten
• So verwenden Sie SVERWEIS zum Vergleichen zweier Spalten
  • Umgang mit anderen Fehlern
• Alternativen zu SVERWEIS für den Spaltenvergleich
  • 1. Verwenden von Index- und Match-Funktionen
  • 2. Einsatz von XLOOKUP in Excel
• Fünf Tipps zur Verbesserung der SVERWEIS-Leistung
  • 1. Beschränken Sie Ihren Suchbereich
  • 2. Verwenden Sie absolute Bezüge
  • 3. Sortieren Sie Ihre Daten
  • 4. Verwenden Sie einen doppelten VLOOKUP
  • 5. Verwenden Sie bedingte Formatierung
• Erweiterte Verwendungsmöglichkeiten für SVERWEIS
• Unser letztes Wort

Grundlagen der Vlookup-Funktion

Angenommen, Sie haben eine Tabelle mit zwei Listen mit Elementen in Spalte A und Spalte B. Sie möchten die Elemente in Liste 1 finden, die auch in Liste 2 erscheinen.

Sie können sich vorstellen, dass das manuelle Durcharbeiten von Listen mit Tausenden von Artikeln enorm zeitaufwändig wäre. Zum Glück bringt Excel SVERWEIS zur Rettung!

Der Begriff VLOOKUP steht für Vertical Lookup. Die Funktion vergleicht zwei Spalten, findet Übereinstimmungen zwischen ihnen und gibt die zugehörigen Werte zurück.

Vlookup-Funktion

Hier ist eine grundlegende Syntax der VLOOKUP-Funktion:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Dies ist eine Aufschlüsselung der Elemente:

• lookup_value : der Wert, nach dem Sie in der ersten Spalte des table_array suchen möchten.

• table_array : Der Zellbereich, der die Daten enthält, in denen Sie suchen möchten.

• col_index_num : die Spaltennummer im table_array, von der Sie den Wert zurückgeben möchten.

• range_lookup : Optional. Es ist entweder WAHR (ungefähre Übereinstimmung) oder FALSCH (genaue Übereinstimmung). Der Standardwert ist TRUE.

Vorbereiten Ihrer Daten für SVERWEIS

Bevor Sie SVERWEIS zum Vergleichen zweier Spalten in Excel verwenden , müssen Sie Ihre Daten vorbereiten.

Erstellen Sie in Ihrem Arbeitsblatt zwei separate Spalten, in denen Sie die Werte vergleichen möchten. In diesem Artikel werden Spalte A und Spalte B für unsere Beispiele verwendet.

Hier sind die Beispieldaten, die in diesem Artikel verwendet werden:

Dies ist die Beispieltabelle mit Kopfzeile, die dem Abschnitt mit dem Titel „Vorbereiten Ihrer Daten für SVERWEIS“ hinzugefügt werden soll:

Liste 1 Liste 2
Punkt 1 Punkt 3
Punkt 2 Punkt 4
Punkt 3 Punkt 1
Punkt 4 Punkt 5

Formatieren der Daten

Es ist wichtig sicherzustellen, dass die Daten in beiden Spalten ähnlich formatiert sind. VLOOKUP unterscheidet zwischen Groß- und Kleinschreibung, was bedeutet, dass Groß- und Kleinbuchstaben wichtig sind.

Wenn die Formatierung inkonsistent ist, kann es im Endergebnis zu Übereinstimmungsfehlern kommen.

Es empfiehlt sich außerdem, alle doppelten Werte oder leeren Zellen zu entfernen, um das Risiko von Fehlern zu minimieren.

So entfernen Sie doppelte Werte:

1. Wählen Sie die Spalte aus.

2. Gehen Sie im oberen Menüband zur Registerkarte „Daten“.

3. Klicken Sie im Abschnitt „Datentools“ auf „Duplikate entfernen“.

Der Knopf kann etwas schwer zu erkennen sein. Dieses Bild hilft Ihnen:

So entfernen Sie leere Zellen:

1. Wählen Sie die Spalte aus.

2. Gehen Sie im oberen Menüband zur Registerkarte „Startseite“.

3. Erweitern Sie das Menü „Suchen und Auswählen“.

4. Wählen Sie „Gehe zu Spezial“.

5. Wählen Sie „Leerzeichen“ aus den Optionen.

6. Klicken Sie im Abschnitt „Zellen“ auf „Löschen“.

So verwenden Sie SVERWEIS zum Vergleichen zweier Spalten

Sobald die Daten vorbereitet sind, können Sie die SVERWEIS-Formel schreiben, um zwei Spalten in Excel zu vergleichen und Übereinstimmungen zu identifizieren. Folge diesen Schritten:

1. Wählen Sie eine Zelle in einer neuen Spalte aus, in der Sie die Vergleichsergebnisse anzeigen möchten (z. B. Zelle C2).

2. Geben Sie die folgende Formel ein: =VLOOKUP(A2, B:B, 1, FALSE))

3. Drücken Sie die Eingabetaste, um die Formel anzuwenden.

4. Im Falle eines übereinstimmenden Werts wird der Wert in derselben Zeile der Ergebnisspalte angezeigt (z. B. Spalte C).

5. Ziehen Sie die Formel von C2 nach unten, um sie über so viele Zellen wie nötig zu kopieren.

Wenn Sie unsere Beispieldaten verwenden, tragen Sie Ihre Vergleichsergebnisse in die dritte Spalte ein. Die Formel findet drei Übereinstimmungen aus den vier Elementen in Liste 1.

Beachten Sie, dass Element 2 als #N/A angezeigt wird. Excel teilt uns mit, dass die Übereinstimmung nicht anwendbar ist, dh sie konnte nicht gefunden werden.

Das ist richtig und eine nützliche Information über fehlende Werte. Einige Excel-Benutzer denken jedoch möglicherweise, dass ein Datenproblem oder ein Funktionsfehler vorliegt.

Es empfiehlt sich, einen anderen Hinweis darauf anzuzeigen, dass keine Übereinstimmung gefunden wurde. Das könnte einfach ein Leerzeichen sein.

Kombinieren Sie dazu die VLOOKUP-Funktion mit der IFNA-Funktion wie folgt:

=IFNA(VLOOKUP(A1, B:B, 1, FALSE), „“)

Die IFNA-Funktion erkennt den #N/A-Fehler und ersetzt die Ausgabe durch ein Leerzeichen („“). Sie können auch die ISNA-Funktion verwenden oder mit der IF-Funktion über zusätzliche Logik verfügen.

Umgang mit anderen Fehlern

SVERWEIS kann manchmal zu anderen Fehlern in Ihren Vergleichsergebnissen führen. Das #REF! Ein Fehler in Excel ist ein echtes Problem mit Ihren Daten.

Dies tritt normalerweise auf, wenn der von Ihnen angegebene Bereich falsch ist. In unserem Beispiel wurde auf die gesamte Spalte verwiesen, Sie können aber auch vertikale Zellbereiche verwenden.

Stellen Sie sicher, dass der Suchbereich, auf den Sie sich beziehen, alle Werte abdeckt, die Sie vergleichen möchten.

Alternativen zu SVERWEIS für den Spaltenvergleich

Beim Vergleich zweier Spalten in Excel, um Übereinstimmungen zu finden, gibt es zwei Hauptalternativen-Suchfunktionen.

1. Verwenden von Index- und Match-Funktionen

Anstatt SVERWEIS zu verwenden, können Sie zwei Spalten in Excel vergleichen, indem Sie die Funktionen INDEX und MATCH kombinieren.

Diese Methode bietet eine flexiblere Möglichkeit zum Nachschlagen von Daten und ist besonders nützlich, wenn mit nicht benachbarten Spalten gearbeitet wird oder wenn sich der Spaltenindex ändern kann.

Dies ist die Syntax, die in die Ergebnisspalte eingefügt werden soll:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Return_range : der Zellbereich, der die Daten enthält, die Sie zurückgeben möchten.

• Lookup_value : der Wert, nach dem Sie innerhalb des Suchbereichs suchen möchten.

• Lookup_range : der Zellbereich, in dem Sie den Lookup-Wert finden möchten.

Unter Verwendung derselben Daten in den vorherigen Beispielen ersetzen wir die SVERWEIS-Formel wie folgt:

=(INDEX(B:B, MATCH(A2, B:B, 0)))

Die MATCH-Funktion gibt die relative Position des Suchwerts innerhalb des Suchbereichs zurück und die INDEX-Funktion gibt den entsprechenden Wert aus dem Rückgabebereich zurück.

Die Ergebnisse für dieselben Daten sind dieselben wie für den VLOOKUP, den wir zuvor verwendet haben. Dieses Bild zeigt die verwendeten Funktionen:

Sie können den Fehler #N/A auch durch eine benutzerdefinierte Nachricht ersetzen. Hier ist ein Beispiel für die Verwendung der IFERROR-Funktion.

=IFERROR(INDEX(B:B, MATCH(A2, B:B, 0)), „Nicht gefunden“)

2. Einsatz von XLOOKUP in Excel

Für Benutzer mit Excel 365 oder Excel 2019 ist XLOOKUP ein alternativer logischer Test zu VLOOKUP zum Finden gemeinsamer Werte in zwei Spalten.

XLOOKUP bietet mehrere Vorteile. Sie können damit Daten sowohl horizontal als auch vertikal durchsuchen, mit nicht benachbarten Spalten arbeiten und benutzerdefinierte Werte für Fehler angeben.

Die Syntax von XLOOKUP lautet:

=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

• lookup_value : der Wert, nach dem Sie innerhalb des Suchbereichs suchen möchten.

• lookup_range : der Zellbereich, in dem Sie den Suchwert finden möchten.

• return_range : der Zellbereich, der die Daten enthält, die Sie zurückgeben möchten.

• Fügen Sie nach Bedarf benutzerdefinierte Fehlerwerte, Übereinstimmungsmodus- und Suchmodusparameter hinzu.

XLOOKUP findet den Suchwert innerhalb des Suchbereichs und gibt den entsprechenden Wert aus dem Rückgabebereich zurück.

Die Formel ist besonders nützlich, wenn Sie zwei Listen in Datensätze mit mehreren Spalten eingebettet haben. In unseren vorherigen Beispielen befanden sich die Listen in der ersten und zweiten Spalte, aber Ihr Blatt enthält möglicherweise mehr Daten.

Hier ist ein Beispiel mit zwei Listen in der ersten und dritten Spalte einer Tabelle:

Die XLOOKUP-Formel sieht folgendermaßen aus:

=XLOOKUP(A2,C:C,C:C“,“)

Dieses Bild zeigt das Ergebnis mit dem Vergleich in Spalte D. Der erste Wert ist vorhanden, aber der zweite fehlt.

Beachten Sie, dass ich keine zusätzlichen Fehlerformeln habe, der fehlende Wert jedoch als Leerzeichen angezeigt wird. Das liegt daran, dass ich den vierten Parameter verwende, bei dem es sich um einen benutzerdefinierten Fehlerwert handelt. In diesem Fall handelt es sich um eine leere Zeichenfolge.

Als Bonus zeige ich Ihnen, wie Sie mit XLOOKUP zeilenübergreifende Vergleiche durchführen. Wenn Sie zwei Listen haben, die sich in Zeile 1 und Zeile 2 befinden, sieht die einfachste Verwendung der Formel so aus:

=XLOOKUP(B1, 2:2, 2:2, „“)

Dieses Bild zeigt die Ergebnisse in den beiden Zeilen.

Mit seiner verbesserten Funktionalität und Flexibilität ist XLOOKUP eine hervorragende Alternative zu VLOOKUP zum Vergleichen von Listen in Excel, um festzustellen, ob Übereinstimmungen vorhanden sind.

Fünf Tipps zur Verbesserung der SVERWEIS-Leistung

Durch die Optimierung Ihrer SVERWEIS-Funktion können Sie lange Wartezeiten vermeiden und die allgemeine Reaktionsfähigkeit Ihres Excel-Arbeitsblatts verbessern.

Hier sind sechs Tipps und Tricks, die Sie anwenden können, um die Leistung Ihres VLOOKUP in Excel zu verbessern.

1. Beschränken Sie Ihren Suchbereich

Ich habe in den Beispielen ganze Spalten verwendet, um sie einfacher zu gestalten. Wenn Sie mit großen Datenmengen arbeiten, sollten Sie dies vermeiden. Die Verwendung vollständiger Spalten kann den Berechnungsprozess von Excel verlangsamen.

Versuchen Sie stattdessen, genau den Bereich zu verwenden, der für Ihre Daten erforderlich ist (z. B. A1:A100). Dadurch wird die Anzahl der Zellen reduziert, die Ihre SVERWEIS-Funktion auswerten muss.

2. Verwenden Sie absolute Bezüge

Verwenden Sie bei der Angabe eines Datenbereichs (z. B. der Zellen von B2 bis B5) absolute Bezüge. Dadurch wird sichergestellt, dass die Formel konsistent ist und Daten vorhanden sind, wenn Sie sie über mehrere Zellen hinweg kopieren.

Hier ist ein Beispiel:

=SVERWEIS(A2, $B$2:$B$5, 1, FALSCH)

3. Sortieren Sie Ihre Daten

Wenn Sie wissen, dass die Daten in der Nachschlagespalte in aufsteigender Reihenfolge sortiert sind, können Sie VLOOKUP mit TRUE oder 1 für das Argument „range_lookup“ verwenden.

Dies führt dazu, dass Excel einen ungefähren Abgleich durchführt, der schneller ist als ein exakter Abgleich über alle Zellen hinweg. Seien Sie jedoch vorsichtig, wenn Sie diese Option verwenden, da eine falsche Sortierung zu falschen Ergebnissen führen kann.

4. Verwenden Sie einen doppelten VLOOKUP

Sie können zwei VLOOKUP-Funktionen verwenden, um den Suchvorgang in Excel zu beschleunigen.

Beim ersten VLOOKUP wird ermittelt, ob der Suchwert vorhanden ist, indem „col_index_num“ auf 1 und „range_lookup“ auf TRUE gesetzt wird.

Wenn TRUE zurückgegeben wird, ruft ein zweiter VLOOKUP den gewünschten Wert ab, wobei „range_lookup“ auf TRUE gesetzt ist.

5. Verwenden Sie bedingte Formatierung

Sie können in Ihrem Excel-Arbeitsblatt bedingte Formatierung verwenden, um übereinstimmende oder fehlende Werte in der angegebenen Spalte hervorzuheben. Sie können Farben auch auf eindeutige Werte anwenden. Dadurch werden Ihre Daten leichter lesbar.

Sie finden das Menü „Bedingte Formatierung“ in der Gruppe „Stile“ auf der Registerkarte „Startseite“.

Erweiterte Verwendungsmöglichkeiten für SVERWEIS

T-Tests für gepaarte Stichproben werden verwendet, um die Mittelwerte zweier verwandter Stichproben zu vergleichen. Dieses Video zeigt ihre Verwendung in Excel.

Wenn Sie über zusätzliche Daten oder Variablen verfügen, auf die Sie während Ihrer Analyse verweisen müssen, können Sie SVERWEIS verwenden, um die erforderlichen Werte aus einer anderen Tabelle oder einem anderen Arbeitsblatt abzurufen.

Beispielsweise können Sie SVERWEIS verwenden, um demografische Informationen oder Behandlungsbedingungen für jede gepaarte Beobachtung in Ihrem Datensatz abzurufen.

Unser letztes Wort

Mittlerweile haben Sie ein solides Verständnis für die Verwendung von SVERWEIS. Mit dieser leistungsstarken Funktion können Sie Unterschiede und übereinstimmende Werte zwischen zwei Listen schnell identifizieren und so die Datenanalyse effizienter und genauer gestalten.

Sie haben auch Beispiele für andere Such- und Referenzfunktionen gesehen, z. B. XLOOKUP, INDEX und MATCH. Wenn Sie diese Elemente zu Ihren Fähigkeiten hinzufügen, werden Ihre Datenanalysefähigkeiten weiter gestärkt.

Üben und verfeinern Sie Ihre SVERWEIS-Fähigkeiten weiter, und schon bald werden Sie ein Experte im Vergleichen von Spalten in Excel, wodurch Sie Zeit sparen und Ihre Produktivität steigern.