Was ist im Namen?
Wenn Sie mit Excel-Tabellen arbeiten, kann dies viel Zeitersparnis und Effizienz bedeuten.
In diesem Tutorial erfahren Sie, wie Sie benannte Bereiche in Excel erstellen und wie Sie damit Zeit sparen.
Dieses Tutorial behandelt:
Benannte Bereiche in Excel-Eine Einführung
Wenn jemand mich anrufen oder auf mich verweisen muss, wird er meinen Namen verwenden (anstatt zu sagen, dass ein Mann an einem Ort mit so und so Größe und Gewicht bleibt).
Richtig?,
Ebenso können Sie in Excel einer Zelle oder einem Zellbereich einen Namen geben.
Anstatt die Zellenreferenz (z. B. A1 oder A1:A10) zu verwenden, können Sie einfach den Namen verwenden, den Sie ihm zugewiesen haben.
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Wenn Sie in diesem Datensatz auf den Bereich mit dem Datum verweisen müssen, müssen Sie A2:A11 in Formeln verwenden. In ähnlicher Weise müssen Sie für Vertriebsmitarbeiter und Verkäufe B2:B11 und C2:C11 verwenden.,
Es ist zwar in Ordnung, wenn Sie nur ein paar Datenpunkte haben, aber wenn Sie komplexe Datensätze verwenden, kann die Verwendung von Zellreferenzen zum Verweisen auf Daten zeitaufwändig sein.
Excel Named Ranges macht es einfach, auf Datensätze in Excel zu verweisen.
Sie können für jede Datenkategorie einen benannten Bereich in Excel erstellen und diesen Namen dann anstelle der Zellreferenzen verwenden. Zum Beispiel können Daten „Datum“ genannt werden, Sales Rep-Daten können „SalesRep“ genannt werden und Verkaufsdaten können „Umsatz“ genannt werden.
Sie können auch einen Namen für eine Zelle., Wenn Sie beispielsweise den Prozentsatz der Verkaufsprovision in einer Zelle haben, können Sie diese Zelle als „Provision“ bezeichnen.
Vorteile der Erstellung benannter Bereiche in Excel
Hier sind die Vorteile der Verwendung benannter Bereiche in Excel.
Namen anstelle von Zellreferenzen verwenden
Wenn Sie benannte Bereiche in Excel erstellen, können Sie diese Namen anstelle der Zellreferenzen verwenden.
Sie können beispielsweise =SUM(SALES) anstelle von =SUM(C2:C11) für den obigen Datensatz verwenden.
Werfen Sie einen Blick auf ṭdie unten aufgeführten Formeln. Anstatt Zellreferenzen zu verwenden, habe ich die benannten Bereiche verwendet.,
Sie würden zustimmen, dass diese Formeln einfach zu erstellen und leicht zu verstehen sind (insbesondere wenn Sie sie mit jemand anderem teilen oder selbst erneut besuchen.
Sie müssen nicht zum Datensatz zurückkehren, um Zellen auszuwählen
Ein weiterer wesentlicher Vorteil der Verwendung benannter Bereiche in Excel besteht darin, dass Sie nicht zurückgehen und die Zellbereiche auswählen müssen.,
Sie können einfach ein paar Alphabete dieses benannten Bereichs eingeben und Excel zeigt die übereinstimmenden benannten Bereiche an (wie unten gezeigt):
Benannte Bereiche Machen Formeln dynamisch
Durch die Verwendung benannter Bereiche in Excel können Sie Excel-Formeln dynamisch machen.
Im Falle einer Verkaufsprovision können Sie beispielsweise anstelle des Wertes 2.5% den genannten Bereich verwenden.
Wenn Ihr Unternehmen später beschließt, die Provision auf 3% zu erhöhen, können Sie einfach den genannten Bereich aktualisieren, und die gesamte Berechnung wird automatisch aktualisiert, um die neue Provision widerzuspiegeln.,
So erstellen Sie benannte Bereiche in Excel
Hier sind drei Möglichkeiten, benannte Bereiche in Excel zu erstellen:
Methode #1 – Mit Define Name
Hier sind die Schritte zum Erstellen benannter Bereiche in Excel mit Define Name:
- Wählen Sie den Bereich aus, für den Sie einen benannten Bereich in Excel erstellen möchten.
- Gehe zu Formeln –> Definiere den Namen.
- Geben Sie im Dialogfeld Neuer Name den Namen ein, den Sie dem ausgewählten Datenbereich zuweisen möchten., Sie können den Bereich als die gesamte Arbeitsmappe oder ein bestimmtes Arbeitsblatt angeben, Wenn Sie ein bestimmtes Blatt auswählen, wäre der Name nicht auf anderen Blättern verfügbar.
- Klicken Sie auf OK.
Dadurch wird ein benannter Bereich SALESREP erstellt.
Methode # 2: Wählen Sie im Feld Name
- den Bereich aus, für den Sie einen Namen erstellen möchten (wählen Sie keine Header aus).
- Gehen Sie zum Feld Name links in der Leiste und geben Sie den Namen des Bereichs ein, mit dem Sie den benannten Bereich erstellen möchten.,
- Beachten Sie, dass der hier erstellte Name für die gesamte Arbeitsmappe verfügbar ist. Wenn Sie es auf ein Arbeitsblatt beschränken möchten, verwenden Sie Methode 1.
Methode 3: Verwenden der Option Aus Auswahl erstellen
Dies ist der empfohlene Weg, wenn Sie Daten in tabellarischer Form haben und einen benannten Bereich für jede Spalte / Zeile erstellen möchten.
Wenn Sie beispielsweise im folgenden Datensatz schnell drei benannte Bereiche (Date, Sales_Rep und Sales) erstellen möchten, können Sie die unten gezeigte Methode verwenden.,
Hier sind die Schritte, um schnell benannte Bereiche aus einem Datensatz zu erstellen:
- Wählen Sie den gesamten Datensatz (einschließlich der Header).
- Gehe zu Formeln – > Aus Auswahl erstellen (Tastenkombination – Strg + Umschalt + F3). Es wird das Dialogfeld „Namen aus Auswahl erstellen“ geöffnet.
- Aktivieren Sie im Dialogfeld Namen aus Auswahl erstellen die Optionen, in denen Sie die Header haben. In diesem Fall wählen wir nur die oberste Zeile aus, da sich die Kopfzeile in der obersten Zeile befindet., Wenn Sie sowohl in der oberen Zeile als auch in der linken Spalte Kopfzeilen haben, können Sie beide auswählen. Wenn Ihre Daten angeordnet sind, wenn sich die Header nur in der linken Spalte befinden, aktivieren Sie nur die Option Linke Spalte.
Dadurch werden drei benannte Bereiche erstellt – Date, Sales_Rep und Sales.
Beachten Sie, dass es automatisch Namen aus den Headern aufnimmt. Wenn zwischen Wörtern ein Leerzeichen vorhanden ist, wird ein Unterstrich eingefügt (da in benannten Bereichen keine Leerzeichen vorhanden sind).,
Namenskonvention für benannte Bereiche in Excel
Es gibt bestimmte Benennungsregeln, die Sie beim Erstellen benannter Bereiche in Excel kennen müssen:
- Das erste Zeichen eines benannten Bereichs sollte ein Buchstabe und ein Unterstrich sein Zeichen (_) oder ein Backslash(\). Wenn es etwas anderes ist, wird ein Fehler angezeigt. Die verbleibenden Zeichen können Buchstaben, Zahlen, Sonderzeichen, Punkte oder Unterstriche sein.
- Sie können keine Namen verwenden, die auch Zellreferenzen in Excel darstellen. Sie können beispielsweise AB1 nicht verwenden, da es sich auch um eine Zellenreferenz handelt.,
- Sie können beim Erstellen benannter Bereiche keine Leerzeichen verwenden. Sie können beispielsweise keinen Vertriebsmitarbeiter als benannten Bereich haben. Wenn Sie zwei Wörter kombinieren und einen benannten Bereich erstellen möchten, verwenden Sie einen Unterstrich, einen Punkt oder Großbuchstaben, um ihn zu erstellen. Sie können beispielsweise Sales_Rep, SalesRep oder SalesRep.
- Beim Erstellen benannter Bereiche behandelt Excel Groß – und Kleinbuchstaben auf die gleiche Weise. Wenn Sie beispielsweise einen benannten Bereich SALES erstellen, können Sie keinen anderen benannten Bereich wie „sales“ oder „Sales“ erstellen.
- Ein benannter Bereich kann bis zu 255 Zeichen lang sein.,
Zu Viele Benannte Bereiche in Excel? Keine Sorge
Manchmal erstellen Sie in großen Datensätzen und komplexen Modellen möglicherweise viele benannte Bereiche in Excel.
Was ist, wenn Sie sich nicht an den Namen des von Ihnen erstellten benannten Bereichs erinnern?
keine Sorge, hier sind einige nützliche Tipps.
Abrufen der Namen aller benannten Bereiche
Hier sind die Schritte, um eine Liste aller von Ihnen erstellten benannten Bereiche abzurufen:
- Wechseln Sie zur Registerkarte Formeln.
- Klicken Sie in der definierten benannten Gruppe auf In Formel verwenden.
- Klicken Sie auf “ Einfügen-Namen.,
Dadurch erhalten Sie eine Liste aller benannten Bereiche in dieser Arbeitsmappe. Um einen benannten Bereich (in Formeln oder einer Zelle) zu verwenden, doppelklicken Sie darauf.
Anzeigen der übereinstimmenden benannten Bereiche
- Wenn Sie eine Vorstellung vom Namen haben, geben Sie einige Anfangszeichen ein und Excel zeigt eine Dropdown-Liste der übereinstimmenden Namen an.,
So bearbeiten Sie benannte Bereiche in Excel
Wenn Sie bereits einen benannten Bereich erstellt haben, können Sie ihn mit den folgenden Schritten bearbeiten:
- Gehen Sie zur Registerkarte Formeln und klicken Sie auf Name Manager.
- Das Dialogfeld Name Manager listet alle benannten Bereiche in dieser Arbeitsmappe auf. Doppelklicken Sie auf den benannten Bereich, den Sie bearbeiten möchten.
- Nehmen Sie die Änderungen im Dialogfeld Name bearbeiten vor.
- Klicken Sie auf OK.
- Schließen Sie das Dialogfeld Name Manager.,
Nützliche Tastenkombinationen für benannte Bereiche (die Potenz von F3)
Hier sind einige nützliche Tastenkombinationen, die nützlich sind, wenn Sie mit benannten Bereichen in Excel arbeiten:
- Um eine Liste aller benannten Bereiche abzurufen und in die Formel einzufügen: F3
- Um einen neuen Namen mit dem Dialogfeld Name Manager zu erstellen: Control + F3
- Um benannte Bereiche aus der Auswahl zu erstellen: Control + Shift + F3
Dynamische benannte Bereiche in Excel
Bisher haben wir in diesem Tutorial statische benannte Bereiche erstellt.,
Dies bedeutet, dass sich diese benannten Bereiche immer auf denselben Datensatz beziehen.
Wenn A1:A10 beispielsweise als „Sales“ bezeichnet wurde, bezieht es sich immer auf A1:A10.
Wenn Sie weitere Verkaufsdaten hinzufügen, müssen Sie die Referenz im benannten Bereich manuell aktualisieren.
In der Welt der sich ständig erweiternden Datensätze kann dies viel Zeit in Anspruch nehmen. Jedes Mal, wenn Sie neue Daten erhalten, müssen Sie möglicherweise die benannten Bereiche in Excel aktualisieren.,
Um dieses Problem zu beheben, können wir dynamische benannte Bereiche in Excel erstellen, die automatisch zusätzliche Daten berücksichtigen und in den vorhandenen benannten Bereich aufnehmen.
Wenn ich beispielsweise zwei zusätzliche Verkaufsdatenpunkte hinzufüge, bezieht sich ein dynamischer benannter Bereich automatisch auf A1:A12.
Diese Art von dynamischem benanntem Bereich kann mithilfe der Excel-Indexfunktion erstellt werden. Anstatt die Zellreferenzen beim Erstellen des benannten Bereichs anzugeben, geben wir die Formel an. Die Formel wird automatisch aktualisiert, wenn die Daten hinzugefügt oder gelöscht werden.,
Lassen Sie uns sehen, wie dynamische benannte Bereiche in Excel erstellt werden.
Angenommen, wir haben die Verkaufsdaten in Zelle A2: A11.
Hier sind die Schritte zum erstellen Dynamische Benannte Bereiche in Excel:
Fertig!
Sie haben jetzt einen dynamischen benannten Bereich mit dem Namen „Umsatz“. Dies wird automatisch aktualisiert, wenn Sie Daten hinzufügen oder Daten daraus entfernen.
Wie funktionieren dynamische benannte Bereiche?
Um zu erklären, wie dies funktioniert, müssen Sie etwas mehr über die Excel-Indexfunktion wissen.,
Die meisten Leute verwenden INDEX, um einen Wert aus einer Liste basierend auf der Zeilen-und Spaltennummer zurückzugeben.
Aber die Indexfunktion hat auch eine andere Seite.
Es kann verwendet werden, um eine Zellenreferenz zurückzugeben, wenn sie als Teil einer Zellenreferenz verwendet wird.,
Zum Beispiel ist hier die Formel, die wir verwendet haben, um einen dynamischen benannten Bereich zu erstellen:
=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,"<>"&""))
Daher gibt es hier =$A$2:$A$11
Wenn wir der Verkaufsspalte zwei zusätzliche Werte hinzufügen, wird =$A$2:$A$13
Wenn Sie der Liste neue Daten hinzufügen, gibt die Excel COUNTIF-Funktion die Anzahl der nicht leeren Zellen in der daten. Diese Zahl wird von der Indexfunktion verwendet, um die Zellreferenz des letzten Elements in der Liste abzurufen.
Hinweis:
- Dies würde nur funktionieren, wenn die Daten keine leeren Zellen enthalten.,
- Im obigen Beispiel habe ich eine große Anzahl von Zellen (A2:A100) für die benannte Bereichsformel zugewiesen. Sie können dies basierend auf Ihrem Datensatz anpassen.
Sie können auch OFFSET-Funktion verwenden, um eine dynamische benannte Bereiche in Excel zu erstellen, aber da OFFSET-Funktion flüchtig ist, kann es eine langsame Excel-Arbeitsmappe führen. INDEX hingegen ist halbflüchtig, was es zu einer besseren Wahl macht, dynamische benannte Bereiche in Excel zu erstellen.