Alles über benannte Excel-Bereiche - Excel-Bereichsname

Anonim

Während der Arbeit an Excel müssen Sie von benannten Bereichen in Excel gehört haben. Vielleicht von einem Freund, Kollegen oder einem Online-Tutorial. Sogar ich habe es oft in meinen Artikeln erwähnt. In diesem Artikel lernen wir die benannten Bereiche in Excel kennen und werden jeden Aspekt davon untersuchen.

Was sind benannte Bereiche in Excel?

Benannte Bereiche sind nichts anderes als einige Excel-Bereiche, die mit einem aussagekräftigen Namen versehen sind. Wenn Sie beispielsweise eine Zelle haben, die beispielsweise B1 das alltägliche Ziel enthält, können Sie diese Zelle als speziell "Ziel" benennen. Jetzt können Sie mit „Ziel“ auf A1 verweisen, anstatt B1 zu schreiben.
Kurz gesagt, Benannter Bereich ist nur die Benennung von Bereichen.

Wie benenne ich einen Bereich in Excel?

Name manuell definieren:

Um einen Namen für einen Bereich zu definieren, können Sie die Tastenkombination STRG+F3 verwenden. Oder Sie können diese Schritte ausführen.

    • Gehen Sie zum Formel-Tab
    • Suchen Sie den Abschnitt „Definierte Namen“ und klicken Sie auf „Namen definieren“. Es öffnet den Namensmanager.

    • Klicken Sie auf Neu.
    • Geben Sie den Namen ein.

  • Wählen Sie den Umfang (Arbeitsmappe oder Blatt)
  • Schreiben Sie einen Kommentar, wenn Sie möchten.
  • Schreiben Sie in das Feld Bezieht sich auf die Referenz oder wählen Sie mit der Maus einen Bereich aus.
  • Klicken Sie auf OK. Es ist vollbracht.

Jetzt können Sie darauf verweisen, indem Sie einfach seinen Namen eingeben.
Beim Erstellen von Namen sind einige Regeln zu beachten. Sie sind

  1. Namen sollten nicht mit anderen Ziffern oder Sonderzeichen als Unterstrich (_) und umgekehrtem Schrägstrich (\) beginnen.
  2. Namen dürfen keine Leerzeichen und keine Sonderzeichen außer _ und \ enthalten.
  3. Der Bereich sollte nicht als Zellbezug benannt werden. Beispielsweise sind die Namen A1, B1 oder AZ100 usw. ungültig.
  4. Sie können einen Bereich nicht als „r“ und „c“ benennen, da sie für Zeilen- und Spaltenreferenzen reserviert sind.
  5. Zwei benannte Bereiche dürfen in einer Arbeitsmappe nicht denselben Namen haben.
  6. Derselbe Bereich kann mehrere Namen haben.

Name automatisch definieren

Nun, die meiste Zeit werden Sie mit einer strukturierten Datentabelle arbeiten. Sie haben Spalten und Zeilen mit Spaltenüberschriften und Zeilenüberschriften. Und meistens sind diese Namen für die Daten von Bedeutung, und Sie möchten Ihren Bereich als diese Spaltenüberschriften benennen. Excel bietet ein Tool zum automatischen Benennen von Bereichen mithilfe von Titeln. Folge diesen Schritten.

    • Die Bereiche, die Sie als Überschriften benennen möchten
    • Drücken Sie STRG+UMSCHALT+F3, oder Suchen Sie den Abschnitt "Definierte Namen" auf der Registerkarte "Formel" und klicken Sie auf Aus Auswahl erstellen.

    • Das unten stehende Optionsfeld wird angezeigt. Ich habe nur Top Row ausgewählt, da ich diesen Bereich als Überschrift benennen möchte und keine Zeilen benennen möchte.

  • OK klicken.

Jetzt wird jede Spalte als ihre Überschrift benannt. Immer wenn Sie eine Formel eingeben, werden diese Namen in der zu verwendenden Option aufgeführt.

Benennung des Bereichs mithilfe von Excel-Tabellen

Wenn wir in Excel mit STRG + T Daten als Tabelle organisieren, werden die Spaltenüberschriften automatisch als Name der jeweiligen Spalte zugewiesen. Sie sollten Excel-Tabellen und ihre Vorteile erkunden.

