Excel 数据分析最常用的 15 个函数（附示例）

Excel 有大约 500 个函数，但做数据分析时，真正反复用到的只有十几个，而且它们大多成「家族」出现、共用同一种语法。本文按「这个函数解决什么问题」来整理这组核心函数，统一使用一张销售表：区域（A 列）、产品（B 列）、数量（C 列）、销售额（D 列）、日期（E 列）。所有函数名在中文版 Excel 和 WPS 表格里都是英文，直接照抄即可。

先说两个基础：公式怎么「锁」、怎么「拖」

在进入具体函数之前，有两个几乎每个人都会踩的坑：

• 绝对引用。 公式往下拖时，A2:A100 会变成 A3:A101。条件区域需要固定时，写成 $A$2:$A$100（选中引用后按 F4 切换），或者直接引用整列 A:A。
• 条件写在单元格里。 不要把「华东」硬写进每个公式，把它放在某个单元格（比如 H1），公式里引用 H1。以后改一个单元格，整张表的结果都跟着变。

条件汇总

1. SUMIFS：多条件求和

所有条件同时满足时求和。先写求和列，再成对写「条件列、条件」。

=SUMIFS(D:D, A:A, "华东", B:B, "保温杯")

这是「华东区保温杯卖了多少钱」。只有一个条件时也建议用 SUMIFS 而不是 SUMIF——参数顺序统一，以后加条件不用改结构。

2. COUNTIFS：多条件计数

语法相同，只是没有求和列。「华东区数量超过 100 的订单有几笔？」

=COUNTIFS(A:A, "华东", C:C, ">100")

3. AVERAGEIFS / MAXIFS / MINIFS

同一家族的其他成员。保温杯的平均销售额：

=AVERAGEIFS(D:D, B:B, "保温杯")

日期条件要把比较符写成字符串再拼接：E:E, ">="&DATE(2024,2,1)。

4. SUMPRODUCT

当 IFS 家族表达不了的时候的「万能钥匙」——「或」条件、在条件里做计算、加权求和。

=SUMPRODUCT((A2:A100="华东")*(C2:C100)*(D2:D100))

只在区域为华东的行上把数量 × 销售额相加。范围很大时会慢一些，但灵活性无可替代。

查找与匹配

5. XLOOKUP

在一列里找值，从同一行的另一列返回结果。默认精确匹配，可以向任意方向查找，还自带「找不到时显示什么」的参数。

=XLOOKUP("G-200", 产品!A:A, 产品!C:C, "未找到")

详细对比见 XLOOKUP 和 VLOOKUP 的区别。如果你用的是 Excel 2019 或更早版本，改用 INDEX/MATCH 或 VLOOKUP。

6. INDEX / MATCH

更早的两函数组合，至今仍然值得看懂：

=INDEX(C:C, MATCH("G-200", A:A, 0))

动态数组：重塑数据（Excel 365 / 2021）

这组函数返回的是多个单元格——结果会自动「溢出」到下方和右侧——它们取代了过去大量的辅助列技巧。较新版本的 WPS 表格也已支持其中大部分。

7. FILTER：按条件筛出行

满足条件的行，以一张实时更新的表格形式返回。

=FILTER(A2:E100, (A2:A100="华东")*(D2:D100>10000))

条件相乘表示「且」，相加表示「或」。外面套一个 SUM，就能写出透视表表达不了的条件求和。

8. UNIQUE：去重

=UNIQUE(A2:A100) 列出所有不同的区域；=COUNTA(UNIQUE(A2:A100)) 统计有几个——「有多少个不同的客户」这个问题，一个单元格就能回答。它是用公式实现「删除重复项」的方式，原数据不会被改动。

9. SORT / SORTBY：排序

=SORTBY(A2:B100, D2:D100, -1)——按销售额从高到低排列的区域和产品。外面套一层 TAKE(…, 10)，就是一个会自动更新的前 10 名榜单。

10. GROUPBY（Excel 365，2024 年起）

公式版的数据透视表：

=GROUPBY(A2:A100, D2:D100, SUM)

按区域汇总的销售额，自动溢出。再配合 PIVOTBY 就能做二维交叉表。如果你的 Excel 有这两个函数，「用透视表还是用公式」的争论基本可以结束了。透视表的做法见 数据透视表教程。

文本处理

11. TEXT

把数字或日期转成指定格式的文本，用来拼标签、做分组键。

=TEXT(E2, "yyyy-mm")

把日期变成「2024-03」这样的月份键，可以直接用来分组。=TEXT(D2, "¥#,##0.00") 用于显示金额。

12. TRIM / CLEAN / SUBSTITUTE

