- Die Beispieldatei für SVERWEIS - Christopher Olbertz
SVERWEIS und WVERWEIS sind zwei sehr nützliche Excel-Funktionen, deren Sinn sich jedoch nicht jedem Benutzer sofort erschließt. In diesem Artikel sollen diese beiden Funktionen an einem Beispiel vorgestellt werden. Den Aufbau des Beispiels sehen Sie in Bild 1.
Die Preise unserer Artikel sind in einer Preisliste festgehalten. Als Identifizierungsmerkmal dient die Artikelnummer. In der Bestellliste geben wir die Daten einer bestimmten Bestellung ein. Dabei sollen alle benötigten Informationen aus der Preisliste automatisch in die Bestellliste übernommen werden, sobald eine Artikelnummer eingegeben wird - also Artikelbezeichnung und Preis. Der Kunde erhält einen Rabatt, der sich aus dem zu bezahlenden Betrag ergibt. Und genau an dieser Stelle kommt der SVERWEIS ins Spiel.
Voraussetzungen
Sie sollten sich mit folgenden Themen auskennen:
- Formeln
- Funktionen
- Relative und absolute Zellbezüge
Die Anwendung der Funktion SVERWEIS im Beispiel
In unserem Beispiel wird die Funktion mehrmals eingesetzt. Sehen wir uns die Funktionen einmal in der Zeile 4 an. In Zelle A4 steht die Artikelnummer 4712. SVERWEIS soll nun ermitteln, welcher Artikel zu dieser Nummer gehört. In Zelle C4 soll sie den Preis des Artikels 4712 eintragen. In Zelle E4 wird der Gesamtpreis für den Artikel berechnet, indem die Anzahl mit dem Preis multipliziert wird. Das gleiche tun wir für die nächsten Artikel in den folgenden drei Zeilen. In Zelle E9 wird schließlich der Gesamtpreis für die ganze Bestellung berechnet.
Nun soll der Kunde einen Rabatt bekommen, der nach dem Wert der Bestellung ermittelt wird. In der Rabatttabelle ist festgehalten, ab welchem Betrag der Kunde wie viel Rabatt erhält. SVERWEIS soll in der Zelle B11 eintragen, wie viel Rabatt der Kunde erhält. Dieser wird anhand des Gesamtbetrags aus der Rabatttabelle bestimmt. Im Beispiel muss der Kunde 53.000 Euro bezahlen. Doch dieser Betrag erscheint nicht in der Rabatttabelle. In diesem Fall hat Excel zwei Möglichkeiten: Entweder darf der Prozentsatz des nächstniedrigeren Betrags genommen werden oder es erscheint ein Fehler. In unserem Beispiel darf Excel bei einem Betrag von 53.000 Euro auf 50.000 Euro abrunden und 5% in B11 eintragen.
Wie funktioniert die Funktion SVERWEIS?
SVERWEIS ordnet einen Wert Werten aus einer anderen Tabelle zu. In unserem Beispiel werden einer Artikelnummer, die wir in der Bestellliste eingeben, eine Artikelbezeichnung und ein Preis aus der Preisliste zugeordnet. In der Zelle B4 ermitteln wir den Artikelbezeichnung anhand der Artikelnummer. Die Syntax der Funktion SVERWEIS sieht folgendermaßen aus:
SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis). Die Funktion SVERWEIS erwartet vier Argumente:
- Suchkriterium: Der Wert, der in der Tabelle gesucht wird und dem entsprechende Werte zugeordnet werden.
- Matrix: Die Tabelle, in der das Suchkriterium gesucht wird.
- Spaltenindex: Die Spalte der Tabelle Matrix, in welcher die Daten stehen, die dem Suchkriterium zugeordnet werden.
- Bereich_Verweis: Gibt an, ob Excel auf den nächstniedrigeren Wert abrunden darf oder nicht.
Sehen wir uns einmal die Umsetzung in dem Beispiel an. Beginnen wir mit der Formel in Zelle B4: =SVERWEIS(A4;$H$4:$J$9;2, FALSCH). Die Argumente der Funktion müssen in der oben angegebenen Reihenfolge an die Funktion übergeben werden. Das bedeutet, dass in A4 das Suchkriterium steht. Die Matrix ist die Tabelle von H4:J9. Diese wird als absoluter Zellbezug angegeben, da sie immer an derselben Stelle im Tabellenblatt steht.
Wir möchten gerne als Ergebnis die Artikelbezeichnungen sehen. Da diese in der zweiten Spalte innerhalb der Matrix stehen, muss an dritter Stelle eine 2 eingetragen werden. Zu guter Letzt wird FALSCH übergeben, denn SVERWEIS muss die genaue Artikelnummer in der Matrix finden - ist sie nicht vorhanden, soll ein Fehler ausgegeben werden. Bei diesem Fehler wird in die Zelle A4 #NV eingetragen.
Die Formel in C4 sieht fast genauso aus: =SVERWEIS(A4;$H$4:$J$9;3; FALSCH). Das Suchkriterium ist dasselbe und steht somit auch in Zelle A4. Die Matrix ist ebenfalls dieselbe. Der einzige Unterschied ist der Spaltenindex. Da wir gerne den Preis sehen möchten und die Preise in der dritten Spalte stehen, wird als Spaltenindex 3 eingetragen. Nun können wir die Formeln auf die nächsten drei Zeilen übertragen.
In B11 finden wir diese Formel: =SVERWEIS(E9;$H$14:$I$18;2;WAHR). Hier möchten wir den Gesamtbetrag einem Rabatt zuordnen. Unser Suchkriterium, der Gesamtbetrag, befindet sich in E9. Nun wird als Matrix die Rabatttabelle angegeben und als Spaltenindex zwei. Der interessanteste Unterschied zu den obigen Formeln liegt im letzten Argument: Wir übergeben WAHR, denn Excel darf nun, wenn ein Betrag in der Rabatttabelle nicht gefunden wurde, den Rabatt des nächstniedrigeren Betrags nehmen. Das geschieht hier auch, denn in B11 steht als Ergebnis 5%, was der Rabatt von 50.000 Euro ist. Wenn Sie Bereich_Verweis auf WAHR setzen, muss die Matrix aufsteigend sortiert sein, denn Excel zeigt einfach den Wert in der vorherigen Zeile an.
Wie funktioniert WVERWEIS?
WVERWEIS arbeitet fast genauso wie SVERWEIS, nur dass statt einer senkrechten Tabelle eine waagerechte Tabelle als Matrix verwendet wird. Aus diesem Grund wird ein Zeilenindex und kein Spaltenindex erwartet. Auf dem zweiten Bild sehen Sie den Aufbau der Tabellen für die Funktion WVERWEIS. Sie sehen, dass die Tabellen nun waagerecht angeordnet sind. Bis auf diesen Unterschied funktionieren die beiden Funktionen gleich.
Christopher Olbertz - Ich heiße Christopher Olbertz und komme aus dem Saarland. Nach dem Abitur im Jahr 2000 und meinem Wehrdienst startete ich eine ...
