查找最接近的匹配项

要在Excel中的数值数据集中查找查找值的最接近匹配项，可以结合使用INDEX、MATCH、ABS， 和MIN函数。

如何在Excel中找到最接近的匹配项？

要知道哪个销售人员的销售额最接近20,000美元的目标（如上图所示），一个结合了INDEX、MATCH、ABS和MIN函数的公式将帮助您实现这一目标：ABS函数会将每个销售人员的销售额与销售目标之间的所有差值转为正值，然后MIN函数将找到最小的差值，这意味着最接近的匹配项。现在我们可以使用MATCH函数定位最接近匹配项的位置，并用INDEX函数检索相应位置的值。

通用语法

=INDEX(返回范围,MATCH(MIN(ABS(查找范围-查找值)),ABS(查找范围-查找值),0))

√ 注意：这是一个数组公式，需要您使用Ctrl + Shift + Enter输入。

要知道哪个销售人员的销售额最接近20,000美元的目标，请复制或在F5单元格中输入以下公式，并按Ctrl + Shift + Enter获取结果：

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-20000)),ABS(C5:C10-20000),0))

或者，使用单元格引用来使公式动态化：

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

公式的解释

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

• ABS(C5:C10-F4): 部分C5:C10-F4将获得C5:C10范围内每个销售额与单元格F4中的目标销售额20,000美元之间的所有差值，形成如下数组：{-4322;2451;6931;-1113;6591;-4782}。ABS函数将把所有负数转换为正数，结果如下：{4322;2451;6931;1113;6591;4782}。
• MIN(MIN(ABS(C5:C10-F4))) = MIN(MIN({4322;2451;6931;1113;6591;4782})): MIN函数将从数组{4322;2451;6931;1113;6591;4782}中找到最小的数字，这意味着最小的差异，或者说是最接近的匹配项。因此，该函数将返回1113。
• MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0) = MATCH(1113,{4322;2451;6931;1113;6591;4782},0): 匹配类型0强制MATCH函数在数组{4322;2451;6931;1113;6591;4782}中找到确切数字1113的位置。由于该数字位于第4个位置，因此函数将返回4。
• INDEX(B5:B10B5:B10,,,MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4),0))) = INDEX(B5:B10B5:B10,,,4): INDEX函数返回名称范围B5:B10中的第4个值，即Bale。

相关函数

相关公式