Excel

Excel SVERWEIS-Funktion

Excel Vlookup Function

Excel SVERWEIS-FunktionZusammenfassung

SVERWEIS ist eine Excel-Funktion zum Nachschlagen von Daten in einer vertikal organisierten Tabelle. SVERWEIS unterstützt ungefähre und genaue Übereinstimmungen und Platzhalter (* ?) für Teilübereinstimmungen. Lookup-Werte müssen im Erste -Spalte der an SVERWEIS übergebenen Tabelle.



Zweck Suchen eines Wertes in einer Tabelle durch Abgleichen in der ersten Spalte Rückgabewert Der abgeglichene Wert aus einer Tabelle. Syntax =SVERWEIS (Wert, Tabelle, Spaltenindex, [Bereichssuche]) Argumente
  • Wert - Der Wert, nach dem in der ersten Spalte einer Tabelle gesucht werden soll.
  • Tisch - Die Tabelle, aus der ein Wert abgerufen werden soll.
  • col_index - Die Spalte in der Tabelle, aus der ein Wert abgerufen werden soll.
  • range_lookup - [optional] TRUE = ungefähre Übereinstimmung (Standard). FALSE = genaue Übereinstimmung.
Ausführung Excel 2003 Nutzungshinweise

SVERWEIS ist eine Excel-Funktion zum Abrufen von Daten aus einer vertikal organisierten Tabelle. Lookup-Werte müssen im Erste -Spalte der an SVERWEIS übergebenen Tabelle. SVERWEIS unterstützt ungefähre und genaue Übereinstimmungen und Platzhalter (* ?) für Teilübereinstimmungen.

Vertikale Daten | Spaltennummern | Sieht nur richtig aus | Matching-Modi | Genaue Übereinstimmung | Ungefähre Übereinstimmung | Erstes Spiel | Wildcard-Match | Zwei-Wege-Suche | Mehrere Kriterien | #k.A. Fehler | Videos





V steht für vertikal

Der Zweck von SVERWEIS besteht darin, Informationen aus einer wie folgt organisierten Tabelle zu erhalten:

SVERWEIS ist für vertikale Daten



Unter Verwendung der Bestellnummer in Spalte B als Nachschlagewert kann SVERWEIS die Kundennummer, den Betrag, den Namen und den Status für jede Bestellung abrufen. Um beispielsweise den Kundennamen für die Bestellung 1004 zu erhalten, lautet die Formel:

 
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'

Für horizontale Daten können Sie die HVERWEIS , INDEX und MATCH , oder XVERWEIS .

Wählen Sie eine Zahl zwischen 1 und 12

SVERWEIS basiert auf Spaltennummern

Stellen Sie sich bei der Verwendung von SVERWEIS vor, dass jede Spalte im Tisch ist von links beginnend nummeriert. Um einen Wert aus einer bestimmten Spalte abzurufen, geben Sie die entsprechende Zahl als 'Spaltenindex' an. Der Spaltenindex zum Abrufen des folgenden Vornamens lautet beispielsweise 2:

Beispiel für eine genaue Übereinstimmung von SVERWEIS

Mit den Spalten 3 und 4 können Nachname und E-Mail abgerufen werden:

 
= VLOOKUP (H3,B4:E13,2,FALSE) // first name = VLOOKUP (H3,B4:E13,3,FALSE) // last name = VLOOKUP (H3,B4:E13,4,FALSE) // email address

SVERWEIS sieht nur richtig aus

SVERWEIS kann nur nach rechts schauen. Die abzurufenden Daten (Ergebniswerte) können in jeder Spalte erscheinen Nach rechts der Lookup-Werte:

SVERWEIS kann nur nach rechts schauen

Wenn Sie links nach Werten suchen müssen, siehe INDEX und MATCH , oder XVERWEIS .

Genaue und ungefähre Übereinstimmung

SVERWEIS hat zwei Modi des Abgleichs, genau und ungefähr. Der Name des Arguments, das die Übereinstimmung steuert, lautet ' range_lookup '. Dies ist ein verwirrender Name, denn er scheint etwas damit zu tun zu haben Zellbereiche wie A1:A10. Tatsächlich bezieht sich das Wort „Bereich“ in diesem Fall auf „Wertebereich“ – wenn range_lookup WAHR ist, entspricht SVERWEIS a Wertebereich anstatt einen genauen Wert. Ein gutes Beispiel dafür ist die Verwendung von SVERWEIS um Noten zu berechnen .

