So erstellen Sie benannte Bereiche in Excel (eine Schritt-für-Schritt-Anleitung)

Was ist im Namen?

Wenn Sie mit Excel-Tabellen arbeiten, kann dies viel Zeit und Effizienz sparen.

In diesem Tutorial erfahren Sie, wie Sie benannte Bereiche in Excel erstellen und wie Sie damit Zeit sparen.

Benannte Bereiche in Excel - Eine Einführung

Wenn mich jemand anrufen oder mich ansprechen muss, wird er meinen Namen verwenden (anstatt zu sagen, dass ein Männchen mit so und so Größe und Gewicht an diesem und jenem Ort bleibt).

Richtig?

Ebenso können Sie in Excel einer Zelle oder einem Zellbereich einen Namen geben.

Anstatt den Zellbezug (z. B. A1 oder A1:A10) zu verwenden, können Sie jetzt 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 in Formeln A2:A11 verwenden. Ebenso müssen Sie für Vertriebsmitarbeiter und Vertrieb B2:B11 und C2:C11 verwenden.

Es ist zwar in Ordnung, wenn Sie nur ein paar Datenpunkte haben, aber bei großen komplexen Datensätzen 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 in Excel für jede Datenkategorie einen benannten Bereich erstellen und dann diesen Namen anstelle der Zellbezüge verwenden. Beispielsweise können Daten mit „Datum“, Vertriebsmitarbeiterdaten mit „Vertriebsmitarbeiter“ und Vertriebsdaten mit „Vertrieb“ benannt werden.

Sie können auch einen Namen für eine einzelne Zelle erstellen. Wenn Sie beispielsweise den Prozentsatz der Verkaufsprovision in einer Zelle haben, können Sie diese Zelle als "Provision" benennen.

Vorteile der Erstellung benannter Bereiche in Excel

Hier sind die Vorteile der Verwendung benannter Bereiche in Excel.

Verwenden Sie Namen anstelle von Zellreferenzen

Wenn Sie benannte Bereiche in Excel erstellen, können Sie diese Namen anstelle der Zellbezüge verwenden.

Sie können beispielsweise =SUM(SALES) anstelle von =SUM(C2:C11) für den obigen Datensatz verwenden.

Sehen Sie sich die unten aufgeführten Formeln an. Anstatt Zellbezüge zu verwenden, habe ich die benannten Bereiche verwendet.

• Anzahl der Verkäufe mit einem Wert über 500: =COUNTIF(Sales”,>500″)
• Summe aller von Tom getätigten Verkäufe: =SUMIF(SalesRep“,Tom“,Sales)
• Von Joe verdiente Provision (Umsatz von Joe multipliziert mit dem Provisionsprozentsatz):
  =SUMIF(SalesRep“,Joe“,Sales)*Provision

