做报表3年才知道,这8个Excel统计函数能让你少加一半班

大家好，我是沈未迟。

还记得刚接手销售报表那会儿，我每天的状态就是：打开Excel，盯着满屏的数据，陷入深深的迷茫。

老板说："帮我算一下华东区这个月销售额大于5000的订单有多少笔？"

我：好的老板。（然后开始一行行数，数到第200行眼睛花了）

主管说："华南区美妆品类的平均客单价是多少？最高最低分别多少？"

我：好的主管。（然后手动筛选，筛完算平均，再筛一次算最高，再筛一次算最低……）

最惨的是月末做排名，十几个销售的业绩排名次，我一个个算，公式写错一步后面全错，又得从头来。

那时候我只知道SUM、COUNT、AVERAGE这几个基础函数，多条件统计全靠手动筛选+计算器。直到后来财务姐姐看不下去了，甩给我一张函数清单，说："姑娘，这些都不会，你不加班谁加班？"

今天这篇，我把统计函数家族里最实用的8个掏出来给你们唠唠。每个都配真实场景和我踩过的坑，看完你会发现：原来以前加的班，全是因为没认识它们。

一、COUNTIFS：多条件计数，比COUNTIF强一万倍

语法说明

COUNTIFS(条件区域1, 条件1, 条件区域2, 条件2, ...)

和COUNTIF长得很像，就多了个S。但这个S可不是白加的——COUNTIF只能一个条件，COUNTIFS可以写**最多127个条件**，而且是"且"的关系，所有条件同时满足才算。

真实工作场景

老板问："华东区美妆品类这个月有多少笔订单？"

以前我是先筛选地区=华东，再筛选品类=美妆，然后看状态栏计数。现在一个公式搞定：

=COUNTIFS(A:A, "华东", B:B, "美妆")

想加条件继续往后写就行，比如再加个"金额大于1000"：

=COUNTIFS(A:A, "华东", B:B, "美妆", C:C, ">1000")

按时间段统计也很方便，算3月份新增了多少客户：

=COUNTIFS(D:D, ">=2024-3-1", D:D, "<=2024-3-31")

日期条件要用双引号括起来，大于小于号写在日期前面。

我当年踩过的坑

刚学COUNTIFS的时候，我犯过一个特别蠢的错误——**条件区域的大小要一致**。

有一次我写的是=COUNTIFS(A2:A100, "华东", B2:B99, "美妆")，一个99行一个98行，结果直接#VALUE!报错。我查了半天都没发现问题，还是同事提醒我行数对不齐。

还有一个坑：文本条件不区分大小写，但"华东 "（后面多了个空格）就不一样了。数据清洗很重要，别让看不见的空格坑了你。

二、SUMIFS：多条件求和，告别手动筛选

语法说明

SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2, ...)

注意参数顺序！SUMIF是"条件区域, 条件, 求和区域"，SUMIFS反过来了——**求和区域放在第一个**。这个坑90%的人都踩过。

真实工作场景

老板问："华东区美妆品类这个月的总销售额是多少？"

=SUMIFS(C:C, A:A, "华东", B:B, "美妆")

C列是销售额，同时满足地区=华东、品类=美妆的就加起来。就这么简单。想加条件继续往后写就行。

模糊条件也支持，比如算所有姓"王"的销售的业绩总和：

=SUMIFS(C:C, E:E, "王*")

我当年踩过的坑

说出来不怕你们笑，我用SUMIFS的前三天，几乎每次都写错参数顺序。因为之前SUMIF用习惯了，条件在前求和在后，到了SUMIFS就反过来了。每次写完结果都是0，还不知道哪错了。

后来我给自己编了个口诀：**SUMIFS，S在后面，求和跑最前面。** 默念三遍就不会错了。

还有一个巨坑：**求和区域里如果有文本型数字，SUMIFS会直接忽略**。我曾经对账差了两万多，查了一下午，发现有几行金额是文本格式的。转成数字之后就对了。

三、AVERAGEIFS：多条件求平均

语法说明

AVERAGEIFS(求平均区域, 条件区域1, 条件1, ...)

参数顺序和SUMIFS一样，求平均区域放第一个。

真实工作场景

主管问："华南区护肤品类的平均客单价是多少？"

