在 SUM() 中使用时，将范围与值进行比较不会产生数组

Question

Excel 中的某些SUM()函数我不太明白。

我可以对一个范围求和，像这样：=SUM(A2:A4)。

我可以对范围列表求和：=SUM(A2, A3:A4)。

我可以对数组求和：（=SUM({1, 2, 3})返回6）。

我可以通过将元素强制转换为数字来对非数字布尔值数组求和，如下所示：

=SUM(-{TRUE,TRUE,TRUE})（返回-3）。

但，下面的操作不起作用，我不知道为什么：

=SUM(--(A2:A7>0.5))

经过不起作用我的意思是结果要么是错误消息，0要么是1。我期望结果像函数一样工作COUNTIF()，结果将报告A2:A7值 > 0.5 的实例数。

我的理解是--(A2:A7>0.5)应该返回一个数组，或者我是这样认为的。

例如，使用SUMPRODUCT()接受范围或数组的，我可以执行=SUMPRODUCT(A2:A4, B2:B4, --(A2:A4>0.5))，结果将是列中的值A> 0.5 的那些列的总和。

我知道，SUMIFS()但我并不是在寻找解决方案。我只是好奇，想了解一下类似的东西--(a2:a7>0.5)实际上返回了什么，以及为什么SUM()函数无法处理它。

Answer 1

这一切都源于您在公式中使用多单元格区域时发生的情况，而公式中需要单个单元格/值。当您这样做时，如果该区域是一列（或其中的一部分），Excel 将使用多单元格区域内与输入公式的单元格的行相对应的单个单元格；如果该区域是一行（或其中的一部分），Excel 将使用与公式单元格的列相对应的单元格。确实如此不是返回一个数组。

如果没有相应的行/列或者提供了二维范围，则#VALUE!使用。

因此，例如，如果您=--(A2:A7>0.5)在 中输入公式C5，Excel 会将其计算为=--(A5>0.5)，因为比较通常采用单个值，而您提供了多单元格区域。如果您在 中输入公式A9，则它会将其计算为=--(#VALUE!>0.5)。

现在，自从SUM()可以采用多单元格区域/数组或者单个单元格/值作为参数，再加上比较通常在单个值之间进行的事实，它们会返回单个值，Excel 会将其计算=SUM(--(A2:A7>0.5))为（当然，=SUM(--(A5>0.5))在 中的任何地方输入时）。$5:$5

这就是为什么如果在 中的任何位置输入公式，则会得到0或1（或如果 中的相应单元格包含错误，则会出现错误） ，否则会出现错误（因为计算结果为）。$A:$A$2:$7#VALUE!=SUM(--(#VALUE!>0.5))#VALUE!

之所以=SUMPRODUCT(A2:A4, B2:B4, --(A2:A4>0.5))有效，是因为SUMPRODUCT()它被设计用于处理多单元格区域/数组参数。（它将要尽管如此，仍然可以使用单个单元格/值/单值数组。）因此，它将比较视为具有数组结果的多值比较。

---

有两种方法可以SUM()按照所需的方式完成工作：

1.INDEX()方法

此方法使用的事实是，INDEX()需要多单元格范围作为第一个参数，并且（或我更喜欢的）返回整个数组。因此，中的比较是多值的，并且可以按预期工作。 INDEX(array,0)INDEX(array,)INDEX(--(A2:A7>0.5),)=SUM(INDEX(--(A2:A7>0.5),))

2. 数组公式法

=SUM(--(A2:A7>0.5))通过将公式输入为数组公式，可以强制将比较运算符和其他运算符视为多值。这是使用++而Ctrl不是仅在输入公式时完成的。ShiftEnterEnter

Answer 2

确实 =(a2:a7>0.5)会返回一个值数组。

如果单元格包含错误，则数组元素将是TRUE或或错误。FALSE

--(a2:a7>0.5)将返回一个数组1，0或者错误（如果适用）。

sum(--(a2:a7>0.5)，如果正常输入，将根据公式相对于 a2:a7 的位置返回不同的值。该值将是1、0、数组中的错误消息或错误#VALUE!。

sum(--(a2:a7>0.5)当以数组公式形式输入时，按住ctrl+shift并点击enter，将显示数组的总和。但是，如果数组包含错误，则公式将返回错误。