如何用单个ARRAYFORMULA实现Google Sheets整列数据计算

编辑说明：此前主表格链接错误指向图片而非示例工作簿，感谢@horanimal指出，现已修正链接。

问题背景 #

我在Google Sheets的【Main sheet】中使用ARRAYFORMULA，希望基于A列日期从Info表筛选数据填充整列。计算逻辑如下：

• 统计Info表中同时满足以下条件的匹配项数量：对应A列日期、匹配Main表B2和B3单元格数据、状态为PASS或REPAIRED
• 用该数量乘以Data表B4单元格数值
• 最后减去Info表中对应符合条件的数值总和

目前在B4单元格编写的公式向下复制可得到正确结果，但希望仅在B3单元格用单个公式处理整列。尝试改写的公式仅在C4返回首个结果，C5及以下单元格为空，求正确改写方式。

现有公式 #

B4单元格公式（可正常计算单个行）

=ARRAYFORMULA(COUNTA(IFERROR(FILTER(Info!$A$5:$5,Info!$A$2:$2=$A4,Info!$A$3:$3=$B$2,Info!$A$4:$4="➕",REGEXMATCH(Info!$A$6:$6,"PASS|REPAIRED"),REGEXMATCH(Info!$A$7:$7,"PASS|REPAIRED"))))*Data!$B$4 -SUM(IFERROR(FILTER(Info!$A$8:$8,Info!$A$2:$2=$A4,Info!$A$3:$3=$B$2,Info!$A$4:$4="➕",REGEXMATCH(Info!$A$6:$6,"PASS|REPAIRED"),REGEXMATCH(Info!$A$7:$7,"PASS|REPAIRED")))))

尝试的C3单元格公式（仅返回首个结果）

={"➕➕";ARRAYFORMULA(COUNTA(IFERROR(FILTER(Info!A$5:$5,Info!A$2:$2=$A4,Info!A$3:$3=$B$2,Info!A$4:$4="➕➕",REGEXMATCH(Info!A$6:$6,"PASS|REPAIRED"),REGEXMATCH(Info!A$7:$7,"PASS|REPAIRED"))))*Data!$B$4 -SUM(IFERROR(FILTER(Info!A$8:$8,Info!A$2:$2=$A4,Info!A$3:$3=$B$2,Info!A$4:$4="➕➕",REGEXMATCH(Info!A$6:$6,"PASS|REPAIRED"),REGEXMATCH(Info!A$7:$7,"PASS|REPAIRED")))))}

解决方案 #

问题核心是FILTER仅能返回单组匹配结果，无法与A列所有日期做数组级匹配。推荐使用BYROW遍历A列每个日期，逐个执行计算逻辑：

修正后的C3单元格公式

={"➕➕";BYROW(A4:A,LAMBDA(date, IF(date="",,
 COUNTA(IFERROR(FILTER(Info!$A$5:$5,Info!$A$2:$2=date,Info!$A$3:$3=$B$2,Info!$A$4:$4="➕➕",REGEXMATCH(Info!$A$6:$6,"PASS|REPAIRED"),REGEXMATCH(Info!$A$7:$7,"PASS|REPAIRED"))))*Data!$B$4 
 -SUM(IFERROR(FILTER(Info!$A$8:$8,Info!$A$2:$2=date,Info!$A$3:$3=$B$2,Info!$A$4:$4="➕➕",REGEXMATCH(Info!$A$6:$6,"PASS|REPAIRED"),REGEXMATCH(Info!$A$7:$7,"PASS|REPAIRED"))))
))}

优化高效版（用SUMPRODUCT减少重复筛选）

={"➕➕";BYROW(A4:A,LAMBDA(date, IF(date="",,
 SUMPRODUCT(--(Info!$A$2:$2=date), --(Info!$A$3:$3=$B$2), --(Info!$A$4:$4="➕➕"), --REGEXMATCH(Info!$A$6:$6,"PASS|REPAIRED"), --REGEXMATCH(Info!$A$7:$7,"PASS|REPAIRED"))*Data!$B$4 
 -SUMPRODUCT(Info!$A$8:$8, --(Info!$A$2:$2=date), --(Info!$A$3:$3=$B$2), --(Info!$A$4:$4="➕➕"), --REGEXMATCH(Info!$A$6:$6,"PASS|REPAIRED"), --REGEXMATCH(Info!$A$7:$7,"PASS|REPAIRED"))
))}

逻辑说明

• BYROW(A4:A, LAMBDA(date, ...))：遍历A4及以下的每个日期，将当前日期赋值给变量date
• IF(date="",, ...)：空日期单元格返回空，避免无效计算
• 高效版用SUMPRODUCT替代两次FILTER，通过布尔值转0/1的方式实现计数和数值求和，提升计算效率

内容的提问来源于stack exchange，提问作者raphaelsword