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
In dieser ältesten Variante ist die Aufgabenstellung einmal mit SVERWEIS(Hotel) und einmal mit INDEX/VERGLEICH (Tagungsort) gelöst.
Beispiel2
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
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.
