Wie erstellt man einen dynamischen benannten Bereich in Excel?
Normalerweise sind benannte Bereiche für Excel-Nutzer äußerst nützlich: Sie können eine Reihe von Werten in einer Spalte definieren, dieser einen Namen geben und dann auf den Bereich über diesen Namen statt über Zellbezüge verweisen. Meistens müssen Sie jedoch zu einem späteren Zeitpunkt neue Daten hinzufügen, um den Wertebereich Ihres Bezugs zu erweitern. In diesem Fall müssen Sie zum Namens-Manager zurückkehren und den Bereich neu definieren, um die neuen Werte einzuschließen. Um dies zu vermeiden, können Sie einen dynamischen benannten Bereich erstellen – so passen sich Ihre Bezüge automatisch an, sobald Sie eine neue Zeile oder Spalte zur Liste hinzufügen.
Erstellen eines dynamischen benannten Bereichs in Excel durch Erstellung einer Tabelle
Erstellen eines dynamischen benannten Bereichs in Excel mithilfe einer Funktion
Erstellen eines dynamischen benannten Bereichs in Excel mit VBA-Code
Erstellen eines dynamischen benannten Bereichs in Excel durch Erstellung einer Tabelle
Wenn Sie Excel 2007 oder eine neuere Version verwenden, ist der einfachste Weg, einen dynamischen benannten Bereich zu erstellen, die Erstellung einer benannten Excel-Tabelle.
Angenommen, Sie haben einen Bereich mit folgenden Daten, der in einen dynamischen benannten Bereich umgewandelt werden soll.
1. Zunächst definiere ich einen Zellennamen für diesen Bereich. Wählen Sie den Bereich A1:A6 aus, geben Sie den Namen Datum in das Namensfeld ein und drücken Sie anschließend die Eingabetaste. Definieren Sie auf dieselbe Weise einen Namen für den Bereich B1:B6 als Verkaufspreis. Gleichzeitig erstelle ich in einer leeren Zelle die Formel =SUMME(Verkaufspreis) – siehe Screenshot:
2. Wählen Sie den gewünschten Bereich aus und klicken Sie auf Einfügen > Tabelle; siehe Screenshot:
3. Im Dialogfeld Tabelle erstellen aktivieren Sie das Kontrollkästchen Meine Tabelle enthält Überschriften (wenn der Bereich keine Überschriften enthält, deaktivieren Sie es), und klicken Sie auf die Schaltfläche OK. Der Bereich wurde nun in eine Tabelle umgewandelt. Siehe Screenshots:
 |  |  |
4. Wenn Sie nach den vorhandenen Daten neue Werte eingeben, passt sich der benannte Bereich automatisch an – und die zugehörige Formel wird sofort mitaktualisiert. Siehe folgende Screenshots:
 | |  |
