☰

当范围只有 1 行且部分计算为空白时，Sumproduct 会失败

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
当范围只有 1 行且部分计算为空白时，Sumproduct 会失败

我有以下公式，当范围有多于 1 行时有效，但如果只有 1 行并且其中一个因子为空，则会生成 #VALUE 错误。

=SUMPRODUCT(cost, units)

Cost    Units
   1         1
   2         1

结果为 3。

Cost    Units
   1         
   2

结果为 0。

Cost    Units
   1         1

结果为 1。

Cost    Units
   1

結果是#VALUE。

我可以通过将公式包装在 IFERROR 中来防止出现错误：

=IFERROR((SUMPRODUCT(cost, units)), 0)

为什么 SUMPRODUCT 不能仅对一行起作用？有没有比 IFERROR 包装器更好的方法来处理这个问题？

答案1

尝试

=SUMPRODUCT(costs*units)

有趣的是，在评估公式工具中看到函数经历了这个阶段：

在此处输入图片描述

然后出现 #Value! 错误。但如果单元格包含实际公式

=SUMPRODUCT(1,0)

那么结果就是 0。这看起来像是一个错误。

另一种不出现错误的语法是

=SUMPRODUCT(--(costs),--(units))

编辑：经过进一步的研究和 Excel MVP 同事的反馈，我们解释了上述行为：在 Sumproduct 中，Excel 会将空单元格强制转换为零值，但前提是它是一个数组，即由多个单元格组成的区域。单个单元格区域不会触发强制转换，因此空单元格不会被强制转换为 0。

使用乘法运算符或双一元运算符也会将空单元格强制为零值。

这令人困惑并且有点恼人，但是如果输入是一个未与运算符或双一元运算符连接的单个空单元格，则该单个空单元格将按面值取值并将触发#Value！错误，因为它不是数字。

答案2

来自MS 文档页面

评论

  • 数组参数必须具有相同的维度。如果维度不一致，SUMPRODUCT 将返回错误值 #VALUE!。
  • SUMPRODUCT 将非数字的数组条目视为零。

相关内容