Blog

Rainer Pollmann

Rainer Pollmann

Funktion XVERWEIS

Mit einem Update Ende April 2020 haben sich tiefgreifende Änderungen in Excel 365 ergeben. In diesem Beitrag möchte ich die neue Funktion XVERWEIS vorstellen, die SVERWEIS und WVERWEIS obsolet macht. Je nachdem, könnte man auch auf die von uns präferierte Kombination aus INDEX()/VERGLEICH() zukünftig verzichten. Die Funktion ist leider nicht abwärtskompatibel!
Vorstellen möchte ich diese neue Funktion an einem Beispiel, dass wir seit 1993 in unserem Seminar EXCEL im Controlling und Finanzwesen  verwenden.


In unserem Beispiel geht es darum, für die Qualifizierung von Mitarbeitern das kostengünstigste Tagungshotel zu finden. Der Wert ist nicht eindeutig und hängt von verschiedenen Faktoren ab. Diese werden über Schaltflächen gesteuert und in ihrer Kombination in einer Tabelle zu Gesamtkosten berechnet. Für die jeweilige Konstellation sollen dann aus einer Tabelle das Hotel und der Tagungsort herausgesucht werden. Für solche Aufgabenstellungen sind bei Excel die Matrix-Funktionen gedacht.

Beispiel1

SVERWEIS1
SVWERWEIS()
INDEX1
Kombination aus INDEX() und VERGLEICH()

In dieser ältesten Variante ist die Aufgabenstellung einmal mit SVERWEIS(Hotel) und einmal mit INDEX/VERGLEICH (Tagungsort) gelöst.

Beispiel2

INDEX2

Diese Variante zeigt meinen Lieblingsansatz, in dem ich eine einzige verschachtelte Funktion verwende, um die Aufgabenstellung zu lösen. Teilnehmern der Seminare Liquiditätssteuerung und Kapitalbedarfsrechnung mit Excel und Simulationsmodelle mit Excel erstellen kennen diesen Ansatz aus dem Beispiel Rolling Forecast.

Beispiel3

XVERWEIS

Diese neueste Variante  zeigt die Lösung mit einem zweimal verschachtelten XVERWEIS. XVERWEIS durchsucht eine Spalte (oder Zeile) und liefert intern eine Zeilen-(oder Spalten-)nummer. Mit dieser Positionsangabe arbeitet dann die Rückgabematrix. Beachten Sie das Feld „Wenn_nicht_gefunden“. Hier können Sie einen alternativen Zellinput bestimmen, falls XVERWEIS() bei der Suchmatrix das Suchkriterium nicht finden kann. Sonst gibt es die Fehlermeldung #NV!

Hier finden Sie die Funktion in unserer Beispieldatei.

Argumente von XVWERWEIS

Suchkriterium:
Die Zahl, Zeichenfolge oder Zellinhalt, nachdem gesucht wird. Platzhalterzeichen sind möglich. In diesem Fall muss Vergleichsmodus  =2 sein
Suchmatrix:
Der Bereich indem gesucht werden soll. Müsste eigentlich Suchvektor heißen, da der angegebene Bereich nur eine Spalte bzw. eine Zeile umfassen darf.
Rückgabematrix:
Der Bereich aus dem die Rückgabewerte kommen. Kann aus einer oder mehreren Spalten/Zeilen bestehen. Führt unter Umständen zur Fehlermeldung #Überlauf!. In diesem Fall den impliziten Schnittmengen-Operator (@)  eingeben.
Wenn_nicht_gefunden:
Falls es zu einer Fehlermeldung #NV kommt, können Sie eine alternative Zeichenfolge oder eine Leere Zelle als Rückgabewert definieren. Das Feld ist [optional].
Vergleichsmodus:
Hier geben Sie [optional] an, ob Sie:
 0 = eine genaue Übereinstimmung (wird verwendet, wenn Sie hier nichts eintragen),
 1 = genaue Übereinstimmung oder nächst größeres Element,
-1 = genaue Übereinstimmung oder nächst kleineres Element wünschen oder
 2 = Platzhalterzeichen beim Suchkriterium verwendet haben.
Suchmodus:
Hier geben Sie [optional] an, wie in der Suchmatrix gesucht werden soll:
 0 = vom ersten zum letzten Element (wird verwendet, wenn Sie hier nichts eintragen),
-1 = vom letzten zum ersten Element,
 2 = Binärsuche aufsteigend (d.h. Suchmatrix ist aufsteigend sortiert) ,
-2 = Binärsuche absteigend (d.h. Suchmatrix ist absteigend sortiert)

Im Gegensatz zu SVERWEIS (), lässt sich mit XVERWEIS() die Suchmatrix nicht nur von oben bzw. links, sondern auch von unten bzw. rechts durchsuchen.


Wenn Ihnen dieser Beitrag gefallen hat, dann teilen  Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.

Beitrag teilen:

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on xing
Share on email
Share on print

Up to date bleiben

Melden Sie sich für unseren Newsletter an!