Excel-Matrixformel: Die Funktion SVERWEIS bzw. WVERWEIS

DARUM GEHT’S

Sie möchten für die Vertriebsmitarbeiter Ihres Hauses eine Provisionsabrechnung erstellen. Für die verschiedenen Produktgruppen, die vertrieben werden, gelten unterschiedliche Provisionssätze, die Sie den entsprechenden Umsätzen zuordnen müssen. Sie benötigen also eine Funktion, die in einer Liste nachschlagen kann, welcher Provisionssatz jeweils anzuwenden ist. Sie lernen in diesem Lernobjekt die Excel-Matrixformel SVERWEIS kennen, mit der Sie diese Aufgabe erledigen können. Die analog zu handhabende Funktion WVERWEIS, die in der Praxis wesentlich seltener verwendet wird, wird zusätzlich beschrieben.

Matrixformel

SO WIRD’S GEMACHT

Die Abbildung zeigt im linken Bereich die eigentliche Abrechnung. Im rechten Teil ist die Matrix mit den Provisionssätzen zu erkennen. Der jeweils zutreffende Provisionssatz für jede Produktgruppe soll in Spalte C eingetragen werden. Damit die Funktion SVERWEIS verwendet werden kann, müssen Kriterien und eine Matrix, in der diese Kriterien in einer Spalte aufgelistet sind, vorhanden sein.

Matrixformel

In diesem Beispiel ist das Suchkriterium die Bezeichnung der Produktgruppe. Die Excel-Matrixformel SVERWEIS arbeitet nun wie folgt:

1.    Gehe mit dem Suchkriterium in die erste Spalte der angegebenen Matrix und suche nach diesem Kriterium. Wichtig: Der SVERWEIS sucht immer in der ersten Spalte.
2.    Liefere aus der angegebenen Spalte der gleichen Zeile einen Wert zurück. Die Spalte ist eine Zahl, welche die relative Spalte innerhalb der Matrix kennzeichnet. Im Beispiel besteht die Matrix aus zwei Spalten. Der Wert, welcher zurückgeliefert werden soll, der Provisionssatz, steht in Spalte 2.

Matrixformel

Die Funktion SVERWEIS benötigt vier Argumente:

Das Suchkriterium, in diesem Beispiel die Produktgruppe “Fleischprodukte”, für die die Provision berechnet werden soll.
Die Matrix, also den Zellbereich mit den Provisionssätzen und den Produktgruppen.
Die Spalte der Matrix, welche einen Wert zurückliefern soll.
Die Angabe, ob eine exakte Suche erwünscht ist (in diesem Fall ist eine 0 einzutragen, sonst ein beliebiger anderer Wert). Diese Eingabe ist optional. Der SVWERWEIS ist nicht nur in der Lage ein Suchkriterium exakt zu finden, sondern kann auch einen Wert in eine Gruppe einordnen, wie das folgende Beispiel zeigt:(

Matrixformel

ACHTUNG

Falls Sie Wert auf eine exakte Suche legen, dürfen Sie keinesfalls vergessen, beim letzten Argument eine 0 (Null) einzutragen; die Ergebnisse könnten ansonsten sehr unzuverlässig sein.

ACHTUNG

Wird bei exakter Suche kein dem Suchkriterium entsprechender Wert in der Matrix gefunden, liefert die Funktion den Fehlerstatus “#NV”.

Um die Funktion SVERWEIS und WVERWEIS gut zu unterscheiden, kann der erste Buchstabe dienen:

S: Die Funktion sucht senkrecht in der ersten Spalte
W: Die Funktion sucht waagerecht in der ersten Zeile

Mit anderen Worten: Der WVERWEIS arbeitet um 90° gedreht. Gesucht wird in der ersten Zeile, und es wird dann ein Wert aus der im dritten Argument angegebenen Zeile zurückgegeben.
In der Praxis ist überwiegend der SVERWEIS zu finden, da seine senkrechte Ausrichtung eher mit dem vertikalen Aufbau der meisten Arbeitsblätter in Einklang gebracht werden kann.

ACHTUNG

Dieses Beispiel dient nur zur Demonstration. In der Praxis wird sich die Matrix der Provisionssätze, welche ja für die Abrechnungen aller Vertriebsmitarbeiter gilt, nicht auf dem gleichen Blatt befinden wie die Abrechnung selbst. Vielmehr wird sie auf einem anderen Blatt der gleichen Arbeitsmappe oder sogar in einer anderen Arbeitsmappe gespeichert sein. Mit der Funktion SVERWEIS können Sie problemlos auf eine Matrix zugreifen, welche außerhalb des aktuellen Blatts gespeichert ist. Sie müssen die Arbeitsmappe mit dieser Matrix lediglich geöffnet haben, wenn Sie die Funktion SVERWEIS mit Argumenten versehen. Der spätere Zugriff erfolgt im Hintergrund, ohne dass die betreffende Arbeitsmappe von Ihnen geöffnet werden muss.

SCHRITT FÜR SCHRITT

Berechnen Sie nun den zutreffenden Provisionssatz mit der Excel-Matrixformel SVERWEIS. Damit Sie die folgenden Schritte nachvollziehen können, öffnen Sie bitte die Datei provision_3_5_7_1.xlsx.

1.    Markieren Sie die Zelle C4.
2.    Starten Sie den Funktionsassistenten und wählen Sie aus der Kategorie Matrix die Funktion SVERWEIS.
3.    Wählen Sie als Kriterium die Zelle A4.
4.    Für das Argument Matrix markieren Sie den Bereich F4:G11. Da Sie die Funktion für die anderen Produktgruppen kopieren wollen, drücken Sie die Taste [F4], um einen absoluten Bezug zu erzeugen.
5.    Für das Argument Spaltenindex geben Sie eine 2 ein, da die Funktion den Wert aus der 2. Spalte der Matrix liefern soll.
6.    Für Bereich_Verweis geben Sie eine 0 (Null) ein, um eine exakte Suche zu gewährleisten.
7.    Klicken Sie auf OK, um den Vorgang abzuschließen.
8.    Formatieren Sie die Zelle mit dem %-Format und kopieren Sie die Funktion nach unten bis zur Zelle C11.
9.    Errechnen Sie in Spalte D die Provisionsbeträge.

Ihr Ergebnis sollte nun so aussehen.

Matrixformel

Wenn Sie Ihr Ergebnis mit der Musterlösung vergleichen möchten, öffnen Sie hierzu die Datei provision_3_5_7_1_e.xlsx.

UND JETZT SIND SIE DRAN!

Öffnen Sie bitte die Datei rangfolge_3_5_7_1_u.xlsx. Ordnen Sie die Verkäufer entsprechend ihrem Umsatz in eine Kategorie ein. Vergleichen Sie das Ergebnis mit der Datei rangfolge_3_5_7_1_ue.xlsx.

Matrixformel

Dieser Artikel gefällt Ihnen?

Dann teilen Sie ihn mit Freunden oder erhalten Sie mit unserem Newsletter die neuesten Infos.

Ihr Kommentar zu diesem Artikel

Connect with Facebook

*