Excel函数与公式53集课：用示例文件练透办公自动化

Excel函数与公式这套53集课程配上Excel 2010示例文件，真正值钱的地方，在于它能不能帮你把“每周手动复制粘贴三小时”变成“写一次公式、以后一直复用”。它属于典型的办公自动化入门材料：内容覆盖统计、判断、查找、文本清洗、日期换算这些日常高频场景，适合每天都要处理表格、又不想学编程的人。最值得关注的，是它提供了配套示例文件；你能对着真实表格改参数、看结果，比只看函数列表强得多。下面按实际学习顺序拆，先说怎么安排顺序，再说环境、参数、场景和排查。

1. 先想清楚这套53集Excel函数与公式该按什么顺序学

很多人拿到一整套课程后的第一反应是从第1集顺着往下看，看到二十几集开始走神，最后记住的函数名不超过十个。问题不在课程，在顺序。函数是工具，工具的记性来自“用上一次”，而不是“看过一遍”。所以真正有效的顺序，是先从示例文件里挑出一张你工作里也会遇到的表，再倒推需要哪几个函数。

1.1 顺着集数刷是最容易放弃的方式

53集如果按每集十几分钟算，全看完大概要十几个小时。这十几个小时里，前三分之一讲基础语法和引用，中间讲统计和查找，后面讲文本、日期、数组和嵌套。问题在于：看到查找函数那一集时，前面讲的引用规则已经模糊了；看到数组那一集时，统计函数的参数顺序又记混了。这不是记性问题，是没有练习闭环。

我的建议是把整套内容拆成三层。第一层是“地基”，包括单元格引用、运算符优先级、括号的作用、错误值含义，这一层必须一次搞透，后面所有内容都建在上面。第二层是“高频件”，SUM、IF、COUNTIF、SUMIF、VLOOKUP、INDEX、MATCH、IFERROR、TEXT、TRIM 这几个，覆盖了大部分日常表格需求。第三层才是“扩展件”，数组公式、财务函数、工程函数、复杂嵌套。第三层不必强求，用到再回来查。

按这个分层，实际学习节奏可以是：地基两天，高频件一周，扩展件按需。每看完一个函数，立刻在示例文件里找一个字段试着套一遍，套不出来再回去看那一集。只看不敲，等于没学，这句话在函数学习上几乎没有例外。

1.2 用示例文件倒推：先看表结构，再看函数

打开示例文件之后，先别急着看公式。先做三件事：数一数有几张工作表、每张表的字段名是什么、哪些列是人工填的原始数据、哪些列是算出来的结果。这一步的价值在于建立“数据流”的概念。

比如一张销售明细表，通常会有日期、区域、产品、数量、单价、金额几列。其中日期、区域、产品、数量、单价是原始录入，金额大概率是公式算出来的。你只要点一下金额列的第一个单元格，看编辑栏里写的是什么，就能知道这一列用的是乘法还是带条件的公式。顺着他写的方式，你能学到“为什么这里要加绝对引用”“为什么这里要套 IFERROR”。

反过来做也成立：先看结果列长什么样，再猜它可能用了什么函数，然后自己写一遍，最后对照原公式。这个过程比被动看讲解有效得多，因为你在做判断，而不是在接受信息。示例文件的真正价值就在这里——它给了你一个可以反复试错的沙盘。

1.3 哪些内容可以快进，哪些必须亲手敲一遍

有几类内容必须动手，不能快进：

• 引用方式：相对引用、绝对引用、混合引用。这个不亲手拖一次，永远会记混。
• IF 的条件写法：大于、小于、等于、文本要加引号、多条件用 AND/OR 或者嵌套。
• 查找类函数：VLOOKUP 的四个参数分别是什么、最后一个参数写 0 和写 1 有什么区别。
• 条件统计：COUNTIF、COUNTIFS、SUMIF、SUMIFS 的参数顺序，以及条件里怎么拼接比较符号。
• 文本清洗：TRIM 去空格、SUBSTITUTE 替换字符、TEXT 转格式，这几个是批量处理脏数据的前置工序。

