简介面向日常办公、数据处理与表格分析初学者这份PDF系统整理了WPS官方函数公式视频课程的学习入口汇总374个实例覆盖VLOOKUP、IF、SUMIF、SUMIFS、COUNTIF、INDEX与MATCH、TEXT、MID、SUBTOTAL等高频函数并延伸到条件求和、跨表匹配、不完全匹配查询、销售统计、日期计算及云同步协作等实用场景适合希望从入门函数逐步过渡到综合数据处理的读者查漏补缺。资源包共1个PDF文件约331KB以课程清单和知识点索引为主便于按函数名称或应用场景快速定位对应视频减少四处搜索的时间。目前已有1003人学习内容强调函数组合与真实表格问题可帮助读者建立较完整的WPS公式工具箱并提升统计、核对与汇总效率。1. 从“收藏了就等于学会”到“遇到问题能查得到”这套函数课到底值不值得跟上周某公司做运营的朋友发来一张表VLOOKUP 报了一整屏 #N/A。她说自己收藏了几十个 WPS 教程视频真到用的时候还是一片空白。问题不在于记性而在于大多数教程把函数讲成孤立知识点不告诉你什么时候该用哪个函数也不说参数填错会出现什么现象。这套 WPS 函数公式视频教程走的是另一条路374 个短课最长不到 4 分钟多数在 1 分钟左右一个函数配一集一个场景从 SUM、IF 这种入门款一路排到 IRR、MMULT 这类偏专业的函数。适合已经会用表格但效率卡住的人也适合想系统补一遍的人。2. 一百八十多条目录怎么排顺序按“一周内用得上”重排成四条主线2.1 先看清结构编号断档、时长分布与三类内容翻课程目录时有一件事值得注意编号是断的。列表里出现了 1-1、3-10、4-4、15-3 这种编号对应的内容却分别是“云文档初识”“打印多余空白页”“云同步多人编辑”和“三大实用函数”。这说明原始课程按模块录制后来被抽成片段放到学习中心编号沿用旧体系跟知识点顺序对不上。照着编号从 1 学到 188一定会学得七零八落。再看时长。最短的一集 42 秒Hyperlink 批量插入超链接最长的一集 3 分 59 秒云是什么中位数落在 1 分 20 秒上下。这个长度只够完整演示一次操作讲不了推导过程。因此正确的观看方式应该是先把进度条拖到结尾看结果再回到开头跟着逐步操作中途暂停两次自己输入一次公式。把所有视频直接当连续剧看完效果会很差。内容大致分三类。第一类是纯函数课占绝大多数一集一个函数从 ABS 到 XIRR 都有第二类是功能操作课比如云文档协作、打印排版第三类是组合应用题比如“快速统计产品销售总额以及总量”“出勤天数计算全勤奖”“快速计算新个人所得税”。前两类当字典用遇到问题查第三类才需要坐下来慢慢学因为它教的是函数之间如何衔接。还有一个容易被忽略的细节同一个函数可能出现在多集里。VLOOKUP 除了两集正课“实用三大函数”“秒速配两表数据”还挂了四集 #N/A 排错专题编号 32、34、38、39RANK 有 RANK、RANK.EQ、RANK.AVG 三集四舍五入相关有 ROUND、ROUNDUP、ROUNDDOWN、INT、TRUNC 五集。这些“同族课”必须连着看否则很容易只知道一个函数的存在不知道它们各自解决哪一段边界问题。提示课程目录里的链接很长建议先把标题和时长抄进一张自己的表加一列“是否已跟练”比在收藏夹里翻要有效得多。2.2 第一周统计与条件求和先把每天的报表跑通统计类函数是投入产出比最高的一段。SUM、AVERAGE、MAX、MIN、COUNT、COUNTA 这六个是底子SUMIF、SUMIFS、COUNTIF、COUNTIFS、AVERAGEIF、AVERAGEIFS 是真正干活的部分再加一个 SUBTOTAL 处理筛选后的可见行。这一组学完日常 80% 的汇总需求就不用再手工筛了。我一般让人按“单条件 → 多条件 → 可见行”三段走。先拿一张模拟的销售明细表练手字段就四个日期、区域、产品、金额。第一步只做单条件求和把华东区的金额加起来 单条件求和统计 B 列区域中等于“华东”的 D 列金额 SUMIF(B2:B1000, 华东, D2:D1000) 条件写在单元格里更灵活改 E1 的内容结果自动变 SUMIF(B2:B1000, E1, D2:D1000)第一个公式的三个参数依次是条件区域、条件本身、求和区域。这里有一个常见的错误很多人以为第三个参数可以省略实际上省略时会对条件区域自身求和如果条件列不是数值列结果就是 0还找不到原因。条件写在单元格里那种写法更适合做下拉切换后面的 SUMIFS 也会沿用这个思路。第二步上多条件。SUMIFS 的参数顺序和 SUMIF 是反的求和区域放在最前面后面成对出现“条件区域 条件” 多条件求和区域华东 且 产品显示器 且 金额5000 SUMIFS(D2:D1000, B2:B1000, 华东, C2:C1000, 显示器, D2:D1000, 5000) 日期区间把比较符拼进字符串日期用 DATE 构造避免格式歧义 SUMIFS(D2:D1000, A2:A1000, DATE(2024,1,1), A2:A1000, DATE(2024,6,30))这里有两处需要留心。第一所有条件区域的行数必须完全一致B2:B1000 配 D2:D999 会直接报错或算出错误结果而且这种错误在数据量大时极难发现。第二写比较符时必须用引号把符号包住再用 连接变量直接写 “2024-1-1” 这种字面量在不同日期格式设置下会被当成文本比较结果常常相差一个月。第三步是 SUBTOTAL。它的第一个参数是功能编号9 是求和、3 是计数前面加 100即 109、103表示忽略手动隐藏的行。做筛选报表时用 109用 9 会把隐藏行也算进去这是最容易混淆的一条。整套课程里关于可见行计算的只有这一集但它在实际报表里的出场率极高。2.3 第二周查找引用链按 VLOOKUP → MATCH → INDEX 的顺序查找引用这一族的课最多也最该讲顺序。VLOOKUP 覆盖的场景最广先把它学熟然后学 MATCH因为它解决的是“某个值在第几行”这个独立问题最后学 INDEX用 INDEX MATCH 组合解决 VLOOKUP 查不了的方向。为什么不建议一上来就学 INDEX MATCH因为它由两个函数套起来参数里还夹着区域和序号新手很容易把行列两个参数写反得到一串看似正确、实际错位的数字。先学 VLOOKUP 建立“查找值—区域—列号—匹配方式”的思维模型再回头看 INDEX MATCH才知道每一步各管什么。那四集 #N/A 排错专题不要一开始就看。没有实际踩过坑的人看完也记不住。正确的位置是放在整个查找链的末尾等你至少写错过三次再回去看每一集都能对上自己犯过的某个错。这个顺序上的小调整比多看十集正课更有用。2.4 第三周文本与日期清洗脏数据都在这里真正拖慢表格的往往不是公式写不出来而是数据本身不干净。这一块要学的是 LEFT、RIGHT、MID、LEN、FIND、SEARCH、SUBSTITUTE、REPLACE、TRIM、CLEAN、TEXT、TEXTJOIN 这一串加上日期侧的 TODAY、DATE、DATEDIF、EDATE、EOMONTH、WORKDAY、NETWORKDAYS。学习这类函数最好配一份自己手上真实但已经脱敏的数据。用课程里的示例数据跟练第二天就会忘用自己那份带着多余空格、混着文本型数字、日期格式五花八门的表跟练每解决一个具体问题记忆会牢固得多。这一组学完你会发现自己以前手工删空格、手工拼字段的时间能省下一大半。2.5 按岗位挑着学四条不同的取舍线如果想省时间可以按岗位跳过一大半内容。下面这张表是我按实际使用频率整理的取舍建议左列是岗位右列是优先看的课其余可以晚点再看。岗位方向优先跟练的课程可以暂时跳过财务成本PMT、PV、FV、RATE、NPV、IRR、XIRR、DB、个税与年终奖两集三角函数、矩阵、分布类人事行政DATEDIF、EDATE、EOMONTH、NETWORKDAYS、WORKDAY、MID 提身份证、出勤与全勤奖工程函数、复数函数运营销售VLOOKUP 全系列、SUMIFS、COUNTIFS、RANK 三集、销售总额统计财务函数、统计检验类数据分析STDEV 系列、CORREL、COVAR、QUARTILE、PERCENTRANK、FREQUENCY、LINEST文本替换类这么分的好处是一两周内就能覆盖自己岗位的高频场景剩下的当字典用到再查。硬着头皮按目录全刷一遍往往刷到第 60 集就停了。3. 查找引用三件套VLOOKUP 的四个参数到底怎么填3.1 参数逐个拆第二参数写成整列引用会出什么事VLOOKUP 的语法是 VLOOKUP(查找值, 查找区域, 返回列号, 匹配方式)。四个参数里第一个和第三个最容易被写错第二个最容易埋下性能问题。第一参数是查找值这里最常见的失误是没有锁定列。写VLOOKUP(A2, 表2!$A:$F, 5, 0)往下拖时A2 会变成 A3、A4这是想要的效果但如果公式要往右拖A2 就会变成 B2、C2直接查不到东西。往右拖的场景必须写成$A2锁列不锁行。第二参数是查找区域最大的诱惑是直接写整列$A:$F。写起来省事代价是全列参与计算。十万行数据、几百个 VLOOKUP 公式连着整列引用重算一次要等好几秒这是“表格越用越卡”的常见来源。我一般写成固定区间$A$2:$F$10000或者用 CtrlT 把源数据转成表格后引用结构化名称。第三参数是返回列号从查找区域的第一列开始数不是从工作表的 A 列开始数。很多人把$B:$F当成区域却按 A 列起数填了 5实际返回的是 F 列。要避开这个问题可以在旁边临时写一个COLUMN(F1)-COLUMN(B1)1验证一下确认后再填回去。第四参数后面单独说。 标准写法查找值锁列、区域固定区间、返回第 5 列、精确匹配 VLOOKUP($A2, 员工表!$A$2:$F$10000, 5, 0) 用 MATCH 动态算列号源表插列后不会错位 VLOOKUP($A2, 员工表!$A$2:$F$10000, MATCH(部门, 员工表!$A$1:$F$1, 0), 0)第二段公式是给源表结构会变动的场景准备的。MATCH 在表头行里找“部门”这两个字出现在第几列把这个数字直接喂给 VLOOKUP 的第三参数。源表中间插了一列公式不用改。代价是多一次查找计算数据量特别大时会慢一点属于典型的“用性能换可维护性”。3.2 第四参数 0 与 1精确匹配和“找区间”是两种活第四参数填 0或 FALSE是精确匹配填 1或 TRUE是近似匹配省略时默认是 1。这个默认值是新手最容易踩的坑少写一个 0公式不报错结果却是错的。精确匹配的逻辑很直白找到完全一样的那一行找不到就返回 #N/A。近似匹配的逻辑是“二分查找”它假定查找区域的第一列已经按升序排好然后找到小于等于查找值的最后一个位置。所以它真正的用途不是“模糊查找”而是“分段匹配”比如按分数查等级、按金额查提成档位、按工龄查年假天数。 分段查提成比例A 列是档位下限且已升序排列返回 B 列比例 VLOOKUP(D2, $A$2:$B$8, 2, 1) 典型的档位表长这样A 列必须升序 0 0.03 50000 0.05 100000 0.08 200000 0.12用近似匹配有三条硬性要求第一列必须升序否则结果不可预测档位表要用下限值不能用区间文字查找值小于第一档下限时返回 #N/A。最后这一条经常被忽略比如查 0 元业绩而档位表第一行是 1就会报错。稳妥的做法是在档位表最上面补一行下限为 0 的记录。顺便说一句很多人把“按编号前缀查供应商”这种需求交给第四参数填 1这是典型的误用。它本质上是分段匹配不是文本模糊匹配处理不了前缀问题真要做部分字符匹配得靠下一节说的通配符。3.3 逆向查找IF({1,0}) 与 INDEXMATCH 的取舍VLOOKUP 有一个先天限制只能从查找列往右返回返不回左边的列。课程目录里专门有一集叫“vlookup 可以逆向查找吗”讲的就是这个。绕过去的路有两条。第一条是用 IF({1,0}) 在内存里重排一个虚拟区域 假设要按 C 列姓名查 A 列工号 IF({1,0}, C列, A列) 在内存中构造出「姓名在左、工号在右」的两列区域 VLOOKUP(E2, IF({1,0}, $C$2:$C$1000, $A$2:$A$1000), 2, 0)这个写法在老版本里必须按 CtrlShiftEnter 输入数组公式新版动态数组直接回车即可。它的优点是改动量小原来会写 VLOOKUP 的人换个区域就能用缺点是整个区域在内存里重建数据量大时明显变慢而且公式可读性差交接给别人容易被改坏。第二条是 INDEX MATCH MATCH 找位置INDEX 按位置取值方向随意 INDEX($A$2:$A$1000, MATCH(E2, $C$2:$C$1000, 0)) 返回多列时把列号做成可下拉的形式 INDEX($A$2:$F$1000, MATCH($E2, $C$2:$C$1000, 0), COLUMN(B1))思路拆开看很清楚MATCH 在姓名列里找目标值排第几行INDEX 拿这个行号去工号列里取第几行的值。它不受方向限制源表插列也不影响因为两个区域是分开指定的。代价是要写两个函数参数多了容易写串。我自己的取舍标准是一次性、临时性的表用 IF({1,0}) 图快要交给别人维护、或者要长期跑的报表一律用 INDEX MATCH。理由很实际三个月后回来看 IF({1,0}) 那个公式自己也得多看两眼。3.4 部分字符匹配通配符只在精确匹配下生效“只有部分字符匹配怎么查”是课程里单独占一集的问题比如订单号后面带了批次后缀要用订单号主体去匹配。这类需求靠通配符解决。VLOOKUP 的精确匹配模式支持三个通配符*代表任意长度字符?代表单个字符~是转义符用来查真正的星号或问号。写法是在查找值里拼接通配符 查找以 A2 内容开头的记录A2 里是订单号主体 VLOOKUP(A2*, $B$2:$D$1000, 3, 0) 查找包含 A2 内容的记录 VLOOKUP(*A2*, $B$2:$D$1000, 3, 0) 要查找的内容本身带 * 号用 ~ 转义 VLOOKUP(SUBSTITUTE(A2,*,~*), $B$2:$D$1000, 3, 0)三个前提必须同时满足第四参数必须是 0通配符只在精确匹配模式下有效被查找的内容必须是文本纯数字加通配符匹配不到只要出现多个匹配项返回的是从上往下第一个命中的顺序不确定就容易拿错行。实际做报表时我更倾向用辅助列把复杂匹配拆开先加一列ISNUMBER(FIND(A2,B2))标出哪些行包含目标串再配合 FILTER 或者筛选取值。虽然多一列但每一步都能肉眼验证出问题时不用对着一个嵌套公式发呆。3.5 参数速查四个参数各自的高频错误对照参数位置含义高频错误排查动作第 1 参数查找值往右拖没锁列A2 变 B2改成$A2第 2 参数查找区域整列引用导致重算慢区域没锁被拖动固定区间加$或改用表格结构化引用第 3 参数返回列号从工作表 A 列起数而不是从区域首列起数用COLUMN()相减验证一次第 4 参数匹配方式漏写 0走近似匹配返回错行所有精确查找强制写 0不用默认值这张表建议打印出来贴在工位上。四个参数里任何一个出错现象都是“结果看着像对的”这比直接报错危险得多。4. 条件统计到文本清洗SUMIFS 与 TEXT 的组合拳4.1 SUMIFS 的条件写法比较符必须拼进字符串SUMIFS 的语法是 SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2, ...)最多可以挂 127 组条件。参数顺序和 SUMIF 相反这一点必须记牢写反了不报错只会算出一个看着合理的错数。条件写法分两类。等值条件直接写内容或者引用单元格比较条件要把运算符用引号包起来再用 连接变量 等值条件直接写文本 SUMIFS($D$2:$D$1000, $B$2:$B$1000, 华东) 等值条件引用单元格做成交互式报表 SUMIFS($D$2:$D$1000, $B$2:$B$1000, $J$1, $C$2:$C$1000, $J$2) 比较条件运算符加引号用 拼变量 SUMIFS($D$2:$D$1000, $D$2:$D$1000, $J$3) 文本包含条件用通配符 SUMIFS($D$2:$D$1000, $C$2:$C$1000, *显示器*)这里有三个细节值得单独拎出来。第一比较运算符一定在引号里变量一定在引号外写成J3就是把 J3 当成文本比较了结果永远是 0 或者全量。第二所有条件区域的行数必须和求和区域完全对齐错一行不会报错但会静默错位这类问题在大表里排查起来非常费劲。第三文本包含条件用通配符在 SUMIFS 里能用但效率不高几十万行时会明显卡顿能改用辅助列标记就先标记。另外提醒一句整列引用在 SUMIFS 里比在 VLOOKUP 里更危险。SUMIFS 会对每个条件区域做全列扫描五六个条件挂上去、每个都是整列一次重算可能要十几秒。固定区间是基本纪律。4.2 SUBTOTAL 的 3 与 103筛选行和隐藏行的差别SUBTOTAL 是这套课程里最容易被低估的一个函数。它的语法是 SUBTOTAL(功能编号, 区域)第一个参数不只是选功能还控制“要不要忽略隐藏行”。功能编号分两段1 到 11 是基础版包含隐藏行101 到 111 是对应功能的增强版忽略手动隐藏的行。常用的几个是 3COUNTA 计数、9SUM 求和、1AVERAGE 平均、4MAX、5MIN对应增强版就是 103、109、101、104、105。 9求和时包含手动隐藏的行 SUBTOTAL(9, $D$2:$D$1000) 109求和时跳过手动隐藏的行适合筛选后看小计 SUBTOTAL(109, $D$2:$D$1000) 3 / 103计数区别同上 SUBTOTAL(103, $A$2:$A$1000)关键区别在“隐藏”这两个字上。SUBTOTAL 的 10x 系列只忽略手动隐藏的行不忽略筛选掉的行而筛选掉的行SUBTOTAL 无论哪个版本都会自动跳过。反过来说如果你用“隐藏行”而不是“筛选”来临时收起数据那就要用 109 而不是 9。SUBTOTAL 还有一个隐藏能力它会自动跳过区域内嵌套的 SUBTOTAL 结果。这意味着你可以在明细区每隔一段插一个 SUBTOTAL 小计再在最外层套一个 SUBTOTAL 总计总计不会把小计重复加一遍。做多层汇总表时这一点能避免大量的手工调整。注意SUBTOTAL 和 AGGREGATE 不是一回事。AGGREGATE 能忽略错误值SUBTOTAL 不能区域里有 #N/ASUBTOTAL 的结果也是 #N/A。4.3 TEXT 的格式代码日期、金额、占位符三套写法TEXT 是把数值转成指定格式文本的函数语法 TEXT(值, 格式代码)。它的价值在于拼接字符串时数值不控制格式就会露馅。“本月合计”D2 拼出来的可能是 12345.6789而你要的是 12,345.68。格式代码分三套。数值用 0 和 # 做占位0 是位数不够补零# 是位数不够不显示日期用 y、m、d、h、s 这些字母组合文本可以加引号包住固定内容。 金额千分位保留两位 TEXT(D2, #,##0.00) 日期转中文年月 TEXT(A2, yyyy年m月) 百分比保留一位小数 TEXT(B2, 0.0%) 拼接一句话把日期和金额都格式化后再连 截至TEXT(A2,yyyy年m月d日)累计TEXT(D2,#,##0.00)元最容易忽略的一点是TEXT 返回的是文本不是数值。用 TEXT 处理过的列再拿去 SUM结果是 0。这一点在拼接报表标题时看不出问题等到做汇总统计才暴露。稳妥做法是只在最终展示层用 TEXT中间计算层保持数值。还有一个经典问题日期本质上是序列号直接和文本拼接会得到一串 4 万多。必须先 TEXT 格式化或者用 连接时先套 TEXT。同理yyyy-mm-dd里 m 是月份、mm 是补零月份而hh:mm里的 m 是分钟字母重复次数决定补不补零多一个少一个差别很大。4.4 文本截取MID、LEFT、RIGHT 与它们的 B 版本文本截取三件套的语法很直白LEFT(文本, 个数) 从左边取RIGHT(文本, 个数) 从右边取MID(文本, 起始位置, 个数) 从中间取。课程里最典型的一集是“用 MID 一秒提取身份证年月日”因为这是几乎所有人事和行政都会遇到的需求。 18 位身份证第 7 位起取 8 位得到 19900101 这样的出生日期 MID(A2, 7, 8) 转成真正的日期值方便算年龄 TEXT(MID(A2,7,8), 0000-00-00)*1 15 位老身份证第 7 位起取 6 位前面补 19 19MID(A2, 7, 6) 从右侧取 4 位做尾号核对 RIGHT(A2, 4) 提取省份按“省”字定位 LEFT(A2, FIND(省, A2))第二段那个*1是把文本型日期转成数值的技巧等价于 VALUE 函数。转完之后才能拿去做 DATEDIF 算年龄、做条件格式标红。不转换的话看着像日期实际是文本排序会按字符顺序排2 月会排到 10 月后面。真正需要留神的是 B 版本函数LEFTB、MIDB、LENB、REPLACEB。它们按字节计数不是按字符。中文在默认编码下一个汉字占 2 字节所以LENB(张三)是 4LEN(张三)是 2。什么时候用 B 版本混合中英文的字段要按固定字节位截取时。什么时候绝对不能用纯中文内容按字符位截取时用 B 版本会把汉字劈成半个得到乱码。顺带说 TRIM 和 CLEAN。TRIM 清掉首尾空格并把中间连续空格压成一个CLEAN 清掉不可打印字符。这两个函数在处理从系统导出的数据时几乎是必备的导出表里带不可见字符导致 VLOOKUP 匹配不上这是血泪经验级别的常见问题看着两列一模一样就是查不到套一层TRIM(CLEAN(A2))立刻正常。5. 六个高频翻车现场从 #N/A 到小数误差的排查清单5.1 现象VLOOKUP 报 #N/A肉眼看着两列内容完全一样原因通常有三种。第一种是格式不一致一边是数值 12345一边是文本 “12345”等号判断为 FALSE。第二种是带了不可见字符从网页或系统导出时混进了空格、换行或者不间断空格TRIM 也清不掉。第三种是查找区域没锁往下拖的时候区域整体下移越往下漏掉的行越多。解决顺序建议这样走先用A2B2做一次直接比对返回 FALSE 就说明内容不等再用LEN(A2)和LEN(B2)比长度长度不同基本可以确定有隐藏字符然后用TRIM(CLEAN(A2))清洗一遍还不行就上SUBSTITUTE(A2,CHAR(160),)处理不间断空格格式问题用VALUE(A2)或者分列功能统一成数值。最后把查找区域改成绝对引用或者在公式里直接写成固定区间。5.2 现象SUMIFS 结果比手工筛出来的少一截最典型的原因是条件区域大小不一致。求和区域是 D2:D1000条件区域写成了 B2:B999函数不会报错它按两者的交集或者错位对齐去算结果就少一截。第二个原因是日期条件写成了字面量2024-1-1被当成文本比较实际上大部分行都不满足。解决方法是把每个区域的起止行号逐个核对一遍用 F4 锁成绝对引用后整体检查。日期条件一律改成DATE(2024,1,1)这种写法避开区域设置带来的格式歧义。还有一种隐蔽情况条件里写的是 “华东”源数据里是 “华东 ”带一个尾随空格SUMIFS 不会自动忽略需要先清洗数据列。5.3 现象ROUND 之后还是对不上差一分钱原因是浮点数在计算机里的存储方式。2.675 在二进制里没法精确表示实际存的是 2.67499999...ROUND(2.675,2) 得到 2.67 而不是 2.68。这在财务报表对账时是致命的一份几千行的表累计误差能有几块钱。解决办法有两个层次。单点修正可以把公式改成ROUND(ROUND(A2,4),2)先多保留两位再修约系统性方案是在文件选项里开启“以显示精度为准”让参与计算的值等于显示的值。这个开关有副作用开启后所有小数都按显示位数截断后续再改格式也回不去所以要先备份一份原始文件。注意单元格格式里设置“保留两位小数”只改显示不改存储值。合计行的结果和逐行相加的结果对不上八成就是这个原因。5.4 现象INDIRECT 跨表引用报 #REF!INDIRECT 是把文本当引用解析语法 INDIRECT(“表名!A1”)。报 #REF! 最常见的原因是工作表名里有空格、横杠或者以数字开头这时候必须用单引号把表名包起来INDIRECT(A2!B2)。第二个原因是表名拼写里有多余空格看着一样实际不一样。还有一个连带问题INDIRECT 引用已关闭的工作簿会返回 #REF!跨文件做动态引用时必须保持源文件打开。这也是我不太推荐用 INDIRECT 做多表汇总的原因稳定性太依赖文件状态。同样的需求用 SUMIFS 配合辅助列或者用 Power Query 合并都比 INDIRECT 靠谱。5.5 现象表格越用越卡改一个格要等好几秒易失性函数是主因。TODAY、NOW、RAND、RANDBETWEEN、OFFSET、INDIRECT 都属于这一类任何一次重算都会触发它们重新求值连带依赖它们的公式全部重算。一张表里散着几百个 OFFSET改一个单元格就能卡半分钟。解决办法是逐个替换OFFSET 求动态区域可以改成 INDEX 配合 COUNTA 算边界INDEX 不是易失性的TODAY 只在一处取值其他地方引用那个单元格而不是每个公式都写一遍RAND 生成的随机数要固定下来选中区域复制后选择性粘贴为值。另外条件格式和数据验证里也会藏易失性函数排查时要一并检查。5.6 现象VLOOKUP 返回了数据但明显是错行第四参数漏写 0走了近似匹配。这时候它找的是“小于等于查找值的最后一个位置”如果查找列没有升序排列返回结果完全不可预测。更麻烦的是这种错误不报错只是偶尔错几行抽查时看不出来。解决办法很简单所有精确查找一律显式写 0不用默认值。已经写好的老公式批量检查一遍用查找替换把,TRUE)和,1)结尾的 VLOOKUP 全部找出来复核。另外近似匹配只应该出现在分段查档位的场景一旦看到它用在编号、姓名、订单号这类字段上基本可以判定是写错了。6. 用一张自测表验收把看过的视频转成能直接使用的能力看视频最大的错觉是“看懂了”。进度条走完跟着操作一遍感觉全会关掉视频自己从空白表开始一半的公式写不出来。我给自己的验收方式是做一张自测表十个场景全部从空表开始不看任何参考写完再对照答案。自测表的样子大概是这样编号场景描述验收标准1按区域汇总金额筛选后小计跟着变用 SUMIFS SUBTOTAL筛选任意区域数值正确2按姓名从右往左查工号用 INDEXMATCH 实现源表插列不报错3给成绩分段打等级用 VLOOKUP 近似匹配档位表升序4从身份证提取出生日期并算年龄MID TEXT DATEDIF 串起来结果是数值5把日期和金额拼成一句话TEXT 两处格式化拼接后不错位6统计某产品的订单数并去重COUNTIFS 配合辅助列7计算贷款月供PMT利率除 12、期数乘 12符号正确8找出销售额前五名LARGE INDEX/MATCH重复值处理正确9清洗导入数据的首尾空格与不可见字符TRIM CLEAN清洗后能正常匹配10定位所有 #N/A 并替换成指定文本IFERROR 与 IFNA 各写一遍说清区别十条里能独立完成八条这套课就算吃透了。做不到的回去找对应那一集只看那一集看完立刻重做。这个循环比顺序刷完 374 集有效得多。有两处细节值得在验收时特意较真。一是 PMT 那题很多人算出来的月供是负数因为现金流方向约定没搞清借进来的本金是正的还出去的钱就是负的反过来写就得到正数。这不是错误是符号约定但报表里要统一不然合计时会互相抵消。二是 IFERROR 和 IFNA 的区别IFERROR 会吞掉所有错误包括引用错误、除零错误出问题时你连线索都看不到IFNA 只处理 #N/A。我自己的习惯是查找类公式一律用 IFNA其他错误留着让它显形这就是给自己留的后悔药。还有一个习惯是我强制自己执行的每学完一个函数就在自己的“函数笔记”表里加一行写清楚三件事——它解决什么问题、最容易写错的参数是哪个、报错时先看哪里。写不出来说明还没学会。这张表攒到三四十行的时候你会发现查它比查课程目录快得多。从那以后我每次带新人都要求他们先不看视频拿十道场景题硬写一遍写不出来再去目录里找对应的那一集。带着问题看一集一分半的视频能顶半小时的盲刷。希望帮到你。本文还有配套的精品资源点击获取