Wie erstellt man einen dynamischen benannten Bereich in Excel?

Normalerweise sind benannte Bereiche sehr nützlich für Excel-Benutzer. Sie können eine Reihe von Werten in einer Spalte definieren, dieser Spalte einen Namen geben und dann auf diesen Bereich über den Namen statt über die Zellbezüge verweisen. Aber meistens müssen Sie in Zukunft neue Daten hinzufügen, um die Datenwerte Ihres referenzierten Bereichs zu erweitern. In diesem Fall müssen Sie zurück zu Formeln > Namen-Manager gehen und den Bereich neu definieren, um den neuen Wert einzuschließen. Um dies zu vermeiden, können Sie einen dynamischen benannten Bereich erstellen, was bedeutet, dass Sie die Zellbezüge nicht jedes Mal anpassen müssen, wenn Sie eine neue Zeile oder Spalte zur Liste hinzufügen.

Dynamischen benannten Bereich in Excel durch Erstellen einer Tabelle erstellen

Dynamischen benannten Bereich in Excel mit Funktion erstellen

Dynamischen benannten Bereich in Excel mit VBA-Code erstellen

Dynamischen benannten Bereich in Excel durch Erstellen einer Tabelle erstellen

Wenn Sie Excel 2007 oder neuere Versionen verwenden, ist die einfachste Möglichkeit, einen dynamischen benannten Bereich zu erstellen, eine benannte Excel-Tabelle zu erstellen.

Angenommen, Sie haben einen Bereich mit folgenden Daten, der zu einem dynamischen benannten Bereich werden soll.

1. Zuerst werde ich diesem Bereich Bereichsnamen zuweisen. Wählen Sie den Bereich A1:A6 aus und geben Sie den Namen Datum in das Namensfeld ein, dann drücken Sie die Eingabetaste. Weisen Sie dem Bereich B1:B6 auf die gleiche Weise den Namen Verkaufspreis zu. Gleichzeitig erstelle ich in einer leeren Zelle die Formel =summe(Verkaufspreis), siehe Screenshot:

2. Wählen Sie den Bereich aus und klicken Sie auf Einfügen > Tabelle, siehe Screenshot:

3. Aktivieren Sie im Dialogfeld „Tabelle erstellen“ das Kontrollkästchen Meine Tabelle hat Überschriften (falls der Bereich keine Überschriften hat, deaktivieren Sie es), klicken Sie auf die Schaltfläche OK, und die Bereichsdaten wurden in eine Tabelle konvertiert. Siehe Screenshots:

4. Und wenn Sie nach den Daten neue Werte eingeben, wird sich der benannte Bereich automatisch anpassen und die erstellte Formel ändert sich ebenfalls. Siehe folgende Screenshots:

Hinweise:

1. Ihre neu eingegebenen Daten müssen direkt an die vorhandenen Daten angrenzen, d.h., es dürfen keine leeren Zeilen oder Spalten zwischen den neuen und den bestehenden Daten sein.

2. In der Tabelle können Sie Daten zwischen den vorhandenen Werten einfügen.

Dynamischen benannten Bereich in Excel mit Funktion erstellen

In Excel 2003 oder früheren Versionen ist die erste Methode nicht verfügbar, daher gibt es hier eine andere Möglichkeit für Sie. Die folgende OFFSET()-Funktion kann Ihnen dabei helfen, aber sie ist etwas umständlich. Angenommen, ich habe einen Datenbereich, der die Bereichsnamen enthält, die ich definiert habe, zum Beispiel A1:A6 mit dem Bereichsnamen Datum und B1:B6 mit dem Bereichsnamen Verkaufspreis. Gleichzeitig erstelle ich eine Formel für Verkaufspreis. Siehe Screenshot:

Sie können die Bereichsnamen mit den folgenden Schritten in dynamische Bereichsnamen ändern:

1. Klicken Sie auf Formeln > Namen-Manager, siehe Screenshot:

2. Wählen Sie im Dialogfeld Namen-Manager das Element aus, das Sie verwenden möchten, und klicken Sie auf die Schaltfläche Bearbeiten.

