Benannte Bereiche sind eine dieser knusprigen alten Funktionen in Excel, die nur wenige Benutzer verstehen. Neue Benutzer finden sie möglicherweise seltsam und beängstigend, und sogar alte Hände meiden sie möglicherweise, weil sie sinnlos und komplex erscheinen.
Aber benannte Bereiche sind eigentlich eine ziemlich coole Funktion. Sie können Formeln* viel * einfacher erstellen, lesen und pflegen. Und als Bonus erleichtern sie die Wiederverwendung von Formeln (portabler).
Tatsächlich verwende ich beim Testen und Prototyping von Formeln ständig benannte Bereiche. Sie helfen mir, Formeln schneller zum Laufen zu bringen., Ich benutze auch benannte Bereiche, weil ich faul bin und keine komplexen Referenzen eintippe 🙂
Die Grundlagen benannter Bereiche in Excel
Was ist ein benannter Bereich?
Ein benannter Bereich ist nur ein vom Menschen lesbarer Name für einen Zellbereich in Excel. Wenn ich zum Beispiel den Bereich A1:A100 „data“ nenne, kann ich MAX verwenden, um den Maximalwert mit einer einfachen Formel zu erhalten:
=MAX(data) // max value
Das Schöne an benannten Bereichen ist, dass Sie in Ihren Formeln aussagekräftige Namen verwenden können, ohne an Zellreferenzen zu denken., Sobald Sie einen benannten Bereich haben, verwenden Sie ihn einfach wie eine Zellenreferenz. 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 valueVideo: So erstellen Sie einen benannten Bereich
Das Erstellen eines benannten Bereichs 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 Feld Name 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 Feld Name. Excel wählt den Bereich auf dem Arbeitsblatt aus.,
Excel kann Namen automatisch erstellen (Strg + Umschalt + F3)
Wenn Sie gut strukturierte Daten mit Beschriftungen haben, können Sie Excel benannte Bereiche für Sie 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 der Multifunktionsleiste:
Sie können auch die Tastenkombination strg + Umschalt + F3 verwenden.
Mit dieser Funktion können wir benannte Bereiche für die Population von 12 Zuständen in einem Schritt erstellen:
Wenn Sie auf OK klicken, werden die Namen erstellt., Sie finden alle neu erstellten Namen im Dropdown-Menü neben dem Namensfeld:
Mit Namen können Sie sie in Formeln wie diesen verwenden
=SUM(MN,WI,MI)Aktualisieren Sie benannte Bereiche im Namensmanager (Control + F3)
Sobald Sie einen benannten Bereich erstellt haben, verwenden Sie den Namensmanager (Control + F3) Control + F3) nach Bedarf zu aktualisieren. Wählen Sie den Namen aus, mit dem Sie arbeiten möchten, ändern Sie die Referenz direkt (dh 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 gesamten benannten Bereich in einem Arbeitsblatt auswählen, können Sie ihn an einen neuen Speicherort 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 einen Weg kennen, dies zu tun, Glockenspiel unten!
Alle benannten Bereiche anzeigen (Strg + F3)
Um schnell alle benannten Bereiche in einer Arbeitsmappe anzuzeigen, verwenden Sie das Dropdown-Menü neben dem Feld Name.,
Wenn Sie mehr Details sehen möchten, öffnen Sie den Namensmanager (Control + F3), der alle Namen mit Referenzen auflistet und auch einen Filter bereitstellt:
Hinweis: Auf einem Mac gibt es keinen Namensmanager, daher wird stattdessen der Dialog Name definieren angezeigt.
Kopieren und Einfügen aller benannten Bereiche (F3)
Wenn Sie einen dauerhafteren Datensatz benannter Bereiche in einer Arbeitsmappe wünschen, können Sie die vollständige Liste der Namen beliebig einfügen., Gehen Sie zu Formeln > In Formel verwenden (oder verwenden Sie die Verknüpfung F3) und wählen Sie 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 die Zoomstufe 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:
Benannte Bereiche in Formeln
Benannte Bereiche sind in Formeln einfach zu verwenden
Angenommen, Sie benennen eine Zelle in Ihrer Arbeitsmappe „aktualisiert“. Die Idee ist, dass Sie das aktuelle Datum in die Zelle einfügen können (Strg + 😉 und auf das Datum an anderer Stelle in der Arbeitsmappe verweisen können.
Die Formel in B8 sieht folgendermaßen aus:
="Updated: "& TEXT(updated, "ddd, mmmm d, yyyy")Sie können diese Formel an einer beliebigen Stelle in die Arbeitsmappe einfügen und korrekt anzeigen., Wenn Sie das Datum in „aktualisiert“ ändern, wird die Nachricht überall dort aktualisiert, wo die Formel verwendet wird. Weitere Beispiele finden Sie auf dieser Seite.
Benannte Bereiche werden angezeigt, wenn Sie eine Formel eingeben
Sobald 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.,
Benannten Bereichen arbeiten können, wie Konstanten
Da die benannten Bereiche erstellt werden, in einer zentralen Lage, können Sie verwenden Sie wie Konstanten, die ohne eine Zelle Referenz. 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 überall in Formeln verwenden und ihren Wert an einem zentralen Ort aktualisieren.,
Benannte Bereiche sind standardmäßig absolut
Benannte Bereiche verhalten sich standardmäßig wie absolute Referenzen. In diesem Arbeitsblatt lautet die Formel zur Berechnung des Kraftstoffs beispielsweise:
=C5/$D$2
Der Verweis auf D2 ist absolut (gesperrt), sodass die Formel kopiert werden kann, ohne dass sich D2 ändert.,
Wenn wir D2 „MPG“ nennen, wird die Formel:
=C5/MPG
Da MPG standardmäßig absolut ist, kann die Formel 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 relativ benannter Bereich bezieht sich auf einen Bereich, der relativ zur Position der aktiven Zelle zum Zeitpunkt der Erstellung des Bereichs ist. Infolgedessen sind relative benannte Bereiche nützlich, um generische Formeln zu erstellen, die überall dort funktionieren, wo sie verschoben werden.,
Sie können beispielsweise einen generischen „CellAbove“ – benannten Bereich wie folgt erstellen:
- Wählen Sie Zelle A2
- Control + F3 aus, um den Name Manager
- Tab in den Abschnitt „Bezieht sich auf“ zu öffnen, und geben Sie dann ein: = A1
CellAbove ruft nun den Wert aus der obigen Zelle ab, wo immer er verwendet wird.
Wichtig: Stellen Sie sicher, dass sich die aktive Zelle an der richtigen Stelle befindet, bevor Sie den Namen erstellen.
Benannte Bereiche auf vorhandene Formeln anwenden
Wenn Sie über Formeln verfügen, die keine benannten Bereiche verwenden, können Sie Excel auffordern, 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 aus > Namen definieren > Namen anwenden.
Excel ersetzt dann Referenzen mit einem entsprechenden benannten Bereich durch den Namen selbst.
Sie können auch Namen mit suchen und Ersetzen anwenden:
Wichtig: Speichern Sie ein Backup Ihres Arbeitsblatts und wählen Sie nur die Zellen aus, die Sie ändern möchten, bevor Sie suchen und Ersetzen in Formeln verwenden.,Hauptvorteile benannter Bereiche
Benannte Bereiche erleichtern das Lesen von Formeln
Der größte einzelne Vorteil benannter Bereiche besteht darin, dass sie das Lesen und Warten von Formeln erleichtern. Dies liegt daran, dass sie kryptische Referenzen durch aussagekräftige Namen ersetzen. Betrachten Sie beispielsweise dieses Arbeitsblatt mit Daten zu Planeten in unserem Sonnensystem., Ohne benannte Bereiche ist eine VLOOKUP-Formel zum Abrufen von „Position“ aus der Tabelle ziemlich kryptisch:
=VLOOKUP($H$4,$B$3:$E$11,2,0)
Mit B3:E11 mit dem Namen „data“ und H4 mit dem Namen „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 in diesen Formeln im Spaltenindex.
Benannte Bereiche machen Formeln portabel und wiederverwendbar
Benannte Bereiche können die Wiederverwendung einer Formel in einem anderen Arbeitsblatt erheblich erleichtern., Wenn Sie Namen im Voraus in einem Arbeitsblatt definieren, können Sie eine Formel einfügen, die diese Namen verwendet, und sie 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“ Daten “ numerische Werte enthält, funktioniert die Formel sofort.,
Tipp: Ich empfehle, dass Sie die erforderlichen Bereichsnamen *zuerst* in der Zielarbeitsmappe erstellen und dann die Formel nur als Text kopieren (dh nicht die Zelle kopieren, die die Formel in einem anderen Arbeitsblatt enthält, sondern nur den Text der Formel). Dadurch wird verhindert, dass Excel im laufenden Betrieb Namen erstellt, und Sie können den Namenerstellungsprozess vollständig steuern. Um nur Formeltext zu kopieren, kopieren Sie Text aus der Formelleiste 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 sind ideal für die 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 einer anderen Stelle einen Hyperlink erstellen, der Sie dorthin zurückbringt.,
Um einen benannten Bereich innerhalb der HYPERLINK-Funktion zu verwenden, fügen Sie ein Pfundsymbol vor dem benannten Bereich hinzu:
=HYPERLINK("#home","take me home")Hinweis: Seltsamerweise können Sie keinen Hyperlink zu einer Tabelle wie einem normalen bereichsname. Sie können jedoch einen Namen definieren, der einer Tabelle entspricht (dh =Table1) und einen Hyperlink dazu. Wenn jemand eine Möglichkeit kennt, einen Tisch direkt zu verknüpfen, läuten Sie ein!,
Named ranges for data validation
Names ranges funktionieren gut für die Datenvalidierung, da Sie eine logisch benannte Referenz verwenden können, um Eingaben mit einem Dropdown-Menü zu validieren. Unten heißt der Bereich G4:G8 „statuslist“ und wendet dann die Datenvalidierung mit einer Liste an, die wie folgt verknüpft ist:
Das Ergebnis ist ein Dropdown-Menü in Spalte E, das nur Werte im benannten Bereich zulässt:
Dynamische benannte Bereiche
Namen Bereiche sind äußerst nützlich, wenn sie automatisch anpassung an neue Daten in einem Arbeitsblatt., Ein auf diese Weise 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 der einfachste Weg, einen dynamischen benannten Bereich zu erstellen. Wählen Sie eine beliebige Zelle in den Daten aus und verwenden Sie dann die Tastenkombination Strg + T:
Wenn Sie eine Excel-Tabelle erstellen, wird automatisch ein Name erstellt (z. B. Tabelle1), aber Sie können die Tabelle nach Belieben umbenennen. Sobald 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 Funktionen wie OFFSET und INDEX. Obwohl diese Formeln mäßig komplex sind, bieten sie eine leichte Lösung, wenn Sie keine Tabelle verwenden möchten., Die folgenden Links bieten Beispiele mit vollständigen Erklärungen:
- Beispiel für Dynamikbereichsformel mit INDEX
- Beispiel für Dynamikbereichsformel mit OFFSET
Tabellennamen in der Datenvalidierung
Da Excel-Tabellen einen automatischen Dynamikbereich bieten, scheinen sie eine natürliche Anpassung für Datenvalidierungsregeln zu sein, 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 zum Erstellen von Datenvalidierungs-oder bedingten Formatierungsregeln verwenden können., Mit anderen Worten, Sie können keinen Tabellennamen in Eingabebereichen mit bedingter 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 Video unten durchläuft diesen Ansatz im Detail.
Video: Verwendung benannter Bereiche mit Tabellen
Löschen benannter Bereiche
Hinweis: Wenn Sie Formeln haben, die sich auf benannte Bereiche beziehen, sollten Sie die Formeln zuerst aktualisieren, bevor Sie Namen entfernen. Andernfalls sehen Sie #NAME?, fehler in Formeln, die sich immer noch auf gelöschte Namen beziehen. Speichern Sie Ihr Arbeitsblatt immer, bevor Sie benannte Bereiche entfernen, falls Sie Probleme haben und zum Original zurückkehren müssen.
Benannte Bereiche anpassen beim Löschen und Einfügen von Zellen
Wenn Sie *Teil* eines benannten Bereichs löschen oder Zellen/Zeilen/Spalten in einen benannten Bereich 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 Name Manager „bezieht sich auf“ als:
=Sheet1!#REF!Löschen Sie Namen mit Name Manager
Um benannte Bereiche manuell aus einer Arbeitsmappe zu entfernen, öffnen Sie den Namen Manager, wählen Sie einen Bereich aus und klicken Sie auf die Schaltfläche Löschen. Wenn Sie mehr als einen Namen gleichzeitig entfernen möchten, können Sie mit Umschalt + Klick oder Strg + Klick 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 mit der Filterschaltfläche im Namensmanager nach Namen mit Fehlern filtern:
Dann Umschalt+Klick, um alle Namen auszuwählen und zu löschen.
Benannte Bereiche und Bereich
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 einen Bereich von „Arbeitsmappe“, und lokale Namen haben einen Bereich, der dem Blattnamen entspricht, auf dem sie existieren., Der Bereich für einen lokalen Namen könnte beispielsweise „Sheet2″sein.
Der Zweck des Bereichs
Benannte Bereiche mit einem globalen Bereich sind nützlich, wenn alle Blätter in einer Arbeitsmappe Zugriff auf bestimmte Daten, Variablen oder Konstanten haben sollen. Sie können beispielsweise einen globalen Bereich mit dem Namen a tax rate assumption verwenden, der in mehreren Arbeitsblättern verwendet wird.
Lokaler Bereich
Lokaler Bereich 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., Zum Beispiel haben Sie vielleicht eine Arbeitsmappe mit monatlichen Verfolgungsblättern (eine pro Monat), die benannte Bereiche mit demselben Namen verwenden, die alle lokal festgelegt sind. Auf diese Weise können Sie möglicherweise dieselben Formeln in verschiedenen Blättern wiederverwenden. Der lokale Bereich 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 einem lokalen Bereich zu verweisen, können Sie dem Bereichsnamen den Blattnamen voranstellen:
Sheet1!total_revenueSheet2!total_revenueSheet3!total_revenueBereichsnamen, die mit dem Feld Name erstellt wurden, haben automatisch einen globalen Bereich., Um dieses Verhalten zu überschreiben, fügen Sie den Blattnamen bei der Definition des Namens hinzu:
Globaler Bereich
Globaler Bereich bedeutet, dass ein Name an einer beliebigen Stelle in einer Arbeitsmappe funktioniert. Sie können beispielsweise eine Zelle „last_update“ nennen und ein Datum in die Zelle eingeben. Anschließend können Sie die folgende Formel verwenden, um das Datum der letzten Aktualisierung in einem Arbeitsblatt anzuzeigen.
=last_updateGlobale Namen müssen innerhalb einer Arbeitsmappe eindeutig sein.
Lokaler Bereich
Lokal begrenzte benannte Bereiche sind für Arbeitsblätter sinnvoll, die benannte Bereiche nur für lokale Annahmen verwenden., Zum Beispiel haben Sie vielleicht eine Arbeitsmappe mit monatlichen Verfolgungsblättern (eine pro Monat), die benannte Bereiche mit demselben Namen verwenden, die alle lokal festgelegt sind. Der lokale Bereich ermöglicht, dass die Namen in jedem Blatt korrekt funktionieren, ohne mit Namen in den anderen Blättern zu kollidieren.
Benannter Bereichsbereich verwalten
Standardmäßig sind neue Namen, die mit der Namebox erstellt wurden, global, und Sie können den Bereich eines benannten Bereichs nach der 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 in local ä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 lokal definierten Namen 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. Sie können das Dienstprogramm Name Manager hier herunterladen.,
