36个Excel神仙公式｜HR/财务/销售通用，少走3年弯路

写在前面：本文整理了 Excel 办公最高频、最实用的 36 个公式，按 9 大业务场景（S1～S9）重新归类，每个公式都给出「使用场景 + 完整公式 + 通俗讲解」。不堆术语、不绕弯子，看完直接就能用。无论你是 HR、财务、销售、PM，还是刚接触 Excel 的新人，收藏这一篇，相当于拥有了一本「随时可查的 Excel 函数速查手册」。

一、为什么要学这 36 个 Excel 公式？

在职场中，真正决定你工作效率的，往往不是 Office 操作熟练度，而是 「能不能用一个公式解决一类问题」。

• 财务每个月要算几十个分公司的销售合计？——用 SUMIFS 一次搞定。
• HR 要从几千行打卡记录里筛出「销售部 + 迟到」？——用 COUNTIFS 一步到位。
• 销售要根据客户姓名反查产品线？——用 XLOOKUP 比 VLOOKUP 简单十倍。
• PM 要看本月最后一天是哪天？——EOMONTH 一键出结果。

这 36 个公式不是「大而全的函数手册」，而是 「在真实业务里被反复用到、且能直接抄走」 的神仙公式。覆盖 9 大场景：

场景编号	场景名称	解决的问题
S1	多维统计	多条件求和、计数、加权平均
S2	逻辑判断	自动化判定、错误清洗、分支选择
S3	查找引用	跨表查值、反向查找、模糊匹配
S4	文本清洗	去空格、拼接、提取邮箱/身份证
S5	日期与工时	项目工期、年龄工龄、月底结账
S6	动态数组	一键去重、动态筛选、自动排序
S7	高阶引用与排版	二级联动、动态图表、行列互换
S8	数据修饰与极值	格式美化、四舍五入、隔行填色
S9	辅助运算与逻辑补遗	排行榜、长度校验、自动编号、名单核对

下面逐一拆解。

---

二、S1 多维统计：告别手工计算器

一句话价值：掌握这 4 个函数，能解决日常报表中 80% 的统计问题，是「统计之王」。

1. SUMIFS – 多条件求和

• 使用场景：财务/销售核心。计算「华东区」且「A 产品」且「1 月份」的销售总额。
• 完整公式：=SUMIFS(C:C, A:A, "华东", B:B, "A产品")
• 通俗讲解：支持 127 对条件（最多 127 个区域+条件组合），是「多条件求和」的事实标准。比 SUMIF 强大得多，是做报表必学的「统计之王」。

2. COUNTIFS – 多条件计数

• 使用场景：考勤/HR 核心。统计「销售部」且「迟到」的人数。
• 完整公式：=COUNTIFS(B:B, "销售部", C:C, "迟到")
• 通俗讲解：数据透视表的底层逻辑之一，用于「快速盘点满足多个条件的有多少条」。配合 SUMIFS 是 HR/财务的黄金组合。

3. SUMPRODUCT – 万能乘积求和

• 使用场景：解决加权平均、不通过辅助列直接计算「单价 × 数量」的总和。
• 完整公式：=SUMPRODUCT(A2:A10, B2:B10)
• 通俗讲解：数组函数的鼻祖。无需三键（Ctrl+Shift+Enter）即可执行数组运算，逻辑强大。常用于「按权重计算总分」「按条件求和（早期替代 SUMIFS）」。

4. SUBTOTAL – 可见数据统计

• 使用场景：筛选后只对显示出来的数据求和/计数，自动忽略被筛选掉的行。
• 完整公式：=SUBTOTAL(109, B2:B100)
• 通俗讲解：参数 9 或 109 代表 SUM，是 唯一能「看见」筛选状态 的函数。109 还会忽略手动隐藏的行，比 9 更稳健。

---

三、S2 逻辑判断：构建自动化流程

一句话价值：让表格「拥有决策能力」，是自动化的基石。

5. IF – 逻辑判断基础

• 使用场景：核心必修。判断业绩是否达标，达标发钱，不达标谈话。
• 完整公式：=IF(B2>=10000, "达标", "未达标")
• 通俗讲解：一切自动化判断的基石。条件成立返回第二个参数，否则返回第三个参数。注意第三个参数不可省略，否则不满足时会返回 FALSE。