Wie werden alle benannten Bereiche angezeigt?

Nun, manchmal möchten Sie alle verfügbaren benannten Bereiche in der Arbeitsmappe anzeigen. Alle Namensbereiche anzeigen Drücken Sie STRG+F3. Oder du gehst zu Registerkarte Formel > Namensmanager. Es werden alle benannten Bereiche aufgelistet, die in der Arbeitsmappe verfügbar sind. Sie können verfügbare benannte Bereiche bearbeiten, löschen und neue Namen hinzufügen.

Ein Bereich Mehrere Namen

Excel ermöglicht es Benutzern, denselben Bereich mit unterschiedlichen Namen zu benennen. Zum Beispiel kann der Bereich A2:A10 gleichzeitig als „Kunden“ und „Kunden“ bezeichnet werden. Beide Namen beziehen sich auf denselben Bereich A2:A10.
Sie können jedoch nicht dieselben Namen für zwei verschiedene Bereiche verwenden. Es eliminiert die Wahrscheinlichkeit von Mehrdeutigkeiten.

Liste der benannten Bereiche auf Blatt abrufen

Wenn Sie also eine Liste der benannten Bereiche und der Bereiche haben möchten, die sie abdecken, können Sie diese Verknüpfung verwenden, um sie an einer Stelle in einem Blatt einzufügen.

    • Wählen Sie eine Zelle aus, in der Sie die Liste der benannten Bereiche abrufen möchten.
    • Drücken Sie F3. Dies wird geöffnet PAste Name Dialog Box.
    • Klicke auf Liste einfügen Taste.

  • Die Liste wird in die ausgewählte Zelle und weiter eingefügt.


Wenn Sie auf den Namen der benannten Bereiche im Feld Namen einfügen doppelklicken, werden sie als Formeln in die Zelle geschrieben. Versuch es.

Benannte Bereiche manuell aktualisieren

Nun, wenn Sie eine Zelle in einen benannten Bereich einfügen, wird sie automatisch aktualisiert und erweitert. Wenn Sie jedoch Daten am Ende der Tabelle hinzufügen, müssen Sie das benannte Feld aktualisieren. Gehen Sie folgendermaßen vor, um benannte Bereiche zu aktualisieren.

  • Drücken Sie STRG+F3, um den Namensmanager zu öffnen.
  • Klicken Sie auf den benannten Bereich, den Sie bearbeiten möchten. Klicken Sie auf Bearbeiten.
  • Geben Sie in Bezieht sich auf Spalte den Bereich ein, auf den Sie erweitern möchten, und klicken Sie auf OK.

Und es ist vollbracht. Dies ist die manuelle Aktualisierung benannter Bereiche. Wir können es jedoch dynamisch machen, indem wir einige Formeln verwenden.

Benannte Bereiche dynamisch aktualisieren

Es ist ratsam, Ihre benannten Bereiche dynamisch zu gestalten, damit Sie sie nicht bearbeiten müssen, wenn Ihre Daten den vordefinierten Bereich überschreiten.
Ich habe es in einem separaten Artikel mit dem Titel Dynamische benannte Bereiche behandelt. Die Vorteile davon können Sie hier im Detail erfahren und verstehen.

Benannte Bereiche löschen

Wenn Sie einen Teil des benannten Bereichs löschen, wird der Bereich automatisch angepasst. Wenn Sie jedoch den gesamten Namensbereich löschen, verschwindet er aus der Namensliste. Jede Formel, die von diesen Bereichen abhängt, zeigt den Fehler #REF oder eine falsche Ausgabe (Zählfunktionen).
Wenn Sie benannte Bereiche löschen möchten, führen Sie die folgenden Schritte aus.

  • Drücken Sie STRG+F3. Der Namensmanager wird geöffnet.
  • Wählen Sie Benannte Bereiche aus, die Sie löschen möchten.
  • Klicken Sie auf die Schaltfläche Löschen oder drücken Sie die Schaltfläche Löschen auf der Tastatur.


Vorsicht: Stellen Sie vor dem Löschen der benannten Bereiche sicher, dass keine Formeln von diesen Namen abhängig sind. Falls vorhanden, konvertieren Sie sie zuerst in Bereiche. Andernfalls wird der Fehler #REF angezeigt.

