Autofilter mit mehr als zwei Bedingungen in einer Spalte

Der Autofilter erlaubt benutzerdefiniert maximal zwei Filterkriterien pro Spalte. Ansonsten ist der Spezialfilter einzusetzen.
Über den Einsatz einer Hilfsspalte mit einer Formel können wir aber auch mit dem Autofilter

• mehr als 2 Bedingungen in einer Hilfsspalte filtern
• durch filtern der Hilfsspalte erreichen, dass in einem Zug Kriterien über verschiedene Spalten berücksichtigt werden

Und das erreichen wir mit der Funktion WENN in Kombination mit den Operatoren UND und/oder ODER.
Oder als Kurzform ebenfalls mit UND und/oder ODER. Innerhalb einer Spalte kann logischer Weise nur der Operator ODER eingesetzt werden. Das UND kommt beim Einbeziehen mehrerer Spalten zum Zug. Als Formelergebnis wird dann die Konstante WAHR oder FALSCH ausgegeben. Filtern Sie dann in der Hilfsspalte nach WAHR aus und Sie haben das gewünschte Filterergebnis.

Wir können die Kriterien: Gut wenn: Direkt in die Formel aufnehmen die Formel und Kriterien immer gleich bleiben Durch einen Zellbezug in die Formel einbinden(dynamischer) die Kriterien inhaltlich varrieren, die Formel aber gleich bleibt Durch einen Namen mit Formel definieren (dynamischst) Formel, Vergleichsoperatoren sowie Kriterien häufig geändert werden

Diese drei Möglichkeiten sowie mehrere Kriterien über verschiedene Spalten in einer Hilfsspalte zu filtern stelle ich Ihnen nachstehend vor, die Wahl des für Sie besten Weges hängt von Ihren Bedüfnissen ab. Um meine Beispiele übersichtlich zu halten, beschränke ich mich auf maximal drei Kriterien pro Spalte. Sie können die Anzahl aber erhöhen.


Vorab noch das nötige Handwerkszeug, um unbeschränkt filtern zu können:


Ein Hinweis, wenn Sie die Kriterien direkt in die Formel schreiben oder Formeln als Namen definieren:

• Wenn Sie Daten filtern möchten, berücksichtigen Sie, dass Excel Datumsangaben als fortlaufende Zahlen speichert. Sie dürfen also nicht das Datum in die Formel schreiben sondern die entsprechende Zahl, z.B. entspricht der 09.02.2006 der Zahl 38757
• Wenn Sie Text filtern möchten, müssen Sie diesen in Anführungszeichen setzten, wie in meinen Beispielen gezeigt
• Bei echten Zahlen brauchen Sie das nicht

Setzen Sie die Möglichkeit ein, die Kriterien über Zellbezüge zu definieren, dann brauchen Sie das nicht beachten.


Als Vergleichsoperatoren in der Formel verwenden können Sie:

entspricht = entspricht nicht <> ist größer als > ist größer oder gleich >= ist kleiner als < ist kleiner oder gleich <=

Der Autofilter bietet uns ja benutzerdefiniert noch mehr Filtermöglichkeiten:

Um diese zu erzielen, müssen Sie Formeln einsetzen, die ich Ihnen hier kurz zeigen will:

A B C D E F G H I 1                   2   Land Beginnt mit Beginnt nicht mit Endet mit Endet nicht mit Enthält Enthält nicht   3   U U A A *S* *S*   4   USA WAHR FALSCH WAHR FALSCH 1 0   5   Italien FALSCH WAHR FALSCH WAHR 0 1   6             Beachten Sie hier den Platzhalter * !   7                   Formeln der Tabelle C4 : =LINKS($B4;1)=C$3 D4 : =LINKS($B4;1)<>D$3 E4 : =RECHTS($B4;1)=E$3 F4 : =RECHTS($B4;1)<>F$3 G4 : =ZÄHLENWENN(B4:B4;G$3) H4 : =ZÄHLENWENN(B4:B4;"<>"&H$3)

Die Funktionen LINKS und RECHTS geben die Konstanten WAHR und FALSCH als Text zurück, während ZÄHLENWENN 1 oder 0 als Wert zurückgibt. Da WAHR 1 entspricht und FALSCH 0 entspricht, ist das für unseren Zweck ebenso einsetzbar.

Zum Blattanfang

Autofilter: In einer Hilfsspalte nach mehr als 2 Bedingungen einer Spalte filtern
Kriterien direkt in die Formel aufnehmen

Diese Vorgehensweise empfiehlt sich, wenn Formel und Kriterien immer gleich bleiben.

Beispiel: Sie möchten in folgender Tabelle in Spalte B die Länder USA, Irland und Polen ausfiltern

A B C D E 1           2   Land Punkte Hilfsspalte   3   USA 80 WAHR   4   Italien 30 FALSCH   5   Deutschland 60 FALSCH   6   Spanien 70 FALSCH   7   Irland 30 WAHR   8   GB 20 FALSCH   9   Polen 60 WAHR   10   USA 40 WAHR   11   Deutschland 10 FALSCH   12   GB 50 FALSCH   13           Formeln der Tabelle D3 : =ODER(B3="USA";B3="Irland";B3="Polen")

