Ein Namensbereich ist eine Zelle oder eine Gruppe von Zellen in Excel, die neben ihrer Zellenadresse einen definierten Namen hat. Dieser definierte Name können Sie als Benutzer frei dem Bereich zuweisen und anschließend mit beispielsweise einer Formel darauf verweisen.

Anstelle dass Sie mit einer Formel auf die Zellen A1:A10 verweisen, können Sie diesem Zellbereich einen Namen geben. Wenn Sie diesem Zellbereich den Namen „Umsatz“ zuweisen, dann können Sie anschliessend die Formel wie folgt schreiben: =SUMME(Umsatz). Excel weiss, dann, dass Umsatz für A1:A10 steht.

In diesem Artikel erklären wir zwei Varianten, wie dieser Namensbereich automatisch erweitert wird, wenn ein neuer Eintrag hinzukommt. Im konkreten Fall wenn der Bereich neu A1:A11 ist.

Es gibt eine Reihe von Möglichkeiten, einen dynamischen benannten Bereich zu erstellen. In diesem Artikel besprechen wir, wie Sie einen dynamischen benannten Bereich mit einer BEREICH.VERSCHIEBEN-Formel und einer INDEX-Formel erstellen.

Dynamischer Namen mit BEREICH.VERSCHIEBEN definieren

Ein dynamischer benannter Bereich kann mithilfe der BEREICH.VERSCHIEBEN-Formel erstellt werden. Die Formel dafür lautet:

=BEREICH.VERSCHIEBEN($A$1;0;0;ANZAHL2($A:$A);1)

Die Formel verwendet auch die ANZAHL2-Formel. Dies ist für die Spaltenreferenz, auf die es sich bezieht.

Analysieren wir kurz die Formel und ihre Teile. Die BEREICH.VERSCHIEBEN-Formel hat 5 Parameter, von denen drei erforderlich sind und die letzten beiden optional sind.

Aus der obigen Formel ist $A$1 der Referenzpunkt, die beide Nullen sind die Zeilen-Verschiebung und die Spalten Verschiebung. D.h. hier wird angegeben um wie viele Zeilen und Spalten der Bereich verschoben werden soll.

Die nächsten beiden Parameter sind die Höhe des Bereichs, die von der ANZAHL2-Formel bereitgestellt wird. Die Formel ist der Parameter, der den benannten Bereich dynamisch macht.

Der letzte Parameter ist 1. Dies repräsentiert nur die Breite des benannten Bereichs.

Unter Verwendung der obigen Formel erstreckt sich der benannte Bereich von Zelle A2 bis A14 (das Leerzeichen nach dem Eintrag wird weiterhin berücksichtigt). Bei jedem neuen Eintrag in Spalte A wird der benannte Bereich bis A15 erweitert.

Dynamischer Namensbereicht mit Index

Eine andere Methode zum Erstellen eines dynamischen Namensbereich ist die Verwendung der INDEX-Formel. Die Formel dafür lautet:

=$A$2:INDEX($A:$A;ANZAHL2($A:$A))

Genau wie für den BEREICH.VERSCHIEBEN verwendet die Formel auch eine ANZAHL2-Formel. Die ANZAHL2-Formel dient zum Abrufen der letzten Zeile

Analysieren wir kurz die Formel und ihre Teile. Die gesamte Formel hat 3 Teile, während der INDEX-Formelteil 2 Parameter hat.

Der erste Teil ist die Startreferenz des benannten Bereichs. Genau wie bei einer normalen Referenz für eine Gruppe von Zellen folgt auf $A$2 ein „:“. Der nächste Teil ist die INDEX-Formel.

Der erste Teil der Indexformel ist die Adresse der Zellengruppe.

Der zweite Parameter dafür ist die ANZAHL2-Formel. Verwenden Sie dann die ANZAHL2-Formel, um die „letzte Zeile“ im Bereich herauszufinden. Unter Verwendung des obigen Bildbeispiels funktioniert ANZAHL2 hier gut, da es 13 Werte in Spalte A gibt, einschließlich einer Kopfzeile. ANZAHL2 gibt also 13 zurück, die direkt als Zeilennummer in INDEX eingeht.

Wie in den obigen Bildern zu sehen, erweitert sich der benannte Bereich, wenn ein neuer Eintrag vorhanden ist. Beachten Sie außerdem, dass nach der Liste kein Leerzeichen vorhanden ist. Jetzt ist die Liste von A2:A13 und nicht mehr bis 14.

Fazit dynamischer Namensbereich

Das Erstellen eines dynamischen benannten Bereichs kann entweder mithilfe der Formel BEREICH.VERSCHIEBEN oder INDEX erstellt werden. Unabhängig davon, welche Formel verwendet wurde, wird normalerweise die ANZAHL2-Formel verwendet, um bei der Einrichtung des dynamischen benannten Bereichs zu helfen.

Benötigen Sie einen VBA Programmierer?

Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).

Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular.