News & Tipps zu Microsoft Excel

1. SmartTools Blogs
2. Excel
3. 17.3.2026

Beitrag aus SmartTools Excel Weekly

Auswertungen dynamisch an den Eingabebereich anpassen

Excel 365 2024 2021 2019 2016 2013

FRAGE In einer Tabelle erfasse ich Einnahmen und Ausgaben über mehrere Jahre. Die Tabelle wird laufend erweitert. Genau da liegt das Problem: In einer anderen Tabelle möchte ich nämlich die Gesamteinnahmen und -ausgaben in den erfassten Jahren berechnen. Dazu müsste ich die entsprechenden Formeln aber immer wieder an die Größe des Eingabebereichs anpassen. Wenn ich die Einnahmesummen jetzt zum Beispiel aus dem Bereich D4:D250 herausziehe, müssten sie nach neuen Eingaben beispielsweise aus dem Bereich D4:D275 gebildet werden. Es gelingt mir nicht, diese Dynamik in meine Summenformeln einzubauen. Haben Sie einen Tipp für mich?

Diverse Anfragen

ANTWORT Dynamische Bereichsadressen lassen sich mit Hilfe einer VERGLEICH-Funktion ermitteln: In einer Spalte mit Datums- oder Zahlenwerten suchen Sie per VERGLEICH nach einer besonders kleinen Zahl (etwa "1E-30"), in einer Spalte mit Textwerten nach einer Zeichenfolge, die nirgendwo sonst in der Spalte auftaucht (etwa "~~~"). Als Ergebnis liefert die Funktion die Position der letzten nicht leeren Zelle in der Spalte. Wenn Sie die gesamte Spalte durchsuchen lassen, entspricht das Ergebnis der Zeilennummer der letzten Zelle.

In einer Tabelle namens "Journal", in der Sie in Spalte A Datumswerte eingegeben haben, erhalten Sie die Position der letzten Zelle dieser Spalte mit

Eine dynamische Bereichsadresse können Sie dann ganz leicht mit einer INDIREKT-Funktion festlegen, in der Sie die fest vorgegebene Startadresse mit der VERGLEICH-Funktion verketten. INDIREKT wandelt eine in Textform eingegebene Bereichsadresse in einen Zellbezug um.

Wenn im oben genannten Beispiel der erste Datumswert etwa in Zelle A4 steht, verweisen Sie auf den Bereich von A4 bis zur letzten Zelle in dieser Spalte mit

Jetzt fehlt noch die Summenberechnung in Abhängigkeit von der Jahreszahl. Dafür bietet sich die Funktion SUMMENPRODUKT an. Sie multipliziert Matrizen und bildet die Summe aus den Ergebnissen.

In diesem Fall arbeiten Sie mit zwei Matrizen: die erste Matrix ist eine Folge von Nullen und Einsen, die aussagen, ob ein Datum im gesuchten Jahr liegt; die zweite Matrix entspricht den Werten, die je nach Jahreszahl addiert werden sollen. Da eine Multiplikation mit Null ebenfalls Null ergibt, fließen nur die Werte in die abschließende Summenbildung ein, deren Jahreszahl übereinstimmt.

Das Beispiel von oben könnten Sie somit fortsetzen, indem Sie zuerst die gesuchte Jahreszahl in Zelle A6 eines neuen Tabellenblatts eingeben. Rechts daneben in Zelle B6 lassen Sie dann die Ausgabensumme in diesem Jahr berechnen. Davon ausgehend, dass Sie die Ausgabebeträge in der Tabelle "Journal" in Spalte C ab Zelle C4 eingegeben haben, lautet die dafür erforderliche Formel:

Die Formel können Sie nach unten kopieren, um die Ausgabensummen für andere Jahre zu ermitteln. Dafür müssen Sie natürlich die gesuchten Jahreszahlen in die jeweils links angrenzenden Zellen eingeben.

Und wenn Sie die Einnahmen in der Tabelle "Journal" in Spalte D eingegeben haben, erhalten Sie in der Auswertungstabelle die Summe für das in A6 stehende Jahr mit folgender Formel: