Blog

Rainer Pollmann

Rainer Pollmann

Funktion XVERGLEICH

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.

Leistungsprämie
Die Betriebsrats-Vereinbarung

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)

XVERWEISKomplett
XVERWEIS mit insgesamt sechs Argumenten!

XVERWEIS habe ich in ein einem anderen Beitrag bereits vorgestellt, daher konzentriere ich mich hier auf XVERGLEICH.

Beispiel2: INDEX() und XVERGLEICH()

INDEX

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.

XVERGLEICH

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)


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!