Mit einem Update Ende April 2020 haben sich tiefgreifende Änderungen in Excel 365 ergeben. In diesem Beitrag möchte ich die neue Funktion XVERGLEICH vorstellen, die leider nicht abwärtskompatibel ist!
Vorstellen möchte ich diese neue Funktion an einem Beispiel aus dem Personal-Controlling, dass wir seit 1993 in unserem Seminar EXCEL im Controlling und Finanzwesen verwenden.
In unserem Beispiel geht es um Berechnung von Mitarbeiter-Produktivität eine produzierenden Unternehmens und um die Berechnung eines Leistungsgrades sowie der mit dem Betriebsrat vereinbarten Leistungsprämie.
Dazu gibt es Intervall, das ausgelesen werden muss. Dies haben wir seit 1993 mit SVERWEIS, bzw. mit INDEX/VERGLEICH gelöst und möchten dies in diesem Beispiel mit XVERWEIS und XVERGLEICH lösen. Die Herausforderung in diesem Beispiel besteht darin, dass nur die Intervallgrenzen definiert sind, die meisten gesuchten Werte also nicht explizit vorkommen, sondern zwischen zwei Werten liegen. Vor Excel365 musste die Intervalltabelle zwingend aufsteigend sortiert sein, jetzt muss sie (technisch gesehen) nicht sortiert sein. Für die Pflege ist das natürlich zu empfehlen.
Beispiel1: XVERWEIS()
XVERWEIS habe ich in ein einem anderen Beitrag bereits vorgestellt, daher konzentriere ich mich hier auf XVERGLEICH.
Beispiel2: INDEX() und XVERGLEICH()
INDEX() gibt aus einem größeren Zellbereich den Inhalt einer Zelle zurück. Dazu werden eine Zeilen- und eine Spaltennummer benötigt. das Zeichen (@) wird von Microsoft als impliziter Schnittmengen-Operator bezeichnet und wurde mit dem Update von Ende April 2020 eingeführt.
Der XVERGLEICH liefert die für INDEX() in diesem Beispiel wichtige Zeilennummer.
So arbeitet XVERGLEICH()
Mit der XVERGLEICH Funktion kann man die Position eines Eintrages in einem Zellbereich ermitteln. Der Zellbereich darf dabei jedoch nur so breit wie eine Spalte oder so hoch wie eine Zeile sein (=VEKTOR). Falls der Eintrag gefunden wird, wird die relative Position innerhalb des Zellbereichs als Zahl zurückgegeben. Im Gegensatz zu VERGLEICH (), lässt sich mit XVERGLEICH() die Suchmatrix nicht nur von oben bzw. links, sondern auch von unten bzw. rechts durchsuchen.
Hier finden Sie die Funktion in unserer Beispieldatei.
Argumente von XVERGLEICH()
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. 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)
