使用 Google 表格中的数组公式滚动总和 [英] Rolling sum with array formula in Google Sheets
本文介绍了使用 Google 表格中的数组公式滚动总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下面的数据集.Col1 是给定的数据,Col2 是 Col1 前 5 行(含)的滚动和.
日期 Col1 Col201/04/20 2 202/04/20 1 303/04/20 4 704/04/20 705/04/20 706/04/20 5 1007/04/20 2 1108/04/20 709/04/20 710/04/20 1 811/04/20 312/04/20 113/04/20 114/04/20 115/04/20 1 1
有没有办法使用 arrayformula 来做到这一点,而不是在 Col2 向下的每个单元格中输入求和公式?
我有一个
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
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屋!
查看全文