函数大概有500多个，常用的不到50个，特别常用的不到20个。对于90%的使用者来说，20个掌握熟练了基本就够用了。

一、为什么只需要学20个？

Excel里有500多个函数，但别被这个数字吓到。就像你手机里装了100个App，每天真正打开的可能就那5-6个。

这20个函数覆盖了日常工作中90%的数据处理场景：

下面逐一拆解，每个函数都配了真实工作场景，不是干巴巴的语法说明。

二、基础计算类——表格的"基本功"

1. SUM — 求和之王

功能：对指定范围内的数值求和。

=SUM(A1:A10)

实际场景：审计时汇总全年采购入库金额、统计部门季度总支出。

💡 快捷技巧：选中一列数字后按 Alt + =，自动填充SUM公式，比手敲快10倍。

2. AVERAGE — 均值计算

功能：计算数值的平均值。

=AVERAGE(B1:B5)

实际场景：计算供应商供货均价、员工平均绩效得分、月度平均库存周转天数。

3. MAX / MIN — 找极值

功能：返回一组数据中的最大值或最小值。

=MAX(C1:C100)' 最大值=MIN(C1:C100)' 最小值

实际场景：

●MAX：找出采购单价最高的异常订单（审计重点关注对象）

●MIN：定位库存量最低的物料（预警补货）

4. COUNT — 数字计数

功能：统计包含数字的单元格个数。

=COUNT(D3:D50)

实际场景：统计已填报数据的记录条数、核对账目行数是否完整。

⚠️ 注意：COUNT 只数数字，文本和空格不算。要数所有非空单元格用 COUNTA。

三、逻辑与条件类——让表格会"思考"

5. IF — 万能条件判断

功能：根据条件返回不同结果，Excel里最常用的逻辑函数，没有之一。

=IF(C1>60, "合格", "不合格")

实际场景：

●审计判断：=IF(差异率>5%, "⚠️ 异常", "✅ 正常")

●风险评级：=IF(评分>=80, "A级", IF(评分>=60, "B级", "C级"))

●库存预警：=IF(当前库存<安全库存, "🔴 补货", "充足")

6. SUMIF / SUMIFS — 条件求和

功能：根据一个或多个条件对数据进行求和。

' 单条件求和：计算"苹果"的总销量=SUMIF(B2:B6, "苹果", C2:C6)' 多条件求和：计算男性且薪资>6000的总薪资=SUMIFS(G2:G9, D2:D9, "男", G2:G9, ">6000")

实际场景：

●按供应商分类汇总采购额（单条件）

●按煤种+月份+仓库三维交叉汇总入库量（多条件）—— 这正是你做原料煤/燃料煤分析时的核心操作！

💡 从Excel 2007开始推荐用 SUMIFS，支持多条件，比 SUMIF 更灵活。

7. COUNTIF / COUNTIFS — 条件计数

功能：根据条件统计符合条件的单元格数量。

' 统计大于60分的记录数=COUNTIF(A1:A100, ">60")' 统计某部门中薪资超过平均值的男员工人数=COUNTIFS(D:D, "销售部", G:G, ">"&AVERAGE(G:G))

实际场景：统计某供应商出现异常报价的次数、统计审计发现问题的类别分布频次。

四、查找与匹配类——跨表关联神器

8. VLOOKUP — 垂直查找（经典但正在被淘汰）

功能：在首列查找目标值，返回同行指定列的数据。

=VLOOKUP("张三", A1:D10, 3, FALSE)' 在A1:D10的首列找"张三"，返回第3列的值

实际场景：根据供应商编码查公司全称、根据工号查姓名和部门。

⚠️ VLOOKUP的致命缺陷：只能从左往右查，列号是硬编码的（插入列后公式全部报错）。新项目建议直接用下面的INDEX+MATCH替代。

9. HLOOKUP — 水平查找

功能：在首行查找目标值，返回同列指定行的数据。

=HLOOKUP("Q1", A1:Z3, 2, FALSE)' 在第一行找"Q1"，返回第2行的对应值

实际场景：横向布局的报表中按季度/月份提取数据。实际使用频率远低于VLOOKUP。

10. INDEX + MATCH — 灵活查找的"黄金组合"

功能：INDEX负责取值，MATCH负责定位位置，组合后实现任意方向查找。

=INDEX(C:C, MATCH(F2, B:B, 0))' 先在B列找到F2的位置，再返回C列同一行的值

实际场景：这是VLOOKUP的现代替代方案，优势明显：

💡 Excel 365用户可以直接用 XLOOKUP，一行搞定，更强大。

五、文本处理类——脏数据清洗必备

11. CONCATENATE / TEXTJOIN — 文本合并

功能：将多个字符串拼接成一个。

=CONCATENATE(A1, "-", B1)' 结果："张三-001"=TEXTJOIN("-", TRUE, A1, B1, C1)' 自动忽略空格拼接

实际场景：

●将"省+市+区"合并为完整地址

●拼接物料编号（如 "YT04" & "-" & "MM" → YT04-MM）

●生成统一格式的合同编号

💡 更简单的写法：直接用 & 连接符，=A1&"-"&B1 效果一样，还不用记函数名。

12. LEFT / RIGHT / MID — 文本截取

功能：从字符串中提取指定位置的字符。

=LEFT(A1, 3)' 取左边3个字符=RIGHT(A1, 4)' 取右边4个字符=MID(A1, 2, 5)' 从第2位开始取5个字符

