Die Excel-Funktion SVERWEIS mit variabler Tabellenangabe

Die Funktion SVERWEIS wird in der Regel verwendet, um in einer Tabelle in der ersten Spalte nach einem Ausdruck zu suchen und einen Wert aus einer anderen Spalte der Tabelle abzurufen. Die Tabelle, in der gesucht wird, ist meistens fest vorgegeben. In diesem Tipp zeigen wir Ihnen, wie der Zellbereich, in dem gesucht wird, variabel gehalten werden kann.

In dem Beispiel soll für diverse Kunden der Rabatt ermittelt werden, den sie auf aktuelle Bestellungen erhalten. Der Rabatt ist abhängig von der Einordnung des Kunden in eine Kundengruppe und den Abnahmemengen des Vorjahres. Für jede Kundengruppe gibt es eine eigene Rabattstaffel mit unterschiedlichen Mindestabnahmemengen und Rabattsätzen. Das Tabellenblatt mit der Rabattberechnung sieht folgendermaßen aus:

Rabattberechnung mit SVerweis

Es gibt zwei Kundengruppen A und B. Die Rabattstaffel für Kunden der Kundengruppe A befindet sich im rot umrandeten Zellbereich F3:G6. Der Zellbereich hat den Namen Kundengruppe_A. Der grün umrandete Zellbereich F10:G13 hat den Namen Kundengruppe_B und enthält die Rabattkonditionen für die zweite Kundengruppe B. Für jeden Kunden soll nun im Zellbereich D2:D5 der ihm zustehende Rabatt aufgrund der Abnahmemengen des Vorjahres und der zugeordneten Kundengruppe ermittelt werden. Die Formel ist eine Kombination der Funktion SVERWEIS und INDIREKT. Die Formel für Kunde 1 lautet folgendermaßen:

=SVERWEIS(C2;INDIREKT(“Kundengruppe_”&B2);2;WAHR)

Die Funktion INDIREKT stellt den Schlüssel für die variable Angabe der Tabelle dar, in der nach dem Rabattsatz gesucht wird. Mit der Funktion INDIREKT können Sie einen Bezug an die Funktion SVERWEIS geben, der als Text angegeben werden kann. In diesem Fall wird die Tabelle zusammengesetzt aus dem Begriff Kundengruppe_ und der Kundengruppe, die in der Spalte B der gleichen Zeile steht.
Entscheidend für die variable Tabellenangabe in der Funktion SVERWEIS ist die Benennung der beiden Zellbereiche mit den Rabattstaffeln nach dem gleichen Muster, so dass mit der Funktion INDIREKT der Tabellenname zusammengesetzt werden kann.

Wenn Sie die Formel in Zelle D2 für die anderen Kunden nach unten kopieren, sieht das Tabellenblatt folgendermaßen aus:

Rabattberechnung mit SVerweis

Hinweise

  • Die Formel kann unverändert bleiben, wenn Sie statt zwei noch mehrere Kundengruppen mit unterschiedlichen Rabattstaffeln nutzen möchten. Sie brauchen nur die die weiteren Zellbereiche nach dem gleichen Muster benennen. Der Einsatz der INDIREKT-Funktion ist übersichtlicher als der alternative Einsatz ineinander geschachtelter WENN-Funktionen.
  • Wenn die Tabelle in der Formel mit dem SVERWEIS nicht gefunden wird, erscheint in der Ergebniszelle ein Fehler vom Typ #BEZUG!.
  • Die folgende Formel für Kunde 1 ohne die Funktion INDIREKT erzeugt einen Fehler vom Typ #WERT!:
    =SVERWEIS(C2;”Kundengruppe_”&B2;2;WAHR)
Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

2 Kommentare

  1. Hallo, ich finde Ihre Erklärung super, leider bekomme ich keine Lösung.

    Vllt können Sie mir ja einen Tipp geben?

    Ich habe folgende Formel:
    SVERWEIS(A3;’O:\allgemein\Master\[Master – Stammdaten.xlsx]Stamm-Daten Produktion’!$A$1:$CA$2116;3;FALSCH
    beim Versenden via Mail, ändert er beim Öffnenden Laufwerk O auf C ab.

    Jetzt habe ich folgendes geschrieben:
    SVERWEIS(A4;INDIREKT(AC1&”$A$1:$CA$2116″);3;FALSCH
    in Zeile AC1 steht folgendes
    ‘O:\allgemein\Master\[Master – Stammdaten.xlsx]Stamm-Daten Produktion’!
    leider bekomme ich nur einen #BEZUG und weis nicht warum.

    Vielen Dank schon einmal.

    Gruß

    Antworten
    • Hallo Herr Detlefsen,

      der Bezugsfehler erscheint u .a., wenn die Arbeitsmappe Master – Stammdaten.xlsx vorher nicht geöffnet worden ist. Zur Sicherheit geben Sie in der Zelle AC1 zu Beginn zwei Hochkommata ein, so dass dort ”O:\allgemein\Master\[Master – Stammdaten.xlsx]Stamm-Daten Produktion’! erscheint.

      Schöne Grüße

      Michael Richter

      Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Pin It on Pinterest

Share This