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

Mehr lesen...

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

Zusatzthema:
Filtern ohne FILTER-Funktion

Wie kann man Daten automatisch filtern ohne FILTER-Funktion - nur durch Einsatz konventioneller Formeln?

Manch einer ist auf eine solche Lösung angewiesen, wenn er zum Beispiel Excel 2019 oder eine ältere Version verwendet und keine Makros verwenden möchte.

Anhand einer kleinen Beispieldatei soll die Vorgehensweise gezeigt und erklärt werden. Sie kann hier heruntergeladen werden:

▸Beispieldatei zum Filtern ohne FILTER-Funktion

Ausgangsmaterial sind die Daten im Bereich B6:E25. Alle Zeilen mit einem Umsatz >= 900 sollen gefiltert werden.

In der Beispieldatei werden zwei Lösungen vorgestellt: eine ohne Matrixformeln und eine zweite mit Matrixformeln.

a)  Lösung ohne Matrixformeln

In der Spalte A tragen wir zunächst die laufenden Nummern 1 bis 20 ein. Diese Hilfsspalte wie auch die beiden folgenden können später ausgeblendet werden.

In die Zelle G6 tragen wir die folgende Formel ein:
=ZEILE(A1)+NICHT($E6>=900)*1000000
Diese Formel wird nach unten kopiert bis zur Zelle G25.

Der rot dargestellte Teil der Formel (das Argument der NICHT-Funktion) ist das Filterkriterium. Die Formel erzeugt für alle Umsätze, die dem Filterkriterium entsprechen, laufende Nummern ab 1 aufwärts. Entspricht der Umsatz nicht dem Filterkriterium, wird zur laufenden Nummer die Zahl 1000000 addiert. Dies hat zum Ziel, dass bei einer nachfolgenden Sortierung diese großen Nummern alle am Ende der Liste stehen.

Die nachfolgende Sortierung der erzeugten Nummern erfolgt mit der Formel:
=KKLEINSTE($G$6:$G$25;ZEILE(A1))
Diese Formel wird in die Zelle H6 eingetragen und nach unten kopiert.

Mit Hilfe der SVERWEIS-Funktion können wir uns nun die Zeilen mit den kleinen Nummern anzeigen lassen. In die Zelle I6 tragen wir die Formel
=WENNFEHLER(SVERWEIS($H6;$A$6:$E$25;2;0);"")
ein und kopieren sie nach unten.

Die SVERWEIS-Funktion erzeugt bei den großen laufenden Nummern einen Fehler. Die vorgeschaltete Funktion WENNFEHLER sorgt dafür, dass in diesen Fällen eine leere Zelle erscheint.

Die Formeln für die drei weiteren Spalten lauten:
Zelle J6:  =WENNFEHLER(SVERWEIS($H6;$A$6:$E$25;3;0);"")
Zelle K6:  =WENNFEHLER(SVERWEIS($H6;$A$6:$E$25;4;0);"")
Zelle L6:  =WENNFEHLER(SVERWEIS($H6;$A$6:$E$25;5;0);"")
Der rote Parameter entscheidet, welche Spalte der Originaldaten hier angezeigt werden soll.

b)  Lösung mit Matrixformeln

Die Hilfsspalte mit den sortierten laufenden Nummern kann mit einer einzigen Matrixformel erzeugt werden. Dazu markieren wir den Bereich N6:N25, tragen in Bearbeitungszeile oben die Formel
=KKLEINSTE(ZEILE($1:$20)+NICHT($E$6:$E$25>=900)*1000000;ZEILE($1:$20))
ein und schließen die Eingabe nicht mit ENTER, sondern mit STRG-SHIFT-ENTER ab. Die Formel erscheint dann in der Bearbeitungszeile automatisch in geschweiften Klammern.

HINWEIS:
Möchte man in dieser zweiten Variante mehrere Filterkriterien verwenden, so darf man nicht die UND- bzw. ODER-Funktion zum Verknüpfen der Filterkriterien verwenden, sondern die Operatoren * und +. Eine Formel, die alle Umsätze von 900 bis 1200 herausfiltert, würde dann z.B. folgendermaßen aussehen:
=KKLEINSTE(ZEILE($1:$20)+NICHT(($E$6:$E$25>=900)*($E$6:$E$25<=1200))*1000000;ZEILE($1:$20))

Um nun die gefilterten Daten anzuzeigen, kann man den Bereich O6:R6 (die erste Zeile) markieren, in die Bearbeitungszeile oben die Formel
=WENN(N6>1000000;"";BEREICH.VERSCHIEBEN($B$5:$E$5;N6;0))
eintragen und die Eingabe mit STRG-SHIFT-ENTER abschließen.

Diese Matrixformel wird anschließend nach unten kopiert.

Wer sich gerne mit Matrixformeln vertraut machen möchte, findet hier eine ausführliche Einführung:
▸Tutorial zu Matrixformeln