效率翻倍|Excel 搞定前 N 名、动态排名、筛选求和,拒绝重复干活

之前给大家介绍了SUMIF、COUNTIF、AVERAGEIF，这一期为大家介绍四个同样高频、但很多人还在靠「排序 + 肉眼挑」的场景——取前几名、算排名、筛选后自动求和。

比如：这个月销售冠军是谁？某个人的业绩排第几名？筛选出「电脑」之后，销售额总和是多少？

这三个问题，分别对应 LARGE / SMALL、RANK、SUBTOTAL。它们凑在一起，就是一套「不动物理顺序、自动跟着数据变」的取数排名方案。

LARGE / SMALL / RANK / SUBTOTAL 速查表

核心逻辑：LARGE / SMALL 是一对，一个取大、一个取小，比 MAX / MIN 强在能取「任意名次」。RANK 专门解决「排第几」。SUBTOTAL 是最容易被忽略的高手函数——它能自动忽略被筛选隐藏的行，这是普通 SUM 做不到的。

场景1：LARGE / SMALL 取前3名、倒数第1名

需求

你手上有一张销售业绩表，老板连问三个：这个月冠军是谁？前三名是谁？垫底的是谁？

以前你怎么做？点排序 → 降序排一遍看前三 → 再升序排一遍看倒一 → 记完还得把顺序排回去，否则原表顺序全乱了。或者用 MAX、MIN，但这两个只能各取一个最大、最小，取不到第二名、第三名。

示例数据区域： A1:B9，月度业绩

通用公式：

=LARGE(B2:B9,1)    → 第1名：96 =LARGE(B2:B9,3)    → 第3名：85 =SMALL(B2:B9,1)    → 倒数第1：35 

分析：

LARGE(区域, N) 取区域里第 N 大的数，SMALL(区域, N) 取第 N 小的数。N 写几就是第几名，全程不用动原始数据的顺序。

更进一步，想求「前三名的业绩总和」，可以嵌套：=SUM(LARGE(B2:B9,{1,2,3}))（365 直接回车，旧版 Ctrl+Shift+Enter），一次算出前三名合计 96 + 91 + 85 = 272。这是 MAX、MIN 完全做不到的。

场景2：RANK 实时排名

需求

季度考核，要给每个销售员的业绩排个名次，从第1名排到最后一名，还要放进表里、能被别的公式引用。

以前你怎么做？排序 → 手动填 1、2、3、4……填完发现漏了一个人，或者改了一个业绩数，名次全乱，又得重新排、重新填。

示例数据区域： A1:C9，业绩排名表

通用公式（C2 填入后下拉）：

=RANK(B2,$B$2:$B$9,0) 

分析：

RANK(数值, 区域, 方式) 返回「数值在区域里排第几」。第三个参数 0 或省略 = 降序（数字大的排第1），写 1 = 升序（数字小的排第1）。业绩这种「越大越好」的，用 0。

$B$2:$B$9 加绝对引用锁死排名范围，下拉时范围不变，只有被排名的 B2 跟着变。结果：王五 96 排第1、周八 91 排第2、张三 85 排第3……吴九 35 排第8。

💡 并列会跳号：如果有两个人都是 96 并列第1，下一个会直接是「第3名」而不是第2名（传统 RANK 的排名规则）。如果一定要「并列也连续」（1、1、2、3），用 =RANK(B2,$B$2:$B$9,0)+COUNTIF($B$2:B2,B2)-1，这个技巧我们后面讲查询函数时会再展开。

场景3：SUBTOTAL 筛选后动态求和

需求

一张销售明细表，几十个品类。你点筛选选「电脑」，想看电脑的销售额总和，还想把这个数字固定在一个单元格里，方便对比和引用。

以前你怎么做？筛选后用 =SUM(C2:C9)——结果不变！因为 SUM 会把被隐藏的行也一起算进去。你以为算的是「电脑」，其实算的是「全部」。想看对的数，只能盯状态栏，但状态栏的数字又写不进单元格、没法被引用。

示例数据区域： A1:C9，销售明细

通用公式：

=SUBTOTAL(9,C2:C9) 

分析：

SUBTOTAL(功能码, 区域) 的第一个参数是「功能码」，9 代表求和（SUM）。关键在它的行为：SUBTOTAL 会自动忽略被筛选隐藏的行。筛「电脑」后，=SUBTOTAL(9,C2:C9) 只算看得见的电脑那几行 = 85,000 + 96,000 + 73,000 + 52,000 = 306,000；不筛选时它就是全部总和。同一个格子，跟着筛选自动变。

功能码有一组常用组合，除了 9（求和），还有 1 = 平均、2 = 计数、3 = 计数（非空）、4 = 最大值、5 = 最小值。记住最常用的 9 和 109 的区别：9 忽略筛选隐藏的行、但算手动隐藏的行；109 连手动隐藏的行也不管。日常筛选用 9 就够了。

四个函数对比总结

统计函数三期的收官篇，就讲到这里。LARGE / SMALL 管「第几名是多少」，RANK 管「它是第几名」，SUBTOTAL 管「看得到的才算」——三个方向覆盖了日常取数、排名、汇总的大半需求。

下次遇到「前几名」「排第几」「筛选后求和」，别再排序挑数、别再手动填名次、更别再用 SUM 骗自己了。一个公式下去，数据怎么变，结果怎么对。

下期预告：统计函数系列告一段落，下一期开新系列——查询函数，先讲 VLOOKUP 的「反向查找」和「跨表匹配」，这是财务对账最常用的硬功夫。回复「查询1」提前领取配套练习。

附：长期坚持原创不易，如文章能够为大家带来少少帮助的，请大家点赞并转发，以支持我继续分享创作，你的支持将是我的不竭动力！谢谢！

（本文为本公众号原创，未经允许和授权，严禁转载，违者必究）