Excel Funktion
FILTER
für Excel 2007 bis 2019
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.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 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")
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.
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.
Filter - Beispiel 4
Zum Schluss noch ein Beispiel für mehrfaches Filtern.
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.
Wissen teilen ist die Zukunft der Menschheit