Variables gleitendes Fenster in Excel

Question

Ich verwende Excel 2010, gehe aber davon aus, dass dies in jeder anderen Excel-Version gleich gelöst wird.

Ich habe Daten, die ich summieren möchte – also im Wesentlichen einen gleitenden kumulativen Wert liefern möchte. In einer Tabelle mit 1000 Zeilen hätte ich beispielsweise gerne eine Spalte, die die Summe des Werts der aktuellen Zeile und der vorhergehenden X Zeilen anzeigt, wobei X eine Zahl ist, die ich in einer anderen Zelle angeben möchte. Beispielsweise der kumulative Wert der letzten 6 Zeilen oder der letzten 5 oder 8 oder was auch immer.

Ich habe verschiedene Kombinationen mit ADRESSE und INDIREKT, INDEX usw. ausprobiert – aber nichts schien zu funktionieren.

Hier ist ein Beispiel, das die gewünschten Ergebnisse zeigt, wobei die Fenstergröße 2 beträgt:

  |A     |B
1 |Value |Cumulative
2 | 1    |   
3 | 2    |3
4 |15    |17
5 |10    |25
6 |11    |21
7 | 8    |19

und für Fenstergröße 3

  |A     |B
1 |Value |Cumulative
2 | 1    |   
3 | 2    |
4 |15    |18
5 |10    |27
6 |11    |36
7 | 8    |29

Wie gesagt, ich möchte, dass die Größe des Fensters in einer anderen Zelle der Tabelle angegeben wird (z. B. mit dem Namen „Fenstergröße“).

Wenn die Fenstergröße über die gültigen Daten hinausgeht, wäre es schön, wenn ein leerer oder 0-Wert zurückgegeben würde – aber das ist nicht unbedingt erforderlich. Ich habe nichts dagegen, #Refs zu ignorieren oder den Spaltenanfang manuell anzupassen, um dies zu berücksichtigen.

Ich würde gerne glauben, dass dies mit einer Formel möglich ist und ohne dass man auf Visual Basic zurückgreifen muss. Ist das möglich?

Danke, Yosh

Answer 1

Hier ist die Indexmethode:

=IF(ROW(1:1)<$E$1,"",SUM(INDEX(A:A,(ROW()-$E$1)+1):INDEX(A:A,ROW())))

INDEX ist eine nichtflüchtige Funktion, die nur dann neu berechnet wird, wenn sich die Daten ändern, auf die sie sich bezieht.

BeideOFFSET() und INDIRECT()sind volatile Funktionen. Die volatile Funktion wird jedes Mal berechnet, wenn Excel neu berechnet. Wenn es also viele davon gibt, verlangsamt dies die Berechnungen durch unnötige Berechnungen.

Answer 2

Dies sollte auf dem Handy funktionieren B2:

=SUM(A2:INDIRECT(ADDRESS(ROW(A2)-($D$2-1),1,4)))

Der Fensterwert steht in Zelle $D$2.

Answer 3

Ich würde die Offset-Funktion verwenden

=sum(offset(A2,0,0,D1))

wobei D1 die Anzahl der Zeilen enthält.