Excel：公式中的计算顺序由什么决定？

Question

我有一个工作表，A1:G1单元格内的内容如下：

7  8  4  2  9  11  10

公式

=SUMPRODUCT(MIN($B1:$G1-$A1)) (1)

评估为-5。

=SUMPRODUCT(ABS($B1:$G1-$A1)) (2)

计算结果为18。但是

=SUMPRODUCT(MIN(ABS($B1:$G1-$A1))) (3)

出现了#VALUE!错误。

为了理解问题，我使用公式审计->评估公式。 在工作的公式（1和2）中，首先评估$A1（下划线）。 在不起作用的公式（3）中，首先评估$B1:$G1（下划线）。

错误的原因是什么？这些公式之间的不同行为是什么？

- sancho.s ReinstateMonicaCellio

---

我喜欢这个问题。查找时，我发现了这个并不完美但可能会有用的解决方案：http://office.microsoft.com/zh-cn/excel-help/overview-of-formulas-HP010081865.aspx - user2299169

对于第三个公式，当公式在A列中时，我可以复制它，但如果我向右填充，每个单元格中都会看到不同的结果，这非常奇怪，因为公式并没有改变。 - mr.Reband

@mr.Reband 不错的发现！实际上，我最初是在O1中输入了我的公式。我会在这里查找。不过，我觉得有趣的是：1）评估顺序不同，2）类似地向右填充公式（1）会给出一个恒定的结果，而不是公式（3）。 - sancho.s ReinstateMonicaCellio

@Mark：我在你提供的链接中没有找到解决方案的提示。你是指像John的答案中那样将公式输入为数组吗？ - sancho.s ReinstateMonicaCellio

@Mark - 点已澄清 :) - sancho.s ReinstateMonicaCellio

Answer 1

根据我的评论，如果不使用“数组公式”来获取A1和B1:G1之间的最小差异，您可以使用INDEX来完成您试图使用SUMPRODUCT完成的操作，即： =MIN(INDEX(ABS($B1:$G1-$A1),0))

- barry houdini

Answer 2

看起来您正在使用SUMPRODUCT函数将此公式作为数组公式工作，但Excel没有将第三个公式计算为数组，导致在输入到A列中时出现#VALUE错误。在B至G列中，它没有给出这个错误，但也没有作为数组计算。通过在输入公式后按下Shift+Ctrl+Enter将公式输入为数组公式即可解决此问题。您还可以使用更简单的公式获得相同的结果：

=MIN(ABS($B1:$G1-$A1))

一旦将此作为数组公式输入，您就可以逐步进行评估并看到其正确运行。有关数组的更多信息，请查看这里：http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx。

- John

---

谢谢指点。我知道将其输入为数组公式可以解决问题，但我的意思是要避免这种情况。这就是引入“SUMPRODUCT”的原因，我不明白为什么它只在某些情况下起作用。这个问题的目的是特别理解这一点，而不是寻找达到预期结果的替代方法。 - sancho.s ReinstateMonicaCellio

+1，优秀的回答 - SUMPRODUCT并不总是能够神奇地将需要数组输入的公式转换为不需要的公式，我认为ABS需要CSE，而TRANSPOSE也属于同一类别。 - barry houdini

@sancho.s，你的问题不是评估顺序，而是Excel评估的方式（数组与常规函数）。解决方法是强制Excel将其评估为数组，而不是通过在数组函数内嵌套非数组函数来决定如何评估（SUMPRODUCT是一个数组函数）。有趣的是，当作为数组和常规函数进行评估时，Excel会翻转评估顺序。我以前从未注意到这一点。 - John

文档说明 ABS 接受一个数字作为输入，MIN 接受任意数量的数字，而 SUMPRODUCT 接受任意数量的数组。当 ABS 嵌套得太深时，它似乎默认采用数字并无法返回数组。 - Brad

很好的观点Brad，为了抵消这一点，我们可以使用INDEX round ABS，获得正确的结果，而不需要"array entry"和SUMPRODUCT，即=MIN(INDEX(ABS($B1:$G1-$A1),0))。 - barry houdini

Answer 3

总结了Brad和barry houdini（最初来自此）的评论：

文档说ABS以数字作为输入，MIN以任意数量的数字作为输入，SUMPRODUCT以任意数量的数组作为输入。当ABS嵌套得太深时，它默认采用数字，并且无法确定如何返回数组。

因此，我们可以使用INDEX将ABS四舍五入，并在没有“数组输入”和SUMPRODUCT的情况下获得正确的结果，即=MIN(INDEX(ABS($B1:$G1-$A1),0))。

这展示了输入公式的正确方法，并解释了错误的原因。

- sancho.s ReinstateMonicaCellio

Answer 4

我认为您可以使用“公式> 评估公式”来调查此问题。

这些结果适用于典型的数学运算：函数从内向外计算。

因为=SUMPRODUCT(MIN(ABS($B1:$G1-$A1)))没有被强制作为数组进行计算，所以$B1:$G1将返回该数组中与调用单元格位于同一列的值。例如，如果B2 =，则$B1:$G1将返回B1，如果A2= $B1:$G1，则它将尝试返回A1，但是没有要返回的内容，因此会出现#VALUE错误。

- Brad

---

我承认我没有完全理解你的回答（我会考虑一下）。同时，你认为它是否解释了仅使用MIN或ABS中的一个时与两者同时使用时不同的行为？ - sancho.s ReinstateMonicaCellio