如何在 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 提供了实用的 Select Special Cells 功能，可快速选出所有与目标值相差在指定范围内的数值。

例如，假设你的目标值为18，设定的偏差值为2。那么你的筛选范围就是16（18–2）到20（18+2）之间的所有值。具体操作步骤如下：

1.选中要查找的区域（如 B3:B22），然后依次点击 Kutools > 选择 > 选择指定单元格。

2. 在“选择指定单元格”对话框中：

• 在“选择类型”下，选择“单元格”。
• 在 指定类型:
  - 将第一个下拉列表设置为 大于或等于 并输入 16 在输入框中。
  - 将第二个下拉列表设置为 小于或等于 并输入 20.

3。点击 确定 执行操作。Kutools 会提示符合条件的单元格数量，并将所有指定偏差范围内的最接近数值高亮显示，如下图所示：

这种方法特别适合批量快速查找所有邻近值，适用于处理大范围且容差变化的场景。请注意，筛选的准确性取决于您设定的偏差——如果偏差设置过窄或过宽，可能会遗漏相关数据，或选中不需要的值。

对于希望实现自动化，或需在多个工作表或大数据集间自定义查找最近值（无论数值还是文本）用户来说，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 单元格目标值距离相等的所有单元格高亮显示。

如果在处理大范围数据或遇到结果异常时，请再次确认引用设置是否正确，以及绝对/相对引用（使用 $ 锁定目标单元格和区域引用）是否如预期设置。