✅ So verwenden Sie Namen in Formeln in Excel

Inhaltsverzeichnis

In diesem Artikel erfahren Sie, wie Sie Namen in einer Formel in Excel verwenden.

Szenario :

Während Sie an Excel arbeiten, haben Sie bestimmt schon von benannten Bereichen in Excel gehört. Vielleicht von einem Freund, Kollegen oder einem Online-Tutorial. Ich habe es oft in meinen Artikeln erwähnt. In diesem Artikel lernen wir den benannten Bereich in Excel kennen und untersuchen jeden einzelnen Aspekt davon.

Benannte Bereiche in Excel

Formeln -> Namensverwaltung -> Namen definieren

Benannte Bereiche sind nichts anderes als einige Excel-Bereiche, die einen aussagekräftigen Namen erhalten. Wenn Sie beispielsweise eine Zelle haben, die beispielsweise B1 enthält, die alltägliche Ziele 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 verwenden STRG + F3. Oder Sie können diese Schritte ausführen.

Gehe zu Formel Tab
Suchen Sie den Abschnitt Definierte Namen und klicken Sie auf Namen definieren. Dies öffnet die Name Manager.
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 fertig.

Jetzt können Sie darauf verweisen, indem Sie einfach seinen Namen eingeben.

Beim Erstellen von Namen sind einige Regeln zu beachten. Hier sind einige.

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

Nun, die meiste Zeit werden Sie mit strukturierten Datentabellen arbeiten. Sie haben Spalten und Zeilen mit Spaltenüberschriften und Zeilenüberschriften. Und meistens sind diese Namen für die Daten aussagekräftig und Sie möchten Ihren Bereich als diese Spaltenüberschriften benennen. Excel bietet ein Tool zum automatischen Benennen von Bereichen mithilfe von Überschriften. 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 als Optionen aufgelistet, auf die zugegriffen werden kann.

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

Nun, es wird Zeiten geben, in denen Sie alle verfügbaren benannten Bereiche in der Arbeitsmappe anzeigen möchten. Alle Namensbereiche anzeigen Drücken Sie STRG+F3. Oder du gehst zu Registerkarte "Formel" > Namensmanager. Dadurch 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. Das ist gut. Dies 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 in ein Blatt einzufügen.

Wählen Sie eine Zelle aus, in der Sie eine Liste mit benannten Bereichen abrufen möchten.
Drücken Sie F3. Dies wird geöffnet Liste einfügen Dialog Box.
Klicken Sie auf die Paste aufführen Taste.
Die Liste wird in ausgewählte Zellen 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 den benannten Bereich aktualisieren. Gehen Sie folgendermaßen vor, um benannte Bereiche zu aktualisieren.

Drücken Sie STRG+F3 die öffnen Namensmanager.
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 den Summenteil des benannten Bereichs löschen, passt er seinen Bereich automatisch an. Beim Löschen verschwindet jedoch der gesamte Namensbereich 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 aus irgendeinem Grund einfach diese 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 Tools zum Löschen von Namen, die nur Fehler aufweisen. 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 benannter Bereiche wird mit Formeln untersucht. Die Formeln werden mit benannten Bereichen wirklich flexibel und lesbar. 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 Namen würden Sie einen Bereich für die ZÄHLENWENN-Funktion von Excel angeben, für den Sie sich möglicherweise zuerst den Bereich ansehen und dann den Bereich auswählen oder in eine Formel eingeben müssen.

Excel bedient die verfügbaren Namensbereiche.

Die benannten Bereiche werden als Vorschläge angezeigt, wenn Sie einen beliebigen Buchstaben eingeben. Als Excel zeigt die Liste der Formeln. Wenn Sie beispielsweise =u eingeben, wird jede Formel 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 Zahlen 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 zum benannten Bereich schreiben, verhält er sich wie eine relative 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 "Befor" benennen, der sich auf die Zelle links bezieht, wo immer sie geschrieben steht. 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 nun das "Befor" in die Formel schreiben, bezieht es sich auf die Zelle links davon.

Hier habe ich es zuvor in der 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 sich der Wert in der linken Zelle in der Kundenliste befindet 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 Vorkommen von 'east' in der Region namens 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 einwandfrei, bis die Zielarbeitsmappe denselben 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 Regionen benannt sind, funktioniert es perfekt.

Einfach in der Arbeitsmappe navigieren

Es wird einfacher, in einer Arbeitsmappe 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.

Wenn Sie sich beispielsweise auf Blatt 10 befinden und eine Kundenliste erhalten möchten, und Sie nicht wissen, auf welchem Blatt es sich befindet. 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 vollbracht. 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. Zum Beispiel, wenn Sie eine Dropdown-Liste mit den Kategorien Obst und Gemüse wünschen. Wenn Sie nun Obst wählen, sollte ein weiteres 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

Ich hoffe, dieser Artikel über die Verwendung von Namen in einer Formel in Excel ist erklärend. Weitere Artikel zu Benennungsbereichen und verwandten Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site.

Das Namensfeld in Excel : Excel Name Box ist nichts anderes als ein kleiner Anzeigebereich oben links im Excel-Blatt, der den Namen der aktiven Zelle oder der aktiven Bereiche in Excel anzeigt. Sie können eine Zelle oder ein Array für Referenzen umbenennen.

So erhalten Sie den Blattnamen des Arbeitsblatts in Excel : Die CELL-Funktion in Excel liefert Ihnen die Informationen zu jedem Arbeitsblatt wie Spalte, Inhalt, Dateiname usw. Erfahren Sie hier, wie Sie den Blattnamen mit der CELL-Funktion erhalten.

So erhalten Sie die fortlaufende Zeilennummer in Excel: Manchmal müssen wir eine fortlaufende Zeilennummer in einer Tabelle erhalten, es kann sich um eine Seriennummer oder etwas anderes handeln. In diesem Artikel erfahren Sie, wie Sie Zeilen in Excel vom Beginn der Daten an nummerieren.

Erhöhen Sie eine Zahl in einer Textzeichenfolge in Excel: Wenn Sie eine große Liste von Elementen haben und die letzte Zahl des Textes des alten Textes in Excel erhöhen müssen, benötigen Sie Hilfe von den beiden Funktionen TEXT und RIGHT.

Populäre Artikel :

So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.

So verwenden Sie die SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.

So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.

So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.