Excel Named range – Definieren und Verwenden von Namen in Excel

Das Tutorial erklärt, was ein Excel-Name ist, und zeigt, wie ein Name für eine Zelle, einen Bereich, eine Konstante oder eine Formel definiert wird. Außerdem erfahren Sie, wie Sie definierte Namen in Excel bearbeiten, filtern und löschen.

Namen in Excel sind paradox: Als eine der nützlichsten Funktionen werden sie oft als sinnlos oder nerdig angesehen. Der Grund dafür ist, dass nur sehr wenige Benutzer die Essenz von Excel-Namen verstehen., In diesem Tutorial erfahren Sie nicht nur, wie Sie einen benannten Bereich in Excel erstellen, sondern auch, wie Sie diese Funktion nutzen können, um das Schreiben, Lesen und Wiederverwenden Ihrer Formeln zu vereinfachen.,option

Was bedeutet Name in Excel?,

Im Alltag werden Namen häufig verwendet, um sich auf Personen, Objekte und geografische Orte zu beziehen. Anstatt beispielsweise „die Stadt liegt bei 40,7128° N und 74,0059° W“ zu sagen, sagen Sie einfach „New York City“.

In ähnlicher Weise können Sie in Microsoft Excel einer einzelnen Zelle oder einem Zellbereich einen vom Menschen lesbaren Namen geben und auf diese Zellen nicht als Referenz, sondern als Name verweisen.,

Um beispielsweise die Summe der Verkäufe (B2:B10) für einen bestimmten Artikel (E1) zu ermitteln, können Sie die folgende Formel verwenden:

=SUMIF($A$2:$A$10, $E$1, $B$2:$B$10)

Oder Sie können den Bereichen und einzelnen Zellen aussagekräftige Namen geben und diese Namen der Formel zuweisen:

=SUMIF(items_list, item, sales)

Schauen Sie sich den folgenden Screenshot an, welche der beiden Formeln sind für Sie leichter zu verstehen?,

Excel – Namenstypen

In Microsoft Excel können Sie zwei Arten von Namen erstellen und verwenden:

Definierter Name-ein Name, der sich auf eine einzelne Zelle, einen Zellbereich, einen konstanten Wert oder eine Formel bezieht. Wenn Sie beispielsweise einen Namen für einen Zellbereich definieren, wird dieser als benannter Bereich oder definierter Bereich bezeichnet. Diese Namen sind Gegenstand des heutigen Tutorials.

Tabellenname-ein Name einer Excel-Tabelle, die automatisch erstellt wird, wenn Sie eine Tabelle in ein Arbeitsblatt einfügen (Strg + T). Weitere Informationen zu Excel-Tabellen finden Sie unter Erstellen und Verwenden einer Tabelle in Excel.,

So erstellen Sie einen Excel-benannten Bereich

Insgesamt gibt es drei Möglichkeiten, einen Namen in Excel zu definieren: Namensfeld, Schaltfläche Name definieren und Excel Name Manager.

Geben Sie einen Namen in das Namensfeld ein

Das Namensfeld in Excel ist der schnellste Weg, um einen benannten Bereich zu erstellen:

1. Wählen Sie eine Zelle oder einen Bereich von Zellen aus, die Sie benennen möchten.
2. Geben Sie einen Namen in das Feld Name ein.
3. Drücken Sie die Eingabetaste.

Voila, ein neues Excel namens range wird erstellt!,

Erstellen Sie einen Namen mit der Option Name definieren

Eine andere Möglichkeit, einen benannten Bereich in Excel zu erstellen, ist:

1. Wählen Sie die Zelle(n) aus.
2. Klicken Sie auf der Registerkarte Formeln in der Gruppe Namen definieren auf die Schaltfläche Name definieren.
3. Geben Sie im Dialogfeld Neuer Name drei Dinge an:
   • Geben Sie im Feld Name den Bereichsnamen ein.
   • Legen Sie im Dropdown-Menü Bereich den Namensbereich fest (Arbeitsmappe standardmäßig).
   • Überprüfen Sie im Feld Bezieht sich auf die Referenz und korrigieren Sie sie bei Bedarf.
