极值与排名：MAX、MIN、LARGE、SMALL、RANK

一、MAX 与 MIN：最值及其隐藏规则

=MAX(数字1,[数字2],...) 返回一组值中的最大值，=MIN 返回最小值，两者的参数规则完全相同，最多 255 个参数，可以是区域、常量或算式。日常最常用的是对一个区域取最值，例如 =MAX(C2:C200) 得到最高销售额，=MIN(C2:C200) 得到最低销售额。

MAX 与 MIN 会忽略文本、逻辑值和空单元格，这一点与 SUM、AVERAGE 保持一致。所以区域里混着待定、未录入这类文字时，最值照样能算出来。但它们不忽略错误值，区域里只要有一个 #N/A，整个公式就报错。

真正需要留意的是文本型数字同样被忽略。如果 C 列里有几个数字是文本格式，MAX 会跳过它们，导致最大值偏小。这类错误特别隐蔽，因为结果看起来完全合理，只是比真实值小一点。核对方法是对比 =COUNT(C2:C200) 与实际数据行数。

MAX 与 MIN 对日期同样有效，因为日期本质上是数字。=MAX(A2:A200) 返回最晚的日期，=MIN(A2:A200) 返回最早的日期，比按日期排序还快。做项目排期表时，用这两个函数自动算出起止日期，比手工维护省事得多。

还有一个实用技巧：MAX 可以带第二个参数做下限约束。例如提成按销售额的百分之五计算，但保底 500 元，写成 =MAX(C2*0.05,500)。MIN 同理可做上限封顶，例如报销上限 2000 元写成 =MIN(C2,2000)。这种写法比 IF 判断更短，也更好读。

还有一个高频需求：取最值对应的那条记录的其他字段。MAX 只给你数值，不告诉你这个数是哪个人的。要拿到姓名，需要用查找函数配合，写成 =INDEX(A2:A200,MATCH(MAX(C2:C200),C2:C200,0))。这条组合是极值与查找的经典搭配，查找部分的原理见 INDEX 加 MATCH：更稳的查找组合。

用 MAX 做下限约束时还要注意一点：两个参数的类型必须一致。写 =MAX(C2*0.05,500) 没问题，但如果写成 =MAX(C2,"500")，文本型的 500 会干扰比较，结果可能不是你想要的。凡是参与最值比较的值，都要确保是数值类型，这一点在拼接公式时尤其容易出错。

最后提醒一句，MAX 与 MIN 对隐藏行一视同仁，隐藏不等于排除。筛选之后想只对可见单元格取最值，需要借助 SUBTOTAL 与 OFFSET 的组合，或者干脆先把筛选结果复制到新区域再算。多数情况下，复制出来算一遍比写复杂公式更快，也更不容易出错。

二、LARGE 与 SMALL：第 N 大与第 N 小

=LARGE(数组,第几个) 返回数组中第 N 大的值，=SMALL(数组,第几个) 返回第 N 小的值。第二参数必须是正整数，写成 1 时等价于 MAX 与 MIN，写成 2 就是第二大、第二小。

典型用法是取前三名。=LARGE(C2:C200,1) 得到第一名，=LARGE(C2:C200,2) 得到第二名，=LARGE(C2:C200,3) 得到第三名。把数字放在单元格里用引用代替，比如 F2 里写 1、F3 里写 2，公式写成 =LARGE(C$2:C$200,F2)，向下填充就能拉出一整列排名值。

第二参数超过数据个数时会返回 #NUM!。例如区域里只有 10 个数，写 =LARGE(C2:C11,11) 就会报错。做模板时给外层套 IFERROR，写成 =IFERROR(LARGE(C2:C200,F2),"")，超出范围时显示空白而不是错误。

LARGE 与 SMALL 的真正价值在于配合求和操作。要算前三名的业绩合计，写成 =SUM(LARGE(C2:C200,{1,2,3}))；要算去掉最高最低之后的合计，写成 =SUM(C2:C200)-MAX(C2:C200)-MIN(C2:C200)。这类组合在评分统计里极为常见。

