在 Excel 的一列之中，查找小于自己的最大值

这个案例来自网友提问：如何在一列数据中查找大于自己的最小值，或者小于自己的最大值？

这种需求其实解法很多，不要想复杂了，公式越短越好。

案例：

下图 1 是各销售人员当月获客数列表，请根据 D 列指定的最低标准，查找出 B 列中低于最低标准的最大值及其行号。

效果如下图 2 所示。

解决方案：

比如我们将最小获客数设置为 6，来看一下两组不同的公式用法。

1. 在 E2 单元格中输入以下公式 --> O365 直接回车，低版本按 Ctrl+Shift+Enter 三键回车：

=MAX(IF(B2:B13-D2<0,B2:B13))

公式释义：

• IF(B2:B13-D2<0,B2:B13)：查找 B2:B13 区域中小于 D2 的所有值

• MAX(...)：提取上述查找结果中的最大值
  

2. 在 F2 单元格中输入以下公式 --> O365 直接回车，低版本按 Ctrl+Shift+Enter 回车：

=MATCH(MAX(IF(B2:B13-D2<0,B2:B13)),B:B,0)

公式释义：

• 用 match 函数匹配出上一个公式查找的结果在 B 列中的行号
  

如果用的是 O365 版本，其实还可以用一个函数将公式简化一下。

3. 在 E3 单元格中输入以下公式 --> 回车：

=MAXIFS(B:B,B:B,'<'&D2)

开始公式释义前，我们先学习一下 maxifs 函数。

MAXIFS 函数详述

作用：

• 返回一组给定条件或标准指定的单元格中的最大值。

语法：

• MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

参数：

• max_range：必需， 确定最大值的实际单元格区域。

• criteria_range1：必需，一组用于条件计算的单元格。

• criteria1：必需，用于确定哪些单元格是最大值的条件，格式为数字、表达式或文本。一组相同的条件适用于 MINIFS、SUMIFS 和 AVERAGEIFS 函数。

• [criteria_range2, criteria2], ...：可选，附加区域及其关联条件。最多可以输入 126 个区域/条件对。
  

说明：

• max_range 和 criteria_rangeN 参数的大小和形状必须相同，否则这些函数会返回 #VALUE! 错误。

公式释义：

• MAXIFS(B:B,B:B,'<'&D2)：在 B 列中查找小于 D2 单元格的最大值

4. 在 F3 单元格中输入以下公式 --> 回车：

=MATCH(MAXIFS(B:B,B:B,'<'&D2),B:B,0)

公式释义：

• 用 match 函数查找出前一个公式结果在 B 列中的行号