Namen mit Fehlern löschen

Excel bietet ein Tool zum Entfernen von Namen, die nur Fehler enthalten. Sie müssen nicht jeden von ihnen selbst identifizieren. Gehen Sie folgendermaßen vor, um Namen mit Fehlern zu löschen:

    • Öffnen Sie den Namensmanager (STRG+F3).
    • Klicken Sie auf das Dropdown-Menü Filter in der rechten oberen Ecke.
    • Wählen Sie "Name mit Fehlern"

  • Wählen Sie Alle aus und klicken Sie auf die Schaltfläche Löschen.

Und sie sind weg. Alle Namen mit Fehlern werden sofort aus dem Datensatz gelöscht.

Benannte Bereiche mit Formeln

Die beste Verwendung von benannten Bereichen wird mit Formeln erlebt. Die Formeln werden mit benannten Bereichen flexibler und lesbarer. Mal sehen wie.

Einfach zu schreibende Formeln

Angenommen, Sie haben einen Bereich als "Elemente" bezeichnet. Jetzt die Artikelliste, die Sie zählen möchten "Bleistifte". Mit Namen ist es einfach, diese ZÄHLENWENN-Formel zu schreiben. Einfach schreiben

=ZÄHLENWENN(Artikel, "Bleistift")

Sobald Sie die öffnende Klammer der Formel schreiben, wird die Liste der verfügbaren benannten Bereiche angezeigt. Ohne einen Namen würden Sie eine gi-ZÄHLENWENN-Funktion von Excel mit Bereichen schreiben, für die Sie sich möglicherweise zuerst den Bereich ansehen und dann den Bereich auswählen oder in die Formel eingeben müssen.

Excel bedient die verfügbaren Namensbereiche.

Die Namen der Bereiche werden als Vorschläge angezeigt, wenn Sie einen Buchstaben nach dem =-Zeichen eingeben. Wie Excel zeigt die Liste der Formeln. Wenn Sie beispielsweise =u eingeben, werden jede Methode und jeder benannte Bereich mit u beginnend angezeigt, damit Sie sie leicht verwenden können.

Konstanten mit benannten Bereichen erstellen

Bisher haben wir etwas über das Benennen von Bereichen gelernt, aber Sie können auch Werte benennen. Wenn Ihr Kunde zum Beispiel Sunder Pichai ist, können Sie einen Namen "Kunde" machen und er bezieht sich auf "Sundar Pichai". Wenn Sie jetzt =Client in eine beliebige Zelle schreiben, wird Sundar Pichai angezeigt.


Nicht nur Text, sondern Sie können auch eine Zahl als Konstante zuweisen, mit der Sie arbeiten können. Sie definieren beispielsweise ein Ziel. Oder der Wert von etwas, das sich nicht ändert.
Absolute und relative Referenzierung mit benannten Bereichen

Die Referenzierung mit benannten Bereichen in ist sehr flexibel. Wenn Sie beispielsweise den Namen eines benannten Bereichs in eine relative Zelle schreiben, verhält er sich wie eine entsprechende Referenz. Siehe Bild unten.

Aber wenn Sie es mit Formeln verwenden, verhält es sich absolut. Nun, die meiste Zeit werden Sie sie mit Formeln verwenden, also Sie können sagen, dass sie standardmäßig absolut sind, aber eigentlich sind sie flexibel.

Aber wir können sie auch relativieren.

Wie erstelle ich relative benannte Bereiche in Excel?

Nehmen wir an, ich möchte einen Bereich "Before" benennen, der sich auf die linke Zelle bezieht, auf die sie geschrieben wird. Wie mache ich das? Folge diesen Schritten:

  • Drücken Sie STRG+F3
  • Klicken Sie auf Neu
  • Geben Sie „Vor“ in den Abschnitt „Name“ ein.
  • Schreiben Sie im Abschnitt "Bezieht sich auf:" die Adresse der Zelle links. Wenn Sie sich beispielsweise in Zelle B1 befinden, schreiben Sie "=A2" in den Abschnitt "Bezieht sich auf:". Stellen Sie sicher, dass es kein $-Zeichen enthält.


Wo immer Sie jetzt "Befor" in die Formel schreiben, bezieht es sich auf die Zelle links davon.

Hier habe ich zuvor die COLUMN-Funktion verwendet. Die Formel gibt die Spaltennummer der linken Zelle zurück, in die sie geschrieben wird. Zu meiner Überraschung zeigt A1 die Spaltennummer der letzten Spalte an. Das bedeutet, dass das Blatt kreisförmig ist. Ich dachte, es wird ein #REF-Fehler angezeigt.

Geben Sie häufig verwendeten Formeln einen Namen?

Jetzt ist dieser erstaunlich. Oft verwenden Sie dieselbe Formel immer wieder in einem Arbeitsblatt. Sie möchten beispielsweise prüfen, ob ein Name in Ihrer Kundenliste enthalten ist oder nicht. Und diese Notwendigkeit kann viele Male auftreten. Dafür schreiben Sie jedes Mal dieselbe komplexe Formel.

=WENN(ZÄHLENWENN(Kunde,I3),"In Liste","Nicht in Liste")

Wie wäre es, wenn Sie einfach '=IsInCustomer' in eine Zelle eingeben und es wird Ihnen angezeigt, ob der Wert in der linken Zelle in der Kundenliste ist oder nicht?

Ich habe zum Beispiel hier eine Tabelle vorbereitet. Jetzt möchte ich nur "=IsInCustomer" in J5 eingeben und sehen, ob der Wert in I5 in der Kundenliste ist oder nicht. Führen Sie dazu diese Schritte aus.

    • Drücken Sie STRG+F3
    • Klicken Sie auf Neu
    • Schreiben Sie in Name "IsInCustomer"
    • Schreiben Sie in "Bezieht sich auf" Ihre Formel. =WENN(ZÄHLENWENN(Kunde,I5),"In Liste","Nicht in Liste")

  • Klicken Sie auf die Schaltfläche OK.

Wo immer Sie jetzt "IsInCustomer" eingeben, wird der Wert in der linken Zelle in der Kundenliste überprüft.

Dies hindert Sie daran, sich immer wieder zu wiederholen.

Anwenden von benannten Bereichen auf Formeln

So oft definieren wir Namen für unsere Bereiche, nachdem wir bereits Formeln basierend auf Bereichen geschrieben haben. Zum Beispiel habe ich den Gesamtpreis als Zellen = E2 * F2. Wie können wir es ändern in Einheiten*Einheit_Kosten.

    • Wählen Sie die Formeln aus.
    • Wechseln Sie zur Registerkarte Formel. Klicken Sie auf das Dropdown-Menü Name definieren.

  • Klicken Sie auf Namen anwenden.
  • Eine Liste aller benannten Bereiche wird angezeigt. Wählen Sie die richtigen Namen und klicken Sie auf OK.


Und die Namen werden jetzt angewendet. Sie können es in der Formelleiste sehen.

Leicht lesbare Formeln mit benannten Bereichen

Wie Sie gesehen haben, erleichtern benannte Bereiche das Lesen der Formeln. Wenn ich =COUNTIF(“A2:A100”,B2) schreibe, wird niemand verstehen, was ich zu zählen versuche, bis er die Daten sieht oder jemand es ihm erklärt.

Aber wenn ich =COUNTIF(region,'east') schreibe, werden die meisten Benutzer sofort feststellen, dass wir das Auftreten von 'east' in der Region mit dem Namen range zählen.

Tragbare Formeln

Benannte Bereiche machen es sehr einfach, Formeln zu kopieren und einzufügen, ohne sich Gedanken über das Ändern von Referenzen machen zu müssen. Und Sie können eine Formel von einer Arbeitsmappe in eine andere übertragen und es funktioniert, bis die Zielarbeitsmappe dieselben Namen hat.

Zum Beispiel, wenn Sie eine Formel haben =ZÄHLENWENN(Region,Ost) in der Verteilungstabelle und Sie haben eine andere Arbeitsmappe zu sagen Kunden das hat auch einen benannten Bereich „Region“. Wenn Sie diese Formel jetzt direkt an eine beliebige Stelle in dieser Arbeitsmappe kopieren, werden Ihnen die richtigen Informationen angezeigt. Die Struktur der Daten spielt keine Rolle. Es spielt keine Rolle, wo sich diese Spalte in Ihrer Arbeitsmappe befindet. Dies wird korrekt funktionieren.

Im obigen Bild habe ich genau die gleiche Formel in zwei verschiedenen Dateien verwendet, um die Zahl oder den Osten zu zählen, die in der Regionsliste vorkommen. Jetzt befinden sie sich in verschiedenen Spalten, aber da beide als Region benannt sind, funktioniert es perfekt.

Einfach in der Arbeitsmappe navigieren

Es wird einfacher, in Arbeitsmappen mit benannten Bereichen zu navigieren. Sie müssen nur den Namen des Namens in das Namensfeld eingeben. Excel führt Sie zum Bereich, egal wo Sie sich in der Arbeitsmappe befinden. Vorausgesetzt, dass der benannte Bereich vom Geltungsbereich der Arbeitsmappe ist.
Zum Beispiel, wenn Sie sich auf Blatt 10 befinden und eine Kundenliste haben möchten und nicht wissen, auf welchem ​​Blatt es ist. Gehen Sie einfach zum Namensfeld und geben Sie "Kunde" ein. Sie werden in Sekundenbruchteilen zum benannten Bereich geleitet.


Es verringert den Aufwand, sich die Bereiche zu merken.

Navigieren mit Hyperlinks mit benanntem Bereich

Wenn Ihr Blatt groß ist und Sie häufig von einem Punkt zum anderen wechseln, verwenden Sie gerne Hyperlinks, um einfach zu navigieren. Well Named Ranges können perfekt mit Hyperlinks arbeiten. Gehen Sie wie folgt vor, um Hyperlinks mithilfe von benannten Bereichen hinzuzufügen.

    • Wählen Sie eine Zelle aus, in der Sie einen Hyperlink einfügen möchten
    • Drücken Sie STRG+K oder gehen Sie zu Registerkarte Einfügen> HyperLink, um das Dialogfeld Hyperlink einfügen zu öffnen.

    • Klicken Sie auf In diesem Dokument platzieren.
    • Scrollen Sie nach unten, um die verfügbaren benannten Bereiche unter den definierten Namen anzuzeigen

  • Wählen Sie den benannten Bereich aus, um einen Hyperlink zu diesem Bereich einzufügen.

Und es ist fertig. Sie haben Ihren Hyperlink zu Ihrem ausgewählten benannten Bereich. Auf diese Weise können Sie einen Index der benannten Bereiche erstellen, die Sie sehen und anklicken können, um direkt zu ihnen zu navigieren. Dadurch wird Ihre Arbeitsmappe wirklich benutzerfreundlich.

Benannter Bereich und Datenvalidierung

Benannte Bereiche und Datenvalidierung sind wie füreinander gemacht. Benannte Bereiche machen die Datenvalidierung hochgradig anpassbar. Es wird viel einfacher, eine Validierung aus einer Liste mit einem benannten Bereich hinzuzufügen. Lassen Sie uns sehen, wie…

    • Gehen Sie zur Registerkarte Daten
    • Klicken Sie auf Datenvalidierung
    • Wählen Sie im Abschnitt "Zulassen:" die Liste aus.
    • Geben Sie im Abschnitt "Quelle:" "=Kunde" ein (schreiben Sie den benannten Bereich, den Sie haben)

  • Klicken Sie auf OK

Jetzt enthält diese Zelle Namen von Kunden, die Teil des Bereichs "Kundenname" sind. Einfach, nicht wahr.

Abhängige oder kaskadierende Datenvalidierung mit benannten Bereichen

Was nun, wenn Sie eine kaskadierende oder abhängige Datenvalidierung wünschen. Wenn Sie beispielsweise eine Dropdown-Liste mit den Kategorien Obst und Gemüse wünschen. Wenn Sie nun Obst wählen, sollte ein anderes Dropdown-Menü nur die Option Obst anzeigen und wenn Sie Gemüse wählen, dann nur Gemüse.

Dies kann leicht erreicht werden, indem benannte Bereiche verwendet werden. Lernen wie.

  • Abhängiges Dropdown mit benanntem Bereich
  • Andere Möglichkeiten für die kaskadierende Datenvalidierung

