KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中快速找出小于 X 的最大值？

作者孙修改日期

在日常 Excel 操作中，使用简单函数找出数据集中的最大值可谓轻而易举。但当您需要找出“小于特定阈值的最大值”（例如“小于 100 的最大值”）时，挑战便随之而来——尤其在处理大型数据区域或动态条件时更是如此。这类需求常见于设定数据上限、基于最低阈值评估性能，或为报告筛选合适数值等场景。值得庆幸的是，Excel 提供了多种高效且可靠的方法来轻松应对这一难题。本指南将为您详解快速定位小于指定数值的最大值的实用技巧，并附上典型应用场景、操作建议及故障排除提示。

显示原始数据和条件的截图

使用公式查找小于 X 的最大值

使用 Kutools for Excel 查找小于 X 的最大值

使用 VBA 代码查找小于 X 的最大值

使用公式查找小于 X 的最大值

Excel 公式可助您快速找出指定范围（例如 A1:E7）内小于特定数值（如 100）的最大值。此方法适用于静态数据集，或偏好直接使用工作表函数的用户，准确可靠，无需插件或高级技能。

1. 单击要显示结果的空白单元格，然后输入以下公式。本例中，我们将在区域 A1:E7 中查找小于 100 的最大值：

=MAX(IF(A1:E7<100,A1:E7,""))

提示：这是一个数组公式。在 Office 365 之前的 Excel 版本中，必须按 Ctrl + Shift + Enter 激活，公式周围会出现花括号 {}；而在较新版本的 Excel（Office 365 及更高版本）中，只需按 Enter 即可。

 
箭头的截图
最终结果的截图

如果没有任何值低于指定阈值（例如，当范围内所有数据均大于或等于 100 时），则返回结果为零。此时，可考虑使用以下公式以返回“无匹配项”：

=IF(SUMPRODUCT(--(A1:E7<100))=0, "No match", MAX(IF(A1:E7<100,A1:E7)))

在目标单元格中输入此公式后，按 Ctrl + Shift + Enter 即可返回小于 100 的最大数值。若需应用于其他区域，只需调整公式中的单元格引用即可。

优点：适用于一次性查询或简单条件，操作快速且灵活。
缺点：当条件或区域动态变化时，可能需要调整公式；数组公式对初学者不够直观。

实用技巧：使用单元格引用或命名区域代替硬编码值，提升灵活性。例如，将 100 替换为单元格引用（如 $G$1），即可动态调整阈值。

故障排除：若出现 #VALUE！ 错误，请检查所选区域是否仅包含数字，以及阈值是否已正确指定。对于大型区域，数组公式可能会降低工作簿性能。

使用 Kutools for Excel 查找小于 X 的最大值

Kutools for Excel 提供强大的增强型公式工具，显著简化工作流程，尤其适用于标准公式过于复杂或难以记忆的场景。其内置的公式助手让您仅需简单点击鼠标，即可轻松设置基于查找条件的查询，省去手动输入公式的繁琐步骤，有效降低出错风险。对于需要频繁调整参数或高效处理大型表格的用户而言，这一功能尤为实用。

在免费安装 Kutools for Excel 后，请按以下步骤操作：

1. 单击要显示查找结果的单元格，然后依次点击 Kutools > 公式助手 > 公式助手。

在公式助手对话框中：

1）从 Lookup 下拉菜单中选择公式类型；
2）在选择一个公式中，选择查找小于指定值的最大数值；
3）在 Range 中指定您的数据区域，并在最大值中输入阈值。
配置相应公式的截图

3. 单击 OK，Kutools 将立即返回小于您指定数值的最大值。
显示最终结果的截图

此方法无需掌握复杂公式，且支持灵活调整参数；对于重复性分析，使用 Kutools 相比手动输入可大幅节省时间。

优点：适用于频繁或复杂的查询；避免公式出错；非技术用户也能轻松上手。
缺点：需安装 Kutools；在默认 Excel 环境中不可用。

注意事项：请仔细核对所选区域和输入值，避免逻辑错误。若数据中包含空单元格或非数值单元格，请务必检查输出结果的准确性。

实用技巧：在 Kutools 对话框中使用命名区域，即可轻松简化大型表格的选择操作。

故障排除：若未显示结果，请检查输入条件，确认 Kutools 已正确安装，并确保您的数据区域包含低于阈值的数值。

使用 VBA 代码查找小于 X 的最大值

Excel VBA（Visual Basic for Applications）可用于自动遍历指定区域，快速找出小于设定阈值的最大数值。VBA 尤其适用于以下场景：

  • 您需要处理可能动态变化大小的区域
  • 您希望在不使用复杂数组公式的情况下自动化搜索过程
  • 您需要重复执行此分析，或将解决方案嵌入工作流中

此方法特别适合高级用户，或需要定期更新数据集的场景。以下步骤将指导您如何设置并执行 VBA 解决方案。

1. 在 Excel 中，转到开发工具选项卡，单击 Visual Basic，即可打开 Microsoft Visual Basic for Applications 编辑器。点击插入> 模块，并将下方代码粘贴到新模块中：

Sub FindLargestLessThanX()
    Dim WorkRng As Range
    Dim xCell As Range
    Dim xMax As Double
    Dim xThreshold As Double
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select range to search", xTitleId, WorkRng.Address, Type:=8)
    
    xThreshold = Application.InputBox("Enter the threshold value", xTitleId, "", Type:=1)
    
    xMax = -1E+308 ' Initialize to very small number
    
    For Each xCell In WorkRng
        If IsNumeric(xCell.Value) Then
            If xCell.Value < xThreshold And xCell.Value > xMax Then
                xMax = xCell.Value
            End If
        End If
    Next
    
    If xMax = -1E+308 Then
        MsgBox "No value found less than " & xThreshold, vbInformation
    Else
        MsgBox "Largest value less than " & xThreshold & " is: " & xMax, vbInformation
    End If
End Sub

2. 要运行代码，请单击运行按钮按钮，或按 F5. 系统将弹出对话框，提示您选择一个数据区域，然后输入阈值（例如 100）。结果将以消息框形式显示。

优点：适用于自动化和动态表格，无需手动调整即可智能应对区域变化。
缺点：需启用宏，部分用户可能对 VBA 设置不够熟悉。

注意事项：请确保 Excel 环境中已启用宏，并仅在受信任的工作簿中运行 VBA 代码。
提示：您可修改 VBA 脚本，将输出值写入指定工作表单元格而非弹出消息框，便于后续处理。

故障排除：如果代码未执行，请确认已启用宏、输入格式正确，且所选区域包含数值。

此 VBA 方法可轻松应对动态更新的数据、自动化重复性操作，或规避公式在超大区域中带来的性能瓶颈。

相关文章：

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