4. Klicken Sie auf OK, um die änderungen zu speichern und das Dialogfeld zu schließen.,

Im Vergleich zur vorherigen Methode erfordert die Verwendung von Define Name in Excel einige zusätzliche Klicks, bietet aber auch einige weitere Optionen wie das Festlegen des Namensbereichs und das Hinzufügen eines Kommentars, der etwas über den Namen erklärt., Darüber hinaus können Sie mit der Funktion Name definieren von Excel einen Namen für eine Konstante oder Formel erstellen.

Erstellen Sie einen benannten Bereich mit Excel Name Manager

Normalerweise wird der Name Manager in Excel verwendet, um mit vorhandenen Namen zu arbeiten. Es kann Ihnen jedoch auch helfen, einen neuen Namen zu erstellen. So geht ‚ s:

1. Gehen Sie zur Registerkarte Formeln > Gruppe Definierte Namen und klicken Sie auf Name Manager. Oder drücken Sie einfach Strg + F3 (mein bevorzugter Weg).,
2. Klicken Sie in der oberen linken Ecke des Namensmanager-Dialogfensters auf die Schaltfläche Neu…:
   
3. Dadurch wird das Dialogfeld Neuer Name geöffnet, in dem Sie einen Namen konfigurieren, wie im vorherigen Abschnitt gezeigt.

So erstellen Sie einen Excel-Namen für eine Konstante

Zusätzlich zu benannten Bereichen können Sie mit Microsoft Excel einen Namen ohne Zellreferenz definieren, der als benannte Konstante funktioniert. Um einen solchen Namen zu erstellen, verwenden Sie entweder die Excel Define Name-Funktion oder den Namen-Manager, wie oben erläutert.

Und jetzt können Sie diesen Namen überall in Ihren Formeln verwenden, um USD in EUR umzuwandeln:

Sobald sich der Wechselkurs ändert, aktualisieren Sie den Wert nur an einem zentralen Ort, und alle Ihre Formeln werden in einem einzigen Schritt neu berechnet!,

So definieren Sie einen Namen für eine Formel

Auf ähnliche Weise können Sie einer Excel-Formel einen Namen geben, z. B. derjenigen, die die Anzahl nicht leerer Zellen in Spalte A zurückgibt, mit Ausnahme der Kopfzeile (-1):

=COUNTA(Sheet5!$A:$A)-1

Wenn Sie nun wissen möchten, wie viele Elemente sich in Spalte A in Sheet5 befinden, ohne die Spaltenüberschrift, geben Sie einfach das Gleichheitszeichen gefolgt vom Namen Ihrer Formel in eine beliebige Zelle ein: =Items_count

So benennen Sie Spalten in Excel (Namen aus Auswahl)

Wenn Ihre Daten in tabellarischer Form angeordnet sind, können Sie schnell Namen für jede Spalte und/oder Zeile basierend auf auf ihren Etiketten:

1. Wählen Sie die gesamte Tabelle einschließlich der Spalten-und Zeilenüberschriften aus.,
2. Gehen Sie zur Registerkarte Formeln > Definieren Sie die Gruppe Namen und klicken Sie auf die Schaltfläche Aus Auswahl erstellen. Oder, drücken Sie die Tastenkombination Strg + Umschalt + F3.
3. In jedem Fall wird das Dialogfeld Namen aus Auswahl erstellen geöffnet. Sie wählen die Spalte oder Zeile mit Kopfzeilen oder beides aus und klicken auf OK.,

In diesem Beispiel haben wir Kopfzeilen in der oberen Zeile und in der linken Spalte, also wählen wir diese beiden Optionen aus:

Als Ergebnis erstellt Excel 7 benannte Bereiche und nimmt automatisch Namen aus den Kopfzeilen auf:

• Äpfel, Bananen, Zitronen und Orangen für Zeilen und
• Jan, Feb und Mar für Spalten.

Excel dynamic named range

In allen vorherigen Beispielen haben wir es mit statischen benannten Bereichen zu tun, die sich immer auf dieselben Zellen beziehen, was bedeutet,dass Sie die Bereichsreferenz manuell aktualisieren müssen, wann immer Sie neue Daten zum benannten Bereich hinzufügen möchten.

