学会这些技巧，从此复杂数据处理只需快速操作。

01 基础工具箱：摆脱重复性机械劳动

你是否厌倦了每天耗费数小时进行筛选、求和与判断？所谓的“万能”公式，就是那些经过反复验证、可直接套用的自动化方案。

无论你是 Excel 新手，还是经验丰富的数据分析师，彻底精通以下这 20 个公式，足以让你从 80% 的日常数据处理繁琐任务中脱身，实现效率的跨越式增长。

版本说明：标有“*”的公式需使用 WPS 最新版或 Office 365（Excel 2021+）。老版本用户在文中有替代写法。核心建议*：若环境允许，强烈建议升级至新版本，新函数带来的效能变革是颠覆性的。

02 逻辑与检索：实现判断与匹配的自动化

1. 多条件判定（IFS/AND/OR）

场景应用：这不仅是 HR 考核绩效的工具，更是各类决策分析的基础。例如，市场部自动评估广告渠道表现：=IFS(ROI>1.2, "优秀", ROI>0.8, "合格", TRUE, "需优化")。

公式展示：

• AND（必须全满足）：=IF(AND(B2>10000, C2<0.05), "晋升", "考察")
• OR（满足其一即可）：=IF(OR(E2="A", F2>=3), "候选人", "否")
• *IFS（多层级判定，更直观）：=IFS(A2>=10000, 0.1, A2>=5000, 0.08, A2>=2000, 0.05, TRUE, 0.03)

2. 多条件搜索（突破 VLOOKUP 局限）

场景应用：在包含“部门 - 姓名 - 日期”的海量数据中，一秒精准定位。XLOOKUP 的出现，意味着你可以彻底遗忘 VLOOKUP 的列序号限制及从左向右的要求。

公式展示：

• 通用兼容版：=LOOKUP(1,0/(($A$2:$A$100=G2)*($B$2:$B$100=H2)), $C$2:$C$100) 在 A 列匹配 G2，B 列匹配 H2，返回 C 列对应值。
• *XLOOKUP 版（更简洁强大）：=XLOOKUP(G2&H2, $A$2:$A$100&$B$2:$B$100, $C$2:$C$100, "未找到") 支持反向查找、近似匹配，并内置错误处理。

3. 隐藏错误（IFERROR/IFNA）

场景应用：确保报表整洁且可计算。特别是使用 VLOOKUP 时，用 IFERROR 包裹，让#N/A 等错误显示为"-"或 0，避免后续 SUM、AVERAGE 报错。

公式展示：=IFERROR(VLOOKUP(F2, $A$2:$B$100, 2, FALSE), "无此记录")

03 条件统计：一键完成复杂数据汇总

4. 多条件求和（SUMIFS）

场景应用：这是使用频次极高的函数。财务按“部门 + 月份 + 费用类型”汇总支出；销售按“产品线 + 地区 + 季度”统计业绩。

公式展示：=SUMIFS(销售金额列，地区列，"华东", 产品列，"手机", 日期列，">=2026-1-1", 日期列，"<=2026-3-31")

5. 多条件计数（COUNTIFS）

场景应用：统计符合多重条件的记录数量。比如，HR 统计“研发部，级别在 P7 及以上，入职满 3 年”的员工人数。

公式展示：=COUNTIFS(部门列，"研发部", 级别列，">=P7", 入职日期列，"<=2023-4-9")

6. 按月/年求和（SUMPRODUCT + MONTH/YEAR）

场景应用：从每日流水账中，快速提取月度、季度、年度汇总数据，用于制作动态图表和仪表盘。

公式展示：

• 按月：=SUMPRODUCT((MONTH(日期列)=4)*(金额列)) （汇总 4 月份数据）
• 按年：=SUMPRODUCT((YEAR(日期列)=2026)*(金额列))

7. 排名计算（RANK.EQ）

场景应用：不仅用于销售排行榜，任何需要确定数值相对位置的场景均可使用，如绩效考核分数排名、项目时效排名。

公式展示：=RANK.EQ(B2, $B$2:$B$100, 0) （0 表示降序，即数字越大排名越靠前）

04 文本处理利器：混乱数据的拯救者

8. 提取任意位置数字*

场景应用：从“订单号 ABC2026XYZ001”中提取纯数字编号，或从客户留言“大概需要 300 台左右”中提取需求数量。正则表达式是终极武器。

公式展示：

• *Excel REGEXEXTRACT：=REGEXEXTRACT(A2, "\d+") 提取连续数字。
• *WPS REGEXP：=REGEXP(A2, "\d+", 0)

9. 智能拆分文本*

场景应用：TEXTSPLIT 是 TEXTJOIN 的反向操作，能按指定分隔符（支持多个）将单元格内容拆分为多列，彻底告别“分列”功能。

公式展示：=TEXTSPLIT(A2, "-") 将“北京 - 朝阳区 - 销售部”拆分为三列。

05 效率倍增器：攻克高频痛点

