Excel: Bereichsnamen vergeben und nutzen

Mal angenommen, du möchtest ein umfangreiches Excel-Modell nach einer gewissen Nutzungszeit überarbeiten oder aktualisieren.

Normalerweise wirst du mit unzähligen Formeln konfrontiert, die auf die unterschiedlichsten Zellbereiche zugreifen.

Du weißt, das Verständnis der Formeln ist mitunter extrem schwer, denn du musst dich erst wieder in dein damaliges Modelldenken hineinfinden.

Viel einfacher zu verstehen sind Formeln wie

=Menge * Einzelpreis

Zu diesen Formeln kommst du, indem du die bestimmten Zellbereiche namentlich definierst.

Wie das geht, zeigt dieser Blogbeitrag.

1. Namen definieren

In einem Bereich des Arbeitsblattes sind ein paar Modelle aus dem Essplatzgruppenprogramm eines Möbelherstellers aufgeführt. Rechts davon sind die Lagerbestände am Ende der jeweiligen Monate ersichtlich.

Du willst den Bereichen B4:B11, C3:D3 und C4:D11 jeweils einen Namen geben.

Markiere zuerst den Bereich B4:B11 und rufe im Menü Formeln / Definierte Namen den Menüpunkt „Namen definieren“ auf. Es erscheint diese Eingabemaske:

Gib bei „Name“ den gewählten Namen „Modelle“ ein.

Entscheide bei „Bereich“, ob der Name in der gesamten Arbeitsmappe oder nur in einem bestimmten Tabellenblatt gültig sein soll.

Unter „Bezieht sich auf“ ist der markierte Bereich bereits eingetragen.

Bei „Kommentar“ kannst du z.B. eintragen, was du dir bei dem Namen gedacht hast.

Markiere als nächstes den Bereich C3:D3 und vergib den Namen „Monate“.

Markiere nun noch den Bereich C4:D11 und nenne ihn „Werte“.

2. Namen verwenden

2.1 Namen in Formeln verwenden

Erweitere die Bestandsübersicht, indem du rechts neben der eigentlichen Tabelle zwei Auswahlfelder positionierst.

Willst du nun z.B. mit der INDEX-Funktion den Bestand des in G3 gezeigten Modells für den in G4 gezeigten Monat abrufen, kannst du dies unter Verwendung der vergebenen Namen mit dieser Formel tun.

Dabei verwendest du zusätzlich die Funktion VERGLEICH.

=INDEX(Werte;VERGLEICH(G3;Modelle;0);VERGLEICH(G4;Monate;0))

Das erste Argument in INDEX, Matrix, bildest du mit dem Namen „Werte“ ab.

Das zweite Argument, Zeile, erhältst du unter Verwendung der VERGLEICH-Funktion, indem du die Position des Modells in G3 in der Liste „Modelle“ feststellst. LERHAMN nimmt den vierten Platz in „Modelle“ ein, du erhältst also Zeile 4.

Das dritte Argument, Spalte, erhältst du wiederum unter Verwendung der VERGLEICH-Funktion, indem du die Position des Monats in der Liste „Monate“ feststellst. Es ist hier der erste Platz in „Monate“, du erhältst also Spalte 1.

In Zelle G6 schließlich wird dir das Ergebnis der Berechnung gezeigt.

Nun könntest du behaupten, dass die Formel

=INDEX(Tabelle1!C4:D11;4;1)

doch viel kürzer ist. Das ist richtig, aber sie ist nicht dynamisch. Das heißt, wenn du das Modell GAMLEBY im Monat Jan abfragen willst, musst du die Formel ändern auf:

=INDEX(Tabelle1!C4:D11;7;1)

Verwendest du dagegen die einmal vergebenen Namen, brauchst du in G3 und G4 nur die Auswahl ändern und erhältst das neue Ergebnis.

2.2 Namen für Auswahllisten verwenden

Im Blogbeitrag „Datenüberprüfung: Liste zulassen“ habe ich vor einiger Zeit beschrieben, wie eine einfach Drop-Down-Liste mit Excel erstellt wird.

Das Definieren eines Namens für einen Zellbereich hast du in diesem Beitrag bereits im Abschnitt 1 kennengelernt.

Um in Zelle G3 mit dem Namen „Auswahl“ nun auch eine Auswahl des Modells möglich zu machen, gehst du ins Menü Daten / Datentools und rufst dort die „Datenüberprüfung“ auf.

Fülle die nachfolgende Maske nun so aus:

Mit G4 verfährst du analog.

Klickst du jetzt G3 oder G4 an, erscheint am rechten Rand jeweils ein kleines Dreieck, mit dem du Auswahllisten aufrufen kannst.