可以快进的：

• 财务函数里的折旧、内部收益率之类，除非你确实做财务。
• 工程函数和部分统计分布函数。
• 数组公式的细节推导，先用“能跑通”的写法，等遇到性能或结构问题再深入。

还有一类内容容易被当成“简单”而跳过，其实很关键：运算符和括号。加减乘除、连接符、比较符的优先级，以及括号怎么改变计算顺序。很多“公式结果不对”的问题，最后查出来就是少了一对括号。

2. 打开示例文件前先把练习环境和版本差异处理干净

Excel 函数学习最怕两件事：一是把原始示例文件改坏了找不回来，二是明明公式写对了却因为单元格格式不对显示不出结果。这两件事都跟环境有关，跟函数能力无关，但会消耗掉大量信心。

2.1 Excel 2010 示例文件在新版本打开时的兼容提示怎么处理

示例文件来自 Excel 2010 年代，用较新的版本打开时，可能会看到“兼容模式”的提示，标题栏也会带相应的标记。这个提示本身不影响使用，它只是告诉你文件保存格式还是旧格式。处理方式很简单：要么维持兼容模式继续练，要么另存为新格式。练习阶段我更建议维持原格式，因为你练的是函数逻辑，不是文件格式转换，少一步操作少一个变量。

真正需要注意的是函数可用性差异。2010 版本的示例文件不会用到后来才加入的函数，比如 XLOOKUP、TEXTJOIN、动态数组相关的一批写法。如果你在更新的版本里练习，看到别人用这些新函数，不要以为自己的写法错了，只是版本不同。反过来，如果你照着示例文件学，也别指望在新版本里自动获得新函数的能力，那是两回事。

还有一种情况：文件里有宏或者外部数据连接，打开时提示“启用内容”。练习用的示例文件如果是纯公式表，通常不需要启用；如果提示安全警告而你不确定来源，直接选择不启用，先看表格结构和公式，功能上一般不受影响。

2.2 建一个只放练习文件的目录，并保留原始副本

这一步听起来像废话，但它是很多人学到一半放弃的直接原因。我的做法是：

text复制excel-practice/
├─ 00-原始示例/        # 只读，不动
├─ 01-按章节练习/      # 每个函数一个文件
├─ 02-综合练习/        # 把几个函数串起来做的小任务
└─ 03-回收站/          # 改坏的、写废的，先扔这里不删

核心原则是原始文件不碰。每次练习都复制一份出来，改坏了直接删副本，重新复制。这样做的好处是心理负担低——你知道自己不会把材料毁掉，就敢乱试。函数学习必须敢乱试，不敢试就永远停在“看得懂、写不出”。

在 Windows 上建这套目录可以用命令行一次建完：

bat复制mkdir excel-practice\00-原始示例
mkdir excel-practice\01-按章节练习
mkdir excel-practice\02-综合练习
mkdir excel-practice\03-回收站

用命令行而不是右键新建，是因为路径里不要出现空格和特殊符号。带空格的文件路径在部分导入、连接场景下容易出问题，练习阶段就养成习惯，后面省事。

2.3 单元格格式、文本型数字、隐藏空格这三类“看起来正常”的脏数据

这三类问题有个共同特点：表面看数据是好的，公式却算不对。它们不是函数问题，是数据问题，但排查时最容易被误判成函数写错了。

文本型数字。单元格里显示 123，但实际存储的是文本。判断方法：选中单元格看对齐方式，数字默认右对齐，文本默认左对齐；或者用 =ISNUMBER(A2) 判断，返回 FALSE 就是文本。文本型数字会导致 SUM 求和结果为 0、COUNTIF 统计不到、VLOOKUP 匹配不上。转换方式有几种：选中列后用“分列”直接点完成、用 =VALUE(A2) 转换、或者 =A2*1 强制转数值。

