Autor: Beate Schmitz  --- Aus Excel Standard - Gruppe: Spezielle Menübefehle

Autofilter

Autor: Beate Schmitz - Erstellt: 2006-04      - Letzte Revision: ?
Autofilter - Grundlagen

Mit dem Autofilter können Listen nach bestimmten Kriterien gefiltert werden, so dass die Zeilen, die diese Kriterien nicht erfüllen, ausgeblendet werden. Sie können in unbegrenzt vielen Spalten auch gleichzeitig filtern. Interessant sind die Möglichkeiten des benutzerdefinierten Filterns sowie des Top-10-Autofilters. Beides wird nachstehend erklärt.



Der Einbau des Autofilters
  • die Liste sollte wie hier beschrieben aufgebaut sein
  • dann markieren Sie die Überschriftenzeile der Liste mit der Maus, indem Sie auf die Zeilenüberschrift (links von Spalte A die Zeilennummer) klicken
  • gehen Sie nun über Menü > Daten > Filter > Autofilter
  • danach haben alle Zellen der Überschriftenzeile einen Dropdownpfeil rechts vom Text
  • und über das Anklicken dieser Pfeile können Sie in den einzelnen Spalten filtern

Ab Excel 2007:
Register Daten -> Gruppe Sotieren und Filtern -> Dort das Icon Filtern.
Zum aufheben des Autofilter wieder auf dieses Icon klicken.



Hier sehen Sie eine Tabelle, die in der Spalte Artikel den Autofilter aufgeklappt hat. Und an diesem Muster werde ich meine nachstehenden Erklärungen aufhängen:



Wie sie sehen, zeigt das Autofilterdropdown die Kriterienauswahl aufsteigend sortiert an. Wobei Zahlen vor Buchstaben angezeigt werden. Diese Reihenfolge kann man nicht ändern.

Ist die Kriterienzahl sehr groß, erscheint rechts eine Bildlaufleiste, über die Sie zum gewünschten Kriterium scrollen können.

In gefilterten Spalten verändert der Autofilterpfeil seine Farbe und wird blau. Daran können wir erkennen, welche Spalten einen aktiven Filter haben. In der Statuszeile unten links wird uns angezeigt, wieviele von wieviel Datensätzen z.Zt. angezeigt werden. Also auch ein Hinweis auf gesetzten Filter; ist die gefilterte Tabelle sehr umfangreich, dann erscheint das jedoch nicht in der Statusleiste. Wenn Sie ohne Prüfung sicher gehen möchten, dass die Liste ungefiltert ist, dann heben Sie alle gesetzten Autofilter gleichzeitig auf.


Der Autofilter hat Beschränkungen, die über Umwege ausgehebelt werden können. Diese Beschränkungen sind:
  • es können max. 2 UND/ODER Kriterien pro Spalte gefiltert werden. Zur Problemlösung
  • im Autofilterdropdown werden nur bis zu 1.000 verschiedene Kriterien angezeigt. Zur Problemlösung

Eine Möglichkeit, schnell immer nach gleichen einmal gespeicherten Kriterien zu filtern, bietet der Einsatz des Autofilters als benutzerdefinierte Ansicht.


Wissenswertes zum Autofilter: Wem das nicht reicht, sollte sich den Spezialfilter näher betrachten:
 
Spezialfilter (1)
Duplikate wegfiltern
Spezialfilter (2)
Filtern nach Kriterien
Spezialfilter (3)
Filtern mit Funkionen
Spezialfilter (4)
Filterergebnis in ein anderes Tabellenblatt
 


Zum Blattanfang

Autofilter "normal"

Klicken Sie in der Spalte, die Sie filtern möchten, auf den Dropdownpfeil und die Kriterien werden Ihnen angezeigt. Klicken Sie das gewünschte Kriterium an, damit startet der Filtervorgang. Alle Zeilen, die dem Kriterium in der Spalte nicht entsprechen, werden ausgeblendet. Sie können nun die Auswahl weiter einschränken, indem Sie auch noch andere Spalten filtern.

Beispiel: Hier wurde die Spalte Artikel nach dem Kriterium "Platte" gefiltert:
 
 ABCDE
1     
2 ArtikelFarbePreis-Kategorie 
5 Plattelila70 
13 Platteorange58 
37     
 


Zum Blattanfang

Autofilter benutzerdefiniert mit einem Kriterium

