如何在 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目标值距离最接近的所有单元格。

若在超大范围操作或结果不符合预期，请仔细核查引用是否正确，并检查绝对/相对引用设置是否满足需要（用 $ 锁定目标单元格或需要的范围引用）。