Benannte Bereiche sind eine dieser verkrusteten alten Funktionen in Excel, die nur wenige Benutzer verstehen. Neue Benutzer können sie seltsam und beängstigend finden, und selbst alte Hasen können sie vermeiden, weil sie sinnlos und komplex erscheinen.
Aber benannte Bereiche sind eigentlich ein ziemlich cooles Feature. Sie können Formeln *viel* einfacher erstellen, lesen und verwalten. Und als Bonus machen sie Formeln einfacher wiederzuverwenden (mobiler).
Tatsächlich verwende ich beim Testen und Prototyping von Formeln ständig benannte Bereiche. Sie helfen mir, Formeln schneller zum Laufen zu bringen. Ich verwende auch benannte Bereiche, weil ich faul bin und nicht gerne komplexe Referenzen eintippe :)
Die Grundlagen der benannten Bereiche in Excel
Was ist ein benannter Bereich?
Ein benannter Bereich ist nur ein von Menschen lesbarer Name für einen Zellbereich in Excel. Wenn ich beispielsweise den Bereich A1:A100 'Daten' nenne, kann ich mit MAX den Maximalwert mit einer einfachen Formel ermitteln:
= MAX (data) // max value
Das Schöne an benannten Bereichen ist, dass Sie in Ihren Formeln aussagekräftige Namen verwenden können, ohne an Zellbezüge denken zu müssen. Sobald Sie einen benannten Bereich haben, verwenden Sie ihn einfach wie einen Zellbezug. Alle diese Formeln sind mit dem benannten Bereich 'data' gültig:
= MAX (data) // max value = MIN (data) // min value = COUNT (data) // total values = AVERAGE (data) // min value
Video: So erstellen Sie einen benannten Bereich
Einen benannten Bereich zu erstellen ist einfach
Das Erstellen eines benannten Bereichs ist schnell und einfach. Wählen Sie einfach einen Zellbereich aus und geben Sie einen Namen in das Namensfeld ein. Wenn Sie die Eingabetaste drücken, wird der Name erstellt:
Um den neuen Bereich schnell zu testen, wählen Sie den neuen Namen in der Dropdown-Liste neben dem Namensfeld aus. Excel wählt den Bereich auf dem Arbeitsblatt aus.
Excel kann Namen automatisch erstellen (Strg + Umschalt + F3)
Wenn Sie über gut strukturierte Daten mit Beschriftungen verfügen, können Sie von Excel benannte Bereiche erstellen lassen. Wählen Sie einfach die Daten zusammen mit den Beschriftungen aus und verwenden Sie den Befehl 'Aus Auswahl erstellen' auf der Registerkarte Formeln des Menübands:
Sie können auch die Tastenkombination Strg + Umschalt + F3 verwenden.
Mit dieser Funktion können wir in einem Schritt benannte Bereiche für die Bevölkerung von 12 Bundesstaaten erstellen:
wie man Dropdown-Liste in Excel setzt
Wenn Sie auf OK klicken, werden die Namen erstellt. Sie finden alle neu erstellten Namen im Dropdown-Menü neben dem Namensfeld:
Wenn Namen erstellt wurden, können Sie sie in Formeln wie dieser verwenden
= SUM (MN,WI,MI)
Benannte Bereiche im Namensmanager aktualisieren (Strg + F3)
Nachdem Sie einen benannten Bereich erstellt haben, verwenden Sie die Namensmanager (Strg + F3), um bei Bedarf zu aktualisieren. Wählen Sie den Namen aus, mit dem Sie arbeiten möchten, ändern Sie dann die Referenz direkt (d. h. bearbeiten Sie 'bezieht sich auf') oder klicken Sie auf die Schaltfläche rechts und wählen Sie einen neuen Bereich aus.
Sie müssen nicht auf die Schaltfläche Bearbeiten klicken, um eine Referenz zu aktualisieren. Wenn Sie auf Schließen klicken, wird der Bereichsname aktualisiert.
Hinweis: Wenn Sie einen ganzen benannten Bereich auf einem Arbeitsblatt auswählen, können Sie ihn an eine neue Position ziehen und die Referenz wird automatisch aktualisiert. Ich kenne jedoch keine Möglichkeit, Bereichsreferenzen anzupassen, indem ich direkt auf das Arbeitsblatt klicke und ziehe. Wenn Sie eine Möglichkeit kennen, dies zu tun, klingeln Sie unten!
Alle benannten Bereiche anzeigen (Strg + F3)
Um schnell alle benannten Bereiche in einer Arbeitsmappe anzuzeigen, verwenden Sie das Dropdown-Menü neben dem Namensfeld.
Wenn Sie mehr Details sehen möchten, öffnen Sie den Namensmanager (Strg + F3), der alle Namen mit Referenzen auflistet und auch einen Filter bereitstellt:
Hinweis: Auf einem Mac gibt es keinen Namensmanager, daher wird stattdessen das Dialogfeld „Namen definieren“ angezeigt.
Kopieren Sie alle benannten Bereiche und fügen Sie sie ein (F3)
Wenn Sie einen dauerhafteren Datensatz benannter Bereiche in einer Arbeitsmappe wünschen, können Sie die vollständige Liste der Namen an einer beliebigen Stelle einfügen. Gehen Sie zu Formeln > In Formel verwenden (oder verwenden Sie die Tastenkombination F3) und wählen Sie dann Namen einfügen > Liste einfügen:
Wenn Sie auf die Schaltfläche Liste einfügen klicken, werden die Namen und Referenzen in das Arbeitsblatt eingefügt:
Siehe Namen direkt auf dem Arbeitsblatt
Wenn Sie den Zoomfaktor auf weniger als 40 % einstellen, zeigt Excel Bereichsnamen direkt auf dem Arbeitsblatt an:
Danke für diesen Tipp, Felipe!
Namen haben Regeln
Befolgen Sie beim Erstellen benannter Bereiche die folgenden Regeln:
- Namen müssen mit einem Buchstaben, einem Unterstrich (_) oder einem umgekehrten Schrägstrich () beginnen.
- Namen dürfen keine Leerzeichen und die meisten Satzzeichen enthalten.
- Namen können nicht mit Zellbezügen in Konflikt geraten – Sie können einen Bereich nicht mit „A1“ oder „Z100“ benennen.
- Einzelne Buchstaben sind für Namen in Ordnung ('a', 'b', 'c' usw.), aber die Buchstaben 'r' und 'c' sind reserviert.
- Bei Namen muss die Groß-/Kleinschreibung nicht beachtet werden – 'home', 'HOME' und 'Home' sind für Excel alle gleich.
Benannte Bereiche in Formeln
Benannte Bereiche sind einfach in Formeln zu verwenden
Angenommen, Sie benennen eine Zelle in Ihrer Arbeitsmappe als 'aktualisiert'. Die Idee ist, dass Sie das aktuelle Datum in die Zelle einfügen (Strg + ) und auf das Datum an anderer Stelle in der Arbeitsmappe verweisen können.
Die Formel in B8 sieht so aus:
='Updated: '& TEXT (updated, 'ddd, mmmm d, yyyy')
Sie können diese Formel an einer beliebigen Stelle in der Arbeitsmappe einfügen und sie wird korrekt angezeigt. Immer wenn Sie das Datum in 'aktualisiert' ändern, wird die Nachricht überall dort aktualisiert, wo die Formel verwendet wird. Sehen diese Seite für weitere Beispiele.
Benannte Bereiche werden beim Eingeben einer Formel angezeigt
Nachdem Sie einen benannten Bereich erstellt haben, wird er automatisch in Formeln angezeigt, wenn Sie den ersten Buchstaben des Namens eingeben. Drücken Sie die Tabulatortaste, um den Namen einzugeben, wenn Sie eine Übereinstimmung haben und Excel den Namen eingeben soll.
Benannte Bereiche können wie Konstanten funktionieren
Da benannte Bereiche an einer zentralen Stelle erstellt werden, können Sie sie wie Konstanten ohne Zellbezug verwenden. Sie können beispielsweise Namen wie 'MPG' (Meilen pro Gallone) und 'CPG' (Kosten pro Gallone) erstellen und feste Werte zuweisen:
Dann können Sie diese Namen an beliebiger Stelle in Formeln verwenden und ihren Wert an einer zentralen Stelle aktualisieren.
Benannte Bereiche sind standardmäßig absolut
Standardmäßig verhalten sich benannte Bereiche wie absolute Referenzen. In diesem Arbeitsblatt lautet die Formel zum Berechnen des Kraftstoffs beispielsweise:
=C5/$D
Der Bezug zu D2 ist absolut (gesperrt), sodass die Formel nach unten kopiert werden kann, ohne dass D2 geändert wird.
Wenn wir D2 'MPG' nennen, lautet die Formel:
=C5/MPG
Da MPG standardmäßig absolut ist, kann die Formel unverändert in Spalte D kopiert werden.
Benannte Bereiche können auch relativ sein
Obwohl benannte Bereiche standardmäßig absolut sind, können sie auch relativ sein. Ein relativer benannter Bereich bezieht sich auf einen Bereich, der relativ zur Position der aktiven Zelle ist zum Zeitpunkt der Erstellung des Sortiments . Daher sind relative benannte Bereiche nützlich, um generische Formeln zu erstellen, die überall funktionieren, wo sie verschoben werden.
Sie können beispielsweise einen generischen 'CellAbove' benannten Bereich wie folgt erstellen:
- Zelle A2 auswählen
- Strg + F3, um den Namensmanager zu öffnen
- Tippen Sie in den Abschnitt 'Bezieht sich auf' und geben Sie dann ein: =A1
CellAbove ruft jetzt den Wert aus der obigen Zelle ab, wo immer er verwendet wird.
Wichtig: Stellen Sie sicher, dass sich die aktive Zelle an der richtigen Position befindet, bevor Sie den Namen erstellen.
Wenden Sie benannte Bereiche auf vorhandene Formeln an
Wenn Sie über vorhandene Formeln verfügen, die keine benannten Bereiche verwenden, können Sie Excel bitten, die benannten Bereiche in den Formeln für Sie anzuwenden. Wählen Sie zunächst die Zellen aus, die Formeln enthalten, die Sie aktualisieren möchten. Führen Sie dann Formeln > Namen definieren > Namen anwenden aus.
Excel ersetzt dann Verweise mit einem entsprechenden benannten Bereich durch den Namen selbst.
Sie können Namen auch mit Suchen und Ersetzen anwenden:
Hauptvorteile benannter Bereiche
Benannte Bereiche erleichtern das Lesen von Formeln
Der größte Einzelvorteil von benannten Bereichen besteht darin, dass Formeln einfacher zu lesen und zu verwalten sind. Dies liegt daran, dass sie kryptische Verweise durch aussagekräftige Namen ersetzen. Betrachten Sie zum Beispiel dieses Arbeitsblatt mit Daten zu Planeten in unserem Sonnensystem. Ohne benannte Bereiche ist eine SVERWEIS-Formel zum Abrufen von 'Position' aus der Tabelle ziemlich kryptisch:
= VLOOKUP ($H,$B:$E,2,0)
Wenn Sie herausfinden möchten, wie viele Tage zwischen zwei Daten liegen, welche Funktion würden Sie verwenden?
Mit B3:E11 namens „data“ und H4 namens „planet“ können wir jedoch Formeln wie diese schreiben:
= VLOOKUP (planet,data,2,0) // position = VLOOKUP (planet,data,3,0) // diameter = VLOOKUP (planet,data,4,0) // satellites
Auf einen Blick sehen Sie den einzigen Unterschied dieser Formeln im Spaltenindex.
Benannte Bereiche machen Formeln portabel und wiederverwendbar
Benannte Bereiche können die Wiederverwendung einer Formel in einem anderen Arbeitsblatt erheblich vereinfachen. Wenn Sie Namen im Voraus in einem Arbeitsblatt definieren, können Sie eine Formel einfügen, die diese Namen verwendet, und es funktioniert 'einfach'. Dies ist eine großartige Möglichkeit, eine Formel schnell zum Laufen zu bringen.
Diese Formel zählt beispielsweise eindeutige Werte in einem Bereich numerischer Daten:
= SUM (--( FREQUENCY (data,data)>0))
Um diese Formel schnell in Ihr eigenes Arbeitsblatt zu „portieren“, benennen Sie einen Bereich „Daten“ und fügen Sie die Formel in das Arbeitsblatt ein. Solange 'data' numerische Werte enthält, funktioniert die Formel sofort.
Tipp: Ich empfehle, dass Sie die benötigten Bereichsnamen *zuerst* in der Zielarbeitsmappe erstellen und dann die Formel nur als Text kopieren (dh die Zelle, die die Formel enthält, nicht in ein anderes Arbeitsblatt kopieren, sondern nur den Text der Formel kopieren ). Dies hindert Excel daran, Namen im laufenden Betrieb zu erstellen und l ermöglicht Ihnen die vollständige Kontrolle über den Namenserstellungsprozess. Um nur Formeltext zu kopieren, kopieren Sie Text aus der Bearbeitungsleiste oder kopieren Sie ihn über eine andere Anwendung (z. B. Browser, Texteditor usw.).
Benannte Bereiche können für die Navigation verwendet werden
Benannte Bereiche eignen sich hervorragend für eine schnelle Navigation. Wählen Sie einfach das Dropdown-Menü neben dem Namensfeld aus und wählen Sie einen Namen. Wenn Sie die Maus loslassen, wird der Bereich ausgewählt. Wenn ein benannter Bereich auf einem anderen Blatt vorhanden ist, werden Sie automatisch zu diesem Blatt weitergeleitet.
Benannte Bereiche funktionieren gut mit Hyperlinks
Benannte Bereiche erleichtern Hyperlinks. Wenn Sie beispielsweise A1 in Sheet1 'home' nennen, können Sie an anderer Stelle einen Hyperlink erstellen, der Sie dorthin zurückführt.
Um einen benannten Bereich innerhalb der HYPERLINK-Funktion zu verwenden, fügen Sie ein Pfund-Symbol vor dem benannten Bereich hinzu:
= HYPERLINK ('#home','take me home')
Hinweis: Seltsamerweise können Sie keine Hyperlinks zu einer Tabelle wie bei einem normalen Bereichsnamen erstellen. Sie können jedoch einen Namen gleich einer Tabelle (d. h. =Tabelle1) und einen Hyperlink darauf definieren. Wenn jemand eine Möglichkeit kennt, eine Tabelle direkt zu verknüpfen, melden Sie sich an!
Benannte Bereiche für die Datenvalidierung
Namensbereiche eignen sich gut für die Datenvalidierung, da Sie eine logisch benannte Referenz verwenden können, um Eingaben mit einem Dropdown-Menü zu validieren. Unten wird der Bereich G4:G8 als 'statuslist' bezeichnet, dann wenden Sie die Datenvalidierung mit einer wie folgt verknüpften Liste an:
Das Ergebnis ist ein Dropdown-Menü in Spalte E, das nur Werte im genannten Bereich zulässt:
Dynamische benannte Bereiche
Namensbereiche sind äußerst nützlich, wenn sie sich automatisch an neue Daten in einem Arbeitsblatt anpassen. Ein so eingerichteter Bereich wird als 'dynamischer benannter Bereich' bezeichnet. Es gibt zwei Möglichkeiten, einen Bereich dynamisch zu gestalten: Formeln und Tabellen.
Dynamischer benannter Bereich mit einer Tabelle
Eine Tabelle ist die einfachste Möglichkeit, einen dynamischen benannten Bereich zu erstellen. Wählen Sie eine beliebige Zelle in den Daten aus und verwenden Sie dann die Tastenkombination Strg + T:
Beim Erstellen einer Excel-Tabelle wird automatisch ein Name erstellt (z. B. Tabelle1), Sie können die Tabelle jedoch beliebig umbenennen. Nachdem Sie eine Tabelle erstellt haben, wird sie automatisch erweitert, wenn Daten hinzugefügt werden.
Dynamischer benannter Bereich mit einer Formel
Sie können auch einen dynamischen benannten Bereich mit Formeln erstellen, indem Sie Funktionen wie OFFSET und INDEX verwenden. Obwohl diese Formeln mäßig komplex sind, bieten sie eine einfache Lösung, wenn Sie keine Tabelle verwenden möchten. Die folgenden Links bieten Beispiele mit vollständigen Erklärungen:
- Beispiel einer Dynamikbereichsformel mit INDEX
- Beispiel einer Dynamikbereichsformel mit OFFSET
Tabellennamen in der Datenvalidierung
Da Excel-Tabellen einen automatischen dynamischen Bereich bieten, scheinen sie sich für Datenvalidierungsregeln zu eignen, bei denen das Ziel darin besteht, anhand einer Liste zu validieren, die sich möglicherweise ständig ändert. Ein Problem bei Tabellen besteht jedoch darin, dass Sie strukturierte Verweise nicht direkt verwenden können, um Regeln für die Datenvalidierung oder bedingte Formatierung zu erstellen. Mit anderen Worten, Sie können keinen Tabellennamen in Eingabebereichen für bedingte Formatierung oder Datenvalidierung verwenden.
Als Problemumgehung können Sie jedoch einen benannten Bereich definieren, der auf eine Tabelle verweist, und dann den benannten Bereich für die Datenvalidierung oder bedingte Formatierung verwenden. Das folgende Video führt diesen Ansatz im Detail durch.
Video: So verwenden Sie benannte Bereiche mit Tabellen
Benannte Bereiche löschen
Hinweis: Wenn Sie Formeln haben, die sich auf benannte Bereiche beziehen, möchten Sie möglicherweise zuerst die Formeln aktualisieren, bevor Sie Namen entfernen. Andernfalls wird #NAME angezeigt? Fehler in Formeln, die noch auf gelöschte Namen verweisen. Speichern Sie Ihr Arbeitsblatt immer, bevor Sie benannte Bereiche entfernen, falls Sie Probleme haben und zum Original zurückkehren müssen.
Benannte Bereiche werden beim Löschen und Einfügen von Zellen angepasst
Wenn Sie *Teil* eines benannten Bereichs löschen oder Zellen/Zeilen/Spalten innerhalb eines benannten Bereichs einfügen, wird die Bereichsreferenz entsprechend angepasst und bleibt gültig. Wenn Sie jedoch alle Zellen löschen, die einen benannten Bereich einschließen, verliert der benannte Bereich die Referenz und zeigt einen #REF-Fehler an. Wenn ich beispielsweise A1 'test' nenne und dann Spalte A lösche, zeigt der Namensmanager 'bezieht sich auf' als:
=Sheet1!#REF!
Namen mit Namensmanager löschen
Um benannte Bereiche manuell aus einer Arbeitsmappe zu entfernen, öffnen Sie den Namensmanager, wählen Sie einen Bereich aus und klicken Sie auf die Schaltfläche Löschen. Wenn Sie mehrere Namen gleichzeitig entfernen möchten, können Sie mit Umschalt + Klicken oder Strg + Klicken mehrere Namen auswählen und dann in einem Schritt löschen.
Namen mit Fehlern löschen
Wenn Sie viele Namen mit Referenzfehlern haben, können Sie die Filterschaltfläche im Namensmanager verwenden, um nach Namen mit Fehlern zu filtern:
Dann Umschalt+Klick, um alle Namen auszuwählen und zu löschen.
Benannte Bereiche und Geltungsbereich
Benannte Bereiche in Excel haben einen sogenannten 'Bereich', der bestimmt, ob ein benannter Bereich lokal für ein bestimmtes Arbeitsblatt oder global für die gesamte Arbeitsmappe ist. Globale Namen haben den Geltungsbereich 'Arbeitsmappe', und lokale Namen haben einen Geltungsbereich, der dem Blattnamen entspricht, auf dem sie existieren. Der Gültigkeitsbereich für einen lokalen Namen könnte beispielsweise 'Sheet2' lauten.
Der Zweck des Geltungsbereichs
Benannte Bereiche mit globalem Gültigkeitsbereich sind nützlich, wenn Sie möchten, dass alle Blätter in einer Arbeitsmappe auf bestimmte Daten, Variablen oder Konstanten zugreifen können. Sie können beispielsweise einen globalen benannten Bereich verwenden, eine Steuersatzannahme, die in mehreren Arbeitsblättern verwendet wird.
Lokaler Geltungsbereich
Lokaler Geltungsbereich bedeutet, dass ein Name nur auf dem Blatt funktioniert, auf dem er erstellt wurde. Dies bedeutet, dass Sie mehrere Arbeitsblätter in derselben Arbeitsmappe haben können, die alle denselben Namen verwenden. Vielleicht haben Sie beispielsweise eine Arbeitsmappe mit monatlichen Nachverfolgungsblättern (eines pro Monat), die benannte Bereiche mit demselben Namen verwenden, die alle lokal begrenzt sind. Auf diese Weise können Sie möglicherweise dieselben Formeln in verschiedenen Blättern wiederverwenden. Der lokale Gültigkeitsbereich ermöglicht, dass die Namen in jedem Blatt korrekt funktionieren, ohne mit Namen in den anderen Blättern zu kollidieren.
Um auf einen Namen mit lokalem Geltungsbereich zu verweisen, können Sie den Blattnamen dem Bereichsnamen voranstellen:
Sheet1!total_revenue Sheet2!total_revenue Sheet3!total_revenue
Bereichsnamen erstellt mit dem Namensfeld haben automatisch einen globalen Geltungsbereich. Um dieses Verhalten zu überschreiben, fügen Sie beim Definieren des Namens den Blattnamen hinzu:
Sheet3!my_new_name Globaler Geltungsbereich
Globaler Geltungsbereich bedeutet, dass ein Name überall in einer Arbeitsmappe funktioniert. Sie könnten beispielsweise eine Zelle 'letztes_update' nennen und ein Datum in die Zelle eingeben. Dann können Sie die folgende Formel verwenden, um das Datum der letzten Aktualisierung in einem beliebigen Arbeitsblatt anzuzeigen.
=last_update Globale Namen müssen innerhalb einer Arbeitsmappe eindeutig sein.
Lokaler Geltungsbereich
Benannte Bereiche mit lokalem Gültigkeitsbereich sind für Arbeitsblätter sinnvoll, die benannte Bereiche nur für lokale Annahmen verwenden. Vielleicht haben Sie beispielsweise eine Arbeitsmappe mit monatlichen Nachverfolgungsblättern (eines pro Monat), die benannte Bereiche mit demselben Namen verwenden, die alle lokal begrenzt sind. Der lokale Gültigkeitsbereich ermöglicht, dass die Namen in jedem Blatt korrekt funktionieren, ohne mit Namen in den anderen Blättern zu kollidieren.
Benannte Bereichsbereiche verwalten
Standardmäßig sind neue Namen, die mit der Namebox erstellt wurden, global, und Sie können den Geltungsbereich eines benannten Bereichs nach seiner Erstellung nicht bearbeiten. Als Problemumgehung können Sie jedoch einen Namen mit dem gewünschten Bereich löschen und neu erstellen.
Wenn Sie mehrere Namen gleichzeitig von global auf lokal ändern möchten, ist es manchmal sinnvoll, das Blatt mit den Namen zu kopieren. Wenn Sie ein Arbeitsblatt duplizieren, das benannte Bereiche enthält, kopiert Excel die benannten Bereiche in das zweite Blatt und ändert gleichzeitig den Bereich in lokal. Nachdem Sie das zweite Blatt mit Namen mit lokalem Gültigkeitsbereich haben, können Sie optional das erste Blatt löschen.
Jan Karel Pieterse und Charles Williams haben ein Dienstprogramm namens Name Manager entwickelt, das viele nützliche Operationen für benannte Bereiche bereitstellt. Du kannst Laden Sie hier das Dienstprogramm Name Manager herunter .
Autor Dave Bruns
