Excel五大"万能函数"曝光！一个顶十个，不会用真的亏大了！

每天做报表加班到10点？统计、查找、格式转换全靠手动？

别慌！今天这篇“Excel万能函数指南”，一次性揭秘5个被职场人称为“卷王”的函数——它们能替代10+个普通函数，从动态统计到复杂查找，从数据清洗到格式美化，一个函数搞定一堆活！

一、SUBTOTAL：动态统计的“隐形高手”

一句话定位：Excel里“最聪明的统计员”，能自动识别筛选状态，还能区分隐藏数据！

核心功能：11种统计一键切换

=SUBTOTAL(9, D2:D21)    //求和（最常用）
=SUBTOTAL(1, D2:D21)    //平均值
=SUBTOTAL(4, D2:D21)    //最大值
=SUBTOTAL(5, D2:D21)    //最小值

只要改第一个参数（功能代码），就能从求和切到平均、最大、最小……甚至总体方差（代码111）！

两大隐藏技能

1. 智能筛选统计：用它统计时，被筛选掉的行会自动“隐身”，只算你看到的数据。比如筛出“华东区”销售，SUBTOTAL只会统计这部分人的业绩。
2. 双重模式： 代码1-11：包含隐藏行（比如手动隐藏的某行数据也会被统计）； 代码101-111：忽略隐藏行（只算可见数据）。

适用场景：做动态报表时，搭配筛选功能用它统计，比手动改范围快10倍！

二、AGGREGATE：错误值和隐藏行的“终结者”

一句话定位：SUBTOTAL的“加强版”，能同时处理错误值、隐藏行、嵌套函数，堪称“数据清洁工”！

核心功能：19种功能×8种场景=152种组合

=AGGREGATE(9, 6, D2:D21)  //求和，忽略错误值和隐藏行

第一个参数是功能（如9=求和），第二个参数是“忽略规则”（共8种），第三个参数是数据区域。

八大忽略规则（关键！）

· 0：忽略嵌套的SUBTOTAL/AGGREGATE结果；

· 1：忽略隐藏行和嵌套函数；

· 2：忽略错误值和嵌套函数；

· 3：忽略隐藏行、错误值、嵌套函数（最常用！）；

· 6：忽略错误值（比如#N/A、#DIV/0）；

· 7：忽略隐藏行和错误值。

适用场景：处理乱数据时（比如有错误值、隐藏行），用它统计比SUBTOTAL更“抗造”！

三、SUMPRODUCT：多条件计算的“全能保姆”

一句话定位：Excel里“最灵活的计算器”，能做求和、排名、计数，甚至矩阵运算！

三大经典用法

1. 多条件求和（最常用）

//单条件：统计“一分店”销售额  
=SUMPRODUCT((A2:A21="一分店")*D2:D21)  

//多条件：统计“一分店+朱毓华”销售额  
=SUMPRODUCT((A2:A21="一分店")*(B2:B21="朱毓华")*D2:D21)  

原理：用(条件区域=目标值)生成0/1数组，相乘后只保留符合条件的位置，再乘数据区域求和。

2. 数据排名（比RANK更强大）

//整体排名：从高到低排所有销售额  
=SUMPRODUCT(($D$2:$D$21>=D15)*1)  

//分组排名：只在“一分店”内排朱毓华的名次  
=SUMPRODUCT(($D$2:$D$21>=D2)*($A$2:$A$21=A2))  

3. 其他神操作

· 条件计数：=SUMPRODUCT((A2:A21="一分店")*(D2:D21>1000))（统计一分店销售额超1000的次数）；

· 加权平均：=SUMPRODUCT(分数区域*权重区域)/SUM(权重区域)。

适用场景：需要多条件计算的复杂报表（比如销售排名、业绩考核），用它比VLOOKUP+SUMIFS更高效！

四、TEXT：数据格式的“魔法师”

一句话定位：Excel里“最会变装的设计师”，能把数字、日期、文本随便“改头换面”！

三大经典玩法

1. 数字格式化（货币、大写、百分比）

//添加货币符号和单位：yen100元  
=TEXT(D2,"yen0元")  

