Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial gibt Ihnen eine Einführung in dynamische Array-Formeln in Excel und Google Sheets.
Einführung
Im September 2022 hat Microsoft Dynamic Array Formulas in Excel eingeführt. Ihr Zweck besteht darin, das Schreiben komplexer Formeln zu vereinfachen und die Fehlerwahrscheinlichkeit zu verringern.
Dynamische Array-Formeln sollen schließlich Array-Formeln ersetzen, d. h. erweiterte Formeln, die die Verwendung von Strg + Umschalt + Eingabe (CSE) erfordern.
Hier ist ein kurzer Vergleich zwischen der Array-Formel und der dynamischen Array-Formel, die verwendet wird, um eine Liste einzigartiger Abteilungen aus unserer Liste im Bereich zu extrahieren A2: A7.
Legacy-Array-Formel (CSE):
Die folgende Formel wird in die Zelle eingegeben D2 und wird durch Drücken von Strg + Umschalt + Eingabetaste eingegeben und von nach unten kopiert D2 zu D5.
| 1 | {=IFERROR(INDEX($A$2:$A$7, MATCH(0, ZÄHLENWENN($D$1:D1, $A$2:$A$7), 0)), "")} |
Dynamische Array-Formel:
Die folgende Formel wird nur in die Zelle eingegeben D2 und durch Drücken der Eingabetaste eingegeben. Auf einen Blick erkennen Sie, wie einfach und unkompliziert es ist, eine dynamische Array-Formel zu schreiben.
| 1 | =EINZIGARTIG(A2:A7) |
Verfügbarkeit
Ab August 2022 sind dynamische Array-Formeln nur für Office 365-Benutzer verfügbar.
Auslauf und Auslaufbereich
Dynamische Array-Formeln funktionieren, indem sie mehrere Ergebnisse an einen Zellbereich zurückgeben, basierend auf einer einzelnen Formel, die in eine Zelle eingegeben wurde.
Dieses Verhalten wird als bezeichnet „Verschütten“ und der Zellenbereich, in dem die Ergebnisse platziert werden, wird als bezeichnet „Spill-Bereich“. Wenn Sie eine Zelle innerhalb des Überlaufbereichs auswählen, hebt Excel sie mit einem dünnen blauen Rahmen hervor.
Im folgenden Beispiel ist die dynamische Array-Formel SORTIEREN ist in der Zelle D2 und die Ergebnisse wurden im Bereich verschüttet D2: D7
| 1 | =SORTIEREN(A2:A7) |
Die Ergebnisse der Formel sind dynamisch, dh wenn eine Änderung im Quellbereich auftritt, ändern sich auch die Ergebnisse und der Überlaufbereich wird skaliert.
#VERSCHÜTTEN!
Beachten Sie, dass ein #SPILL-Fehler zurückgegeben wird, wenn Ihr Spill-Bereich nicht vollständig leer ist.
Wenn Sie den #SPILL-Fehler auswählen, wird der gewünschte Spill-Bereich der Formel mit einem gestrichelten blauen Rahmen hervorgehoben. Durch Verschieben oder Löschen der Daten in die nicht leere Zelle wird dieser Fehler behoben, sodass die Formel überlaufen kann.
Verschüttungsreferenznotation
Um den Überlaufbereich einer Formel zu referenzieren, platzieren wir das # Symbol nach dem Zellbezug der ersten Zelle im Spill.
Sie können den Überlauf auch referenzieren, indem Sie alle Zellen im Überlaufbereich auswählen, und es wird automatisch ein Verweis auf den Überlauf erstellt.
Im folgenden Beispiel möchten wir die Anzahl der Mitarbeiter in unserem Unternehmen mit der Formel zählen ANZAHL nachdem sie alphabetisch mit der dynamischen Array-Formel geordnet wurden SORTIEREN.
Wir betreten die SORTIEREN Formel in D2, um die Mitarbeiter in unserer Liste zu ordnen:
| 1 | =SORTIEREN(A2:A7) |
Wir betreten dann die ANZAHL Formel in G2 um die Anzahl der Mitarbeiter zu zählen:
| 1 | =ANZAHL(D2#) |
Beachten Sie die Verwendung von # in D2#, um sich auf die von SORT im Bereich D2:D7 ausgegebenen Ergebnisse zu beziehen.
Neue Formeln
Nachfolgend finden Sie die vollständige Liste der neuen dynamischen Array-Formeln:
- EINZIGARTIG - Gibt eine Liste eindeutiger Werte aus einem Bereich zurück
- SORTIEREN - Sortiert Werte in einem Bereich
- SORTIEREN NACH - Sortiert Werte nach einem entsprechenden Bereich
- FILTER - Filtert einen Bereich basierend auf den angegebenen Kriterien
- RANDARRAY - Gibt ein Array von Zufallszahlen zwischen 0 und 1 zurück
- REIHENFOLGE - Erzeugt eine Liste von fortlaufenden Nummern wie 1, 2, 3, 4, 5
Dynamische Array-Formels in Google Tabellen
Alle oben genannten Beispiele funktionieren in Google Sheets genauso wie in Excel.