隐藏空格。最常见的是前导空格和尾随空格，肉眼完全看不出来。VLOOKUP 匹配不上、COUNTIF 计数偏少，经常是这个原因。排查用 =LEN(A2) 看长度，跟可见字符数对不上就是有空格。清洗用 =TRIM(A2)，它会把首尾空格去掉，中间连续空格压成一个。

单元格格式。公式结果明明是个数字，却显示成日期或者一串井号；或者日期列显示成 45000 这样的序列值。这是格式问题，不是值的问题。改格式即可：选中列，右键设置单元格格式，数字或日期按需选。

注意：判断“公式对不对”之前，先用 ISNUMBER、LEN、ISTEXT 这类函数把数据本身验一遍。经验上，表格里报出来的错误，一半以上出在数据层，不在公式层。

3. 函数真正的门槛是参数和引用，不是函数名

函数名好记，参数顺序难记。SUM 只有一个可变参数，COUNTIF 是两个，VLOOKUP 是四个，SUMIFS 的参数个数还跟条件对数有关。真正让人卡住的不是“这个函数叫什么”，而是“第几个参数填什么、能不能省略、省略了会怎样”。

3.1 从 SUM、IF、VLOOKUP 三个函数建立参数思维

挑这三个函数是因为它们代表了三种典型结构。

SUM 代表可变参数：

text复制=SUM(C2:C10)
=SUM(C2,C4,C6)
=SUM(C2:C10,E2:E10)

参数可以是连续区域、单个单元格、多个区域，数量不限。理解这一点，就能理解为什么 SUMIFS 可以挂很多组条件。

IF 代表固定三参数：

text复制=IF(B2>=60,"及格","不及格")

第一个参数是条件，第二个是条件成立时的返回值，第三个是不成立时的返回值。文本返回值必须加英文双引号，这是新手最容易漏的地方。多条件可以嵌套：

text复制=IF(B2>=90,"优秀",IF(B2>=60,"及格","不及格"))

嵌套时括号的配对要数清楚，建议写完先看编辑栏的括号提示，或者用换行整理结构。

VLOOKUP 代表带查找逻辑的四参数：

text复制=VLOOKUP($A2,数据源!$A:$D,4,0)

四个参数依次是：找什么、在哪找、返回第几列、精确还是近似。最后一个参数写 0（或 FALSE）是精确匹配，写 1（或 TRUE）是近似匹配。日常对账、匹配名称，绝大多数场景应该写 0。写成 1 却拿到奇怪结果，先怀疑这里。

把这三个函数的参数结构吃透，再去看 COUNTIFS、SUMIFS、INDEX+MATCH，会发现它们是同一套思维的变体：条件在前，区域在后，返回值在最后。

3.2 相对引用、绝对引用、混合引用在拖拽时到底怎么变

引用是函数学习里最该动手的部分。规则本身不复杂，但一定要亲手拖一次才记得住。

切换方式：编辑公式时把光标放在引用上，按 F4 循环切换四种状态。这个快捷键值得记，它能把“手打美元符号”的出错率降到很低。

为什么这件事重要？因为公式往下拖的时候，Excel 默认会按相对位置调整引用。如果你写 =VLOOKUP(A2,A2:D100,4,0) 然后往下拖，第二行会变成 =VLOOKUP(A3,A3:D101,4,0)，查找区域整体下移，结果就全乱了。正确写法是把查找区域锁死：=VLOOKUP(A2,$A$2:$D$100,4,0)。

同理，做占比计算时，分母要锁：=B2/$B$11。不锁的话，拖到下面分母会跟着走，占比越算越离谱。这类错误在示例文件里很常见，也是检查别人表格时最先看的地方。

3.3 错误值是最好的调试提示：#N/A、#VALUE!、#REF!、#DIV/0!