实际场景：从身份证号提取出生日期、从物料编码中拆分煤种编号（如从YT04MM提取YT04）、从银行账号截取后4位脱敏显示。

13. LEN — 字符长度

功能：计算文本字符串的字符数（含空格）。

=LEN("Excel")' 返回 5=LEN(A1)

实际场景：

●验证录入格式是否合规（如手机号必须11位）

●配合TRIM检测隐藏空格导致的查询失败

●数据质量检查：筛选出超长/过短的异常记录

六、日期与时间类——不再手动算天数

14. TODAY / NOW — 动态日期时间

功能：返回系统当前的日期或日期时间（每次打开文件自动更新）。

=TODAY()' 返回当天日期，如 2026-04-13=NOW()' 返回日期+时间，如 2026-04-13 10:15:30

实际场景：

●报表标题自动显示"截至今天"

●计算账龄：=TODAY()-开票日期 → 得出逾期天数

●项目进度看板中的动态倒计时

⚠️ 如果需要固定日期不自动更新，请手动输入日期或复制后粘贴为数值。

15. DATEDIF — 日期差值计算

功能：计算两个日期之间的间隔（天/月/年）。

=DATEDIF(A1, B1, "d")' 相差天数=DATEDIF(A1, B1, "m")' 相差整月数=DATEDIF(A1, B1, "y")' 相差整年数

实际场景：

●计算员工在职时长（精确到月）

●分析采购周期：从下单到入库的平均天数

●固定资产折旧年限核算

🔍 这个函数在Excel的插入函数向导里是"隐形"的（微软未正式文档化），但它一直能用且非常好使。

七、实用辅助函数——让公式更稳健

16. IFERROR — 错误兜底

功能：当公式出错时返回自定义值，避免满屏 #N/A/#DIV/0!。

=IFERROR(A1/B1, "除零错误")=IFERROR(VLOOKUP(...), "未找到")

实际场景：这是审计工作中最高频使用的辅助函数之一。当VLOOKUP找不到匹配值时返回"无数据"而不是刺眼的错误码；做除法运算时防止分母为零导致公式崩溃。

💡 最佳实践：把所有可能出错的公式外层都包一层IFERROR，专业又美观。

17. ROUND — 精度控制

功能：将数值四舍五入到指定小数位数。

=ROUND(3.14159, 2)' 返回 3.14=ROUND(3.5, 0)' 返回 4

实际场景：

●财务报表金额保留2位小数

●百分比精度统一（如差异率保留1%）

●防止浮点误差累积（0.1 + 0.2 ≠ 0.3 的经典问题）

18. RANK — 排名函数

功能：返回数值在其数据集中的排名。

=RANK(E2, $E$2:$E$9)' E2在E2:E9中的排名=RANK(E2, $E$2:$E$9, 0)' 0=降序（第1名最大），1=升序

实际场景：

●供应商评分排名（你给华源祥打83分的场景）

●销售业绩排行榜

●各煤种采购量占比排序

💡 引用范围要用绝对引用 $E$2:$E$9（按F4切换），否则拖拽公式时范围会偏移。

19. SUMPRODUCT — 多条件加权运算

功能：数组乘积后求和，支持复杂的多条件运算。

=SUMPRODUCT((A1:A10>50)*(B1:B10))' 同时满足A列>50时，对B列对应值求和

实际场景：

●加权平均计算（如各批次煤炭按重量加权平均热值）

●多维度交叉分析（无需辅助列）

●替代SUMIFS在某些特殊场景下的应用

20. FILTER（Excel 365）— 动态筛选

功能：根据条件动态筛选并返回符合条件的整行数据。

=FILTER(A1:D100, B1:B100="是")' 返回B列为"是"的所有行（含A-D列）

实际场景：

●从13万条采购记录中一键筛出某个供应商的全部交易

●动态生成异常数据清单（如差异率>5%的记录）

●做交互式仪表盘的数据源

⚠️ 此函数需要 Excel 365 或 Excel 2021，旧版本不可用。没有的话用INDEX+MATCH组合也能实现类似效果。

八、学习路径建议

不要试图一次背完20个，按以下优先级逐步掌握即可：

🟢 第一优先级（立即掌握，每天都会用到）

🟡 第二优先级（每周用到几次）

🔵 第三优先级（特定场景下救急）

九、实战练习清单

理论看再多不如动手练一遍。以下5个小练习覆盖了80%的工作场景：

1. 📊 月度汇总表：用 SUMIFS 按部门+月份双维度汇总费用支出
2. 🔍 异常排查：用 IF + COUNTIF 标注出重复录入的发票号码
3. 📋 数据匹配：用 INDEX+MATCH 将两个表的供应商信息合并
4. 🧹 数据清洗：用 LEFT/MID/TRIM 规范化杂乱的物料编码格式
5. ⏱️ 账龄分析：用 TODAY - DATEDIF 自动计算每笔应收款的逾期天数

十、写在最后

Excel的强大不在于你背下了多少函数，而在于遇到问题时能快速想到用什么工具解决。

这20个函数就是你的工具箱里的"瑞士军刀"。熟练之后你会发现：

以前花半天人工翻阅的凭证，现在一个SUMIFS公式3秒出结果。

这不是夸张——这正是AI时代之前，Excel带给每个职场人的第一次"效率革命"。而如今，AI正在开启第二次革命（详见上篇文章《AI赋能内部审计》）。

参考来源：DeepSeek推荐的Excel高频函数整理，结合审计与财务工作实践场景改写

觉得有用？点赞 + 收藏，下次找公式不用再百度了 👇