FILTER-Funktion in Excel: Listen per Formel filtern

FILTER-Funktion oder AutoFilter: was ist der Unterschied?

Der AutoFilter unter Daten › Filtern blendet Zeilen in deiner Liste aus. Du siehst danach weniger, aber es ist immer noch dieselbe Liste. Die FILTER-Funktion arbeitet anders: Sie lässt das Original komplett in Ruhe und schreibt die passenden Zeilen als Kopie an eine andere Stelle, zum Beispiel neben die Liste oder auf ein eigenes Blatt.

Das hat zwei Vorteile. Erstens rechnet die Kopie mit: Kommt eine neue Bestellung aus Mainz dazu oder ändert jemand einen Ort, ist das Ergebnis sofort aktuell. Niemand muss den Filter neu setzen. Zweitens kannst du mehrere Auswertungen gleichzeitig zeigen, etwa Mainz links und Wiesbaden rechts. Mit dem AutoFilter geht immer nur eine Sicht pro Liste.

Für einen schnellen Blick in eine Tabelle bleibt der AutoFilter trotzdem der bessere Weg. FILTER lohnt sich, sobald eine Auswertung dauerhaft stehen soll: eine Liste offener Posten für den Chef, die Kunden einer Region für den Außendienst, alle Bestellungen über einem Betrag.

Überlauf: eine Formel, viele Zellen

FILTER, EINDEUTIG und SORTIEREN gehören zu den dynamischen Arrays. Du tippst die Formel in eine einzige Zelle, und Excel füllt so viele Zellen darunter und daneben, wie das Ergebnis braucht. Das heißt Überlauf. Den Bereich erkennst du an einem dünnen blauen Rahmen, sobald du eine Zelle darin anklickst.

Die Formel steht nur in der ersten Zelle. Klickst du auf eine Zelle weiter unten, zeigt die Bearbeitungsleiste dieselbe Formel in Grau. Du kannst sie dort weder ändern noch einzeln löschen. Willst du das Ergebnis loswerden, löschst du die erste Zelle, dann verschwindet der ganze Bereich.

