☰

Excel中的可變滑動視窗

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Excel中的可變滑動視窗

我使用的是 Excel 2010，但我認為在任何版本的 Excel 中都會以相同的方式解決此問題。

我有一些想要求和的數據 - 本質上是提供一個滑動累積值。例如，在包含 1000 行的表中，我想要一列顯示目前行值與前面 X 行值的總和，其中 X 是我想在另一個儲存格中指定的數字。例如，最後 6 行累積值或最後 5 行或 8 行或其他值。

我嘗試了使用 ADDRESS 和 INDIRECT、INDEX 等的各種組合 - 但似乎沒有任何效果。

下面是一個範例，顯示了所需的結果，其中視窗大小為 2：

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

對於視窗尺寸 3

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

正如我所說，我希望在電子表格的另一個單元格中指定視窗的大小（例如，名為“WindowSize”）。

如果視窗大小超出了有效數據，那麼返回空白或 0 值會很好 - 但這不是必需的。我不介意忽略 #Refs 或手動調整列的開頭來解決這個問題。

我相信這可以透過公式來完成，而不必求助於 Visual Basic。它可以？

謝謝，約什

答案1

這是索引方法：

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

INDEX 是一個非揮發性函數，因為它只在其引用的資料發生變更時才重新計算。

兩個都OFFSET() 和 INDIRECT()是易失性函數。每次 Excel 重新計算時，Volatile 函數都會進行計算。因此，如果它們太多，就會因為不需要的計算而減慢計算速度。

在此輸入影像描述

在此輸入影像描述

答案2

這應該適用於單元格B2：

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

視窗值位於 cell 中$D$2。

答案3

我會使用偏移函數

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

其中 D1 包含其中的行數。

相關內容