excel - 如何判断sumproduct是否需要用CSE插入？

Question

我有时使用 sumproduct 固有的数组功能来避免必须使用 Control + Shift + Enter 输入公式。但它并不总是有效。例如

=SUMPRODUCT((LEN(B2:F2)-LEN(SUBSTITUTE(B2:F2,M$2:M$10,"")))*N$2:N$10)

可以工作，同时

=SUMPRODUCT(--IF(ISNUMBER(N6:N9),N6:N9))

不会。

对我来说，这并不是很明显，为什么第一个给出了正确的结果，而第二个却没有。

Answer 1

问得好。我的经验法则 > 您会看到 IF 和要分析的范围 > 您按 CSE。

为什么？有些函数可以在 native 为您处理 CSE( SUMPRODUCT 就是其中之一)，但其他函数则不然，例如 SUM，但肯定还有 IF。看看 here和 here 。该理论的底线(AFAIK)是 CSE 将禁用称为“隐式交集”的功能，对此进行了解释 here 。归结为:

"Implicit intersection occurs when a range is passed to a function that expects a scalar (single) value. In this situation, Excel will try to resolve the formula using a reference in the same row, or in the same column......Entering an array formula with Control + Shift + Enter (CSE) explicitly disables the implicit intersection behavior. This makes it possible to create formulas that manipulate multiple values input as ranges."

因为您使用 IF，所以它在 SUMPRODUCT 内并不重要。您仍然需要按 CSE 来禁用使用 IF 附带的 native “隐式交集”。

<小时/>

FWIW:有关称为“隐式交集”的行为的一些附加信息。

让我们想象一下以下数据:

我从 A2:C2 范围创建了一个名为 Vals 的命名范围。现在，B5 中的公式只是 =Vals，但结果是 Val5。这意味着隐式交集返回了与我在其中输入公式的列相交的命名范围中的值。

为什么？因为在后台(看不见)Excel 使用隐式交集运算符(“@”)从刚才提到的交集返回单个值。我是否使用 CSE(读取，删除逻辑运算符)，返回的值将是 Val2 (数组中左上角的值)。

"Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value."

逻辑运算符“@”将阻止返回数组并确保您将返回单个值。删除这个逻辑运算符(我们通过按 CSE 或使用 native 执行此操作的函数来执行此操作)将使公式返回数组。

您可能看不到/不知道这个运算符，但随着动态数组公式的出现，它们将更多地出现在您的公式中。请参阅this关于此事的 MS 文档。有了这些新功能，删除逻辑运算符不仅会返回数组，而且实际上会将值溢出到相邻单元格。因此出现了术语“动态数组公式”。因此，您可以将新的动态数组公式视为 automated alternative用于旧版 CSE 公式，并添加溢出功能等。

<小时/>

总结一下:

你的第二个公式也可以写成:

=@SUMPRODUCT(--@IF(@ISNUMBER(N6:N9),N6:N9))

按 Enter 不起作用，因为只有 SUMPRODUCT 本身会取消(看不见的)逻辑运算符，而 IF 仅需要一个标量(单个) 值(value)。因此，虽然看不见但有效，您的公式如下所示:

=SUMPRODUCT(--@IF(@ISNUMBER(N6:N9),N6:N9))

但是，按 Control + Shift + Enter 确实会排除逻辑运算符，并有效地使您的公式看起来像这样:

=SUMPRODUCT(--IF(ISNUMBER(N6:N9),N6:N9))

因此能够获取数组。希望这能澄清为什么您需要使用第二个 IF 公式按 CSE。

<小时/>

_{有趣的事实: 下次，尝试编写 =@SUMPRODUCT(... 或 =@IF(...)。您会注意到公式已被接受，但逻辑运算符消失。说明该运算符在后台使用的标志 =)}