Question

求EXCEL公式解释什么意思=MAX((MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2))*$A$2:$A$21)

求EXCEL公式解释什么意思=MAX((MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2))*$A$2:$A$21)
按照步骤能否理解为
1、(MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2)) MIN是取出数值里面最小值可后面这个=号不明白号啥意思

2、(MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2))*$A$2:$A$21这一步不就是等于了以后乘以$A$2:$A$21嘛
3、MAX((MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2))*$A$2:$A$21)最后一步是取出最大值

Answer 1

该公式用于取出A2:A21中与I2最接近的数值。鉴定完毕。

追问

我得到的结果的确是与I2最接近的数值可是不明白为啥，最小值还的乘以A2：21的数值呢

Answer 2

{=MAX((MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2))*$A$2:$A$21)}

 

数组公式

 

--------------------------- 

如图数据； 

ABS($A$2:$A$21-$I2)

      返回数组  {8.6,7.6,6.6,5.6,4.6,3.6,2.6,1.6,0.6,0.4,1.4,2.4,3.4,4.4,5.4,6.4,7.4,8.4,9.4,10.4}

 

MIN(ABS($A$2:$A$21-$I2)) 

      返回数值    0.4

 

MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2)                  

      返回数组{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

 

(MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2))*$A$2:$A$21

      返回数组{0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0}

 

=MAX((MIN(ABS($A$2:$A$21-$I2))=ABS($A$2:$A$21-$I2))*$A$2:$A$21)

            返回数值 3

 

Answer 3

这个是数组公式，你可以用“工具/公式审核/公式求值”，连续按“求值”，看其计算过程来理解公式的意义。