很多人看到错误值就慌，其实错误值是 Excel 在告诉你问题出在哪一类。看懂它，排查效率会高很多。

知道原因之后，处理方式分两种。一种是修数据，比如去空格、转数值；另一种是包一层容错，让表格在异常时显示友好结果：

text复制=IFERROR(VLOOKUP($A2,数据源!$A:$D,4,0),"未匹配")

IFERROR 的作用是“出错就显示指定内容”。它在做批量匹配时特别有用，因为一张几千行的表里总会有几条匹配不上，包一层之后，你能一眼看出哪些是没匹配上的，而不是满屏 #N/A。

但要注意边界：IFERROR 会掩盖真实错误。如果公式本身写错了，包上 IFERROR 之后所有行都显示“未匹配”，反而更难查。我的习惯是先用不带容错的版本跑一遍，确认逻辑对了，再加 IFERROR。

4. 把单个函数串成公式：三类高频办公场景的复现步骤

单个函数会用了，下一步是组合。真实表格里几乎没有“只用 SUM”的需求，都是“按条件求和”“按编号查名称”“把脏文本洗干净再统计”。这一节按场景拆，每个场景给出可复现的步骤和判断标准。

4.1 条件统计与多条件汇总

假设有一张明细表，字段是日期、区域、产品、数量、金额，现在要统计“华东区”的总金额。

单条件用 SUMIF：

text复制=SUMIF(B:B,"华东",E:E)

参数依次是：条件区域、条件、求和区域。注意条件区域和求和区域的行数要一致，这是最容易出错的地方。用整列引用 B:B 和 E:E 能保证一致，但数据量大时会有性能开销，后面会讲。

多条件用 SUMIFS：

text复制=SUMIFS(E:E,B:B,"华东",C:C,"A产品")

注意 SUMIFS 的参数顺序跟 SUMIF 是反的：求和区域在最前面，后面才是条件区域和条件成对出现。这个顺序很多人记混，写错了不报错，只是结果不对，所以写完一定要拿一小块数据手工验算。

计数用 COUNTIF 和 COUNTIFS，结构跟 SUMIF/SUMIFS 完全对应：

text复制=COUNTIF(B:B,"华东")
=COUNTIFS(B:B,"华东",E:E,">1000")

带比较符号的条件要拼字符串：

text复制=COUNTIFS(B:B,"华东",E:E,">="&1000)
=COUNTIFS(A:A,">="&DATE(2024,1,1),A:A,"<="&DATE(2024,12,31))

为什么用 ">="&1000 而不是直接写 ">=1000"？因为前者可以引用单元格，写成 ">="&$H$1 之后，改 H1 的值就能改条件，不用改公式。这是把表格做成“可调参数”的关键一步。

判断标准很简单：随便挑一个区域，手工筛选一遍，把金额加总，跟公式结果比。一致就说明参数顺序没错。不一致就先看区域引用是否对齐、条件是否被当成文本。

4.2 查找匹配：VLOOKUP 的四个参数和它的边界

查找是办公场景里用得最多的功能，也是最容易出错的地方。

text复制=VLOOKUP($A2,数据源!$A:$D,4,0)

四个参数：查找值、查找区域、返回第几列、匹配方式。三个硬性约束必须记住：

1. 查找值必须在查找区域的第一列。VLOOKUP 只会往右找，不会往左找。
2. 返回列号是相对于查找区域起点的，不是相对于整张表的。区域从 A 列开始，返回 D 列就写 4。
3. 精确匹配必须写 0。省略或者写 1，遇到近似值会返回意外结果。

VLOOKUP 的边界也很明确：不能向左查、插入列之后列号会错、整列引用在大数据量下慢。向左查的替代方案是 INDEX+MATCH：

text复制=INDEX(数据源!$A:$A,MATCH($A2,数据源!$D:$D,0))

这组写法的好处是不怕插入列，因为 MATCH 定位的是行号，INDEX 按行号取值，中间插多少列都不影响。缺点是比 VLOOKUP 多一层理解成本。我的建议是：日常匹配先用 VLOOKUP，遇到“要往左查”或者“表格结构经常变动”再换 INDEX+MATCH。

还有一个常被忽略的点：查找值两边的数据类型必须一致。一边是数字，另一边是文本型数字，结果一定是 #N/A。遇到匹配不上，先别改公式，先用 =ISNUMBER() 和 =LEN() 把两边各验一遍。

4.3 文本清洗与日期换算，为什么必须放在统计之前

很多人拿到一张外部导出的表，第一反应是直接写统计公式，结果各种对不上。原因通常不是公式错，是数据没洗。

文本清洗的三个主力函数：

text复制=TRIM(A2)                     去首尾空格，中间连续空格压成一个
=SUBSTITUTE(A2," ","")        去掉所有空格
=CLEAN(A2)                    清掉不可见控制字符

外部系统导出的数据里，空格、换行符、不间断空格很常见。它们肉眼看不见，但会让“华东”和“华东 ”变成两个不同的值，COUNTIF 统计出来就少一条。清洗之后再做统计，结果才对得上。

日期处理同理。日期在 Excel 里本质是序列值，但外部导入的日期经常是文本，比如“2024/1/5”被存成字符串。判断方式：=ISNUMBER(A2) 返回 FALSE 就是文本。转换可以用：

text复制=DATEVALUE(A2)
=TEXT(A2,"yyyy-mm-dd")      注意：TEXT 返回的是文本，不能再直接参与日期运算

这里有个容易踩的坑：TEXT 转出来的是文本，看着像日期，但不能直接做日期加减。如果要参与运算，用 DATEVALUE 或者分列转成真日期。

统一格式的写法：

text复制=TEXT(A2,"yyyy-mm-dd")

这个用法适合“只做展示”的列。如果这一列后面还要参与计算，就别用 TEXT，改成设置单元格格式。搞清楚“值是值、格式是格式”，能省掉很多莫名其妙的错误。

5. 结果不对、公式很慢时按这个顺序排查

公式类问题的排查有个基本顺序：数据 → 引用 → 参数 → 版本。这个顺序不能反，因为越靠前的可能性越大。跳过前两步直接改公式，往往越改越乱。

5.1 先查数据源，不要先改公式

判断方法很直接：把公式拆开，单独看每个参数取到的值对不对。

• 看条件区域里到底有哪些值，有没有前后空格、有没有全半角混用。
• 看查找值在目标区域里到底存不存在，用查找功能搜一下。
• 看数值列是不是文本型数字，用 ISNUMBER 验一遍。
• 看有没有合并单元格，合并单元格在排序、筛选、填充时都会出问题。

这一步最常见的三个结论：有空格、格式不一致、数据本身缺失。这三个都不是公式问题，改公式解决不了。

注意：怀疑公式之前，先选中相关单元格看编辑栏里的真实内容。显示出来的值和存进去的值经常不是一回事。

5.2 再查引用范围和整列引用带来的开销

引用范围的问题主要有两类：范围不对、范围太大。

范围不对的表现是“部分行结果正确、部分行错位”。原因通常是相对引用没锁，往下拖的时候区域整体偏移。检查方法：点最后一个出错的单元格，看编辑栏里的区域是不是跟第一行一致（锁定部分应该不变）。

范围太大的表现是“文件变大、改一个格子整表卡一下”。整列引用 A:A 写起来方便，但它会把整列一百万行都纳入计算范围。数据量小的时候感觉不出来，几万行以上、公式几百个的时候，重算就会明显变慢。

优化思路是把整列换成实际范围：

text复制=SUMIF($B$2:$B$5000,"华东",$E$2:$E$5000)

