In diesem Excel Tipp erfahren Sie, wie Sie den Spaltenindex beim SVERWEIS automatisch anpassen
Wenn wir im Controlling oder im Rechnungswesen mit Excel arbeiten, kommt es oft vor, dass wir eine größere Tabelle haben und hier Werte über den SVERWEIS ermitteln/anpassen.
Hin und wieder kommt es allerdings vor, dass eine oder mehrere Spalten gelöscht werden, wenn diese überflüssig sind und nicht mehr benötigt werden.
Das Problem dabei ist, dass wenn wir diese Tabelle verwenden, um per SVERWEIS gewisse Werte ermitteln möchten, der Spaltenindex nicht mehr korrekt ist, weil dieser sich nicht dynamisch verändert. Wir müssten diesen nun also manuell anpassen.
Bei lediglich einer Formel ist dies relativ schnell erledigt. Wenn wir allerdings wesentlich mehr Werte per SVERWEIS berechnen, kann dies sehr zeitaufwendig sein.
Doch dafür gibt es eine Lösung.
Sehen wir uns dazu einmal die folgende Tabelle mit folgenden Werten an.
Wenn wir jetzt die Stadt als Suche festlegen und dann die Personalkosten ermitteln möchten, wäre die klassische SVERWEIS-Formel, zum Beispiel wenn in der Zell B13 die Stadt steht und der Wert der Personalkosten in der Zelle C13 ausgeben werden soll:
=SVERWEIS(B13;B4:H8;6;0)
und wir würden den Wert 7.399 erhalten.
Nun passen wir die Formel wie folgt an.
Zunächst schreiben wir den gesuchten Begriff in die Zell C12 - hier "Personal" ohne die Anführungszeichen.
Lösung: SVERWEIS Spaltenindex automatisch berechnen
Statt den Spaltenindex jetzt als feste Nummer zu verwenden, benutzen wir die Funktion "VERGLEICH".
Unsere Excel-Formel in der Zelle C13 lautet nun:
=SVERWEIS(B13;B4:H8;VERGLEICH(C12;C3:H3;0)+1;0)
Wie hier zu sehen ist, wird beim VERGLEICH erst der Suchbegriff "Personal" festgelegt.
Im Anschluss wird dieser nur aus der Überschriftsreihe (hier C3 bis H3) gezogen.
Danach wird diese noch um plus 1 addiert.
Wenn jetzt ein oder mehrere Spalten gelöscht werden, verändert sich der Wert nicht mehr bzw. muss die Excel-Formel nicht nochmal manuell angepasst werden, da der Spaltenindex dynamisch gesucht wird.
Diese Excel-Formel können Sie nun natürlich beliebig anpassen bzw. bei sehr großen Tabellen und Positionen verwenden.
Ich wünsche Ihnen viel Spaß beim Ausprobieren.
zum Beispiel: SVERWEIS nach links
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.