如何区分sumproduct是否需要插入CSE? [英] How to distinguish if sumproduct needs to be inserted with CSE or not?
问题描述
我有时会使用 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))不会.
这对我来说并不是很明显，为什么第一个给出了正确的结果，而第二个却没有.
好问题.我的经验法则 > 你看到 IF 和一个要分析的范围 > 你按下 CSE.
为什么?一些函数为您本地处理 CSE(SUMPRODUCT 是其中之一)，但其他函数则不会，例如 SUM 但肯定也IF.看看
我从 A2:C2 范围创建了一个名为 Vals 的命名范围.现在B5 中的公式只是=Vals 但结果是Val5.含义隐式交集返回了与我在其中输入公式的列相交的命名范围中的值.
为什么?因为在后台(看不见的)Excel 使用隐式交集运算符(@")从刚刚提到的交集返回单个值.我会使用 CSE(读取，删除逻辑运算符)，返回的值将是 Val2(数组中的左上角值).
隐式交集逻辑将许多值减少为单个值.Excel 这样做是为了强制公式返回单个值，因为单元格只能包含一个值."
逻辑运算符@"将阻止返回数组并确保返回单个值.删除此逻辑运算符(我们通过按 CSE 或使用本机执行此操作的函数来执行此操作)将使公式返回数组.
您可能不知道/不知道这个运算符，但是随着动态数组公式的出现，它们将更多地出现在您的公式中.请参阅此关于此事的 MS 文档.使用这些新功能，删除逻辑运算符不仅会返回数组，而且实际上会将值溢出到相邻的单元格.因此，术语动态数组公式".因此，您可以将新的动态数组公式视为 自动替代 旧版 CSE 公式，并增加了溢出功能等.
<小时>总结:
你的第二个公式也可以写成:
=@SUMPRODUCT(--@IF(@ISNUMBER(N6:N9),N6:N9))按 Enter 不起作用，因为只有 SUMPRODUCT 本机取消了(看不见的)逻辑运算符，而 IF 只需要一个标量(单个) 价值.所以，看不见但有效，你的公式看起来像:
=SUMPRODUCT(--@IF(@ISNUMBER(N6:N9),N6:N9))但是，按 Control + Shift + Enter 确实会排除逻辑运算符并有效地使您的公式看起来像:
=SUMPRODUCT(--IF(ISNUMBER(N6:N9),N6:N9))因此能够接受数组.希望这能说明为什么您需要使用第二个 IF 公式按 CSE.
有趣的事实: 下次，尝试编写 =@SUMPRODUCT(... 或 =@IF(....您会注意到公式被接受，但逻辑运算符消失了.这个运算符在后台使用的标志=)
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)
would work, while
=SUMPRODUCT(--IF(ISNUMBER(N6:N9),N6:N9))
would not.
It isn't really obvious to me, why the first one gives correct results, while the second does not.
Nice question. Rule of thumb for me > You see IF and a range to be analized > you press 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:
"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."
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: Some additional information on the behaviour called "implicit intersection".
Let's imagine the following data:
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.
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).
"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."
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.
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))
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))
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))
And thus being able to take arrays. Hopefully that clarified why you needed to press CSE with your second IF formula.
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屋！