Es ist wichtig, das zu verstehen range_lookup ist standardmäßig WAHR , was bedeutet, dass SVERWEIS standardmäßig eine ungefähre Übereinstimmung verwendet, was gefährlich sein kann . Satz range_lookup auf FALSE, um eine genaue Übereinstimmung zu erzwingen:

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

Hinweis: Sie können für eine genaue Übereinstimmung auch Null (0) anstelle von FALSE angeben.

Genaue Übereinstimmung

In den meisten Fällen möchten Sie SVERWEIS wahrscheinlich im genauen Übereinstimmungsmodus verwenden. Dies ist sinnvoll, wenn Sie einen eindeutigen Schlüssel als Nachschlagewert verwenden, beispielsweise den Filmtitel in diesen Daten:

SVERWEIS genaue Übereinstimmung mit Filmen

Die Formel in H6 zu finden Jahr , basierend auf einer genauen Übereinstimmung mit dem Filmtitel, ist:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Ungefähre Übereinstimmung

In Fällen, in denen Sie die bester Treffer , nicht unbedingt ein genaue Übereinstimmung , sollten Sie den Näherungsmodus verwenden. Im Folgenden möchten wir beispielsweise einen Provisionssatz in der Tabelle G5:H10 nachschlagen. Die Lookup-Werte stammen aus Spalte C. In diesem Beispiel müssen wir SVERWEIS in verwenden ungefähre Übereinstimmung Modus, da in den meisten Fällen nie eine genaue Übereinstimmung gefunden wird. Die SVERWEIS-Formel in D5 ist so konfiguriert, dass sie eine ungefähre Übereinstimmung durchführt, indem das letzte Argument auf TRUE gesetzt wird:

SVERWEIS ungefähre Match-Provisionsrate

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

SVERWEIS durchsucht die Werte in Spalte G nach dem Suchwert. Wenn eine genaue Übereinstimmung gefunden wird, verwendet SVERWEIS sie. Wenn nicht, wird SVERWEIS 'zurücktreten' und mit der vorherigen Zeile übereinstimmen.

Hinweis: Daten müssen in aufsteigender Reihenfolge nach Nachschlagewert sortiert werden, wenn Sie den ungefähren Übereinstimmungsmodus mit SVERWEIS verwenden.

Erstes Spiel

Bei doppelten Werten findet SVERWEIS die erstes Spiel wenn der Match-Modus genau ist. Im unteren Bildschirm ist SVERWEIS so konfiguriert, dass der Preis für die Farbe 'Grün' ermittelt wird. Es gibt drei Einträge mit der Farbe Grün, und SVERWEIS gibt den Preis für die Erste Eintritt, . Die Formel in Zelle F5 lautet:

 
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17

SVERWEIS gibt die erste Übereinstimmung zurück

Wildcard-Match

Die SVERWEIS-Funktion unterstützt Platzhalter , die es ermöglicht, eine teilweise Übereinstimmung mit einem Nachschlagewert durchzuführen. Sie können beispielsweise SVERWEIS verwenden, um Werte aus einer Tabelle abzurufen, nachdem Sie sie nur eingegeben haben Teil eines Nachschlagewertes. Um Platzhalter mit SVERWEIS zu verwenden, müssen Sie den genauen Übereinstimmungsmodus angeben, indem Sie FALSE oder 0 für das letzte Argument angeben. range_lookup . Die Formel in H7 ruft den Vornamen „Michael“ ab, nachdem Sie „Aya“ in Zelle H4 eingegeben haben:

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

SVERWEIS Wildcard-Match

Lesen Sie mehr ausführliche Erklärung hier .

Zwei-Wege-Suche

Innerhalb der SVERWEIS-Funktion ist das Spaltenindexargument normalerweise als statische Zahl hartcodiert. Sie können jedoch auch eine dynamischer Spaltenindex indem Sie die MATCH-Funktion verwenden, um die rechte Spalte zu finden. Mit dieser Technik können Sie eine dynamische bidirektionale Suche erstellen, die in beiden Zeilen übereinstimmt und Säulen. Im folgenden Bildschirm ist SVERWEIS so konfiguriert, dass eine Suche basierend auf Name und Monat durchgeführt wird. Die Formel in H6 lautet:

 
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)

