Excel:什么决定了公式中的评估顺序? [英] Excel: What determines evaluation order in a formula?
问题描述
我有一个工作表,在A1中有以下内容:G1
I have a worksheet with the following contents in 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首先被评估(下划线)。
To try to understand the issue, I use Formula Auditing -> Evaluate Formula. In the formulas that work (1 and 2), $A1 is evaluated (underlined) first. In the formula that doesn't work (3), $B1:$G1 is evaluated (underlined) first.
错误的原因是什么,和公式之间的不同行为?
What is the reason for the error, and the different behavior among formulas?
推荐答案
根据我的评论,要获得A1和B1之间的最小差异:G1使用数组输入公式,您可以使用 INDEX
来执行您尝试使用 SUMPRODUCT
,即
As per my comment, to get the smallest difference between A1 and B1:G1 without using an "array entered" formula you can use INDEX
to do what you were trying to do with SUMPRODUCT
, i.e.
= MIN(INDEX(ABS($ B1:$ G1- $ A1),0))
这篇关于Excel:什么决定了公式中的评估顺序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!