清洗三件套。TRIM 去掉多余空格，CLEAN 去掉不可打印字符，SUBSTITUTE 替换文本。组合起来：=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(D2), "¥", ""), ",", "")) 能把 " ¥2,400.50 " 变成数字 2400.5。更多方法见 清洗 Excel 脏数据。

13. TEXTSPLIT / TEXTBEFORE / TEXTAFTER（365）

不用「分列」也能拆分文本。比如把「广东省-深圳市」拆开：=TEXTBEFORE(A2, "-") 得到省，=TEXTAFTER(A2, "-") 得到市。旧版本可以用 LEFT + FIND 组合实现。

日期

14. EOMONTH / EDATE / DATE

=EOMONTH(E2, 0) 返回当月最后一天，是标准的「月份键」；=EOMONTH(E2, -1)+1 返回当月第一天；=EDATE(E2, 12) 返回一年后的同一天；=DATE(2024, 2, 1) 安全地构造日期——不要在条件里直接写 "2/1/2024"，不同地区的日期顺序不一样。

15. YEAR / MONTH / WEEKNUM / NETWORKDAYS

提取年、月、周用于分组；NETWORKDAYS(开始, 结束) 计算工作日天数，第三个参数可以传入节假日列表，把春节、国庆等假期排除掉。

两个「粘合剂」

IFERROR——=IFERROR(公式, "")——但要少用：它会隐藏所有错误，包括拼写错误和引用错误。优先用 XLOOKUP 自带的「未找到」参数，或者只捕获 #N/A 的 IFNA。

LET——在一个公式里给中间结果起名字，让公式可读：

=LET(净额, D2-F2, 毛利率, 净额/D2, IF(毛利率<0.2, "偏低", "正常"))

变量名可以用中文，读起来非常直观。

把五个函数组合起来

一个典型的周一问题：「本季度华东销售额前 5 的产品，以及各自占华东销售额的比例。」 在新版 Excel 里，一个溢出公式就能完成：

=LET(
  华东, FILTER(A2:E1000, (A2:A1000="华东")*(E2:E1000>=DATE(2024,1,1))*(E2:E1000<DATE(2024,4,1))),
  产品, UNIQUE(INDEX(华东,,2)),
  合计, SUMIFS(INDEX(华东,,4), INDEX(华东,,2), 产品),
  排名, SORTBY(HSTACK(产品, 合计), 合计, -1),
  TAKE(排名, 5)
)

FILTER 筛出区域和季度，UNIQUE 找出产品，SUMIFS 分别求和，SORTBY 排序，TAKE 保留前五，LET 让整个公式保持可读。把合计除以 SUM(合计) 就是占比。在 Excel 2019 或更早版本里，同样的结果可以用数据透视表加区域筛选和「前 10 项」值筛选实现。

常见错误

• #NAME?：函数名拼错，或者你的 Excel / WPS 版本不支持这个函数（常见于 XLOOKUP、FILTER、GROUPBY）。
• #SPILL!：动态数组要溢出的区域里已经有内容，清空下方和右侧的单元格即可。
• 结果为 0：求和列里的数字是文本型数字（左上角有绿色小三角）。选中该列 → 数据 → 分列 → 完成，一键转成数字。
• 条件里的中文匹配不上：检查是否有多余空格或全角/半角混用，先用 TRIM 清理。
• 公式拖动后结果全错：条件区域没有加 $ 锁定。

WPS 表格用户需要知道的

本文的函数在 WPS 表格里基本都能用，函数名和参数顺序与 Excel 一致。区别主要在版本：SUMIFS、COUNTIFS、SUMPRODUCT、INDEX/MATCH、TEXT 以及所有日期函数在各版本 WPS 中都可用；XLOOKUP、FILTER、UNIQUE、SORTBY 等动态数组函数需要较新版本；GROUPBY、PIVOTBY 这类最新函数是否可用，请以你的版本为准——输入函数名开头，看提示列表里有没有即可。

另一种选择：先问，再学公式

上面每个公式都对应一个你本来就会问的问题：按区域合计、有多少个不同的客户、前 10 名、每月销售额。如果你知道问题、却一时想不起公式，可以把表格上传到 ChatExcel，直接用中文问——「华东销售额前 10 的产品」、「每个月有多少个不同的客户」。答案基于整份文件计算，可以画成图表或导出。然后再问一句 「在 Excel 里怎么做？」，你就会得到这份清单里的公式，而且是按你表格的真实列写好的。一次一个真实问题，是学会这 15 个函数最快的方法。ChatExcel 为付费订阅，套餐见 价格页。