Keine Datenvalidierung mit Namen von Tabellendaten

Excel-Tabellen bieten zwar strukturierte Namen, können jedoch nicht mit Datenvalidierung und bedingter Formatierung verwendet werden. Ich weiß nicht, warum Excel das nicht zulässt.
Aber es bedeutet nicht, dass es nicht möglich ist. Sie können Bereiche innerhalb einer Tabelle benennen und diese dann zur Validierung verwenden. Excel hat damit kein Problem.

Umfang der benannten Bereiche

Bisher haben wir über benannte Bereiche mit Arbeitsmappenbereich gesprochen. Was? Wir haben es nicht besprochen? Ok, also können wir schnell verstehen, was der Geltungsbereich von benannten Bereichen ist.

Was ist der Umfang eines Namensbereichs?

Well-Scope definiert, wo ein Namensbereich erkannt werden kann. Jeder Name kann nicht aus seinem Geltungsbereich heraus erkannt werden. Beispielsweise kann ein Name in Arbeitsmappe1 in anderen Arbeitsmappen nicht erkannt werden. Excel bietet zwei Optionen für den Umfang benannter Bereiche Arbeitsblatt und Arbeitsmappe.

Wie definiere ich einen Bereich eines benannten Bereichs?

Wenn Sie einen neuen Namensbereich erstellen, sehen Sie den Abschnitt „Scope:“. Klicken Sie auf das Dropdown-Menü und wählen Sie den Geltungsbereich für Ihren Namensbereich. Sie können den Bereich nicht ändern, nachdem Sie einen benannten Bereich erstellt haben. Also besser vorher machen. Standardmäßig ist es Arbeitsmappe.

Umfang der Arbeitsmappe

Dies ist der Standardbereich für einen benannten Bereich. Ein mit dem Umfang der Arbeitsmappe definierter Name kann in der gesamten Arbeitsmappe verwendet werden, in der er definiert ist (nicht in anderen Arbeitsmappen).
Alle obigen Beispiele hatten Arbeitsmappenumfang.

Arbeitsblattumfang

Ein Name, der mit einem Arbeitsblattbereich definiert ist, kann nur im Arbeitsblatt definieren verwendet werden. Zum Beispiel, wenn ich 'Gesamt' für eine Gesamtzelle mit dem Umfang von sheet1 definiere. Die Summe wird dann nur auf Blatt1 erkannt. Andere Blätter werden nicht erkannt.

Ich möchte einen Excel-Bereich

Excel hat weder Global noch sagt Excel Scope. Eigentlich möchte ich einige Namen definieren, die in allen Arbeitsmappen auf meinem System erkannt werden. Wenn jemand weiß, wie wir das machen können, lassen Sie es mich wissen.

Bearbeiten des Bereichs nach dem Erstellen von Namen

Sie können nicht. Excel ermöglicht es Ihnen nicht, den Bereich eines benannten Bereichs zu bearbeiten, nachdem Sie ihn erstellt haben. Da alle benannten Bereiche auf einem Blatt standardmäßig auf Arbeitsmappen beschränkt sind und Sie ihren Bereich möglicherweise in ein Blatt ändern möchten.
Erstellen Sie dazu einfach eine Kopie dieses Blatts und Excel wird jeden Namen auf diesem Blatt lokalisieren, um Mehrdeutigkeiten zu vermeiden. Sie können nun das Originalblatt löschen, wenn Sie möchten.

Ausschneiden Namensbereich einfügen

Wenn Sie einen benannten Bereich ausschneiden und von einem Ziel zu einem anderen einfügen, ändert sich die Referenz an die neue Position. Wenn Sie beispielsweise einen benannten Bereich „Kunde“ in A2:A10 haben und ihn ausschneiden und in B2:B10 einfügen, dann bezieht sich der Kundenname auf den neuen Standort B2:B10.

Dynamische benannte Bereiche in Excel

17 erstaunliche Funktionen von Excel-Tabellen

Populäre Artikel:

50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität

So verwenden Sie die SVERWEIS-Funktion in Excel

So verwenden Sie die ZÄHLENWENN-Funktion in Excel

So verwenden Sie die SUMIF-Funktion in Excel