SVERWEIS Zwei-Wege-Suche

Für mehr Details, siehe dieses Beispiel .

Hinweis: Im Allgemeinen INDEX und MATCH ist eine flexiblere Möglichkeit, Zwei-Wege-Suchen durchführen .

Mehrere Kriterien

Die SVERWEIS-Funktion verarbeitet nicht nativ mehrere Kriterien. Sie können jedoch a Helferspalte um mehrere Felder miteinander zu verbinden und diese Felder wie mehrere Kriterien in SVERWEIS zu verwenden. Im folgenden Beispiel ist Spalte B eine Hilfsspalte, die verkettet Vor- und Nachname zusammen mit dieser Formel:

 
=C5&D5 // helper column

SVERWEIS ist so konfiguriert, dass es dasselbe tut, um einen Nachschlagewert zu erstellen. Die Formel in H6 lautet:

 
= VLOOKUP (H4&H5,B5:E13,4,0)

SVERWEIS mit mehreren Kriterien

Für Details, siehe dieses Beispiel .

Notiz: INDEX und MATCH und XVERWEIS sind robustere Möglichkeiten, Suchvorgänge basierend auf mehreren Kriterien zu handhaben.

SVERWEIS- und #N/A-Fehler

Wenn Sie SVERWEIS verwenden, werden Sie unweigerlich auf den Fehler #N/A stoßen. Der Fehler #N/A bedeutet nur 'nicht gefunden'. Im folgenden Bildschirm ist beispielsweise der Nachschlagewert „Toy Story 2“ in der Nachschlagetabelle nicht vorhanden und alle drei SVERWEIS-Formeln geben #N/A zurück:

SVERWEIS #N/A Fehlerbeispiel

Eine Möglichkeit, den NA-Fehler abzufangen, besteht darin, die IFNA-Funktion so was:

SVERWEIS #N/A Fehlerbeispiel - behoben

Die Formel in H6 lautet:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')

Die Nachricht kann nach Belieben angepasst werden. Um nichts zurückzugeben (d.h. um ein leeres Ergebnis anzuzeigen), wenn SVERWEIS #N/A zurückgibt, können Sie eine leere Zeichenfolge wie diese verwenden:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message

Der Fehler #N/A ist nützlich, da er Ihnen mitteilt, dass etwas nicht stimmt. In der Praxis kann dieser Fehler aus vielen Gründen auftreten, darunter:

  • Der Nachschlagewert ist in der Tabelle nicht vorhanden
  • Der Nachschlagewert ist falsch geschrieben oder enthält zusätzliches Leerzeichen
  • Der Match-Modus ist genau, sollte aber ungefähr sein
  • Der Tabellenbereich ist nicht richtig eingegeben
  • Sie kopieren SVERWEIS und die Tabelle Referenz ist nicht gesperrt

Weiterlesen: SVERWEIS ohne #N/A-Fehler

Mehr über SVERWEIS

Weitere Hinweise

  • Range_lookup kontrolliert, ob Wert muss genau übereinstimmen oder nicht. Der Standardwert ist TRUE = nicht genaue Übereinstimmung zulassen.
  • Satz range_lookup zu FALSCH zu benötigen eine genaue Übereinstimmung und WAHR zu eine ungenaue Übereinstimmung zulassen .
  • Wenn range_lookup WAHR ist (die Standardeinstellung), führt eine nicht genaue Übereinstimmung dazu, dass die SVERWEIS-Funktion mit dem nächsten Wert in der Tabelle übereinstimmt, der ist noch weniger als Wert .
  • Wann range_lookup weggelassen wird, erlaubt die SVERWEIS-Funktion eine nicht genaue Übereinstimmung, verwendet jedoch eine genaue Übereinstimmung, falls vorhanden.
  • Wenn range_lookup TRUE (die Standardeinstellung) ist, stellen Sie sicher, dass die Lookup-Werte in der ersten Zeile der Tabelle in aufsteigender Reihenfolge sortiert sind. Andernfalls kann SVERWEIS einen falschen oder unerwarteten Wert zurückgeben.
  • Wenn range_lookup ist FALSE (genaue Übereinstimmung erforderlich), Werte in der ersten Spalte von Tisch müssen nicht sortiert werden.


^