Wie erstelle ich einen dynamischen Namensbereich in Excel?
Normalerweise Benannte Bereiche sind für Excel-Benutzer sehr nützlich. Sie können eine Reihe von Werten in einer Spalte definieren, dieser Spalte einen Namen geben und dann anstelle der Zellreferenzen nach Namen auf diesen Bereich verweisen. Meistens müssen Sie jedoch neue Daten hinzufügen, um die Datenwerte Ihres angegebenen Bereichs in Zukunft zu erweitern. In diesem Fall müssen Sie zu zurückkehren Formeln > Name Manager und definieren Sie den Bereich neu, um den neuen Wert einzuschließen. Um dies zu vermeiden, können Sie einen dynamischen benannten Bereich erstellen. Dies bedeutet, dass Sie die Zellreferenzen nicht jedes Mal anpassen müssen, wenn Sie der Liste eine neue Zeile oder Spalte hinzufügen.
Erstellen Sie einen dynamischen benannten Bereich in Excel, indem Sie eine Tabelle erstellen
Erstellen Sie mit Funktion einen dynamischen benannten Bereich in Excel
Erstellen Sie einen dynamischen benannten Bereich in Excel mit VBA-Code
Erstellen Sie einen dynamischen benannten Bereich in Excel, indem Sie eine Tabelle erstellen
Wenn Sie Excel 2007 oder eine neuere Version verwenden, können Sie am einfachsten einen dynamischen benannten Bereich erstellen, indem Sie eine benannte Excel-Tabelle erstellen.
Angenommen, Sie haben eine Reihe von folgenden Daten, die zu einem dynamischen benannten Bereich werden müssen.
1. Zunächst werde ich Bereichsnamen für diesen Bereich definieren. Wählen Sie den Bereich A1: A6 und geben Sie den Namen ein Datum in die Namensfeld, dann drücken Enter Schlüssel. So definieren Sie einen Namen für den Bereich B1: B6 als Verkaufspreis auf die gleiche Weise. Gleichzeitig erstelle ich eine Formel = Summe (Verkaufspreis) in einer leeren Zelle siehe Screenshot:
2. Wählen Sie den Bereich aus und klicken Sie auf Insert > Tisch, siehe Screenshot:
3. In dem Tabelle erstellen Kontrollkästchen aktivieren Meine Tabelle hat Überschriften (Wenn der Bereich keine Überschriften enthält, deaktivieren Sie ihn.) Klicken Sie auf OK Schaltfläche, und die Bereichsdaten wurden in eine Tabelle konvertiert. Siehe Screenshots:
 |
 |
 |
4. Wenn Sie nach den Daten neue Werte eingeben, wird der benannte Bereich automatisch angepasst und die erstellte Formel wird ebenfalls geändert. Siehe folgende Screenshots:
 |
|
 |
