☰

为什么此特定的 Offset 函数与 Sumproduct 结合时会返回值错误？

tag-icon • tag-icon microsoft-excel
为什么此特定的 Offset 函数与 Sumproduct 结合时会返回值错误？

我正在尝试编写一个公式，将每 3 列的值乘以 3。我尝试使用 Sumproduct 函数并以内部 Offset 函数作为计数器来实现此目的。

为什么 Offset 函数仅在低于其应偏移的数据范围时才返回值错误？参见下图，当它与数据范围相邻时，该函数正确返回值“3”，但当该函数粘贴在数据下方时返回错误。

此外，虽然 Offset 函数在写入数据范围旁边时可以正确返回一个值，但为什么将该函数与 Sumproduct 结合起来仍然会返回错误？

相关Excel工作表的截图

答案1

在最新版本的 Excel 中，所有这些公式都将起作用，因为此时OFFSET可以返回动态数组。

看来您使用的是较早版本的 Excel。我能够使用“@”运算符模拟您所看到的行为。

只要您引用的范围不包含您输入公式的行，此调用就会OFFSET返回错误。因此，您会在尝试计算的范围上方和下方都收到 #VALUE! 错误：#VALUE!

在此处输入图片描述

您还会发现，对于左侧或右侧的公式，如果将它们上下拖动，则只要公式超出或低于函数内引用的行限制，它们就会开始显示错误：

在此处输入图片描述

我猜想在早期版本的 Excel 中，他们试图避免将数组返回到可能与其他数据重叠的区域。因此，您返回的数组元素始终与参考区域/数组位于同一行。

至于问题SUMPRODUCT，它返回零，因为 OF​​FSET 正在产生错误，该错误被视为零。

不管怎样，数组维度必须相同，所以即使 OFFSET 在这里没有返回错误，SUMPRODUCT 也会返回，因为第二个参数中只有一个值。

一般来说，我认为如果您的 Excel 版本不支持动态数组，您应该尝试提出一种不以这种方式使用偏移的方法。如果您编辑帖子以包含有关预期结果和目的的更多详细信息，我可以尝试提供帮助。

答案2

OFFSET似乎不会生成SUMPRODUCT可以使用的数组。当使用 内部计算公式艺术时会生成F9，但是...当 Excel 以任何方式执行时不会生成。

但是，OFFSET正如所见，IS 会在内部生成一个数组F9。需要做的就是强制（“强迫”）Excel 使用它。

用它包装INDEX（如INDEX(OFFSET(...),,1)捕获所有行）可以防止 Excel 丢失OFFSET数组。

然后，当然，正如FlexYourData指出的那样，必须匹配数组结构，以便简单地SEQUENCE(ROWS($I$4:$I$22),1,3,0)完成公式。

如果使用旧版本，最后一个数组的构建方式不同，但很容易。因此，对于旧版本，唯一真正的问题是是否SUMPRODUCT会使用这些部分。从结构上讲，没有问题，因为它对于普通的“Enter”输入来说是正确形成的，但如果不是，则始终有“Control-Shift-Enter”（“{CSE}”）。唯一的问题是它是否采用数组或将它们切割为一个值（=9）。但像这样的函数SUMPRODUCT是专门为此而编写的，所以我相信它应该可以正常工作。

（当然，您似乎想要一列 19 个条目，因此选择单元格并使用“{CSE}”条目似乎是明智的。）

相关内容