6. IFS – 多条件分支

• 使用场景：Excel 365/2019/WPS 新贵。解决 IF 层层嵌套的噩梦，用于多档位奖金或评级计算。
• 完整公式：=IFS(A2>90, "优", A2>80, "良", A2>60, "及格", TRUE, "差")
• 通俗讲解：逻辑清晰，从左到右依次判断，满足即停。TRUE 表示兜底默认值，避免遗漏分支报错。

7. IFERROR – 错误值清洗

• 使用场景：专门处理 #DIV/0!、#N/A 等难看的错误代码，保持报表专业美观。
• 完整公式：=IFERROR(A2/B2, 0)
• 通俗讲解：如果出错了，就显示 0（或空白）。是公式排错的 最后一道防线，建议在所有对外报表的公式外层都套上。

8. SWITCH – 精确匹配开关

• 使用场景：比 IF 更高效的点对点判断。将 1 换成「周一」，2 换成「周二」。
• 完整公式：=SWITCH(A2, 1, "周一", 2, "周二", "未知")
• 通俗讲解：针对特定值的替换，比 IF 嵌套更简洁，类似编程中的 Case 语句。最后一个参数是兜底值。

---

四、S3 查找引用：Excel 的灵魂

一句话价值：职场通用语言，没学过查找引用，等于没学过 Excel。

9. VLOOKUP – 大众情人

• 使用场景：就算有新函数，它依然是职场通用语言。根据姓名找工资，最基础的匹配。
• 完整公式：=VLOOKUP(E2, A:C, 3, 0)
• 通俗讲解：必须强调第 4 参数设为 0（精确匹配），这是新手最容易踩的坑。1 代表近似匹配（适合区间查找，但极容易出错）。注意：VLOOKUP 只能向右查找，无法向左。

10. XLOOKUP – 终极查找

• 使用场景：Excel 365 杀手锏。向左查、查不到返回自定义文字、从下往上查，全能。
• 完整公式：=XLOOKUP(E2, A:A, B:B, "查无此人")
• 通俗讲解：不用数第几列，直接选列；不仅不会错，还能顺手处理错误值。是 VLOOKUP 的完美替代品，强烈建议新项目直接用 XLOOKUP。

11. INDEX + MATCH – 黄金搭档

• 使用场景：在旧版本中实现「反向查找」和「双向交叉查询」的唯一解。
• 完整公式：=INDEX(C:C, MATCH(E2, A:A, 0))
• 通俗讲解：MATCH 负责找「在第几行」，INDEX 负责「把那行的数据抓出来」。组合后能实现 VLOOKUP 做不到的左向查询，且运行效率高。

12. LOOKUP – 向量查找

• 使用场景：解决「查找最后一次出现的记录」或「根据分数区间模糊匹配」的神器。
• 完整公式：=LOOKUP(1, 0/(A:A=E2), B:B)
• 通俗讲解：经典的二分法逻辑，虽然老，但在处理「最后一条」数据时依然无敌。0/(条件) 是个经典套路，把布尔值转成 0/错误值，再用 LOOKUP 找到最后一个非错误值。

---

五、S4 文本清洗：脏数据克星

一句话价值：VLOOKUP 查不到 90% 的原因，都可以用这一章解决。

13. TRIM – 去除幽灵空格

• 使用场景：VLOOKUP 查不到往往是因为后面带了空格，此函数一键清除首尾空格。
• 完整公式：=TRIM(A2)
• 通俗讲解：数据清洗第一步，只保留单词中间必要的空格，删除首尾所有空格。注意：对全角空格无效，全角空格需用 SUBSTITUTE 配合 CLEAN 联合处理。

14. TEXTJOIN – 强力拼接

• 使用场景：完胜 & 和 CONCAT。将一列数据合并并在中间加上逗号，还能忽略空单元格。
• 完整公式：=TEXTJOIN(", ", TRUE, A2:A20)
• 通俗讲解：第二参数 TRUE 表示忽略空单元格。制作名单汇总、关键词标签云、生成 SQL IN 列表时必备。

15. FIND – 智能定位

• 使用场景：确定某个字符（如 @ 或 -）在第几位，通常配合 MID 使用。
• 完整公式：=FIND("@", A2)
• 通俗讲解：区分大小写。如果是提取邮箱用户名，必须先用它定位 @ 的位置，再交给 MID 截取。