Sie stimmen zu, dass diese Formeln einfach zu erstellen und leicht zu verstehen sind (insbesondere, wenn Sie sie mit jemand anderem teilen oder selbst erneut aufrufen.

Sie müssen nicht zum Dataset zurückkehren, um Zellen auszuwählen

Ein weiterer wesentlicher Vorteil der Verwendung von benannten Bereichen 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 passenden benannten Bereiche an (wie unten gezeigt):

Benannte Bereiche machen Formeln dynamisch

Durch die Verwendung von benannten Bereichen in Excel können Sie Excel-Formeln dynamisch machen.

Im Fall einer Verkaufsprovision können Sie beispielsweise anstelle des Werts 2,5% den benannten Bereich verwenden.

Wenn Ihr Unternehmen später beschließt, die Provision auf 3% zu erhöhen, können Sie einfach den benannten Bereich aktualisieren, und die gesamte Berechnung würde automatisch aktualisiert, um die neue Provision widerzuspiegeln.

So erstellen Sie benannte Bereiche in Excel

Es gibt drei Möglichkeiten, benannte Bereiche in Excel zu erstellen:

Methode Nr. 1 - Verwenden von Namen definieren

Hier sind die Schritte zum Erstellen benannter Bereiche in Excel mithilfe von Namen definieren:

• Wählen Sie den Bereich aus, für den Sie einen benannten Bereich in Excel erstellen möchten.
• Gehen Sie zu Formeln -> Namen definieren.
• 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 Arbeitsblatt auswählen, wäre der Name auf anderen Arbeitsblättern nicht verfügbar.
• OK klicken.

Dadurch wird ein Named Range SALESREP erstellt.

Methode #2: Verwenden des Namensfelds

• Wählen Sie den Bereich aus, für den Sie einen Namen erstellen möchten (keine Überschriften auswählen).
• Gehen Sie zum Namensfeld auf der linken Seite der Bearbeitungsleiste und geben Sie den Namen des 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 die empfohlene Methode, wenn Sie Daten in Tabellenform haben und einen benannten Bereich für jede Spalte/Zeile erstellen möchten.

Wenn Sie beispielsweise im folgenden Dataset schnell drei benannte Bereiche (Datum, 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 Dataset zu erstellen:

• Wählen Sie den gesamten Datensatz (einschließlich der Überschriften) aus.
• Gehen Sie zu Formeln -> Aus Auswahl erstellen (Tastenkombination - Strg + Umschalt + F3). Es öffnet sich das Dialogfeld „Namen aus Auswahl erstellen“.
• Aktivieren Sie im Dialogfeld Namen aus Auswahl erstellen die Optionen für die Überschriften. In diesem Fall wählen wir nur die obere Reihe aus, da sich die Kopfzeile in der obersten Reihe befindet. Wenn Sie Kopfzeilen sowohl in der oberen Zeile als auch in der linken Spalte haben, können Sie beide auswählen. Wenn Ihre Daten angeordnet sind, wenn sich die Überschriften nur in der linken Spalte befinden, aktivieren Sie in ähnlicher Weise nur die Option Linke Spalte.

Dadurch werden drei benannte Bereiche erstellt - Date, Sales_Rep und Sales.

Beachten Sie, dass es automatisch Namen aus den Kopfzeilen übernimmt. Wenn zwischen Wörtern Leerzeichen vorhanden sind, wird ein Unterstrich eingefügt (da Sie keine Leerzeichen in benannten Bereichen haben können).

Namenskonvention für benannte Bereiche in Excel

Es gibt bestimmte Benennungsregeln, die Sie kennen müssen, wenn Sie benannte Bereiche in Excel erstellen:

• Das erste Zeichen eines benannten Bereichs sollte ein Buchstabe und ein Unterstrich (_) oder ein umgekehrter Schrägstrich (\) sein. Wenn es etwas anderes ist, wird ein Fehler angezeigt. Die restlichen Zeichen können Buchstaben, Zahlen, Sonderzeichen, Punkte oder Unterstriche sein.
• Sie können in Excel keine Namen verwenden, die auch Zellbezüge darstellen. Beispielsweise können Sie AB1 nicht verwenden, da es sich auch um einen Zellbezug 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, Punkt oder Großbuchstaben, um ihn zu erstellen. Sie können beispielsweise Sales_Rep, SalesRep oder SalesRep haben.
  • Beim Erstellen benannter Bereiche behandelt Excel Groß- und Kleinschreibung gleich. Wenn Sie beispielsweise einen benannten Bereich SALES erstellen, können Sie keinen weiteren benannten Bereich wie "Sales" oder "Sales" erstellen.
• Ein benannter Bereich kann bis zu 255 Zeichen lang sein.

Zu viele benannte Bereiche in Excel? Mach dir keine Sorge

In großen Datensätzen und komplexen Modellen können Sie manchmal viele benannte Bereiche in Excel erstellen.

Was ist, wenn Sie sich nicht an den Namen des von Ihnen erstellten benannten Bereichs erinnern?

Mach dir keine Sorge - Hier sind einige nützliche Tipps.

Abrufen der Namen aller benannten Bereiche

So erhalten Sie eine Liste aller von Ihnen erstellten benannten Bereiche:

• Wechseln Sie zur Registerkarte Formeln.
• Klicken Sie in der Gruppe Definiert benannt auf In Formel verwenden.
• Klicken Sie auf „Namen einfügen“.

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 von dem 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 Namensmanager.
• 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 im Dialogfeld Name bearbeiten die Änderungen vor.
• OK klicken.
• Schließen Sie das Dialogfeld Namensmanager.

Nützliche Shortcuts für benannte Bereiche (die Macht von F3)

Hier sind einige nützliche Tastenkombinationen, die beim Arbeiten mit benannten Bereichen in Excel nützlich sind:

• So rufen Sie eine Liste aller benannten Bereiche ab und fügen sie in die Formel ein: F3
• So erstellen Sie einen neuen Namen mit dem Dialogfeld „Namensmanager“: Strg + F3
• So erstellen Sie benannte Bereiche aus der Auswahl: Strg + Umschalt + F3

Dynamische benannte Bereiche in Excel erstellen

Bisher haben wir in diesem Tutorial statische benannte Bereiche erstellt.

Dies bedeutet, dass diese benannten Bereiche immer auf denselben Datensatz verweisen würden.

Wenn beispielsweise A1:A10 als "Verkauf" bezeichnet wurde, würde es sich immer auf A1:A10 beziehen.

Wenn Sie weitere Verkaufsdaten hinzufügen, müssen Sie die Referenz im benannten Bereich manuell aktualisieren.

In der Welt der ständig wachsenden 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 lösen, 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, würde sich ein dynamischer benannter Bereich automatisch auf A1:A12 beziehen.

Diese Art von Dynamic Named Range kann mithilfe der Excel-INDEX-Funktion erstellt werden. Anstatt die Zellbezüge 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.

Sehen wir uns an, wie Sie dynamische benannte Bereiche in Excel erstellen.

Angenommen, wir haben die Verkaufsdaten in Zelle A2:A11.

Hier sind die Schritte zum Erstellen dynamischer benannter Bereiche in Excel:

1. 1. Gehen Sie zur Registerkarte Formel und klicken Sie auf Namen definieren.
   2. Geben Sie im Dialogfeld Neuer Name Folgendes ein:
      • Name: Verkauf
      • Geltungsbereich: Arbeitsbuch
      • Bezieht sich auf: =$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100”,”&””))
   3. OK klicken.

