Bereits im Jahr 2019 hat Microsoft mit den sogenannten dynamischen Array-Funktionen ein völlig neues Konzept im Umgang mit Formeln, Funktionen und Zellbezügen eingeführt.
Und trotzdem ist meiner Erfahrung nach diese bahnbrechende Veränderung immer noch vielen Anwendern völlig unbekannt. Neben einer Reihe von neuen Funktionen, wie FILTER, SORTIEREN oder EINDEUTIG spielt ein kleines Zeichen eine wichtige Rolle. Ursprünglich bekannt als Raute-Zeichen, manche nennen es auch Gartenzaun:
In aktuellen Excel-Versionen hingegen ist das der Überlauf-Operator #
Dieses unscheinbare Symbol stellt viele bisherigen Gewissheiten in Excel auf den Kopf. Was es mit diesem Zeichen auf sich hat und wie du dir das Excel-Leben mit seiner Hilfe bedeutend einfacher machen kannst, zeige ich in diesem Beitrag.
Der Überlauf-Operator
Damit du überhaupt in den Genuss dieser Funktionen kommen kannst, benötigst du mindestens Excel 2021, 2024 oder Excel aus Microsoft 365. In den Versionen davor ist dieses Konzept leider noch nicht verfügbar.
Das Raute-Zeichen # wird jetzt also als Überlauf-Operator bezeichnet (englisch: „spill“). Warum?
Wie eingangs erwähnt, wurden vor geraumer Zeit neue Funktionen eingeführt, die als Ergebnis nicht nur einen Wert zurückliefern, so wie man es bis dahin kannte. Sondern die gleich eine ganze Reihe von Werten zurückgeben können und dabei automatisch in so viele Zellen „überlaufen“, wie eben gerade benötigt werden. Ursprünglich waren das einige wenige Funktionen, wie FILTER, EINDEUTIG oder SORTIEREN. Mittlerweile gibt es aber mehr als 30 dieser sogenannten dynamischen Array-Funktionen.
Online-Kurs „Dynamische Array-Funktionen“
Wenn man nun mit den Ergebnissen dieser Funktionen weiterarbeiten möchte, ergibt sich jedoch eine Schwierigkeit:
Woher weiß man, auf wie viele Zellen man zugreifen muss? Schließlich wächst oder schrumpft der Ausgabebereich dieser Funktionen ja dynamisch.
Folgendes Beispiel soll die Problematik verdeutlichen.
Beispiel 1: Dynamische Formelliste
Eine intelligente Tabelle mit dem Namen t_Städte enthält für jedes Land die Millionenstädte und deren Einwohnerzahlen. Rechts daneben wird mit Hilfe der Funktionen FILTER, EINDEUTIG und SORTIEREN die sortierte Liste aller Länder für den in Zelle G1 ausgewählten Kontinent ausgegeben:
=SORTIEREN(EINDEUTIG(FILTER(t_Städte[Land];t_Städte[Kontinent]=G1)))
Eine dynamisch generierte Länderliste
Am blauen Rahmen um den Ausgabebereich erkennt man, dass es sich um ein dynamisches Array handelt. Und je nach ausgewähltem Land wird diese Liste automatisch länger – oder auch kürzer. So weit, so einfach.
Nun sollen in der Spalte daneben die Summen der Einwohnerzahlen der Millionenstädte für das jeweilige Land ausgegeben werden. Als erfahrener Excel-Anwender würde man dazu beispielsweise in die erste Zelle eine SUMMEWENN-Formel eingeben und die Formel dann nach unten kopieren:
=SUMMEWENN(t_Städte[Land];F4;t_Städte[Einwohner])
Summenbildung pro Land – bisher
Formel wird nach unten kopiert
Ändert man nun den Kontinent in Zelle G1, ist das Ergebnis nicht mehr so schön. Entweder man hat zu viele Formeln in Spalte G oder es fehlen welche:
Eine andere Auswahl führt zu unschönen oder unvollständigen Ergebnissen
Und genau jetzt schlägt die große Stunde des Überlauf-Operators!
Wir löschen also zunächst wieder alle kopierten Formeln, bis auf die erste. Dort wird im Argument für das Suchkriterium einfach das #-Zeichen an die Adresse F4 angehängt. Also statt F4 steht dort jetzt F4#
Summenbildung mit Überlauf-Operator
Bereits während der Eingabe ist zu erkennen, dass dadurch gleich sämtliche Länder in der Ausgabeliste markiert werden. Sobald die Eingabe bestätigt wird, sehen wir auch in der Spalte mit den Einwohnerzahlen ein dynamisches Array:
Aus SUMMEWENN wird ein dynamischer Ausgabebereich
Ändert sich durch die Auswahl eines neuen Kontinents die Länge der Länderliste, passt sich die Spalte mit den Einwohnerzahlen automatisch auf die richtige Länge an:
Die Ergebnisse passen sich dynamisch der Auswahl an
Das unscheinbare Raute-Zeichen sorgt also nicht nur dafür, dass immer der richtige Bereich angesprochen wird. Wie man an diesem Beispiel gut erkennen kann, werden damit sogar „alte“ Funktionen, wie SUMMEWENN, plötzlich dynamisch!
Beispiel 2: Dynamische Dropdown-Listen
Auch für dynamische und sogar mehrstufige Dropdown-Listen eignet sich der Überlauf-Operator ganz hervorragend. Bleiben wir bei der Länderliste.
Ich möchte über drei Dropdown-Felder zuerst den Kontinent, dann ein Land innerhalb des Kontinents und schließlich eine Stadt innerhalb des Landes auswählen. Am Ende soll dann die passende Einwohnerzahl ausgespuckt werden:
Eine dreistufige Dropdown-Auswahl
Dazu habe ich in den Spalten I bis K mit Hilfe von dynamischen Array-Funktionen meine Quellen für die drei Dropdown-Felder eingerichtet:
Die benötigten Formeln für die Datenquellen
Und in der Datenprüfung für die Dropdown-Felder verweise ich immer nur auf die jeweils erste Zelle, gefolgt vom Überlauf-Operator #
Der Überlauf-Operator in der Datenüberprüfung
Und obwohl sich Länge und Inhalte der Listen für die Länder und Städte dynamisch verändern, wird mit Hilfe des #-Zeichens immer genau der benötigte Bereich ausgewählt und in den Dropdown-Feldern angeboten.
Genial! Und wo ist der Haken?
Der Überlauf-Operator steht – genau wie die dynamischen Array-Funktionen – nur in aktuellen Excel-Versionen zur Verfügung (Excel 2021, 2024 und Microsoft 365). Wer eine ältere Version einsetzt oder zwingend auf den Datenaustausch mit älteren Versionen angewiesen ist, muss leider darauf verzichten.
Diese dynamischen Ausgabebereiche benötigen außerdem Platz. Wenn also unterhalb (oder manchmal auch rechts daneben) Zellen schon gefüllt sind, kommt es zu einem #ÜBERLAUF!-Fehler. In diesem Fall muss also erst der entsprechende Bereich geleert werden, bevor die Funktion ihre Wirkung entfalten kann.
Fazit
Der Überlauf-Operator ist zusammen mit den dynamischen Array-Funktionen ein echter Game-Changer. Wenn du also mit einer aktuellen Excel-Version arbeitest, solltest du unbedingt diese Funktionen ausprobieren. Sie machen deine Werke einfacher, dynamischer und weniger fehleranfällig.
Hast du schon Erfahrungen mit dem Überlauf-Operator gemacht oder war das Konzept völlig neu für dich? Lass es uns in den Kommentaren wissen!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
ich habe den Überlauf-Operator vor einiger Zeit in einem anderen deiner Blog-Artikel kennengelernt, wo du ihn nebenbei erwähnt hast – wie du schreibst: ein echter Game-Changer! Deine Tipps sind wirklich super wertvoll!
Hallo Rebekka,
dankeschön. Es sind oft die Kleinigkeiten, die einen großen Unterschied machen können.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank – das macht echt Excel nochmal deutlich dynamischer… und das ganz „ohne Programmierung“. Ich kann dir und Rebekka nur zustimmen – ein echter Mehrwert!
Hallo Martin,
ich habe das gerade mal in einer meiner Dateien getestet und bin auf eine Einschränkung gestoßen, die du nicht erwähnt hattest. Wenn ich diesen dynamischen Bereich in einer Grafik darstellen möchte, laufe ich leider auf einen Fehler. Auch der Umweg über den Namensmanager hilft da nicht weiter… Schade eigentlich, aber vielleicht kommt das ja noch…
Hallo Matthias,
das mit den Diagrammen stimmt teilweise. In meinem 1. Beispiel oben hatte ich ja zwei Arrays erstellt: einmal die Länder und daneben die Einwohner. In diesem Fall funktioniert die erhoffte Dynamik bei den Diagrammen tatsächlich nicht.
Erstellt man aber ein Diagramm, das nur auf einem einzigen Array basiert, dann klappt die Dynamik sehr gut. Probier mal in meiner Beispieldatei die folgende Formel:
=SPALTENWAHL(FILTER(t_Städte;t_Städte[Kontinent]=G1);2;4)
Damit gibt es ein einziges Array mit Stadt und Einwohnerzahl. Wenn man hierüber wie gewohnt ein Diagramm erstellt, dann wächst es dynamisch mit. Der Überlauf-Operator taucht zwar nicht im Diagrammdatenbereich auf, aber das Diagramm erkennt trotzdem die dynamische Änderung.
Schöne Grüße,
Martin
Hallo Martin,
wer hätte gedacht, das der unscheinbare „Gartenzaun“ 😉 mal so mächtig wird. Wieder mal ein toller Artikel von dir. Ich muss allerdings gestehen, das ich viel zu wenig mit den Arrays arbeite (Mea Culpa). Trotzdem danke dafür.
Viele Grüße, Harald.