SUM（）和SUMPRODUCT（）中的ROW（）函数的行为不同， [英] ROW() function behaves differently inside SUM() and SUMPRODUCT()

问题描述

问题定义

在单元格 A1 中输入任意数字。现在在第一行的任何地方尝试以下公式。

  = SUM（INDIRECT（A& ROW（）））
  

和

  = SUMPRODUCT（INDIRECT（A& ROW（）））
  

评估，第二个给出#VALUE错误。
这是由 ROW（）在 SUMPRODUCT（）中的行为不同引起的。

在第一个公式中， ROW（）返回 1 。在第二个公式中，行返回 {1} （一个长度的数组），即使公式未输入为CSE公式。

为什么会这样？

背景

我需要评估类型的公式

  = SUMPRODUCT（INDIRECT（* range by concatenation and using ROW（）* ）> 1）
  

这是一个错误。作为此问题的解决方法，我现在在另一个单元格（在同一行显然）中计算 ROW（）并连接在我的 INDIRECT（ ）。或者，我也尝试将它封装在一个sum函数中，像 SUM（ROW（）），这样也是一样。

如果有人可以解释（或指向一个可以解释的资源），为什么 ROW（）返回 SUMPRODUCT（）而不输入CSE。

解决方案

有趣的问题。这里有一些我没有看到的细微问题。

似乎 INDIRECT（A& ROW（））返回一个由一个元素组成的数组，该元素是对单元格的引用，而不是该单元格中的值。许多函数无法正确解析这种类型的数据，但N和T等几个函数可以取消引用数组并返回底层值。

在这种情况下，数组中有两个元素：

  = SUM（N（INDIRECT（A& ROW（1：2））））
  

当数组输入时，返回A1 + A2，但在正常输入时只返回A1。但是，当正常输入时，该公式中的ROW（1：2）更改为{1; 2}返回正确的结果。无论数组是否输入，等效的SUMPRODUCT公式返回A1 + A2。

这可能与参数在函数中的注册有关。根据 http://msdn.microsoft.com/en-us/library/bb687900 .aspx 基本上有两种注册函数参数来处理Excel数据类型的方法：

类型R / U：值，数组和范围引用。

类型P / Q：准备这些参数时，Excel将单细胞引用转换为简单值和多单元格引用到数组。

SUM参数似乎符合R / U类型，而SUMPRODUCT参数的行为类似于P / Q。数组输入上面的SUM公式会强制将ROW中的范围引用参数作为数组进行求值，而SUMPRODUCT会自动执行。

更新

经过多一点调查，进一步证明可能支持这一理论。根据评论中的链接，公式= SUM（（A1，A2））给出的值相同：

 ？ executeexcel4macro（CALL（Xlcall32Excel42JRJR，4，1，（！R1C1，！R2C1）））
  / pre> 
 
 
通过将 2JRJR 更改为 2JRJP 将最后一个参数注册为P类型code>在这种情况下给出错误，但允许单个区域范围，如！R1C1：！R2C1 。另一方面，将4（xlfsum）更改为228（xlfsumproduct）仅允许单个区域引用，就像SUMPRODUCT一样调用它。
 
Problem definition:

Enter any number in cell A1. Now try the following formulae anywhere on first row.
=SUM(INDIRECT("A"&ROW()))
and
=SUMPRODUCT(INDIRECT("A"&ROW()))
The first formula evaluates, the second one gives a #VALUE error.
This is caused by the ROW() function behaving differently inside SUMPRODUCT().

In the first formula, ROW() returns 1. In the second formula, row returns {1} (array of one length), even though the formula has not been entered as a CSE formula.

Why does this happen?

Background

I need to evaluate a formula of the type 
=SUMPRODUCT(INDIRECT(*range formed by concatenation and using ROW()*)>1)
This is working out to an error. As a workaround to this issue, I now calculate ROW() in another cell (in the same row, obviously) and concatenate that inside my INDIRECT(). Alternately, I also have tried encapsulating it inside a sum function, like SUM(ROW()), and that works as well.

I would sure appreciate it if someone could explain (or point me to a resource that can explain) why ROW() returns an array inside SUMPRODUCT() without being CSE entered.
解决方案
Interesting question. There are subtle issues here which I haven't seen documented.

It seems INDIRECT("A"&ROW()) returns an array consisting of one element which is a reference to a cell - not the value in that cell. Many functions cannot resolve this type of data correctly but a few functions such as N and T can "dereference" the array and return the underlying value. 

Take this case where there are two elements in the array:
=SUM(N(INDIRECT("A"&ROW(1:2))))
This returns A1+A2 when array entered but it only returns A1 when entered normally. However changing ROW(1:2) to {1;2} in this formula returns the correct result when entered normally. The equivalent SUMPRODUCT formula returns A1+A2 whether array entered or not.

This may be related to how the arguments are registered in the function. According to http://msdn.microsoft.com/en-us/library/bb687900.aspx there are essentially two methods to register function arguments to handle Excel data types: 

Type R/U: "Values, arrays, and range references."

Type P/Q: "Excel converts single-cell references to simple values and multi-cell references to arrays when preparing these arguments."

SUM arguments seem to conform with type R/U while SUMPRODUCT arguments behave like type P/Q. Array-entering the SUM formula above forces the range reference argument in ROW to be evaluated as an array whereas this happens automatically with SUMPRODUCT.

Update

After a little more investigation, here's further evidence that might support this theory. Based on the link in the comment, the formula =SUM((A1,A2)) gives the same values as:
?executeexcel4macro("CALL(""Xlcall32"",""Excel4"",""2JRJR"",4,,1,(!R1C1,!R2C1))")
Registering the last argument as type P by changing 2JRJR to 2JRJP gives an error in this case but does allow for single area ranges like !R1C1:!R2C1. On the other hand, changing the 4 (xlfsum) to 228 (xlfsumproduct) only allows single area references either way it's called just like SUMPRODUCT.

                        
这篇关于SUM（）和SUMPRODUCT（）中的ROW（）函数的行为不同，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！