Google表格中带有数组公式的滚动计数 [英] Rolling count with array formula in Google Sheets

问题描述

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

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

Date      Col1  Col2
01/04/20  2     1
02/04/20  1     2 
03/04/20  4     3
04/04/20        3
05/04/20        3
06/04/20  5     3
07/04/20  2     3
08/04/20        2
09/04/20        2
10/04/20  1     3
11/04/20        2
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 count formula into every cell in Col2 going down?

推荐答案

您可以在行上使用带有条件的Countifs:

You can use Countifs with a condition on the rows:

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

假设数字为正

要包含任何数字，可以使用:

To include any number, you can use:

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

如果您希望将与将来日期相对应的行显示为空白，则可以添加If语句:

If you wanted to show rows corresponding to future dates as blanks, you could add an If statement:

=ArrayFormula(filter(if(A2:A>today(),"",countifs(isnumber(B2:B),true,row(B2:B),"<="&row(B2:B),row(B2:B),">"&row(B2:B)-5)),A2:A<>""))

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