Jetzt kommt Bewegung rein: Dynamische Bereichsnamen 2

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.



Kommentar erstellen

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

2 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ß Beitragsautor

      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