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


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


案例：


下图 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 列中的行号


很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

现在终于有了，文中专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。