Benannte Bereiche sind eine nützliche, aber häufig zu wenig genutzte Funktion von Microsoft Excel . Benannte Bereiche können Formeln verständlicher machen (und debuggen), die Erstellung komplizierter Tabellenkalkulationen vereinfachen und Ihre Makros vereinfachen.

Ein benannter Bereich ist nur ein Bereich (entweder eine einzelne Zelle oder ein Bereich von Zellen), dem Sie einen Namen zuweisen. Sie können diesen Namen dann anstelle normaler Zellreferenzen in Formeln, in Makros und zum Definieren der Quelle für Diagramme oder Datenvalidierung verwenden.

Die Verwendung eines Bereichsnamens wie TaxRate anstelle eines standardmäßigen Zellbezugs wie Sheet2 !$C$11 kann das Verständnis und Debuggen/Prüfen einer Tabelle erleichtern.

Benannte Bereiche in Excel verwenden

Schauen wir uns zum Beispiel ein einfaches Bestellformular an.  Unsere Datei enthält ein ausfüllbares Bestellformular mit einem Dropdown-Menü zur Auswahl der Versandart sowie ein zweites Blatt mit einer Tabelle der Versandkosten und des Steuersatzes.

Version 1 (ohne benannte Bereiche) verwendet in ihren Formeln normale Zellreferenzen im A1-Stil (siehe Formelleiste unten).^(A1-style)

Version 2 verwendet benannte Bereiche, wodurch die Formeln viel einfacher zu verstehen sind. Benannte Bereiche erleichtern auch die Eingabe von Formeln, da Excel eine Liste mit Namen anzeigt, einschließlich Funktionsnamen, aus der Sie auswählen können, wenn Sie mit der Eingabe eines Namens in eine Formel beginnen.  Doppelklicken Sie auf^{(Double-click)} den Namen in der Auswahlliste, um ihn Ihrer Formel hinzuzufügen.

Wenn Sie das Namens-Manager^{(Name Manager)} - Fenster über die Registerkarte „ Formeln^(Formulas) “ öffnen, wird eine Liste der Bereichsnamen und der Zellbereiche angezeigt, auf die sie verweisen.

Aber benannte Bereiche haben auch andere Vorteile. In unseren Beispieldateien wird die Versandart über ein Dropdown (Datenvalidierung) in Zelle B13 auf Sheet1 ausgewählt^(Sheet1) . Die ausgewählte Methode wird dann verwendet, um die Versandkosten auf Sheet2 nachzuschlagen^(Sheet2) .

Ohne benannte Bereiche müssen die Dropdown-Auswahlmöglichkeiten manuell eingegeben werden, da die Datenüberprüfung es Ihnen nicht ermöglicht, eine Quellliste auf einem anderen Blatt auszuwählen. Daher müssen alle Auswahlmöglichkeiten zweimal eingegeben werden: einmal in der Dropdown-Liste und erneut auf Sheet2 . Außerdem müssen die beiden Listen übereinstimmen.

Wenn bei einem der Einträge in einer der Listen ein Fehler gemacht wird, generiert die Versandkostenformel einen #N/A-Fehler, wenn die falsche Auswahl ausgewählt wird. Die Benennung der Liste auf Sheet2 als ShippingMethods beseitigt beide Probleme.

Sie können einen benannten Bereich referenzieren, wenn Sie die Datenvalidierung für eine Dropdown-Liste definieren, indem Sie beispielsweise einfach =ShippingMethods in das Quellfeld eingeben. Auf diese Weise können Sie eine Auswahlliste verwenden, die sich auf einem anderen Blatt befindet.

Und wenn die Dropdown-Liste auf die tatsächlichen Zellen verweist, die in der Suche verwendet werden (für die Versandkostenformel), dann stimmen die Dropdown-Auswahlmöglichkeiten immer mit der Suchliste überein, wodurch #NV-Fehler vermieden werden.

Erstellen Sie einen benannten Bereich in Excel

Um einen benannten Bereich zu erstellen, wählen Sie einfach die Zelle oder den Zellbereich aus, die Sie benennen möchten, klicken Sie dann in das Namensfeld^{( Name Box)} (wo normalerweise die ausgewählte Zellenadresse angezeigt wird, direkt links neben der Formelleiste^{(Formula Bar)} ) und geben Sie den Namen ein, den Sie verwenden möchten , und drücken Sie die Eingabetaste^(Enter) .

Sie können einen benannten Bereich auch erstellen, indem Sie im Namensmanager^(Manager) - Fenster auf die Schaltfläche „ Neu “ klicken. ^(New)Dadurch wird ein  Fenster „ Neuer Name “ geöffnet, in dem Sie den neuen Namen eingeben können.^{(New Name)}

Standardmäßig wird der zu benennende Bereich auf den ausgewählten Bereich eingestellt, wenn Sie auf die Schaltfläche „ Neu^(New) “ klicken , aber Sie können diesen Bereich vor oder nach dem Speichern des neuen Namens bearbeiten.

Beachten Sie, dass Bereichsnamen keine Leerzeichen enthalten dürfen, wohl aber Unterstriche und Punkte. Im Allgemeinen sollten Namen mit einem Buchstaben beginnen und dann nur Buchstaben, Ziffern, Punkte oder Unterstriche enthalten.

Bei Namen wird nicht zwischen Groß- und Kleinschreibung unterschieden, aber die Verwendung einer Zeichenfolge aus großgeschriebenen Wörtern wie TaxRate oder December2018Sales erleichtert das Lesen und Erkennen der Namen. Sie können keinen Bereichsnamen verwenden, der einen gültigen Zellbezug imitiert, wie z. B. Dog26 .

Sie können Ihre Bereichsnamen bearbeiten oder die Bereiche ändern, auf die sie sich beziehen, indem Sie das Name Manager - Fenster verwenden.

Beachten Sie auch, dass jeder benannte Bereich einen definierten Geltungsbereich hat. Normalerweise ist der Bereich standardmäßig Workbook , was bedeutet, dass auf den Bereichsnamen von überall innerhalb der Arbeitsmappe verwiesen werden kann. Es ist jedoch auch möglich, zwei oder mehr Bereiche mit demselben Namen auf separaten Blättern, aber innerhalb derselben Arbeitsmappe zu haben.

Beispielsweise könnten Sie eine Verkaufsdatendatei mit separaten Blättern für Januar^(January) , Februar^(February) , März^(March) usw. haben. Jedes Blatt könnte eine Zelle (benannter Bereich) mit dem Namen MonthlySales haben, aber normalerweise wäre der Umfang jedes dieser Namen nur das Blatt, das enthält es.

Somit würde die Formel =ROUND(MonthlySales,0) den Februarumsatz^(February) ergeben , auf den nächsten ganzen Dollar gerundet, wenn die Formel auf dem Februarblatt^(February) steht, aber der Märzumsatz^(March) , wenn auf dem Märzblatt^(March) usw.

Um Verwirrung in Arbeitsmappen mit mehreren Bereichen auf separaten Blättern mit demselben Namen oder einfach komplizierten Arbeitsmappen mit Dutzenden oder Hunderten von benannten Bereichen zu vermeiden, kann es hilfreich sein, den Blattnamen als Teil jedes Bereichsnamens einzuschließen.

Dadurch wird auch jeder Bereichsname eindeutig, sodass alle Namen einen Arbeitsmappenbereich^(Workbook) haben können . Zum Beispiel January_MonthlySales , February_MonthlySales , Budget_Date , Order_Date usw.

Zwei Vorsichtsmaßnahmen bezüglich des Geltungsbereichs von benannten Bereichen:^{(Two cautions regarding the scope of named ranges:)} (1) Sie können den Geltungsbereich eines benannten Bereichs nicht bearbeiten, nachdem er erstellt wurde, und (2) Sie können den Geltungsbereich eines neuen benannten Bereichs nur angeben, wenn Sie ihn mit der Schaltfläche „ Neu^(New) “ in erstellen das Name Manager- Fenster.

Wenn Sie einen neuen Bereichsnamen erstellen, indem Sie ihn in das Namensfeld eingeben ,^(Box) wird der Bereich standardmäßig entweder auf Workbook (wenn kein anderer Bereich mit demselben Namen vorhanden ist) oder auf das Blatt festgelegt, in dem der Name erstellt wird. Um einen neuen benannten Bereich zu erstellen, dessen Geltungsbereich auf ein bestimmtes Blatt beschränkt ist, verwenden Sie daher die Schaltfläche „Neu“ im Name Manager.

Schließlich kann für diejenigen, die Makros schreiben, in VBA -Code einfach auf Bereichsnamen verwiesen werden, indem der Bereichsname einfach in Klammern gesetzt wird. Anstelle von ThisWorkbook.Sheets (1).Cells(2,3) können Sie beispielsweise einfach [ SalesTotal ] verwenden, wenn sich dieser Name auf diese Zelle bezieht.

Beginnen Sie^(Start) mit der Verwendung von benannten Bereichen in Ihren Excel -Arbeitsblättern und Sie werden die Vorteile schnell zu schätzen wissen! Genießen!

Why You Should Be Using Named Ranges in Excel

Named ranges are a useful, but often underutilized, featυre of Miсrosoft Excel.  Named ranges can make formulas easier to understand (and debug), simplify the creation of complicated spreadsheets, and simplify your macros.

A named range is just a range (either a single cell, or a range of cells) to which you assign a name.  You can then use that name in place of normal cell references in formulas, in macros, and for defining the source for graphs or data validation.

Using a range name, like TaxRate, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

Using Named Ranges in Excel

For example, let’s look at a simple order form.  Our file includes a fillable order form with a dropdown to select the shipping method, plus a second sheet with a table of shipping costs, and the tax rate.

Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

Version 2 uses named ranges, making its formulas much easier to understand.  Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from, whenever you start to type a name in a formula.  Double-click the name in the pick list to add it to your formula.

Opening the Name Manager window from the Formulas tab displays a list of the range names and the cell ranges they reference.

But named ranges have other benefits also.  In our example files, the shipping method is selected using a dropdown (data validation) in cell B13 on Sheet1.  The selected method is then used to lookup the shipping costs on Sheet2.

Without named ranges, the dropdown choices must be manually entered since data validation will not allow you to select a source list on a different sheet.  So all of the choices must be entered twice: once in the dropdown list, and again on Sheet2.  In addition, the two lists must match.

If an error is made in one of the entries in either list, then the shipping cost formula will generate an #N/A error when the erroneous choice is selected.  Naming the list on Sheet2 as ShippingMethods eliminates both problems.

You can reference a named range when defining the data validation for a dropdown list by simply entering =ShippingMethods in the source field, for example.  This allows you to use a list of choices that are on another sheet.

And if the dropdown is referencing the actual cells used in the lookup (for the shipping cost formula), then the dropdown choices will always match the lookup list, avoiding #N/A errors.

Create a Named Range in Excel

To create a named range, simply select the cell or range of cells you want to name, then click in the Name Box (where the selected cell address is normally displayed, just left of the Formula Bar), type the name you want to use, and press Enter.

You can also create a named range by clicking the New button in the Name Manager window.  This opens a New Name window where you can enter the new name.

By default, the range to be named is set to whatever range is selected when you click the New button, but you can edit that range before or after saving the new name.

Note that range names cannot include spaces, although they can include underscores and periods.  Generally, names should begin with a letter and then contain only letters, numbers, periods, or underscores.

Names are not case-sensitive, but using a string of capitalized words, such as TaxRate or December2018Sales, makes the names easier to read and recognize.  You cannot use a range name that mimics a valid cell reference, such as Dog26.

You can edit your range names or change the ranges they refer to using the Name Manager window.

Note also that each named range has a defined scope.  Normally, the scope will default to Workbook, which means the range name can be referenced from anywhere within the workbook.  However, it is also possible to have two or more ranges with the same name on separate sheets, but within the same workbook.

For example, you might have a sales data file with separate sheets for January, February, March, etc.  Each sheet could have a cell (named range) called MonthlySales, but normally the scope of each of those names would only be the sheet containing it.

Thus, the formula =ROUND(MonthlySales,0) would give February sales, rounded to the nearest whole dollar, if the formula is on the February sheet, but March sales if on the March sheet, etc.

To avoid confusion in workbooks having multiple ranges on separate sheets with the same name or simply complicated workbooks with dozens or hundreds of named ranges, it can be helpful to include the sheet name as part of each range name.

This also makes each range name unique, so that all the names can have a Workbook scope.  For example, January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date, etc.

Two cautions regarding the scope of named ranges: (1) You cannot edit the scope of a named range after it is created, and (2) you can only specify the scope of a new named range if you create it using the New button in the Name Manager window.

If you create a new range name by typing it in the Name Box, the scope will default to either Workbook (if no other range with the same name exists), or to the sheet where the name is being created.  Therefore, to create a new named range whose scope is limited to a particular sheet, use the Name Manager “New” button.

Finally, for those who write macros, range names can be easily referenced in VBA code by simply placing the range name within brackets.  For example, instead of ThisWorkbook.Sheets(1).Cells(2,3) you can simply use [SalesTotal] if that name refers to that cell.

Start using named ranges in your Excel worksheets and you will quickly being to appreciate the benefits! Enjoy!

Related posts

• So löschen Sie leere Zeilen in Excel

• So verwenden Sie die Speak Cells-Funktion von Excel

• So fügen Sie ein Excel-Arbeitsblatt in ein Word-Dokument ein

• So verwenden Sie die Was-wäre-wenn-Analyse von Excel

• So reparieren Sie eine Zeile in Excel

• Ein fortgeschrittener VBA-Leitfaden für MS Excel

• So schreiben Sie eine IF-Formel/Anweisung in Excel

• So fügen Sie schnell mehrere Zeilen in Excel ein

• So erstellen Sie mehrere verknüpfte Dropdown-Listen in Excel

• Verwenden Sie dynamische Bereichsnamen in Excel für flexible Dropdowns

• So entfernen Sie doppelte Zeilen in Excel

• So verwenden Sie die PMT-Funktion in Excel

• Zahlen schnell in Word und Excel buchstabieren

• So suchen Sie in Excel

• So beheben Sie #NV-Fehler in Excel-Formeln wie SVERWEIS

• So erstellen Sie ein Kreisdiagramm in Excel

• Verbinden von Excel mit MySQL

• So entfernen Sie Gitternetzlinien in Excel

• Wann sollte Index-Match anstelle von SVERWEIS in Excel verwendet werden?

• 10 Excel-Tipps und Tricks für 2019