FILTER: eine Formel, die beim nächsten Import mitwächst
FILTER liefert aus einer einzigen Zelle heraus alle passenden Zeilen, und das Ergebnis wächst beim nächsten Import von selbst mit, solange ihm im Blatt nichts im Weg steht.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Der Autofilter erledigt den Moment, nicht die Wiederholung
Jeden Monat kommt derselbe Export, jemand setzt den Autofilter auf die Region, markiert die Treffer, kopiert sie in ein neues Blatt und formatiert nach. Vier Wochen später beginnt dieselbe Runde von vorn, und im Ordner liegen inzwischen sechs Blätter, deren Datenstand niemand mehr sicher zuordnen kann.
Dazu kommt ein Nebeneffekt, der in gemeinsam genutzten Dateien regelmäßig für Rückfragen sorgt: Ein gesetzter Autofilter blendet Zeilen in der Originaltabelle aus und verändert damit die Ansicht für alle mit, die gleichzeitig in der Datei arbeiten. Wer nichts davon weiß, hält die ausgeblendeten Zeilen für gelöscht.
FILTER dreht das um, weil die Auswertung an anderer Stelle entsteht und die Quelle unberührt bleibt. Der Preis dafür sind zwei neue Themen: die Versionsfrage, denn dynamische Arrays gibt es erst ab Microsoft 365 sowie Excel 2021 und 2024, und der Überlaufbereich, der Platz braucht und ihn auch bekommen muss.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Formel im Detail
Aufbau der Formel
=FILTER(Matrix; Einschließen; [Wenn_leer]) - 01 Ein dynamisches Array
FILTERDie Formel steht in einer Zelle, das Ergebnis belegt viele. Excel nennt das den Überlaufbereich und zeichnet einen dünnen Rahmen darum, ändern lässt sich die Formel nur in der obersten linken Zelle.
- 02 Was ausgegeben wird
MatrixDer Bereich, dessen Zeilen im Ergebnis landen, ohne Überschriftenzeile. Gibst du mehrere nebeneinanderliegende Spalten an, läuft das Ergebnis auch nach rechts aus.
- 03 Die Prüfung
EinschließenEin Vergleich über eine Spalte mit genau so vielen Zeilen wie die Matrix, etwa B2:B500="Nord". Weicht die Zeilenzahl auch nur um die Überschrift ab, kommt #WERT!.
- 04 Nur zwei Trennstellen
;Weitere Bedingungen kommen nicht als zusätzliche Argumente dazu, sondern werden innerhalb der Prüfung verrechnet: (B2:B500="Nord")*(C2:C500>1000) verknüpft mit Und, ein Pluszeichen mit Oder.
- 05 Ohne Treffer
[Wenn_leer]Ohne dieses Argument liefert FILTER #KALK!, wenn keine einzige Zeile passt. Die eckigen Klammern zeigen zwar an, dass es optional ist, in einer geteilten Auswertung gehört es trotzdem gesetzt.
Wenn es nicht funktioniert
Das siehst du
In der Zelle steht #KALK! statt einer leeren Liste.
Warum
Keine Zeile passt auf die Bedingung, und das dritte Argument fehlt.
Was hilft
Wenn_leer setzen, etwa mit dem Text "keine Treffer", und nebenbei prüfen, ob das Kriterium genauso geschrieben ist wie in den Daten.
Das siehst du
#ÜBERLAUF!, obwohl der Zielbereich leer aussieht.
Warum
Im Überlaufbereich steht noch etwas, häufig nur ein einzelnes Leerzeichen oder ein Rest aus einer früheren Fassung der Tabelle.
Was hilft
Auf das Warndreieck neben der Zelle klicken und über den Eintrag zu den blockierenden Zellen springen, diese leeren und die Formel neu berechnen lassen.
Das siehst du
#ÜBERLAUF! in einer Zelle, die innerhalb einer formatierten Tabelle liegt.
Warum
Dynamische Arrays können nicht innerhalb einer mit Strg + T erzeugten Tabelle auslaufen.
Was hilft
Die Formel außerhalb der Tabelle setzen. Aus einer Tabelle heraus zu lesen ist dagegen der stabilere Weg, weil die Spaltennamen automatisch mitwachsen.
Das siehst du
#WERT! direkt nach dem Schreiben der Formel.
Warum
Matrix und Prüfbereich umfassen unterschiedlich viele Zeilen, meist weil einer der beiden die Überschriftenzeile einschließt.
Was hilft
Beide Angaben auf dieselben Zeilen setzen, also zum Beispiel A2:D500 und B2:B500.
Das siehst du
Bei einem Kollegen steht #NAME? in der Zelle, bei dir läuft alles.
Warum
In Excel 2019 und älter gibt es die dynamischen Arrays nicht, im Funktionsnamen erscheint dort der Vorsatz _xlfn.
Was hilft
Für Dateien, die nach außen gehen, beim Autofilter oder einer Pivot-Tabelle bleiben und FILTER auf interne Auswertungen beschränken.
Sechs Bausteine einer Auswertung, die sich selbst pflegt
- 01 Die Quelle wird mit Strg + T zur Tabelle und wächst beim Import mit.
- 02 FILTER holt die passenden Zeilen an eine freie Stelle im Blatt.
- 03 Das dritte Argument fängt den Fall ohne einen einzigen Treffer ab.
- 04 SORTIEREN legt sich außen herum und bestimmt die Reihenfolge.
- 05 EINDEUTIG erzeugt daraus die Auswahlliste für die Datenüberprüfung.
- 06 Der Bezug mit Rautezeichen hält Summen und Nummerierung aktuell.
Was du danach einmal baust statt jeden Monat neu
Eine Auswertung, die sich selbst pflegt, besteht aus drei Teilen: einer Quelle, die mitwächst, einer Formel, die daraus liest, und einem Zielbereich, in dem das Ergebnis auslaufen darf. Wenn diese drei stimmen, brauchst du beim nächsten Import nichts anzufassen.
Bedingungen mit Rechenzeichen
Mehrere Bedingungen verknüpfst du nicht mit UND und ODER, sondern mit Sternchen für Und und Pluszeichen für Oder. Jede Teilbedingung gehört dabei in eigene Klammern.
Den leeren Fall abfangen
Ohne drittes Argument liefert FILTER #KALK!, sobald keine Zeile passt. In einer Auswertung, die andere benutzen, gehört dort ein sprechender Text hinein.
Auf das ganze Ergebnis verweisen
Steht die Formel in F2, meint F2# die komplette Ausgabe. =ZEILEN(F2#) zählt die Treffer, und eine Summe über diesen Bezug stimmt auch dann noch, wenn beim nächsten Import Zeilen dazukommen.
Sortieren und Dubletten entfernen
SORTIEREN legt sich außen um FILTER und bringt die Reihenfolge, EINDEUTIG liefert die Liste der vorkommenden Werte. Das dritte Argument von EINDEUTIG zeigt nur die Werte, die genau einmal auftauchen.
Den Zielbereich freihalten
Verbundene Zellen, Reste aus früheren Fassungen und selbst ein einzelnes Leerzeichen blockieren den Überlauf. Halte die Fläche, in der das Ergebnis landet, von Formatierungsspielereien frei.
Auswahllisten mitwachsen lassen
Ein Bezug mit Rautezeichen funktioniert auch als Quelle unter Daten > Datentools > Datenüberprüfung. Damit ergänzt sich das Auswahlfeld, sobald eine neue Region in den Daten auftaucht.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Wie FILTER aufgebaut ist
FILTER erwartet die Matrix, also den auszugebenden Bereich, und eine Prüfung mit derselben Zeilenzahl. =FILTER(A2:D500;B2:B500="Nord") gibt alle Spalten von A bis D für die Region Nord aus. Haben Matrix und Prüfbereich unterschiedlich viele Zeilen, kommt #WERT!, und zwar auch dann, wenn der Unterschied nur eine Kopfzeile beträgt.
Mehrere Bedingungen verknüpfst du mit Rechenzeichen statt mit UND und ODER, weil UND das Ergebnis zu einem einzigen Wahrheitswert zusammenfassen würde. Das Sternchen wirkt als Und, das Pluszeichen als Oder: =FILTER(A2:D500;(B2:B500="Nord")*(C2:C500>1000)). Jede Teilbedingung gehört dabei in eigene Klammern.
Findet FILTER keinen einzigen Treffer, liefert er #KALK!. Das dritte Argument fängt genau diesen Fall ab: =FILTER(A2:D500;B2:B500=F1;"keine Treffer"). Bei einer Auswertung, die auch andere Leute benutzen, solltest du dieses Argument grundsätzlich setzen.
Der Überlaufbereich und das Rautezeichen
Die Formel steht nur in einer Zelle, das Ergebnis belegt aber viele. Excel nennt das den Überlaufbereich und zeichnet einen dünnen blauen Rahmen darum, solange eine Zelle darin markiert ist. Klickst du in eine der ausgelaufenen Zellen, siehst du die Formel ausgegraut in der Bearbeitungsleiste, ändern lässt sie sich nur in der obersten linken Zelle.
Auf das gesamte Ergebnis verweist du mit einem Rautezeichen hinter der Adresse dieser Formelzelle. Steht die FILTER-Formel in F2, dann meint F2# die komplette Ausgabe. =ZEILEN(F2#) sagt dir, wie viele Treffer es gerade sind, und eine Summe über eine ausgelaufene Spalte stimmt auch dann noch, wenn beim nächsten Import dreißig Zeilen dazukommen.
Auch die Datenüberprüfung nimmt einen solchen Bezug an. Wenn du mit EINDEUTIG eine Liste der vorkommenden Regionen erzeugst und diese unter Daten > Datentools > Datenüberprüfung als Quelle mit Rautezeichen angibst, ergänzt sich das Auswahlfeld von selbst, sobald eine neue Region in den Daten auftaucht.
#ÜBERLAUF! und wo er herkommt
#ÜBERLAUF! heißt, dass dem Ergebnis der Platz fehlt. Meist steht irgendwo im Zielbereich noch etwas, oft nur ein einzelnes Leerzeichen oder ein Rest aus einer früheren Fassung der Tabelle. Klick auf das Warndreieck neben der Zelle, dort steht der Grund, und über den Eintrag zum Auswählen der blockierenden Zellen springt Excel direkt hin.
Der zweite häufige Grund: Dynamische Arrays können nicht innerhalb einer mit Strg + T erzeugten Tabelle auslaufen. Setz die Formel außerhalb der Tabelle. Aus einer Tabelle heraus lesen darfst du problemlos, ihre Spaltennamen als Bezug zu verwenden ist sogar der stabilere Weg.
Auch verbundene Zellen im Zielbereich blockieren den Überlauf. Wenn du eine Auswertungsfläche baust, in der FILTER-Ergebnisse landen sollen, halte diesen Bereich von Formatierungsspielereien frei, sonst suchst du den Fehler in der Formel, obwohl er im Layout sitzt.
SORTIEREN, EINDEUTIG und SEQUENZ im Zusammenspiel
SORTIEREN bringt ein Ergebnis in Reihenfolge, der Sortierindex ist dabei die Spaltennummer innerhalb der übergebenen Matrix, und -1 als Reihenfolge sortiert absteigend. Verschachtelt ergibt das eine fertige Auswertung: =SORTIEREN(FILTER(A2:D500;B2:B500="Nord");4;-1) liefert die Nordumsätze mit dem größten Wert zuerst.
EINDEUTIG entfernt Dubletten. In SORTIEREN eingebettet, liefert es eine gepflegte Liste aller vorkommenden Werte einer Spalte, etwa mit =SORTIEREN(EINDEUTIG(B2:B500)). Das dritte Argument von EINDEUTIG kehrt die Logik um und gibt nur die Werte zurück, die genau einmal vorkommen, womit sich Ausreißer und Tippfehler in Stammdaten aufspüren lassen.
SEQUENZ erzeugt eine fortlaufende Zahlenreihe. Für eine Nummerierung, die immer zur Trefferzahl passt, schreibst du =SEQUENZ(ZEILEN(F2#)) neben die FILTER-Ausgabe. Dann stimmt die laufende Nummer auch nach dem nächsten Import, ohne dass jemand eine Spalte nachziehen muss.
Was in älteren Versionen passiert
In Excel 2019 und älter gibt es die dynamischen Arrays nicht. Öffnest du dort eine Datei mit FILTER, steht in der Zelle ein Funktionsname mit dem Vorsatz _xlfn und das Ergebnis lautet #NAME?. Die zuletzt berechneten Werte sind zwar noch als gespeichertes Ergebnis vorhanden, sobald aber neu gerechnet wird, bleibt der Fehler stehen.
Ein zweiter Stolperstein betrifft den umgekehrten Weg. Ältere Formeln, die früher mit Strg + Umschalt + Enter als Matrixformel abgeschlossen wurden, funktionieren in aktuellen Versionen weiter, brauchen die Tastenkombination aber nicht mehr. Wenn du beim Öffnen einer alten Datei mitten in einer Formel ein @ findest, ist das der Operator für die implizite Schnittmenge, den Excel beim Umwandeln einsetzt, damit sich das Verhalten der Formel nicht ändert.
Für Dateien, die nach außen gehen, gilt dieselbe Regel wie bei XVERWEIS: Wenn du die Version des Empfängers nicht kennst, bleib beim Autofilter oder bei einer Pivot-Tabelle. Für interne Auswertungen, bei denen alle auf demselben Stand arbeiten, sparen die dynamischen Arrays eine Menge Nacharbeit.
Dazu passende Kurse
Neue Excel-Funktionen im Seminar kennenlernen kannst du bei cmt, dort geht es auch um das, was seit der letzten Version dazugekommen ist.
Steht im Unternehmen gerade der Wechsel auf eine neue Version an, sind die Umstiegskurse auf Office 2024 der passendere Einstieg.
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Der passende Lernpfad
Wenn du nicht nur ein Thema abhaken, sondern eine Rolle ausfüllen willst, zeigt dir der Lernpfad die Kurse in der Reihenfolge, in der sie aufeinander aufbauen.
Excel Allrounder
Excel halbwegs zu können, das reicht heute nicht mehr. Entweder du beherrschst es – oder du verlierst täglich Zeit, Überblick und jede Menge Effizienz.
Dieser Lernpfad macht dich zur verlässlichen Excel-Anlaufstelle im Team: schneller arbeiten, sauber analysieren, überzeugend präsentieren. Warte nicht, bis andere den Job besser machen, nur weil sie Excel professionell nutzen. Steig jetzt ein – und mach aus Excel dein echtes Allround-Werkzeug.
Der Einstieg
Sehr kompetenter Dozent mit einem intensiven Training welches anwendungsorientiert ist.
Das doch recht "langweilige" Thema Excel, wurde einem sehr gut präsentiert. Durch direktes mitarbeiten wurde es einem nie langweilig.
Sehr gut vorbereitetes Seminar mit hilfreichen Informationen und anschaulicher Vermittlung. Vielen Dank!
Häufige Fragen
Was ist der Unterschied zum normalen Autofilter?
Kann ich mit FILTER auch nur bestimmte Spalten ausgeben?
Warum steht bei mir #KALK! statt einer leeren Liste?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Excel-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Vom monatlichen Kopieren zur Auswertung, die sich selbst pflegt
Die dynamischen Arrays und ihr Zusammenspiel mit Tabellen und Pivot gehören bei cmt zu den Aufbauthemen der Excel-Kurse.