//金额大写（报销必用）：壹佰元整  
=TEXT(D2,"[DBNum2]0元")  

//百分比保留2位小数：50.00%  
=TEXT(E2,"0.00%")  

2. 日期处理（从“乱码”到“规范”）

//完整日期+星期：2025年09月17日 星期三  
=TEXT(C2,"e年mm月dd日 aaaa")  

//只显示月份：09月  
=TEXT(C2,"mm月")  

3. 条件显示（自动打标签）

//超额/未完成提醒：超额200元；还差300元；刚好达标  
=TEXT(D2-2000,"超额0元;还差0元;刚好")  

自定义格式代码：

· 0：强制显示数字（如001→001）；

· #：不显示无效0（如001→1）；

· ?：小数位对齐（如1.2和1.20都显示1.20）；

· [DBNum2]：中文大写数字（报销专用）。

五、XLOOKUP：查找界的“六边形战士”

一句话定位：VLOOKUP的“终极替代者”，能反向查、多条件查、自定义错误提示，查找界的“全能ACE”！

完整语法（记住这1行就够了）

=XLOOKUP(查找值, 查找范围, 结果范围, [找不到时显示的值], [匹配方式], [搜索模式])

六大核心优势（碾压VLOOKUP）

1. 双向查找：不用再纠结“查找值在首列还是首行”，直接查；
2. 反向查找：比如用销售额找姓名，不用做辅助列；
3. 多条件查找：直接用&连接多个条件（如"北京"&"销售"）；
4. 通配符匹配：支持*（任意字符）和?（单个字符）；
5. 自定义错误提示：找不到值时显示“未找到”，比#N/A好看100倍；
6. 搜索模式：支持从前往后、从后往前、二分查找（精确匹配）。

实战示例（直接套用）

//精确匹配：找“张三”的销售额（找不到显示“未找到”）  
=XLOOKUP("张三", A2:A100, D2:D100, "未找到", 0)  

//反向查找：用销售额85找对应的等级  
=XLOOKUP(85, B2:B100, C2:C100)  

//多条件查找：找“北京+销售”岗的负责人  
=XLOOKUP("北京"&"销售", A2:A100&B2:B100, D2:D100)  

五大函数对比表：一键选对“最优解”

实战建议：按场景选函数，效率翻倍！

• 新手入门：先学SUBTOTAL（动态统计）和XLOOKUP（查找），覆盖80%日常工作；
• 数据处理：遇到乱数据（含错误值、隐藏行），用AGGREGATE清洗；
• 报表美化：用TEXT给数字加货币符号、大写，给日期补星期，领导看了直夸专业；
• 复杂计算：多条件求和、分组排名，SUMPRODUCT比VLOOKUP+SUMIFS更高效。

学习技巧：3步成为函数高手

1. 分层学：先掌握基础用法（如SUBTOTAL求和），再学高级应用（如AGGREGATE忽略错误）；
2. 实战练：把今天的公式直接套用到自己的报表里，边用边记；
3. 组合用：比如用XLOOKUP找数据，再用TEXT美化结果，1+1＞2！

测试题：你能答对几道？

测试题1：要做动态报表，筛选“华东区”后自动统计销售额，应该用哪个函数？

A. SUMPRODUCT

B. SUBTOTAL

C. AGGREGATE

测试题2：统计时需要忽略隐藏行和#N/A错误，AGGREGATE的第二个参数应该选？

A. 1

B. 3

C. 6

测试题3：要找“张三”+“3月”的销售额，XLOOKUP的正确写法是？

A. =XLOOKUP("张三",A:A,"3月",D:D)

B. =XLOOKUP("张三"&"3月",A:A&B:B,D:D)

C. =XLOOKUP("张三",A:A,XLOOKUP("3月",B:B,D:D))

答案

1. B（SUBTOTAL支持筛选时自动忽略隐藏数据）；
2. B（参数3=忽略隐藏行、错误值、嵌套函数）；
3. B（用&连接多条件，查找范围和结果范围都用多条件组合）。

欢迎关注：千万别学excel，这里有数千篇excel相关视频和文章，带你玩转excel！祝你早日升任CEO，迎娶白富美，走上人生巅峰～(￣▽￣～)~