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.
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 | | | | | | |
|
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.
Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben