Excel 365 Überlauf? Das ist doch verboten!

Alexander Achenbach/ Datenanalyse, Excel

In meinen Seminaren sage ich oft beiläufig: „Eine Formel beginnt immer mit einem = und besteht aus einer beliebigen Verkettung von Wert, Operator und Wert. Also zum Beispiel =1+2+3+5+7, denn =1+2 3 5 7 würde ja keinen Sinn machen, denn dann weiß Excel ja nicht was es mit der 3, 5 und 7 tun soll. Haben wir in Mathe ja auch so gelernt.“ – Alle Beteiligen nicken zustimmend.

Genau genommen ist das eine Wahrheit, die wir von der Grundschule bis zur Mittelstufe gewohnt sind und die für die meisten Fälle auch so ausreichend ist. Dann muss man den Excel Anfängern natürlich noch erklären, dass es Adressen und Bereiche gibt und auch hier die Regel gilt: Einzelne Adressen mit einem Operator verknüpfen, Bereiche in einer Funktion nutzen.

=A1+A2+A3+A4
=Summe(A1:A4)
=A1+Summe(A2:A4)

Das ist alles OK, aber

=A1+A2:A4

ist leider falsch und bis vor kurzem hätten wir dann auch einen #WERT! oder #BEZUG! Fehler in Excel bekommen. In Excel 365 aber nicht mehr! Und das ist der Grund für diesen Artikel.

Sobald wir nach Eingabe der Formel =A1+A2:A4 die ENTER-Taste drücken, wird nicht nur eine, sondern es werden direkt drei Zellen gefüllt.

Der erfahrene Excel Anwender wird jetzt sagen: „Alter Hut! Das sind doch Array-Funktionen.“ Diese Aussage stimmt allerdings nicht ganz. Was wir hier sehen ist eine Überlauf-Formel, die sich von einer Array-Formel massiv unterscheidet:

Die Überlauf-Formel entsteht automatisch beim drücken der ENTER Taste (nicht mit STRG+UMSCHALTEN+ENTER).
Die Überlauf-Formel muss nicht kopiert werden, sondern füllt sich automatisch in die benachbarten Zellen.
Adressen verhalten sich intuitiv absolut ($).
Um eine Überlauf-Formel zu löschen muss lediglich die erste Zelle gewählt und gelöscht werden, nicht der gesamte Bereich.

Warum erhalten wir drei untereinander stehende Ergebnisse? Weil in unserer Formel ein Bereich (A2:A4) aus 1 x 3 Zellen vorkommt. Und wie kommt Excel auf die Ergebnisse?

3 = 1 + 2 also A1+A2, weil A2 die erste Zelle im Bereich ist.
4 = 1 + 3 also A1+A3, weil A3 die zweite Zelle im Bereich ist.
6 = 1 + 5 also A1+A4, weil A4 die dritte Zelle im Bereich ist.

Bemerkenswert ist, dass der Bezug auf A1 quasi absolut durchgezogen wird, wohingegen die Bezüge auf A2:A4 quasi einer Schleife einzeln abgearbeitet werden.

Excel 365 macht also den Sprung von der 1-wertigen Grundschulmathematik zu Mengen und Matrizen. Hat eine Formel mehr als genau ein Ergebnis, liefert diese Formel konsequent mehrere Ergebnisse in mehreren Zellen.

Abschließend noch ein kleines Beispiel, welches mit exakt 4 Formeln und ohne kopieren oder Dollarzeichen gelöst werden kann:

In den Spalten E bis G soll der MwSt-Betrag errechnet werden. Also jeder Wert aus B bis D muss mit 19% in B16 multipliziert werden. Dies kann mit nur einer Formel in E3 erfolgen:

=B3:D14*B16

In den Spalten H bis J müssen alle Werte aus B bis D mit den entsprechenden Werten aus E bis G addiert werden. Auch hier reicht genau eine Formel in H3:

=B3:D14+E3:G14

Schauen wir uns die Formel nach Bestätigung erneut an, steht hier etwas anderes:

=B3:D14+E3#

Nimm jeden Wert im Bereich B3:D14 und addiere dazu jeden Wert im Überlauf-Bereich von E3. Der Hashtag (#) sagt also: Nimm nicht den einen Wert aus E3, sondern alle Werte die zu E3 gehören.

Den Hashtag (#) können wir uns also auch bei den beiden Summen in B18 und B19 zunutze machen denn wir haben doch in E3# alle MwSt-Beträge und in H3# alle Brutto-Beträge durch unsere zwei Überlauf-Formeln:

=SUMME(E3#)
=SUMME(H3#)

Und fertig ist unsere Übersicht:

Insgesamt gibt es nur genau vier Zellen mit Formeln.

In den Beispielen haben wir Bereiche mit festen Adressen verwendet. Besonders interessant wird der Überlauf natürlich dann, wenn wir gar nicht wissen wie groß ein Bereich ist, weil er sich vielleicht täglich ändert. In diesem Moment kommen Hilfsmittel wie Namen oder Datentabellen ins Spiel, die uns ein bislang ungeahntes Maß an Flexibilität geben. Auf diese Möglichkeiten gehe ich in den folgenden Artikeln ein.