Wie Sie oben am aufgeklappten Autofilter erkennen können, bietet Excel auch die Möglichkeit, benutzerdefiniert zu filtern. Diese Möglichkeit wird gewählt, wenn man nicht einen einzigen bestimmten Wert sucht, sondern z.B. Werte die größer sind als ein bestimmter Wert oder Zellen die bestimmten Text enthalten. Wählen Sie im Autofilterdropdown benutzerdefiniert aus und das Dialogfenster benutzerdefinierter Autofilter springt auf. Es werden uns folgende Möglichkeiten dort wieder per Dropdown angeboten:
 
entsprichtist kleiner alsEndet mit
entspricht nichtist kleiner oder gleichEndet nicht mit
ist größer alsBeginnt mitEnthält
ist größer oder gleichBeginnt nicht mitEnthält nicht
 

Im rechten Dropdownfeld können wir nun den Wert aussuchen, auf den diese Bedingung angewandt werden soll. Sie können auch direkt in dieses Textfeld hineinschreiben und dabei zusätzlich die Platzhalter * und ? verwenden.

Beispiel: Hier wurde die Spalte Preiskategorie nach dem Kriterium "ist größer als: 60" gefiltert:
 
 ABCDE
1     
2 ArtikelFarbePreis-Kategorie 
4 Becherweiß75 
5 Plattelila70 
8 Becherlila66 
12 Tasserose76 
14 Tassegelb68 
37     
 

Beispiel: Hier wurde die Spalte Artikel nach dem Kriterium "entspricht: ?a*" gefiltert, damit sollen alle Artikel ausgewiesen werden, die an 2. Stelle ein "a" haben:
 
 ABCDE
1     
2 ArtikelFarbePreis-Kategorie 
7 Tasseorange60 
10 Kanneweiß19 
11 Tasseschwarz36 
12 Tasserose76 
14 Tassegelb68 
15 Kanneweiß39 
37     
 


Zum Blattanfang

Autofilter benutzerdefiniert mit zwei Kriterien UND/ODER

Bevor Sie hier einsteigen, sollten Sie die Grundlagen zum benutzerdefierten Einsatz des Autofilters kennen.

Sie können eine Spalte nach einem Kriterium ausfiltern oder auch zwei Kriterien, da wiederum können Sie variieren ob Sie nur Ergebnisse zulassen wollen, die beide (UND) oder nur ein Kriterium (ODER) erfüllen.

Ob Sie sich für UND oder ODER entscheiden, teilen Sie Excel durch Klick auf das entsprechende Optionsfeld im Dialogfenster benutzerdefinierter Autofilter mit.

Beispiel: Hier wurde die Spalte Preiskategorie nach dem Kriterium "entspricht: 70" ODER "ist kleiner als: 30" gefiltert:
 
 ABCDE
1     
2 ArtikelFarbePreis-Kategorie 
5 Plattelila70 
9 Korbgelb7 
10 Kanneweiß19 
37     
 

Hätten wir hier UND gewählt, hätte das Filtern keine Treffer gebracht, weil keine der Zellen beiden Kriterien entpricht.

Zum Blattanfang

Autofilter Top 10 - kann noch mehr

Vorab: Diese Filtermöglichkeit kann nur auf Spalten angewandt werden, die Werte enthalten.

Wir haben im Autofilterdropdown die Möglichkeit, die Top 10 auszuwählen. Dann springt das Dialogfenster Top-10-Autofilter auf.

Diese Bezeichnung ist eher irritierend. Weil viel mehr Möglichkeiten bestehen!

Im Dialogfenster Top-10-Autofilter können wir an 3 Stellen über Dropdown wählen:
  • ob wir die obersten oder untersten Werte der Spalte ausfiltern möchten
  • auch an die Zahl 10 sind wir nicht gebunden, die können wir erhöhen oder reduzieren
  • und wir können wählen, ob uns z.B. die obersten/untersten x Werte oder alle Zeilen, die zu den obersten/untersten x % gehören, angezeigt werden sollen
Beispiel: Hier wurde die Spalte Preiskategorie nach den obersten 5 Werten als Element gefiltert:
 
 ABCDE
1     
2 ArtikelFarbePreis-Kategorie 
4 Becherweiß75 
5 Plattelila70 
8 Becherlila66 
12 Tasserose76 
14 Tassegelb68 
37     
 

Beispiel: Hier wurde die Spalte Preiskategorie nach den obersten 5 % gefiltert:
 
 ABCDE
1     
2 ArtikelFarbePreis-Kategorie 
12 Tasserose76 
37     
 

Sie sehen an diesen beiden Beispielen, welch großen Unterschied die Einstellungen im Filterergebnis machen.

Zum Blattanfang

Umgang mit gefilterten Listen: Kopieren und nach unten ausfüllen

