Excel 常用函数速查手册

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 的顺序建议

大多数人卡住不是因为函数难,是因为顺序错了:

  1. 先学数据规范:一行一条记录、不加合并单元格、不用颜色当分类。数据不规范,什么函数都救不了。
  2. 再学查找:VLOOKUP 或 XLOOKUP,这是分水岭。
  3. 然后条件统计:SUMIFS / COUNTIFS。
  4. 最后透视表:它比函数快,但需要前面三步的数据基础。

常见问题

Q:VLOOKUP 为什么总是 #N/A?
A:九成是两种情况——查找值有隐藏空格(用 TRIM 清一下),或者两边格式不一致(一边文本一边数字)。

Q:公式拖动后结果全错?
A:引用没锁。按 F4 把区域改成绝对引用 $A$1:$A$100。

Q:公式太多表格变卡?
A:整列引用(A:A)是主因。改成具体范围,或者把计算结果选择性粘贴成值。