Mit Array-Formeln kann eine einzige Formel mehrere Werte gleichzeitig verarbeiten und mehrere Ergebnisse zurückgeben. Vor Excel 365 waren Arrays eine Nischenfunktion für fortgeschrittene Nutzer, für die eine spezielle Tastenkombination erforderlich war. Dynamische Arrays in Excel 365 haben das geändert - Funktionen wie FILTER, SORTIEREN und EINDEUTIG arbeiten automatisch als Arrays und übertragen die Ergebnisse ohne zusätzliche Schritte in benachbarte Zellen.
Was sind Arrays in Excel?
Ein Array ist eine Sammlung von Werten, die innerhalb einer Formel als eine Einheit behandelt werden. Stell dir das so vor, als würde Excel eine Schleife für dich ausführen.
Beispiel ohne Arrays: Um nur Werte zu summieren, bei denen die Kategorie „Vertrieb“ übereinstimmt, würdest du schreiben: =SUMMEWENN(A2:A100;"Sales";B2:B100) So funktionieren Arrays im Hintergrund: SUMMEWENN multipliziert eine logische Bedingung (ist A = „Sales“?) mit den entsprechenden Werten in Spalte B und summiert die Ergebnisse - im Grunde eine Array-Operation, die die Funktion intern ausführt.Wenn du deine eigenen Array-Formeln schreibst, nutzt du denselben Mechanismus für Aufgaben, für die es keine integrierte Funktion gibt, die den Bedarf abdeckt.
Ältere CSE-Array-Formeln (Excel 2019 und früher)
Vor der Einführung dynamischer Arrays hast du Array-Formeln eingegeben, indem du Strg+Umschalt+Enter (CSE) statt nur Enter gedrückt hast. Excel hat die Formel in geschweifte Klammern {} gesetzt, um zu signalisieren, dass es sich um eine Array-Formel handelte.
Beispiel: Zähle die Zellen in B2:B100, die größer als 100 sind UND in Spalte A die Bezeichnung „Nord“ haben: {=SUM((A2:A100="North")*(B2:B100>100))}Die geschweiften Klammern werden von Excel automatisch hinzugefügt, wenn du Strg+Umschalt+Enter drückst - gib sie niemals manuell ein.
Wichtige Einschränkung: Eine CSE-Array-Formel belegt genau eine Zelle. Sie kann Ergebnisse nicht auf mehrere Zellen verteilen (das ist eine Funktion des dynamischen Arrays). Wenn du immer noch CSE-Arrays siehst: Ältere Arbeitsmappen, Vorlagen und Tutorials, die vor 2020 erstellt wurden, verwenden häufig CSE-Arrays. Du musst sie erkennen können. Wenn du Excel 365 nutzt, kannst du sie in der Regel durch übersichtlichere dynamische Array-Formeln ersetzen.Dynamische Arrays (Excel 365 und Excel 2021)
Dynamische Arrays übertragen ihre Ergebnisse automatisch in benachbarte Zellen. Strg+Umschalt+Enter ist nicht erforderlich. Wenn eine Formel 10 Werte zurückgibt, füllt sie automatisch 10 Zellen aus.
Überlaufbereich: Der blaue Rahmen um Zellen, in die ein dynamisches Array übergelaufen ist. Wenn eine andere Zelle den Überlauf blockiert, bekommst du einen #SPILL!-Fehler - verschiebe oder lösche die blockierende Zelle. Auf einen Ausbreitungsbereich verweisen: Verwende die Zelladresse der Formel, gefolgt von #. Wenn sich deine Formel beispielsweise in A1 befindet und nach unten ausgebreitet wird, bezieht sich =A1# dynamisch auf den gesamten Ausbreitungsbereich.FILTER - Zeilen extrahieren, die bestimmten Bedingungen entsprechen
=FILTER(array; include; [if_empty])FILTER gibt nur die Zeilen aus einem Bereich zurück, bei denen die Bedingung WAHR ist.
Beispiel: Zeige nur Zeilen an, in denen Spalte C (Status) den Wert „Offen“ hat: =FILTER(A2:D100; C2:C100="Open"; "No results")Das dritte Argument („Keine Ergebnisse“) wird angezeigt, wenn keine Zeilen übereinstimmen - das verhindert einen #CALC!-Fehler.
Mehrere Bedingungen (UND): =FILTER(A2:D100; (C2:C100="Open")*(B2:B100>1000); "No results")Multipliziere Bedingungen miteinander für die UND-Logik. Verwende + für die ODER-Logik.
Mehrere Bedingungen (ODER): =FILTER(A2:D100; (C2:C100="Open")+(C2:C100="Pending"); "No results")FILTER ersetzt komplexe SUMMEWENN/ZÄHLENWENN-Kombinationen und macht die manuelle Pflege separater gefilterter Tabellen überflüssig.
SORTIEREN und SORTIERENNACH - Daten mit einer Formel sortieren
=SORTIEREN(array; [sort_index]; [sort_order]; [by_col])Sort_order: 1 = aufsteigend (Standard), -1 = absteigend
Beispiel: Sortiere den Bereich A2:D100 nach Spalte 3 (absteigend): =SORTIEREN(A2:D100; 3; -1) =SORTIERENNACH(array; by_array1; [sort_order1]; [by_array2]; [sort_order2]; ...)SORTIERENNACH ist flexibler - sortiert nach einem Array, das gar nicht in der Ausgabe enthalten ist.
Beispiel: Sortiere eine Produktliste nach einer separaten Spalte mit Punktzahlen, die du in der Ausgabe nicht haben möchtest: =SORTIERENNACH(A2:B50; C2:C50; -1)Das gibt die Spalten A und B sortiert nach den Werten in Spalte C (absteigend) zurück, ohne dass C in der Ausgabe enthalten ist.
Kombiniere das mit FILTER: =SORTIEREN(FILTER(A2:D100; C2:C100="Open"); 2; 1)Filtere nach den Zeilen mit dem Wert „Open“ und sortiere das Ergebnis dann nach Spalte 2 in aufsteigender Reihenfolge - alles in einer einzigen Formel.
EINDEUTIG - Eine Liste mit eindeutigen Werten erstellen
=EINDEUTIG(array; [by_col]; [exactly_once])Liefert eine Liste mit deduplizierten Werten. Keine Hilfspalten, kein „Duplikate entfernen“, keine Pivot-Tabelle.
Beispiel: Erstelle eine Liste mit eindeutigen Kundennamen aus dem Bereich A2:A500: =EINDEUTIG(A2:A500) Einzigartige Kombinationen über Spalten hinweg: Setze by_col auf FALSE (Standard) und gib mehrere Spalten an: =EINDEUTIG(A2:B500) - gibt eindeutige Kombinationen aus „Name“ und „Region“ zurück Werte, die genau einmal vorkommen (nicht nur unterschiedliche Werte): Setze exactly_once auf TRUE: =EINDEUTIG(A2:A500; FALSCH; WAHR) - gibt Werte zurück, bei denen es keinerlei Duplikate gibt In Kombination mit SORTIEREN für eine übersichtliche Dropdown-Quelle: =SORTIEREN(EINDEUTIG(A2:A500))ABLAUF - Zahlenreihen erstellen
=SEQUENZ(rows; [cols]; [start]; [step])Erzeugt ein 2D-Array mit fortlaufenden Zahlen.
Beispiele:- =SEQUENZ(10) → die Zahlen 1 bis 10 in einer Spalte
- =SEQUENZ(5;3) → ein Raster mit 5 Zeilen und 3 Spalten, gefüllt mit den Zahlen 1 - 15
- =SEQUENZ(12;1;1;1) → Monate 1 - 12
- =SEQUENZ(10;1;0;5) → 0, 5, 10, 15... (10 Werte, Schrittweite 5)
XVERWEIS mit Arrays
XVERWEIS unterstützt von Haus aus Array-Lookups und ersetzt damit sowohl SVERWEIS als auch WVERWEIS.
=XVERWEIS(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode]) Mehrere Spalten gleichzeitig zurückgeben: Setze return_array auf einen mehrspaltigen Bereich: =XVERWEIS(G2; A2:A100; B2:D100) - sucht in Spalte A nach G2 und gibt die gesamte Zeile B:D zurück Mehrere Suchvorgänge gleichzeitig (Array von Suchwerten): =XVERWEIS(G2:G10; A2:A100; B2:B100) - liefert gleichzeitig 10 Ergebnisse für 10 Suchwerte Ungefähre Übereinstimmung für Bereiche: Der Parameter match_mode (4. Argument nach if_not_found):- 0 = exakte Übereinstimmung (Standard)
- -1 = genau oder die nächstkleinere Zahl
- 1 = genau oder nächstgrößer
Häufig gestellte Fragen
Was ist der #SPILL!-Fehler und wie behebe ich ihn?
Ein #SPILL!-Fehler bedeutet, dass die Formel versucht, in Zellen zu überlaufen, die nicht leer sind. Klick auf die Formelzelle - blaue gepunktete Linien zeigen an, wohin die Formel überlaufen will. Lösche oder verschiebe den Inhalt dieser Zellen. Häufige Ursache: Ein Wert, der sich in einer scheinbar leeren Zelle versteckt (ein Leerzeichen). Wähle jede blockierende Zelle aus und drücke Entf.
Funktionieren dynamische Array-Formeln auch in älteren Excel-Versionen?
Nein. Für die Funktionen FILTER, SORTIEREN, SORTIERENNACH, EINDEUTIG und SEQUENZ ist Excel 365 oder Excel 2021 erforderlich. In Excel 2019 oder früheren Versionen zeigen diese Funktionen den Fehler #NAME? an. Wenn du Dateien mit Nutzern teilst, die ältere Versionen verwenden, können diese die Formeln weder nutzen noch bearbeiten. Aus Kompatibilitätsgründen sind CSE-Array-Formeln die Alternative, auch wenn sie weniger elegant sind.
Kann ich mit FILTER Daten in einer bestimmten Form ausgeben?
Ja. FILTER gibt dieselben Spalten zurück wie das Eingabe-Array. Um nur bestimmte Spalten zurückzugeben, setze WAHL ein oder verwende mehrere FILTER-Aufrufe. In Excel 365 kannst du außerdem ein horizontales Array mit Spaltenindizes an CHOOSECOLS übergeben:
=CHOOSECOLS(FILTER(A2:D100; C2:C100="Open"); 1; 3) - gibt nur die Spalten 1 und 3 des gefilterten Ergebnisses zurück.
Gibt es einen Leistungsunterschied zwischen CSE und dynamischen Arrays?
Dynamische Array-Formeln sind in der Regel schneller und effizienter als ihre CSE-Entsprechungen, da sie von Grund auf für die moderne Excel-Berechnungsengine entwickelt wurden. Vermeide volatile Funktionen wie OFFSET oder INDIREKT in Array-Formeln - sie werden bei jeder Änderung neu berechnet, unabhängig davon, ob sich die Eingabe geändert hat.
Kann ich „EINDEUTIG“ oder „FILTER“ als Quelle für ein Dropdown-Menü bei der Datenüberprüfung verwenden?
Nicht direkt - das Feld „Quelle“ der Datenüberprüfung akzeptiert keine Verweise auf Überlaufbereiche (=A1#). Workaround: Benenne deinen EINDEUTIG/FILTER-Überlaufbereich mit OFFSET und ANZAHL2, um einen dynamischen benannten Bereich zu erstellen, und verweise dann in der Datenüberprüfung auf diesen benannten Bereich. In Excel 365 ist es eine elegantere Lösung, eine Tabelle über der Ausgabedatei des Überlaufbereichs zu erstellen und in der Validierung auf die Spalte dieser Tabelle zu verweisen.