Excel Funktion
XVERGLEICH
für Excel 2007 bis 2019
Download der Exceldatei mit dem VBA-Code der Funktionen
SORTIEREN, SORTIERENNACH, XVERWEIS, XVERGLEICH, FILTER, EINDEUTIG, SEQUENZ, ZUFALLSMATRIX
und allen auf diesen Seiten dargestellten Beispielen:
▸Download der Exceldatei
(Version 3.8 - Letzte Änderung: 24.10.2024 - XVerweis und XVergleich nun fit für den Einsatz in Matrixformeln ▸mehr dazu)
Worum geht es?
In Excel 365 und ab Excel 2021 hat Microsoft die lange vermissten Zellfunktionen SORTIEREN, SORTIERENNACH, FILTER, XVERWEIS und weitere implementiert. Allerdings wurden sie für ältere Excel-Versionen nicht nachgerüstet, so dass alle Benutzer von Excel 2007 bis einschließlich Excel 2019 darauf verzichten mussten.
Hier gibt es nun (seit Ende Januar 2022) eine Lösung für die Benutzer älterer Excel-Versionen.
Mit Hilfe von VBA wurden benutzerdefinierte Zellfunktionen - sog. UDFs (User Defined Functions) - implementiert, die genau dieselben Parameterlisten haben wie die entsprechenden Microsoft-Funktionen.
In den Fällen, wo die hier zur Verfügung gestellten Funktionen mehrere Ergebnisse zurückgeben, muss ihre Eingabe mit STRG-UMSCHALT-EINGABE abgeschlossen werden. Vor der Eingabe der Formel muss auf dem Tabellenblatt ein Bereich selektiert werden, der die zurückgegebenen Daten aufnimmt - wie bei den sog. CSE-Matrixformeln üblich (CSE = Control Shift Enter).
Eine grundlegende und ausführliche Einführung in das Thema Matrixformeln gibt es in ▸diesem Tutorial.
Bei Funktionsnamen von UDFs ist es egal, ob man die Buchstaben groß oder klein schreibt.
Anleitung zum Einbinden der Funktionen in die eigene Exceldatei siehe ▸Einbinden der Funktionen.
Die Funktion XVERGLEICH
Die Funktion XVERGLEICH ist eine Verbesserung der bisherigen Funktion VERGLEICH. Sie erfüllt deren Aufgaben und bietet darüber hinaus weitere Möglichkeiten. Sie sucht in genau derselben Weise wie die Funktion XVERWEIS nach einem Element in einer Liste, gibt jedoch nicht gefundene Werte zurück, sondern die relative Position des gefundenen Elements in der Liste.
Syntax
= XVergleich ( Suchkriterium; Suchmatrix; [Vergleichsmodus]; [Suchmodus] )
| Parameter | Erläuterung |
|---|---|
| Suchkriterium | Wert, nach dem gesucht wird Wird dieser Parameter nicht angegeben (nur Semikolon), wird nach einer leeren Zelle gesucht |
| Suchmatrix | Array oder Bereich, in dem gesucht wird |
| Vergleichsmodus (optional) |
Typ der Übereinstimmung beim Suchen: 0: Suche nach genauer Übereinstimmung (Standard) 1: Suche nach genauer Übereinstimmung Wird nichts gefunden, wird der Index des nächstgrößeren Elements zurückgegeben -1: Suche nach genauer Übereinstimmung Wird nichts gefunden, wird der nächstkleinere Wert zurückgegeben 2: Suche mit Wildcard-Symbolen Wildcards wie *, ?, ~ usw. können für die Suche eingesetzt werden |
| Suchmodus (optional) |
Suchmodus: 1: Normale Suche beginnend mit dem ersten Element (Standard) -1: Umgekehrte Suche beginnend mit dem letzten Element 2: Schnelle Binärsuche, bei der die Suchmatrix in aufsteigender Reihenfolge sortiert sein muss -2: Schnelle Binärsuche, bei der die Suchmatrix in absteigender Reihenfolge sortiert sein muss |
Die Funktion XVergleich hat das gleiche Verhalten wie die Excel 365-Funktion XVERGLEICH.
Vergleiche dazu auch die Funktionsbeschreibung von Microsoft:
▸https://support.microsoft.com/de-de/office/xvergleich-funktion-d966da31-7a6b-4a13-a1c6-5a33ed6a0312
Die neue Funktion XVERGLEICH hat gegenüber der Funktion SVERWEIS wesentlich mehr Möglichkeiten. Allein die letzten beiden Parameter ermöglichen 4 ✕ 4 = 16 verschiedene Kombinationen.
Beispiel:
Die die letzten beiden optionalen Parameter fehlen, werden die Standardwerte verwendet (Suche nach genauer Übereinstimmung und normale Suchreihenfolge beginnend mit dem ersten Element der Liste).
Neue Möglichkeiten der Funktion:
- Die Liste kann auch in umgekehrter Reihenfolge durchsucht werden, so dass das letzte Vorkommen des Suchbegriffs gefunden wird.
- Man kann wählen, ob man eine unsortierte Liste durchsuchen lässt (Suchmodus 1 und -1) oder eine sortierte Liste (Suchmodus 2 und -2). Auch bei einer unsortierten Liste wird das nächstgrößere bzw. das nächstkleinere Element gefunden.
- Man kann jetzt auch nach leeren Feldern suchen (ersten Parameter unbenannt lassen).
Die Suche in der unsortierten Liste hat einen Geschwindigkeitsnachteil, denn die Suche in einer sortierten Liste nach dem sog. binären Suchverfahren geht wesentlich schneller vonstatten.
Die binäre Suche funktioniert so wie das Zahlenraten, bei dem man eine Zahl zwischen 1 und 1000 sucht:
Man wählt als erste Antwort die Mitte, also 500, und bekommt dann mitgeteilt, dass die gesuchte Zahl größer ist. Daraufhin wählt man vom infrage kommenden Bereich 501 bis 1000 wieder die Mitte, also 750, und bekommt die Rückmeldung, dass die Zahl kleiner ist. Wenn man dies so weiter fortsetzt, hat man spätestens nach 10 Vergleichen die gesuchte Zahl gefunden.
Bei einer unsortierten Liste muss man der Reihe nach alle Werte in der Liste vergleichen (sequentielle Suche). Im ungünstigsten Fall hat man 1000 Vergleiche, im Durchschnitt ungefähr 500 Vergleiche.
Wissen teilen ist die Zukunft der Menschheit