Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial zeigt, wie Sie das Excel SVERWEIS-Funktion in Excel, um einen Wert nachzuschlagen.
Übersicht über die SVERWEIS-Funktion
Die SVERWEIS-Funktion Vlookup steht für vertikales Nachschlagen. Es sucht nach einem Wert in der äußersten linken Spalte einer Tabelle. Gibt dann einen Wert mit einer angegebenen Anzahl von Spalten rechts vom gefundenen Wert zurück. Es ist dasselbe wie ein hlookup, außer dass Werte vertikal statt horizontal gesucht werden.
(Beachten Sie, wie die Formeleingaben angezeigt werden)
Syntax und Argumente der SVERWEIS-Funktion:
| 1 | =SVERWEIS(lookup_value,table_array,col_index_num,range_lookup) |
Lookup-Wert - Der Wert, nach dem Sie suchen möchten.
table_array - Der Datenbereich, der sowohl den Wert enthält, nach dem Sie suchen möchten, als auch den Wert, den das vlookup zurückgeben soll. Die Spalte mit den Suchwerten muss die Spalte ganz links sein.
col_index_num - Die Spaltennummer des Datenbereichs, aus dem Sie einen Wert zurückgeben möchten.
range_lookup - Richtig oder falsch. FALSE sucht nach einer genauen Übereinstimmung. TRUE sucht nach der nächsten Übereinstimmung, die kleiner oder gleich dem lookup_value ist. Wenn TRUE gewählt wird, muss die Spalte ganz links (die Nachschlagespalte) aufsteigend sortiert werden (von der niedrigsten zur höchsten).
Was ist die SVERWEIS-Funktion?
Als eine der älteren Funktionen in der Welt der Tabellenkalkulationen wird die SVERWEIS-Funktion verwendet, um Vertik Nachschlagen. Es hat einige Einschränkungen, die oft mit anderen Funktionen wie INDEX/MATCH überwunden werden. Es hat jedoch den Vorteil, dass es sich um eine einzelne Funktion handelt, die selbst eine genaue Übereinstimmungssuche durchführen kann. Es ist auch eine der ersten Funktionen, von denen die Leute erfahren.
Einfaches Beispiel
Schauen wir uns ein Beispiel von Daten aus einem Notenbuch an. Wir werden mehrere Beispiele zum Extrahieren von Informationen für bestimmte Schüler angehen.
Wenn wir herausfinden möchten, in welcher Klasse Bob sich befindet, würden wir die Formel schreiben:
| 1 | =SVERWEIS("Bob", A2:C5, 2, FALSCH) |
Wichtig ist, dass sich der gesuchte Artikel (Bob) in der ersten Spalte unseres Suchbereichs (A2:C5) befinden muss. Wir haben der Funktion mitgeteilt, dass wir einen Wert aus der 2 . zurückgeben wollennd Spalte, die in diesem Fall Spalte B ist. Schließlich haben wir angegeben, dass wir eine genaue Übereinstimmung indem Sie False als letztes Argument platzieren. Hier lautet die Antwort „Lesen“.
Seitentipp: Sie können als letztes Argument auch die Zahl 0 anstelle von False verwenden, da sie denselben Wert haben. Manche Leute bevorzugen dies, da es schneller zu schreiben ist. Wissen Sie nur, dass beides akzeptabel ist.
Verlagerte Daten
Um unser erstes Beispiel zu verdeutlichen, muss sich das Nachschlageelement nicht in Spalte A Ihrer Tabelle befinden, sondern nur in der ersten Spalte Ihres Suchbereichs. Verwenden wir denselben Datensatz:
Lassen Sie uns nun die Note für die Klasse der Naturwissenschaften finden. Unsere Formel wäre
| 1 | =SVERWEIS("Wissenschaft", B2:C5, 2, FALSCH) |
Dies ist immer noch eine gültige Formel, da die erste Spalte unseres Suchbereichs Spalte B ist, in der unser Suchbegriff „Wissenschaft“ zu finden ist. Wir geben einen Wert von 2 . zurücknd Spalte des Suchbereichs, in diesem Fall Spalte C. Die Antwort lautet dann „A-“.
Wildcard-Nutzung
Die SVERWEIS-Funktion unterstützt die Verwendung der Wildcards „*“ und „?“ bei der Suche. Nehmen wir zum Beispiel an, wir hätten vergessen, wie man Franks Namen schreibt, und wollten nur nach einem Namen suchen, der mit „F“ beginnt. Wir könnten die Formel schreiben
| 1 | =SVERWEIS("F*", A2:C5, 2, FALSCH) |
Dies könnte den Namen Frank in Zeile 5 finden und dann den Wert von 2 . zurückgebennd relative Spalte. In diesem Fall lautet die Antwort „Wissenschaft“.
Ungenaue Übereinstimmung
Meistens möchten Sie sicherstellen, dass das letzte Argument in SVERWEIS False (oder 0) ist, damit Sie eine genaue Übereinstimmung erhalten. Es kann jedoch vorkommen, dass Sie nach einer nicht genauen Übereinstimmung suchen. Wenn Sie eine Liste mit sortierten Daten haben, können Sie auch SVERWEIS verwenden, um das Ergebnis für das gleiche oder das nächstkleinere Element zurückzugeben. Dies wird häufig verwendet, wenn es sich um zunehmende Zahlenbereiche handelt, beispielsweise in einer Steuertabelle oder bei Provisionsprämien.
Angenommen, Sie möchten den Steuersatz für ein in Zelle D2 eingegebenes Einkommen ermitteln. Die Formel in D4 kann lauten:
| 1 | =SVERWEIS(D2, A2:B6, 2, WAHR) |
Der Unterschied in dieser Formel besteht darin, dass unser letztes Argument „Wahr“ ist. In unserem konkreten Beispiel können wir sehen, dass unsere Person, wenn sie ein Einkommen von 45.000 US-Dollar einbringt, einen Steuersatz von 15 % hat.
Notiz: Obwohl wir normalerweise eine genaue Übereinstimmung mit False als Argument wünschen, vergessen Sie, die 4 . anzugebenNS -Argument in einem SVERWEIS ist der Standardwert True. Dies kann zu unerwarteten Ergebnissen führen, insbesondere beim Umgang mit Textwerten.
Dynamische Spalte
SVERWEIS erfordert, dass Sie ein Argument angeben, das angibt, aus welcher Spalte Sie einen Wert zurückgeben möchten, aber es kann vorkommen, dass Sie nicht wissen, wo sich die Spalte befindet, oder Sie möchten, dass Ihr Benutzer die Spalte ändert, aus der zurückgegeben werden soll. In diesen Fällen kann es hilfreich sein, die MATCH-Funktion zu verwenden, um die Spaltennummer zu ermitteln.
Betrachten wir noch einmal unser Notenbuch-Beispiel mit einigen Eingaben in E2 und E4. Um die Spaltennummer zu erhalten, könnten wir eine Formel schreiben von
| 1 | =VERGLEICH(E2, A1:C1, 0) |
Dadurch wird versucht, die genaue Position von „Grade“ im Bereich A1:C1 zu finden. Die Antwort lautet 3. Wenn wir dies wissen, können wir es in eine SVERWEIS-Funktion einbinden und eine Formel in E6 wie folgt schreiben:
| 1 | =SVERWEIS(E4, A2:C5, VERGLEICH(E2, A1:C1, 0) |
Die MATCH-Funktion wird also zu 3 ausgewertet, und das weist den SVERWEIS an, ein Ergebnis aus der 3 . zurückzugebenrd Spalte im Bereich A2:C5. Insgesamt erhalten wir dann unser gewünschtes Ergebnis von „C“. Unsere Formel ist jetzt dynamisch, da wir entweder die anzuzeigende Spalte oder den zu suchenden Namen ändern können.
SVERWEIS-Einschränkungen
Wie am Anfang des Artikels erwähnt, besteht der größte Nachteil von SVERWEIS darin, dass der Suchbegriff in der Spalte ganz links des Suchbereichs gefunden werden muss. Es gibt zwar einige ausgefallene Tricks, die Sie tun können, um dies zu überwinden, aber die übliche Alternative ist die Verwendung von INDEX und MATCH. Diese Kombination gibt Ihnen mehr Flexibilität und kann manchmal sogar eine schnellere Berechnung sein.
SVERWEIS in Google Tabellen
Die SVERWEIS-Funktion funktioniert in Google Sheets genauso wie in Excel:
Zusätzliche Bemerkungen
Verwenden Sie die SVERWEIS-Funktion, um eine VERTICAL-Suche durchzuführen. Der SVERWEIS sucht nach einer genauen Übereinstimmung (range_lookup = FALSE) oder die nächste Übereinstimmung, die kleiner oder gleich dem lookup_value (range_lookup = TRUE, nur numerische Werte) in der ersten Zeile des table_array. Es gibt dann einen entsprechenden Wert zurück, n Zeilen unter der Übereinstimmung.
Wenn Sie einen SVERWEIS verwenden, um eine genaue Übereinstimmung zu finden, definieren Sie zuerst einen identifizierenden Wert, nach dem Sie suchen möchten, als Lookup-Wert. Dieser identifizierende Wert kann eine SSN, eine Mitarbeiter-ID, ein Name oder eine andere eindeutige Kennung sein.
Als nächstes definieren Sie den Bereich (genannt table_array), das die Bezeichner in der obersten Zeile enthält und alle Werte, nach denen Sie letztendlich in den Zeilen darunter suchen möchten. WICHTIG: Die eindeutigen Kennungen müssen sich in der obersten Zeile befinden. Wenn dies nicht der Fall ist, müssen Sie entweder die Zeile nach oben verschieben oder MATCH / INDEX anstelle von SVERWEIS verwenden.
Drittens, definieren Sie die Zeilennummer (Zeilenindex) des table_array die Sie zurückgeben möchten. Denken Sie daran, dass die erste Zeile mit den eindeutigen Bezeichnern Zeile 1 ist. Die zweite Zeile ist Zeile 2 usw.
Zuletzt müssen Sie angeben, ob nach einer genauen Übereinstimmung (FALSE) oder nach der nächsten Übereinstimmung (TRUE) im gesucht werden soll range_lookup. Wenn die Option für exakte Übereinstimmung ausgewählt ist und keine genaue Übereinstimmung gefunden wird, wird ein Fehler zurückgegeben (#N/A). Damit die Formel leer oder „nicht gefunden“ oder einen anderen Wert anstelle des Fehlerwerts (#N/A) zurückgibt, verwenden Sie die IFERROR-Funktion mit SVERWEIS.
So verwenden Sie die SVERWEIS-Funktion, um einen ungefähren Übereinstimmungssatz zurückzugeben: range_lookup = WAHR. Diese Option ist nur für numerische Werte verfügbar. Die Werte müssen aufsteigend sortiert sein.
Um eine horizontale Suche durchzuführen, verwenden Sie stattdessen die HLOOKUP-Funktion.
SVERWEIS-Beispiele in VBA
Sie können auch die SVERWEIS-Funktion in VBA verwenden. Typ:application.worksheetfunction.vlookup(lookup_value,table_array,col_index_num,range_lookup)
Für die Funktionsargumente (lookup_value usw.) können Sie diese entweder direkt in die Funktion eingeben oder stattdessen Variablen definieren.
Zurück zur Liste aller Funktionen in Excel