10. 统计不重复值个数

场景应用：计算“本月有多少个独立访客/客户/供应商”，是去重统计的刚需。

公式展示：

• 通用版：=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) （数组公式，需 Ctrl+Shift+Enter 三键结束的旧版用法，现可直接回车）
• *新版：=COUNTA(UNIQUE(A2:A100))

11. 多表同位置快速求和

场景应用：汇总 1-12 月，每个工作表 B5 单元格（如“总营收”）的年度总和。结构相同的多表汇总神器。

公式展示：=SUM(‘1 月:12 月’!B5)

12. 为公式添加“注释”

场景应用：在复杂公式后添加备注，方便日后维护及同事理解。使用 N() 函数包裹的注释，在计算中视为 0，不影响结果。

公式展示：=VLOOKUP(A2,$D$2:$F$100,3,FALSE) + N("根据最新价目表匹配，2026 年 4 月更新")

13. 计算精确的月份间隔（DATEDIF）

场景应用：计算工龄、账龄、项目周期（精确到月）的首选。DATEDIF 是 Excel 的隐藏函数，虽无提示但功能强大。

公式展示：=DATEDIF(入职日期，TODAY(), "M") 计算至今的总月数。

14. 生成随机数（RANDBETWEEN/RANDARRAY）

场景应用：不仅用于抽奖，还可生成随机测试数据、随机排序（配合排序功能）、随机分组。

公式展示：=RANDBETWEEN(1000, 9999) 生成 4 位随机数。

15. 精确四舍五入（ROUND/ROUNDUP/ROUNDDOWN）

场景应用：财务计算、结果展示的必备。区分清楚：ROUND 是四舍五入，ROUNDUP 是向上进位（如快递计费），ROUNDDOWN 是直接舍去（如计算满减）。

公式展示：=ROUND(A2*0.85, 2) 打 85 折后保留两位小数。

06 新式核武器：365/WPS 最新版专属

16. 动态筛选（FILTER）*

场景应用：它让 Excel 变身动态数据库。一键提取满足“上海地区，销售额>1 万，且产品为 A 或 B”的所有订单详情，结果随源数据自动更新。

公式展示：=FILTER(订单表，(地区列="上海")*(销售额列>10000)*((产品列="A")+(产品列="B")), "无符合条件订单") 注意：*表 AND，+表 OR。

17. 多表合并（VSTACK/HSTACK）*

场景应用：一键将 12 个月的结构相同表格上下堆叠（VSTACK）成年度总表，或将季度报表左右拼接（HSTACK）。告别复制粘贴。

公式展示：=VSTACK(‘1 月’!A1:E100, ‘2 月’!A1:E100, ..., ‘12 月’!A1:E100)

18. 动态分类汇总（GROUPBY）*

场景应用：用一条公式实现数据透视表的核心汇总功能，且结果可联动其他公式。按“大区”和“产品类别”二维度，对销售额进行求和与计数。

公式展示：=GROUPBY(大区列&产品列，销售额列，SUM) （365 新函数，功能强大）

19. 创建智能超链接（HYPERLINK）

场景应用：制作工作表目录、构建报表导航系统。点击单元格直接跳转到指定工作表的指定位置。

公式展示：=HYPERLINK("#‘销售明细’!A1", "查看销售明细")

20. 一键提取唯一值（UNIQUE）*

场景应用：从可能有重复的客户列表、产品列表中，瞬间生成干净无重复的唯一值列表，用于数据验证（下拉菜单）的源数据。

公式展示：=UNIQUE(A2:A1000) 或 =UNIQUE(FILTER(...)) 可结合 FILTER 先筛选再去重。

附图文教程

07 实战测试

下面 3 道单选题，检验一下你的掌握程度：

1. 你需要从一张“销售明细表”中，动态提取出“华东区”且“销售额大于 5000”的所有订单记录，并且当源数据新增或修改时，结果能自动更新。最合适的函数是？A. SUMIFS B. VLOOKUP C. FILTER D. 高级筛选功能
2. 你有一张“部门费用表”，需要根据 B 列的“部门”和 C 列的“费用类型”两个条件，来汇总 D 列的“金额”。你应该使用哪个函数？A. COUNTIF B. VLOOKUP C. SUMIFS D. IF
3. 你手头有“姓名 - 电话”混合在一个单元格的客户信息（如“张三 13800138000”），想快速将姓名和电话拆分到两列，使用哪个新函数最方便？A. LEFT + FIND B. TEXTSPLIT C. MID D. CONCAT

最后的核心建议：无需死记硬背。将此文收藏，工作中遇到具体问题时，按“场景关键词”前来搜索对应的公式并直接套用。用上 3 次，这个公式就真正属于你了。

效率的提升，始于将每一次重复的手工操作，替换为一次正确的公式设置。

从混乱的数据中提炼价值，需要的不是时间，而是正确的工具。

测试题答案：1. C 2. C 3. B

（完）