Dynamische Array-Funktionen in Excel: Eine neue Dimension erschließen

Microsoft Excel hat mit dynamischen Array-Funktionen eine neue Ära eingeleitet – eine Reihe leistungsstarker Funktionen in Excel für Microsoft 365 (und Excel 2021+), die die Funktionsweise von Formeln grundlegend verändern. Anstatt einen einzelnen Wert zurückzugeben, können diese Funktionen automatisch ein Array von Werten zurückgeben , das in benachbarte Zellen übergeht . Das bedeutet, dass eine Formel mehrere Zellen gleichzeitig füllen kann. Dadurch entfallen die herkömmlichen Array-Formeln mit Strg+Umschalt+Eingabe, und komplexe Aufgaben werden deutlich vereinfacht.

In diesem Handbuch werden die nützlichsten dynamischen Array-Funktionen vorgestellt: SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, RANDARRAY und LET. Wir zeigen Ihnen anhand praktischer Beispiele und hilfreicher Tricks, wie sie funktionieren.

Dynamic array functions in Excel - the foundation

Was sind dynamische Array-Funktionen?

Dynamische Arrayfunktionen verteilen ihre Ergebnisse automatisch in benachbarte Zellen, den sogenannten Überlaufbereich. Ändert sich der Überlaufbereich, passt sich Excel dynamisch an, sodass Sie Formeln nicht mehr verschieben oder STRG+UMSCHALT+EINGABE verwenden müssen. Wenn Sie auf einen Überlaufbereich verweisen, fügen Sie nach dem Zellbezug oben links ein Rautezeichen (#) hinzu (z. B. A1#).

(Hinweis: Dynamische Array-Funktionen sind nur in Excel für Microsoft 365 oder Excel 2021 und höher verfügbar . Wenn Sie diese Formeln in einer älteren Version öffnen, wird ein @-Symbol hinzugefügt, andernfalls funktionieren sie nicht.)

• SORT-Funktion

Mit der SORT-Funktion können Sie Daten ganz einfach ordnen.

Syntax:

=SORT(array, [sort_index], [sort_order], [by_col])

Beispiel: Sortieren einer Produktliste nach Preis in absteigender Reihenfolge.

=SORT(A2:B10, 2, -1)

Die Produkte werden jetzt automatisch mit dem höchsten und niedrigsten Preis angezeigt.

Kurzer Tipp: SORT funktioniert standardmäßig vertikal, aber verwenden Sie by_col=TRUE, um horizontal zu sortieren!

Bevor Sie fortfahren, beachten Sie: Wenn Ihre Quelldaten in einer Excel-Tabelle vorliegen, erweitert SORT die Ausgabe nicht automatisch, wenn neue Daten hinzugefügt werden (da Sie den Array-Verweis anpassen müssten). Für mehr Flexibilität können Sie strukturierte Verweise oder die Funktion SORTBY verwenden.

• SORTBY-Funktion

SORTBY sortiert Daten basierend auf verschiedenen Arrays.

Syntax:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)

Beispiel: Sortieren Sie die Mitarbeiter zuerst nach Abteilung und dann nach Gehalt.

=SORTBY(A2:C10, B2:B10, 1, C2:C10, -1)

Jetzt sind Ihre Mitarbeiter übersichtlich organisiert, nach Abteilungen gruppiert und dann innerhalb jeder Abteilung nach Gehalt.

Trick: Randomisieren Sie Ihre Liste ganz einfach!

=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))

Mischen Sie Daten sofort wie bei einer Lotterieziehung.

Warum SORTBY statt SORT verwenden? Die Funktion SORTBY ist besonders nützlich, wenn Sie mehrstufige oder benutzerdefinierte Sortierungen benötigen, die nicht nur aufsteigend/absteigend in einer der Ausgabespalten erfolgen. Auch wenn in Ihren Quelldatenbereich neue Spalten eingefügt werden könnten, ist SORTBY (das Bereiche nach Namen referenziert) robuster als SORT (das Indexnummern verwendet, die bei Spaltenverschiebungen fehlerhaft werden könnten).

• FILTER-Funktion

FILTER gibt nur die Zeilen zurück, die bestimmte Kriterien erfüllen.

Syntax:

=FILTER(array, include, [if_empty])

Beispiel: Filtern von Bestellungen über 500 $.

=FILTER(A2:B100, B2:B100>500, "No orders")

Dadurch werden Bestellungen mit hohem Wert schnell aufgelistet und bei Datenänderungen dynamisch aktualisiert.

Multikriterien-Tipp:

Kombinieren Sie Kriterien durch Multiplikation (UND-Logik):

=FILTER(A2:C100, (B2:B100="East")*(C2:C100>500))

• UNIQUE-Funktion

UNIQUE extrahiert mühelos eindeutige Werte.

Syntax:

=UNIQUE(array, [by_col], [exactly_once])

Beispiel: Rufen Sie eine Liste eindeutiger Kundennamen ab.

=UNIQUE(A2:A50)

Erstellt schnell eine saubere Kundenliste ohne Duplikate.

Tipp: Sorgen Sie dafür, dass Einträge genau einmal erscheinen:

=UNIQUE(A2:A50, FALSE, TRUE)

Perfekt zum Identifizieren einzigartiger Umfrageantworten!

Trick – Sortierung eindeutiger Ergebnisse: Wichtig: UNIQUE sortiert die Ergebnisse nicht; die ursprüngliche Reihenfolge des ersten Vorkommens bleibt erhalten. Wenn Sie eine sortierte Liste eindeutiger Ergebnisse benötigen, können Sie UNIQUE mit SORT umschließen. Beispiel: =SORT(UNIQUE(A2:A100)). Dies ergibt eine sortierte, eindeutige Liste ab A2:A100. Nach dem Filtern einiger Kriterien können Sie FILTER und UNIQUE kombinieren, z. B. eindeutige Werte.

• SEQUENCE-Funktion

SEQUENCE erstellt Zahlenfolgen.

Syntax:

=SEQUENCE(rows, [columns], [start], [step])

Beispiel: Monatsdaten generieren:

=SEQUENCE(12, 1, DATE(2024,1,1), 30)

Erstellt sofort eine Liste mit monatlichen Daten ab Januar 2024.

Kreativ-Tipp: Einmaleins erstellen:

=SEQUENCE(10)*TRANSPOSE(SEQUENCE(10))

Trick – Schnell eine Berechnungstabelle erstellen: Da SEQUENCE eine Matrix ausgeben kann, können Sie kreativ werden. Beispiel: Um ein Einmaleins von 1 bis 10 zu erzeugen: Geben Sie =SEQUENCE(10,1,1,1 ) in A2:A11 für die vertikalen Überschriften und =SEQUENCE(1,10,1,1) in B1:K1 für die horizontalen Überschriften ein (oder erzeugen Sie beides über Formeln). Geben Sie dann in B2 =$A2 B$1 ein und kopieren Sie es durch B2:K11 – voilà. Ein noch coolerer Trick: Mit dynamischen Arrays geht das in einem Rutsch: =SEQUENCE(10,1,1,1) * SEQUENCE(1,10,1,1) in eine einzelne Zelle eingegeben, ergibt ein 10x10-Einmaleins! Sie könnten =SEQUENCE(10)*TRANSPOSE(SEQUENCE(10)) eingeben, um dasselbe mit einer Formel zu erreichen. Dies zeigt, wie dynamische Arrays und Funktionen wie SEQUENCE Matrixberechnungen ohne Hilfszellen ermöglichen.

• RANDARRAY-Funktion

RANDARRAY generiert Zufallszahlen.

Syntax:

=RANDARRAY([rows], [columns], [min], [max], [whole_number])

Beispiel: Zufällige Ganzzahlen für eine Scheinumfrage.

=RANDARRAY(5,3,1,10,TRUE)

Erstellt sofort zufällige Umfrageantworten.

Bonus: Generieren Sie zufällige Buchstaben für Passwörter oder IDs.

=CHAR(RANDARRAY(6,1,65,90,TRUE))

Erzeugt schnell zufällige Großbuchstaben.

Extra-Trick – Zufällige Buchstaben oder Zeichenfolgen: Obwohl es keine integrierte Funktion ist, können Sie mit RANDARRAY und der CHAR-Funktion zufällige Buchstaben generieren. Beispiel: =CHAR(RANDARRAY(5,1,65,90,TRUE)) ergibt fünf zufällige Großbuchstaben (da CHAR(65) = "A" und 90 = "Z"). Dies funktioniert durch die Generierung zufälliger Ganzzahlen zwischen 65 und 90. Längere Textzeichenfolgen können Sie durch die Generierung eines Arrays und dessen Verkettung oder mit den neuen TEXTJOIN/LAMBDA-Methoden generieren.

• LET-Funktion

LET definiert Variablen innerhalb von Formeln und vereinfacht so Berechnungen.

Syntax:

=LET(name1,value1,name2,value2,..., calculation)

Beispiel: Verwendung von LET zur Verdeutlichung:

=LET(avg, AVERAGE(A1:A10), SUM((A1:A10 - avg)^2))

Berechnen Sie komplexe Formeln übersichtlich und effizient.

Profi-Tipp: Kombinieren Sie LET mit dynamischen Arrays für ultimative Flexibilität:

=LET(dates, SEQUENCE(endDate-startDate+1,,startDate), FILTER(dates, WEEKDAY(dates,2)<6))

