如何区分sumproduct是否需要与CSE一起插入? [英] How to distinguish if sumproduct needs to be inserted with CSE or not?

问题描述

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

I sometimes use the inherent array functionality of sumproduct to avoid havingt to enter formulas with Control + Shift + Enter. But it isn't always working. For example

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

可以，而

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

不会.

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

It isn't really obvious to me, why the first one gives correct results, while the second does not.

推荐答案

很好的问题.我的经验法则>您看到IF和要分析的范围>按CSE.

Nice question. Rule of thumb for me > You see IF and a range to be analized > you press CSE.

为什么?有些功能可以为您本地处理CSE(SUMPRODUCT是其中的一种)，但其他功能则不能，例如SUM，但肯定也可以使用IF.在此处和此处.该理论(AFAIK)的底线是CSE将禁用称为隐式交集"的内容，这在此处.归结为:

Why? Some functions handle CSE natively for you (SUMPRODUCT being one of them), but others do not, for example SUM but definately also IF. Have a look here and here. Bottom line of the theory (AFAIK) is that CSE will disable something called "implicit intersection" which is explained here. It comes down to:

当将范围传递给期望标量(单个)值的函数时，发生隐式交集. 在这种情况下，Excel会尝试使用 在同一行或同一列中引用...输入 具有 Control + Shift + Enter (CSE)的数组公式明确禁用 隐式相交行为.这样就可以创建 用于将多个值输入为范围的公式."

"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随附的本机隐式交叉点".

Because you use IF, it doesn't matter it's within SUMPRODUCT. You'll still need to press CSE to disable the native "implicit intersection" that comes with using IF.

FWIW :有关行为的一些附加信息，称为隐式交点".

FWIW: Some additional information on the behaviour called "implicit intersection".

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

Let's imagine the following data:

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

I created a named range called Vals from the range A2:C2. Now the formula in B5 is simply =Vals but the result is Val5. Meaning implicit intersection returned the value from my named range that intersected with the column I entered the formula in.

为什么?因为在后台(看不见)，Excel使用隐式交集运算符("@")从上述交集返回单个值.我将使用CSE(读取，除去逻辑运算符)，返回的值将为Val2(数组左上角的值).

Why? Because in the background (unseen) Excel used the implicit intersection operator ("@") to return a single value from the intersection just mentioned. Would I use CSE (read, removing the logical operator), the value returned would be Val2 (top-left value in the array).

隐式交叉逻辑将许多值减少为一个值.Excel这样做是为了强制公式返回单个值，因为 单元格只能包含一个值."

"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或使用本机执行的函数来执行此操作)将使公式返回数组.

The logical operator "@" will prevent the return of an array and makes sure you'll get a single value returned. Removing this logical operator (is what we do by pressing CSE, or by using functions that do so natively) will make the formula return the array.

您可能看不到/不知道此运算符，但是随着动态数组公式的出现，它们将更多地出现在您的公式中.参见此有关此事的MS文档.使用这些新功能，删除逻辑运算符不仅会返回数组，而且实际上会将值溢出到相邻单元格中.因此，术语动态数组公式".因此，您可以将新的动态数组公式视为

You may not see/know about this operator but with the comming of dynamic array formulas they will be in your formulas a lot more. See this MS-documentation on the matter. With those new functionalities, removing the logical operator will not only return the array, it will actually spill the values to neighboring cells. Hence the term "Dynamic array formulas". So you can see the new dynamic array formulas as an automated alternative for legacy CSE-Formulas with the addition to have a spill function amongst others.

总结一下:

So to conclude:

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

Your second formula could also be written:

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

按 Enter 无效，因为只有SUMPRODUCT会自然地取消(看不见的)逻辑运算符，而IF仅需要标量(单个)值.因此，看不见但有效的公式如下:

Pressing Enter does not work because only SUMPRODUCT natively cancels out the (unseen) logical operator, while IF only expects a scalar (single) value. So, unseen but effectively, your formula looks like:

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

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

However, pressing Control + Shift + Enter will indeed rule out the logical operator and effectively make your formula look like:

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

因此可以获取数组.希望这阐明了为什么需要使用第二个IF公式按CSE.

And thus being able to take arrays. Hopefully that clarified why you needed to press CSE with your second IF formula.

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

_{Fun fact: Next time, try to write =@SUMPRODUCT(... or =@IF(.... You'll notice that the formula is accepted but the logical operator disappears. A sign that this operator is used in the background =)}

这篇关于如何区分sumproduct是否需要与CSE一起插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！