Tutorials

Dynamische Jahressummen mit der BEREICH.VERSCHIEBEN-Funktion

Melden

Hier ist ein ausführliches Tutorial, wie du die Excel-Funktion BEREICH.VERSCHIEBEN (englisch: OFFSET) nutzt, um eine Summe dynamisch bis zu einem bestimmten Punkt (z. B. einem ausgewählten Jahr) zu berechnen.

In vielen Berichten möchte man nicht immer die gesamte Zeile summieren, sondern nur die Werte vom Start bis zu einem ganz bestimmten Jahr (Year-to-Date oder kumuliert bis zu einem Stichtag). Mit der Kombination aus SUMME, BEREICH.VERSCHIEBEN und VERGLEICH kannst du diesen Prozess automatisieren.

1. Die Ausgangslage

Stell dir vor, du hast eine Tabelle, in der die Jahre in der obersten Zeile stehen (z. B. von Spalte B bis G) und die entsprechenden Werte direkt darunter in Zeile 2.

  • Zeile 1: Jahre (z. B. 2020, 2021, 2022, 2023, 2024, 2025)
  • Zeile 2: Werte (Umsatzzahlen, Kosten, etc.)
  • Zelle J1: Das "Zieljahr" (Hier gibst du ein, bis zu welchem Jahr summiert werden soll).

2. Die Kernfunktionen erklärt

Um die Formel zu verstehen, schauen wir uns die drei Bestandteile an:

  1. SUMME(…): Addiert die Zahlen im berechneten Bereich.
  2. BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite]): Diese Funktion erstellt einen dynamischen Zellbereich. Wir nutzen sie, um die Breite des Summenbereichs festzulegen.
  3. VERGLEICH(Suchkriterium; Suchmatrix; [Vergleichstyp]): Diese Funktion sagt uns, an welcher Position (Spalte) sich das gesuchte Jahr befindet.

3. Schritt-für-Schritt-Anleitung

Schritt A: Die Position des Zieljahres finden

Zuerst müssen wir wissen, wie viele Spalten wir summieren müssen. Wenn "2022" in der dritten Spalte deines Datenbereichs steht, muss die Breite unserer Summe "3" sein.

=VERGLEICH(J1; B1:G1; 0)

Dieses Ergebnis liefert eine Zahl (z. B. 3 für das dritte Jahr im Bereich).

Schritt B: Den dynamischen Bereich definieren

Jetzt nutzen wir BEREICH.VERSCHIEBEN. Wir starten bei der ersten Datenzelle (B2) und sagen Excel, dass der Bereich eine bestimmte Breite haben soll.

BEREICH.VERSCHIEBEN(B2; 0; 0; 1; VERGLEICH(J1; B1:G1; 0))
  • B2: Startpunkt (der erste Wert).
  • 0; 0: Wir bleiben in derselben Zeile und Spalte wie der Startpunkt.
  • 1: Die Höhe des Bereichs ist 1 Zeile.
  • Breite: Hier setzen wir den VERGLEICH ein. Wenn das Jahr an 3. Stelle steht, ist der Bereich 3 Spalten breit.

Schritt C: Die fertige Formel

Nun umschließen wir alles mit der SUMME-Funktion:

=SUMME(BEREICH.VERSCHIEBEN(B2; 0; 0; 1; VERGLEICH(J1; B1:G1; 0)))

4. Funktionsweise im Detail (Beispiel)

Angenommen, deine Daten sehen so aus:

  • B1:G1 enthält: 2020, 2021, 2022, 2023, 2024, 2025
  • B2:G2 enthält: 10, 20, 30, 40, 50, 60
  • In Zelle J1 steht: 2022
  1. VERGLEICH(2022; B1:G1; 0) ergibt 3, da 2022 der dritte Wert in der Liste ist.
  2. BEREICH.VERSCHIEBEN(B2; 0; 0; 1; 3) erstellt einen Bereich, der bei B2 beginnt, 1 Zeile hoch und 3 Spalten breit ist. Das entspricht dem Bereich B2:D2.
  3. SUMME(B2:D2) rechnet 10 + 20 + 30 und ergibt 60.

5. Vorteile dieser Methode

  • Flexibilität: Sobald du das Jahr in Zelle J1 änderst (z. B. auf 2024), passt sich die Summe sofort an, ohne dass du die Formel anpassen musst.
  • Erweiterbarkeit: Wenn du am Ende neue Jahre hinzufügst, musst du lediglich den Bereich in der VERGLEICH-Funktion (B1:G1) vergrößern.
  • Automatisierung: Ideal für Dashboards, bei denen Nutzer über ein Dropdown-Menü den Zeitraum wählen können.

Tipp: Wenn du die englische Excel-Version nutzt, lautet die Formel: =SUM(OFFSET(B2, 0, 0, 1, MATCH(J1, B1:G1, 0)))

0