Auf einen Überlaufbereich verweist du mit einem Rautezeichen: =ANZAHL2(F2#) zählt alles, was die Formel in F2 ausgibt, egal ob es drei oder dreißig Zeilen sind. Das ist sicherer als ein fester Bereich wie F2:F20, der zu kurz oder zu lang sein kann.

#ÜBERLAUF! beheben

#ÜBERLAUF! heißt: Das Ergebnis hat keinen Platz. Irgendwo im Bereich, in den die Formel schreiben will, steht schon etwas. Oft ist es etwas Unsichtbares, ein Leerzeichen oder eine alte Formel, die "" liefert. Klick auf die Fehlerzelle und dann auf das kleine Warnsymbol daneben. Dort gibt es Blockierende Zellen auswählen. Excel markiert genau die Zelle, die stört. Leer machen, fertig.

Drei weitere Ursachen kommen seltener vor: Im Zielbereich sind verbundene Zellen, das mag der Überlauf nicht. Die Formel steht in einer Excel-Tabelle (Strg + T), dort sind Überlauf-Formeln nicht erlaubt. Schreib sie daneben in normale Zellen. Oder das Ergebnis würde über den Rand des Blatts hinausgehen, etwa wenn du eine ganze Spalte wie A:A sortierst und die Formel nicht in Zeile 1 steht.

Bevor du eine große FILTER-Formel einfügst, lass rechts und unterhalb genug Platz. Ein guter Ort ist ein eigenes Blatt nur für Auswertungen.

Mehrere Bedingungen: * für UND, + für ODER

Die naheliegende Idee =FILTER(A2:D9;UND(B2:B9="Mainz";D2:D9>=5)) funktioniert nicht. UND fasst alle Zeilen zu einem einzigen WAHR oder FALSCH zusammen, FILTER braucht aber eine Antwort pro Zeile. Stattdessen multiplizierst du die Bedingungen, jede in eigenen Klammern: =FILTER(A2:D9;(B2:B9="Mainz")*(D2:D9>=5)). Nur wo beide Bedingungen stimmen, ergibt das Produkt 1.

Für ODER addierst du: =FILTER(A2:D9;(B2:B9="Mainz")+(B2:B9="Bingen")) liefert alle Zeilen aus Mainz oder Bingen. Die Klammern um jede Bedingung sind Pflicht, sonst rechnet Excel in der falschen Reihenfolge. Wie UND und ODER sonst funktionieren, steht unter WENN mit mehreren Bedingungen.

EINDEUTIG: jeder Wert nur einmal

=EINDEUTIG(B2:B9) schreibt jeden Ort genau einmal untereinander, in der Reihenfolge, in der er zuerst auftaucht. Groß- und Kleinschreibung spielt keine Rolle, „Mainz“ und „MAINZ“ gelten als gleich. Gibst du mehrere Spalten an, etwa =EINDEUTIG(B2:C9), zählt die Kombination: Mainz mit Riesling und Mainz mit Silvaner sind dann zwei Zeilen.

Das dritte Argument ist wenig bekannt, aber praktisch: =EINDEUTIG(A2:A9;;WAHR) zeigt nur Werte, die genau einmal vorkommen. So findest du zum Beispiel Kunden, die nur ein einziges Mal bestellt haben.

Anders als Duplikate entfernen löscht EINDEUTIG nichts. Das Original bleibt vollständig, die Liste ohne Doppelte steht daneben und wächst mit.

SORTIEREN: Ergebnis gleich in Reihenfolge

=SORTIEREN(A2:D9;4;-1) gibt die ganze Liste nach der vierten Spalte absteigend aus, die größte Bestellung oben. Die 4 zählt innerhalb des Bereichs, nicht im Blatt. Ohne weitere Angaben sortiert SORTIEREN nach der ersten Spalte von A bis Z. Wie du die Liste selbst per Klick umsortierst, zeigt Excel sortieren.

Richtig stark werden die drei Funktionen zusammen. =SORTIEREN(EINDEUTIG(B2:B9)) ergibt eine alphabetische Liste aller Orte, ideal als Quelle für ein Dropdown. =SORTIEREN(FILTER(A2:D9;B2:B9="Mainz");4;-1) zeigt die Mainzer Kunden, größte Bestellung zuerst.

Es gibt noch SORTIERENNACH. Damit sortierst du nach einer Spalte, die im Ergebnis gar nicht auftaucht, etwa Kundennamen nach Kartons, ohne die Kartons anzuzeigen.

Eindeutige Werte zählen: ANZAHL2, nicht ANZAHL

Wie viele verschiedene Weine, Kunden oder Orte gibt es? =ANZAHL2(EINDEUTIG(C2:C9)). Der häufigste Fehler ist ANZAHL statt ANZAHL2. ANZAHL zählt nur Zahlen, und Namen sind Text. Das Ergebnis ist dann 0. Den Unterschied erklärt die Seite ANZAHL und ANZAHL2.

Vorsicht bei Lücken: Steht im Bereich eine leere Zelle, liefert EINDEUTIG dafür eine 0 als eigenen Eintrag, und die Zählung ist um eins zu hoch. Dann filterst du die Leeren vorher weg: =ANZAHL2(EINDEUTIG(FILTER(C2:C99;C2:C99<>""))).

Stolperfallen: alte Versionen, Nullen, ungleiche Bereiche

Öffnet jemand deine Datei in Excel 2019 oder älter, sieht er zunächst die zuletzt gespeicherten Werte. Sobald Excel neu rechnet, steht dort #NAME?, und in der Formel taucht ein _xlfn. vor dem Funktionsnamen auf. Die Funktionen gibt es dort einfach nicht. Wenn du Dateien an Kunden oder Behörden schickst, kopier das Ergebnis vorher und füge es als Werte ein.

Leere Zellen im Original erscheinen im Ergebnis als 0. Wer lieber leere Zellen sieht, hängt &"" an den Bereich: =FILTER(A2:D9&"";B2:B9="Mainz"). Damit wird allerdings alles zu Text, auch die Zahlen. Rechnen solltest du mit diesem Ergebnis dann nicht mehr.

Und noch ein Klassiker: Die Bedingung muss genauso lang sein wie die Matrix. =FILTER(A2:D9;B2:B10="Mainz") ergibt #WERT!, weil neun Bedingungen auf acht Zeilen treffen. Am sichersten ist eine Excel-Tabelle als Quelle, dann wachsen alle Bereiche gemeinsam.