Und monatlich grüßt das Murmeltier…

Sind Sie auch jeden Monat aufs Neue damit beschäftigt, Ihre Abschlüsse an den aktuellen Berichtszeitraum anzupassen? Auf diese eintönige Handarbeit kann getrost verzichten, wer die Excel-Formel „Summe(Bereich.Verschieben())“ richtig anzuwenden weiß. In seinem neuen Blog-Beitrag gibt Norbert Engelhardt eine wertvolle Anleitung und zeigt Schritt für Schritt, wie man sich viel lästige Routinearbeit sparen kann.

Wer kennt die Situation nicht: Man sitzt mal wieder über dem Monats- oder Quartalsabschluss und muss die Formeln an den aktuellen Berichtszeitraum anpassen. Unzählige Zeilen auf unterschiedlichen Tabellenblättern, die alle überarbeitet werden wollen. Zudem wartet der Chef schon auf die aktuellen Reports.

 

Mit der Formel „Bereich.Verschieben()“ kann man auf das manuelle Anpassen von Summenformeln getrost verzichten. Es genügt dann, jeweils den aktuellen Monat auszuwählen. Schauen wir uns also die Formel „Summe(Bereich.Verschieben())“ etwas genauer an.

 

Diese Formel musste bislang jeden Monat manuell angepasst werden. Mit Hilfe der Formel „Summe(Bereich.Verschieben())“ lässt sich diese Notwendigkeit automatisieren. Hierfür stellen wir zunächst eine Auswahlmöglichkeit zur Verfügung, um den aktuellen Berichtsmonat zu selektieren:

Dies wird durch eine simple Datenüberprüfung ermöglicht:

 

Im nächsten Schritt wird eine Hilfstabelle benötigt, um die Anzahl der zu betrachtenden Spalten je Monat zu definieren:

  • Spalte Z bildet die Monate 1 – 12
  • Spalte AA kennzeichnet den Monat innerhalb des jeweiligen Quartals
  • Spalte AB stellt den Anfangsmonat -1 des jeweiligen Quartals dar

 

Nach diesen zwei Schritten sieht der Aufbau des Berichts wie unten dargestellt aus. Weiter geht es mit der Summe(Bereich.Verschieben()):

 

Syntax:

Summe(Bereich.Verschieben(Bezug;Zeilen;Spalten;[Höhe];[Breite]))

  • Bezug – Ist der Ausgangspunkt für das Verschieben (im Beispiel D10)
  • Zeilen – Anzahl der Zeilen, um welche der Ausgangspunkt nach unten oder oben verschoben werden soll (im Beispiel 0, da innerhalb der Zeile)
  • Spalten – Anzahl der Spalten, um welche der Ausgangspunkt nach links oder rechts verschoben werden soll (im Beispiel 0, da die YTD Summe immer bei Januar beginnt)
  • Höhe – Anzahl der Zeilen für die Höhe des Summenbereiches (im Beispiel 1, da innerhalb der Zeile)
  • Breite – Anzahl der Spalten für die Breite des Summenbereichs (im Beispiel ist diese vom ausgewählten Monat abhängig – Spalte Z)

Wenn wir dies auf die YTD Berechnung in unserem Beispiel übertragen, nutzen wir folgende Formel:

=SUMME(BEREICH.VERSCHIEBEN(D10;0;0;1;SVERWEIS($F$5;$Y$10:$AA$21;2;0)))

 

Die Breite unseres Summenbereiches ist abhängig vom ausgewählten Monat in Zelle F5. Mit Hilfe eines Sverweis() ermitteln wir den Monatswert aus Spalte Z.

 

Für die Summe QTD ist es notwendig, die Formel etwas anzupassen, da der Ausgangspunkt nicht immer bei Januar liegt, sondern sich mit den Quartalen verschiebt. Hier kommt folgende Formel zum Einsatz:

 

=SUMME(BEREICH.VERSCHIEBEN(D10;0;SVERWEIS($F$5;$Y$10:$AB$21;4;0);1;SVERWEIS($F$5;$Y$10:$AB$21;3;0)))

 