=AVERAGEIFS(C:C, A:A, "华南", B:B, "护肤")

直接出结果，不用先筛选再拉平均值。

做运营经常会遇到异常值的问题，比如0元订单、超大额团购单，会把平均值拉得很偏。这时候可以加条件排除：

=AVERAGEIFS(C:C, C:C, ">0", C:C, "<10000")

这样算出来的平均值更能反映真实的日常水平。

我当年踩过的坑

AVERAGEIFS有个和前两位兄弟不一样的地方——**如果没有满足条件的单元格，它会返回#DIV/0!错误**。COUNTIFS和SUMIFS没找到会返回0，但AVERAGEIFS没找到就报错，因为除以0没意义。

我第一次遇到的时候慌得一批，以为公式写错了。后来学聪明了，外面套个IFERROR：

=IFERROR(AVERAGEIFS(...), "无数据")

四、MAXIFS / MINIFS：条件最大最小值

语法说明

- MAXIFS(最大值区域, 条件区域1, 条件1, ...) —— 满足条件的最大值

- MINIFS(最小值区域, 条件区域1, 条件1, ...) —— 满足条件的最小值

这两个是Excel 2019及以后版本才有的函数，老版本需要用数组公式替代。

真实工作场景

老板问："华东区最高的一单是多少钱？最低的呢？"

最高：=MAXIFS(C:C, A:A, "华东")

最低：=MINIFS(C:C, A:A, "华东")

两个公式两秒钟出结果。搁以前我得先筛选再找极值，来回切。

还有个好用法：找客户最近一次下单日期。因为日期在Excel里就是数字，MAX就是最近的日期：

=MAXIFS(D:D, E:E, "张三")

我当年踩过的坑

刚知道MAXIFS的时候我特别兴奋，结果回公司一用，Excel提示"#NAME?"，我当时人都傻了——公司电脑装的是Excel 2016，没有这个函数！

老版本可以用数组公式替代：=MAX(IF(A:A="华东", C:C))，输完按Ctrl+Shift+Enter。能升级就升级吧，新函数是真香。

五、RANK：排名函数，中国式排名vs美式排名

语法说明

RANK(要排名的数值, 排名的区域, 排序方式)

- 排序方式：0或省略 = 降序，1 = 升序

真实工作场景

月末给十几个销售排业绩名次，C列是总业绩，D列排名次：

=RANK(C2, C$2:C$20, 0)

往下一拉，名次就都出来了。注意排名区域要按F4锁死，不然往下拉的时候区域会飘。

我当年踩过的坑

说到排名就不得不提：**并列排名怎么办？**

两个人都是100分，RANK会给他们都排第1，下一个人就是第3名，这叫"美式排名"。

但我们中国人有时候习惯"中国式排名"——两人并列第1，下一个是第2名，名次不跳号。

我第一次做排名表就踩了这个坑。老板看到第1名有两个人，直接跳到第3名，说："怎么没有第2名？你是不是算错了？"

我当时还特理直气壮地说Excel就是这样的。后来才知道，中国式排名有专门的公式：

=SUMPRODUCT((C$2:C$20>C2)/COUNTIF(C$2:C$20, C$2:C$20))+1

记不住也没关系，收藏起来用的时候复制粘贴就行。

六、LARGE / SMALL：第N大/第N小

语法说明

- LARGE(数据区域, N) —— 返回区域中第N大的值

- SMALL(数据区域, N) —— 返回区域中第N小的值

真实工作场景

老板问："这个月业绩前三名分别是多少？"

=LARGE(C:C, 1) 第1名，=LARGE(C:C, 2) 第2名，=LARGE(C:C, 3) 第3名。直接出结果，不用排序不用筛选。

光有数值不够，还想知道是谁？配合XLOOKUP：

=XLOOKUP(LARGE(C:C, 1), C:C, B:B)

找到第1大的业绩，然后返回对应的人名。做排行榜的时候巨好用。

我当年踩过的坑

有一次我做TOP10榜单，用LARGE算前10名，结果发现第5名和第6名数值一样，XLOOKUP返回的人名也一样——因为找到第一个匹配项就返回了。

也就是说，如果有并列的情况，简单的LARGE+XLOOKUP会把同一个人返回两次。

