Letzte Änderung: 27.03.2022 
Excel Logo hb

Download der Exceldatei mit dem VBA-Code der Funktionen SORTIEREN, SORTIERENNACH, FILTER, XVERWEIS, XVERGLEICH, EINDEUTIG, SEQUENZ, ZUFALLSMATRIX und allen auf diesen Seiten dargestellten Beispielen:
Download der Exceldatei
(Version 3.4 - Letzte Änderung: 15.03.2022)

Weitere Informationen auf der Seite Sortieren.

Die Funktion XVERWEIS

 

Um es vorweg zu nehmen - viele Exceller haben davon geträumt:
Es gibt sie jetzt - die Funktion XVERWEIS mit mehreren Suchkriterien (siehe weiter unten XVERWEIS2).
Diese Sonderfunktion XVERWEIS2 bietet verglichen mit XVERWEIS folgende Zusatzoptionen:
  1. mehrere Suchkriterien
  2. Wildcards in den Suchkriterien (optional)
  3. Rückgabe aller Treffer (optional)
Außerdem gibt es ein Tutorial, wie man die UDF XVERWEIS2 mit Hilfe von VBA erstellt (siehe unten Tutorial).

 

Aber zunächst zur Funktion XVERWEIS:

Die Funktion XVERWEIS ist eine Verbesserung der bisherigen Funktionen SVERWEIS und WVERWEIS. Sie erfüllt die Aufgaben dieser beiden und bietet darüber hinaus noch wesentlich mehr Möglichkeiten.

Syntax

