如何在 Excel 中找到最接近或最鄰近的值？

假設您在 B 欄有一串數字，需要判斷哪個值最接近給定數字（如18）。在 Excel 中透過陣列公式，您可以有效率地找出這個數值，無需手動逐一比對。

首先，請選取一個空白儲存格，並輸入下列公式。輸入完畢後，務必按下 Ctrl + Shift + Enter，而不是單獨按下 Enter。這樣才能正確執行陣列公式，確保功能運作無誤：

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

此方法最適合僅需從連續區域取回單一最接近值時使用，當數值精度及確切吻合十分重要時格外適用。不過要注意，陣列公式在數據量非常大時可能造成資源使用偏高。如遇到效能問題或 #VALUE! 等錯誤訊息，請再次確認單元格參照是否正確，並確實透過 Ctrl + Shift + Enter 輸入公式。

有時候，您不僅需找出單一最接近的值，還希望選取所有落在目標值某個特定範圍（即偏差範圍）內的數字。Kutools for Excel 提供「選擇指定單元格」功能允許您快速選出與目標值相差指定數值以內的所有數字。

例如，假設您的目標值為18，並設定偏差值為2，這表示您希望選取區域內所有介於16（18–2）到20（18+2）之間的數值。以下是詳細操作步驟：

1. 選取您要查找的區域（如 B3:B22），然後前往 Kutools > 選擇 > 選擇指定單元格。

2. 在「選擇指定單元格」對話視窗中：

• 於「選擇類型」下選擇「單元格」。
• 在 指定類型:
  - 首先將第一個下拉選單設為 大於或等於 並於方框中輸入 16 。
  - 將第二個下拉選單設為 小於或等於 並於方框中輸入 20.

3。按一下 確定 執行操作。Kutools會顯示符合條件的儲存格數量，並將所有處於指定偏差範圍內的值以選取項目的背景色標示，如下圖所示：

這個方法非常適合大量尋找鄰近值，尤其對於需考慮多筆數據且容忍範圍變動的情境。請注意選取精準度主要取決於偏差設定——偏差太小可能漏掉相關數據，太大則可能包含不希望出現的數值。

若需自動化處理，或必須在多個工作表及大量數據間根據數值或文字資料來搜尋最接近項目，VBA 巨集提供一個高效率且靈活的解法。透過程式化的方式自動比對目標值與所有候選者的差異，不僅能取回數字資料，也可依字串距離找出最近符串。

此方式尤其適用於需要整合自動化的大範圍數據，或執行重複性任務時。不過需注意，執行 VBA 巨集前必須啟用巨集，且建議具備 VBA 基礎操作知識。為避免資料遺失，執行巨集前請務必備份您的檔案。

1. 點擊「開發人員 」 >「Visual Basic」。在 Microsoft Visual Basic for Applications 視窗中，點選「插入」>「模組」，並將下列程式碼複製進模組中：

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. 接著回到您的工作表，在空白儲存格輸入公式：=FindClosest(B3:B22, E2)。按下 Enter 鍵即可得到最接近的值。

在檢視或展示資料時，無須進行篩選或排序，只需直觀辨識哪些值最接近目標數值。Excel內建「條件格式化」可協助您將最鄰近目標值的儲存格突出顯示，使其一目了然。雖然這種方式無法直接返回數值本身，但長於快速資料分析與視覺強調。

這種做法的最大優點是非破壞性、動態標示：只要資料或目標值變更，強調效果便會即時更新。特別適用於儀表板、簡報與需加強可見度的複查情境。若多個值與目標距離相同，標示效果可會較不精確，同時不會直接輸出結果供進一步處理。

1. 選取您要分析的儲存格區域（如 B3:B22）。

2. 在「常用」索引標籤中，點選「條件格式化」>「新增規則」。

3. 在對話方塊中選擇「使用公式來決定要格式化哪些儲存格 」。然後在公式欄輸入以下公式：

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. 按一下「格式」並選擇一款選取項目的背景色，點選確定，再按一次確定套用規則。

這將標示出您選擇的區域內，與 E2 所設定的目標值距離最接近的所有儲存格。

如需處理大量區域或遇到非預期結果，請務必再確認參照設定正確、且已依需求設定絕對/相對參照（利用 $ 鎖定目標儲存格及區域參照）。