Anmerkungen:
1. Ihre neu eingegebenen Daten müssen neben den oben genannten Daten liegen. Dies bedeutet, dass zwischen den neuen Daten und den vorhandenen Daten keine leeren Zeilen oder Spalten vorhanden sind.
2. In der Tabelle können Sie Daten zwischen den vorhandenen Werten einfügen.
Erstellen Sie mit Funktion einen dynamischen benannten Bereich in Excel
In Excel 2003 oder einer früheren Version ist die erste Methode nicht verfügbar. Hier ist eine andere Möglichkeit für Sie. Folgende OFFSET () Funktion kann diesen Gefallen für Sie tun, aber es ist etwas mühsam. Angenommen, ich habe einen Datenbereich, der die von mir definierten Bereichsnamen enthält, z. A1: A6 Der Bereichsname lautet Datum und B1: B6 Bereichsname ist VerkaufspreisGleichzeitig erstelle ich eine Formel für die Verkaufspreis. Siehe Screenshot:
Sie können die Bereichsnamen mit den folgenden Schritten in dynamische Bereichsnamen ändern:
1. Gehe zu klicken Formeln > Name Manager, siehe Screenshot:
2. In dem Name Manager Wählen Sie im Dialogfeld das Element aus, das Sie verwenden möchten, und klicken Sie auf Bearbeiten .
3. In der herausgesprungen Namen bearbeiten Dialog, geben Sie diese Formel ein = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) in die Bezieht sich auf Textfeld, siehe Screenshot:
4. Dann klick OKund wiederholen Sie dann Schritt 2 und Schritt 3, um diese Formel zu kopieren = OFFSET (Sheet1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) in die Bezieht sich auf Textfeld für die Verkaufspreis Bereichsname.
5. Und die dynamischen benannten Bereiche wurden erstellt. Wenn Sie nach den Daten neue Werte eingeben, wird der benannte Bereich automatisch angepasst und die erstellte Formel wird ebenfalls geändert. Siehe Screenshots:
|
|
|
Hinweis: Wenn sich in der Mitte Ihres Bereichs leere Zellen befinden, ist das Ergebnis Ihrer Formel falsch. Dies liegt daran, dass die nicht leeren Zellen nicht gezählt werden, sodass Ihr Bereich kürzer als erwartet ist und die letzten Zellen im Bereich weggelassen werden.
Tipp: Erklärung für diese Formel:
- = OFFSET (Referenz, Zeilen, Spalten, [Höhe], [Breite])
- = OFFSET (Sheet1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
- Referenz entspricht in diesem Beispiel der Position der Startzelle Sheet1! $ A $ 1;
- Reihe bezieht sich auf die Anzahl der Zeilen, die Sie relativ zur Startzelle nach unten verschieben (oder nach oben, wenn Sie einen negativen Wert verwenden). In diesem Beispiel gibt 0 an, dass die Liste ab der ersten Zeile nach unten beginnt
- Überblick Entspricht der Anzahl der Spalten, die Sie relativ zur Startzelle nach rechts verschieben (oder mit einem negativen Wert nach links). In der obigen Beispielformel bedeutet 0, dass 0 Spalten nach rechts erweitert werden.
- [Höhe] entspricht der Höhe (oder Anzahl der Zeilen) des Bereichs ab der eingestellten Position. $ A: $ A, es werden alle in Spalte A eingegebenen Elemente gezählt.
- [Breite] entspricht der Breite (oder Anzahl der Spalten) des Bereichs ab der eingestellten Position. In der obigen Formel ist die Liste 1 Spalte breit.
Sie können diese Argumente nach Bedarf ändern.
Erstellen Sie einen dynamischen benannten Bereich in Excel mit VBA-Code
Wenn Sie mehrere Spalten haben, können Sie für alle verbleibenden Spalten eine einzelne Formel wiederholen und eingeben. Dies wäre jedoch ein langer, sich wiederholender Vorgang. Zur Vereinfachung können Sie einen Code verwenden, um den dynamischen benannten Bereich automatisch zu erstellen.
1. Aktivieren Sie Ihr Arbeitsblatt.
2. Halten Sie die Taste gedrückt ALT + F11 Tasten, und es öffnet die Microsoft Visual Basic für Applikationen-Fenster.
3. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Feld ein Modulfenster.
VBA-Code: Dynamischen Namensbereich 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. Dann drücken F5 Schlüssel, um den Code auszuführen, und es werden einige dynamische benannte Bereiche generiert, die mit den Werten der ersten Zeile benannt werden, und es wird auch ein dynamischer Bereich mit dem Namen erstellt Meine Daten welches die gesamten Daten abdeckt.
5. Wenn Sie nach den Zeilen oder Spalten neue Werte eingeben, wird auch der Bereich erweitert. Siehe Screenshots:
 |
|
 |
Anmerkungen:
1. Mit diesem Code werden die Bereichsnamen nicht in der angezeigt Namensfeld, um die Bereichsnamen bequem anzuzeigen und zu verwenden, habe ich installiert Kutools for ExcelMit seinen Navigationsbereichwerden die erstellten Dynamikbereichsnamen aufgelistet.
2. Mit diesem Code kann der gesamte Datenbereich vertikal oder horizontal erweitert werden. Beachten Sie jedoch, dass bei der Eingabe neuer Werte keine leeren Zeilen oder Spalten zwischen den Daten stehen sollten.
3. Wenn Sie diesen Code verwenden, sollte Ihr Datenbereich in Zelle A1 beginnen.
In Verbindung stehender Artikel:
Wie aktualisiere ich ein Diagramm automatisch, nachdem ich neue Daten in Excel eingegeben habe?
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, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...
Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
- Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