LARGE 与 SMALL 的第二参数也可以来自公式。比如要取前 10% 名次的临界值，写成 =LARGE(C2:C200,ROUNDUP(COUNT(C2:C200)*0.1,0))，先用 COUNT 数出有效个数，乘 10% 再向上取整，得到第 N 名。这个临界值可以直接作为条件格式的判断依据，把前 10% 的行自动标色，数据变化时无需手工调整。

另一个常见用法是取倒数几名做风险排查。例如库存周转天数最长的五个 SKU，写成 =LARGE(F2:F200,{1,2,3,4,5})，或者用 SMALL 取最短的几个做对比。把 TOP5 与 BOTTOM5 并排展示，比只给一个平均值更能说明问题，这也是看板设计的常用手法。

三、RANK 家族：排名与并列处理

=RANK(数字,区域,[排序方式]) 返回某个数字在区域中的排名。第三参数为 0 或省略时按降序排名，也就是数值越大排名越靠前；第三参数为 1 时按升序排名，数值越小排名越靠前。日常的成绩排名、业绩排名都用降序，写成 =RANK(C2,C$2:C$200,0)。

RANK 处理并列的方式是占用名次。两个人并列第 2 名时，两个人都显示 2，而下一个人直接跳到第 4 名，第 3 名空缺。这是国际通用的排名规则，符合大多数人的直觉，也和中国式排名不同。

中国式排名要求并列之后不跳号，也就是两个人并列第 2 名后，下一个人仍是第 3 名。实现写法是 =SUMPRODUCT((C$2:C$200>C2)*(1/COUNTIF(C$2:C$200,C$2:C$200)))+1。公式的原理是数出比当前值大的不重复值个数再加 1，逻辑清晰，兼容性也很好。

另外两个变体是 RANK.EQ 与 RANK.AVG。RANK.EQ 与 RANK 行为完全一致，是新版里的规范名称；RANK.AVG 在并列时返回平均名次，两人并列第 2 和第 3 时都显示 2.5。老版本 Excel 只认 RANK，写文件给别人看时用 RANK 最保险。

排名区域必须用绝对引用锁定。写成 =RANK(C2,C$2:C$200,0) 后向下填充，区域始终固定，被排名的单元格逐行变化。忘记加美元符号是排名公式最常见的错误，表现为所有人的排名都一样或者全部出错。

排名公式还有一个细节值得注意：区域里如果包含小计行，小计会被当成一条数据参与排名，导致所有人的名次都被推后一位。解决办法是把排名区域严格写成数据行范围，避开小计行；或者把小计行放在数据区之外。这一点在做带分组小计的长表时几乎必然会踩到，值得提前规避。

如果只需要知道某人是否进入前三，不必算出完整排名。写成 =IF(C2>=LARGE(C$2:C$200,3),"进入前三","") 即可，逻辑更直接，计算量也更小。这类用最值做阈值判断的写法，比先排名再判断省一层公式，在条件格式和 IF 判断里都很常见。

四、按条件取最值与数组写法

MAX 本身不支持条件，Excel 2019 与 365 提供了 =MAXIFS(最大区域,条件区域1,条件1,...)，参数顺序与 SUMIFS 一致。例如算市场部的最高销售额，写成 =MAXIFS(C2:C200,B2:B200,"市场部")。MINIFS 同理。

在没有 MAXIFS 的版本里，通用写法是数组公式 =MAX(IF(B2:B200="市场部",C2:C200))。输入后需要按 Ctrl 加 Shift 加 Enter 确认，Excel 会自动在公式外面加上大括号。注意不要自己手打那对大括号，那是 Excel 加的标记，手打无效。

数组写法的原理是：IF 先生成一个由 FALSE 与目标值组成的中间数组，MAX 再忽略其中的逻辑值取最大。如果条件不成立时返回 FALSE，MAX 会跳过；如果写成返回 0，则最小值场景会出错。所以 MIN 的数组写法要写成 =MIN(IF(B2:B200="市场部",C2:C200))，同样依赖 IF 的第三参数被省略时返回 FALSE。

双条件的数组写法里，两个条件用乘号连接，表示与关系；用加号连接则表示或关系。这是布尔运算在数组公式里的标准用法，理解之后几乎所有多条件数组问题都能套这个模板。更详细的说明见 AND、OR、NOT 与布尔运算。