Wenn Sie mit erweiterbaren Datensätzen arbeiten, liegt es nahe, einen dynamischen benannten Bereich zu erstellen, der neu hinzugefügte Daten automatisch aufnimmt.,

Die detaillierte Anleitung zum Erstellen eines dynamischen benannten Bereichs in Excel finden Sie hier:

• Excel-OFFSET-Formel zum Erstellen eines dynamischen Bereichs
• Indexformel zum Erstellen eines dynamischen Bereichs

Excel-Namensregeln

Beim Erstellen eines Namens in Excel sind einige Regeln zu beachten:

Excel-Namensbereich

In Bezug auf Excel-Namen ist der Bereich der Ort oder die Ebene innerhalb eines welche der Name erkannt wird., Es kann entweder sein:

• Spezifisches Arbeitsblatt-die lokale Arbeitsblattebene
• Arbeitsmappe-die globale Arbeitsblattebene

Arbeitsblattebenennamen

Ein Arbeitsblattebenenname wird innerhalb des Arbeitsblatts erkannt, in dem er sich befindet. Wenn Sie beispielsweise einen benannten Bereich erstellen und seinen Bereich auf Sheet1 festlegen, wird er nur in Sheet1 erkannt.

Um einen Namen auf Arbeitsblattebene in einem anderen Arbeitsblatt verwenden zu können, müssen Sie dem Namen des Arbeitsblatts gefolgt vom Ausrufezeichen (!), wie folgt:

Sheet1!,items_list

Um auf einen Namen auf Arbeitsblattebene in einer anderen Arbeitsmappe zu verweisen, sollten Sie auch den Namen der Arbeitsmappe in eckige Klammern setzen:

Sheet1!items_list

Wenn entweder der Blattname oder der Arbeitsmappenname Leerzeichen enthält, sollten diese in einfache Anführungszeichen gesetzt werden:

‚Sheet1‘!items_list

Workbook-Level-Namen

Ein Workbook-Level-Name wird innerhalb der gesamten Arbeitsmappe erkannt, und Sie können es einfach nach Namen aus jedem Blatt in der gleichen Arbeitsmappe beziehen.,

Eine Verwendung zu einer Arbeitsmappe-Level-Namen in einer anderen Arbeitsmappe, vor dem Namen mit dem Arbeitsmappennamen (einschließlich der Erweiterung), gefolgt von dem Ausrufezeichen:

Book1.xlsx!items_list

Gültigkeitsbereich Vorrang

Ein definierter Name muss in seinem Gültigkeitsbereich eindeutig sein. Sie können denselben Namen in verschiedenen Bereichen verwenden, dies kann jedoch zu einem Namenskonflikt führen. Um dies zu verhindern, hat die Arbeitsblattebene standardmäßig Vorrang vor der Arbeitsmappenebene.,

Wenn es einige identisch benannte Bereiche mit unterschiedlichen Bereichen gibt und Sie den Namen auf Arbeitsmappenebene verwenden möchten, setzen Sie dem Namen den Namen der Arbeitsmappe voran, als würden Sie auf einen Namen in einer anderen Arbeitsmappe verweisen, z. B.: Book1.xlsx!Daten. Auf diese Weise kann der Namenskonflikt für alle Arbeitsblätter außer dem ersten Blatt überschrieben werden, das immer den Namen auf lokaler Arbeitsblattebene verwendet.,

Excel Name Manager-schnelle Möglichkeit zum Bearbeiten, Löschen und Filtern von Namen

Wie der Name schon sagt, wurde der Excel Name Manager speziell zum Verwalten von Namen entwickelt: Ändern, Filtern oder Löschen vorhandener Namen sowie Erstellen neuer Namen.

Es gibt zwei Möglichkeiten, zum Namensmanager in Excel zu gelangen:

• Klicken Sie auf der Registerkarte Formeln in der Gruppe Namen definieren auf den Namensmanager
  
• Drücken Sie die Tastenkombination Strg + F3.