3. Geben Sie im erscheinenden Dialogfeld Namen bearbeiten diese Formel =OFFSET(Arbeitsblatt1!$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 =OFFSET(Arbeitsblatt1!$B$1; 0; 0; ANZAHL2($B:$B); 1) in das Textfeld Bezieht sich auf für den Bereichsnamen Verkaufspreis zu kopieren.

5. Und der dynamische benannte Bereich wurde erstellt. Wenn Sie nach den Daten neue Werte eingeben, wird sich der benannte Bereich automatisch anpassen und die erstellte Formel ändert sich ebenfalls. Siehe Screenshots:

Hinweis: Wenn es leere Zellen in der Mitte Ihres Bereichs gibt, wird das Ergebnis Ihrer Formel falsch sein. Das liegt daran, dass die nicht-leeren Zellen nicht gezählt werden, sodass Ihr Bereich kürzer sein wird als er sollte, und die letzten Zellen im Bereich werden ausgelassen.

Tipp: Erklärung für diese Formel:

• =OFFSET(Bezug;Zeilen;Spalten;[Höhe];[Breite])
• 
• =OFFSET(Arbeitsblatt1!$A$1; 0; 0; ANZAHL2($A:$A); 1)

• Bezug entspricht der Startposition der Zelle, in diesem Beispiel Arbeitsblatt1!$A$1;
• Zeile bezieht sich auf die Anzahl der Zeilen, die Sie nach unten verschieben werden, relativ zur Startzelle (oder nach oben, wenn Sie einen negativen Wert verwenden). In diesem Beispiel zeigt 0 an, dass die Liste ab der ersten Zeile beginnt.
• Spalte entspricht der Anzahl der Spalten, die Sie nach rechts verschieben werden, relativ zur Startzelle (oder nach links, wenn Sie einen negativen Wert verwenden). Im obigen Beispiel zeigt 0 an, dass um 0 Spalten nach rechts erweitert wird.
• [Höhe] entspricht der Höhe (oder 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 (oder der Anzahl der Spalten) des Bereichs, beginnend an der angepassten Position. In der obigen Formel wird die Liste 1 Spalte breit sein.

Sie können diese Argumente nach Bedarf ändern.

Dynamischen benannten Bereich in Excel mit VBA-Code erstellen

Wenn Sie mehrere Spalten haben, könnten Sie die individuelle Formel für alle verbleibenden Spalten wiederholen und eingeben, aber das wäre ein langer, repetitiver Prozess. Um die Dinge einfacher zu machen, können Sie einen Code verwenden, um den dynamischen benannten Bereich automatisch zu erstellen.

1. Aktivieren Sie Ihr Arbeitsblatt.

2. Halten Sie die ALT + F11-Tasten gedrückt, und es öffnet sich das Fenster Microsoft Visual Basic for Applications.

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 dann die F5-Taste, um den Code auszuführen, und es werden einige dynamische benannte Bereiche generiert, die mit den Werten der ersten Zeile benannt sind. Außerdem wird ein dynamischer Bereich namens MyData erstellt, der den gesamten Datensatz abdeckt.

5. Wenn Sie nach den Zeilen oder Spalten neue Werte eingeben, wird sich der Bereich ebenfalls erweitern. Siehe Screenshots:

Hinweise:

1. Mit diesem Code werden die Bereichsnamen nicht im Namensfeld angezeigt. Um die Bereichsnamen bequem anzuzeigen und zu verwenden, habe ich Kutools für Excel installiert. Mit seiner Navigation werden die erstellten dynamischen Bereichsnamen aufgelistet.

2. Mit diesem Code kann der gesamte Datenbereich vertikal oder horizontal erweitert werden, aber denken Sie daran, dass es keine leeren Zeilen oder Spalten zwischen den Daten geben darf, wenn Sie neue Werte eingeben.

3. Wenn Sie diesen Code verwenden, sollte Ihr Datenbereich bei Zelle A1 beginnen.

Verwandter Artikel:

Wie aktualisiert man ein Diagramm automatisch, nachdem man neue Daten in Excel eingegeben hat?