Excel 有 400 多个函数,日常工作真正用得上的不超过 30 个。这篇按用途分组,每个函数只写语法、一个真实场景和一句”什么时候别用”。
一、查找匹配(最常用,也最容易学错)
1. VLOOKUP
=VLOOKUP(查找值, 查找区域, 返回第几列, FALSE)
场景:根据工号查姓名、根据商品编号查价格。
最后一参数写 FALSE(精确匹配),写 TRUE 会返回错误结果,还不报错。
局限:查找值必须在区域第一列,不能向左查。
2. XLOOKUP(新版首选)
=XLOOKUP(查找值, 查找列, 返回列, "没找到")
能向左查、能设找不到时的默认值、不用数第几列。Excel 365 和 2021 以上可用。
3. INDEX + MATCH
=INDEX(返回列, MATCH(查找值, 查找列, 0))
老版本的万能组合。比 VLOOKUP 灵活,但写起来长。团队里有人用旧版 Excel 时用它。
二、条件判断
4. IF
=IF(条件, 成立时, 不成立时)
嵌套超过三层就该换 IFS 或者做辅助列,否则自己都看不懂。
5. IFS
=IFS(分数>=90,"优秀", 分数>=60,"及格", TRUE,"不及格")
多层判断的清晰写法。最后一个条件写 TRUE 兜底。
6. IFERROR
=IFERROR(原公式, "错误提示")
把 #N/A、#DIV/0! 换成你想显示的内容。所有查找类公式都建议套一层。
三、条件求和与计数
7. SUMIF / SUMIFS
=SUMIFS(求和列, 条件列1, 条件1, 条件列2, 条件2)
场景:统计”华东区 + 本月 + 已付款”的销售额。
8. COUNTIF / COUNTIFS
=COUNTIFS(条件列1, 条件1, 条件列2, 条件2)
场景:统计某状态的订单数。
9. SUMPRODUCT
=SUMPRODUCT((区域1="条件")*(区域2))
老版本里做多条件求和,比 SUMIFS 灵活。新版有 SUMIFS 就不必绕。
四、文本处理
| 函数 | 作用 | 例子 |
|---|---|---|
| LEFT / RIGHT | 取左/右 N 个字符 | =LEFT("2026-11",4) → 2026 |
| MID | 从中间取 | =MID(A1,5,2) |
| LEN | 字符数 | 检查手机号是否 11 位 |
| TEXTJOIN | 带分隔符合并 | =TEXTJOIN("-",TRUE,A1:A3) |
| TEXT | 格式化数字 | =TEXT(1234.5,"#,##0.00") |
| TRIM | 清多余空格 | 清洗导入数据必用 |
| SUBSTITUTE | 替换指定文本 | 批量去掉单位”元” |
五、日期时间
| 函数 | 作用 | 典型用法 |
|---|---|---|
| TODAY / NOW | 今天 / 此刻 | 算账期、做倒计时 |
| DATEDIF | 两个日期差 | =DATEDIF(开始,结束,"Y") 算工龄 |
| EOMONTH | 月末日期 | 算本月最后一天 |
| WORKDAY | N 个工作日之后 | 算交付日 |
| NETWORKDAYS | 两个日期之间的工作日 | 算项目工期 |
六、动态数组(新版 Excel)
UNIQUE
=UNIQUE(A2:A100)
去重,一列变一列。以前要”数据 → 删除重复项”,现在一个公式搞定,源数据变了结果自动变。
FILTER
=FILTER(数据区, 条件列="华东")
按条件筛选,结果自动溢出。做动态看板时比数据透视表灵活。
SORT
=SORT(区域, 2, -1)
按第 2 列降序排列。
七、几个救命的小函数
ROUND四舍五入到指定位数,别用单元格格式假装四舍五入——格式只改显示,值还是原值。ABS取绝对值,算偏差时用。MAX / MIN配合条件可以用MAXIFS。ROW / COLUMN生成序号,比手动拖快。
学 Excel 的顺序建议
大多数人卡住不是因为函数难,是因为顺序错了:
- 先学数据规范:一行一条记录、不加合并单元格、不用颜色当分类。数据不规范,什么函数都救不了。
- 再学查找:VLOOKUP 或 XLOOKUP,这是分水岭。
- 然后条件统计:SUMIFS / COUNTIFS。
- 最后透视表:它比函数快,但需要前面三步的数据基础。
常见问题
Q:VLOOKUP 为什么总是 #N/A?
A:九成是两种情况——查找值有隐藏空格(用 TRIM 清一下),或者两边格式不一致(一边文本一边数字)。
Q:公式拖动后结果全错?
A:引用没锁。按 F4 把区域改成绝对引用 $A$1:$A$100。
Q:公式太多表格变卡?
A:整列引用(A:A)是主因。改成具体范围,或者把计算结果选择性粘贴成值。