Excel 高频函数实战手册：按场景查，不再背参数

Excel 高频函数实战手册

这篇不按字母顺序列函数——那种排列方式适合当字典，不适合解决眼前的问题。下面按「你想干什么」来组织，遇到场景直接翻到对应小节。

场景一：想在另一张表里查这个人的信息

首选 XLOOKUP（Excel 365 / 2021 及以上）

=XLOOKUP(A2, 员工表!A:A, 员工表!C:C, "未找到", 0)

四个参数依次是：查什么、在哪列查、返回哪列、找不到时显示什么。比 VLOOKUP 强在三点：能向左查、不用数列号、找不到可以自定义返回值而不是显示 #N/A。

老版本只能用 VLOOKUP

=IFERROR(VLOOKUP(A2, 员工表!A:C, 3, FALSE), "未找到")

必须记住的三个限制：

1. 查找列必须在区域的第一列 2. 第三个参数是列序号，从查找列开始数，插入一列就会错位 3. 第四个参数必须写 FALSE（或 0）才是精确匹配，写 TRUE 会按近似匹配返回错误结果

比 VLOOKUP 更好用的组合：INDEX + MATCH

=INDEX(员工表!C:C, MATCH(A2, 员工表!A:A, 0))

插入删除列都不会错位，现在有了 XLOOKUP 之后优先级降低了，但兼容老文件时仍然可靠。

场景二：多条件查找

要按「品类 + 月份」两个条件取数，单个 VLOOKUP 做不到，加个辅助列最直接：

=XLOOKUP(A2&"-"&B2, 数据表!D:D, 数据表!E:E, 0)

辅助列 D 用 =品类&"-"&月份 拼出来。方法土但稳定，几十万行也不卡。

不想加辅助列就用 FILTER：

=FILTER(返回区域, (条件列1=条件1)*(条件列2=条件2), "无匹配")

星号在这里相当于「且」，加号相当于「或」。

场景三：按条件求和与计数

| 需求 | 写法 | | --- | --- | | 单条件求和 | =SUMIF(品类列, "女装", 金额列) | | 多条件求和 | =SUMIFS(金额列, 品类列, "女装", 月份列, "9月") | | 单条件计数 | =COUNTIF(状态列, "已完成") | | 多条件计数 | =COUNTIFS(品类列, "女装", 金额列, ">100") | | 多条件平均值 | =AVERAGEIFS(金额列, 品类列, "女装") |

注意 SUMIF 和 SUMIFS 的参数顺序是反的 —— SUMIF 先写条件区域，SUMIFS 先写求和区域。这是新手最常记混的地方。

条件写「大于 100」时要加引号：">100"。引用单元格时要拆开：">"&F1。

场景四：去重统计与判断重复

=COUNTA(UNIQUE(A2:A500))                    ' 去重后的个数
=IF(COUNTIF(A$2:A2, A2)>1, "重复", "")      ' 标记第二次及以后出现的行

第二行的技巧是混合引用：A$2:A2 的起始行锁死、结束行跟着下拉变化，形成「从第 2 行到当前行」的动态区域，专门用来标记重复项。

场景五：文本处理（清洗导出数据）

后台导出的字段经常带空格、多余字符。

=TRIM(A2)                              ' 去掉首尾空格（清洗导入数据第一步）
=TEXTBEFORE(A2, "-")                   ' 取分隔符前的部分
=TEXTAFTER(A2, "-")                    ' 取分隔符后的部分
=TEXTSPLIT(A2, ",")                    ' 按逗号拆成多列
=TEXT(A2, "yyyy-mm-dd")                ' 统一日期显示格式
=SUBSTITUTE(A2, CHAR(10), "")          ' 去掉换行符

不符合规范的日期和数字，往往就是 TRIM 之后才恢复正常的——导出的表里面藏着大量不可见字符。

场景六：条件判断与错误处理

=IFS(A2>=90, "优秀", A2>=60, "及格", TRUE, "不及格")   ' 多分支，比嵌套 IF 清爽
=IFERROR(原公式, "出错时的兜底值")                      ' 统一兜底，报表不再满屏 #N/A

写报表时养成习惯：外层一律包 IFERROR。发出去的表出现 #REF!、#N/A 这类符号，观感很差也很难排查。

新手最常踩的六个坑

1. 数字被存成文本 —— 左上角有绿色三角。选中列 → 数据 → 分列 → 完成，或 =VALUE(A2) 转换 2. 公式不计算，只显示公式本身 —— 单元格格式是「文本」。改成「常规」后按 F2 再回车 3. 下拉填充时引用跑偏 —— 该锁的地方没加 $。F4 键循环切换引用方式 4. VLOOKUP 返回 #N/A —— 大概率是查找值带空格或类型不一致，先 TRIM 再查 5. SUM 结果不对 —— 数字列里混了文本，SUM 会静默忽略，用 COUNT 和 COUNTA 对比一下行数 6. 日期算出来是五位数 —— 那是日期的序列号，把单元格格式改成日期就行

快捷键（每天能省十分钟）

| 操作 | Windows | macOS | | --- | --- | --- | | 填充 / 切换引用方式 | Ctrl + D / F4 | Cmd + D / Cmd + T | | 向下填充邻近单元格值 | Ctrl + D | Cmd + D | | 选中当前数据区 | Ctrl + A | Cmd + A | | 定位到区域边缘 | Ctrl + 方向键 | Cmd + 方向键 | | 打开函数参数对话框 | Ctrl + A（函数名后） | Cmd + A | | 粘贴为数值 | Ctrl + Alt + V 再选「值」 | Cmd + Ctrl + V |

下一步

• 数据量大到透视表卡：改学 Power Query 做清洗，见本站 Power Query 自动化那篇
• 想要自动刷新的报表：看「数据透视表与动态看板」
• 重复性操作极多：考虑用 Python + openpyxl，见 Python 批量处理 Excel 那篇