Excel-Tutorial 5: SVERWEIS und WVERWEIS

Voraussetzungen

Einfache Formeln

Funktionen

Relative und absolute Zellbezüge

Beschreibung des Beispiels

Im folgenden Bild sehen Sie den Aufbau des Beispiels.

In einer Liste sind die Artikel und deren Preise festgehalten. Identifiziert werden die Artikel über die Artikelnummer. Zusätzlich gibt es eine Bestellliste, in welcher die Daten einer Bestellung eingegeben werden. Alle benötigten Informationen sollen automatisch aus der Preisliste ermittelt werden, sobald eine Artikelnummer in die Bestellung eingegeben wird. Der Kunde erhält einen Rabatt, der sich aus dem zu zahlenden Betrag ergibt.

SVERWEIS im Beispiel

Versuchen Sie anhand der folgenden Beschreibung bitte, die Funktionsweise des Beispiels nachzuvollziehen.

Im Beispiel wird die Funktion SVERWEIS mehrmals eingesetzt. Beginnen wir in Zeile 4. In Zelle A4 sehen Sie die Artikelnummer 4712. Es soll nun ermittelt werden, welcher Artikel zu dieser Nummer gehört. Der Preis des Artikels 4712 soll in die Zelle C4 eingetragen werden. In Zelle E4 wird der Gesamtpreis für den Artikel in der Bestellung berechnet, in dem die Anzahl mit dem Preis multipliziert wird. Das gleiche geschieht für die nächsten Artikel. In Zelle E9 wird der Gesamtpreis berechnet.

Der Kundenrabatt wird nach dem Wert der Bestellung ermittelt. In der Rabatttabelle steht, ab welchem Betrag der Kunde wieviel Rabatt erhält. In der Zelle B11 soll eingetragen werden, wie viel Rabatt der Kunde erhält. Im Beispiel beträgt der Rechnungsbetrag 53.000 Euro. Sie sehen, dass dieser Betrag nicht in der Rabatttabelle erscheint. Es gibt nun zwei Möglichkeiten: entweder wird der Prozentsatz des nächstniedrigen Betrags genommen oder es erscheint ein Fehler. In diesem Beispiel wäre ein Fehler nicht sinnvoll. Stattdessen wird der Rabatt von 50.000 Euro eingetragen, also 5 %.

Können Sie das Beispiel nachvollziehen? Dann schauen wir uns mal an, wie die beiden Funktionen eingesetzt werden.

Die Funktionsweise von SVERWEIS

SVERWEIS ordnet einen Wert den Werten aus einer anderen Tabelle zu. Im Beispiel wird eine Artikelnummer in der Bestellung einer Artikelbezeichnung und einem Preis aus der Preisliste zugeordnet. In Zelle B4 wird die Funktion SVERWEIS verwendet, um die Artikelbezeichnung anhand der Artikelnummer zu ermitteln.

Sehen wir uns zuerst die Syntax von SVERWEIS an.

SVERWEIS(Suchkriterium; Matrix; Spaltenindex; Bereich_Verweis). SVERWEIS erwartet also vier Argumente:

• Suchkriterium: Der Wert, der in der Tabelle gesucht wird. Ihm werden Werte aus der anderen Tabelle zugeordnet.
• Matrix: Die Tabelle, in der das Suchkriterium gesucht wird. Hier stehen die Werte, die dem Suchkriterium zugeordnet werden.
• Spaltenindex: Die Spalte in der Tabelle Matrix, in der die Daten stehen, die dem Suchkriterium entsprechen.
• Bereich_Verweis gibt an, ob Excel auf den nächstniedrigen Wert abrunden darf oder nicht.

Sie verstehen diese Argumente besser, wenn Sie sich die Umsetzung im Beispiel ansehen: In B4 steht die folgende Formel: =SVERWEIS(A4; $H$4:$J$9;2; FALSCH). In A4 steht also das Suchkriterium. Die Matrix ist die Tabelle von H4:J9. Angegeben wird sie als absoluter Zellbezug, da sie immer an derselben Stelle im Tabellenblatt steht.

Die Artikelbezeichnungen stehen an zweiter Stelle in der Matrix. Da wir die Bezeichnungen sehen möchten, müssen wir als drittes Argument 2 übergeben. SVERWEIS muss die genaue Artikelnummer finden und darf nicht irgendwie abrunden. Wir möchten einen Fehler, wenn die Artikelnummer nicht existiert. Daher wird als viertes Argument FALSCH angegeben.

In C4 steht fast die gleiche Formel: =SVERWEIS(A4; $H$4:$J$9;3; FALSCH). Der einzige Unterschied ist der Spaltenindex, da wir den Preis sehen möchten und dieser in der dritten Spalte der Matrix steht. Nun können Sie die beiden Formeln auf die nächsten Zeilen übertragen.

In B11 sehen Sie die folgende Formel: =SVERWEIS(E9; $H$14:$i$18;2; WAHR). Der Gesamtbetrag soll einem Rabatt zugeordnet werden. Unser Suchkriterium ist der Gesamtbetrag in E9. Die Matrix ist die Rabatttabelle und der Spaltenindex ist 2. Interessant ist das letzte Argument, denn hier ist es WAHR. Das bedeutet, dass Excel den Rabatt des nächstniedrigeren Betrag verwenden darf, wenn der genaue Betrag nicht gefunden wurde. Wenn Bereich_Verweis auf WAHR gesetzt wird, muss die Tabelle aufsteigend sortiert sein. Bei FALSCH ist dies natürlich nicht notwendig.

Die Funktionsweise von WVERWEIS

Die beiden Funktionen funktionieren fast gleich. Der einzige Unterschied ist, dass WVERWEIS statt einer senkrechten Tabelle eine waagerechte Tabelle als Matrix verwendet. Sie müssen dementsprechend einen Zeilenindex statt eines Spaltenindex angeben. Auf dem nächsten Bild sehen Sie die Tabellen für die Funktion WVERWEIS. Sonst sind die beiden Funktionen identisch.

Excel-Tutorial Teil 4: Die Verwendung von Funktionen

Fortsetzung folgt!