FILTER, SORTIEREN und EINDEUTIG: dynamische Arrays in Excel

Kurz gesagt: =FILTER(A2:C8;C2:C8>=1000) holt alle passenden Zeilen, =SORTIEREN(A2:C8;3;-1) sortiert absteigend nach der dritten Spalte, =EINDEUTIG(A2:A8) entfernt Doppelte. Die Formel steht nur in einer Zelle, das Ergebnis läuft über. Diese Funktionen gibt es erst ab Excel 2021 und in Microsoft 365.

Probier es gleich aus

In Spalte A stehen Städte, manche mehrfach. Gesucht ist eine Liste, in der jede Stadt nur einmal vorkommt. Klick in die gelbe Zelle und schreib die Formel. Das Ergebnis füllt die markierten Zellen darunter.

Aufgabe Schreib in E2 eine Formel, die jede Stadt aus A2:A8 genau einmal auflistet. Das Ergebnis läuft automatisch nach unten über.

	A	B	C	D	E
1	Stadt	Kunde	Umsatz €		Städte
2	Berlin	Kaya GmbH	1200
3	Hamburg	Nordwerk	800
4	Berlin	Weber & Söhne	450
5	Leipzig	Sachsenbau	990
6	Hamburg	Hafenlogistik	300
7	Dresden	Elbtal AG	1500
8	Berlin	Spree Media	700

Was heißt Überlaufen?

Klassische Formeln liefern genau einen Wert für genau eine Zelle. Die neuen Funktionen rechnen mit ganzen Bereichen und geben einen Bereich zurück. Excel nennt das dynamische Arrays: Du schreibst die Formel in E2, und das Ergebnis läuft nach unten und nach rechts in so viele Zellen wie nötig. Ändern sich die Daten, wächst oder schrumpft die Liste von selbst. Nur die erste Zelle enthält die Formel, die anderen Zellen zeigen sie grau in der Bearbeitungsleiste.

Steht im Weg schon etwas, und sei es ein Leerzeichen, zeigt Excel #ÜBERLAUF!. Die Lösung ist fast immer, die Zellen unter und neben der Formel frei zu räumen. Auch in einer formatierten Tabelle (Strg+T) läuft nichts über, dort gibt es ebenfalls #ÜBERLAUF!.

So ist FILTER aufgebaut

Argument	Bedeutung	Im Beispiel
Matrix	Der Bereich, der zurückkommen soll. Wie viele Spalten er hat, so viele Spalten hat auch das Ergebnis.	A2:C8
einschließen	Eine Bedingung, die für jede Zeile WAHR oder FALSCH ergibt. Sie muss genauso viele Zeilen haben wie die Matrix.	C2:C8>=1000
wenn_leer	Optional: Was angezeigt wird, wenn keine Zeile passt. Ohne dieses Argument kommt #KALK!.	"keine Treffer"

Mit allen drei Argumenten lautet die Formel =FILTER(A2:C8;C2:C8>=1000;"keine Treffer"). Probier das jetzt selbst.

Noch eine Übung: alle großen Aufträge

Diesmal kommen ganze Zeilen mit drei Spalten zurück. Achte darauf, dass auch ein Umsatz von genau 1000 € zählt.

Aufgabe Schreib in E2 eine Formel, die alle Zeilen aus A2:C8 mit einem Umsatz von mindestens 1000 € zurückgibt, mit allen drei Spalten. Der Bereich E2:G8 ist dafür frei.

	A	B	C	D	E	F	G
1	Kunde	Stadt	Umsatz €		Kunde	Stadt	Umsatz €
2	Kaya GmbH	Berlin	1450
3	Nordwerk	Hamburg	820
4	Weber & Söhne	Frankfurt	1000
5	Sachsenbau	Leipzig	640
6	Hafenlogistik	Bremen	2300
7	Elbtal AG	Dresden	980
8	Spree Media	Berlin	1200

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

FILTER kennt kein UND() und ODER() im üblichen Sinn, weil diese Funktionen nur einen einzigen Wert zurückgeben. Stattdessen setzt du jede Bedingung in Klammern und verknüpfst sie rechnerisch. WAHR zählt als 1, FALSCH als 0.

• UND mit Malzeichen: =FILTER(A2:C8;(B2:B8="Berlin")*(C2:C8>=1000)) liefert nur Berliner Kunden mit mindestens 1000 €. Nur wenn beide Bedingungen 1 sind, ist das Produkt 1.
• ODER mit Pluszeichen: =FILTER(A2:C8;(B2:B8="Berlin")+(B2:B8="Hamburg")) liefert alle Kunden aus Berlin oder Hamburg. Sobald eine Bedingung 1 ist, ist die Summe größer als 0.

