如何在 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 宏是一种高效且灵活的解决方案。通过编程，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 单元格目标值同样最接近的单元格。

如果处理大型区域或结果异常，请仔细检查引用是否正确，并确认绝对/相对引用设置符合预期（使用 $ 符号锁定目标单元格和区域）。