Google Sheets中用ARRAYFORMULA批量替换SUMPRODUCT公式的方法

需求背景 #

当前E列使用单个公式SUMPRODUCT(A2:A+D3>B2:B)/COUNTA(A2:A)计算巴萨与皇马的进球差概率，需要用一个公式自动填充E4:E8（对应D4:D8的数值），核心是要将D3:D10的数值批量融入数组运算。

解决方案1：用BYROW函数（直观易读） #

在E3单元格输入以下公式，会自动生成E3到E10的结果：

=BYROW(D3:D10, LAMBDA(d, SUMPRODUCT((A2:A + d > B2:B)*1)/COUNTA(A2:A)))

• BYROW(D3:D10, LAMBDA(d, ...))：逐个遍历D3到D10的每个数值，将当前数值命名为d
• (A2:A + d > B2:B)*1：把布尔判断结果转为1（满足条件）或0（不满足）
• SUMPRODUCT(...)：统计满足巴萨进球+d > 皇马进球的行数
• 最后除以A列的有效数据行数COUNTA(A2:A)得到概率

解决方案2：用ARRAYFORMULA+MMULT（纯数组运算） #

如果偏好使用ARRAYFORMULA，可以用矩阵乘法实现批量求和：

=ARRAYFORMULA(MMULT(N(A2:A+TRANSPOSE(D3:D10)>B2:B),SEQUENCE(ROWS(A2:A),1,1,0))/COUNTA(A2:A))

• TRANSPOSE(D3:D10)：将D列的纵向数组转为横向，和A列的纵向数组做广播运算，生成二维的布尔判断矩阵
• N(...)：把布尔值转为1/0的数值矩阵
• MMULT(..., SEQUENCE(...))：通过矩阵乘法对每一列求和，得到每个D值对应的满足条件的行数
• 除以总数得到对应概率

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