#Namen in #Excel – und wenn der #Bereich wächst?


Sie können sich erinnern? Ich hab Ihnen neulich erzählt, wie praktisch es ist, wenn Sie Bereichen einen Namen geben. Was aber, wenn der Bereich selbst „wachsen“ kann – immer wieder werden unten Datensätze angefügt. Und das vielleicht auch noch von anderen Menschen. Müssen Sie diese dann darin einschulen, wie sie den Namen neu richtig setzen?

Natürlich nicht. Erzeugen Sie den Bereich für den Namen dynamisch.

Das geht so: Sie öffnen über FORMELN > definierte Namen > Namensmanager den Namensmanager und öffnen mit Neu… den Dialog für die Namens-Neuanlage.

XTipp Bereich.verschieben Name neu

Als Name suchen Sie sich etwas Passendes (nur Buchstaben, Ziffern und _, und ein Buchstabe zu Beginn, und kein reserviertes Wort) aus, ich hab mich für lst_Ort entschieden. Den Kommentar können Sie frei wählen, er dient nur dazu, dass Sie in sieben Jahren noch nachlesen können, wozu Sie diesen Namen erstellt haben. Und bei Bezieht sich auf kommt jetzt das Dynamische:

=BEREICH.VERSCHIEBEN(Name_dyn!$A$1;1;0;
ANZAHL2(Name_dyn!$A:$A)-1;1)

XTipp Bereich.verschieben Name

So. Fertig. Funktioniert.

Und was heißt das? Fitzeln wir das auseinander, und betrachten wir dazu die Funktion BEREICH.VERSCHIEBEN näher. Sie schaut so aus:
BEREICH.VERSCHIEBEN(Startzelle;nachunten;nachrechts;Höhe;Breite). Und liefert als Ergebnis den Bereich, der nachunten Zeilen und nachrechts Spalten von der Startzelle aus verschoben ist und Höhe Zeilen lang und Breite Zeilen breit ist. Wenn Höhe und Breite nicht angegeben sind, gibt’s nur eine Zelle zurück.

Und wir verwenden hier

  • Startzelle: Name_dyn!$A$1 . Es geht also in der Zelle A1 des Blatts Name_dyn los mit unserer Bereichverschieberei. 
  • nachunten: 1. Der Bereich mit den Orten beginnt also eine Zelle unterhalb der Startzelle. In Zeile 2. Passt.
  • nachrechts: 0. Der Bereich mit den Orten beginnt in derselben Spalte wie die Startzelle – in Spalte A also.
  • –> Der Bereich mit den Orten beginnt in A2. Perfekt.
  • Höhe: ANZAHL2(Name_dyn!$A:$A)-1. Da ist jetzt der eigentliche Trick versteckt. Wir zählen die nicht-leeren Zellen in der Spalte A auf dem Blatt Name_dyn mit der Funktion ANZAHL2 und ziehen davon 1 ab (die Überschrift). In dem Beispiel kommt bei ANZAHL2(Name_dyn!$A:$A) 9 heraus, davon ziehen wir wie Hans Rosenthal eins ab, macht acht Orte. Hurra!
  • Breite: 1. Wir haben nur eine Spalte, die den Orte-Bereich ausmacht.

Und damit verweist lst_Orte auf den Bereich Name_dyn!A2:A9. Und wenn wir unten Zwettl dazuschreiben, verweist lst_Orte auf Name_dyn!A2:A10. Und wenn …

Das gefällt Ihnen, oder?

Falls Sie mit einer englischen Version arbeiten: BEREICH.VERSCHIEBEN heißt da elegant OFFSET. Und ANZAHL2 entspricht COUNTA.

Über katharinakanns

Microsoft Office Master Specialist mit viel Verständnis für IHR Geschäft - ich analysiere IHRE Situation, optimiere IHRE Prozesse, automatisiere IHRE Routineaufgaben, finde IHRE Lösung, unterrichte IHRE MitarbeiterInnen, mache Vorlagen mit IHRER CI, spare IHRE Zeit und IHR Geld. Ich freue mich darauf, SIE kennenlernen zu dürfen :-)
Dieser Beitrag wurde unter Excel, Uncategorized abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s