16. MID – 中段截取

• 使用场景：身份证提生日、或从固定编码中提取产品线代码。
• 完整公式：=MID(A2, 7, 8)
• 通俗讲解：配合 FIND 可实现动态截取，比 LEFT/RIGHT 更具普适性。身份证第 7 位开始的 8 位正是出生年月日（格式 YYYYMMDD）。

---

六、S5 日期与工时：HR/PM 必备

一句话价值：所有和「天数」相关的计算，这一章全搞定。

17. WORKDAY – 项目截止日

• 使用场景：项目今天开始，工期 10 个工作日，哪天交付？自动跳过周末。
• 完整公式：=WORKDAY(TODAY(), 10)
• 通俗讲解：还可以添加第三参数（节假日列表），把「法定节假日」也排除在外。是 PM 排期的神器。

18. NETWORKDAYS – 净工作日

• 使用场景：计算员工实际出勤天数，或两个日期之间间隔了多少个工作日。
• 完整公式：=NETWORKDAYS(开始日, 结束日)
• 通俗讲解：算工资、算工期必备，自动扣除周六日。同样支持第三参数（节假日列表）。

19. DATEDIF – 隐藏神技

• 使用场景：专门计算两个日期相差的整年数（年龄）、整月数（工龄）。
• 完整公式：=DATEDIF(入职日, TODAY(), "Y")
• 通俗讲解：Excel 帮助文档里搜不到它（属于「隐藏函数」），但它是计算年龄最准确的方法。单位参数："Y" 年 / "M" 月 / "D" 天。

20. EOMONTH – 账期计算

• 使用场景：计算本月、下月或上月的最后一天。
• 完整公式：=EOMONTH(A2, 1)
• 通俗讲解：获取下个月月底的日期。0 表示本月最后一天，-1 表示上月最后一天。财务结账、合同到期日计算常用。

---

七、S6 动态数组：Excel 365 & 最新版 WPS（一个公式生成一张表）

一句话价值：动态数组是 Excel 近年最大的革命，学会等于提前 3 年。

21. UNIQUE – 智能去重

• 使用场景：瞬间提取客户名单中的不重复项，实时更新。
• 完整公式：=UNIQUE(A2:A100)
• 通俗讲解：以前需要「复制 – 粘贴 – 删除重复值」三步，现在一步到位且动态关联。新增数据时，结果自动更新。

22. FILTER – 万能筛选

• 使用场景：根据条件提取所有行到新区域，相当于「高级筛选」的公式版。
• 完整公式：=FILTER(A2:C20, B2:B20="研发部")
• 通俗讲解：制作动态看板的核心，一旦源数据变动，筛选结果自动变。注意：此函数为 Excel 365/2021 及最新 WPS 专属，旧版需用 INDEX+SMALL+IF 组合替代。

23. SORT – 自动排序

• 使用场景：让数据永远保持从大到小排列，无需手动点击按钮。
• 完整公式：=SORT(A2:B20, 2, -1)
• 通俗讲解：第三参数 -1 代表降序，1 代表升序。能够与 FILTER 嵌套使用，生成「销售前十名排行榜」。

24. VSTACK – 堆叠合并

• 使用场景：你还在手动复制粘贴把 1 月、2 月的数据拼在一起吗？
• 完整公式：=VSTACK(一月!A2:C10, 二月!A2:C10)
• 通俗讲解：垂直堆叠数组，将分散的表格瞬间合并成一张总表。横向合并用 HSTACK。注意：跨表引用时表名要带 !。

---

八、S7 高阶引用与排版：骨灰级玩家

一句话价值：做出「会动」的报表和目录页，全靠这一章。

25. INDIRECT – 动态引用

• 使用场景：制作二级下拉菜单，或根据单元格里的文本（如 Sheet2）去引用对应工作表。
• 完整公式：=INDIRECT(A2&"!B2")
• 通俗讲解：将「文本字符串」转化为「真实的引用地址」，动态引用的核心。注意：当目标工作表关闭时会报错 REF!，建议加 IFERROR 兜底。

26. OFFSET – 偏移引用

