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.
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.
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.
2 Antworten auf Monatsgenaue Auswertung: Variable Bereiche vergleichbar machen mit der Funktion BEREICH.VERSCHIEBEN