Excel ist Ihr ultimatives Produktivitätstool. Erfahren Sie, wie Sie mit VBA benannte Bereiche in Excel erstellen, und finden Sie Tipps, um beim Arbeiten mit Daten und Formeln Zeit zu sparen.
Microsoft Excel ist eine leistungsstarke Softwareanwendung, mit der Sie Daten erstellen, verfolgen und analysieren können. Zusätzlich zu den Standardfunktionen von Excel können Sie auch VBA-Makros erstellen, um bestimmte Aufgaben zu automatisieren.
Möchten Sie mehr über das Erstellen benannter Bereiche und die Verwendung von VBA-Makros in Excel erfahren? Dann sind Sie bei uns genau richtig. In diesem Blogbeitrag besprechen wir, wie Sie benannte Bereiche in Excel mithilfe von VBA erstellen.
Wir stellen außerdem einige Beispiele dafür bereit, wie Sie benannte Bereiche in Ihrer eigenen Arbeit verwenden können. Fangen wir an!
Was ist ein benannter Bereich in Microsoft Excel?
Durch die Verwendung eines benannten Bereichs anstelle einer Spalten-/Zeilenreferenz können Sie mit einem benutzerdefinierten Namen auf eine Zelle oder eine Gruppe von Zellen verweisen. Die Möglichkeit, die Daten in Ihren Zellen anzugeben, ist ein großer Vorteil der Verwendung benannter Bereiche.
Hier ist ein kurzes Beispiel, wie Sie mithilfe benannter Bereiche feststellen können, ob die Versandkosten mit dem Produktpreis berechnet werden:
- = (B7 + B5 * C4) * (1 + A3)
- =(ShippingCharge + ProductPrice * Quantity) * (1 + TaxRate)
Option Nummer zwei gibt Ihnen, wie Sie sehen, sofort einen Hinweis darauf, ob im Preis der Produkte Versandkosten enthalten sind. Dadurch ist es für den Benutzer einfacher zu verstehen, wie die Formel funktioniert, ohne dass er Zeit damit verschwenden muss, durch die Zellen zu schauen, um herauszufinden, was was ist.
Wie verwende ich benannte Bereiche in Excel?
Als Finanzanalyst wird von Ihnen möglicherweise erwartet, dass Sie mit verschiedenen Zinssätzen experimentieren. Als Beispiel könnte alles verwendet werden, vom Steuersatz bis zur prognostizierten Inflationsrate. Sie können benannte Bereiche verwenden, um Variablen zu organisieren, die entweder selten geändert werden (z. B. Monat oder Jahr) oder über einen langen Zeitraum statisch bleiben (z. B. die Inflationsrate).
Hier ist eine Liste gebräuchlicher Namen, die Sie möglicherweise häufig verwenden, wenn Sie mit benannten Bereichen in Excel arbeiten:
- ReportDate
- Jahr
- Monat
- FcstID
- Steuersatz
- Rohdaten
Dies sind nur einige der Möglichkeiten – Sie können einen Bereich beliebig benennen, der den Excel-Regeln für benannte Bereiche entspricht.
Eindeutige Namen erstellen
Das Erstellen eines benannten Bereichs ist ein Kinderspiel. Jetzt müssen Sie nur noch die Zelle(n) auswählen, auf die Sie verweisen möchten, und einen Namen in das Namensfeld eingeben.
Da Ihr Name keine Leerzeichen enthalten darf, müssen Sie den ersten Buchstaben jedes neuen Wortes groß schreiben oder einen Unterstrich (_) verwenden, um Begriffe zu trennen. Um die Einrichtung des benannten Bereichs abzuschließen, drücken Sie die EINGABETASTE, nachdem Sie den Namen eingegeben haben.
Hinweis: Jeder mit dem Namensfeld eingerichtete benannte Bereich hat einen Arbeitsmappenbereich. Das bedeutet, dass jedes Arbeitsblatt in Ihrer Excel-Datei auf den benannten Bereich zugreifen kann.
Namen mit dem Namensmanager erstellen
Sie können benannte Bereiche im Namensmanager ändern und neue erstellen (Registerkarte „Formeln“ > Gruppe „Definierte Namen“ > Schaltfläche „Namensmanager“), wenn Sie Ihre benannten Bereiche noch weiter anpassen möchten.
Sie sollten sich darüber im Klaren sein, dass der Namensmanager Folgendes ermöglicht:
- Ändern Sie den Namen eines bereits vorhandenen benannten Bereichs,
- Ändern Sie die Referenzformel,
- Definieren Sie den Umfang, auf welche Arbeitsblätter über den Namen zugegriffen werden kann.
Da Sie nun wissen, was ein benannter Bereich ist und wie man ihn erstellt, schauen wir uns einige praktische Beispiele an, wie Sie ihn in Ihrer Arbeit verwenden können.
Praktische Beispiele für die Verwendung benannter Bereiche
Beispiel 1: Erstellen eines Dynamikbereichs
Ein dynamischer Bereich ist ein benannter Bereich, der sich automatisch an neue Daten anpasst, die dem Arbeitsblatt hinzugefügt werden. Dies ist eine großartige Möglichkeit, Zeit zu sparen, wenn sich Ihre Daten häufig ändern.
Beispiel 2: Erstellen eines 3D-Bereichs
Ein 3D-Bereich ist ein benannter Bereich, der auf Daten in mehreren Arbeitsblättern verweist. Dies kann eine große Zeitersparnis sein, wenn Sie mit großen Datenmengen arbeiten, die in mehreren Arbeitsblättern auf ähnliche Weise organisiert sind.
Beispiel 3: Erstellen eines benannten Arrays
Ein benanntes Array ist eine Art dynamischer Bereich, der sich auf ein Array von Werten bezieht. Dies kann eine großartige Möglichkeit sein, auf Daten zu verweisen, die in einem Tabellenformat organisiert sind.
Automatisieren Sie benannte Bereiche mit VBA-Makros
Nachdem wir nun einen kurzen Überblick über benannte Bereiche gegeben haben, werfen wir einen Blick auf einige VBA-Makros, die Ihnen bei der Automatisierung ihrer Verwendung helfen können.
Benannten Bereich hinzufügen
Der folgende VBA-Code zeigt, wie verschiedene Arten benannter Bereiche erstellt werden.
|
Sub NameRange_Add() 'ZWECK: Verschiedene Möglichkeiten zum Erstellen eines benannten Bereichs 'QUELLE: SoftwareKeep.com Zelle als Bereich dimmen Dim rng As Range Dim RangeName As String Dim CellName As String 'Einzelzellenverweis (Arbeitsmappenbereich) RangeName = "Price" CellName = "D7"
Setze Zelle = Arbeitsblätter("Sheet1").Bereich(Zellenname) ThisWorkbook.NamenFügen Sie Name:=RangeName, RefersTo:=cell hinzu'Einzelzellenreferenz (Arbeitsblattumfang) RangeName = "Jahr" CellName = "A2"
Setze Zelle = Arbeitsblätter("Sheet1").Bereich(Zellenname) Arbeitsblätter("Sheet1").NamenFügen Sie Name:=RangeName, RefersTo:=cell hinzu'Zellbereichsreferenz (Arbeitsmappenbereich) RangeName = "myData" CellName = "F9:J18"
Setze Zelle = Arbeitsblätter("Sheet1").Bereich(Zellenname) ThisWorkbook.NamenFügen Sie Name:=RangeName, RefersTo:=cell hinzu'Geheimer benannter Bereich (wird nicht im Namensmanager angezeigt) RangeName = "Benutzername" CellName = "L45"
Setze Zelle = Arbeitsblätter("Sheet1").Bereich(Zellenname) ThisWorkbook.NamenFügen Sie Name:=RangeName, RefersTo:=cell, Visible:=False hinzuEnd Sub |
Schleife durch benannte Bereiche
Der nächste VBA-Makrocode zeigt, wie Sie die benannten Bereiche Ihrer Tabelle durchlaufen.
|
Sub NamedRange_Loop() 'ZWECK: Alle benannten Bereiche in der aktiven Arbeitsmappe löschen 'QUELLE: SoftwareKeep.com Dim nm As Name 'Durchlaufen Sie jeden benannten Bereich in der Arbeitsmappe Für jeden nm in ActiveWorkbook.Namen Debuggen.Drucken nm.Name, nm.RefersTo Nächster nm
'Durchlaufen Sie jeden benannten Bereich, der auf ein bestimmtes Arbeitsblatt beschränkt ist Für jedes Nanometer in Arbeitsblättern („Sheet1“).Namen Debuggen.Drucken nm.Name, nm.RefersTo Nächster nm End Sub |
Alle benannten Bereiche löschen
Diese VBA-Funktion hilft Ihnen, einen großen Teil der benannten Bereiche in Excel zu bereinigen, die Sie in Ihrem Projekt nicht mehr benötigen.
|
Sub NamedRange_DeleteAll() 'ZWECK: Alle benannten Bereiche in der ActiveWorkbook löschen (Druckbereiche optional) 'QUELLE: SoftwareKeep.com Dim nm As Name DeleteCount As Long dimmen 'Auch PrintAreas löschen? UserAnswer = MsgBox("Möchten Sie Druckbereiche überspringen?", vbYesNoCancel) Wenn UserAnswer = vbYes, dann SkipPrintAreas = True Wenn UserAnswer = vbCancel, dann beenden Sie Sub 'Fehlerbehandler für den Fall, dass Funktionsfehler gelöscht werden Bei Fehler GoTo Skip 'Durchlaufen Sie jeden Namen und löschen Sie Für jeden nm in ActiveWorkbook.Namen Wenn SkipPrintAreas = True And Right(nm.Name, 10) = „Print_Area“ Dann GoTo Skip
'Fehlerbehandler für den Fall, dass Funktionsfehler gelöscht werden Bei Fehler GoTo Skip
'Benannten Bereich löschen nm.Löschen DeleteCount = DeleteCount + 1
Überspringen: 'Fehlerbehandler zurücksetzen Bei Fehler GoTo 0 Weiter
'Berichtsergebnis Wenn DeleteCount = 1, dann MsgBox „[1] Name wurde aus dieser Arbeitsmappe entfernt." Sonst MsgBox „[“ & DeleteCount & „]-Namen wurden aus dieser Arbeitsmappe entfernt." End If End Sub |
Benannte Bereiche mit Fehlern löschen
Nur benannte Bereiche mit Fehlern werden mit diesem VBA-Code gelöscht. Diese Probleme können durch das Löschen von Arbeitsblättern oder Zeilen/Spalten ausgelöst werden.
|
Sub NamedRange_DeleteErrors() 'ZWECK: Alle benannten Bereiche mit #REF-Fehler im ActiveWorkbook löschen 'QUELLE: SoftwareKeep.com Dim nm As Name DeleteCount As Long dimmen 'Durchlaufen Sie jeden Namen und löschen Sie Für jeden nm in ActiveWorkbook.Namen Wenn InStr(1, nm.RefersTo, "#REF!") > 0 Then 'Fehlerbehandler für den Fall, dass Funktionsfehler gelöscht werden Bei Fehler GoTo Skip
'Benannten Bereich löschen nm.Löschen DeleteCount = DeleteCount + 1 End If Überspringen: 'Fehlerbehandler zurücksetzen Bei Fehler GoTo 0 Weiter
'Berichtsergebnis Wenn DeleteCount = 1, dann MsgBox „[1] fehlerhafter Name wurde aus dieser Arbeitsmappe entfernt." Sonst MsgBox „[“ & DeleteCount & „] fehlerhafte Namen wurden aus dieser Arbeitsmappe entfernt." End If
End Sub |
TL;DR
- Benannte Bereiche sind eine großartige Möglichkeit, Ihre Daten in Excel zu organisieren.
- Sie können sie zum Erstellen dynamischer Bereiche, 3D-Bereiche und benannter Arrays verwenden.
- Sie können die Verwendung benannter Bereiche auch mit VBA-Makros automatisieren.
Fazit
Excel ist ein erstaunliches Tool, das mit umfangreichen Automatisierungsfunktionen selbst die banalsten Aufgaben spannend gestalten kann. Wenn Sie sich das Leben leichter machen möchten, ist das Erlernen der Arbeit mit erweiterten Tools wie Named Ranges mit VBA ein guter Anfang.
Wir hoffen, dass dieser Artikel Ihnen dabei geholfen hat, etwas mehr über die Verwendung benannter Bereiche in Excel zu erfahren. Wenn Sie Fragen haben, hinterlassen Sie uns wie immer gerne unten einen Kommentar!
Vielen Dank fürs Lesen.
Noch etwas
Suchen Sie nach weiteren Tipps? Schauen Sie sich unsere anderen Anleitungen in unserem Blog an oder besuchen Sie unser Hilfecenter für eine Fülle von Informationen zur Behebung verschiedener Probleme.
Melden Sie sich für unseren Newsletter an und greifen Sie frühzeitig auf unsere Blogbeiträge, Aktionen und Rabattcodes zu. Außerdem erfahren Sie als Erster von unseren neuesten Leitfäden, Angeboten und anderen aufregenden Updates!
Empfohlene Artikel
» So fügen Sie führende Nullen in Excel hinzu und entfernen sie
» So verwenden Sie „Wenn eine Zelle enthält“-Formeln in Excel
» So sperren und entsperren Sie die Bildlaufsperre in Excel?
Wenn Sie Fragen oder Wünsche haben, können Sie uns gerne an uns wenden.