So oder so öffnet sich das Dialogfenster Namensmanager, in dem Sie alle Namen in der aktuellen Arbeitsmappe auf einen Blick sehen können., Jetzt können Sie den Namen auswählen, mit dem Sie arbeiten möchten, und auf eine der 3 Schaltflächen oben im Fenster klicken, um die entsprechende Aktion auszuführen: Bearbeiten, Löschen oder Filtern.

So bearbeiten Sie den benannten Bereich in Excel

Um einen vorhandenen Excel-Namen zu ändern, öffnen Sie den Namensmanager, wählen Sie den Namen aus und klicken Sie auf Bearbeiten … Dadurch wird das Dialogfeld Name bearbeiten geöffnet, in dem Sie den Namen und die Referenz ändern können. Der Umfang des Namens kann nicht geändert werden.

Tip. Um eine Namensreferenz zu bearbeiten, müssen Sie das Dialogfeld Name bearbeiten nicht öffnen., Wählen Sie einfach den gewünschten Namen im Excel Name Manager aus und geben Sie eine neue Referenz direkt in das Feld Bezieht sich auf ein, oder klicken Sie auf die Schaltfläche rechts und wählen Sie den gewünschten Bereich auf dem Blatt aus. Nachdem Sie auf die Schaltfläche Schließen geklickt haben, fragt Excel, ob Sie die Änderungen speichern möchten, und Sie klicken auf Ja.

So filtern Sie Namen in Excel

Wenn Sie viele Namen in einer bestimmten Arbeitsmappe haben, klicken Sie auf die Schaltfläche Filter oben rechts im Excel Name Manager-Fenster, um nur die zu einem bestimmten Zeitpunkt relevanten Namen anzuzeigen., Folgende Filter stehen zur Verfügung:

• Namen für Arbeitsblatt oder Arbeitsmappe
• Namen mit oder ohne Fehler
• Definierte Namen oder Tabellennamen

So löschen Sie den benannten Bereich in Excel

Um einen benannten Bereich zu löschen, wählen Sie ihn im Namensmanager aus und klicken Sie oben auf die Schaltfläche Löschen.

Um mehrere Namen zu löschen, klicken Sie auf den Vornamen, drücken Sie dann die Strg-Taste und halten Sie sie gedrückt, während Sie auf andere Namen klicken, die Sie entfernen möchten. Klicken Sie dann auf die Schaltfläche Löschen, und alle ausgewählten Namen werden auf einmal gelöscht.,

Um alle definierten Namen in einer Arbeitsmappe zu löschen, wählen Sie den Vornamen in der Liste aus, halten die Umschalttaste gedrückt und klicken dann auf den Nachnamen. Lassen Sie die Umschalttaste los und klicken Sie auf Löschen.

So löschen Sie definierte Namen mit Fehlern

Wenn Sie eine Reihe ungültiger Namen mit Referenzfehlern haben, klicken Sie auf die Filterschaltfläche > Namen mit Fehlern, um sie zu filtern:

Wählen Sie danach alle gefilterten Namen aus, wie oben erläutert (mit der Umschalttaste), und klicken Sie auf die Schaltfläche Löschen.

Top 5 Vorteile der Verwendung von Namen in Excel

Bisher haben wir uns in diesem Tutorial hauptsächlich auf How-to-Dinge konzentriert, die verschiedene Aspekte der Erstellung und Verwendung benannter Bereiche in Excel abdecken. Aber Sie können neugierig sein zu wissen, was an Excel-Namen so besonders ist, dass sie die Mühe wert sind? Die fünf wichtigsten Vorteile der Verwendung definierter Namen in Excel folgen unten.,

Excel-Namen erleichtern das Erstellen und Lesen von Formeln

Sie müssen keine komplexen Referenzen eingeben oder Bereiche auf dem Blatt hin und her auswählen. Geben Sie einfach den Namen ein, den Sie in der Formel verwenden möchten, und Excel zeigt eine Liste übereinstimmender Namen zur Auswahl an., Doppelklicken Sie auf den gewünschten Namen, und Excel fügt ihn sofort in die Formel ein:

Excel-Namen ermöglichen das Erstellen erweiterbarer Formeln

