Jetzt kommt Bewegung rein: Dynamische Bereichsnamen 13

Bereichsnamen, die sich automatisch an einen sich ändernden Zellbereich anpassen, machen das Leben in Excel einfacher.
 

In Excel gibt es ja die sehr praktische Möglichkeit, für einen Zellbereich einen Namen zu vergeben, den man später in seinen Funktionen verwenden kann. Gerade komplexere oder verschachtelte Funktionen werden leichter lesbar, wenn man anstelle von Zellkoordinaten einfach einen aussagefähigen Namen verwendet.

Was macht man aber, wenn sich der Bereich ständig ändert, auf den sich ein Name beziehen soll? Weil man z.B. am unteren Ende laufend neue Datensätze anfügt.

Mit einem sich dynamisch anpassenden Bereichsnamen ist das kein Problem!

Und so geht’s:

Die Ausgangslage

Nehmen wir an, du hast in Excel ein kleines Haushaltsbuch erstellt, in der du deine Ausgaben in einer Tabelle erfasst (die Musterdatei kannst du dir hier herunterladen). Es gibt Spalten für Datum, Kategorie, Bemerkung und natürlich für den Betrag. Außerdem möchte ich in einer kleinen Übersicht sehen, wieviel Geld ich in jeder Kategorie ausgegeben habe:

Eingabeliste und Auswertung

Eingabeliste und Auswertung

Dazu habe ich für den Bereich B4:B12 den Namen “Kategorie” vergeben und für den Bereich D4:D12 den Namen “Ausgaben”:

Ein statischer Namensbereich

Ein statischer Namensbereich

Diese Namen verwende ich in den Zellen G4:G10 in einer einfachen SUMMEWENN-Funktion, um meine kleine Auswertung zu bekommen.

Hier wird aber schon das Problem deutlich: Sobald ich in Zeile 13 neue Daten ergänze, stimmt der angegebene Bereich nicht mehr und die Auswertung ist falsch:

Das Problem mit statischen Namen

Das Problem mit statischen Namen

Jetzt kommt die Dynamik ins Spiel:
Anstatt einen fixen Zellenbereich für unsere beiden Namen zu definieren, vergeben wir mit Hilfe von ein paar kleinen Excel-Funktionen dynamische Bereichsnamen. Öffnen wir also den Namens-Manager und verändern den Bezug für den Namen “Kategorie” im Feld “Bezieht sich auf” wie folgt:
=$B$4:BEREICH.VERSCHIEBEN($B$4;VERGLEICH("";$B:$B;-1)-4;0)

Dynamische Namen

Dynamische Namen

Dann wiederholen wir das Ganze auch für den Namen “Ausgaben” und geben diese Formel ein:
=$D$4:BEREICH.VERSCHIEBEN($D$4;VERGLEICH("";$B:$B;-1)-4;0)

... und noch einmal.

… und noch einmal.

Und schon passt unsere Auswertung wieder, da sich die benannten Bereiche “Kategorien” und “Ausgaben” automatisch angepasst haben und den zuletzt hinzugefügten Datensatz einschließen:

Korrekte Auswertung

Korrekte Auswertung

Zur Erläuterung
Wir geben statt des statischen einen dynamischen Bezug ein. Dabei bleibt Anfangsbezug immer noch gleich, nämlich $B$4.
Der Endbezug wird über die BEREICH.VERSCHIEBEN-Funktion ermittelt. Diese Funktion habe ich hier und hier schon ausführlich erläutert. Hier nur noch mal die Kurzfassung:
Wir verschieben einen Zellbereich, ausgehend von B4, um eine bestimmte Anzahl von Zeilen nach unten. Um wieviele genau, ermitteln wir mit der VERGLEICH-Funktion. Mit dieser suchen wir nämlich nach der letzten leeren Zelle in Spalte B. Diesen Trick habe ich in diesem Artikel beschrieben. Die Funktion liefert die Zeilennummer der letzten gefüllten Zeile wieder. Von der so ermittelten Zeilennummer ziehe ich in meinem Beispiel noch 4 ab, da meine Datentabelle ja erst ab der vierten Zeile beginnt.

Dynamische Namen werden im Namensfeld leider nicht angezeigt, ebensowenig im Gehezu-Fenster, das man über die Funktionstaste F5 aufrufen kann. Wenn man in diesem Fenster aber im Verweis-Feld den Namen eintippt, wird trotzdem der korrekte Bereich markiert:

Manuelle Eingabe des Namens

Manuelle Eingabe des Namens


Korrekter Bereich wird markiert

Korrekter Bereich wird markiert

Du kannst nun weitere Datensätze eingeben und wie eben gezeigt mit F5 überprüfen, dass tatsächlich der korrekte Bereich für den Namen ermittelt wurde.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

