Excel 实用技巧 | 统计函数(下):排名、取Top N、分类汇总,这4个函数让你的统计报表升维

前两期讲了（SUM/AVERAGE/MAX/MIN）基础统计和（COUNTIF/SUMIF/AVERAGEIF）条件统计，相信很多财务统计的问题都可以比较高效快速的解决了，但如果老板想要了解的更加细致例如：

"业绩排名第3的是谁？" "前两名贡献了多少业绩？" "筛选东区后，总业绩是多少？"

将这期的LARGE / SMALL / RANK / SUBTOTAL 四个进阶函数学会，上面的问题全部迎刃而解。

先混个脸熟

这4个函数的核心共同点：它们不是在"所有数据"上算，而是在"一部分数据"上算——要么是排第几的那部分（LARGE/SMALL），要么是每个人的相对位置（RANK），要么是筛选后的可见部分（SUBTOTAL）。

————————————————————————————————————————

场景数据（沿用前两期销售表，稍作扩充）

（数据范围：A2:D9，业绩列 C2:C9）

————————————————————————————————————————

场景1：LARGE — 业绩排名第3的是多少？前3名加起来多少？

需求

老板问："这个月业绩前三名分别是多少？把第3名的成绩亮出来。"

手动排序 → 从大到小 → 看第3行……数据少还好说，几千行你怎么办？

公式：

=LARGE(C2:C9,1)→ 120万（第1名：孙七）

=LARGE(C2:C9,2)→ 93万（第2名：吴九）

=LARGE(C2:C9,3)→ 91万（第3名：王五）

语法：=LARGE(数据区域, 第K个)

第一个参数是你的数据范围，第二个参数是"你要第几大的"。

分析：

LARGE本质就是Top N。想做"前三名合计贡献了多少"？套进SUM里：

=SUM(LARGE(C2:C9,{1,2,3}))→ 304万

用花括号 {1,2,3} 表示"我要第1大、第2大、第3大的值"，然后SUM包在外面加总。这是数组公式的经典用法，按 Ctrl+Shift+Enter 确认（Excel 365以上直接回车也行）。

前3名合计304万，占全公司652万的 46.6%——这就是所谓的"二八法则"在销售团队中的体现。

💡 反过来想，如果你想知道"业绩前50%的人最低是多少？"——对8个人来说，就是第4名。用 =LARGE(C2:C9,4) → 85万（张三）。就是前50%的门槛线。

————————————————————————————————————————

场景2：SMALL — 倒数第3是谁？后两名怎么帮扶？

需求

老板说："业绩后三名的名单给我，下周重点帮扶。"

公式：

=SMALL(C2:C9,1)→ 55万（倒数第1：周八）

=SMALL(C2:C9,2)→ 62万（倒数第2：李四）

=SMALL(C2:C9,3)→ 68万（倒数第3：郑十）

语法：=SMALL(数据区域, 第K个) — 和LARGE一模一样，只是从最小端开始数。

分析：

SMALL和LARGE是对称的兄弟姐妹。做末位淘汰分析、低绩效识别时，SMALL比手动排序快得多。

还可以和AVERAGE组合：后三名平均业绩 = =AVERAGE(SMALL(C2:C9,{1,2,3})) → 61.7万，远低于全公司平均81.5万。

⚠️ 注意：LARGE和SMALL的第二个参数不能小于1，也不能大于数据个数。数据共8个，如果你写 =LARGE(C2:C9,9) 会返回 #NUM! 错误。建议先用COUNT确认数据量。

————————————————————————————————————————

场景3：RANK — 张三在全公司排第几？

需求

老板指着一个名字问："张三这个月排第几？孙七呢？周八排第几？"

公式：

=RANK(85,C2:C9,0)→ 第4名（张三）

=RANK(120,C2:C9,0)→ 第1名（孙七）

=RANK(55,C2:C9,0)→ 第8名（周八）

更实用的写法——引用单元格，往下拉就能给所有人自动排名：

=RANK(C2,$C$2:$C$9,0)→ 向下填充

语法：=RANK(要排名的数值, 整个排名区域, 排序方式)

分析：

RANK最容易被新手坑的是忘了锁定区域。下拉填充时，如果写成 =RANK(C2,C2:C9,0)，拉到C3时会变成 =RANK(C3,C3:C10,0)——排名区域跟着跑了，结果全错。

正确写法：=RANK(C2,$C$2:$C$9,0) ← 用 $ 把区域钉死。

💡 想显示"第1名""第2名"这种更友好的格式？用 =RANK(C2,$C$2:$C$9,0) & "名"。结果就是"第4名"，老板看了一目了然。

RANK的并列问题：

如果两个人业绩相同（比如都是85万），RANK会返回相同的排名，然后跳过下一个排名（两个人并列第4，下一个人就是第6名）。想实现"并列第4，下一个人第5"的密集排名？Excel 2010以上提供了 RANK.EQ（同RANK）和 RANK.AVG（并列时给平均排名），或者用更强大的 SORT + UNIQUE 数组公式。

————————————————————————————————————————

场景4：SUBTOTAL — 筛选后统计，老板随时换条件

需求

老板说："我想只看东区，东区的总业绩、平均业绩、人数分别是多少？……好，现在换成西区看看。"

每次筛选完，SUM和AVERAGE的结果不会变——它们统计的是所有单元格，包括被隐藏的。你需要一个能感知筛选的函数。

公式：

=SUBTOTAL(9,C2:C9)→ 筛选东区：360万;筛选西区：292万

=SUBTOTAL(1,C2:C9)→ 筛选东区：90万;筛选西区：73万

=SUBTOTAL(2,C2:C9)→ 筛选东区：4人;筛选西区：4人（COUNT）

=SUBTOTAL(3,C2:C9)→ 筛选东区：4人;筛选西区：4人（COUNTA）

语法：=SUBTOTAL(功能编号, 数据区域)

关键区别：SUBTOTAL vs 普通函数

· 普通SUM =SUM(C2:C9) → 永远是652万（不管筛选、隐藏行还是全部显示）

· SUBTOTAL(9,C2:C9) → 只看筛选后可见的行，隐藏的行自动排除

做报表时，把SUBTOTAL放在表头或汇总行，老板在做筛选切换时，汇总结果跟着变——真正动态的报表就是这么来的。

编号还有个101-111的版本：

=SUBTOTAL(109,C2:C9) → 手动隐藏行也排除（而编号9只排除筛选隐藏，不排除手动隐藏）。实际工作中用编号9（1-11）就够了。

💡 SUBTOTAL还有一个隐藏特性——它自身不会被其他SUBTOTAL重复计算。如果你的汇总行里已经有SUBTOTAL，再在外面套一层SUBTOTAL，不会造成双重加总。这在制作多层分类汇总报表时非常有用。

————————————————————————————————————————

一张表总结

————————————————————————————————————————

全系列回顾：统计函数三剑客

三期的数据场景是递进的——同一个销售数据表，从简单加总，到按条件统计，再到排名与分类汇总。全部学会，老板问什么你都能秒回。🎯

————————————————————————————————————————

下个系列预告：统计函数系列完结！接下来开启查询函数系列——VLOOKUP、XLOOKUP、INDEX+MATCH，工作中最常用的"查数据"三件套。关注别错过，下期见！

————————————————————————————————————————

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

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