Mithilfe dynamischer benannter Bereiche können Sie eine „dynamische“ Formel erstellen, die automatisch neue Daten in Berechnungen enthält, ohne dass Sie jede Referenz manuell aktualisieren müssen.

Excel-Namen erleichtern die Wiederverwendung von Formeln

Excel-Namen erleichtern das Kopieren einer Formel in ein anderes Blatt oder das Portieren einer Formel in eine andere Arbeitsmappe erheblich., Alles, was Sie tun müssen, ist, dieselben Namen in der Zielarbeitsmappe zu erstellen, die Formel so zu kopieren/einzufügen, wie sie ist, und Sie werden sie sofort zum Laufen bringen.

Benannte Bereiche Vereinfachen Sie die Navigation

Um schnell zu einem bestimmten benannten Bereich zu gelangen, klicken Sie einfach auf dessen Namen im Feld Name. Wenn sich ein benannter Bereich auf einem anderen Blatt befindet, führt Sie Excel automatisch zu diesem Blatt.

Benannte Bereiche ermöglichen das Erstellen dynamischer Dropdown-Listen

Um eine erweiterbare und aktualisierbare Dropdown-Liste zu erstellen, erstellen Sie zuerst einen dynamischen benannten Bereich und erstellen Sie dann eine Datenvalidierungsliste basierend auf diesem Bereich. Die detaillierte Schritt-für-Schritt-Anleitung finden Sie hier: So erstellen Sie ein dynamisches Dropdown-Menü in Excel.,

Excel named range-Tipps und Tricks

Nun, da Sie die Grundlagen der Erstellung und Verwendung von Namen in Excel kennen, lassen Sie mich ein paar weitere Tipps teilen, die bei Ihrer Arbeit hilfreich sein können.

So erhalten Sie eine Liste aller Namen in der Arbeitsmappe

Um eine greifbarere Liste aller Namen in einer aktuellen Arbeitsmappe zu erhalten, gehen Sie wie folgt vor:

1. Wählen Sie die oberste Zelle des Bereichs aus, in dem die Namen angezeigt werden sollen.
2. Wechseln Sie zur Registerkarte Formeln > Gruppe Namen definieren, klicken Sie auf In Formeln verwenden und dann auf Namen einfügen… Oder drücken Sie einfach die Taste F3.,
3. Klicken Sie im Dialogfeld Namen einfügen auf Liste einfügen.

Dadurch werden alle Excel-Namen zusammen mit ihren Referenzen in das aktuelle Arbeitsblatt eingefügt, beginnend in der ausgewählten Zelle.

Absolute Excel-Namen vs. relative Excel-Namen

Standardmäßig verhalten sich Excel-Namen wie absolute Referenzen-für bestimmte Zellen gesperrt. Es ist jedoch möglich, einen benannten Bereich relativ zur Position der aktiven Zelle zum Zeitpunkt der Namensdefinition festzulegen., Relative Namen verhalten sich wie relative Referenzen-werden geändert, wenn die Formel in eine andere Zelle verschoben oder kopiert wird.

Tatsächlich kann ich mir keinen Grund vorstellen, warum man einen relativ benannten Bereich erstellen möchte, außer vielleicht, wenn ein Bereich aus einer einzelnen Zelle besteht. Als Beispiel erstellen wir einen relativen Namen, der sich auf eine Zelle bezieht eine Spalte links von der aktuellen Zelle in derselben Zeile:

1. Wählen Sie Zelle B1.
2. Drücken Sie Strg + F3, um Excel Name Manager zu öffnen, und klicken Sie auf das Neue…
3. Geben Sie im Feld Name den gewünschten Namen ein, z. B. item_left.,
4. Geben Sie im Feld Bezieht sich auf =A1ein.
5. Klicken Sie auf OK.

Nun wollen wir sehen, was passiert, wenn wir den item_left Namen in einer Formel verwenden, zum Beispiel:

Wobei items_list bezieht sich auf $A$2:$A$10 und sales bezieht sich auf $B$2:$B$10 in der Tabelle unten.,

