Was Spill-Bereiche bedeuten

Die meisten Formeln, die Sie bisher geschrieben haben, geben einen einzelnen Wert in eine einzelne Zelle zurück. Dynamische Arrays ändern das. Eine Formel kann eine vollständige Liste oder ein Raster von Ergebnissen zurückgeben, und die Tabellenkalkulation platziert jedes Ergebnis automatisch in einer eigenen Zelle.

Diese automatische Platzierung wird als Überlaufen bezeichnet. Sie geben die Formel einmal ein, drücken die Eingabetaste, und die Ergebnisse verteilen sich über benachbarte Zellen wie Wasser, das ein Tablett füllt.

Angenommen, A1:A5 enthält die Zahlen 1 bis 5. Geben Sie in Zelle C1 die Formel =A1:A5*10 ein und drücken Sie die Eingabetaste.

Statt eines einzelnen Ergebnisses erhalten Sie fünf: 10, 20, 30, 40, 50, untereinander in C1:C5. Sie haben die Formel nur in C1 eingegeben, aber das Ergebnis ist automatisch in die darunterliegenden Zellen übergelaufen.

Der Zellbereich, den eine Formel ausfüllt, wird als Überlaufbereich bezeichnet. Nur die oben links liegende Zelle, der sogenannte Anker, enthält tatsächlich die Formel. Die anderen Zellen werden automatisch ausgefüllt und zeigen einen blassblauen Rand, wenn Sie den Anker auswählen.

Sie können die übergelaufenen Zellen nicht direkt bearbeiten. Wenn Sie es versuchen, wird ein Fehler angezeigt. Um die Ausgabe zu ändern, bearbeiten Sie die Formel in der Ankerzelle.

Da ein Überlauf größer oder kleiner werden kann, verweisen Sie mit dem Überlaufoperator auf das gesamte Ergebnis: Dazu setzen Sie ein Nummernzeichen hinter die Adresse des Ankers.

Wenn eine Formel in C1 bis C5 nach unten überläuft, bezeichnet C1# den gesamten Überlaufbereich. Verwenden Sie diesen Bezug an anderer Stelle, etwa in =SUM(C1#). Dann enthält die Summe immer jeden übergelaufenen Wert, auch wenn der Überlauf später größer wird.

Die eigentliche Stärke des Überlaufs liegt darin, dass er dynamisch ist. Wenn Sie dem Quellbereich eine neue Zahl hinzufügen, wird der Überlaufbereich automatisch erweitert. Entfernen Sie einen Wert, wird der Überlauf kleiner.

Sie müssen eine Formel nie wieder nach unten kopieren. Die einzelne Formel in der Ankerzelle passt die Größe ihrer Ausgabe an die Daten an, die sie liest.

Viele moderne Funktionen sind darauf ausgelegt, Arrays zurückzugeben, und laufen daher über:

• FILTER gibt passende Zeilen zurück.
• UNIQUE gibt eine Liste eindeutiger Werte zurück.
• SORT und SORTBY ordnen einen Bereich neu an.
• SEQUENCE erzeugt eine Liste von Zahlen.

FILTER und UNIQUE werden Sie in diesem Kurs kennenlernen. Jede dieser Funktionen gibt mit einer einzigen Formel viele Ergebnisse aus.

Stellen Sie sich vor, Spalte A enthält die Regionen Ost, West, Ost, Nord, West. Um eine übersichtliche Liste der eindeutigen Regionen zu erhalten, geben Sie eine einzige Formel ein:

=UNIQUE(A2:A6)

Die Formel lässt drei Ergebnisse überlaufen: Ost, West, Nord. Fügen Sie dem Quellbereich eine weitere Region hinzu, wächst die übergelaufene Liste automatisch. Eine Formel, eine stets aktuelle Liste.

Überläufe sind nicht auf die Richtung nach unten beschränkt. Eine Formel kann sich über Spalten hinweg ausbreiten oder sogar ein zweidimensionales Raster ausfüllen.

Beispielsweise lässt =SEQUENCE(3,4) einen 3 Zeilen hohen und 4 Spalten breiten Bereich überlaufen und füllt 12 Zellen mit den Zahlen 1 bis 12. Richtung und Form hängen vollständig von dem Array ab, das die Formel erzeugt.

Dynamische Arrays und Überläufe funktionieren in Excel for Microsoft 365 sowie in Excel 2021 and later. Auch Google Sheets unterstützt den Überlauf von Arrays schon seit Jahren.

Ältere Versionen wie Excel 2016 und 2019 unterstützen keine Überläufe. Dort mussten Matrixformeln mit Ctrl+Shift+Enter bestätigt werden und konnten nicht automatisch wachsen. Wenn Ihre Formeln nicht überlaufen, überprüfen Sie zuerst Ihre Version.

Vor dynamischen Arrays hätten Sie =B2*C2 in eine Zelle eingegeben und die Formel über Hunderte von Zeilen nach unten gezogen. Mit Überläufen geben Sie sie nur einmal ein.

Die Formel =B2:B100*C2:C100 lässt für jede Zeile im gesamten Bereich ein Ergebnis überlaufen. Die Wartung wird dadurch ganz einfach: Es gibt nur eine Formel zu lesen und zu korrigieren, statt Hunderter Kopien, die möglicherweise nicht mehr übereinstimmen.

Stellen Sie sich eine Formel mit Überlauf als kleines Programm vor, das eine Form von Daten ausgibt und nicht nur eine einzelne Zahl. Die Tabellenkalkulation legt diese Form anschließend ausgehend von der Ankerzelle in das Raster.

Ihre Aufgabe besteht darin, sicherzustellen, dass genügend freie Zellen für diese Form vorhanden sind. In den nächsten Lektionen erfahren Sie, wie Sie mit FILTER und UNIQUE nützliche Formen erzeugen und was Sie tun können, wenn der Zielbereich blockiert ist.

Sie haben gelernt, dass eine Formel mit einem dynamischen Array viele Ergebnisse zurückgeben kann und dass die Tabellenkalkulation diese automatisch in einem Überlaufbereich anordnet.

• Nur die Anker-Zelle enthält die Formel.
• Verweisen Sie mit dem #-Operator auf den gesamten Überlauf, etwa mit C1#.
• Überläufe werden automatisch größer oder kleiner, wenn sich die Quelldaten ändern.
• Funktionen wie FILTER, UNIQUE, SORT und SEQUENCE laufen alle über.

Als Nächstes verwenden Sie FILTER, um nur die gewünschten Zeilen zurückzugeben.