Namensmanager

Bereich eines Namens automatisch erweitern

Roman Formeln , , , , , , , , ,

Wir haben uns bereits den Namensmanager und die Dropdown-Listen angeschaut. Wenn Du zu der Kriterienliste weitere Begriffe hinzufügst, wird der Bereich Deines Namens allerdings nicht automatisch erweitert - Du musst via Namensmanager die letzte Zelle manuell angeben, respektive den effektiven Bereich neu selektieren. Mit der Formel "BEREICH.VERSCHIEBEN" kann dieser Schritt automatisiert werden. Excel sagt uns folgendes über diese Formel:

Gibt einen Bezug zurück, der gegenüber dem angegebenen Bezug versetzt ist.

Man kann damit nicht nur den Bereich "A1:A3" nach "A3:A5" verschieben, sondern auch auf "A1:A5" erweitern - und genau das wollen wir in diesem Beitrag thematisieren. Schauen wir uns einmal die Syntax der Formel an:

BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])

Wir beginnen also mit einem Bezug - einer einzelnen Zelle (A1). "Zeilen" und "Spalten" stehen jeweils für eine Verschiebung um X Zellen nach unten respektive nach rechts. "[Höhe]" und "[Breite]" hingegen lässt uns einen Bereich nach unten oder nach rechts erweitern. Mithilfe der Formel "ANZAHL2" als "Höhe" kannst Du zählen, wie viele Argumente in einer Spalte stehen und so die Anzahl Zellen Deines Bereiches ermitteln. Die Formel lautet demnach:

=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2(A:A);)

Wir setzen A1 absolut ($), weil diese Formel als Namen definiert werden soll. Der Name ist in einem Tabellenblatt oder einer gesamten Excel-Arbeitsmappe vorhanden. Ohne die Dollarzeichen würde sich deshalb in anderen Zellen die Referenz automatisch verschoben werden und nicht an A1 festhalten. Beachte anschliessend die nacheinanderfolgenden Semikolon (;) - wir geben weder bei "Zeilen" noch bei "Spalten" etwas ein - die ANZAHL2-Formel ermittelt die Höhe unseres Bereiches anhand sämtlicher nichtleerer Zellen in Spalte A.

Die obengenannte Formel musst Du bei Deinem Namen als Bezug eingeben, um die Kriterien nachher in Deiner Dropdown-Liste aufzuführen (siehe "Namensmanager" und "Dropdown-Liste"). Klicke hierfür auf den Menüpunkt "Formeln" → "Namen definieren". Der Bezug "$A$1" wird Dir automatisch mit dem Namen des entsprechenden Tabellenblattes ergänzt (bspw. Tabelle1!$A$1). Bitte beachte deshalb folgendes, wenn Du auf andere Tabellenblätter Bezug nehmen musst: Falls Du Deine Kriterien zum Beispiel im separaten Tabellenblatt "Parameter" hast, heissen Deine Bezüge "Parameter!$A$1" und "Parameter!A:A".

Wenn wir nun ein neues Kriterium zu unserer Liste hinzufügen, wird dies automatisch zum Namen und folglich in die Dropdown-Liste mitaufgenommen.

Einführung in den Namensmanager

Roman Allgemein ,

In diesem Beitrag möchte ich Dir den Namensmanager vorstellen. Du findest diese Funktion im Menüband unter "Formeln". In Excel kann einem Bereich ein Name gegeben und zu einem späteren Zeitpunkt kann darauf zugegriffen werden. Als einfaches Beispiel: Wir können mittels Formel eine Summe bilden. Die Formel dazu lautet:

=SUMME(A2:A6)

Markierst Du nun den Bereich A2 bis A6, kannst Du im Namenfeld, wo "A2" steht, einfach einen Namen reinschreiben und mit Enter bestätigen. Im Bild unten siehst Du das Namenfeld. Wichtig zu beachten ist, dass Du bei der Namensgebung keine Leerschläge oder Sonderzeichen verwenden darfst. Zahlen hingegen sind erlaubt. Anstatt im Namenfeld den Namen einzugeben, gelangst Du im Menüband via "Formeln" → "Namen definieren" zum gleichen Ziel.

Namensfeld

Wenn Du nun erneut die Summe bilden möchtest, kannst Du direkt mit der Eingabe von "=SUMME(" beginnen und Dich dann auf Deinen definierten Namen beziehen. Dieser ist nun stellvertretend für den Bereich A2 bis A6. Du findest Deinen Namen auch in der Liste mit den Formeln, allerdings mit einem anderen Kennzeichen vornedran:

VerwendungInFormel

Anpassungen an bestehenden Namen

Um im Nachhinein den definierten Bereich anzupassen oder zu erweitern, kannst Du via "Formeln" auf "Namensmanager" klicken. Dies könnte der Fall sein, wenn Du weitere Daten in Deiner Excel-Tabelle erfasst hast. Im Namensmanager findest Du sämtliche definierten Namen:

Namensmanager

Falls Du Anpassungen vornehmen musst, weil Du beispielsweise weitere Zahlenwerte eingetragen hast, kannst Du den Bereich unter "Bearbeiten..." neu kennzeichnen. Eine schnellere Variante besteht darin, im unteren Bildrand bei "Bezieht sich auf:" einfach die Zahl von "...$A$6" durch den neuen Zeilenindex (beispielsweise 10) zu ersetzen.

Es gibt natürlich weitere Möglichkeiten, wo die Definition von Namen hilfreich ist. Du kannst Dir damit sogenannte Dropdown-Listen erstellen, damit aus einer Liste nur vorgegebene Werte ausgewählt werden können. Das Erweitern des Bereiches im Falle von neuen Zahlenwerten gemäss dem Beispiel oben kann umgangen werden, wenn Du den Bezug mit der Formel "BEREICH.VERSCHIEBEN" dynamisch machst. Dadurch wird Dein Name automatisch um die neuen Werte ergänzt. Dieser komplexeren Formel sowie den Dropdown-Listen möchte ich jedoch eigene Beiträge widmen.