ROW() 函数在 SUM() 和 SUMPRODUCT() 中的行为不同

问题定义：

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

=SUM(INDIRECT("A"&ROW()))

and

=SUMPRODUCT(INDIRECT("A"&ROW()))

第一个公式进行计算，第二个公式给出 #VALUE 错误。 这是由于ROW()函数内部表现不同SUMPRODUCT().

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

为什么会出现这种情况？

背景

我需要评估该类型的公式

=SUMPRODUCT(INDIRECT(*range formed 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 http://msdn.microsoft.com/en-us/library/bb687900.aspx本质上有两种注册函数参数来处理 Excel 数据类型的方法：

输入 R/U：“值、数组和范围引用。”

输入 P/Q：“在准备这些参数时，Excel 将单单元格引用转换为简单值，将多单元格引用转换为数组。”

SUM 参数似乎符合 R/U 类型，而 SUMPRODUCT 参数的行为类似于 P/Q 类型。输入上面的 SUM 公式的数组会强制将 ROW 中的范围引用参数计算为数组，而使用 SUMPRODUCT 时会自动发生这种情况。

Update

经过更多调查后，这里有可能支持这一理论的进一步证据。根据评论中的链接，公式 =SUM((A1,A2)) 给出与以下相同的值：

?executeexcel4macro("CALL(""Xlcall32"",""Excel4"",""2JRJR"",4,,1,(!R1C1,!R2C1))")

通过更改将最后一个参数注册为类型 P2JRJR to 2JRJP在这种情况下会出现错误，但确实允许单个区域范围，例如!R1C1:!R2C1。另一方面，将 4 (xlf sum) 更改为 228 (xlf sumproduct) 只允许单个区域引用，无论采用哪种方式调用，就像 SUMPRODUCT 一样。