Excel Funktion
FILTER
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.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
Wer eine Möglichkeit zum Filtern mit Wildcards sucht, kann gleich nach unten zum ▸entsprechenden Kapitel springen.
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
Microsofts Funktion FILTER unterstützt leider nicht den Einsatz von Wildcards (*, ?, ~) in den Filterkriterien.
Wer eine einfache Funktion zum Filtern mit Wildcards sucht, findet eine Lösung in der Funktion XVERWEIS2.
Diese Funktion ist keine in Excel integrierte Funktion, sondern eine mit VBA programmierte UDF (User Defined Function).
Eine kleine Beispieldatei soll die Verwendung dieser Funktion XVerweis2 zum Filtern mit Wildcards demonstrieren:
▸Beispieldatei zum Filtern mit Wildcards herunterladen
Die Beispieldatei enthält außer der Funktion XVerweis2 auch die ganz oben am Anfang der Webseite genannten Funktionen Sortieren, Filter, Xverweis usw. für ältere Versionen von Excel.
Die verwendete Formel lautet:
=XVerweis2($G$2;$B$6:$B$25;$B$6:$E$25;"";2;2)
Der erste Parameter $G$2 ist der Suchbegriff, der die Wildcards * ? ~ enthalten kann.
Der zweite Parameter $B$6:$B$25 ist die Spalte, in der gesucht wird.
Der dritte Parameter $B$6:$E$25 gibt an, aus welchem Bereich die Treffer zurückgegeben werden.
Im vierten Parameter kann ein String angegeben werden, der bei erfolgloser Suche angezeigt wird.
Der fünfte Parameter "2" gibt an, dass eine Suche mit Wildcards stattfindet.
Der sechste Parameter "2" bedeutet, dass in normaler Suchreihenfolge (vom ersten bis zum letzten Element) gesucht wird und alle Übereinstimmungen zurückgegeben werden, nicht nur die erste.
Hinweis:
Da es sich um eine sog. Matrixformel handelt, muss zuerst der Bereich G6:J25 selektiert werden, bevor man die Formel in die Bearbeitungszeile eingibt. Die Eingabe der Formel muss mit STRG-UMSCHALT-RETURN abgeschlossen werden.
Weitere Informationen zur Funktion XVerweis2 und ein komplexeres Beispiel zum Filtern mit Wildcards sind hier zu finden:
▸Beispiel 4 - Filtern mit Wildcards
Wissen teilen ist die Zukunft der Menschheit