Das Kopieren gefilterter Listen ist denkbar einfach; markieren Sie mit der Maus den gewünschten Bereich und fügen ihn an anderer Stelle ein. Es werden nur die im Filter sichtbaren Zeilen berücksichtigt.

Auch wenn Sie Zellen in gefilterten Listen markieren und nach unten ausfüllen, werden nur die sichtbaren Zellen durch den Inhalt der obersten markierten Zelle überschrieben. Die ausgeblendeten Zellen werden hiervon nicht berührt.

Zum Blattanfang

Autofilter aufheben oder entfernen

 
Autofilter in einer Spalte aufheben:klicken Sie auf den Dropdownpfeil der betreffenden Spalte in der Überschriftenzeile und wählen im Dropdown Alle aus
alle gesetzten Autofilter gleichzeitig aufheben:gehen Sie über Menü > Daten > Filter > alle anzeigen
Autofilter komplett entfernen:gehen Sie über Menü > Daten > Filter  und nehmen den Haken vor Autofilter weg. Sollte die Liste gerade gefiltert sein, werden damit auch alle Zeilen wieder eingeblendet
 


Zum Blattanfang

Autofilter auswerten mit der Funktion TEILERGEBNIS

Eine wichtige Funktion zur Auswertung von Autofilterergebnissen ist TEILERGEBNIS. Diese Funktion kann man durch Einsatz bestimmter Kennziffern zu bestimmten Auswertungen der eingeblendeten Zeilen (= Kriterientreffer) heranziehen.

Die gebräuchlisten TEILERGEBNIS-Unterfunktionen sind in Verbindung mit Autofilter:
  • um die Anzahl der Filterergebnisse festzustellen: =TEILERGEBNIS(3;Bereich)
  • um die Summe der Filterergebnisse festzustellen: =TEILERGEBNIS(9;Bereich)
Beispiel: Hier wurde die Spalte Artikel die Anzahl der Filterergebnisse ausgewertet und in der Spalte Preiskategorie die Summe der Filterergebnisse:
 
 ABCDE
1     
2 Anzahl: Summe: 
3 2 118 
4 ArtikelFarbePreis-Kategorie 
9 Tasseorange60 
15 Platteorange58 
18     
Formeln der Tabelle
B3 : =TEILERGEBNIS(3;B5:B17)
D3 : =TEILERGEBNIS(9;D5:D17)
 


Zum Blattanfang

Zeilen der Tabelle abwechselnd einfärben - mit Wirkung auf gefilterte Liste

Über bedingte Formatierung können Sie erreichen, dass die Zeilen der Liste im gefilterten wie im ungefilterten Zustand abwechselnd farbig unterlegt sind. Das hilft, gerade bei großen Listen, die Zeilen besser im Auge halten zu können.

  • markieren Sie dazu die Datensätze Ihrer Liste und gehen Sie über Menü > Format > bedingte Formatierung...
  • im Dialogfenster wählen Sie im Dropdown unter Bedingung1 Formel ist aus
  • und im Textfeld daneben geben Sie folgende Formel ein: =REST(TEILERGEBNIS(3;$B$3:$B3);2)=0
  • die in der Formel genannte Zelle muss die aktive Zelle Ihres markierten Datenbereichs sein (sie wird Ihnen im Namenfeld angezeigt) - achten Sie auch auf die korrekte Absolutierung
  • wählen Sie über die Schaltfläche Format im Registerblatt Muster die gewünschte Hintergrundfarbe aus, die dann in jeder zweiten sichtbaren Zeile erscheinen wird
Beispiel: Hier sehen Sie unsere Beispieltabelle so aufgerüstet im gefilterten Zustand - die Zeilen abwechselnd gefärbt:
 
 ABCDE
1     
2 ArtikelFarbePreis-Kategorie 
7 Tasseorange60 
11 Tasseschwarz36 
12 Tasserose76 
14 Tassegelb68 
16     
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B71. / Formel ist =REST(TEILERGEBNIS(3;$B$3:$B7);2)=0Abc
 


Zum Blattanfang

Ist das Autofiltern für Excel ein Ereignis, mit dem ein Makro gestartet werden kann?

Diese Frage kann mit einem klaren NEIN beantwortet werden. Sie können aber z.B. durch den Einbau der TEILERGEBNIS-Funktion durch das Filtern eine Neuberechnung auslösen und ein Worksheet_Calculate Makro dadurch starten und auf diesem Weg Ihr Ziel erreichen.

Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Weitere Artikel der Gruppe: Spezielle Menübefehle Aus Excel Standard
Nach oben