Kleine Tipps und Tricks in MS Excel: SVERWEIS

Diese Formel sucht nach einem bestimmten Wert (Suchkriterium) innerhalb der ersten Spalte einer Matrix (eine Matrix definiert einen bestimmten Bereich mit mehreren Spalten und Zeilen, z. B. eine Tabelle) und gibt den Wert einer beliebigen Spalte der gleichen Zeile zurück. Dabei werden von links nach rechts die Spalten innerhalb der Matrix gezählt.

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;[Bereich_Verweis])

Häufig kommt es bei der Nutzung des SVERWEIS vor, dass nicht nur ein Wert einer Spalte relevant ist, sondern Werte mehrerer Spalten nebeneinander gesucht werden. Normalerweise kann eine Formel dynamisch verwendet werden. Beim Ziehen der Formel in die nächste Zelle, verändern sich die Werte innerhalb der Formel dynamisch mit.

Beispiel: Beim Ziehen der SUMMEN-Formel nach rechts, gehen die Werte innerhalb der Formel dynamisch auch ein Feld weiter.

Dies betrifft allerdings im Fall des SVERWEIS den Spaltenindex nicht, da dieser nur als Zahl angegeben wird. Um trotzdem die Formel dynamisch zu verwenden, gibt es mehrere Möglichkeiten. Eine Möglichkeit möchte ich im nächsten Schritt vorstellen.

Dynamische Anwendung der Formel

Dabei verwende ich die Formel SPALTE innerhalb des SVERWEIS. Diese Formel gibt die Spalte einer Zelle als Zahl wieder. Beispielsweise würde die Formel:

=SPALTE(C3)=3

ergeben. Das liegt daran, dass die Spalte C, die dritte Spalte in einem Excel-Arbeitsblatt darstellt. Somit kann die Formel einfach verwendet werden, sobald die Matrix in der Spalte A beginnt. Die Formel würde dann wie folgt aussehen:

=SVERWEIS(Suchkriterium;Matrix;SPALTE(X);[Bereich_Verweis])

Das X kann eine x-beliebige Zelle innerhalb der Spalte darstellen, aus der der Wert gesucht wird. Beispielsweise kann dies die Zelle mit der Überschrift der Spalte sein.

Es kann natürlich auch vorkommen, dass die Matrix nicht in der Spalte A beginnt. Dies würde bedeuten, dass durch die Verschiebung der Spalte 1 der Matrix nach links, unsere Formel einen Spaltenindex wiedergeben würde, der auch um die Größe der Verschiebung vergrößert wäre.

Also müssen wir einen zusätzlichen Trick anwenden. Um die Verschiebung innerhalb des Spaltenindex zu eliminieren, nutzen wir die Differenz der Spalte, in der wir unser Ergebnis suchen, und der Spalte, die den Wert der Verschiebung darstellt. Der Wert der Verschiebung wird mit der letzten Spalte vor der Matrix dargestellt (Lösung 1) oder mit der ersten Spalte der Matrix minus 1 (Lösung 2).

Zwei Lösungsvarianten

Sollte unsere Formel in der Spalte D beginnen und wir suchen unser Ergebnis in Spalte F, würde unsere Formel wie folgt aussehen:

Lösung 1:

=SVERWEIS(Suchkriterium;Matrix;SPALTE(F1)-SPALTE(C1);[Bereich_Verweis])

oder Lösung 2:

=SVERWEIS(Suchkriterium;Matrix;SPALTE(F1)-(SPALTE(D1)-1);[Bereich_Verweis])

Bei beiden Formeln würde ein Spaltenindex von 3 das Ergebnis sein.

Zur besseren Verständlichkeit habe ich eine Musterdatei erstellt:

• Download der Musterdatei (37,7 KiB)

Im ersten Arbeitsblatt „BSEG“ befindet sich ein Ausschnitt der Tabelle BSEG aus dem SAP-System (Testdaten). Sie beinhaltet die Belegsegmente. Was allerdings fehlt sind die Kopfdaten. So fehlt z. B. das Belegdatum, der Benutzer, der die Buchung durchgeführt hat, die Transaktion, die er dabei verwendet hat und die Referenz. Diese sind allerdings in der Tabelle BKPF – Belegkopf zu finden. Daher befindet sich im zweiten Arbeitsblatt „BKPF“ ein Ausschnitt dieser Tabelle.

Die vier gewünschten Werte befinden sich in diesem Beispiel genau nebeneinander (Spalte E-H), sodass unsere Logik angewendet werden kann. Die Verknüpfung der beiden Tabellen kann über die Belegnummer erfolgen. Diese befindet sich in der Tabelle BSEG in der Spalte C. Die Tabelle BKPF beginnt zwar schon in Spalte A, allerdings steht die Belegnummer ebenfalls in der Spalte C. Da die Matrix im SVERWEIS mit der Spalte beginnen muss, in der nach einer Übereinstimmung gesucht wird, muss unsere Matrix mit der Spalte C beginnen. Unsere Formel würde für das Belegdatum wie folgt aussehen:

=SVERWEIS($C2;BKPF!$C$1:$H$201;SPALTE(BKPF!E$1)-(SPALTE(BKPF!$C$1)-1);FALSCH)

Ich habe für den Spaltenindex die Lösung 2 verwendet und die Differenz der Spalte E „Bel.Datum“ und Spalte C „Belegnr“ minus 1 berechnet.

Zusatz: Die $-Zeichen bewirken innerhalb der Formel, dass die Zelle nicht komplett dynamisch verändert wird, sobald die Formel verschoben wird. Beispielsweise wird durch das $-Zeichen vor dem C des Suchkriteriums die Zeilen dynamisch verändert, sobald ich die Formel nach unten ziehe. Allerdings bleibt die Spalte C gleich, sodass beim Verschieben nach rechts die Spalte C und somit das Suchkriterium Belegnr. bestehen bleibt.

Vorsicht bei langen Verschachtelungen

Die meisten Formeln in Excel können mit der Verbindung weiterer Formeln (sogenannter Verschachtelungen) nach den eigenen Vorstellungen dynamisiert werden. Dies kann durchaus Zeit sparen, wenn man mehrere gleichartige Analysen durchführen möchte. Allerdings sollten die Formeln immer mehrfach geprüft werden, um auch sicher zu gehen, dass die Verschachtelungen auch das gewünschte Ergebnis bringen. Es gibt nichts Ärgerlicheres als wenn der Berichtsempfänger am Ende selbst erkennt, dass die Analyse nicht stimmt, weil die eingesetzten Formeln falsch sind.