巧用Excel公式获取最接近平均值的数值

excelperfect

有时候，我们查找的数值并不在数据表中，但要返回数据表中相应的数值。例如，下图1所示，我们想要获取列B中最接近平均值的数值。

图1

很显然，平均值很容易算出，使用公式：

=AVERAGE(B2:B6)

返回结果：

21.8

因为数据较少，很容易找出与平均值21.8最接近的数值是23。那么，如何使用公式返回这个数值呢？

最接近平均值的数值与平均值之差一定是各数与平均值之差中最小的。

我们使用：

B2:B6-AVERAGE(B2:B6)

可以得到数组：

{-11.8;-6.8;1.2;23.2;-5.8}

取其绝对值后求最小值：

MIN(ABS({-11.8;-6.8;1.2;23.2;-5.8}))

返回：

1.2

即为最接近平均值的数值与平均值之差。

然后，查找该值的位置，也就是最接近平均值的数值所在的位置：

MATCH(MIN(ABS({-11.8;-6.8;1.2;23.2;-5.8})), ABS({-11.8;-6.8;1.2;23.2;-5.8}),0)

返回：

3

因此，得到最接近平均值的数值为：

INDEX(B2:B6,3)

返回：

23

综上，最后的公式为：

=INDEX(B2:B6, MATCH(MIN(ABS(B2:B6-AVERAGE(B2:B6))),ABS(B2:B6-AVERAGE(B2:B6)),0))

该公式是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。