Die Funktion SVERWEIS "durchsucht die erste Spalte einer Matrix und durchläuft die Zeile nach rechts, um den Wert einer Zeile zurückzugeben" (aus der Excel Online-Hilfe). Dies soll am Beispiel der Vertreterumsätze deutlich gemacht werden.

SVERWEIS - Hilfedialog

Die folgende Tabelle enthält die Basisdaten der Vertreterumsätze im Monat Januar. Direkt daneben, ist eine Provisionstabelle abgebildet, die Auskunft darüber gibt, wie viel Provision ein Vertreter bei dem gemachten Umsatz bekommt.

Beispiel Vertreterumsatz

Mit Hilfe der Funktion SVERWEIS lässt sich nun in Zelle C4 ermitteln welchen Provisionssatz (Provision in %) ein Vertreter bekommt. Der SVERWEIS erwartet als ersten Parameter den Wert, der in der ersten Spalte Provisionstabelle gesucht wird.

Aufbau der Funktion SVERWEIS

Der SVERWEIS durchsucht immer die erste Spalte der "MATRIX" von oben nach unten.

Der Parameter Matrix im SVERWEIS

Achten Sie beim angeben des Bereichs für die Matrix darauf, dass nur die Daten selbst (ohne die Überschrift) angegeben werden!

Erste Spalte ist entscheidend beim SVERWEIS

Die Funktion SVERWEIS durchsucht immer die erste Spalte der angegebenen Matrix von oben nach unten, bis der gewünschte Wert (der durch den ersten Parameter angegeben wurde) gefunden ist.

Funktionsweise der Funktion SVERWEIS

Ist der entsprechende Eintrag ausgemacht, kommt die Zeile ins Spiel, in der der Wert steht.

Funktionsweise des SVERWEIS

Der dritte Parameter vom SVERWEIS gibt an, welche der Spalten (aus der angegebenen Matrix) zurückgegeben werden soll. Hierbei heißt eine "1", dass der gefundene Wert (hier 150000) zurückgegeben werden soll, eine "2" würde bedeuten, dass die 7% zurückgegeben werden soll.

Funktionsweise der Funktion SVERWEIS

Bei einigen Firmen gibt es Provisionstabellen, die nach Regionen bzw. Bereichen gestaffelt sind. In der obigen Abbildung z. B. den Bereich Nord und den Bereich Süd. Wenn der Umsatz im Januar von Vertretern aus dem Bereich Süd erwirtschaftet wurde, so kann man die Matrix des SVERWEISes auch auf die komplette Provisionstabelle (hier drei Spalten) erweitern und als dritten Parameter statt der "2" eine "3" nutzen. Damit sagen Sie dem SVERWEIS, er soll die dritte Spalte der Matrix zurückgeben.

Zu beachten ist hier nur, dass der Bereich der Matrix (zweiter Parameter) auch den kompletten Bereich (in der Abbildung gelb hinterlegt) umfassen muss.

Aber nun wieder zurück zum Beispiel mit nur einer Region und nur einer Provisionsspalte.

Funktionsweise der Funktion SVERWEIS

Der SVERWEIS kann auch noch einen vierten Parameter haben. Der vierte Parameter kann ausschließlich die Werte "Wahr" oder "Falsch" annehmen. Wird der vierte Parameter nicht gesetzt, so wird Standardmäßig "Wahr" angenommen.

Bei "Wahr" (oder wenn der vierte Parameter weggelassen wird) muss die erst Spalte zwingend aufsteigend sortiert sein (a-z bzw. 0-9), anderenfalls kann es zu falschen Ergebnissen kommen.

Sie sehen, dass die erste Spalte der Provisionstabelle aufsteigend von 0 bis 500000 sortiert ist.

Funktionsweise der Funktion SVERWEIS

Wenn Sie die 151000 suchen, durchläuft der SVERWEIS die erste Spalte und entscheidet bei jedem Eintrag, ob der größer, gleich oder kleiner ist als gesuchte Wert. Ist der Wert gleich, hat er die Zeile gefunden, ist der aktuelle Wert kleiner also der gesucht, muss er weiter suchen, ist der aktuelle Wert größer, ist er über das Ziel hinaus geschossen und kehrt zur vorherigen Zeile zurück. Damit ist die Zeile dann auch Dingfest gemacht.

Funktionsweise der Funktion SVERWEIS

Das ist die selbe Vorgehensweise, die Ihnen auch das "Bauchgefühl" vorschreiben würde.

Funktionsweise der Funktion SVERWEIS

Bei manchen Listen ist dieses Vorgehen nicht erwünscht. Es muss der exakte Wert in der Liste stehen, sonst kann es zu Problemen führen. Für diese Fälle wird der letzte (vierte) Parameter mit "Falsch" angegeben. Für diesen Fall werden wir ein Beispiel im Unterricht durchgehen.

Funktionsweise der Funktion SVERWEIS

Sollte der exakte Wert dann nicht in der Liste stehen, so wird als Ergebnis des SVERWEIS die Fehlermeldung "#NV" für "Nicht vorhanden" bzw. "No Value" ausgegeben.

Funktionsweise der Funktion SVERWEIS

Lösen wir nun den SVERWEIS auf und erstellen die Formel:

Funktionsweise der Funktion SVERWEIS

=SVERWEIS(B4;

Funktionsweise der Funktion SVERWEIS

=SVERWEIS(B4;E5:F15;

Funktionsweise der Funktion SVERWEIS

=SVERWEIS(B4;E5:F15;2

Funktionsweise der Funktion SVERWEIS

=SVERWEIS(B4;E5:F15;2)

Funktionsweise der Funktion SVERWEIS

Das Ergebnis ist hier noch mit 0,07 angegeben. Beachten Sie, dass 7% gleich 7 geteilt durch 100 sind. Und 7/100 = 0,07 ergeben. Um aus der 0,07 eine 7% zu machen, klicken Sie in der Format-Symbolleiste einmal auf das Prozentformat.

Fertig!

ENDE DES WORKSHOPS

Weitere Informationen zum SVERWEIS aus dem Internet: