按场景掌握Excel高频函数：查找、汇总、清洗与动态报表

1. 学Excel函数最该先搞明白的，不是函数清单

我见过太多人收藏了几十页的Excel常用函数大全，真正上表格的时候还是一个都想不起来。问题不在于记性，而在于那份清单是按字母排序的，A开头的ABS、AND、AVERAGE堆在前面，真正高频的查找、统计、文本清洗反而被埋在中间。人脑对这种组织方式几乎免疫——你记住的是"我遇到过什么问题、当时用什么解决的"，而不是"第37个函数叫什么名"。

所以这篇不打算再给你一份字母表。我想按"你手上这张表到底要干什么"来拆：查数据、算条件、洗脏数据、算日期、做判断、动态出报表，六大块，每块把真正高频的函数点透，顺带说说我在实际做表时踩过的坑。如果你刚开始系统学Excel函数，或者用了几年但一直在VLOOKUP附近打转，这篇会很对口；如果你已经是老手，可以重点看每节的取舍分析和性能部分。

在进正题之前，有两件被无数教程跳过、但直接决定你函数写得对不对的地基，必须单独说。

1.1 相对引用和绝对引用，比任何一个函数都重要

新手写公式失败，十个里有六个不是函数用错，是引用跑偏了。A1是相对引用，往右拖会变成B1，往下拖会变成A2；$A$1是绝对引用，怎么拖都不动；$A1锁列不锁行，A$1锁行不锁列。

我做过一个销售汇总，公式是=SUMIFS(明细!$F:$F, 明细!$A:$A, $A2)，往下拖一百行都没问题，因为条件区域锁死了。后来同事拿过去往右拖了一列，整个公式飘了，统计结果全错，他排查了一下午还以为是数据有问题。这类事故几乎每周都在发生。

判断标准很简单：这个区域会不会随公式位置变化。不会变的一律用$锁死，只有一个单元格会变的（比如条件值A2）才放开。我自己的习惯是写完公式先按F4把区域锁上，再去调那个唯一需要相对的部分，比事后检查省事得多。

1.2 日期是数字，数字可能变成文本

Excel 里日期本质是序列号，1900 年 1 月 1 日对应数字 1，2024 年 1 月 1 日对应 45292。你在单元格里看到的"2024/1/1"只是显示格式。这个认知带来的直接后果是：日期可以加减、可以比较大小、可以用MAX和MIN，但一旦它变成文本格式，所有日期函数都会罢工。

另一个高频灾难是文本型数字。从系统导出的表格里，数字经常带着前导撇号或者空格，123看着是数字，实际是文本，这时候VLOOKUP精确匹配会返回#N/A，SUM求和会返回 0。判断办法是看对齐方式：数字默认右对齐，文本默认左对齐，整列左对齐你还看不出来就是有问题。

几种救法我都在实际场景里用过：

提醒：=--A2这种双负号写法只对"长得像数字的文本"有效，如果单元格里有空格或全角字符，会直接报#VALUE!，别盲目整列套。

把这两条地基打牢，后面所有函数才有意义。接下来进入正题。

2. 查找匹配：VLOOKUP、INDEX+MATCH、XLOOKUP 的取舍

查找是Excel里使用频率最高的操作，没有之一。很多人的技能树停在VLOOKUP四级就不动了，结果一遇到"要找的值在返回列的右边"就卡死。这三个工具不是新老替代关系，各有明确的适用边界。

2.1 VLOOKUP 的坑全在后两个参数

语法是=VLOOKUP(找什么, 在哪个区域找, 返回第几列, 精确还是近似)。

前两个参数几乎不会错，出事的都在后两个。第三个参数是从查找区域第一列开始数的第几列，不是从A列数。很多人写VLOOKUP(A2, B:F, 4, 0)心里想的是F列，实际数出来是E列，结果默默错位，还不报错。

