Skip to main content

如何在 Excel 中尋找最接近或最近的值?

Author: Xiaoyang Last Modified: 2025-07-21

在數據分析或報表工作中,經常需要在某一列或一組數值中,找出與指定目標值最接近的項目。雖然 Excel 沒有內建「尋找最接近值」的函數,但我們可以通過公式、VBA、條件格式,或第三方工具來實現。本文將介紹幾種常見的解決方法,並詳細解析每種方式的原理、實施步驟與優缺點,幫助您根據需求選擇最佳方案。


使用陣列公式尋找最接近或最近的數字

假設您在列 B 有一串數字,想要找出最接近指定數字(例如18)的那個值。利用 Excel 的陣列公式,您可以快速找出最接近的項目,而無需手動一一比對。

首先,請選取一個空白單元格,輸入以下公式。輸入完畢後,請務必使用 Ctrl + Shift + Enter 鍵組,而不是僅按 Enter。這樣才能使公式以陣列公式的方式執行,確保正確運作:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))
提示: 在這個陣列公式中 {=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))}:
  • B3:B22 指的是您要比對數據的資料區域。
  • E2 指的是您輸入目標值的位置(例如18)。

這種做法適合需要從連續範圍中取出最接近一筆數字的情境,特別適用於對數值精確度與完全匹配要求較高時。不過請注意,陣列公式在超大數據集下可能影響效能,若遇到效能降低或出現 #VALUE! 等錯誤訊息,請再次檢查儲存格引用是否正確,並確認已正確使用 Ctrl + Shift + Enter 鍵組。


使用 Kutools for Excel 輕鬆選取所有與指定值偏差範圍內的最接近數字

有時候您不只需要單一最接近值,而是想要一次選取所有位於目標值某個範圍內(偏差範圍)的數字。Kutools for Excel 提供了實用的『選擇指定單元格』功能,可快速選出與目標值相差指定幅度的所有項目。

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

例如,假設您的目標值是18,並設定偏差值為2,這代表您要選取區域內所有介於16(18–2)至20(18+2)之間的數值。以下是具體操作步驟:

1. 選取您要搜尋的區域(如 B3:B22),然後依序點選 Kutools > 選擇 > 選擇指定單元格

2. 在『選擇指定單元格』對話方塊中:

  • 於『選擇類型』下選取『單元格』。
  • 指定類型:
    - 設定第一個下拉選單為 大於或等於 並輸入 16 於方框中。
    - 第二個下拉選單設定為 小於或等於 然後輸入 20.

set options in the Select Specific Cells dialog box

3。點擊 確定 作為執行,Kutools會提示滿足條件的單元格數量,並將所有位於偏差範圍內最接近值高亮顯示,如下圖:
all closest values of the given value are selected

此解決方案非常適合需要大量快速篩選附近值,特別是在處理大範圍且容忍度可變的情境。請注意,選取的準確性取決於您偏差設定—範圍過窄或過寬都可能導致遺漏相關數據或包含不需要的值。


使用 VBA 巨集尋找最接近目標值的數字

對於需要自動化操作,或需跨多個工作表或龐大數據集進行自訂尋找最接近值(可為數字或文字)的進階用戶,VBA 巨集能提供彈性又高效的解決辦法。透過為 Excel 撰寫程式,您可系統化地比較目標值與候選數值的差距,不僅能找出最接近的數字,還能根據字串相似度找出最接近的文字。

當需要整合自動化,或面對範圍過於龐大難以手動比對、或需反覆執行任務時,此方式格外有利。但需注意,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 鍵以取得最接近值。

注意:本公式中,B3:B22代表資料區域,E2 為用以尋找最接近值的目標值所在地。

運用條件格式視覺標示最接近的值

在檢視或簡報數據時,若希望在不篩選或重整資料的前提下,直觀找出最接近目標的數值,可以使用 Excel 的內建條件格式功能來高亮顯示距目標值最近的儲存格,讓您一眼識別關鍵資料。此方法雖不會直接返回具體數值,但對於即時分析與視覺強調相當有效。

此方法的主要優點是具有非破壞性且動態的標示效果,可隨數據或目標值改變即時調整,特別適合資訊儀表板、簡報或檢閱場景,強調重要數據時十分實用。不過,若多個值與目標距離相同時,則無法準確顯示唯一值,且無法直接取得返回結果用於進一步處理。

1. 選取欲分析的儲存格範圍(例如 B3:B22)。

2.於[常用]標籤頁中,點選條件格式 > 新增規則

3. 在對話方塊中選擇「使用公式來決定要格式化的儲存格 」,然後於公式欄內輸入下列公式:

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

4. 點選格式並挑選高亮顏色,接著點選確定,再按一次確定套用該規則。

這樣就能高亮顯示於選取範圍中與 E2 欄目目標值等距的所有儲存格。

若您在處理大範圍數據或遇到結果異常時,請再次確認儲存格參照設定正確,並妥善使用絕對/相對參照(如以 $ 鎖定目標儲存格與範圍位置)。


示範:選出與指定值偏差範圍內的所有最接近值

 

最佳辦公效率工具

🤖 Kutools AI 助手:基於智能執行方式革新數據分析:智能執行   |  生成代碼  |  創建自訂公式  |  分析數據並生成圖表  |  調用 Kutools 函數
熱門功能查找、標記重複值或識別重複項   |  刪除空行   |  合併列或單元格而不丟失數據   |   四捨五入無需公式 ...
高級 LOOKUP多條件 VLookup    多值 VLookup  |   多表查找   |   模糊查找 ....
高級下拉列表快速創建下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
列管理器添加特定數量的列  |  移動列  |  切換隱藏列的可見狀態  |  比較區域和列 ...
特色功能網格聚焦   |  設計檢視   |   增強編輯欄    工作簿與工作表管理器   |  資源庫(自動文本)   |  日期提取器   |  合併資料   |  加密/解密儲存格    按列表發送電子郵件   |  超級篩選   |   特殊篩選(篩選粗體/斜體/刪除線...) ...
頂級 15 種工具集12 個文本工具添加文本刪除特定字符、...)   |   50+ 圖表 類型甘特圖、...)   |   40+ 實用 公式基於生日計算年齡、...)   |   19 個插入工具插入QR碼根據路徑插入圖片、...)   |   12 個轉換工具金額轉大寫匯率轉換、...)   |   7 個合併與分割工具高級合併行分割儲存格、...)   |   ... 還有更多

使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。  點擊這裡獲取您最需要的功能...


Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
  • 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
  • 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!