查詢最接近的配對值

若要在 Excel 的數值資料集中查詢與 lookup_value 最接近的配對值，可結合使用 INDEX、MATCH、ABS 與 MIN 函數。

如何在 Excel 中找出最接近的匹配值？

若要找出哪位 業務員的銷售金額最接近 $20,000 的目標值（如上所示），可透過結合 INDEX、MATCH、ABS 與 MIN 函數的公式輕鬆達成：ABS 函數會將每位業務員的實際銷售金額與銷售目標之間的差異全部轉為正數，MIN 函數隨即找出最小差異值（也就是最接近目標的配對項）；接著，MATCH 函數即可定位該配對項的位置，再由 INDEX 函數取出對應的業務員姓名或數值。

通用語法

=INDEX(）return_range,MATCH(MIN(ABS(）））lookup_array-lookup_value)),ABS(lookup_array-lookup_value),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:C 10-F4)),ABS(C5:C 10-F4),0))

• ABS(C5:C 10-F4)： 其中 C5:C 10-F4 會計算範圍 C5:C10 內每筆銷售金額與目標銷售金額 $20,000（位於儲存格 ）F4）之間的差異，並形成以下陣列：{——4322;2451;6931；-1113;6591；-4782}。接著，ABS 函數會將所有負數轉為正數，結果如下：{4322;2451;6931;1113;6591;4782}。
• MIN(）ABS(C5:C 10-F4))=MIN(）{4322;2451;6931;1113;6591;4782})：MIN 函數會從陣列 {4322;2451;6931;1113;6591;4782} 中找出最小數值，也就是最小差異值，即最接近的配對項。因此，該函數會傳回 1113.
• MATCH(）MIN(）ABS(C5:C 10-F4)),ABS(C5:C 10-F4) 參數 match_type 0 會強制 MATCH 函數在陣列 {4322;2451;6931;1113;6591;4782} 中精確找出數值 1113 的位置。該函數會傳回 4，因為該數值位於第 4 個位置。
• INDEX(）B5:B10,MATCH(）MIN(）ABS(C5:C 10-F4)),ABS(C5:C 10-F4)INDEX 函數會傳回名稱範圍 B5:B10 中的第 4 個值，也就是 Bale。

相關函數

相關公式