Monatsgenaue Auswertung: Variable Bereiche vergleichbar machen mit der Funktion BEREICH.VERSCHIEBEN

Für das Jahr 2013 liegen die Absatzzahlen nur bis zum aktuellen Monat vor, für 2012 hingegen sind bereits alle Monatswerte vorhanden. Wie kann nun das kumulierte Ergebnis der bisherigen Monate im Jahr 2013 mit dem entsprechenden Zeitraum des Vorjahres verglichen werden? Bild 1 zeigt diese Konstellation und rechts die Lösung.

Vorschau auf die fertige Lösung

Schritt 1: Anzahl der vorliegenden Monatswerte ermitteln

Der Gesamtwert für 2012 ergibt sich in Zelle C15 ganz einfach mit =SUMME(C3:C14).
Diese Formel wird nach rechts kopiert. In D15 steht dann =SUMME(D3:D14). Diese Formel summiert die bisher vorliegenden Absatzzahlen und liefert somit Monat für Monat den kumulierten Wert für das laufende Jahr.

Allerdings kann das Ergebnis von 9.485 für die zwölf Monate des Jahres 2012 nicht verglichen werden mit dem kumulierten Wert 4.892 für die ersten sechs Monate des Jahres 2013. Das hieße Äpfel mit Birnen vergleichen.

Ein Vergleich ist nur dann sinnvoll, wenn dem kumulierten Ergebnis des laufenden Jahres passgenau die Summe des gleichen Zeitraums aus dem Vorjahr gegenübergestellt wird.

Dieser Vergleich soll in den Zellen F6 und G6 erfolgen. Für G6 genügt der Bezug auf die bereits berechnete Summe in D15. Die Formel für G6 lautet demzufolge =D15.

In Zelle F6 muss sich Monat für Monat die zu berechnende Summe automatisch anpassen – abhängig davon, wie viele Werte im Jahr 2013 schon vorliegen. Wie viele Monatswerte es in der Spalte für 2013 schon sind, lässt sich mit der Funktion ANZAHL ermitteln. Sie zählt Zellen, die eine Zahl enthalten. Die Formel lautet also zunächst =ANZAHL(D3:D14)

Das Ergebnis gibt vor, wie viele Zellen in der Spalte für 2012 zu addieren sind.

Schritt 2: Bereich der zu addierenden Werte ermitteln

Die Funktion BEREICH.VERSCHIEBEN ermittelt den zu kumulierenden Zellbereich, der von Monat zu Monat automatisch größer werden muss.

Hier zunächst die Syntax dieser Funktion:

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

  • C3 ist der Startpunkt des Bereichs und somit das erste Argument.
  • Diese linke obere Ecke der Bereichs wird durch eine 0 (Null) für die nächsten beiden Argumente namens Zeilen und Spalten nicht verschoben.
  • Die Ausdehnung des Bereichs nach unten wird durch die Anzahl der in 2013 belegten Zellen im vierten Argument bestimmt.
  • Die Ausdehnung des Bereichs nach rechts wird mit 1 für eine Spalte im fünften Argument festgelegt.
  • Demzufolge ergibt sich =BEREICH.VERSCHIEBEN(C3;0;0;ANZAHL(D3:D14);1).
  • Diese Funktion meldet berechtigterweise einen Bezugsfehler (#BEZUG), falls noch keine Zahlen für das Jahr 2013 vorliegen.
  • Dieser Fehler wird mit der Funktion WENNFEHLER abgefangen. Im Falle eines Fehlers soll eine 0 ausgegeben werden.

Damit ergibt sich als fertige Formel:
=WENNFEHLER(SUMME(BEREICH.VERSCHIEBEN(C3;0;0;ANZAHL(D3:D14);1));0)

Schritt 3: Mit passender Optik die Aussage hervorheben

Eine bedingte Formatierung in Zelle G6 sorgt für die verständliche Darstellung der positiven beziehungsweise negativen Entwicklung im Vergleich zum Vorjahr.

Das Ergebnis des Vergleichs mit unterschiedlichen Farben signalisieren

Schritt 4: Die Vergleichswerte gut erkennbar machen

Damit auf den ersten Blick erkennbar ist, welche Werte zur Zeit dem Vergleich dienen, werden alle noch nicht relevanten Zahlen im Jahr 2012 ausgegraut.

Werte abblenden, die nicht zum Vergleich dazugehören

Dieser Beitrag wurde unter Excel abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

2 Antworten auf Monatsgenaue Auswertung: Variable Bereiche vergleichbar machen mit der Funktion BEREICH.VERSCHIEBEN

  1. Wittrock,Joachim-Oskar sagt:

    Hallo Frau Hügemann,
    wie kann ich zusätzlich zur kumulierten Jahressumme den aktuellen Monat der Kumulation anzeigen lassen?
    J-O.Wittrock

    • Hallo Herr Wittrock,
      den Text in Zelle F4 bis zum aktuellen Monat möchte ich um den Monatsnamen erweitern.
      Ich gehe wie folgt vor:
      1. Den bisherigen Text in F4 lasse ich durch eine Berechnung ausgeben = „bis zum aktuellen Monat“.
      2. Diesen Text verkette ich nun mit dem Ausdruck TEXT(DATUM(JAHR(HEUTE());ANZAHL(D3:D14);1);“MMMM“). Dabei gibt ANZAHL(D3:D14) die Anzahl der gefüllten Monatszellen wieder und damit die Nummer des gesuchten Monats. Mit der Funktion DATUM setze ich ein fiktives Datum mit dem jeweiligen Monat zusammen. Die Funktion TEXT gibt dann mit der Formatanweisung „MMMM“ den vollständigen Monatsnamen des jeweiligen Datums zurück.

      Die Formel in F4 sieht dann insgesamt wie folgt aus:
      = „bis zum aktuellen Monat “ & TEXT(DATUM(JAHR(HEUTE());ANZAHL(D3:D14);1);“MMMM“)

      Viel Erfolg
      Hildegard Hügemann

Schreibe einen Kommentar

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