Excel - Formeln und Funktionen - Tipp Nr. 458

Variable Summenbereiche

Tipp getestet unter Excel 2007, 2010, 2013, 2013;

Viele Daten, die Sie mit Excel bearbeiten,"leben". Tag für Tag oder Monat für Monat kommen neue Daten hinzu. Nehmen wir eine Umsatztabelle wie die hier abgebildete.

Sie vergleichen die monatlichen Ergebnisse verschiedener Kalenderjahre. In der letzten Zeile (14) stellen Sie fest, wie sich das Gesamtergebnis gegenüber dem Vorjahr verändert hat. Für diesen Jahresvergleich interessieren Sie natürlich nur die Umsätze bis zu dem Monat, für den Sie auch im laufenden Jahr bereits Daten zur Verfügung haben (im Beispiel Februar).

Gefragt ist jetzt eine Formel, die die auszuwertenden Bereiche flexibel summiert, so dass man nicht jeden Monat manuell anpassen muss! Die Tabellenfunktion BEREICH.VERSCHIEBEN bietet eine elegante Lösung. Damit können Sie variable Zellbereiche definieren, die Sie dann als Parameter an die Summenfunktion übergeben.

Geben Sie in die Zelle C14 (sie vergleicht das Ergebnis 2010 mit dem von 2011) als Formel für die Veränderung des Umsatzes ein:

=SUMME(C2:C13)-SUMME(BEREICH.VERSCHIEBEN(B2;0;0;ANZAHL(C2:C13);1))

Das ist die Veränderungsformel für das erste Jahr. Für die Folgejahre kopieren Sie die Formel aus C14 nach D14 und bei Bedarf weiter. Hier sehen Sie den tatsächlichen Vorteil der BEREICH.VERSCHIEBEN-Funktion: Wann immer Sie einen neuen Monat eintragen, wird im Vergleich sofort auch der entsprechende Monat des Vergleichsjahres berücksichtigt. Das mühsame monatliche "Formelanpassen" ist ein für alle Mal passè!