如何用SUMPRODUCT计算含空值列的a_i*(b_i-5)乘积和？

问题核心 #

B列的空单元格在执行B1:B100 - 5运算时会生成#VALUE!错误，而SUMPRODUCT遇到错误值会直接返回错误，无法自动忽略空行，导致公式失效。

可行解决方案 #

以下是几种直接可用的公式写法：

1. 用IFERROR处理错误值
   把空值运算产生的错误转为0，确保SUMPRODUCT能正常计算：

   =SUMPRODUCT(A1:A100; IFERROR(B1:B100 - 5; 0))
   

   逻辑：IFERROR将B列空值减5的错误结果转为0，A列空行对应的值为空（转0），因此只有A、B列都有值的行会参与乘积求和。

2. 通过布尔数组筛选非空行
   用B1:B100<>""生成筛选数组，仅保留非空行的计算项：

   =SUMPRODUCT(A1:A100*(B1:B100<>""); (B1:B100 - 5)*(B1:B100<>""))
   

   逻辑：布尔值TRUE在运算中会转为1，FALSE转为0，空行的计算项会被置为0，不影响最终求和结果。

3. 基于A列判断重构公式
   利用B列非空行与A列非空行一一对应的关系，直接代入B列的生成公式：

   =SUMPRODUCT(IF(A1:A100<>"", A1:A100*(1/A1:A100^2 -5), 0))
   

   注意：旧版Excel需按Ctrl+Shift+Enter作为数组公式输入；新版Excel支持动态数组，可直接回车。

关于N函数的补充 #

你之前尝试的N函数未成功，是因为B1:B100 -5先产生错误，N函数无法处理错误值。调整写法后即可使用：

=SUMPRODUCT(A1:A100; N(IFERROR(B1:B100 -5; 0)))

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