Excel-Beispiel für SVERWEIS: Provisionen berechnen

Die Excel-Funktion SVERWEIS ist ungemein hilfreich, um Daten, die in anderen Bereichen einer Tabelle oder auch auf einem anderen Tabellenblatt – genauer gesagt: in einer beliebigen Spalte- stehen, automatisch ermittelt werden können. Allerdings hat die Funktion auch ein paar Tücken, die mit dem richtigen Know-how vermieden werden können.

Das wird am besten an einem einfachen Beispiel verdeutlicht: Auf einem Tabellenblatt legen wir ab Spalte F einen kleine Provisionstabelle an, die einen Mindestumsatz sowie die zugehörige Prozentzahl der möglichen Provision zeigt. Anschließend wird der Bereich der Provisionen – hier F3 bis G10, siehe Bild – markiert. Über das Namensfeld vergeben wir den Namen “ProvTab” – damit werden später Eingaben in eine Formel erleichtert.

Vorhin wurde von einem Mindestumsatz gesprochen. Das bedeutet hier, dass etwa von 20.000 Euro bis 29.999 Euro die Provision von 5% gezahlt wird, von 30.000 bis 39.000 Euro sind es 6% usw. Das lässt sich einfacher in der Beispieltabelle erkennen, wenn die Zellen in Spalte F entsprechend mit einem benutzerdefinierten Zahlenformat formatiert werden, wie das folgende Bild zeigt:

Hier noch einmal das verwendete Zahlenformat für das Beispiel:

">="* #.##0" €"

Nun wird die Beispieltabelle ergänzt: In Spalte A kommen die Namen, in Spalte B die erzielten Umsätze. In Spalte C soll aus dem Bereich der Provisionstabelle mittels der Funktion SVERWEIS der zugehörige Provisionssatz ermittelt und eingetragen werden. In Spalte D wird dann durch einfache Berechnung die Provision in Euro angezeigt.

Für die SVERWEIS-Funktion wird im Beispiel folgende Formel in Zelle C3 eingetragen und dann einfach nach unten kopiert:

=SVERWEIS(B3;ProvTab;2;WAHR)

Der Aufbau der Funktion lautet allgemein:

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereichverweis)

• Suchkriterium: Das ist hier natürlich in Spalte B der eingegebene Umsatz.
• Matrix: Der Bereich, der nach dem Suchkriterium durchsucht werden soll. Im Beispiel handelt es sich um den Bereich, der den Namen “ProvTab” erhalten hat. Wenn Sie keinen Namen vergeben, können Sie natürlich auch die Zellbezeichnungen verwenden. Diese müssen mit absoluten Bezügen angegeben sein. Im Beispiel ließe sich die Matrix also auch so schreiben: $F$3:$G$10.
• Spaltenindex: Damit wird die Spalte der Matrix angegebenen, aus der die Funktion den zugehörigen Bereich ermitteln soll. Der Spaltenindex 1 wäre die Spalte mit dem Suchkriterium, Spaltenindex 2 ist der zugehörige Prozentsatz, der im Beispiel angezeigt werden soll-
• Bereichverweis: Dieser Parameter ist entweder WAHR oder FALSCH. Sie können ihn auch weglassen, dann stellt Excel automatisch WAHR ein. Und das kann für Fehler sorgen…

Mit der Einstellung WAHR sind nämlich zwei wichtige Vorbedingungen verknüpft:

1. Die Matrix, also die zu durchsuchende Liste, muss aufsteigend sortiert sein. Und:
2. Findet Excel das Suchkriterium nicht, wird automatisch die nächst kleinere Zahl aus der Liste als Kriterium verwendet und damit der dazu gehörende Prozentsatz.

Das bedeutet: Ist die Matrix nicht aufsteigend sortiert, wird mit WAHR vermutlich ein falscher Eintrag angezeigt. Das werden wir in einem späteren Bild noch zeigen – unsere Beispielmatrix ist hier aber aufsteigend sortiert.

Wenn Sie übrigens FALSCH verwenden, spielt die Sortierung der Matrix keine Rolle – allerdings sucht Excel dann nur nach exakten Übereinstimmungen des Suchkriteriums. Nur dann wird die Funktion ausgeführt. Zwischenwerte, die in der Matrix nicht stehen, kommentiert Excel dann einfach ebenfalls mit einer Fehlermeldung.

Zusammengefasst: Wir haben die Matrix aufsteigend sortiert, nutzen den Parameter WAHR – und wie das folgende Bild zeigt, sind überraschenderweise dennoch einige Fehlermeldungen zu sehen:

Die Formel für SVERWEIS ist völlig korrekt eingegeben – die Fehlermeldung #NV in den Zellen C4 und C10 muss also eine andere Ursache haben. Schauen Sie sich einmal die zugehörigen Umsatze, die in B4 und B10 eingetragen wurden an. Vergleichen Sie die mit der Matrix ProvTab, sollte der Fehler schnell klar sein: Umsätze unterhalb von 20.000 Euro sind in der Matrix nicht abgelegt – ein Fehler, der sich schnell einschleicht und eine der Tücken der SVERWEIS-Funktion zeigt. Die Matrix muss also ergänzt werden. Und dabei muss berücksichtigt werden, dass die Liste weiterhin aufsteigend sein muss. Unten einfach anfügen geht also nicht – das liegt daran, wie Sie sich erinnern werden, dass wir den Parameter WAHR verwendet haben.

Achtung: Denken Sie auch daran,dass die Matrix den bisherigen Namen “ProvTab” hat. Auch wenn Sie die Liste ergänzen, bleibt der Namensbereich so bestehen. Sie müssen als etwa über den Namens-Manager von Excel 2007 den bisherigen Namen löschen, die Liste neu markieren und ihr wieder den Namen “ProvTab” zuweisen!

So sieht die Tabelle anschließend ohne Fehler aus – dass in Spalte D in D4 und D10 keine Zahlen sichtbar sind, soll hier nicht weiter stören, dass liegt einer einfachen Multiplikationsformel:

Sie sehen, es gibt in der Tat einige Tücken in der Verwendung der Funktion SVERWEIS. Doch wer sie kennt, kann sie ganz einfach vermeiden oder beheben.

Wie schnell sich Fehler einschleichen können, die möglicherweise gar nicht auf den ersten Blick zu erkennen sind, weil keine Fehlermeldung zu sehen ist, zeigt das nächste Bild. Dabei haben wir die Matrix einfach durcheinander aufgelistet. Da ja nach wie vor der Parameter WAHR verwendet wird, führt SVERWEIS hier mitunter in die Irre:

Übrigens: Während SVERWEIS spaltenweise arbeitet, können Sie WVERWEIS entsprechend für zeilenweise Auswertungen nutzen.

Wer will, kann hier die Beispieldatei zu diesem Tipp herunterladen: Beispiel-Datei (1063)

Ähnliche Beiträge:

• Feste Werte variablen Zahlenbereichen in Excel richtig zuordnen
• Mit der Funktion WENNFEHLER() unerwünschte Fehlermeldungen in Excel 2007 vermeiden
• SVERWEIS-Funktion in OpenOffice Calc nutzen

Gilt für: Excel 2000, Excel 2002, Excel 2003, Excel 2007