• 使用场景：制作动态图表，定义一个会随数据增加而自动长高的区域。
• 完整公式：=OFFSET(A1, 0, 0, COUNTA(A:A), 1)
• 通俗讲解：虽然较难理解，但是定义动态名称管理器（Name Manager）的最佳方案。参数顺序：基准、向下偏移、向右偏移、高度、宽度。

27. TRANSPOSE – 行列转置

• 使用场景：把横排的数据变成竖排，或者反之。
• 完整公式：=TRANSPOSE(A1:E1)
• 通俗讲解：特别适合将打印样式不好的宽表转换为适合数据库的长表。注意：动态数组版本会自动溢出，旧版需用 Ctrl+Shift+Enter 三键确认。

28. HYPERLINK – 超链接导航

• 使用场景：制作目录页，点击单元格直接跳转到对应的发票扫描件或工作表。
• 完整公式：=HYPERLINK("#Sheet2!A1", "查看详情")
• 通俗讲解：提升表格交互体验的神器。#Sheet2!A1 是工作表内部跳转语法，外部链接直接写 URL 即可。

---

九、S8 数据修饰与极值：细节决定成败

一句话价值：这些是「内行看门道」的细节，处理不到位会被领导一眼看穿。

29. TEXT – 万能格式化

• 使用场景：将日期变成「星期几」，或将数值变成 001 这种文本编号。
• 完整公式：=TEXT(A2, "aaaa")
• 通俗讲解：Excel 里的「整容医生」，只改变显示样貌，常用于标题拼接。常用格式码："yyyy-mm-dd"、"0.00%"、"#,##0.00"。

30. SUBSTITUTE – 定点替换

• 使用场景：批量将「2023」替换为「2024」，或将地址中的「省」字去掉。
• 完整公式：=SUBSTITUTE(A2, " ", "")
• 通俗讲解：比 REPLACE 更直观，它是基于 内容 替换，而非基于位置。第四参数可指定「第几次出现」。

31. MOD – 循环与余数

• 使用场景：计算余数。常用于条件格式（隔行填色）、判断奇偶性、计算工时零头。
• 完整公式：=MOD(ROW(), 2)
• 通俗讲解：结果为 1 或 0，配合条件格式可制作漂亮的斑马纹表格。判断奇偶：=MOD(A2,2)=0 即偶数。

32. ROUND – 精确舍入

• 使用场景：财务死命令。必须四舍五入保留 2 位小数，防止汇总差 1 分钱。
• 完整公式：=ROUND(A2*B2, 2)
• 通俗讲解：区别于「设置单元格格式」（那只是显示效果），ROUND 真正改变了数字的值。向下取整 ROUNDDOWN、向上取整 ROUNDUP，按需选用。

---

十、S9 辅助运算与逻辑补遗：数据校验与自动化编号

一句话价值：4 个万能辅助，解决「数据校验、排名、自动编号、名单核对」。

33. LARGE / SMALL – 极值分析

• 使用场景：找出「排名前 3 的销售额」求和，或者找出「剔除最低分」后的平均值。
• 完整公式：=LARGE(B:B, 3)
• 通俗讲解：比 MAX/MIN 灵活，配合 {1,2,3} 数组参数，是做动态排行榜的利器。SMALL 取第 K 小的值，常用于「去极值后求平均」。

34. LEN – 长度校验

• 使用场景：数据录入的一道防线。检查手机号是否为 11 位，身份证是否为 18 位。
• 完整公式：=IF(LEN(A2)=11, "正常", "异常")
• 通俗讲解：简单却极其重要，是数据清洗和验证的第一步。配合 IF 立刻生成「异常清单」。

35. ROW / COLUMN – 自动编号

• 使用场景：制作「删除行后序号自动连续」的序号列，或 VLOOKUP 时不想数第几列。
• 完整公式：=ROW()-1
• 通俗讲解：利用当前行号生成序号，无论怎么删除行、排序，序号永远是 1,2,3… 顺次排列。COLUMN() 用于列号，配合 ADDRESS 可生成动态单元格地址。

36. COUNTIF 变体 – 存在性校验

• 使用场景：核对两个表名单。不需要知道「有几个」，只需要知道「有没有」。
• 完整公式：=IF(COUNTIF(A:A, B2)>0, "存在", "缺失")
• 通俗讲解：利用计数是否大于 0 来判断逻辑真假，是两表核对最经典的方法。配合条件格式可一键高亮「缺失项」。

---

十一、Excel 新手必看：3 步学习路径建议

36 个公式不用一次全背，按下面顺序学，效率最高：

1. 第一阶段（1 周）：先吃透 S1 的 SUMIFS / COUNTIFS，S3 的 VLOOKUP，S2 的 IF / IFERROR。这 5 个函数能解决 50% 的日常报表问题。
2. 第二阶段（2 周）：补齐 S4 文本清洗（TRIM / TEXTJOIN）、S5 日期（WORKDAY / EOMONTH）、S8 修饰（TEXT / ROUND）。覆盖 80% 场景。
3. 第三阶段（持续）：拥抱 S6 动态数组（UNIQUE / FILTER / SORT / VSTACK），配合 S7 高级引用（INDIRECT / OFFSET），做到「一个公式生成一张表」。

---

十二、常见问题 FAQ

Q1：Excel 新手应该先学哪几个公式？

A：建议按以下顺序：IF（逻辑基础）→ SUMIFS（多条件求和）→ VLOOKUP（查找引用）→ IFERROR（错误清洗）→ TEXT（格式化）。这 5 个函数能解决日常 50% 的报表问题，再视工作需要扩展。

Q2：VLOOKUP 和 XLOOKUP 到底学哪个？

A：如果你用的是 Excel 365 / 2021 / 最新版 WPS，直接学 XLOOKUP——它能向左查、能自定义报错、写法更简洁。如果你发出去的表格要给老版本（Excel 2016 及以前）打开，继续用 VLOOKUP 兜底。两者建议都学，因为职场中无法预判对方用什么版本。

Q3：动态数组函数（FILTER / UNIQUE / SORT）在旧版 Excel 怎么替代？

A：在 Excel 2016 及以下版本没有动态数组。UNIQUE 可用「数据 → 删除重复值」替代；FILTER 可用「高级筛选」或 INDEX+SMALL+IF 数组公式；SORT 可用「数据 → 排序」。但强烈建议升级到 Excel 365，动态数组带来的效率提升是数量级的。

Q4：公式输入后只显示公式文本不显示结果怎么办？

A：99% 是单元格被设成了「文本格式」。选中单元格 → 「开始」选项卡 → 把格式改为「常规」→ 按 F2 进入编辑 → 回车。或者检查是否不小心输入了 '（英文单引号）开头。同时检查视图设置里「显示公式」是否被勾选。

Q5：多条件求和除了 SUMIFS 还有更简单的写法吗？

A：在 Excel 365 / WPS 最新版，可以用 SUM(FILTER(求和列, 条件1)*(条件2))，但 SUMIFS 仍然是 兼容性最好、可读性最强 的写法，新手不必追求「花式」。SUMPRODUCT 是另一种万能替代，但参数顺序容易写错，建议熟悉后再用。

Q6：怎么快速检查公式是否引用错误？

A：按 Ctrl+~（波浪号）切换「显示公式」模式，肉眼检查引用区域；按 Ctrl+[（左中括号）可跳转到公式引用的所有单元格；按 F2 进入编辑模式可高亮标色；启用「公式 → 错误检查」可自动扫描 #REF! / #DIV/0! 等异常。

---

十三、写在最后：少走 3 年弯路的真正心法

公式本身不难，难的是「知道在什么场景下该用哪个」。这 36 个 Excel 神仙公式不是让你死记硬背，而是给你一个 「场景 → 公式」的决策地图：

• 看到「多条件统计」→ 想到 SUMIFS / COUNTIFS
• 看到「跨表查值」→ 想到 XLOOKUP / VLOOKUP
• 看到「去重、筛选、排序」→ 想到 UNIQUE / FILTER / SORT
• 看到「日期相减」→ 想到 DATEDIF / NETWORKDAYS
• 看到「格式美化」→ 想到 TEXT / ROUND / MOD

把这篇收藏起来，下次遇到问题时翻一翻，定位到对应公式直接抄走。 一年下来，你会发现：别人加班到 10 点，你 6 点准时下班——这就是「少走 3 年弯路」的真谛。

如果你觉得这份汇总有帮助，欢迎转发给你的同事/朋友——让更多人告别「加班做表」。也可以留言告诉我你最想深入学习哪个公式，我会单独出专题教程。