Bezug, Zeile und Höhe bleiben unverändert. Für die Spaltenangabe nutzen wir einen Sverweis(), der uns den Startmonat aus Spalte AB gibt. Für die Breite ziehen wir nun nicht die Monatsangaben 1 – 12 aus Spalte Z heran, sondern die Anzahl des Monats innerhalb des Quartals aus Spalte AA. Auf diese Weise wandert der Ausgangspunkt um den gewählten Berichtsmonat.

 

Mit Hilfe dieser Formel und einer kleinen Hilfstabelle, die man im Live-Betrieb auf einem ausgeblendeten Tabellenblatt hinterlegen sollte, gelingt es, jegliche Zeiträume wie QTD, YTD, MTD oder auch Halbjahre und Vergleichszeiträume zu berechnen.

 

Dynamischer Diagrammbereich

Um eine weitere Einsatzmöglichkeit der Formel Bereich.Verschieben() aufzuzeigen, wird das Beispiel um eine Balkengrafik erweitert. Sobald eine neue Zeile in der Tabelle aufgenommen wird – zum Beispiel ein weiteres Produkt – muss der Datenbereich im Diagramm manuell angepasst werden. Mit einer kleinen Änderung des Diagrammdatenbereiches können wir diese manuelle Arbeit umgehen. Dazu wird zunächst eine Standard-Balkengrafik in Microsoft Excel erstellt und diese nach Regeln des Information Design angepasst.


Da wir die Bereich.Verschieben()-Formel nicht direkt im Diagrammdatenbereich nutzen können, müssen zunächst zwei benannte Bereiche angelegt werden:

 

Produkt

=BEREICH.VERSCHIEBEN('Summe(Bereich.Verschieben())'!$C$10;0;0;ANZAHL2('Summe(Bereich.Verschieben())'!$C:$C)-1;1)

YTD

=BEREICH.VERSCHIEBEN('Summe(Bereich.Verschieben())'!$C$10;0;15;ANZAHL2('Summe(Bereich.Verschieben())'!$C:$C)-1;1)

  • 'Summe(Bereich.Verschieben())‘! – Name des aktuelles Tabellenblatts
  • $C$10 – Erste Zelle der Tabelle
  • 0 bzw. 15 – Definieren die Spalten für die Achsenbeschriftung (Spalte C) bzw. die YTD Werte (Spalte R)
  • ANZAHL2('Summe(Bereich.Verschieben())'!$C:$C) – Mit Hilfe der Formel Anzahl2() ermitteln wir die Anzahl der nichtleeren Zellen in Spalte C

Im nächsten Schritt gilt es, den Datenbereich in der Balkengrafik anzupassen. Hierfür wählen wir das Diagramm mit der linken Maustaste aus und werfen einen Blick auf die Formelzeile:

 

=DATENREIHE(;'Summe(Bereich.Verschieben())'!$C$10:$C$11;'Summe(Bereich.Verschieben())'!$R$10:$R$11;1)

  • $C$10:$C$11 – Kennzeichnet den Bereich für die Aschenbeschriftung
  • $R$10:$R$11 – Kennzeichnet den Bereich für die YTD-Werte

 

Diese Bereichsangaben ersetzen wir nun durch die benannten Bereiche:

=DATENREIHE(;'Summe(Bereichverschieben()).xlsx'!Produkt;'Summe(Bereichverschieben()).xlsx'!YTD;1)

  • 'Summe(Bereichverschieben()).xlsx'! – Ist der Name des aktuelles Excel Files
  • Produkt – Der benannte Bereich für die Achsenbeschriftung
  • YTD – Der benannte Bereich für die YTD-Werte

 

Wird die Eingabe mit „Enter“ bestätigt, erhalten wir folgende Formel in der Formelzeile:

Zunächst hat sich nicht viel in der Balkengrafik verändert. Wenn wir aber nun eine neue Zeile in der Tabelle aufnehmen, wird diese automatisch in der Grafik abgebildet. Alle beschriebenen Einstellungen und Formeln können Sie im dazugehörigen Excel-File nachvollziehen und an Ihre Anforderungen anpassen.