Erledigt!

Sie haben jetzt einen dynamischen benannten Bereich mit dem Namen "Sales". Dies würde 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-INDEX-Funktion wissen.

Die meisten Benutzer verwenden INDEX, um einen Wert aus einer Liste basierend auf der Zeilen- und Spaltennummer zurückzugeben.

Aber die INDEX-Funktion hat noch eine andere Seite.

Es kann verwendet werden, um einen Zellbezug zurückgeben wenn es als Teil einer Zellreferenz verwendet wird.

Hier ist beispielsweise die Formel, mit der wir einen dynamischen benannten Bereich erstellt haben:

=$$2:INDEX($$2:$$100,COUNTIF($$2:$$100,""&""))

INDEX($A$2:$A$100,COUNTIF($A$2:$A$100”,”&””) -> Von diesem Teil der Formel wird erwartet, dass er einen Wert zurückgibt (der der 10. Wert aus der Liste wäre, wenn man bedenkt, dass es zehn Elemente gibt).

Bei Verwendung vor einer Referenz (=2 $:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100”,”&””))) gibt den Verweis auf die Zelle anstelle des Werts zurück.

Daher gibt es hier =$A$2:$A$11 . zurück

Wenn wir der Spalte "Sales" zwei zusätzliche Werte hinzufügen, würde dies =$A$2:$A$13 . zurückgeben

Wenn Sie der Liste neue Daten hinzufügen, gibt die Excel-Funktion ZÄHLENWENN die Anzahl der nicht leeren Zellen in den Daten zurück. Diese Nummer wird von der INDEX-Funktion verwendet, um den Zellbezug des letzten Elements in der Liste abzurufen.

Notiz:

• Dies würde nur funktionieren, wenn die Daten keine leeren Zellen enthalten.
• Im obigen Beispiel habe ich der Benannten Bereichsformel eine große Anzahl von Zellen (A2:A100) zugewiesen. Sie können dies basierend auf Ihrem Datensatz anpassen.

Sie können auch die OFFSET-Funktion verwenden, um dynamische benannte Bereiche in Excel zu erstellen. Da die OFFSET-Funktion jedoch flüchtig ist, kann dies zu einer langsamen Excel-Arbeitsmappe führen. INDEX hingegen ist halbflüchtig, was es zu einer besseren Wahl macht, dynamische benannte Bereiche in Excel zu erstellen.

Vielleicht gefallen Ihnen auch die folgenden Excel-Ressourcen:

• Kostenlose Excel-Vorlagen.
• Kostenlose Online-Excel-Schulung (7-teiliger Online-Videokurs).
• Nützliche Excel-Makrocodebeispiele.
• 10 Fortgeschrittene Excel SVERWEIS-Beispiele.
• Erstellen einer Dropdown-Liste in Excel.
• Erstellen eines benannten Bereichs in Google Tabellen.
• So verweisen Sie auf ein anderes Blatt oder eine Arbeitsmappe in Excel