如何在 Excel 中尋找最接近或最近的值?
在數據分析或報表工作中,經常需要在某一列或一組數值中,找出與指定目標值最接近的項目。雖然 Excel 沒有內建「尋找最接近值」的函數,但我們可以通過公式、VBA、條件格式,或第三方工具來實現。本文將介紹幾種常見的解決方法,並詳細解析每種方式的原理、實施步驟與優缺點,幫助您根據需求選擇最佳方案。
使用陣列公式尋找最接近或最近的數字
假設您在列 B 有一串數字,想要找出最接近指定數字(例如18)的那個值。利用 Excel 的陣列公式,您可以快速找出最接近的項目,而無需手動一一比對。
首先,請選取一個空白單元格,輸入以下公式。輸入完畢後,請務必使用 Ctrl + Shift + Enter 鍵組,而不是僅按 Enter。這樣才能使公式以陣列公式的方式執行,確保正確運作:
=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 提供了實用的『選擇指定單元格』功能,可快速選出與目標值相差指定幅度的所有項目。
例如,假設您的目標值是18,並設定偏差值為2,這代表您要選取區域內所有介於16(18–2)至20(18+2)之間的數值。以下是具體操作步驟:
1. 選取您要搜尋的區域(如 B3:B22),然後依序點選 Kutools > 選擇 > 選擇指定單元格。
2. 在『選擇指定單元格』對話方塊中:
- 於『選擇類型』下選取『單元格』。
- 在 指定類型:
- 設定第一個下拉選單為 大於或等於 並輸入 16 於方框中。
- 第二個下拉選單設定為 小於或等於 然後輸入 20.
3。點擊 確定 作為執行,Kutools會提示滿足條件的單元格數量,並將所有位於偏差範圍內最接近值高亮顯示,如下圖:
此解決方案非常適合需要大量快速篩選附近值,特別是在處理大範圍且容忍度可變的情境。請注意,選取的準確性取決於您偏差設定—範圍過窄或過寬都可能導致遺漏相關數據或包含不需要的值。
使用 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 鍵以取得最接近值。
運用條件格式視覺標示最接近的值
在檢視或簡報數據時,若希望在不篩選或重整資料的前提下,直觀找出最接近目標的數值,可以使用 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%,每天為您減少數百次鼠標點擊!