Dynamische Arrayformeln und Verhalten bei übergelaufenem Array

Excel-Formeln, die eine Reihe von Werten zurückgeben, die auch als Array bezeichnet werden, geben diese Werte an benachbarte Zellen zurück. Dieses Verhalten wird als Überlaufen bezeichnet.

Formeln, die Arrays variabler Größe zurückgeben können, werden als dynamische Array Formeln bezeichnet. Formeln, die derzeit erfolgreich verschüttete Arrays zurückgeben, können als verschüttete Arrayformeln bezeichnet werden.

Nachfolgend finden Sie einige Hinweise, die Ihnen beim Verständnis und bei der Verwendung dieser Art von Formeln behilflich sind.

Was bedeutet „Überlaufen“?

Hinweis: Ältere Array-Formeln, bekannt als Legacy-Array-Formeln, geben immer ein Ergebnis mit fester Größe zurück – sie werden immer in die gleiche Anzahl von Zellen verschüttet. Das in diesem Thema beschriebene Überlaufverhalten gilt nicht für Legacy-Matrixformeln.

Überlaufen bedeutet, dass eine Formel mehrere Werte zurückgegeben hat und dass diese in benachbarten Zellen platziert wurden. Beispiel: =SORTIEREN(D2:D11,1,-1), wodurch ein Array in absteigender Reihenfolge sortiert wird, würde beispielsweise ein entsprechendes Array zurückgeben, das 10 Zeilen umfasst. Sie müssen die Formel aber nur in der linken oberen Zelle eingeben, in diesem Fall F2; diese läuft dann automatisch nach unten in die Zelle F11 über.

Sortieren Sie die Werte in den Zellen D2:D11 mit =SORTIEREN(D2:D11,1,-1)

Hauptpunkte

Beim Drücken der EINGABETASTE, um die Formel zu bestätigen, passt Excel die Größe des Ausgabebereichs dynamisch für Sie an und platziert die Ergebnisse in jeder Zelle in diesem Bereich.
Wenn Sie eine dynamische Matrixformel für eine Liste von Daten schreiben, empfiehlt es sich, diese in einer Excel-Tabelle zu platzieren und dann strukturierte Verweise zum Verweisen auf die Daten zu verwenden. Dies liegt daran, dass strukturierte Verweise automatisch angepasst werden, wenn Zeilen zu der Tabelle hinzugefügt oder aus dieser entfernt werden.
Übergelaufene Matrixformeln werden in Excel-Tabellen selbst nicht unterstützt, Sie sollten sie daher im Raster außerhalb der Tabelle platzieren. Tabellen sind am besten für Zeilen und Spalten mit unabhängigen Daten geeignet.
Nachdem Sie eine übergelaufene Matrixformel eingegeben haben, platziert Excel, wenn Sie eine Zelle innerhalb des Überlaufbereichs auswählen, einen Rahmen zur Hervorhebung um den Bereich. Der Rahmen verschwindet, wenn Sie eine Zelle außerhalb des Bereichs auswählen.
Nur die erste Zelle im Überlaufbereich kann bearbeitet werden. Wenn Sie eine andere Zelle im Überlaufbereich auswählen, ist die Formel in der Formelleiste sichtbar, der Text ist aber „verwaist“ und kann nicht geändert werden. Wenn Sie die Formel aktualisieren müssen, sollten Sie die obere linke Zelle in dem Arraybereich auswählen und diese bei Bedarf ändern; Excel aktualisiert dann den restlichen Überlaufbereich, wenn Sie die EINGABETASTE drücken.
Formelüberlappung: Matrixformeln können nicht eingegeben werden, wenn der Ausgabebereich durch etwas blockiert wird. In diesem Fall gibt Excel einen #ÜBERLAUF!-Fehler zurück, der angibt, dass es eine Blockierung gibt. Wenn Sie die Blockierung entfernen, läuft die Formel wie erwartet über. Im folgenden Beispiel überlappt der Ausgabebereich der Formel mit einem anderen Bereich mit Daten; dies wird anhand eines gepunkteten Rahmes überlappender Zellen mit Werten dargestellt, die angeben, dass ein Überlaufen nicht möglich ist. Entfernen Sie die blockierenden Daten, oder kopieren Sie sie an eine andere Stelle; die Formel läuft dann wie erwartet über.
Ältere Matrixformeln, die über STRG + UMSCHALT + EINGABETASTE eingegeben wurden, werden aus Gründen der Abwärtskompatibilität weiterhin unterstützt, sollten aber nicht mehr verwendet werden. Sie können bei Bedarf ältere Matrixformeln in dynamische Matrixformeln konvertieren, indem Sie die erste Zelle im Arraybereich suchen, den Text der Formel kopieren, den gesamten Bereich des älteren Arrays löschen und dann die Formel erneut in der oberen linken Zelle eingeben. Bevor Sie ältere Matrixformeln in dynamische Matrixformeln umwandeln, sollten Sie einige Berechnungsunterschiede zwischen den beiden beachten.
Excel unterstützt nur bedingt dynamische Arrays zwischen Arbeitsmappen, und dieses Szenario wird nur unterstützt, wenn beide Arbeitsmappen geöffnet sind. Wenn Sie die Quellarbeitsmappe schließen, geben alle verknüpften dynamischen Matrixformeln beim Aktualisieren einen #BEZUG!-Fehler zurück.