Google表格中具有数组公式的滚动总和 [英] Rolling sum with array formula in Google Sheets

问题描述

我有下面的数据集. Col1是给定的数据，而Col2是Col1的前5行(含)的滚动总和.

I have the dataset below. Col1 is given data and Col2 is the rolling sum of the previous 5 rows of Col1 (inclusive).

Date      Col1  Col2
01/04/20  2     2
02/04/20  1     3 
03/04/20  4     7
04/04/20        7
05/04/20        7
06/04/20  5     10
07/04/20  2     11
08/04/20        7
09/04/20        7
10/04/20  1     8
11/04/20        3
12/04/20        1
13/04/20        1
14/04/20        1
15/04/20  1     1

有没有一种方法可以使用arrayformula来执行此操作，而不是将求和公式输入到Col2发生故障的每个单元格中?

Is there a way to use arrayformula to do this rather than inputting a sum formula into every cell in Col2 going down?

我有一个使用计数的类似问题并得到了以下解决方案:

I had a similar question using count and was given this solution:

=ArrayFormula(filter(countifs(B2:B,">0",row(B2:B),"<="&row(B2:B),row(B2:B),">"&row(B2:B)-5),A2:A<>""))

我可以将其修改为求和吗?

Can I amend this to work for sum?

推荐答案

如果愿意，您也可以通过两个滚动总和之差来做到这一点:

You can also do it by the difference of two rolling sums if you wish:

=ArrayFormula(filter(sumif(row(B2:B),"<="&row(B2:B),B2:B)-sumif(row(B2:B),"<="&row(B2:B)-5,B2:B),A2:A<>""))

这篇关于Google表格中具有数组公式的滚动总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！