代价是数据增加时要手动扩范围。折中方案是用表格对象（把区域转成“表格”），公式会自动跟着数据扩展。这个转换在较新版本里体验更好，旧版本支持有限，练习阶段先用固定范围也没问题。

还有一个设置容易被忽略：计算选项被设成了手动。这种情况下改了数据公式不重算，看起来像公式失效。检查路径在“公式”选项卡里的计算选项，确认是自动重算。

5.3 最后才看版本差异和函数可用性

排到最后是因为它出现的概率最低，但一旦出现就很难自己想明白。

典型现象：公式完全照抄，别人能算出来，你这边报 #NAME?。原因通常是函数在当前版本里不存在。较新版本加入的函数，在旧版本里打不开，这是硬限制，没有绕过的办法，只能换等效写法。

另一类现象是数组公式的写法差异。老版本里需要按特定组合键确认的数组公式，在新版本里可能直接回车就出结果。这不是你写错了，是计算引擎变了。练习时如果示例文件用的是老写法，照老写法敲一遍，理解它的意图就好，不必纠结按键。

判断自己有没有遇到版本问题，最快的办法是把公式里的函数名一个个拆出来单独试。哪个报 #NAME?，哪个就是这个版本不支持的。

6. 怎么判断自己已经能用了：三个自测任务和后续方向

学完一套课程，最容易产生的错觉是“我都看懂了”。看懂和会用之间隔着一次独立完成。下面三个任务可以当作验收标准，能独立做完两个以上，日常表格基本够用。

6.1 三个能独立完成的自测任务

任务一：条件汇总表。给一张明细表，做一张按月、按区域的汇总表，要求只用公式，不用数据透视表。涉及 SUMIFS、日期区间条件、绝对引用。做完之后手工抽查两三个格子验算。

任务二：两表匹配。给两张表，用编号把名称、单价匹配过来，匹配不上的显示“未匹配”。涉及 VLOOKUP 或 INDEX+MATCH、IFERROR、数据类型检查。要求匹配率能达到 95% 以上，剩下的 5% 能说出为什么匹配不上。

任务三：脏数据清洗。给一张外部导出的表，把文本型数字转成数值、去掉多余空格、统一日期格式，然后再做统计。涉及 VALUE、TRIM、SUBSTITUTE、DATEVALUE、格式设置。

三个任务都不难，但都要求你从零开始写，不能抄。卡住的地方，就是真正需要回去补的地方。

6.2 公式之外：什么时候该换数据透视表或 Power Query

公式不是万能的。有几类需求，用公式硬写会非常痛苦，换成别的工具反而简单：

这个判断很重要，因为很多人的路径是“学了函数就什么都想用函数解”，结果做出一张几百个公式互相引用的表，自己都不敢改。公式的定位是“精确控制单个计算结果”，汇总分析和流程化清洗，交给专门的工具更合适。

6.3 长期使用要养成的几个习惯

最后说几个习惯，都是踩过坑之后才重视的。

留原始数据副本。任何清洗、转换都在副本上做，原始表只读。这样出问题能回溯，也能对比清洗前后的差异。

公式分列写。一个复杂公式拆成几个辅助列，每列算一步，最后再合并。看起来列多了，但排查时能一眼看出哪一步出的问题。等逻辑稳定了，再考虑合并成一个公式。

命名要能看懂。辅助列叫“清洗后编号”，不要叫“辅助列1”。半年后回来改表，名字就是文档。

加注释。关键公式旁边用批注写一句“这里锁列是为了横向扩展”，成本很低，收益很高。

先小样本验证。新公式先在一二十行上跑，确认结果对了再往下拖。直接拖几千行再回头查，代价大得多。

这套 53 集的内容加上示例文件，本质上给的是一套“可练手的材料”。真正决定你能不能用的，不是看了多少集，而是有没有把示例文件里的表换成自己的表，独立跑通一遍。跑通一次，比看十集管用。