后来我就学聪明了，如果数据里可能有并列，就用更复杂的公式处理，或者直接排序肉眼确认一下再做榜单。毕竟给老板的东西，错一个人名都很尴尬。

组合实战：1+1>10的神级用法

单个函数已经很能打了，但真正的高手都是组合出招。分享三个我常用的组合拳。

组合1：LARGE + SUM = 前N名求和

老板说："帮我算业绩前5名一共贡献了多少销售额？"

以前先排序再手动选前5行求和，现在一个公式搞定：

=SUMPRODUCT(LARGE(C2:C20, ROW(1:5)))

原理是ROW(1:5)生成{1,2,3,4,5}，LARGE分别返回第1到第5大的值，SUMPRODUCT加起来。想算前几名就把5改成几。

组合2：COUNTIFS + 数据验证 = 不重复输入

做客户信息表时，录入客户名称容易重复。用COUNTIFS配合数据验证，输入重复值时直接弹警告：

1. 选中客户名称列

2. 数据 → 数据验证 → 允许选"自定义"

3. 公式输入：=COUNTIFS(A:A, A1)=1

4. 设置出错警告提示

这样输入已存在的客户名，Excel会直接弹警告。从源头上避免重复数据，比事后删重复项靠谱多了。

组合3：RANK + 条件格式 = 自动高亮前三名

做业绩表时，想让前三名自动变颜色，一眼就能看到谁是头部：

1. 选中业绩列

2. 条件格式 → 新建规则 → 使用公式确定格式

3. 输入：=RANK(C2, C$2:C$20)<=3

4. 设置填充颜色

前三名自动高亮，不用每次手动找。想高亮前几名就把3改成几。

速记口诀 + 新手避坑指南

一、速记口诀

*多条件三兄弟：*

COUNTIFS计数，SUMIFS求和，AVERAGEIFS求平均

注意SUMIFS/AVERAGEIFS：求的区域放第一个

*极值双煞：*

MAXIFS找最大，MINIFS找最小

2019以后才有，老版本用数组替代

*排名三剑客：*

RANK排名次，并列会跳号

LARGE第N大，SMALL第N小

中国式排名不用愁，SUMPRODUCT来搭救

二、新手避坑指南（7条血泪教训）

*1. SUMIFS和SUMIF参数顺序不一样！*

SUMIF是"条件区, 条件, 求和区"，SUMIFS是"求和区, 条件区, 条件..."。记不住就默念：S在后面，求和跑最前面。

*2. 所有条件区域的大小必须一致*

每个区域的行数和列数必须完全相同，不然直接#VALUE!报错。

*3. AVERAGEIFS没找到数据会报#DIV/0!*

不像SUMIFS返回0，AVERAGEIFS没数据就报错，外面套个IFERROR就好。

*4. MAXIFS/MINIFS是2019以后才有的*

老版本用不了会显示#NAME?，要么升级，要么用数组公式替代。

*5. RANK是美式排名，并列会跳号*

两个人并列第1，下一个就是第3名。要中国式排名用SUMPRODUCT那个公式。

*6. 文本型数字会被忽略*

求和、求平均、找极值时，如果数字是文本格式，公式会直接跳过不算。对不上数先检查格式。

*7. 整列引用会拖慢速度*

A:A写着省事，但数据量大的时候公式会很慢。尽量写具体范围，比如A2:A1000。

写在最后

其实Excel的统计函数远不止这些，但日常工作中90%的场景，今天说的这8个就够用了。

我刚学Excel的时候，总觉得统计嘛，不就是求和计数求平均？后来越用越发现，**真正省时间的不是会多少函数，而是知道"这个场景该用哪个函数"。**

老板让你算多条件的数量，用COUNTIFS，两秒出结果；

主管让你找各区域的最高业绩，用MAXIFS，两秒出结果；

月末要排名次，用RANK+LARGE，五分钟搞定半小时的活。

这些东西说破了不值钱，但没人告诉你的话，你可能要加很多次班才能慢慢摸到门道。

这也是我写「效率小本本」的初衷——把我踩过的坑、摸索出来的门道，都摊开给你们看。能让你们少加一点班，多留点时间给自己，这事儿就值。

觉得有用的话，点个在看，分享给你身边还在跟Excel死磕的朋友。

咱们下期见~