五、真实场景：Top N 与自动标注

场景一：业绩龙虎榜。先用 =LARGE(C$2:C$200,ROW(A1)) 向下填充取出前 N 名的数值，ROW(A1) 会随着填充变成 1、2、3。再用 =INDEX(A$2:A$200,MATCH(E2,C$2:C$200,0)) 反查出对应的姓名，其中 E2 是刚才取到的数值。这一套组合能自动排出完整榜单，数据变了榜单立刻跟着变。

场景二：自动标注最高最低。选中数据区，新建条件格式规则，用公式 =C2=MAX($C$2:$C$200) 设置绿色填充，=C2=MIN($C$2:$C$200) 设置红色填充。这里的引用写法很关键：C2 用相对引用以便逐行判断，区域用绝对引用以便固定比较范围。

场景二的补充：条件格式里用最值做判断时，公式必须返回 TRUE 或 FALSE。写 =C2=MAX($C$2:$C$200) 是对的，写 =MAX($C$2:$C$200) 则会对所有行生效或不生效，因为它不随行变化。判断条件是否随行变化，是调试条件格式的第一要点。

场景三：找出最早与最晚日期。项目表里 A 列是任务开始日期，B 列是结束日期。整体工期起点写成 =MIN(A2:A200)，终点写成 =MAX(B2:B200)，总工期写成 =MAX(B2:B200)-MIN(A2:A200)。一行公式替代手工查找，且随数据自动更新。

场景四：剔除异常值后的统计。先用 =AVERAGE(C2:C200) 与 =MEDIAN(C2:C200) 对比判断是否存在极端值，确认后用 =TRIMMEAN(C2:C200,0.1) 或 =SUM(C2:C200)-MAX(C2:C200)-MIN(C2:C200) 重新统计。平均值的取舍原则见 平均值陷阱：AVERAGE、AVERAGEIF 和中位数。

场景四补充一点：判断异常值时不要只看最大值。用 =MAX(C2:C200)/MEDIAN(C2:C200) 算一个倍数，超过三倍通常提示存在真正的异常。这个比值比单纯的极差更有参考价值，因为它把数据的整体水平纳入了考量。

场景五：库存预警。D 列是库存量，E 列是安全库存。要找出所有低于安全库存的 SKU 数量，写成 =SUMPRODUCT(--(D2:D200<E2:E200))；要给出缺口最大的 SKU，先算缺口列 =E2-D2，再用 =MAX(F2:F200) 取最大缺口。注意公式里的小于号要写成半角符号。

六、函数选择速查

面对一个具体需求，先判断你要的是数、是名次、还是那条记录本身。下面这张表按需求类型给出推荐写法，照着选就不会写偏。

选函数的另一个经验是：能用一个函数解决的，不要堆三层嵌套。公式越长，出错概率越高，接手的人也越难读懂。宁可多插一列辅助列，把复杂问题拆成两步，也不要把所有逻辑塞进一个单元格。辅助列不丢人，它是可维护性的体现。

如果最终要把结果呈现给别人，建议把关键数字和它的来源放在同一屏内。比如在最大值旁边注明这是哪个部门、哪一天的数据，用 INDEX 加 MATCH 多引一列即可。单纯一个最大数字往往引发追问，提前把上下文补齐，能省掉一轮来回沟通。

常见问题

MAX 和 LARGE 第二参数为 1 时结果一样吗

完全一样，=MAX(C2:C200) 与 =LARGE(C2:C200,1) 返回值相同，=MIN 与 =SMALL(C2:C200,1) 同理。区别在于扩展性：LARGE 的第二参数可以换成任意数字，也可以引用单元格，方便做模板；MAX 则写死只能取最大。做固定报表时用 MAX 更简洁，做可配置模板时用 LARGE 更灵活。

WPS 里这些函数都能用吗

MAX、MIN、LARGE、SMALL、RANK 在 WPS 表格里全部支持，行为一致。MAXIFS 与 MINIFS 需要较新的版本，遇到 #NAME? 就退回数组写法。另外 WPS 对数组公式的三键确认要求与 Excel 2019 之前相同，写完 =MAX(IF(...)) 后记得按 Ctrl 加 Shift 加 Enter，否则结果可能不对。