Die Klammern um jede Bedingung sind Pflicht. Ohne sie rechnet Excel zuerst Mal oder Plus und vergleicht danach.

SORTIEREN und SORTIERENNACH

=SORTIEREN(Matrix;Sortierindex;Sortierreihenfolge) gibt den Bereich sortiert zurück. Der Sortierindex sagt, nach welcher Spalte des Bereichs sortiert wird, gezählt ab der ersten Spalte. Die Reihenfolge ist 1 für aufsteigend (Standard) und -1 für absteigend. =SORTIEREN(A2:C8;3;-1) sortiert also nach Umsatz, größter zuerst.

SORTIERENNACH sortiert einen Bereich nach einem anderen Bereich, der nicht im Ergebnis stehen muss: =SORTIERENNACH(A2:A8;C2:C8;-1) zeigt nur die Kundennamen, sortiert nach Umsatz. Die Originaldaten bleiben bei beiden Funktionen unverändert, anders als beim Sortieren über das Menü.

EINDEUTIG und Kombinationen

EINDEUTIG entfernt Doppelte und behält die Reihenfolge des ersten Auftretens. Richtig stark werden die Funktionen, wenn du sie ineinander steckst:

• =SORTIEREN(EINDEUTIG(A2:A100)) liefert eine alphabetische Liste aller Städte, ideal als Grundlage für Auswertungen oder Dropdowns.
• =SORTIEREN(FILTER(A2:C8;C2:C8>=1000);3;-1) holt die großen Aufträge und sortiert sie nach Umsatz.
• =ANZAHL2(EINDEUTIG(A2:A100)) zählt, wie viele verschiedene Städte es gibt.

Auf das Ergebnis verweisen: E2#

Ein Überlaufbereich hat keine feste Größe. Damit andere Formeln trotzdem immer genau das ganze Ergebnis erfassen, hängst du ein Rautezeichen an die Startzelle: E2# steht für den kompletten Bereich, der von E2 aus überläuft. =ANZAHL2(E2#) zählt die Einträge, =SUMMEWENN(A2:A8;E2#;C2:C8) berechnet mit den Daten der ersten Übung den Umsatz für jede Stadt der Liste und läuft selbst wieder über.

Typische Fehler

#ÜBERLAUF!

Im Zielbereich steht schon etwas. Klick auf die Fehlerzelle, Excel umrandet den benötigten Bereich gestrichelt. Räum ihn frei oder setz die Formel an eine andere Stelle.

#KALK! bei FILTER

Keine Zeile erfüllt die Bedingung. Das ist kein Formelfehler, sondern ein leeres Ergebnis. Gib als drittes Argument einen Text wie "keine Treffer" an.

#WERT! bei FILTER

Matrix und Bedingung sind unterschiedlich lang, etwa A2:C8 mit C2:C9. Beide müssen dieselben Zeilen abdecken.

#NAME?

Deine Excel-Version kennt die Funktion nicht. Das passiert in Excel 2019 und älter, auch wenn jemand dir eine Datei mit diesen Formeln schickt.

FILTER, SORTIEREN und EINDEUTIG auf Englisch

Deutsch	Englisch
=FILTER(A2:C8;C2:C8>=1000)	=FILTER(A2:C8,C2:C8>=1000)
SORTIEREN	SORT
SORTIERENNACH	SORTBY
EINDEUTIG	UNIQUE
#ÜBERLAUF! / #KALK!	#SPILL! / #CALC!

In welchen Excel-Versionen gibt es diese Funktionen?

FILTER, SORTIEREN, SORTIERENNACH und EINDEUTIG gibt es in Excel 2021, Excel 2024 und Microsoft 365, jeweils auch auf dem Mac. In Excel 2019 und 2016 fehlen sie. Dort erreichst du das Gleiche über Menüs: Daten > Sortieren und Daten > Filtern für den AutoFilter, Daten > Erweitert (Spezialfilter) mit „Keine Duplikate“ für eine Liste ohne Doppelte, oder Daten > Duplikate entfernen. Der Unterschied: Das Ergebnis aktualisiert sich nicht von selbst. Wie das geht, zeigt die Seite Sortieren und Filtern.

Häufige Fragen