SVERWEIS Funktion

Syntax: SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

Die SVERWEIS-Funktion ist eine sehr beliebte Funktion in Excel, weil sie viel Zeit sparen kann. Die Funktion durchsucht anhand eines Suchkriteriums die erste Spalte der angegebenen Matrix nach einem Wert, der dem Suchkriterium entspricht. Falls es keinen Treffer gibt, wird der nächstkleinere Wert in der Spalte von der Funktion gewählt. Groß- und Kleinschreibung werden ignoriert, wenn es sich um nicht näher bestimmte Zeichenabfolgen handelt.

Ausgehend von der Position wird die im Spaltenindex festgelegte Spalte für die Ausgabe berücksichtigt. Falls als Spaltenindexdie Zahl 3 gewählt wird, gibt die Funktion den Wert aus der dritten Spalte rechts des Treffers (Suchkriterium) aus.

Am Ende der Syntax befindet sich das optionale Argument Bereich_Verweis, welcher ein Wahrheitswert ist. Falls nur exakte Übereinstimmungen mit dem Suchkriterium akzeptiert sind, sollte FALSCH gesetzt werden. WAHR hingegen sucht eine genaue oder ungefähre Übereinstimmung mit dem Suchkriterium. Nach ungefähren Treffern zu suchen ist nur sinnvoll, wenn die Werte der Tabelle in aufsteigender Ordnung sortiert sind.

SVERWEIS an einem einfachen Beispiel erklärt

In unserem Beispiel gibt es zwei Tabellen. In der Ersten Tabelle (A1 bis C269) stehen Namen, Alter und die Lieblingsautomarke der entsprechenden Person. Im zweiten Datenfeld befinden sich vier ausgesuchte Charaktere, die sich auch irgendwo in der großen, ersten Tabelle befinden. Unsere Aufgabe ist es, den Namen das dazugehörige Alter (Spalte G) und die entsprechende Automarke (Spalte H) zu finden. Die SVERWEIS Funktion löst diese und ähnliche Aufgaben innerhalb in kürzester Zeit.

Fangen wir mit dem Alter an. Wir klicken auf Zelle G5 und wenden diese SVERWEIS Funktion an.

=SVERWEIS(F5;A2C269;2;FALSCH)

Excel geht wie folgt vor: Finde einen Wert mit dem Suchkriterium aus Zelle F5 aus dem Bereich (Matrix) A2 bis C269. Bei einem übereinstimmenden Wert wird die zweite Spalte der ausgewählten Matrix (Spalte B) als Ausgabe verwendet. Durch das Suchkriterium wird die Zeile und mit dem Spaltenindexdie Spalte in der Matrix bestimmt. FALSCH (Bereich_Verweis) Bedeutet, dass nur identische Treffer akzeptiert werden. Annelie Ruebel ist laut unserer SVERWEIS Funktion 55. Dieser Wert sollte zumindest einmal überprüft werden, weil sich bei einem SVERWEIS gerne kleine Fehler einschleichen.

Kommen wir zur Lieblingsautomarke. Wir verwenden eine ähnliche Funktion, mit dem Unterschied, dass unser Spaltenindex drei statt zwei aufweist. Das bedeutet, dass bei einem Treffer bis zur dritten Spalte der Matrix gezählt wird. In unserem Beispiel wird es Spalte C mit den Automarken sein.

=SVERWEIS(F5;A2C269;3;FALSCH)

Um zeit zu sparen, können beide Versionen der SVERWEIS Funktion (Spalte G und H) heruntergezogen werden. Jedoch sollte die Matrix mit fixen Bezügen ausgestattet werden (siehe Dollarzeichen). Jetzt kann die Funktion nach unten verschoben werden, ohne jedoch die Matrix nach unten zu versetzen.

=SVERWEIS(F5;$A$2$C$269;2;FALSCH)

Häufige Anwendungsfehler

Matrix zu schmal

Oftmals wird der Fehler gemacht, für den Spaltenindex eine so große Zahl zu wählen, die über die festgelegte Matrix hinausläuft und zur Fehlermeldung #BEZUG führt. Das passiert, wenn die Matrix beispielsweise vier Spalten weit eingegrenzt wurde, aber als Spaltenindex die fünfte oder sechste Spalte verlangt. Hier empfiehlt es sich, die Matrix entsprechend nach rechts zu erweitern.

Verschobene Matrix

Ein weiterer Fehler, der oft übersehen wird, ist die Problematik mit der verschobenen Matrix. In der Praxis werden nicht nur ein, sondern für mehrere Suchkriterien entsprechende Treffer mit Hilfe einer SVERWEIS Funktion gesucht. Deshalb konfiguriert man für die oberste Zeile eine funktionierende SVERWEIS Funktion und kopiert diese für die anderen Suchkriterien. Dies geschieht meistens durch das hinunterziehen der Formel. Bei dieser Vorgehensweise verschiebt sich jedoch nicht nur das Suchkriterium, sondern auch die Matrix, in der gesucht wird. Anwender fragen sich dann, warum eine anfangs funktionierende Formel so viele Fehlermeldungen produziert. Glücklicherweise lässt sich das Problem schnell lösen. Die Matrix muss anfangs nur mit festen Bezügen (Dollarzeichen) versehen werden! Aus B2:G10 wird $B$2:$G$10, was Excel signalisiert, dass diese Matrix unverrückbar ist.

Leistungseinbrüche

Ein weiteres Problem mit dem SVERWEIS ist der große Hunger nach Rechenpower, weshalb selbst moderne Computer mit Excel Tabellen Probleme bekommen und das Arbeiten zu einem Graus machen. Abstürze inklusive. Warum ist das so? Angenommen eine Tabelle hat 1000 Zeilen mit Werten und für jede Zeile wird ein SVERWEIS angegeben der eine Zuordnung aus einem anderen Datensatz ermöglicht, die selbst auch aus mindestens 1000 besteht. Das bedeutet, dass 1000 mal in einem Bereich mit 1000 Feldern gesucht wird – und das nach jeder Eingabe in einer Tabelle. Je mehr Zeilen und SVERWEISE, desto mehr gerät der Rechner ins schwitzen. Deshalb empfiehlt es sich, nicht mehr benötigte SVERWEISE mit den festen Werten zu ersetzen. Dies ist sinnvoll, wenn sich die Datengrundlage und deshalb auch die Ergebnisse eines SVERWEISES nicht mehr ändert. Markiere hierfür die Spalte mit den SVERWEISEN und kopiere den Bereich. Klicke nun mit Rechtsklick auf den gewählten Bereich und gehe auf Werte einfügen (W). Jetzt sind die SVERWEISE mit statischen Zahlen ersetzt worden. Der Computer wird es dir danken!

Weitere Tutorials für Excel Formeln

1. SUMME Funktion
2. MIN Funktion
3. WENN Funktion
4. ANZAHL Funktion
5. SUMMEWENN Funktion
6. MITTELWERT Funktion
7. Formeln und Funktionen
8. ZUFALLSBEREICH Funktion
9. MAX Funktion
10. LINKS Funktion
11. ZUFALLSZAHL Funktion