Hinweise:
1. Die neu eingegebenen Daten müssen direkt an die oben genannten Daten angrenzen – es dürfen also keine leeren Zeilen oder Spalten zwischen den neuen und den vorhandenen Daten liegen.
2. In der Tabelle können Sie Daten zwischen bestehende Werte einfügen.
Erstellen eines dynamischen benannten Bereichs in Excel mithilfe einer Funktion
In Excel 2003 oder früheren Versionen steht die erste Methode nicht zur Verfügung – daher folgt hier eine alternative Lösung. Die folgende VERSCHIEBUNG()-Funktion kann Ihnen dabei helfen, ist jedoch etwas umständlich. Angenommen, ich habe einen Datenbereich mit definierten Zellennamen: A1:A6 trägt den Namen Datum und B1:B6 den Namen Verkaufspreis. Gleichzeitig erstelle ich eine Formel für den Verkaufspreis. Siehe Screenshot:
Sie können den Zellenname in einen dynamischen Zellenname mit folgenden Schritten ändern:
1. Klicken Sie auf Formeln > Namens-Manager; siehe Screenshot:
2. Wählen Sie im Dialogfeld Namens-Manager das gewünschte Element aus und klicken Sie auf die Schaltfläche Bearbeiten.
3. Geben Sie im erscheinenden Dialogfeld Name bearbeiten diese Formel =VERSCHIEBUNG(Tabelle1!$A$1;0;0;ANZAHL2($A:$A);1) in das Textfeld Bezieht sich auf ein; siehe Screenshot:
4. Klicken Sie dann auf OK und wiederholen Sie Schritt 2 und Schritt 3, um diese Formel =VERSCHIEBUNG(Tabelle1!$B$1;0;0;ANZAHL2($B:$B); 1) in das Textfeld Bezieht sich auf für den Zellennamen Verkaufspreis einzufügen.
5. So wurden die dynamischen benannten Bereiche erstellt: Sobald Sie nach den vorhandenen Daten neue Werte eingeben, passt sich der benannte Bereich automatisch an – und auch die zugehörige Formel wird umgehend aktualisiert. Siehe Screenshots:
| | |
Hinweis: Wenn sich leere Zellen innerhalb Ihres Bereichs befinden, liefert Ihre Formel ein falsches Ergebnis. Der Grund: Leere Zellen werden nicht gezählt, wodurch Ihr Bereich kürzer ist, als er sein sollte – und die letzten Zellen des Bereichs bleiben unberücksichtigt.
Tipp: Erläuterung zu dieser Formel:
- =OFFSET(Bezug;Zeilen;Spalten;[Höhe];[Breite])
- =OFFSET(Tabelle1!$A$1;0;0;ANZAHL2($A:$A);1)
- Bezugentspricht der Startzellenposition; in diesem Beispiel Tabelle1!$A$1;
- Zeile gibt an, um wie viele Zeilen Sie sich relativ zur Startzelle nach unten bewegen (oder nach oben, wenn Sie einen negativen Wert angeben). In diesem Beispiel bedeutet 0, dass die Liste ab der ersten Zeile beginnt.
- Spalte gibt die Anzahl der Spalten an, um die Sie sich relativ zur Startzelle nach rechts bewegen (oder nach links, wenn Sie einen negativen Wert angeben). In der obigen Beispielformel bedeutet 0, dass keine Spalten nach rechts erweitert werden.
- [Höhe] entspricht der Höhe (bzw. der Anzahl der Zeilen) des Bereichs, beginnend an der angepassten Position. $A:$A zählt alle in Spalte A eingegebenen Elemente.
- [Breite] entspricht der Breite (bzw. der Anzahl der Spalten) des Bereichs, beginnend an der angepassten Position. In der obigen Formel ist die Liste eine Spalte breit.
Passen Sie diese Argumente ganz einfach Ihren Anforderungen an.
Erstellen eines dynamischen benannten Bereichs in Excel mit VBA-Code
Wenn Sie mehrere Spalten haben, könnten Sie für jede verbleibende Spalte eine eigene Formel eingeben – doch das wäre ein langwieriger und repetitiver Prozess. Um es einfacher zu machen, nutzen Sie einfach einen Code, um den dynamischen benannten Bereich automatisch zu erstellen.
1. Aktivieren Sie Ihr Arbeitsblatt.
2. Halten Sie die Tasten ALT + F11 gedrückt – dadurch öffnet sich das Fenster Microsoft Visual Basic für Applikationen.
3. Klicken Sie auf Einfügen > Modul und fügen Sie den folgenden Code im Modulfenster ein.
VBA-Code: Dynamischen benannten Bereich erstellen
Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName <> "" Then
wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
End If
Next
End Sub
4. Drücken Sie anschließend die Taste F5, um den Code auszuführen. Es werden nun dynamische benannte Bereiche erstellt, die nach den Werten der ersten Zeile benannt sind, sowie ein dynamischer Bereich namens MeineDaten, der sämtliche Daten umfasst.
5. Sobald Sie nach den Zeilen oder Spalten neue Werte eingeben, erweitert sich der Bereich automatisch. Siehe Screenshots:
 |
 |
 |
Hinweise:
1. Mit diesem Code werden die Zellennamen nicht im Namensfeld angezeigt. Um die Zellennamen bequem anzeigen und verwenden zu können, habe ich Kutools für Excel installiert; mithilfe der Navigation werden die erstellten dynamischen Zellennamen aufgelistet.
2. Mit diesem Code kann der gesamte Datenbereich sowohl vertikal als auch horizontal erweitert werden. Beachten Sie jedoch, dass beim Eingeben neuer Werte keine Leere Zeilen oder Spalten zwischen den Daten liegen dürfen.
3. Damit dieser Code korrekt funktioniert, sollte Ihr Datenbereich in Zelle A1 beginnen.
Verwandter Artikel:
Wie aktualisiert man ein Diagramm in Excel automatisch, sobald neue Daten eingegeben wurden?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins