Benannte Bereiche sind eine nützliche, aber häufig nicht ausgelastete Funktion von Microsoft Excel. Mit benannten Bereichen lassen sich Formeln leichter verstehen (und debuggen), komplizierte Tabellen erstellen und Makros vereinfachen.
Ein benannter Bereich ist nur ein Bereich (entweder eine einzelne Zelle oder ein Zellbereich) ), dem Sie einen Namen zuweisen. Sie können diesen Namen dann anstelle von normalen Zellreferenzen in Formeln, in Makros und zum Definieren der Quelle für Diagramme oder Datenüberprüfungen verwenden.
Verwenden Sie einen Bereichsnamen wie TaxRate anstelle einer Standardzelle Eine Referenz wie Sheet2! $ C $ 11 erleichtert das Verständnis und Debuggen / Überwachen einer Tabelle.
Verwenden von benannten Bereichen in Excel
Schauen wir uns beispielsweise ein einfaches Bestellformular an . Unsere Akte enthält ein ausfüllbares Bestellformular mit einer Dropdown-Liste zur Auswahl der Versandart sowie ein zweites Blatt mit einer Tabelle mit den Versandkosten und dem Steuersatz.
Version 1 (ohne benannte Bereiche) verwendet in seinen Formeln normale A1-Zellreferenzen (siehe unten stehende Formelleiste).
Version 2 verwendet benannte Bereiche, wodurch die Formeln leichter verständlich werden. Benannte Bereiche erleichtern auch die Eingabe von Formeln, da Excel eine Liste von Namen einschließlich Funktionsnamen anzeigt, aus denen Sie auswählen können, wenn Sie beginnen, einen Namen in eine Formel einzugeben. Doppelklicken Sie auf den Namen in der Auswahlliste, um ihn Ihrer Formel hinzuzufügen.
Öffnen des Fensters Name ManagerAuf dem Tab Formelnwird 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 eine Dropdown-Liste (Datenüberprüfung) in Zelle B13 auf Blatt 1 ausgewählt. Die ausgewählte Methode wird dann zum Nachschlagen der Versandkosten auf Blatt 2 verwendet.
Ohne benannte Bereiche müssen die Dropdown-Optionen manuell eingegeben werden, da Sie bei der Datenvalidierung keine Quellenliste auf einem anderen Blatt auswählen können. Daher müssen alle Auswahlmöglichkeiten zweimal eingegeben werden: einmal in der Dropdown-Liste und erneut in Sheet2. Darüber hinaus müssen die beiden Listen übereinstimmen.
Wenn in einem der Einträge in einer der beiden Listen ein Fehler auftritt, wird bei Auswahl der falschen Auswahl durch die Versandkostenformel ein # N / A-Fehler generiert. Durch die Benennung der Liste in Sheet2 als ShippingMethodswerden beide Probleme beseitigt.
Sie können einen benannten Bereich referenzieren, wenn Sie die Datenüberprüfung für eine Dropdown-Liste definieren, indem Sie einfach = ShippingMethods. Auf diese Weise können Sie eine Liste mit Auswahlmöglichkeiten auf einem anderen Blatt verwenden.
Wenn die Dropdown-Liste auf die tatsächlich in der Suche verwendeten Zellen verweist (für die Versandkostenformel), stimmen die Dropdown-Auswahlmöglichkeiten immer überein die Lookup-Liste, um # N / A-Fehler zu vermeiden.
Erstellen eines benannten Bereichs in Excel
Um einen benannten Bereich zu erstellen, wählen Sie einfach die Zelle oder den Zellbereich aus, den Sie benennen möchten, und klicken Sie dann in das Namensfeld(wo Die ausgewählte Zellenadresse wird normalerweise links von der Formelleiste angezeigt. Geben Sie den gewünschten Namen ein und drücken Sie die Eingabetaste.
Sie können auch einen benannten Bereich erstellen, indem Sie im Fenster "Name Manager" auf die Schaltfläche Neuklicken. Daraufhin wird ein Fenster Neuer Namegeöffnet, in dem Sie den neuen Namen eingeben können.
Standardmäßig wird der zu benennende Bereich auf den Bereich festgelegt, der ausgewählt ist, wenn Sie auf die Schaltfläche Neu klicken. Sie können diesen Bereich jedoch vor oder nach dem Speichern des neuen Namens bearbeiten.
Beachten Sie, dass Bereichsnamen keine Leerzeichen enthalten können, obwohl sie Unterstriche und enthalten können Perioden. Im Allgemeinen sollten Namen mit einem Buchstaben beginnen und dann nur Buchstaben, Zahlen, Punkte oder Unterstriche enthalten.
Bei Namen wird nicht zwischen Groß- und Kleinschreibung unterschieden, sondern es wird eine Zeichenfolge mit Groß- und Kleinschreibung verwendet, z. B. TaxRate oder December2018Sales Die Namen sind leichter zu lesen und zu erkennen. Sie können keinen Bereichsnamen verwenden, der eine gültige Zellreferenz imitiert, z. B. Dog26.
Sie können Ihre Bereichsnamen bearbeiten oder die Bereiche, auf die sie verweisen, im Fenster "Name Manager" ändern.
Beachten Sie auch, dass jeder benannte Bereich einen definierten Bereich hat. Normalerweise ist der Bereich standardmäßig Arbeitsmappe. Dies bedeutet, dass der Bereichsname von einer beliebigen Stelle in der Arbeitsmappe aus referenziert werden kann. Es ist jedoch auch möglich, zwei oder mehr Bereiche mit demselben Namen auf separaten Blättern, jedoch in derselben Arbeitsmappe, zu haben.
Beispielsweise verfügen Sie möglicherweise über eine Verkaufsdatendatei mit separaten Blättern für Januar und Februar , März usw. Jedes Blatt könnte eine Zelle (benannter Bereich) namens MonthlySales haben, aber normalerweise ist der Bereich jedes dieser Namen nur das Blatt, das es enthält.
Daher lautet die Formel = ROUND (MonthlySales, 0)gibt Februar-Umsätze, auf den nächsten ganzen Dollar gerundet, an, wenn sich die Formel auf dem Februar-Blatt befindet, aber März-Umsätze, wenn sich die Formel auf dem März-Blatt usw. befindet.
To Um Verwechslungen 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 einzufügen.
Dies Außerdem wird jeder Bereichsname eindeutig, sodass alle Namen einen Arbeitsmappenbereich haben können. Beispiel: January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date usw.
Zwei Vorsichtsmaßnahmen in Bezug auf den Umfang der benannten Bereiche:(1) Sie können die nicht bearbeiten Bereich eines benannten Bereichs nach seiner Erstellung und (2) Sie können den Bereich eines neuen benannten Bereichs nur angeben, wenn Sie ihn mit der Schaltfläche Neuim Namensmanagererstellen >Fenster.
Wenn Sie einen neuen Bereichsnamen erstellen, indem Sie ihn in das Namensfeld eingeben, wird als Bereich standardmäßig entweder Arbeitsmappe (falls kein anderer Bereich mit demselben Namen vorhanden ist) oder das Blatt verwendet, in dem sich der Name befindet Name wird erstellt. Verwenden Sie zum Erstellen eines neuen benannten Bereichs, dessen Bereich auf ein bestimmtes Blatt beschränkt ist, die Schaltfläche "Neu" im Namensmanager.
Für diejenigen, die Makros schreiben, kann auf Bereichsnamen im VBA-Code einfach verwiesen werden, indem der Bereichsname in eckige Klammern gesetzt wird. Beispielsweise können Sie anstelle von ThisWorkbook.Sheets (1) .Cells (2,3) einfach [SalesTotal] verwenden, wenn sich dieser Name auf diese Zelle bezieht.
Beginnen Sie mit der Verwendung benannter Bereiche in Ihren Excel-Arbeitsblättern und Sie werde schnell die vorteile zu schätzen wissen! Viel Spaß!