In diesem Excel-Tipp erfahren Sie, wie Sie ein SVERWEIS nach unten erstellen können
Allgemein
Wenn wir mit Excel-Tabellen arbeiten, setzen wir oftmals die Formel SVERWEIS ein.
Ein einfaches Beispiel zur Wiederholung:
Im unteren Bild sehen wir eine Tabelle mit mehreren Warengruppen und den dazugehörigen Umsatz und Rohgewinn.
Als nächstes, erstellen wir in der Zelle G3 ein Drop-Down-Menü und schreiben in die Zelle F3 die Bezeichnung Warengruppe. Formatieren Sie das Wort "Fett"
Um ein Drop-Down-Menü zu erstellen, gehen Sie in die Zelle G3 und dann auf die Registerkarte "Daten" und wählen Sie hier den Button "Datenüberprüfung":
Das Fenster "Datenüberprüfung" öffnet sich.
Wählen Sie hier Liste bei "Zulassen:" und klicken Sie dann anschließen auf den Button mit dem Pfeil, der nach oben zeigt.
Das Fenster verkleinert sich.
Markieren Sie jetzt mit der linken Maustaste die Warengruppen aus den Zellen B4 bis B8 und klicken anschließend auf Enter und dann auf "OK".
Nun haben Sie die Möglichkeit, eine Warengruppe aus der Zelle G3 aus dem Drop-Down-Menü zu erstellen.
Schreiben Sie nun in die Zelle H2 die Bezeichnung "Rohgewinn" und formatieren Sie die Bezeichnung ebenfalls "Fett".
Gehen Sie nun in die Zelle H3 und schreiben Sie hier folgende Formel:
=SVERWEIS(G3;B4:D8;3;0)
Somit erhalten Sie den Wert aus der Tabelle, wie Sie im unteren Bild sehen können.
Der SVERWEIS liefert hier das Suchkriterium aus der Zelle G3 und den dazugehörigen Wert aus der Matrix von den Zellen B4 bis D8. Der Spaltenindex 3 ist die dritte Spalte aus der benannten Tabelle (Matrix).
Wenn nun die Warengruppe aus der Zelle G3 verändert wird, ändert sich auch logischerweise der Wert vom Rohgewinn.
SVERWEIS nach unten
Nun kommt es allerdings vor, dass die Warengruppen nicht immer untereinander stehen, sondern wir Tabellen von anderen Kollegen und/oder Vorgesetzten erhalten und aus dieser Tabelle die Werte ermitteln müssen.
Die vorige Tabelle würde dann so aussehen:
In diesem Fall müssen wir zunächst einmal das Drop-Down-Menü umbauen.
Dies machen wir in diesem Beispiel in den Zellen J2 bis L3.
Das Drop-Down-Menü bzw. die Quelle hierfür können Sie auch horizontal statt vertikal anwenden.
In die Zelle L3 schreiben wir folgende Formel
=WVERWEIS(K3;C3:G5;3;0)
Die Formel WVERWEIS ist quasi der SVERWEIS nach unten.
Damit Sie hier allerdings nicht ständig die Zeilenhöhe hier "3" manuell eintragen müssen, können Sie in der Spalte H eine Hilfe einbauen.
Schreiben Sie in die Zelle H3 "Hilfe" und da drunter die Werte 2 bis 3
Jetzt haben wir den Vorteil, dass wir mit der Bezeichnung "Rohgewinn" aus der Zelle L2 die WVERWEIS-Formel mit einem SVERWEIS ergänzen können.
Diese lautet dann:
=WVERWEIS(K3;C3:G5;SVERWEIS(L2;B4:H5;7;0);0)
Wenn wir nun die Zelle L2 in Umsatz ändern, zum Beispiel auch über ein Drop-Down-Menü, erhalten wir für die Warengruppe T-Shirts den dazugehörigen Umsatz.
Jetzt haben wir einen "SVERWEIS" nach unten erstellt, in dem die Zeilenhöhe sogar dynamisch ist.
Ich wünsche Ihnen viel Spaß beim Ausprobieren. Wenn Sie möchten, können Sie die dazugehörige Excel-Datei herunterladen.
Autor
Kristoffer Ditz ist Leiter der Hanseatic Business School in Hamburg und freiberuflicher Dozent an der AMD Akademie Mode und Design / Hochschule Fresenius.
Er verfügt über mehrere Jahre Berufserfahrung u.a. bei der Puma AG, Ludwig Beck AG, Dänisches Bettenlager und der Hamburger Drogeriemarktkette Iwan Budnikowsky.
Für das Controlling Journal und Controller Magazin schreibt er regelmäßig Fachartikel zu den Themen Controlling und Microsoft Excel.