13 Gedanken zu “Jetzt kommt Bewegung rein: Dynamische Bereichsnamen

  • Gerhard Pundt

    Hallo Martin,

    das war wieder ein interessanter Beitrag zum Thema Dynamische Bereichsnamen. Dazu kam mir gleich ein fortführender Gedanke: Mal angenommen, im Bereich Kategorie taucht ein neuer Begriff auf, z.B. Darlehen. Dieser Begriff steht noch nicht in der Zusammenfassung rechts daneben. Man könnte ihn nun einfach dort eintragen und die Formel SUMMEWENN runter ziehen, die einfachste Lösung. Hat man aber sehr viele Datensätze einzutragen mit vielen neuen Begriffen, kann es leicht unübersichtlich werden. Gibt es möglicherweise eine automatische Lösung (ohne VBA), die neuen Begriffe einzutragen?
    Mit dem Spezialfilter kann man etwas hin bekommen, macht aber mehr Arbeit als der manuelle Eintrag.

    Viele Grüße

    Gerhard

    • Martin Weiß Autor des Beitrags

      Hallo Gerhard,

      eine “Quick-and-dirty”-Lösung, um neue Kategorien mehr oder weniger automatisch zu ergänzen, könnte sein, die Kategorienliste als eine Pivot-Tabelle anzulegen und daneben die SUMMEWENN-Funktionen stellen. Durch die Neukalkulation tauchen neue Kategorien automatisch Liste auf und man müsste nur noch die SUMMEWENN-Formel nach unten ziehen.

      Schöne Grüße,
      Martin

  • Manfred Kurth

    Ich habe den Beitrag zu dynamischen Beeichnamen ausprobiert. Es klappt prima. Ein neuer Datensatz wird am unteren Ende der Tabelle eingefügt. Wie ist es aber bei Tabellen, die vorn in der Spalte unterschiedliche Karegorien haben? Als Beispiel eine Adressendatei, bei der erst alle privaten Adressen kommen (Suchkriterium “Privat”), dann Adressen zum Thema Gesundheit (Suchkriterium “Medizin”) u.s.w.
    Ich habe versucht, im dynamischen Bereichnamen hinteren Teil ( …Vergleich(“”; …) die Leerstelle durch das Suchkriterium z.B. “Privat” zu ersetzen. Das klappt nicht. Hast Du vielleicht eine passende Idee ?
    Ich muss noch erwähnen, dass ich das Ganze im Rahmen einer VBA-Prozedur einsetzen will.Neue Adressen sollen einfach am Tabellenschluss eingetragen werden, dann wird nach Kategorien neu sortiert und die Bereiche sollten damit aktuell sein.

    • Martin Weiß Autor des Beitrags

      Hallo Manfred,

      wenn Du ein zusätzliches Kriterium in der Auswertung berücksichtigen möchtest, musst Du nur einen weiteren Namen definieren. Dieser muss sich dann nur auf die entsprechende Spalte beziehen, der Rest der Formel bleibt gleich. Das Leerzeichen in der VERGLEICH-Funktion dient nur dazu, die erste leere Zelle am Ende der Tabelle zu finden. Also angenommen, Du hast eine zweite Kategorie in Spalte A, dann lautet die Formel für den dynamischen Namen:
      =Tabelle1!$A$4:BEREICH.VERSCHIEBEN(Tabelle1!$A$4;VERGLEICH(“”;Tabelle1!$A:$A;-1)-4;0)

      Schöne Grüße,
      Martin

  • Jake

    Hallo Martin,
    vielen Dank für Deine tollen Erklärungen. Ich verstehe nur noch nicht ganz den Sinn der “-4”, mit der Du bewirkst, dass Du erst ab Zeile 4 die Zeilen zählst. Ist die wirklich notwendig? Hast Du das nicht eh schon durch $D$4 festgelegt?

    Gruß,
    Jake

    • Martin Weiß Autor des Beitrags

      Hallo Jake,

      mit $D$4 wird nur der Anfangspunkt festgelegt. Nun geht es ja darum, wieviele Zeilen man von dort aus braucht. Die VERGLEICH-Funktion liefert die Zeilennummer der letzten gefüllten Zeile, also z.B. Zeile 13. Der dynamische Bereich umfasst aber nicht 13 Zeilen, da die eigentliche Tabelle erst ab Zeile 4 beginnt. Und deshalb muss der Wert 4 noch abgezogen werden. Ich weiß, ist am Anfang etwas verwirrend.

      Schöne Grüße,
      Martin

  • Hubert Schostek

    Sehr geehrter Heer Martin Weiß,
    die Anwendung der dynamischen Bereichsnamen ist für die dargestellten Beispiel gut brauchbar.

    Ist es möglich, eine Tabelle mit dynamischen Bereichsnamen zu entwerfen, die für jeden Monat tägliche Tätigkeiten beinhaltet, die einem bestimten Wochentag zugeordnet sind. Beim Wechsel des Monats sollten die Tätigkeiten dem gleichen Wochentag zugeordnet bleiben.
    Dabei gibt es Tätigkeiten, die sich am gleichen Wochentag wiederholen wie auch Tätigkeiten, die neu aufgenommen werden.

    Der aktuelle Zustand ist, dass beim Wechsel des Monats sich auch der Wochentag zum Monatsbeginn ändert, so dass die geplanten Tätigkeiten sich an einem falschen Tag befinden.
    Für diese Änderung könnte die Anwendung des dynamischen Bereichsnamens nutzbar sein, sonst müsste für jeden Monatstag die Tätigkeiten neu eingetragen werden.

    Die o,g, Erweiterung ist für ein Programm gedacht, das unbegrenzt Jahre, inclusive Schaltjahr, Monate sowie Wochentage berücksichtigt.

    Mit freundlichen Grüßen
    Hubert Schostek

  • Hubert Schostek

    Sehr geehrter Herr Weiß,

    wie kann die dynamisch anpasssender Bereichsnamen für Arbeitskalender anwenden.
    Der Arbeitskalender enthält monatliche Tätigkeiten, dabei einige den bestimmten Wochentagen zugeordnet sind.
    Bei Monatswechsel ändern sich die Wochentage die Tabelle bleibt unverändert übernommen und dadurch die bestimmte Tätigkeiten in den falschen Wochentag geraten.

    Das Programm berücksichtigt angegebenen Jahr inclusive Schaltjahr, Monat und Wochentag.

    Mit freundlichen Grüßen
    Hubert Schostek

    • Martin Weiß Autor des Beitrags

      Hallo Herr Schostek,

      ein Tipp ist hier etwas schwierig, da ich den genauen Aufbau und die Funktionsweise Ihres Arbeitskalenders nicht kenne. Ich fasse kurz zusammen, wie ich Sie verstanden habe:
      – Bestimmte Tätigkeiten sollen immer einem bestimmten Wochentag zugeordnet werden.
      – Da der Aufbau des Kalenders statisch ist, stehen nach einem Monatswechsel die Wochentage nicht immer an der gleichen Stelle. Wenn die Tage spaltenweise angeordnet sind, wandert der Montag in eine andere Spalte, bei zeilenweiser Anordnung wandert der Montag in eine andere Zeile. Richtig?

      In diesem Fall könnten Sie also per WOCHENTAG-Funktion prüfen, wie der Wochentag in der jeweiligen Zelle lautet und davon abhängig z.B. mit einer WENN-Funktion die fix zugeordneten Tätigkeiten eintragen lassen. Also ungefähr so:
      =WENN(WOCHENTAG(A2;2)=1;”Montagsaktivität;WENN(WOCHENTAG(A2;2)=2;”Dienstagsaktivität”;””))

      Dynamische Bereichsnamen sind hier nicht zwingend notwendig.

      Schöne Grüße,
      Martin

  • Dominik Honegger

    Guten Tag Martin,

    Erstmal herzlichen Dank für die wertvollen Inputs hier. Weiter so!
    Das klappt ganz gut, was ich als störend empfinde ist der Umstand, jedes Mal bei Einfügen einer neuen Zeile im Namensmanager die Formel anpassen zu müssen, da der Bezug zur ersten Zeile nicht mehr stimmt. Gibt es da eine Umgehungsmöglichkeit?

    Beste Grüsse,
    Dominik

    • Martin Weiß Autor des Beitrags

      Hallo Dominik,

      die Idee bei den dynamischen Bereich ist es ja gerade, dass die Formel nicht ständig angepasst werden muss. Im beschriebenen Beispiel ist das auch der Fall: Wenn neue Zeilen in der Ausgabenliste dazukommen, passen die Formeln trotzdem. Hast du die Bezüge im Namensmanager genauso eingegeben, wie im Beispiel?

      Schöne Grüße,
      Martin

  • Michael Krüger

    Hallo,

    danke für den Tipp und die vielen, vielen anderen, durch die ich mich momentan nach und nach hindurchlese. Superinteressante Sachen dabei!!

    Zum hiesigen Artikel: Ich habe eine ähnliche Ausgabendatei und habe die Bereiche einfach dummplump bis zur Zeile 1000 hinunter fix benannt; das ist ungefähr die Zahl meiner Ausgaben pro Jahr. Ich nehme aus verschiedenen Gründen (ich teile u.a. die Summen auch noch nach Monaten auf) nicht ZÄHLENWENN, sondern SUMMENPRODUKT für die Auswertung, und diese Funktion stört sich überhaupt nicht an den vielen leeren Zeilen.

    Gibt es im gegebenen Beispiel Gründe mit dem dynamischen Bereich zu arbeiten (statt mit: Bereich “Ausgaben” erstreckt sich von $D$4 bis $D$1000) außer den Grund, meinem armen Computer unnötige Arbeit zu ersparen?

    Lieben Gruß
    Michael

    • Martin Weiß Autor des Beitrags

      Hallo Michael,

      man kann natürlich immer einen bestimmten größeren statischen Bereich in seinen Formeln berücksichtigen (z.B. bis Zeile 1000). Aber irgendwann wird vielleicht auch diese Zeile überschritten und dann stimmen die Formeln einfach nicht mehr. In kleinen und überschaubaren Kalkulationen mag das noch auffallen, aber in komplexeren Anwendungen kann eine statische Angabe fatal, da man die fehlerhaften Ergebnisse oftmals nicht oder viel zu spät erkennt.

      Schöne Grüße,
      Martin