☰

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。可以吗？

谢谢，Yosh

答案1

这是 Index 方法：

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

INDEX 是一个非易失性函数，因为它仅在引用的数据发生变化时才重新计算。

两个都OFFSET() 和 INDIRECT()是易失性函数。易失性函数在每次 Excel 重新计算时都会计算。因此，如果存在大量易失性函数，则不必要的计算会减慢计算速度。

在此处输入图片描述

在此处输入图片描述

答案2

这应该适用于单元格B2：

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

窗口值在单元格中$D$2。

答案3

我会使用偏移函数

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

其中 D1 包含行数。

相关内容