Die Formel aus Zelle D3 kann runterkopiert werden. Wenn Sie die Kriterien ändern wollen, müssen Sie die Formel in der ersten Datenzeile editieren und nach unten kopieren. Daher empfiehlt sich diese Vorgehensweise nur für konstante Filterkriterien.

Zum Blattanfang

Autofilter: In einer Hilfsspalte nach mehr als 2 Bedingungen einer Spalte filtern
Kriterien über Zellbezüge in die Formel einbinden

Diese Vorgehensweise empfiehlt sich, wenn die Kriterien inhaltlich varrieren, die Formel aber gleich bleibt.

Dafür errichten Sie einen Bereich mit Hilfszellen (hier F2:H3), in die Sie die Filterkriterien schreiben. Legen Sie so viele, wie maximal nötig an und erweitern die Formel entsprechend. Es brauchen aber später nicht alle Hilfszellen ausgefüllt werden, wenn Sie nur nach weniger als der maximalen Anzahl filtern möchten.

Hier wirkt sich die Änderung eines Kriteriums umgehend auf das Formelergebnis und somit auf das Filterergebnis aus.

A B C D E F G H I 1                   2           Land1 Land2 Land3   3   Land Punkte Hilfsspalte   USA Irland Polen   4   USA 80 WAHR           5   Italien 30 FALSCH           6   Deutschland 60 FALSCH           7   Spanien 70 FALSCH           8   Irland 30 WAHR           9   GB 20 FALSCH           10   Polen 60 WAHR           11   USA 40 WAHR           12   Deutschland 10 FALSCH           13   GB 50 FALSCH           14                   Formeln der Tabelle D4 : =ODER(B4=$F$3;B4=$G$3;B4=$H$3)

Die Formel aus Zelle D3 kann runterkopiert werden.

Tipp: In die Hilfszellen können Sie auch Gültigkeitsdropdowns legen, das beschränkt einerseits die Kriterienauswahl auf die existenten Möglicheiten und Schreibfehler in den Hilfszellen, die zu falschen Auswertungsergebnissen führen würden, werden vermieden.

Zum Blattanfang

Autofilter: In einer Hilfsspalte nach mehr als 2 Bedingungen einer Spalte filtern
Kriterien definieren über Namen

Diese Vorgehensweise empfiehlt sich, wenn die Formel, die Vergleichsoperatoren sowie die Kriterien häufig geändert werden.

Beispiel: Sie möchten in folgender Tabelle in Spalte C Werte <20, =50 und >=70 ausfiltern:

A B C D E 1           2   Land Punkte Hilfsspalte   3   USA 80 WAHR   4   Italien 30 FALSCH   5   Deutschland 60 FALSCH   6   Spanien 70 WAHR   7   Irland 30 FALSCH   8   GB 20 FALSCH   9   Polen 60 FALSCH   10   USA 40 FALSCH   11   Deutschland 10 WAHR   12   GB 50 WAHR   13           Formeln der Tabelle D3 : =Filter

Vorgehensweise:

• markieren Sie die Zelle D3
• gehen Sie über Menü > Einfügen > Namen > definieren....
• im aufspringenden Dialogfenster definieren den Namen: Filter
• bezieht sich auf: =ODER(C3<20;C3=50;C3>=70)
• im Dialogfenster auf den Hinzufügen-Button klicken und anschießend den Dialog über den OK-Button verlassen
• schreiben nun in Zelle D3: =Filter
• und kopieren D3 soweit wie nötig runter

Ändern sich ihre Filterwünsche, brauchen Sie nur die Formel im Namen zu bearbeiten. Sofort wirkt sich das dann auf alle Zellen in Spalte D aus. Selbstverständlich können Sie auch hier Zellbezüge in die Formel einsetzen in Anlehnung an das vorhergehende Beispiel.

Zum Blattanfang

Autofilter: Kriterien über verschiedene Spalten berücksichtigten

In unserem Beispiel sollen USA, Irland und Polen in Spalte B ausgefiltert werden, insofern Sie in Spalte C mehr als 40 Punkte haben.

Nun kommt eine Kombination aus UND und ODER zum Zug:

• ODER bezogen auf die Länderkriterien innerhalb Spalte B und UND auf Spalte C
• WAHR als Ergebnis in der Hilfsspalte kommt nur zustande, wenn eine Bedingungen für Spalte B und die Bedingung für Spalte C zutrifft
• wieviele Spalten Sie einbeziehen und wieviele Bedingungen pro Spalte, ist Ihnen freigestellt

Hier habe ich die Kriterien in die Formel aufgenommen, Sie können aber auch hier wie oben dargestellt über Hilfszellen oder Namensdefinition vorgehen.

A B C D E 1           2   Land Punkte Hilfsspalte   3   USA 80 WAHR   4   Italien 30 FALSCH   5   Deutschland 60 FALSCH   6   Spanien 70 FALSCH   7   Irland 50 WAHR   8   GB 20 FALSCH   9   Polen 60 WAHR   10   USA 40 FALSCH   11   Deutschland 10 FALSCH   12   GB 50 FALSCH   13           Formeln der Tabelle D3 : =UND(ODER(B3="USA";B3="Irland";B3="Polen");C3>40)

Die Formel aus Zelle D3 kann runterkopiert werden.