Excel aus Microsoft 365 bekommt regelmäßig neue Funktionen – manche davon sind eher unscheinbar, andere haben das Potenzial, deine Arbeitsweise komplett zu verändern (Stichwort „dynamische Array-Funktionen“).
Die Funktion, um die es heute geht, fällt in beide Kategorien. Sie ist unscheinbar und löst ein Problem, das viele von uns schon lange kennen: Wie definiert man flexibel einen Bereich, der sich automatisch anpasst? Der immer so viele Zellen umfasst, wie gerade nötig, aber eben nicht mehr.
In diesem Artikel zeige ich dir, welche Möglichkeiten Excel seit kurzem bietet und wie du zukünftig mit sehr wenig Aufwand noch mehr Flexibilität in deine Tabellen bringst.
Und so geht’s:
Beispieldatei herunterladen
Was ist das Problem?
Wer viele Tabellenfunktionen nutzt, kommt immer mal wieder in die Verlegenheit, dass man einen Zellenbereich ansprechen muss, dessen finale Größe man noch nicht genau kennt. Die Lösung schaut in der Praxis entweder so aus:
Hardcore-Funktionsfreaks bauen sich ein Formelmonster aus INDEX, BEREICH.VERSCHIEBEN etc., um Bereiche dynamisch anzusprechen.
Pragmatiker markieren einen großzügig gewählten Bereich oder gleich komplette Spalten oder Zeilen.
Zur Verdeutlichung der Problematik habe ich die folgenden zwei Szenarien entworfen.
Szenario 1
In einer Liste (keine „intelligente“ Tabelle) sollen in einer Spalte ein paar Berechnungen durchgeführt werden. Und es ist davon auszugehen, dass die Liste in Zukunft noch länger werden wird. Was zu folgendem Problem führt:
Kommen neue Datensätze dazu, muss die Formel jedes Mal manuell nach unten nachgezogen werden. In der Realität sieht das meistens so aus, dass die Formal „auf Vorrat“ schon mal in weitere leere Zeilen kopiert wird. Je nach Anzahl dieser „Vorratszeilen“ entstehen damit mehr oder weniger viele unnötige Formeln, die vielleicht auch noch unschöne Fehlerwerte liefern:
Eine Formel wird auf Verdacht nach unten kopiert
Aber das lässt sich doch mit WENNFEHLER ganz leicht unterdrücken, wirst du jetzt sagen.
Stimmt!
Die Konsequenz daraus ist jedoch, dass nun noch mehr unnötige Berechnungen stattfinden. Auch wenn man sie dann vielleicht nicht mehr sieht. Je nach Aufbau und Umfang deiner Arbeitsmappe kann das in ungünstigen Fällen zu Performance-Problemen führen.
Szenario 2
Du hast für deine Umsatzzahlen für jeden Monat eigene, ansonsten identisch aufgebaute Arbeitsblätter: Januar, Februar, März. Die Anzahl der enthaltenen Zeilen kann in jedem Monat natürlich unterschiedlich sein.
Über die sehr geniale Funktion VSTAPELN möchtest du die 3 Monatslisten in einer Gesamtliste zusammenführen. Da du die Anzahl der Zeilen pro Monat noch nicht genau kennst, baust du in die Formel einen kleinen Puffer ein:
=VSTAPELN(Nord!A2:D10;Mitte!A2:D10;Süd!A2:D10)
Zusammenführen von Listen liefert unerwünschte Nullwerte
Wie man im Bild sieht, werden für die nicht befüllten Zeilen Nullwerte angezeigt. Nicht besonders schön…
Dann wäre definitiv mein Online-Kurs etwas für dich! Hier findest du alle weiteren Infos dazu:
Online-Kurs „Dynamische Array-Funktionen“
Wer mit formatierten („intelligenten“) Tabellen arbeitet, kann diese Probleme meistens umgehen. Aber in manchen Situationen hat man es eben nicht mit intelligenten Tabellen, sondern nur mit einfachen Listen zu tun.
In Microsoft 365 gibt es dafür seit Anfang 2025 (ca. ab Version 2412) eine Lösung. Genau gesagt sind es sogar zwei Lösungen.
Die Funktion ABSCHNBEREICH
Bevor wir uns diese neue Funktion mit dem sehr sperrigen Namen ansehen, möchte ich nochmal kurz auf die Berechnung der Einwohner/km² in der Beispieldatei eingehen. Bisher steht dort in Spalte E in jeder Zeile eine eigene Formel =C3/D3 (bzw. =C4/D4, =C5/D5 und so weiter).
Microsoft 365 mit seinen dynamischen Arrayfunktionen bietet dafür grundsätzlich eine bessere Möglichkeit. Anstatt jede Zelle einzeln anzusprechen, lassen sich gleich ganze Zellbereiche in einer einzigen Formel verwenden:
=C3:D23/D3:D23
Dynamisches Array erfordert nur eine einzige Formel
Das löst zwar nicht das Problem mit den Verdachtszeilen (ich bleibe jetzt einfach mal bei diesem Begriff), aber es gibt zumindest nur eine einzige Formel in Zelle F3, die automatisch in den angegebenen Bereich überläuft.
Und nun kommt die neue Funktion ABSCHNBEREICH ins Spiel. Das „ABSCHN“ steht für das Abschneiden eines Bereichs. Der englische Funktionsname TRIMRANGE ist da bedeutend handlicher.
Mit dieser Funktion lassen sich leere Bereiche um einen angegebenen Zellenbezug herum abschneiden. Mit den optionalen Parametern kann dabei genau festgelegt werden, wie dieses Abschneiden erfolgen soll.
=ABSCHNBEREICH(Bereich;[Zeilenkürzung];[Spaltenkürzung])
Für das Beispiel zur Berechnung der Einwohnerzahlen würde das so aussehen:
=ABSCHNBEREICH(C3:C23)/ABSCHNBEREICH(D3:D23)
Auch hier wird zwar jeweils der ganze Bereich bis Zeile 23 angegeben, der Ausgabebereich erstreckt sich aber nur bis zur letzten gefüllten Zeile 11.
Die Funktion ABSCHNBEREICH im Einsatz
In meinem Beispiel habe ich auf die beiden optionalen Parameter verzichtet. Mit ihnen ließe sich genauer steuern, wie etwaige leere Bereiche außenherum entfernt werden sollen:
=ABSCHNBEREICH(Bereich;[Zeilenkürzung];[Spaltenkürzung])
Zeilenkürzung: Bestimmt, welche Leerzeilen gekürzt werden sollen
0 – keine
1 – Kürzt vorausgehende leere Zeilen
2 – Kürzt nachfolgende leere Zeilen
3 – Kürzt vorausgehende und nachfolgende leere Zeilen
Spaltenkürzung: Bestimmt welche Leerspalten gekürzt werden sollen
0 – keine
1 – Kürzt vorausgehende leere Spalten
2 – Kürzt nachfolgende leere Spalten
3 – Kürzt vorausgehende und nachfolgende leere Spalten
Wie du siehst, man kann damit nicht nur leere Zeilen oben und unten, sondern auch leere Spalten links oder rechts des Datenbereichs entfernen.
Lässt man diese optionalen Parameter weg, wird automatisch der Wert 3 (also beides kürzen) angenommen. Wichtig zu wissen:
Befinden sich INNERHALB des angegebenen Bereichs leere Zeilen oder Spalten, werden diese NICHT entfernt.
Kommt jetzt in Zeile 12 eine neue Stadt mit neuen Einwohnerangaben dazu, erweitert sich der Ausgabebereich der Formel automatisch auch auf diese neue Zeile. Somit werden immer nur so viele Zeilen berechnet, wie wirklich nötig sind, obendrein spart man sich in diesem Beispiel die zusätzliche WENNFEHLER-Funktion.
Aber das ist trotzdem eine ziemlich lange Formel, verglichen mit der ursprünglichen Version, oder?
=ABSCHNBEREICH(C3:C23)/ABSCHNBEREICH(D3:D23)
vs.
=C3:C23/D3:D23
Danke, gut dass du fragst!
Gleichzeitig mit der neuen Funktion wurde auch ein neuer Bereichsoperator eingeführt.
Der Punkt als neuer Bereichsoperator
Wem die ABSCHNBEREICH-Funktion zu unhandlich ist, kann alternativ auf eine neue Schreibweise bei den Bereichsangaben zurückgreifen.
=C3:.C23/D3:.D23
Ein zusätzlicher Punkt nach dem üblichen Doppelpunkt bewirkt nämlich ebenfalls, dass die Zeilen nach dem Bereich abgeschnitten werden. Du ahnst es sicherlich bereits: Der Punkt kann auch vor dem Doppelpunkt gesetzt werden, um leere Zeilen (oder Spalten!) vor dem angegebenen Bereich abzuschneiden:
=A1:.F100 -> entfernt nachfolgende leere Zeilen/Spalten
=A1.:F100 -> entfernt führende leere Zeilen/Spalten
=A1.:.F100 -> entfernt führende und nachfolgende leere Zeilen und Spalten
Kommen wir nochmal auf das eingangs genannte Szenario 2 mit VSTAPELN zurück.
Natürlich wäre auch hier der Einsatz von ABSCHNBEREICH möglich und würde zu einem besseren Ergebnis führen:
=VSTAPELN(ABSCHNBEREICH(Nord!A2:D10;2);ABSCHNBEREICH(Mitte!A2:D10;2);ABSCHNBEREICH(Süd!A2:D10;2))
ABSCHNBEREICH kann Formeln sehr umfangreich machen
Aber die Formel wird damit schon arg lang. Die neue Schreibweise mit den Punkten hingegen liefert das perfekte Ergebnis und ist nur um 3 Zeichen länger als das Original:
=VSTAPELN(Nord!A2:.D10;Mitte!A2:.D10;Süd!A2:.D10)
Der zusätzliche Punkt in den Bezügen ist perfekt
Wer noch mehr Anregungen braucht:
Mein Excel-Kollege René Martin hat auf seinem Blog „Excel nervt“ noch ein paar interessante Beispiele vorgestellt.
Jetzt bist du dran:
Sind bei dir diese neuen Funktionen schon verfügbar?
Falls noch nicht:
Habe noch ein wenig Geduld, sie sollten auf jeden Fall in absehbarer Zeit auch in deinem Microsoft 365-Excel auftauchen!
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,
eine interessante Funktion, die ich so noch nicht kannte. Das löst das Problem der zu vielen Zeilen/Spalten sehr effizient, aber leider hilft das nicht, wenn z.B. mal im Süden auch Werte in Zeile 11 stehen. Gibt es dafür auch eine einfache neue Funktion, oder muss ich da weiter mit Anzahl oder so arbeiten?
Weiter so 😉
Vielen Dank,
Matthias
Hallo Matthias,
danke für dein Feedback. Die Lösung auf deine Frage ist einfach:
Gib in der VSTAPEL-Funktion einfach einen so großen Bereich an, dass er auf jeden Fall für alle zu erwartenden Zeilen ausreichen sollte 😉
Schöne Grüße,
Martin
Sehr sehr cool, vielen Dank für diesen Tipp!
Gestern gelesen und heute schon bei einigen Reports umgesetzt, die bisher immer bei neuen Daten auch ein manuelles runterkopieren der verformelten Spalten erfordert hat.
Ich habe das fast ausschließlich über den neuen Operator gelöst, aber teilweise gab es dabei auch erhebliche Hindernisse. Während beispielsweise ein Xverweis ganz ohne Probleme anzupassen war, hat eine einfache Wenn-Funktion so viele Probleme gemacht, dass ich sie gar nicht zum funktionieren gebracht habe – hier ist es daher doch bei einer „Verformelung auf Vorrat“ für ein paar Tausend Zeilen geblieben. Ebenfalls schwierig war eine Summe über mehrere Spalten – hier wurde natürlich das komplette Array summiert, nicht nur die „eigene“ Zeile. Letztlich ließ sich das lösen wie folgt:
Anstelle von:
=SUMME(AV2:AZ2)
habe ich angewendet:
=NACHZEILE(ABSCHNBEREICH(AV2:.AZ3000); LAMBDA(Zeile; SUMME(Zeile)))
Ich weiß schon jetzt, dass die Kollegen mich mal wieder hassen werden, sobald sie in dieser Datei etwas nachvollziehen möchten, aber darüber sehe ich hinweg – schließlich kriege ich nach wie vor Kommentare wie „Das ist so ne blöde Array Funktion, Julius das hast du doch wieder gemacht! Kümmer dich mal drum“ – und es sind bald 5 Jahre, dass diese Funktionen so leicht zu handlen sind und nicht umständlich über { }.
Ich bin schwer begeistert von diesem Beitrag, er ermöglicht so viele coole Sachen und ist top-aktuell, ich finde bei der Google-Suche praktisch noch gar nichts dazu – fühl mich wie ein Pionier 😉
Hallo Julius,
vielen Dank für das Lob, das freut mich sehr!
Ja, an manchen Stellen scheint es noch etwas zu ruckeln, da hilft nur ausprobieren.
Noch ein Tipp zu deiner Formel:
ABSCHNBEREICH kannst du weglassen, da du dort ja ohnehin den Punkt-Operator verwendest. Es geht also auch kürzer:
=NACHZEILE(AV2:.AZ3000; LAMBDA(Zeile; SUMME(Zeile)))
Ansonsten eine schöne Lösung!
Schöne Grüße,
Martin