Letzte Änderung: 08.06.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.6 - Letzte Änderung: 11.09.2022)

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 FILTER

Die Funktion Filter_ ist ein Ersatz für die nur in Excel 365 verfügbare Funktion FILTER. Sie erwartet genau dieselbe Parameterliste wie die Excel 365-Funktion und besitzt dasselbe Filterverhalten.

Der Funktionsname heißt "Filter_" mit Unterstrich am Ende, weil der Funktionsname "Filter" von Excel nicht als Name für eine UDF akzeptiert wird.

= Filter_ ( Matrix; Filterkriterien; [wenn_leer] )

Parameter Erläuterung
Matrix Matrix oder Bereich, der/die gefiltert werden soll
Filterkriterien Ein Ausdruck, der ein boolesches Array erzeugt, dessen Länge mit der Höhe oder der Breite der Matrix übereinstimmt
wenn_leer
(optional)
Ein String, der erscheint, wenn die Liste der gefilterten Daten leer ist
Standard: wenn_leer = ""

Vergleiche dazu auch die Funktionsbeschreibung von Microsoft:
https://support.microsoft.com/de-de/office/filter-funktion-f4f7cb66-82eb-4767-8f7c-4877ad80c759

Beispiel:  = Filter_ ($B$3:$F$85; $F$3:$F$85>1000; "Die gefilterte Liste ist leer.")

Gefiltert wird in diesem Beispiel nach der Spalte F. Vom Bereich $B$3:$F$85 werden diejenigen Zeilen herausgefiltert, bei denen in der Spalte F ein Wert größer als 1000 steht.

Die Funktion erkennt anhand des (senkrechten) Filterkriteriums $F$3:$F$85>1000, dass eine senkrechte Filterung erfolgen soll. Das heißt, es werden bestimmte Zeilen herausgefiltert.

 

Zur Illustration folgen noch vier weitere Beispiele, die u. a. auch die Kombination der Funktionen Sortieren und Filter_ sowie die Verschachtelung der Filter_-Funktion (mehrfache Filterung) zeigen.

Filter - Beispiel 1

Bei diesem Beispiel wird waagerecht gefiltert. Es werden also bestimmte Spalten herausgefiltert.
Das Filterkriterium lautet:   (B5:E5="Region") + (B5:E5="Umsatz")

Excel Beispiel für die Funktion Filter_

Es werden hier die Spalten 'Region' und 'Umsatz' herausgefiltert.

REGEL:
Werden mehrere Filterkriterien miteinander verknüpft, müssen sie unbedingt in Klammern eingefasst werden.
Für die ODER-Verknüpfung wird das Pluszeichen (+), für die UND-Verknüpfung das Malzeichen (*) verwendet.

Da die dynamische Anpassung des Ausgabebereiches mit VBA nicht nachgebildet werden kann, muss man - wie bei CSE-Matrixformeln üblich - vor der Formeleingabe einen bestimmten Ausgabebereich auf dem Tabellenblatt selektieren. Hier wählt man am besten dieselbe Größe wie die der Ausgangsdaten.

Filter - Beispiel 2

Im Beispiel 2 wird senkrecht gefiltert, das heißt es werden bestimmte Zeilen herausgefiltert.

Excel Beispiel für die Funktion Filter_

In diesem Fall werden alle diejenigen Zeilen herausgefiltert, bei denen in der Spalte E ein Umsatz zwischen 1000 und 1500 steht.

Filter - Beispiel 3

Das Beispiel 3 zeigt, wie man die Funktionen Sortieren und Filter_ kombinieren kann. Die gefilterten Daten von Beispiel 2 werden hier zusätzlich nach Umsatz sortiert. Dazu übergibt man der Funktion Sortieren den kompletten Filterausdruck der vorigen Formel als ersten Parameter.

Excel Beispiel für die Funktion Filter_

Filter - Beispiel 4

Zum Schluss noch ein Beispiel für mehrfaches Filtern.

Excel Beispiel für die Funktion Filter_

Es wird als erstes waagerecht gefiltert. Dabei werden die Spalten 'Region' und 'Umsatz' herausgefiltert. Als zweites wird senkrecht gefiltert. Alle Umsätze größer als 1000 werden herausgefiltert.

Die innere Filterfunktion der Formel filtert die Spalten 'Region' und 'Umsatz' heraus. Dieser ganze innere Filterausdruck wird in der äußeren Filterfunktion als erster Parameter eingesetzt. Die äußere Funktion übernimmt sozusagen die Ergebnisdaten (Ergebnismatrix) der inneren Funktion und filtert sie nach der Spalte 'Umsatz'.

Filtern mit Wildcards

Die Funktion FILTER unterstützt leider nicht den Einsatz von Wildcards (*, ?, ~) in den Filterkriterien. Wer in den Filterkriterien Wildcards einsetzen möchte, kann die zusätzlich programmierte Funktion XVERWEIS2 verwenden - siehe XVERWEIS, Beispiel 4 - Filtern mit Wildcards.

Ornament

Wissen teilen ist die Zukunft der Menschheit