Wer in Excel gerne mit benannten Bereichen in Formeln arbeitet wird sich vielleicht schon mal gewünscht haben den Bezugsbereich für den beispielsweise zu summierenden Bereich dynamisch - etwa durch einen Parameter in einer Steuerungszelle - verändern zu können. Diese Funktion bietet die Bereich.Verschieben Funktion.

Auf Anregung von Christian Oehlboeck sehen wir uns heute an, wie mit der Bereich.Verschieben Funktion sehr effizient eine dynamische Year-to-Date Berechnung über einen 12-Monatsbereich realisiert wird.

Unsere Ausgangssituation ist ein 12-Monatssaldenbereich der auch schon statisch zu einer Jahressumme addiert wird. Zusätzlich möchten wir eine dynamische YTD-Berechnung einfügen.

Dies erfolgt mit der mächtigen Bereich.Verschieben Funktion in Zelle F7, diese ist folgendermassen aufgebaut:

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

Bezug: dieser wird auf Zelle H7, also den ersten Monatswert, gesetzt. Hier beginnt der Bereich für die Summierung.
Zeilen: hier könnte der Startpunkt von H7 nach oben oder unten verschoben werden. Hier nicht relevant.
Spalten: hier könnte der Startpunkt von H7 nach links oder rechts verschoben werden. Hier nicht relevant.
Höhe: wir möchten "eine Zeile hoch" summieren, daher Wert 1.
Breite: unser wichtigster Parameter. Wir möchten ja nach Auswahl in Zelle F5 zwischen 1 und 12 Felder breit summieren. Daher Bezug auf Zelle F5.

Um ungültige Selektionen in Zelle F5 zu vermeiden sollte dort eine Datenvalidierung eingerichtet werden (am besten gleich mit Auswahlliste). Dazu vielleicht ein anderes Mal mehr.

Für die automatische Ermittlung des aktuellen Monats kann in Zelle F5 mit der JETZT-Formel eine elegante Lösung geschaffen die beim Öffnen der Berichtsdatei automatisch das üblicherweise gewünschte Year-to-Date Monat ermittelt:

Häufig wird zum Berichtszeitpunkt das Vormonat benötigt - einfach "-1" an die Formeln hinten dransetzen.

Für den wertvollen Input zu diesem Beitrag bedanke ich mich bei Christian Oehlboeck!