Autor: Beate Schmitz  --- Aus Excel Standard - Gruppe: Häufige Fragen

Autofilter mit mehr als 2 Bedingungen in Spalte

Autor: Beate Schmitz - Erstellt: 2006-02      - Letzte Revision: ?
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 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 aufnehmendie 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:
 
 ABCDEFGHI
1         
2 LandBeginnt mitBeginnt nicht mitEndet mitEndet nicht mitEnthältEnthält nicht 
3 UUAA*S**S* 
4 USAWAHRFALSCHWAHRFALSCH10 
5 ItalienFALSCHWAHRFALSCHWAHR01 
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
 
 ABCDE
1     
2 LandPunkteHilfsspalte 
3 USA80WAHR 
4 Italien30FALSCH 
5 Deutschland60FALSCH 
6 Spanien70FALSCH 
7 Irland30WAHR 
8 GB20FALSCH 
9 Polen60WAHR 
10 USA40WAHR 
11 Deutschland10FALSCH 
12 GB50FALSCH 
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.
 
 ABCDEFGHI
1         
2     Land1Land2Land3 
3 LandPunkteHilfsspalte USAIrlandPolen 
4 USA80WAHR     
5 Italien30FALSCH     
6 Deutschland60FALSCH     
7 Spanien70FALSCH     
8 Irland30WAHR     
9 GB20FALSCH     
10 Polen60WAHR     
11 USA40WAHR     
12 Deutschland10FALSCH     
13 GB50FALSCH     
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:
 
 ABCDE
1     
2 LandPunkteHilfsspalte 
3 USA80WAHR 
4 Italien30FALSCH 
5 Deutschland60FALSCH 
6 Spanien70WAHR 
7 Irland30FALSCH 
8 GB20FALSCH 
9 Polen60FALSCH 
10 USA40FALSCH 
11 Deutschland10WAHR 
12 GB50WAHR 
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.

 
 ABCDE
1     
2 LandPunkteHilfsspalte 
3 USA80WAHR 
4 Italien30FALSCH 
5 Deutschland60FALSCH 
6 Spanien70FALSCH 
7 Irland50WAHR 
8 GB20FALSCH 
9 Polen60WAHR 
10 USA40FALSCH 
11 Deutschland10FALSCH 
12 GB50FALSCH 
13     
Formeln der Tabelle
D3 : =UND(ODER(B3="USA";B3="Irland";B3="Polen");C3>40)
 

Die Formel aus Zelle D3 kann runterkopiert werden.



Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben
rechte seite