查詢最接近的匹配

要在 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(MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4)),,ABS(C5:C10-F4)ABS(C5:C10-F4),0),0) = MATCH(MATCH(1113,,{4322;2451;6931;1113;6591;4782},0),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。

相關函數

相關公式