Für viele ausgefeilte Excel-Tabellen sind SVERWEIS() und WVERWEIS() unverzichtbar. Lesen Sie hier, wie Ihnen diese Funktionen helfen.
Die Funktion SVERWEIS ist eine Nachschlagefunktion. Sie dient also dazu, aus einem bestimmten Zellbereich Felder über ihren Inhalt zu finden. SVERWEIS prüft dabei eine vorgegebene Reihe von Zellen gegenüber einem angegebenen Vergleichswert und lokalisiert die erste Zelle, die den genannten Vergleichswert hat.
SVERWEIS hat noch eine Schwesterfunktion namens WVERWEIS. Gesucht wird immer in der ersten Spalte (SVERWEIS) oder Zeile (WVERWEIS) des Bereichs, den Sie angeben. Die ersten Buchstaben der Funktionsnamen bezeichnen dabei die jeweils verwendete Suchrichtung: „senkrecht“ bzw. „waagrecht“. Im Folgenden werden wir auf WVERWEIS nicht besonders eingehen. Alle für SVERWEIS gemachten Aussagen gelten aber auch hier – nur eben mit dem Unterschied, dass diese Funktion waagrecht arbeitet.
Die Parameter der Funktion SVERWEIS
Die Funktion hat das folgende Aufrufschema:
SVERWEIS(Suchkriterium; Matrix; Spaltenindex; Bereich_Verweis)
Der erste Wert in der Parameterliste ist der Vergleichswert, den Sie für die Suche verwenden wollen. Den muss eine Zelle aufweisen, damit Excel sie findet. Anders als Sie vermuten könnten, ist hier nur ein Wert erlaubt, aber kein logischer Ausdruck. Die Verweis-Funktionen prüfen immer nur auf „gleich oder größer“.
An zweiter Stelle steht die Matrix, also der Bereich, den Sie für die Suche verwenden möchten. SVERWEIS verwendet für die Suche die erste Spalte, also die Spalte ganz links im Suchbereich, WVERWEIS die oberste Zeile.
Mit Spaltenindex, also dem dritten Parameter der Funktion, geben Sie an, welche Zelle relativ zur Zelle mit dem gefundenen Wert zurückgegeben werden soll. Ist der Wert für den Index gleich 1, ist das die Fundstelle selbst. Geben Sie hier eine 2 an, ist es die Zelle rechts von der Fundstelle (darunter, falls Sie WVERWEIS nutzen), und so weiter.
Der letzte Parameter Bereich_Verweis hat eine unglückliche Bezeichnung, die den Sinn des Parameters nicht erklärt. Er entscheidet darüber, ob die Suche nach dem Wert exakt sein soll (dann müssen Sie als Parameterwert FALSCH einsetzen) oder ob Excel sich auch mit einem Wert größer als dem Suchkriterium zufrieden gibt (Bereich_Verweis ist WAHR oder nicht angegeben).
Achtung Geben Sie den letzten Parameter von SVERWEIS/WVERWEIS nicht an oder setzen ihn auf WAHR, dann müssen Ihre Werte im durchsuchten Bereich aufsteigend sortiert sein! Ansonsten gibt Ihnen die Funktion nicht das erwartete Ergebnis zurück! Die Erklärung dafür finden Sie am Ende dieses Tipps.
Die beiden Varianten je nach dem Wert des letzten Parameters unterscheiden sich in ihrer Funktion. Schauen Sie sich zuerst einmal den einfacheren Fall an, dass über Bereich_Verweis = WAHR die exakte Suche verwendet wird.
So nutzen Sie SVERWEIS für eine exakte Suche
Ein gängiges Beispiel hierfür wäre eine Telefonliste. Darin haben Sie ein Schlüsselfeld, etwa die Personalnummer aller Mitarbeiter.
Ist die Liste lang, macht es Mühe, sie von Hand zu durchsuchen. Mit Hilfe der Funktion SVERWEIS klappt das viel besser.
Dazu tragen Sie in einer Zelle die Personalnummer des gesuchten Mitarbeiters ein. Die Zelle rechts davon soll dann die Telefonnummer ausgeben und als Kontrolle erscheint noch eine Zelle weiter rechts der Name des Mitarbeiters.
Um dieses Ergebnis zu erreichen, tragen Sie in Zelle B1 die Formel SVERWEIS in folgender Weise ein. Sollte Ihr Telefonbuch, also der rot markierte Bereich, andere Ausmasse haben, dann müssen Sie den zweiten Parameter (also den im Bild rot markierten Teil) entsprechend anpassen.
In die Zelle rechts daneben, die den Namen des gesuchten Mitarbeiters erhalten soll, kopieren Sie die eben eingetippte Formel, wählen aber „4“ als Spaltenindex, was dem Namensfeld in der Liste entspricht.
Die Telefonliste muss keine aufsteigende Sortierung für das erste Feld mit der Personalnummer aufweisen. Weil hier SVERWEIS mit FALSCH als letztem Parameter verwendet wird, erfolgt eine exakte Suche, die das unnötig macht.
Ein einfaches Beispiel für SVERWEIS mit „unexakter Suche“
Ein Händler macht die Versandkosten vom Wert der Einkäufe abhängig: Bis 50 Euro beträgt die Versandpauschale 10 Euro, von 50-100 Euro sind es noch 5 Euro und ab 100 Euro muss der Kunde kein Porto zahlen.
Diese Regel können Sie in folgender Tabelle abbilden, die in der ersten Spalte immer den unteren Wert des jeweiligen Intervalls (also 0-50, 50-100, 100-) enthält:
Damit man die Tabelle innerhalb der Formel mit einem sprechenden Namen verwenden kann, markieren Sie die Versandkostentabellle vergeben für diesen Bereich den Namen „VersandTab“.
Im Beispiel markieren wie den Bereich B6 bis C8 und geben den Namen einfach im Namensfeld links oben ein
Ganz oben im Tabellenblatt bedinndet sich die Kalkulation für eine bestimmte Bestellung. Ganz oben wird der Warenwert eingetragen, darunter in B2 soll die Funktion SVERWEIS für uns die richtige Versandkostenpauschale berechnen und in der Zelle darunter summieren wir beide Werte auf.
Zur Ermittlung der Versandkosten schreiben Sie dazu in Zelle B2 folgende Funktion hinein:
=SVERWEIS(B1;VersandTab;2)
Excel soll also nacheinander die Zellen B6 bis B8 durchgehen und den darin gefundenen Wert mit dem Suchkriterium gegenprüfen, dessen Wert in B1 steht. Beim ersten Wert, der größer oder gleich dem eigegebenen Warenwert aus B1 ist bleibt die Suche in dieser Zeile stehen. Zurückgegeben wird dann der Wert der Zelle rechts davon, denn der Spaltenindex ist ja mit 2 angegeben.
So funktioniert die unexakte Suche per SVERWEIS im Detail
Die ursprüngliche Erklärung, dass SVERWEIS den ersten Wert sucht, der ins Schema passt, ist eine Vereinfachung. Damit Sie das Verhalten ganz verstehen, sollten Sie sich das vor Augen halten. Als Beispiel nehmen wir obiges Beispiel mit einem eingegebenen Warenwert von 32 Euro.
Excel nimmt sich zuerst die erste Zelle vor und prüft, ob ihr Wert kleiner oder gleich dem Suchwert ist. Da 32 größer als 0 ist, geht Excel zur nächsten Zelle. Hier trifft die Bedingung (32 <= 50) zu und die Suche ist erfolgreich. Nun geht Excel wieder einen Schritt zurück und verwendet diese Zeile als Fundstelle, gibt also 10 Euro Versandkosten aus.
Würden Sie zum Beispiel in Zelle B6 einen Euro als Startwert eintragen und dann eine Versandkostenberechnung mit einem Warenwert von 0,99 Euro durchführen, meldet Excel einen Fehler (#NV). Denn Excel hat im allerersten Vergleich gleich Erfolg, kann aber keine Zeile zurückgehen.
So führen unsortierte Werte zu einem Fehler
Wegen der Logik von Excel „finde die erste Zelle größer als Suchwert und gehe dann eins zurück“, gibt es merkwürdige Ergebnisse, wenn Sie Ihre Werte nicht aufsteigend sortiert vorliegen haben und mit der „unexaktern“ Version von SVERWEIS arbeiten, also den letzen Parameter auf WAHR setzen oder ganz weglassen..
Schauen Sie sich folgende Liste an, mit der eine Firma die Provision von Vertriebsmitarbeitern in Abhängiglkeit vom getätigten Umsatz ermitteln möchte:
Wenn Sie hier eine unexakte Suche arbeiten und mittels SVERWEIS die Provision für einen Umsatz von 12.000 Euro ermitteln möchten, schreiben Sie
=SVERWEIS(12000;A2:B4;2)
Was Excel dabei macht, ist:
A2 ansehen, Wert ist kleiner als Suchwert, also weiter
A3 ansehen. Wert ist größer als 12.000, also einen Schritt zurück
Wert in B3 ausgeben
