Automatische Umstufung von Beiträgen / 3 Beitragseinstufung über SVERWEIS

Gibt es zu viele Beitragsstufen, kann auch mit einem SVERWEIS() gearbeitet werden. Die Lösung ist zwar nicht so "elegant", dafür aber einfacher im Handling. In diesem Zusammenhang ist auf Folgendes zu achten:

Man benötigt zwingend eine Tabelle, in dem jedem Lebensjahr separat ein Beitrag zugeordnet wird. Diese bildet quasi die Grundlage für die Arbeit mit der Funktion SVERWEIS. Die Tabelle legt man vorzugsweise in einem separaten Arbeitsblatt an.

Abb. 7: Ausschnitt aus einer Beitragstabelle, in der jedes Lebensjahr gesondert aufgeführt wird

Hinweis

Das "S" in SVERWEIS() steht stellvertretend für "senkrecht".

Die Vorbereitungsarbeiten sind vergleichbar mit der Vorgehensweise bei der WENN-Funktion. Man benötigt entsprechend das Alter des Mitglieds. Dazu geht man analog vor, wie bereits erläutert. Der Beitrag wird den einzelnen Mitgliedern jedoch nicht mithilfe einer WENN-Funktion, sondern über den SVERWEIS zugewiesen.

1. Man setzt die Eingabemarkierung in die Zelle, in der das Ergebnis erscheinen soll, aktiviert das Register Formeln und klickt dort im Bereich Funktionsbibliothek auf die Schaltfläche Nachschlagen und Verweisen.

2. In der folgenden Auswahl entscheidet man sich für den Eintrag SVERWEIS. Auch hier öffnet Excel das Fenster Funktionsargumente. Die Funktion arbeitet mit den Argumenten Suchkriterium, Matrix, Spaltenindex und Bereich_Verweis. Dort sind folgende Angaben notwendig:

   • Das Suchkriterium entspricht dem Alter.
   • Matrix entspricht dem Bereich mit den Beitragsinformationen. Dazu wechselt man in die Tabelle, in der den einzelnen Lebensjahren die Beiträge zugeordnet wurden und markiert die komplette Tabelle. Für das Beispiel ist das der Zellbereich D17:E118. Damit sich die Formel problemlos kopieren lässt, arbeitet man auch hier mit absoluten Zellbezügen (=$D$17:$E$118).
   • Der Beitrag befindet sich im Beispiel in der zweiten Spalte der Matrix. Entsprechend ist als Spaltenindex die Ziffer 2 einzutragen.

   • Bereich_Verweis wird für das aktuelle Beispiel nicht benötigt.

     Hinweis

     Die Angabe des Arguments Bereich_Verweis ist optional. Darüber wird geregelt, ob SVERWEIS() ein exaktes Gegenstück oder eine ungefähre Entsprechung suchen soll. Fehlt die Angabe oder ist Bereich_Verweis WAHR, liefert Excel möglicherweise kein genaues Ergebnis. Insbesondere im Zusammenhang mit unsortierten Listen kann dies zu falschen Resultaten führen.

     Abb. 8: Hier teilen Sie Excel mit, was, wo zu suchen ist.

3. Die Eingabe ist wahlweise durch einen Klick auf die Schaltfläche OK, alternativ durch das Drücken der Enter-Taste zu bestätigen. Die vollständige Formel lautet:
4. =SVERWEIS(G11;Beitragstabelle!$D$17:$E$118;2)
5. Die Formel kann, ebenso wie die Formel zur Ermittlung des Alters, in die nachfolgenden Zeilen kopiert werden.

Hinweis

Die Musterlösung der im Rahmen dieses Beitrags besprochenen Beispiele finden Sie im Excel-Tool "Beitragsumstufung".

Abb. 9: Beitragszuordnung über SVERWEIS