Letzte Änderung: 08.06.2022 
Excel Logo hb

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.7 - Letzte Änderung: 06.01.2023 - 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:

Beispiel für die XVERGLEICH Funktion

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 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.

Ornament

Wissen teilen ist die Zukunft der Menschheit