Generiert dynamisch Wochentage zwischen zwei Daten!

Hinweis zum Gültigkeitsbereich: In LET definierte Variablen existieren nur innerhalb der Formel . Sie erscheinen weder im Namensmanager des Arbeitsblatts noch anderswo. Sie sind wie temporäre Variablen in einer Programmierfunktion. Sobald die Formel abgeschlossen ist, verschwinden diese Namen. Das bedeutet, dass Sie gängige Namen wie x oder temp verwenden können, ohne Konflikte befürchten zu müssen (vermeiden Sie Namen, die Zellreferenzen sein könnten, z. B. nicht „AA1“ als Namen, und denken Sie daran, dass Namen nicht mit einer Zahl beginnen oder wie Zelladressen aussehen dürfen).

Wann LET sinnvoll ist: Bei einfachen Einzeilerformeln kann LET übertrieben sein. LET kann jedoch nützlich sein, wenn Sie einen Ausdruck wiederholen oder Teile der Formel komplex genug sind und Sie diese dokumentieren möchten. Es beschleunigt die Neuberechnung (durch Entfernen redundanter Berechnungen) und vereinfacht die Wartung: Sie können einen Teilausdruck an einer Stelle statt an mehreren ändern. Es ist wie das Erstellen eines Mini-Makros innerhalb Ihrer Formel.

Beispielsweise eine komplizierte Bedingungsformel wie:

=IF(((A1+B1)/2 > 50)  ||  ((A1+B1)/2 < 20),  (A1+B1)/2,  "OK")

Der Unterausdruck (A1+B1)/2 wird dreimal wiederholt. Man könnte ihn mit LET versehen:

=LET(avg, (A1+B1)/2,  IF( OR(avg>50, avg<20),  avg, "OK") )

Das ist kürzer und übersichtlicher. Excel berechnet den Durchschnitt einmal und verwendet ihn erneut.

Zusammenfassend lässt sich sagen, dass LET nicht die Funktionen von Excel ändert, sondern die Art und Weise , wie Sie Formeln erstellen, wodurch diese übersichtlicher und oft effizienter werden. Betrachten Sie es eher als Werkzeug für den Formelersteller als für das Formelergebnis . Besonders leistungsstark ist es in Kombination mit dynamischen Arrays und sogar der neuen LAMBDA-Funktion (zum Erstellen benutzerdefinierter Funktionen), aber das ist ein anderes Thema.

Abschließende Gedanken

Dynamische Array-Funktionen in Excel vereinfachen komplexe Aufgaben und machen Tabellen reaktionsschnell und pflegeleicht. Die Beherrschung dieser Funktionen – SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, RANDARRAY und LET – verbessert Ihre Datenverarbeitungsfähigkeiten erheblich.

Probieren Sie diese leistungsstarken Tools aus und beobachten Sie, wie Ihre Excel-Effizienz in die Höhe schnellt!

Einige abschließende Tipps zur effektiven Verwendung dynamischer Arrays:

• Stellen Sie sicher, dass der Ausgabe-Spill-Bereich ausreichend Platz zum Erweitern bietet. Andernfalls erhalten Sie die Fehlermeldung #SPILL. Löschen Sie alle blockierenden Zellen, und das Ergebnis wird angezeigt.

• Beachten Sie, dass die Neuberechnung dieser Formeln zu erheblichen Aktualisierungen führen kann. Sie sind im Allgemeinen effizient, aber wenn Sie viele große dynamische Funktionen verschachteln, sollten Sie die Leistung im Auge behalten.

• Nutzen Sie die neue Referenzierung mit dem #-Operator. Damit können Sie die Ergebnisse eines dynamischen Arrays problemlos in andere Formeln einspeisen. Wenn beispielsweise =SORT(...) in E2 nach unten überläuft, können Sie die gesamte Ausgabe in einer anderen Formel als E2# referenzieren – Sie müssen den genauen Bereich nicht erraten.

• Diese Funktionen funktionieren nicht innerhalb von Excel-Tabellen (strukturierte Tabellenverweise). Wenn Sie dynamische Arrays in Tabellen benötigen, können Sie diese umgehen, indem Sie sie in Zellen außerhalb der Tabelle verschieben oder diese zum Generieren von Daten verwenden, die Sie dann in eine Tabelle einfügen. Microsoft hat sich dafür entschieden, um Mehrdeutigkeiten bei strukturierten Verweisen und beim automatischen Ausfüllen von Tabellen zu vermeiden.

• Bleiben Sie auf dem Laufenden: Microsoft führt regelmäßig neue Funktionen (wie XLOOKUP, LAMBDA usw.) ein, die gut mit dynamischen Arrays funktionieren. Die Excel-Formelsprache entwickelt sich in der Abonnementversion schnell weiter.