RANK 排名出现重复名次，怎么处理

先确认你想要哪种规则。国际排名会跳号，也就是并列第 2 之后直接第 4，这是 RANK 的默认行为，大多数场景可以直接用。如果想要不跳号的中国式排名，用 =SUMPRODUCT((C$2:C$200>C2)*(1/COUNTIF(C$2:C$200,C$2:C$200)))+1。如果只想让并列的人按顺序错开，可以加一个极小的辅助值，例如 =RANK(C2+ROW()/100000,C$2:C$200,0)，用行号做微调。

LARGE 返回了错误值是怎么回事

最常见的原因是第二参数大于数据个数。区域里只有 50 个数值，写 =LARGE(C2:C200,60) 就会返回 #NUM!。第二个原因是区域里全是文本或空白，此时同样返回错误。第三个原因是整个区域只有一个有效值而第二参数写了 2。逐个检查这三个方向，通常一两分钟就能定位。

MAXIFS 在自己的 Excel 里找不到

MAXIFS 与 MINIFS 是 Excel 2019 与 Microsoft 365 才引入的函数，Excel 2016 及更早版本没有，输入后会显示 #NAME?。解决方案有三：升级版本；改用数组公式 =MAX(IF(...))；或者加辅助列把不符合条件的值替换成空，再对辅助列用 MAX。WPS 表格的新版本已经支持 MAXIFS，老版本同样需要退回数组写法。

数组公式按回车没反应怎么办

=MAX(IF(...)) 这类公式在 Excel 2019 之前必须按 Ctrl 加 Shift 加 Enter 三键确认，只按回车会返回错误值或者只算第一个。确认成功的标志是编辑栏里公式被一对大括号包围。如果你用的是 Excel 365 或 WPS 新版，动态数组引擎会自动处理，直接回车即可。判断自己属于哪种情况，输入一个简单数组公式试一下就知道了。

排名区域里有文本会怎样

RANK 会忽略文本与空白，只对数值排名，所以区域里混着文本通常不会报错。但如果被排名的那个单元格本身是文本，RANK 会返回 #N/A。解决办法是先检查数据类型，用 =ISNUMBER(C2) 确认，再决定要不要转换。数据清洗的做法可以参考 计数三兄弟：COUNT、COUNTA、COUNTIF、COUNTIFS 里的排查思路。

并列第一时，INDEX 加 MATCH 只能返回一个

这是查找类函数的固有行为：MATCH 找到第一个匹配项就停止。如果两个人的业绩完全相同，公式只会返回靠前那位的姓名。解决办法有三：给数值加极小扰动让它们不再相等；改用 FILTER 一次返回所有并列者；或者在排名列先做中国式排名，再按排名查找。哪种方案取决于你要呈现的形式，榜单场景通常用第二种。

为什么最大值和排序后看到的不一样

多半是数据类型不一致。排序时 Excel 会把文本型数字排在一起，而 MAX 会直接忽略它们。用 =COUNT(C2:C200) 对比总行数，少多少行就有多少个非数值。另一种可能是区域没覆盖全，比如数据已经涨到 250 行而公式还写着 C2:C200。把区域改成整列或智能表引用，可以彻底避免这一类问题。

排名结果全是 1 或者全是错误

全是 1 通常是因为区域用了相对引用，向下填充后每个公式都在和自己比。检查公式里的区域是不是写成了 C2:C200 而不是 C$2:C$200。全是错误则多半是被排名的单元格本身不是数值，用 =ISNUMBER(C2) 抽查几行就能确认。这两类错误的表现截然不同，但根因都很简单，三十秒内就能修好。

想按多列综合排序怎么办

RANK 只能对单一数值排名。需要多列综合时，先算出综合分。例如业绩占六成、回款占四成，辅助列写 =C2*0.6+D2*0.4，再对辅助列排名。把权重写在单元格里而不是写死在公式中，方便随时调整口径。这也是加权思想的又一次应用，与加权平均的思路完全一致。