= XVerweis ( Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden];
    [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 (muss eindimensional sein, d.h. eine einzelne Zeile oder einzelne Spalte)
Rückgabematrix Array oder Bereich, aus dem gefundene Werte zurückgegeben werden
wenn_nicht_gefunden
(optional)
Text, der an Stelle des Errorcodes #N/A zurückgegeben wird, wenn nichts gefunden wurde
Vergleichsmodus
(optional)
Typ der Übereinstimmung beim Suchen:
0:  Suche nach genauer Übereinstimmung (Standard)
1:  Suche nach genauer Übereinstimmung
Wird nichts gefunden, wird der nächstgrößere Wert zurückgegeben
-1:  Suche nach genauer Übereinstimmung
Wird nichts gefunden, wird der nächstkleinere Wert zurückgegeben
2:  Suche mit Wildcard-Symbolen
Die Wildcards *, ?, ~ 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

Der Parameter Suchmatrix muss eine einzelne Zeile oder eine einzelne Spalte oder ein eindimensionales Array sein.

Wenn der Parameter Rückgabematrix aus mehreren Zeilen und mehreren Spalten besteht, werden mehrere Werte zurückgegeben. Die Eingabe der Formel muss in diesem Fall mit STRG-UMSCHALT-EINGABE abgeschlossen werden.

Die Funktion XVerweis hat das gleiche Verhalten wie die Excel 365-Funktion XVERWEIS.
Vergleiche dazu auch die Funktionsbeschreibung von Microsoft:
https://support.microsoft.com/de-de/office/xverweis-funktion-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Die neue Funktion XVERWEIS hat gegenüber der Funktion SVERWEIS wesentlich mehr Möglichkeiten. Allein die letzten beiden Parameter ermöglichen theoretisch 4 ✕ 4 = 16 verschiedene Kombinationen. Praktisch sind es nur 14 verschiedene Möglichkeiten. Denn wenn der Parameter Vergleichsmodus gleich 2 ist (Suche mit Wildcards), haben sowohl die Suchmodi 1 und 2 als auch die Suchmodi -1 und -2 dieselbe Wirkung.

Beispiel 1:

In der Liste wird nach dem Mitarbeiter 'Müller' gesucht.

Beispiel für die Funktion XVERWEIS

Die verwendete Formel lautet:
= XVerweis ($C$28; $C$5:$C$24; $B$5:$G$24; "- -"; ; -1)

Es wird nach genauer Übereinstimmung gesucht, denn für den fehlenden Parameter Vergleichsmodus wird der Standardwert 0 verwendet.
Beginnend mit dem letzten Element der Liste wird in umgekehrter Reihenfolge gesucht, da der Parameter Suchmodus gleich -1 ist. Somit wird der letzte Eintrag des Mitarbeiters 'Müller' in Zeile 18 gefunden.

Dieses Beispiel zeigt fünf der neuen Möglichkeiten:

  1. Es können mehrere Werte zurückgegeben werden, nicht nur einer (hier die sechs Werte in den Zellen B31:G31).
  2. An Stelle einer Spaltennummer wird die Adresse einer Spalte angegeben. Somit können auch Werte links vom Suchbereich zurückgegeben werden.
  3. Die Liste kann auch in umgekehrter Reihenfolge durchsucht werden, so dass das letzte Vorkommen des Suchbegriffs gefunden wird.
  4. Man kann einen String angeben, der bei erfolgloser Suche an Stelle des bisherigen Error-Codes zurückgegeben wird.
  5. Standard für die Suche ist jetzt die genaue Überstimmung.

Weitere Neuerungen:


Dieser letzte Punkt eröffnet eine ganze Palette neuer Möglichkeiten. Denn nun kann die Funktion XVERWEIS in anderen Formeln an denjenigen Stellen eingesetzt werden, wo Zellbezüge erwartet werden.

Beispiel 2

Das Beispiel 2 zeigt einen solchen Fall. Zwei XVerweis-Funktionen werden als Parameter in die SUMME-Funktion eingesetzt.

Beispiel für die Funktion XVERWEIS

Die Formel in Zelle G28 lautet:
= SUMME (XVerweis ($C$28; $F$5:$F$24; $G$5:$G$24) : XVerweis ($E$28; $F$5:$F$24; $G$5:$G$24; ; ;-1)).
Voraussetzung ist, dass die Jahreszahlen aufsteigend sortiert sind.

Um die Summe der Umsätze von 2020 bis 2022 zu berechnen, kann man die Formel = SUMME (G10:G22) verwenden. Um den Bereich variabel zu machen, ersetzen wir die beiden Adressen G10 und G22 durch XVerweis-Aufrufe.

Den Zellbezug G10 ersetzen wir durch den Ausdruck XVerweis ($C$28; $F$5:$F$24; $G$5:$G$24). Die XVerweis-Funktion sucht nach dem Jahr 2020 in normaler Suchrichtung und findet deshalb die erste Jahreszahl 2020 in der 6. Zeile der Liste. Aus dem Rückgabebereich $G$5:$G$24 gibt sie daher den Zellbezug $G$10 zurück.

Den Zellbezug G22 ersetzen wir durch den Ausdruck XVerweis ($E$28; $F$5:$F$24; $G$5:$G$24; ; ;-1). Die XVerweis-Funktion sucht nach dem Jahr 2022 in umgekehrter Suchrichtung und findet deshalb die letzte Jahreszahl 2022 in der 18. Zeile der Liste. Aus dem Rückgabebereich $G$5:$G$24 gibt sie daher den Zellbezug $G$22 zurück.

Zwischen den beiden XVerweis-Funktionen steht nur noch der Bereichsdoppelpunkt, der aus den beiden gefundenen Adressen G10 und G22 einen Bereich macht, über den die SUMME-Funktion summiert. Auf diese Weise kann man in den Eingabefelden C28 und E28 nun verschiedene Jahreszahlen eintragen und bekommt immer die Summe der Umsätze für diesen Zeitraum.

Zwei Zusatzbemerkungen:
1.  Wenn man jetzt auf die Idee kommt, bei einer unsortierten Liste den zweiten und dritten Parameter in den XVERWEIS-Ausdrücken durch SORTIEREN(...) zu ersetzen, wird man den Fehlercode #WERT erhalten. Der Grund liegt darin, dass die Funktion SORTIEREN keinen Zellbezug zurückgibt, sondern ein Array von Werten. Wird nun der Funktion XVERWEIS kein Zellbezug übergeben, sondern ein Array, gibt sie auch keinen Zellbezug zurück, sondern gleichfalls ein Array. Die Funktion SUMME benötigt jedoch vor und nach dem Bereichsdoppelpunkt je einen Zellbezug.

2.  Das Beispiel 2 zeigt ganz gut diesen Aspekt der Funktion XVERWEIS (Rückgabe eines Zellbezugs).
In der Praxis würde man diese Aufgabenstellung jedoch nach wie vor mit Hilfe der Funktion SUMMEWENNS lösen:
= SUMMEWENNS ($G$5:$G$24; $F$5:$F$24; ">="&C28; $F$5:$F$24; "<="&E28)
Dieser Formel ist es egal, ob die Jahreszahlen sortiert sind oder nicht.

 

Die Suche in einer unsortierten Liste hat einen Geschwindigkeitsnachteil, denn die Suche in einer sortierten Liste nach dem sog. binären Suchverfahren (Suchmodus 2 oder -2) ist wesentlich schneller.

Die binäre Suche funktioniert so wie das Zahlenraten, bei dem man eine Zahl zwischen 1 und 1000 sucht:
Man wählt als erste Zahl die Mitte, also 500, fragt, ob dies die gesuchte Zahl sei und bekommt dann mitgeteilt, dass die gesuchte Zahl größer ist. Daraufhin wählt man vom in Frage kommenden Bereich 501 bis 1000 wieder die Mitte, also 750, und bekommt die Rückmeldung, dass die Zahl kleiner ist. Wenn man dies auf die gleiche Weise weiter fortsetzt, hat man spätestens nach 10 Fragen (und vergleichenden Rückmeldungen) die gesuchte Zahl gefunden.

Bei einer unsortierten Liste von 1000 Elementen 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.

Zugabe:
Die Funktion XVERWEIS2

Die Funktion XVERWEIS2 ist keine der in Excel 365 verfügbaren Funktionen, sondern von mir frei erfunden.

Meine Motivation: In den Excel-Foren wird häufig nach einer VERWEIS-Funktion mit mehreren Suchkriterien gefragt und auch nach einer VERWEIS-Funktion, die eine Liste aller gefundenen Übereinstimmungen zurückgibt.

 

Die Funktion XVERWEIS2 ermöglicht zusätzlich zur Funktion XVERWEIS drei Dinge:

  1. Mehrere Suchkriterien
  2. Wildcards in allen Suchkriterien (optional)
  3. Rückgabe aller gefundenen Übereinstimmungen (optional)

Der dritte Punkt ermöglicht das Filtern von Daten mit Hilfe von Wildcards (*, ?, ~) - siehe Beispiel 4 (Filtern mit Wildcards).

Syntax

= XVerweis2 ( Suchkriterium1; Suchmatrix1; Rückgabematrix; [wenn_nicht_gefunden];
   [Vergleichsmodus]; [Suchmodus];
   [Suchkriterium2]; [Suchmatrix2]; [Suchkriterium3]; [Suchmatrix3]; ... )

Parameter Erläuterung
Suchkriterium1 1. Suchkriterium
Suchmatrix1 Array oder Bereich, in dem das 1. Suchkriterium gesucht wird
(eindimensional - einzelne Spalte oder einzelne Zeile)
Rückgabematrix Array oder Bereich, aus dem gefundene Werte zurückgegeben werden
wenn_nicht_gefunden
(optional)
Text, der an Stelle des Errorcodes #N/A zurückgegeben wird, wenn nichts gefunden wurde
Vergleichsmodus
(optional)
Typ der Übereinstimmung beim Suchen:
 0:  Suche nach genauer Übereinstimmung (Standard)
 1:  Ungültig (erzeugt den Error #WERT)
-1:  Ungültig (erzeugt den Error #WERT)
 2:  Suche mit Wildcard-Symbolen,
die Wildcards *, ?, ~ können für die Suche eingesetzt werden
Suchmodus
(optional)

Suchmodus:

 1:  Normale Suchreihenfolge beginnend mit dem ersten Element; die erste gefundene Übereinstimmung wird zurückgegeben (Standard)

-1:  Umgekehrte Suchreihenfolge beginnend mit dem letzten Element; die erste gefundene Übereinstimmung wird zurückgegeben

 2:  Normale Suchreihenfolge,
 alle Übereinstimmungen werden zurückgegeben

-2:  Umgekehrte Suchreihenfolge,
 alle Übereinstimmungen werden zurückgegeben

Suchkriterium2
(optional)
2. Suchkriterium
Suchmatrix2
(optional)
Array oder Bereich, in dem das 2. Suchkriterium gesucht wird
(eindimensional - einzelne Spalte oder einzelne Zeile)
Suchkriterium3
(optional)
3. Suchkriterium
Suchmatrix3
(optional)
Array oder Bereich, in dem das 3. Suchkriterium gesucht wird
(eindimensional - einzelne Spalte oder einzelne Zeile)
usw.  . . .

Die ersten sechs Parameter stimmen mit denen der Funktion XVERWEIS überein.

Die ersten beiden Parameter sind das 1. Suchkriterium und die 1. Suchmatrix. Nach dem sechsten Parameter kann eine beliebige Anzahl weiterer Paare bestehend aus Suchkriterium und Suchmatrix folgen.

Suchkriterien, die nicht spezifiziert sind oder den leeren String enthalten, haben keine Auswirkung auf die Suche.

Zu beachten ist, dass beim Parameter 'Vergleichsmodus' die Werte 1 und -1 ungültig sind. Die Suche nach einem nächstgrößeren oder nächstkleineren Wert wäre bei mehreren Suchkriterien nicht mehr eindeutig. Die Werte 0 und 2 haben dieselbe Bedeutung wie bei XVERWEIS.

Beim Parameter 'Suchmodus' haben die Werte 1 und -1 dieselbe Bedeutung wie bei XVERWEIS, die Werte 2 und -2 haben eine andere Bedeutung als bei XVERWEIS. Eine binäre Suche in vorsortierten Listen ergäbe bei mehreren Suchkriterien - und damit mehreren Listen - keinen Sinn mehr.

Beispiel 3:

Dieses Beispiel zeigt die Suche nach den vier Suchkriterien Mitarbeiter, Region, Produkt und Jahr.
Da bei 'Jahr' nichts eingetragen ist (Zelle F29), hat der vierte Parameter keine Auswirkung auf die Suche.

Beispiel für die Funktion XVERWEIS2

Die verwendete Formel lautet:
= XVerweis2 ($C$29; $C$6:$C$25; $B$6:$G$25; "nothing"; 0; 1; $D$29; $D$6:$D$25; $F$29; $F$6:$F$25; $E$29; $E$6:$E$25)

Der Vergleichsmodus ist 0 (exakte Suche), der Suchmodus ist 1 (normale Suchreihenfolge, Rückgabe der ersten gefundenen Übereinstimmung).

Beispiel 4 - Filtern mit Wildcards

Dieses Beispiel zeigt eine Suche nach den vier Suchkriterien Mitarbeiter, Region, Produkt und Jahr im Wildcard-Modus (Vergleichsmodus = 2) mit Rückgabe aller gefundenen Übereinstimmungen (Suchmodus = 2).
Die Suchreihenfolge ist normal, d. h. beginnend mit dem ersten Element der Liste.

Beispiel für die Funktion XVERWEIS

Die verwendete Formel lautet:
= XVerweis2 ($J$3; $C$6:$C$25; $B$6:$G$25; "nichts"; 2; 2; $K$3; $D$6:$D$25; $L$3; $E$6:$E$25; $M$3; $F$6:$F$25)

Mit dem Suchmodus -2 würde man dasselbe Suchergebnis erhalten, jedoch in umgekehrter Reihenfolge.

Als Zielbereich für diese Formel wurde der Bereich I6:N25 vorselektiert. Damit ist genügend Raum, um im Extremfall auch die komplette Liste als Ergebnismatrix aufzunehmen.

Das Verhalten der Funktion XVERWEIS2 ist in diesem Beispiel identisch mit dem der FILTER-Funktion. Der Unterschied besteht nur darin, dass man zur Filterung mit XVERWEIS2 auch Wildcards (*, ?, ~) einsetzen kann, was bei der Funktion FILTER nicht möglich ist.

Für Benutzer von Excel 365

Wer Excel 365 verwendet und an den beiden Sonderfunktionen SORTIERENNACH2 und XVERWEIS2 interessiert ist, kann hier eine Exceldatei herunterladen, die nur diese beiden UDFs enthält:
Download der Exceldatei
(Version 3.4 - Letzte Änderung: 15.03.2022)

Tutorial:   Excel-UDF erstellen

In diesem Tutorial wird am Beispiel der Funktion XVERWEIS2 gezeigt, wie man eine solche UDF mit Hilfe von VBA erstellt und welche Besonderheiten dabei zu beachten sind. Alle Phasen von der Auswertung der Parameter über die Implementierung des Suchvorgangs bis hin zur Aufbereitung des Rückgabewertes werden ausführlich erklärt.

PDF Symbol
Tutorial
Excel-UDF erstellen

Excel_UDF_Tutorial.pdf    (Letzte Änderung: 27.03.2022)
Ornament

Wissen teilen ist die Zukunft der Menschheit