Wenn Sie die Formel in Zelle E2 eingeben und dann in die Spalte kopieren, wird der Gesamtumsatz für jedes Produkt einzeln berechnet, da item_left ein relativer Name ist und sich seine Referenz basierend auf der relativen Position der Spalte und Zeile anpasst, in die die Formel kopiert wird:

So wenden Sie Excel-Namen auf vorhandene Formeln an

Wenn Sie die Bereiche definiert haben, die bereits in Ihren Formeln verwendet werden, ändert Excel die Verweise die entsprechenden Namen automatisch., Anstatt Referenzen von Hand durch Namen zu ersetzen, können Sie Excel die Arbeit für Sie erledigen lassen. So geht ‚ s:

1. Wählen Sie eine oder mehrere Formelzellen aus, die Sie aktualisieren möchten.
2. Gehen Sie zur Registerkarte Formeln > Gruppe Namen definieren und klicken Sie auf Name definieren > Namen anwenden…
   
3. Klicken Sie im Dialogfeld Namen anwenden auf die Namen, die Sie anwenden möchten, und dann auf OK., Wenn Excel in der Lage ist, einen der vorhandenen Namen mit den in Ihren Formeln verwendeten Referenzen abzugleichen, werden die Namen automatisch für Sie ausgewählt:
   

Zusätzlich stehen zwei weitere Optionen zur Verfügung (standardmäßig ausgewählt):

• Ignorieren Sie Relativ/Absolut – aktivieren Sie dieses Kontrollkästchen, wenn Excel nur die Namen mit demselben Referenztyp anwenden soll: Ersetzen Sie relative Referenzen durch relative Namen und absolute Referenzen durch absolute Namen.,
• Zeilen-und Spaltennamen verwenden – Wenn ausgewählt, benennt Excel alle Zellreferenzen um, die als Schnittpunkt zwischen einer benannten Zeile und einer benannten Spalte identifiziert werden können. Klicken Sie für weitere Optionen auf die Optionen

Excel-Namensverknüpfungen

Wie in Excel häufig der Fall ist, können die beliebtesten Funktionen auf verschiedene Arten zugegriffen werden: über die Multifunktionsleiste, das Rechtsklick-Menü und Tastenkombinationen. Excel benannte Bereiche sind keine Ausnahme. Hier sind drei nützliche Verknüpfungen, um mit Namen in Excel zu arbeiten:

• Strg + F3, um den Excel Name Manager zu öffnen.,
• Strg + Umschalt + F3, um benannte Bereiche aus der Auswahl zu erstellen.
• F3, um eine Liste aller Excel-Namen in einer Arbeitsmappe.

Excel-Namensfehler (#REF und #NAME)

Standardmäßig tut Microsoft Excel sein Bestes, um Ihre definierten Namen konsistent und gültig zu halten, indem Bereichsreferenzen automatisch angepasst werden, wenn Sie Zellen innerhalb eines vorhandenen benannten Bereichs einfügen oder löschen. Wenn Sie beispielsweise einen benannten Bereich für die Zellen A1:A10 erstellt und dann eine neue Zeile zwischen den Zeilen 1 und 10 eingefügt haben, ändert sich die Bereichsreferenz in A1:A11., Wenn Sie Zellen zwischen A1 und A10 löschen,schrumpft Ihr benannter Bereich entsprechend.

Wenn Sie jedoch alle Zellen löschen, aus denen ein Excel-benannter Bereich besteht, wird der Name ungültig und zeigt eine #REF an! fehler im Namensmanager. Derselbe Fehler wird in einer Formel angezeigt, die auf diesen Namen verweist:

Wenn sich eine Formel auf einen nicht vorhandenen Namen bezieht (falsch eingegeben oder gelöscht), der #NAME? fehler wird angezeigt. Öffnen Sie in beiden Fällen den Excel Name Manager und überprüfen Sie die Gültigkeit Ihrer definierten Namen (der schnellste Weg besteht darin, Namen mit Fehlern zu filtern).,

So erstellen und verwenden Sie Namen in Excel. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche auf unserem Blog zu sehen!

• So erstellen und verwenden Sie einen dynamischen benannten Bereich in Excel
• Grundlegende Excel-Formeln & Funktionen mit Beispielen
• Microsoft Excel-Formeln mit Beispielen