夜雨聆风同一个 Excel 表格，有人算了半天算不对，有人一个公式下去，三秒出结果。
差距在哪？不是谁更聪明，而是谁手里的"武器"更趁手。
Excel 里有几百个函数，但真正干活用的，翻来覆去就那么 30 个。掌握了它们，90% 的办公场景你都能一招搞定。
今天这篇文章，我把这 30 个函数按7 大类拆开讲：每个函数的语法、用法、真实办公案例，一次给你讲透。建议收藏，用到的时候翻出来对照。
第一类：基础统计——数据汇总的"四则运算"
1. SUM：求和
语法：=SUM(数据区域)
场景：算一个月的销售总额、一周的支出合计。
案例：=SUM(C2:C31)——把 C2 到 C31 的所有数字加起来。
注意：SUM 只认数字，文本和空单元格会被自动忽略。如果单元格里有"100元"这种带文字的内容，SUM 会把它当 0 处理。
2. AVERAGE：求平均值
语法：=AVERAGE(数据区域)
场景：算平均客单价、平均响应时间、平均出勤率。
案例：=AVERAGE(D2:D100)——算 100 个订单的平均金额。
注意：AVERAGE 会把 0 算进去，但不算空格。如果某个单元格填了 0（比如退款订单），它会拉低平均值。想排除 0，用 =AVERAGEIF(D2:D100,"<>0")。
3. COUNT / COUNTA：计数
语法：
=COUNT(数据区域)——只数数字单元格 =COUNTA(数据区域)——数非空单元格，文本、数字都算
场景：COUNT 用来统计有多少人填了成绩（数字）；COUNTA 用来统计签到表有多少人签了名（不管是名字还是工号）。
案例：=COUNTA(A2:A50)——A 列有多少个单元格有内容。
补充：=COUNTBLANK(C2:C100)——反过来，数有多少个空格。
4. MAX / MIN：最大值和最小值
语法：=MAX(数据区域) / =MIN(数据区域)
场景：找最高销售额、最低报价、最晚交货日期。
案例：=MAX(E2:E200)——找出 200 个订单里金额最大的那笔。
5. RANK.EQ：排名
语法：=RANK.EQ(数值, 比较区域, [排序方式])
排序方式：0 或省略 = 降序（最大的排第1）；1 = 升序（最小的排第1）。
场景：给销售业绩排名、给考试成绩排名。
案例：=RANK.EQ(D2, D$2:D$100, 0)——D2 的销售额在全部 100 个数据中排第几。
注意：注意美元符号 $——D$2:D$100 表示下拉公式时范围不会跑偏。这是 Excel 里"绝对引用"的写法，新手一定要记住。
第二类：条件统计——"满足条件才算"
6. SUMIF：单条件求和
语法：=SUMIF(条件区域, 条件, 求和区域)
场景：只算"华东区"的销售额，只算"已完成"项目的工时。
案例：=SUMIF(A2:A100, "华东", E2:E100)——A 列是"华东"的那些行，对应的 E 列金额加在一起。
进阶写法：条件里可以用比较运算符：
=SUMIF(E2:E100, ">10000")——金额超过 1 万的加起来 =SUMIF(B2:B100, ">=2026-01-01", E2:E100)——2026 年以后的订单金额合计
7. SUMIFS：多条件求和
语法：=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2, ...)
注意：SUMIFS 的求和区域放在第一个参数，跟 SUMIF 不一样，这是很多人写错的地方。
场景：算"华东区"+"产品A"的销售额；算"3月份"+"技术部"+"已完成"的项目金额。
案例：=SUMIFS(E2:E500, A2:A500, "华东", B2:B500, "产品A", C2:C500, ">=2026-03-01")
三个条件同时满足：区域是华东、产品是 A、日期在 3 月之后。
8. COUNTIF：单条件计数
语法：=COUNTIF(条件区域, 条件)
场景：统计有多少人的成绩 >=60 分；统计"在职"状态有多少人。
案例：=COUNTIF(D2:D200, ">=60")——D 列中及格的人数。
通配符用法：=COUNTIF(A2:A100, "张*")——统计所有姓"张"的人。* 代表任意多个字符。
9. COUNTIFS：多条件计数
语法：=COUNTIFS(条件区域1, 条件1, 条件区域2, 条件2, ...)
场景：统计"销售部"+"本月签到满 22 天"的人数。
案例：=COUNTIFS(B2:B200, "销售部", C2:C200, ">=22")
10. AVERAGEIF：条件求平均
语法：=AVERAGEIF(条件区域, 条件, 平均区域)
场景：算"上海"门店的平均客单价；算"及格以上"成绩的平均分。
案例：=AVERAGEIF(A2:A100, "上海", F2:F100)——A 列是上海的行，F 列客单价的平均值。
第三类：逻辑判断——让表格"会思考"
11. IF：条件判断
语法：=IF(条件, 条件为真时的值, 条件为假时的值)
场景：成绩 >=60 显示"及格"，否则"不及格"；销售额达标显示"完成"，否则"未达标"。
案例：=IF(D2>=60, "及格", "不及格")
嵌套用法：=IF(D2>=90, "优秀", IF(D2>=75, "良好", IF(D2>=60, "及格", "不及格")))
三层嵌套就能做四档评级。但嵌套超过三层，建议改用 VLOOKUP 区间查找或 IFS 函数（Excel 2019+），不然公式长得自己都看不懂。
12. IFERROR：错误兜底
语法：=IFERROR(公式, 出错时显示的值)
场景：VLOOKUP 找不到会返回 #N/A，很难看。套一层 IFERROR，让它显示"未找到"或者空白。
案例：=IFERROR(VLOOKUP(A2, 数据表!A:C, 3, 0), "未找到该记录")
注意：IFERROR 会把所有错误都吃掉——包括你的公式写错导致的 #VALUE!、#REF! 等。排查问题时，建议先去掉 IFERROR，看到底报什么错。
13. AND / OR：多条件逻辑
语法：
=AND(条件1, 条件2, ...)——所有条件都满足才返回 TRUE =OR(条件1, 条件2, ...)——满足任一条件就返回 TRUE
场景：配合 IF 使用： =IF(AND(D2>=60, E2>=60), "双科及格", "有不及格")
实际用法：AND 和 OR 很少单独用，它们几乎总是嵌在 IF 里面，充当"多重条件"的角色。
第四类：查找引用——Excel 最核心的能力
14. VLOOKUP：纵向查找
语法：=VLOOKUP(查找值, 数据区域, 返回第几列, 匹配方式)
匹配方式：0 或 FALSE = 精确匹配（99% 的场景用这个）；1 或 TRUE = 近似匹配。
场景：根据工号查姓名、根据产品编号查价格、根据手机号查客户信息。
案例：=VLOOKUP(A2, 员工表!A:D, 3, 0)——在员工表的 A 列找 A2 的值，返回同行的第 3 列（姓名）。
VLOOKUP 的三个痛点：
查找值必须在数据区域的第一列，不能向左查 返回的是"第几列"这个数字，中间插了一列后，公式全错 默认是近似匹配，忘了写 0 就悄悄给你错误结果
15. XLOOKUP：新一代查找之王
语法：=XLOOKUP(查找值, 查找区域, 返回区域, [找不到时显示什么], [匹配模式], [搜索方向])
前三个参数必填，后三个可选。
案例：=XLOOKUP(A2, 员工表!A:A, 员工表!C:C, "未找到")
XLOOKUP 比 VLOOKUP 强在哪：
查找区域和返回区域各自独立，不用数第几列 支持向左查找（VLOOKUP 做不到） 内置"找不到时显示什么"的参数，不用套 IFERROR 默认就是精确匹配，不会悄悄出错 支持一次返回多列： =XLOOKUP(A2, B:B, C:E)一次返回三列
多条件查找：=XLOOKUP("销售部"&"张三", A2:A100&B2:B100, D2:D100)
区间查找（评级）：=XLOOKUP(82, {0,60,75,90}, {"不及格","及格","良好","优秀"}, , -1)——返回"良好"。
注意：XLOOKUP 需要 Excel 2021 或 Microsoft 365。老版本用不了，用 INDEX+MATCH 代替。
16. INDEX：按位置取值
语法：=INDEX(数据区域, 第几行, [第几列])
场景：INDEX 本身很简单——"第 3 行第 2 列的值是多少"。但它和 MATCH 组合起来，就是 Excel 最强大的查找方案。
案例：=INDEX(C2:C100, 5)——返回 C 列第 5 个值。
17. MATCH：定位查找
语法：=MATCH(查找值, 查找区域, [匹配方式])
匹配方式：0 = 精确匹配；1 = 小于等于查找值的最大值（需升序排列）；-1 = 大于等于查找值的最小值（需降序排列）。
场景：MATCH 不返回值，它只返回"在第几个位置"。单独用没意义，但配合 INDEX 就无敌了。
INDEX + MATCH 黄金组合：=INDEX(C2:C100, MATCH(A2, B2:B100, 0))
含义：在 B 列找到 A2 的位置，然后返回 C 列同一个位置的值。效果等同于 VLOOKUP，但不受列顺序限制，中间插列也不会出错。
18. OFFSET：动态引用
语法：=OFFSET(基准单元格, 向下偏移几行, 向右偏移几列, [返回几行], [返回几列])
场景：做动态图表时，让数据范围自动跟随最新月份；做滚动窗口平均值。
案例：=SUM(OFFSET(A1, 1, 0, COUNTA(A:A)-1, 1))——自动求 A 列所有数据的合计，不管数据增加到多少行。
注意：OFFSET 是易失性函数，每次表格有任何变动它都会重算。数据量大时会影响性能，谨慎使用。
第五类：文本处理——跟字符串较劲
19. LEFT / RIGHT / MID：截取字符
语法：
=LEFT(文本, 截取长度)——从左截取 =RIGHT(文本, 截取长度)——从右截取 =MID(文本, 起始位置, 截取长度)——从中间截取
案例：
=LEFT(A2, 3)——提取产品编号的前 3 位地区码 =RIGHT(B2, 4)——提取手机号后 4 位 =MID(C2, 7, 8)——从身份证号第 7 位截取 8 位出生日期
20. LEN：字符长度
语法：=LEN(文本)
场景：检查手机号是不是 11 位、身份证号是不是 18 位、输入的内容是否超长。
案例：=IF(LEN(A2)=11, "位数正确", "请检查手机号")
补充：=LENB(文本)——计算字节数。一个汉字算 2 字节，英文字母算 1 字节。可以用来判断有没有输入中文。
21. TRIM：去除多余空格
语法：=TRIM(文本)
场景：从其他系统导出的数据，经常带有莫名其妙的空格，导致 VLOOKUP 匹配不上。TRIM 可以清除前后空格，并把中间的连续空格压缩为一个。
案例：=TRIM(A2)
组合用法：=VLOOKUP(TRIM(D2), TRIM(员工表!A:A), 2, 0)——查找前先清理空格，大幅提升匹配成功率。
22. CONCAT / TEXTJOIN：文本拼接
语法：
=CONCAT(文本1, 文本2, ...)——直接拼接，没有分隔符 =TEXTJOIN(分隔符, 是否忽略空值, 文本1, 文本2, ...)——带分隔符拼接
案例：
=CONCAT(A2, "-", B2)——拼出 "华东-产品A" =TEXTJOIN("，", TRUE, A2:A10)——把 A2 到 A10 的内容用中文逗号连起来，自动跳过空格
注意：老版本 Excel 用的是 CONCATENATE，功能一样但写法更啰嗦。新版直接用 CONCAT 或 & 符号都行。
23. SUBSTITUTE / REPLACE：文本替换
语法：
=SUBSTITUTE(文本, 旧内容, 新内容, [第几个])——按内容替换 =REPLACE(文本, 起始位置, 替换几个字符, 新内容)——按位置替换
区别：SUBSTITUTE 是"找到这个字换掉"，REPLACE 是"从第几个字开始换掉"。
案例：
=SUBSTITUTE(A2, "北京", "上海")——把文本里的"北京"换成"上海" =REPLACE(B2, 7, 8, "********")——把身份证号中间 8 位替换成星号（脱敏处理）
第六类：日期与时间——时间就是数据
24. TODAY / NOW：当前日期和时间
语法：
=TODAY()——返回今天的日期（不含时间） =NOW()——返回当前日期+时间
场景：自动计算"距今多少天"、"还有几天到期"。
案例：=DATEDIF(A2, TODAY(), "D")——从 A2 的日期到今天过了多少天。
注意：TODAY 和 NOW 是"易失性函数"，每次打开表格或有任何变动都会更新。如果需要固定某个日期，按 Ctrl+; 直接输入，不要用函数。
25. DATEDIF：日期差
语法：=DATEDIF(开始日期, 结束日期, "单位")
单位："Y" = 年数、"M" = 月数、"D" = 天数。
场景：算工龄、算合同剩余天数、算项目历时。
案例：
=DATEDIF(A2, TODAY(), "Y")——从 A2 入职日期到现在工作了多少年 =DATEDIF(TODAY(), B2, "D")——距离 B2 的截止日期还有多少天
注意：DATEDIF 是 Excel 里的"隐藏函数"，输入时没有提示，但确实能用。
26. EOMONTH：月末日期
语法：=EOMONTH(开始日期, 往后几个月)
场景：算还款日、算到期日、算"这个月最后一天"。
案例：
=EOMONTH(A2, 0)——A2 所在月份的最后一天 =EOMONTH(A2, 1)——A2 的下个月的最后一天 =EOMONTH(A2, 0)+1——下个月的第一天（常用于生成月份起止区间）
第七类：动态数组新函数——Excel 的未来
以下函数需要 Excel 2021 或 Microsoft 365，WPS 最新版也已跟进。
27. FILTER：按条件筛选整片数据
语法：=FILTER(返回区域, 条件, [无结果时显示什么])
场景：筛选"销售部"的所有记录；筛选"金额>10万"的大额订单。
案例：=FILTER(A2:D500, B2:B500="华东", "无匹配数据")
多条件：用 * 表示"且"，用 + 表示"或"：
=FILTER(A2:D500, (B2:B500="华东")*(D2:D500>100000))——华东区且金额超 10 万 =FILTER(A2:D500, (B2:B500="华东")+(B2:B500="华南"))——华东或华南
注意：第三个参数一定要写！否则没有匹配结果时会报 #CALC! 错误。
28. UNIQUE：一键去重
语法：=UNIQUE(数据区域, [按列], [仅出现一次])
场景：提取所有不重复的客户名单；找出只来过一次的新客户。
案例：
=UNIQUE(B2:B500)——提取 B 列所有不重复的值 =UNIQUE(B2:B500, , TRUE)——只保留恰好出现一次的值（找出"一次性客户"）
组合用法：=SORT(UNIQUE(B2:B500))——去重并自动排序。
29. SORT / SORTBY：公式排序
语法：
=SORT(数据区域, 排序依据第几列, 1升序/-1降序)=SORTBY(返回区域, 排序依据区域, 1升序/-1降序)
场景：按销售额从高到低排列；按交期从近到远排序。
案例：
=SORT(A2:D100, 4, -1)——按第 4 列降序排列 =SORTBY(A2:A100, D2:D100, -1)——只返回姓名列，但按销售额降序排（不用把金额列也显示出来）
核心优势：源数据不会被改动，排序结果在另一个区域生成。再也不用担心"有人排了序把原始数据搞乱"。
30. TEXTSPLIT / TEXTBEFORE / TEXTAFTER：文本拆分三剑客
语法：
=TEXTSPLIT(文本, 分隔符)——按分隔符拆成多列 =TEXTBEFORE(文本, 标记)——提取标记之前的内容 =TEXTAFTER(文本, 标记)——提取标记之后的内容
场景：拆分"省-市-区"地址；从"张三-男-28"中提取性别；从 URL 中提取域名。
案例：
=TEXTSPLIT(A2, "-")——"张三-男-28" 拆成三列：张三 / 男 / 28 =TEXTBEFORE("江苏省南京市玄武区", "省")——返回"江苏" =TEXTAFTER("江苏省南京市玄武区", "市")——返回"玄武区"
对比老方法：以前拆分文本要用 LEFT+FIN+MID 三个函数嵌套，现在一个 TEXTSPLIT 搞定。
30 个函数速查表
这 30 个函数，不需要你一次全记住。
建议分三步走：
第一步：先把 SUM、AVERAGE、COUNT、IF、VLOOKUP 这 5 个吃透。这 5 个能覆盖 70% 的日常需求。打开你手边的表格，找个地方试一遍，比看十篇文章管用。
第二步：学会 SUMIFS + IFERROR 的组合。条件求和+错误兜底，这两个加上去，你的表格就从"能用"升级到"好用"。
第三步：如果你的 Excel 版本支持动态数组，重点学 XLOOKUP、FILTER、UNIQUE。这三个函数会让你的效率直接翻倍，而且写出来的公式比老方法短得多、好懂得多。
函数不在多，够用就行。但每一个你学会的函数，都会在未来某个加班的夜晚，帮你早回家一个小时。
收藏这篇文章，下次用到哪个，翻出来对照。