第四个参数更凶险。写0或FALSE是精确匹配，写1或TRUE是近似匹配。省略不写默认是近似匹配。近似匹配要求查找区域首列升序排列，它会找"小于等于目标值的最大值"。用在查找工号、查商品编码这种场景，会返回一个相邻的错误结果，而且不报错——这是最难排查的一类问题。

=VLOOKUP($A2, 明细!$A:$F, 4, 0)

我个人的建议是第四个参数永远显式写0，哪怕是精确匹配也不要省略。多打一个字符，省掉几小时的排查。

VLOOKUP 还有两个硬伤：不能往左查，返回列是硬编码的数字，插一列就错。所以我在源数据会变动的表里基本不用它。

2.2 INDEX+MATCH 解决"往左查"和"列位置会动"

=INDEX(返回区域, MATCH(找什么, 查找区域, 0))。

MATCH负责定位行号，INDEX按行号取值，两个函数拆开用也很常见。它比 VLOOKUP 强在两点：查找列可以在返回列右边任意位置；插入删除列不影响公式，因为没有硬编码列序号。

=INDEX(明细!$F:$F, MATCH($A2, 明细!$B:$B, 0))

双向查找是它的经典场景——行列都要匹配，比如按"月份+产品"取数：

=INDEX($B$2:$M$13, MATCH($A16,$A$2:$A$13,0), MATCH(B$15,$B$1:$M$1,0))

MATCH的第三个参数同样建议显式写0，表示精确匹配。写1是升序近似匹配，-1是降序近似匹配，用错了结果一样是静默错位。

2.3 XLOOKUP 不是万能，先确认版本

新版Excel提供了=XLOOKUP(找什么, 在哪一列找, 返回哪一列, 找不到显示什么, 匹配模式, 搜索模式)。它把上面两个方案的痛点一次性解决：可以往左查、列位置不怕变、原生支持"找不到时显示指定内容"、还能从后往前搜。

=XLOOKUP($A2, 明细!$B:$B, 明细!$F:$F, "未找到")

但使用前必须确认一件事：你的文件会不会发给用旧版的人。XLOOKUP 是较新版本才有的函数，旧版本打开会直接显示#NAME?。我自己就吃过这个亏——在自己机器上跑得好好的报表，发到同事那里一片#NAME?，只能连夜改回 INDEX+MATCH。

一个务实的判断标准：

3. 条件求和与计数：SUMIFS 家族和 SUMPRODUCT 的分工

"按条件汇总"是第二高频操作。这一组函数的语法差异很容易记混，而记混的后果是结果全错但不报错。

3.1 三个函数的参数顺序不一样

SUMIFS是求和区域在前，条件区域在后；COUNTIFS没有求和区域；AVERAGEIFS和SUMIFS一致。而单条件版本SUMIF恰好反过来，是条件区域在前、求和区域在后。这个不一致是历史遗留，只能死记。

=SUMIFS(明细!$F:$F, 明细!$A:$A, $A2, 明细!$C:$C, "已完成") =COUNTIFS(明细!$A:$A, $A2, 明细!$C:$C, "已完成") =AVERAGEIFS(明细!$F:$F, 明细!$A:$A, $A2)

我常用的记忆方式是：带S的，把要算的东西放最前面；不带S的，条件在前。多条件继续往后追加"区域, 条件"成对出现，理论上能加很多组，实际项目里超过五六个条件就该考虑用辅助列或者透视表了，公式会难维护到没人敢动。

3.2 通配符和比较运算符的写法

SUMIFS、COUNTIFS这些条件函数支持通配符：*代表任意多个字符，?代表单个字符。查"以某字开头"的商品：

=SUMIFS(明细!$F:$F, 明细!$B:$B, "A*")

注意通配符只对文本生效，对数字无效。另外如果条件本身就是*这个字符，要写成~*转义。

比较运算符要作为字符串加引号：">100"、"<=500"。如果比较的值在某个单元格里，必须用连接符拼：

=SUMIFS(明细!$F:$F, 明细!$F:$F, ">"&$B$1)

写成">$B$1"是把$B$1当字符串处理，结果必然错。这个坑我至少踩过三次。

还有一个隐蔽问题：条件区域和求和区域的行数必须一致。写成明细!$F$2:$F$1000配明细!$A$2:$A$999，Excel 会按左上角对齐强行扩展，结果错得毫无提示。用整列引用（$F:$F）能规避这个问题，但要注意性能，第8节会细说。

3.3 SUMPRODUCT 处理"条件里还要算东西"的场景

有些需求用SUMIFS根本表达不出来，比如"统计金额大于各自平均值的产品数量"，或者"多条件计数且条件涉及函数运算"。这时候上SUMPRODUCT。

=SUMPRODUCT((明细!$A$2:$A$1000=$A2)*(明细!$C$2:$C$1000="已完成")*明细!$F$2:$F$1000)

逻辑是：每个条件括号内部生成一组 TRUE/FALSE，乘以 1 转换成 1/0，多个条件相乘就是"同时满足"，最后和数值区域相乘求和。加总效果等价于条件求和。

计数不需要乘数值列：

=SUMPRODUCT((明细!$A$2:$A$1000=$A2)*(明细!$C$2:$C$1000="已完成"))

SUMPRODUCT有三个必须注意的点：区域大小必须严格一致；不能引用整列（会算到崩溃）；条件里的文本比较不区分大小写。另外它对付不了通配符，需要配合ISNUMBER(FIND(...))之类的写法。

我在实际项目里的分工原则是：能用 SUMIFS 就不用 SUMPRODUCT，前者快得多。只有当条件里出现函数运算、或者需要"某列是否包含某关键词"这类模糊判断时，才动用 SUMPRODUCT。

4. 清洗脏数据：文本函数在真实表格里怎么用

从系统导出、网页复制、别人发来的表格，九成带着脏数据。这一节的函数看着简单，组合起来才是真功夫。

4.1 LEFT、RIGHT、MID、FIND 的组合套路

四个函数单独用没什么难度：LEFT(文本, n)取左 n 位，RIGHT(文本, n)取右 n 位，MID(文本, 起始位置, n)从中间取，FIND(找什么, 在哪找)返回位置。

真正的用法是嵌套。举个典型例子：单元格里是"订单编号：SO20240513-001"，要提取中间的SO20240513-001。冒号位置不固定（有时全角有时半角），但编号一定在冒号后面：

=TRIM(MID(A2, FIND("：", A2)+1, 99))

MID的第三参数给个足够大的数（99），超出长度它会自动截到末尾，不用精确计算。这是个小技巧，比先算LEN再减要省事。

如果要取冒号前面的部分：

=LEFT(A2, FIND("：", A2)-1)

FIND找不到会报#VALUE!，所以生产环境里常包一层IFERROR。

FIND区分大小写，SEARCH不区分且支持通配符。处理用户输入的数据我一般用SEARCH，容错性更好。

4.2 TRIM、CLEAN、SUBSTITUTE：看不见的字符才是元凶

有一种#N/A是这么来的：两个单元格看起来一模一样，A2=B2却返回 FALSE。原因通常是空格、换行符或者不可见字符。

TRIM去掉首尾空格并把中间连续空格压成一个；CLEAN去掉 ASCII 0-31 的控制字符，包括换行符。组合起来：

=TRIM(CLEAN(A2))

但这两个函数有个盲区：不间‍断空格（CHAR(160)）去不掉。从网页复制的内容特别容易带这个。得用SUBSTITUTE：

=TRIM(SUBSTITUTE(CLEAN(A2), CHAR(160), " "))

SUBSTITUTE(文本, 旧, 新, [第几次出现])是文本替换的主力。第四个参数省略就全部替换，指定数字只替换第 n 次出现。清理手机号里的横杠、去掉金额里的"元"字、统一全角括号为半角，都靠它。

我处理这类问题的固定流程是：先CLEAN去控制字符，再SUBSTITUTE去不间‍断空格和全角空格，最后TRIM收尾。三步走完还匹配不上，才去怀疑数据类型。

4.3 TEXTJOIN 和 TEXTSPLIT：新版带来的拼接与拆分

老办法合并多列文本是=A2&B2&C2，中间想加分隔符就得写成=A2&"-"&B2&"-"&C2，列一多就没法看。TEXTJOIN解决了这个：

=TEXTJOIN("-", TRUE, A2:C2)

第二参数TRUE表示忽略空单元格，这个非常实用——有空值时不会出现A--C这种尴尬结果。

反向操作是拆分。以前拆"省-市-区"要么用"分列"（会改动原数据），要么用MID+FIND嵌套三层，公式长得像天书。新版有TEXTSPLIT：

=TEXTSPLIT(A2, "-")

结果会横向溢出到相邻单元格，所以右边必须留空，否则会报#SPILL!。这个报错的意思是"溢出区域被挡住了"，清空右边就行，不是公式写错了。

需要拆成多行时，把行分隔符作为第三个参数：

=TEXTSPLIT(A2, , "；")

提醒：TEXTSPLIT和TEXTJOIN的版本要求不同，前者更新。外发文件前先在自己机器以外的地方验证一遍，或者干脆用"分列"功能做一次性处理，把结果转成值。

5. 日期算不准，多半是函数口径没选对

日期相关的报错，绝大部分不是函数写错，而是"你以为的算法"和"Excel的算法"不一致。特别是工龄、账龄、逾期天数这几个高频需求。

5.1 DATEDIF 的三副面孔

=DATEDIF(开始日期, 结束日期, "单位")是个隐藏函数——输入的时候没有参数提示，但能用。

关键在于第三参数的口径：

很多人算工龄用(结束-开始)/365取整，遇到闰年就偏差一天。用=DATEDIF(A2, TODAY(), "Y")才是按完整年算。

MD这个单位有个历史遗留的已知问题，在某些日期组合下会返回负数，官方一直没修。我个人的做法是涉及"还差几天"的场景一律用D单位配合别的函数，不用MD。

5.2 EOMONTH 和 NETWORKDAYS：月末与工作日

=EOMONTH(日期, 0)返回当月最后一天，第二参数写1就是下月最后一天，-1是上月最后一天。做月度报表必用，比DATE(YEAR(A2),MONTH(A2)+1,0)短得多，也不用担心跨年。

=NETWORKDAYS(开始, 结束, [节假日列表])算两个日期之间的工作日数，自动排除周六周日。第三参数可以指定自定义节假日区域：

=NETWORKDAYS(A2, B2, $H$2:$H$20)

$H$2:$H$20里放公司放假日，比如春节、国庆。不传这个参数的话，法定节假日会被当成工作日算进去，结果偏大。

反向需求是"从今天起往后推 10 个工作日是哪天"，用=WORKDAY(A2, 10, $H$2:$H$20)。

这两个函数还有个隐藏用法：算逾期。=NETWORKDAYS(应还日, TODAY())得到已经逾期的工作日数，配合条件格式标红，比简单相减更贴合实际业务节奏。

5.3 TEXT 格式化日期：只改显示，不改本质

=TEXT(A2, "yyyy年m月d日")把日期转成指定格式的文本。常见用途是拼接字符串：

="截止"&TEXT(A2,"yyyy年m月d日")&"共"&TEXT(B2,"0.00")&"元"

这里有个大坑：TEXT的结果是文本，不是日期也不是数字。如果后面还要参与计算，会被当成 0 处理。我见过有人在汇总表里对TEXT转换过的列求和，结果全是 0，排查了半天才发现是类型问题。

另一个高频需求是月份汇总，=TEXT(A2,"yyyy-mm")可以得到"2024-05"这样的月份标识，作为SUMIFS的条件很方便。但要注意排序——文本排序和日期排序结果一致（因为 yyyy-mm 格式），如果是m月这种格式就完全乱了。

6. 让表格自己判断：逻辑函数与错误处理

逻辑函数本身不难，难的是嵌套层数和错误处理的分寸。

6.1 IF 嵌套三层以上就该换 IFS

=IF(条件, 成立返回, 不成立返回)，两层还好，三层就开始数不清括号。新版有IFS：

=IFS(A2>=90,"优秀", A2>=80,"良好", A2>=60,"及格", TRUE,"不及格")

条件按顺序判断，命中就返回，后面的不再看。最后一个TRUE相当于兜底的 "else"，不写的话所有条件都不满足时会返回#N/A。

IFS的注意事项是顺序必须从严格到宽松。把A2>=60放最前面，后面的 90、80 永远命中不了，因为所有大于 90 的也大于 60。

老版本没有IFS的话，可以换个思路：把等级标准做成一张两列的对照表，用VLOOKUP近似匹配或者LOOKUP去查。我更喜欢这个方案——标准会变，改表格比改公式容易得多，也不用担心以后加等级。

6.2 IFERROR 该用，但别到处用

=IFERROR(公式, 出错时显示什么)能捕获所有错误类型：#N/A、#VALUE!、#DIV/0!、#REF!、#NAME?、#NUM!、#NULL!。

查找类公式配IFERROR是合理的：

=IFERROR(VLOOKUP($A2, 明细!$A:$F, 4, 0), "未匹配")

但滥用会掩盖真问题。比如除零错误=IFERROR(A2/B2, 0)，看起来是把错误变成 0 了，实际是分母为空这种数据缺陷被藏起来了，等你发现时已经错了三个月。我的一般做法是：查找匹配用IFERROR兜底，计算类错误先让它显示出来，看到了再决定怎么处理。

6.3 LET 让长公式不用重复算

写复杂公式时经常出现同一个片段重复三次，比如：

=IFERROR(VLOOKUP(A2,明细!A:F,4,0)*VLOOKUP(A2,明细!A:F,5,0), 0)

两次查找同样的东西，计算量翻倍，改起来还容易漏。LET可以给中间结果起名字：

=LET(单价, XLOOKUP(A2,明细!$A:$A,明细!$D:$D,""), 数量, XLOOKUP(A2,明细!$A:$A,明细!$E:$E,0), IF(单价="",0,单价*数量))

最后一行是返回值，前面的"名字, 值"成对出现。好处有两个：计算只做一次；公式读起来像代码，三个月后回来还看得懂。缺点同样是版本兼容性，外发文件要谨慎。

我个人的经验是，公式超过三行嵌套、或者同一片段出现两次以上，就值得上LET。它不会让计算变快很多，但会让排错难度断崖式下降。

7. 动态数组：把重复劳动变成一句话

支持动态数组的版本出来后，很多以前要拖几百行的公式，现在一个单元格就够了。这是近几年Excel公式层面最大的变化，值得单独说。

7.1 溢出机制和 # 引用

老版本写数组公式要按Ctrl+Shift+Enter，外面包一层大括号。现在直接回车，结果自动"溢出"到下方或右侧的空白区域。

溢出区域被挡住会报#SPILL!。这时候不要急着改公式，先看右边和下边有没有内容，清空就好。

想引用整个溢出结果，用#：

=SUM(D2#)

D2#代表 D2 以及它溢出的全部区域。这个写法的好处是源数据增减时，求和范围自动跟着变，不用手动调区域。

7.2 FILTER：多条件筛选一步到位

以前做"按条件提取明细"，要么用筛选功能手动操作，要么用数组公式，要么上 VBA。现在：

=FILTER(明细!A2:F1000, (明细!C2:C1000="华东")*(明细!F2:F1000>10000), "无符合记录")

三个参数：要返回的区域、筛选条件、无结果时显示什么。多个条件用*连接表示"且"，用+连接表示"或"。这个逻辑和SUMPRODUCT一致，学过前面那节就能直接迁移。

条件区域和返回区域的行数必须一致，这是最常见的报错来源。

7.3 UNIQUE + SORT + 组合出报表

=UNIQUE(明细!C2:C1000)一行得到去重后的列表，自动溢出。以前要么用"删除重复项"（破坏原数据），要么用复杂的COUNTIF配合INDEX，还得往下拖到足够多的行数。

排序加一层SORT：

=SORT(UNIQUE(FILTER(明细!C2:C1000, 明细!C2:C1000<>"")), 1, 1)

这行的意思是：先过滤掉空值，去重，再按升序排。三步合成一个公式，源数据新增记录时结果自动更新，完全不用维护。

再配SUMIFS或者新版的分组函数，就能搭出一个全自动的小型汇总表。我在几个日常报表里用这套组合替换掉了原来的透视表刷新流程，省了不少"忘了点刷新"造成的口径不一致。

不过要清楚它的边界：动态数组适合中等数据量。几万行没问题，几十万行会明显卡顿，这种规模还是交给透视表或者更专业的数据处理工具。

8. 公式写得对，还得跑得动、改得动

前面讲的都是"怎么算对"，这一节讲"怎么不算崩"。实际工作中，一个能算出结果但让整张表卡到无法编辑的公式，等于没写出来。

8.1 整列引用的代价

SUMIFS(明细!$F:$F, ...)写起来最省事，不用管数据有多少行。但整列引用意味着 Excel 要处理一百多万行，即使你的数据只有两千行。

单条公式感觉不出来，几十条、上百条整列引用的SUMIFS叠加，文件就会从"秒开"变成"转圈两分钟"。我接手过一个汇总表，两万行数据，每次改一个条件要等七八秒，最后发现是六十多条整列引用的公式。

改法很直接：把$F:$F换成$F$2:$F$10000，或者用表格功能（Ctrl+T）把数据区域转成结构化表格，公式里引用列名明细[金额]。结构化引用的好处是范围随数据自动伸缩，既不会有整列引用的性能问题，也不用每次手动调区域。

=SUMIFS(明细[金额], 明细[区域], $A2)

8.2 易失函数的隐性成本

有一类函数只要表格发生任何变化就会重算，叫易失函数。TODAY()、NOW()、RAND()、RANDBETWEEN()、OFFSET()、INDIRECT()、CELL()都属于这一类。

TODAY()用在几处没问题，但如果几百个单元格里都有它，每次输入一个字符整表就重算一遍。OFFSET和INDIRECT更贵，因为它们连引用关系都无法被 Excel 追踪优化。

替代思路：

• OFFSET做动态区域，新版用表格结构化引用或者FILTER替代
• INDIRECT拼接工作表名做跨表汇总，新版可以用VSTACK或者干脆规范表名后直接引用
• TODAY()大量使用，可以单独放一个单元格，其他地方引用那个单元格

8.3 公式维护的三条经验

做了这些年表，我总结出三条不太写在教程里的规矩：

第一，辅助列不是丢人的事。一个嵌套七层的IF加三个VLOOKUP，别人接手时基本等于没有。拆成三列辅助列，每列一步，加个标题，可读性天差地别。数据量大到不能加列的时候再考虑合并。

第二，公式里不要写死"魔法数字"。提成比例 0.03、阈值 10000 这些，统一放到表头的一个参数区，公式里引用单元格。业务方改比例时改一个格子，不用去撬公式。

第三，交付前把公式转成值的地方一定要留原版。发给别人的表，如果是为了对方只读或者避免版本兼容问题而转成值，自己手上必须留一份带公式的源文件。我见过太多次"改了参数要重算"却找不到原始文件的情况。

提醒：公式多的工作簿，按 `Ctrl+``（Tab上面那个键）可以切换显示公式而非结果，排查时非常好用，检查完再按一次切回来。

最后分享一个我自己用得最多的习惯：任何一张新表，先在最上面留三行——第一行放标题，第二行放口径说明（统计的是哪段时间、含不含税、去不去重），第三行开始才是表头。这看起来跟函数没关系，但它能让你在三个月后打开文件时，不用重新猜一遍当初的公式到底在算什么。函数是把刀，用得好不好，一半看磨刀，一半看有没有人告诉过你这刀该往哪切。