Excel五大&_B站影视

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

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

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

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

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

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

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

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

两大隐藏技能

智能筛选统计：用它统计时，被筛选掉的行会自动“隐身”，只算你看到的数据。比如筛出“华东区”销售，SUBTOTAL只会统计这部分人的业绩。双重模式：代码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. 数字格式化（货币、大写、百分比）

//添加货币符号和单位：¥100元 =TEXT(D2,"¥0元") //金额大写（报销必用）：壹佰元整 =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）

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

实战示例（直接套用）

//精确匹配：找“张三”的销售额（找不到显示“未找到”） =XLOOKUP("张三", A2:A100, D2:D100, "未找到", 0) //反向查找：用销售额85找对应的等级 =XLOOKUP(85, B2:B100, C2:C100) //多条件查找：找“北京+销售”岗的负责人 =XLOOKUP("北京"&"销售", A2:A100&B2:B100, D2:D100)

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

函数最适合场景版本要求学习难度SUBTOTAL动态筛选统计所有版本⭐⭐AGGREGATE复杂数据统计（含错误值）Excel 2010+⭐⭐⭐⭐SUMPRODUCT多条件计算（求和/排名）所有版本⭐⭐⭐TEXT数据格式化（数字/日期）所有版本⭐⭐⭐XLOOKUP查找（多条件/反向）Excel 365/WPS⭐⭐

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

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

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

分层学：先掌握基础用法（如SUBTOTAL求和），再学高级应用（如AGGREGATE忽略错误）；实战练：把今天的公式直接套用到自己的报表里，边用边记；组合用：比如用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的正确写法是？