刚接触Excel那会儿，我总觉得公式和函数是一回事，直到被一个简单的求和折腾了半天，才意识到这俩其实是两层东西。公式更像是我自己写的一句“指令”，比如 =A1+B1，它由等号开头，后面跟着我手动敲进去的运算内容。函数则是Excel已经打包好的工具，像 SUM、IF 这种，我只要把参数塞进去，它就能帮我干活。说白了，公式是容器，函数是零件，两者经常混在一起用，比如 =SUM(A1:A10) 就是一个带函数的公式。
结构上，一个公式通常分三块。等号是起点，告诉Excel“接下来这句话要算一算”。中间是表达式，也就是具体要算什么，可以是加法、可以是函数调用、也可以两者混合。返回值就是算完之后单元格里显示的东西，可能是数字、文本、逻辑值 TRUE/FALSE，甚至是一个错误值。参数是函数括号里的内容，有的函数要一个参数，像 =TODAY() 什么都不用填；有的要一堆，像 =IF(A1>60,"及格","不及格") 就塞了三个。
参数这个东西值得多说一句。它可以是具体的数字或文本，比如 =LEFT("Excel",2) 里的 "Excel" 和 2；也可以是单元格引用，比如 =LEFT(A1,2)；还能是另一个公式的结果，嵌套进去一层套一层。返回值并不总是我肉眼看到的样子，有时候单元格里显示 "2024-01-01"，实际返回的却是一个日期序列值 45292，格式把它打扮成了日期模样。这种“看到的”和“实际存的”不一致，是新手最容易踩的坑之一。
运算符与优先级
Excel里的运算符分四类，用熟了会发现它们各管一摊。算术运算符负责加减乘除和乘方，+ - * / ^ 这几个天天见。比较运算符用来判断大小或是否相等，= > < >= <= <> 返回的是 TRUE 或 FALSE。文本连接符 & 能把两段文字粘一起，比如 =A1&B1 就能把姓名和部门拼成一句。引用运算符里，冒号 : 表示一个连续区域，逗号 , 表示多个不连续区域，空格则代表两个区域的交集，这个用得少但挺巧妙。
优先级这件事，其实和小学数学差不多。乘除号比加减号先算，比较运算符排在算术后面，文本连接符的优先级更低。括号是万能钥匙，想先算哪块就把它括起来。我个人的习惯是，只要公式里超过两个运算符，就随手加括号，不是为了Excel，是为了三个月后的自己还能看懂。比如 =(A1+B1)*C1 和 =A1+B1*C1 结果天差地别，前者先把和算出来再乘，后者只把B1和C1相乘。
有个容易忽略的点，比较运算符返回的逻辑值参与算术运算时，TRUE 当 1 用，FALSE 当 0 用。=(A1>60)*1 这种写法在老版本里常用来把逻辑值转成数字。引用运算符里的空格求交集，我最早是在做预算表时用上的，=SUM(A1:C10 B5:D15) 只算两个区域重叠的那部分，省得手动去框选。不过这种写法可读性差，现在基本被结构化引用和动态数组取代了。
单元格引用
相对引用、绝对引用、混合引用，这三个词听起来吓人，其实就是美元符号 $ 放哪儿的问题。A1 是相对引用，往下拖一行它就变成 A2，往右拖一列就变成 B1。$A$1 是绝对引用，拖到哪儿都盯着A1不放。$A1 锁列不锁行，A$1 锁行不锁列，这两种叫混合引用。
我当初理解混合引用是靠一张九九乘法表。写 =$A1*B$1，往右往下拖，行标题和列标题各自锁住自己的方向，一个公式铺满整张表。跨表引用就是工作表名字后面加个感叹号，=Sheet2!A1 能直接抓另一张表的数据。表名里有空格的话得用单引号包起来，='销售 数据'!A1，这个细节坑过不少人。跨工作簿引用更麻烦，路径也带进去，='[报表.xlsx]Sheet1'!A1，一旦源文件挪了位置或者关了，公式就报错。
三维引用是我觉得最像“魔法”的一种。=SUM(Sheet1:Sheet12!A1) 能把十二个月份表里同一个位置的数一口气加起来，做年报的时候特别省事。不过它要求所有参与的工作表结构完全一致，中间不能插别的类型的表。这个功能在新版Excel里用得少了，因为动态数组和Power Query能做得更灵活，但老报表里还经常见到它的身影。
公式的输入、编辑与自动计算
输入公式的标准动作是以等号开头，敲完按回车或者 Tab。用 Tab 有个好处，光标会自动跳到右边一格，连续录入的时候手不用离开键盘。想改公式，双击单元格或者按 F2 进编辑状态，方向键能移动光标，跟文本编辑器差不多。Esc 键随时取消，这个比点“取消”按钮快得多。
复制填充是公式最强大的地方，也是最容易出问题的地方。拖填充柄往下拉，相对引用的部分会跟着变。双击填充柄能自动填到数据末尾，前提是左边那列有连续数据。Ctrl+D 是向下填充，Ctrl+R 是向右填充，选中一整片区域再按，比拖鼠标准。粘贴的时候有个小技巧，选择性粘贴里可以只粘公式、只粘数值、甚至只粘格式，做报表时把公式算完的结果转成死数值，能防止别人乱改。
自动计算设置藏得比较深，在“公式”选项卡的“计算选项”里。默认是自动，每次改数据所有公式重算一遍。数据量大的时候这个会拖慢速度，可以切成手动，按 F9 才重算。有个坑是手动模式下保存文件，公式结果不会更新，别人打开看到的是旧数据。我自己的习惯是日常保持自动，只有处理几万行的大表时临时切手动，算完了再切回来。
公式可读性与管理
命名区域是我最推荐新手早点掌握的功能。把 Sheet1!$A$2:$A$100 定义成“销售额”，公式里就能写 =SUM(销售额)，比一长串地址清楚得多。定义名字的位置在“公式”选项卡的“定义名称”，也可以用左上角的名称框直接输。名字不能有空格，不能和单元格地址撞车，比如不能叫“A1”。
表格结构化引用是另一个层次的体验。把数据区域按 Ctrl+T 转成“表格”，引用就变成了 =SUM(表1[销售额]) 这种样式。新增行的时候公式自动扩展，不用手动改范围。表格里的列名就是引用名，改列标题公式里的名字也跟着变，这点比命名区域还灵活。不过表格对格式有要求，表头不能有合并单元格，中间不能有空行，数据源不规范的话得先清理。
批注和注释在公式管理里经常被忽略。给一个复杂公式加批注，写清楚它为什么这么算，过半年回来看还能想起来。新版Excel的注释功能更轻量，鼠标悬停就能看。我见过有人把公式的说明直接写在旁边的单元格里，用 N() 函数包起来，=N("这里算的是税前工资")，显示为0但不影响计算，也算一种土办法。命名区域加批注再加结构化引用，这三样凑齐了，一个中等复杂度的报表基本能做到“打开就知道在算什么”。
我整理这份公式清单的起因挺偶然。有个做行政的朋友发来一张表，说要根据打卡时间判断迟到、从工号里拆部门代码、把两个表格的姓名匹配起来。她电脑里存着一份“Excel函数大全”，几百个函数名字列得整整齐齐，可她一个都对应不上自己的问题。那天我意识到，按字母顺序背函数基本等于白背，按场景去找才管用。
下面这些公式，是我这些年用得最频繁的一批。按“遇到什么事用什么工具”来分，不按菜单栏顺序排。每类里挑几个真正高频的，把参数怎么填、容易错在哪儿说清楚。
逻辑判断与容错
IF 是我最早学会的函数，也是被用得最泛滥的一个。基本写法 =IF(条件, 成立时返回什么, 不成立时返回什么)，三个参数。判断成绩是否及格，=IF(A2>=60,"及格","不及格")。这东西简单到没什么好讲的，问题出在嵌套上。三层以上的 IF 套在一起，括号对不对都靠数，改一个条件要动一大片。
IFS 就是来治这个毛病的。=IFS(A2>=90,"优秀",A2>=80,"良好",A2>=60,"及格",TRUE,"不及格")，条件和结果成对出现，从上往下依次判断，命中一个就停。最后那个 TRUE 相当于兜底，等价于 IF 里最后一个“否则”。我现在的习惯是超过三个分支就用 IFS，别跟自己过不去。老版本 Excel 和部分 WPS 版本没有 IFS，得留意兼容性。
AND 和 OR 是给 IF 当条件用的。=IF(AND(A2>60,B2>60),"双科通过","有挂科") 表示两个条件同时成立。OR 是任意一个成立就行。这俩函数单独用在单元格里会返回 TRUE 或 FALSE，跟直接写比较表达式没区别，真正的价值是塞进 IF 里组合判断。乘号 * 能替代 AND，加号 + 能替代 OR，=IF((A2>60)*(B2>60),...) 这种写法在老手圈子里很常见，因为数组公式时代它比 AND 更好用。
IFERROR 和 IFNA 解决的是“公式一报错满屏难看”的问题。=IFERROR(VLOOKUP(...),"") 的意思是，能查到就显示结果，查不到就显示空白。IFNA 更精准，只在 #N/A 错误时接管，其他错误照常暴露。这两个函数我几乎每个查找公式外面都会套一层，不是为了掩盖错误，是为了让报表干净、让真实的错误浮出来。用 IFERROR 包裹一切是危险的，它会把拼写错误、引用错误一起吞掉，排查起来很痛苦。
查找与引用
VLOOKUP 的名气大到不需要介绍。=VLOOKUP(查什么, 在哪片区域查, 返回第几列, 0)。第四个参数写 0 或 FALSE 是精确匹配，写 1 或 TRUE 是近似匹配。我见过太多人栽在这个参数上，忘了写，默认近似匹配，结果匹配出一堆莫名其妙的数据，还以为是数据源的问题。另一个限制是查找列必须在区域的最左边，想往左查就没辙了。
XLOOKUP 是 Microsoft 365 才有的新函数，用过就回不去了。=XLOOKUP(查找值, 查找区域, 返回区域, 找不到时显示什么, 匹配模式)。查找列和返回列分开放，左右都能查。默认就是精确匹配，不用记那个恼人的第四参数。找不到的时候可以直接指定返回值，不用再套 IFERROR。还能从后往前搜，处理重复值时取最后一个匹配项。缺点只有一个，老版本用不了，发给用 2016 的同事，公式会变成 #NAME?。
INDEX 加 MATCH 是经典组合，在 XLOOKUP 出来之前是进阶玩家的标配。=INDEX(返回列, MATCH(查找值, 查找列, 0))，MATCH 负责找位置，INDEX 负责按位置取值。这套组合的灵活性在于，MATCH 找到的行号可以拿去做别的事，比如配合 OFFSET 动态取区域。它的学习曲线比 VLOOKUP 陡，但理解了“位置”这个概念，很多复杂的查找问题都能拆解。
LOOKUP 是老函数，向量形式 =LOOKUP(查找值, 查找列, 返回列) 有个隐藏特性：查找列必须升序排列，它做的是二分查找。乱序数据用它结果不可预测。CHOOSE 更像是“按编号挑东西”，=CHOOSE(2,"一月","二月","三月") 返回“二月”。它常被用来做简单的映射，比如根据数字 1 到 4 返回季度名，判断分支少的时候比 IF 清爽。
统计求和
SUM 是每个人第一个学会的函数，但它的兄弟 SUMIF 和 SUMIFS 才是日常工作的主力。=SUMIF(条件区域, 条件, 求和区域) 做单条件汇总，比如按部门算工资总额。条件可以写 ">1000"、"销售部"、"<>华东" 这种表达式。SUMIFS 是它的多条件版本，注意参数顺序变了，求和区域排到了最前面，=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2)。这个顺序调换是很多人第一次用 SUMIFS 时踩的坑。
COUNTIF 和 COUNTIFS 跟求和那对是一一对应的，只是数个数而不是加总。=COUNTIF(A:A,"已完成") 数 A 列里有多少个“已完成”。=COUNTIFS(A:A,"销售部",B:B,">5000") 数销售部里业绩超五千的人数。有个容易忽略的点，COUNTIF 支持通配符，"张*" 能数出所有姓张的，"???" 能数出三个字的文本。星号问号这种符号想当普通字符对待，前面加波浪号转义。
AVERAGEIF 和 AVERAGEIFS 用法同构，把“求和”换成“求平均”就行。SUBTOTAL 是我做筛选报表时的秘密武器。它的第一个参数决定算法，1 到 11 是包含隐藏行，101 到 111 是忽略隐藏行。筛选之后想只看可见数据的合计，用 =SUBTOTAL(109, C2:C100)，筛选条件一变结果自动更新。AGGREGATE 比 SUBTOTAL 更强大，能忽略错误值、隐藏行、嵌套的 SUBTOTAL。
文本处理
LEFT、RIGHT、MID 是拆文本的三把刀。=LEFT(A2,3) 取左边三个字符，=RIGHT(A2,2) 取右边两个，=MID(A2,3,4) 从第三个字符开始取四个。工号、身份证号、订单编号这类固定格式的数据，拆解全靠它们。规律不固定的时候，得先拿 FIND 或 SEARCH 找位置。FIND 区分大小写，SEARCH 不区分，SEARCH 还支持通配符。=MID(A2, FIND("-",A2)+1, 10) 这种嵌套就是“从横杠后面开始取十个字符”的意思。
LEN 返回字符个数，看着简单，实战里常用来配合其他函数定位。比如提取括号里的内容，得先算出括号在哪个位置。LEN 和 LENB 的区别在老版本里很关键，LENB 按字节数，中文算两个字节，做中英文混排的宽度计算时会用到。新版 Excel 早就把字符集统一了，这个知识点逐渐变成考古题。
TEXT 函数是把数字和日期打扮成想要的样子。=TEXT(A2,"0.00") 强制两位小数，=TEXT(A2,"yyyy年mm月dd日") 把日期变成中文格式，=TEXT(A2,"[>1000]超额;[红色]不足") 甚至能做简单的条件显示。它的本质是自定义格式代码，学明白了，单元格格式设置那套规则也不在话下。返回的是文本，拿去做后续运算前得先转回数字。
CONCAT 和 TEXTJOIN 是新时代的连接工具。以前的 CONCATENATE 最多接 255 个参数，写起来还一堆逗号。CONCAT 直接接受区域，=CONCAT(A2:C2) 把三个格子连一块儿。TEXTJOIN 更贴心，第一个参数是分隔符，第二个参数问要不要忽略空单元格，=TEXTJOIN("-",TRUE,A2:C2) 就是拿横杠把非空的连起来。TEXTSPLIT 是 365 版本的新函数，作用跟“分列”正好反着，=TEXTSPLIT(A2,"-") 一个单元格里的横杠分隔内容能自动溢出成多列。
日期时间
TODAY 返回今天的日期，NOW 返回此刻的日期时间。这两个是易失函数，每次表格重算都会更新，做“账龄分析”“剩余天数”这种动态计算时离不开。=TODAY() 没有参数，=NOW() 也没有。注意它们返回的是序列值，显示成什么样靠单元格格式决定。
DATE 负责拼日期，=DATE(2024,12,25) 返回 2024 年 12 月 25 日。它的妙处在于参数可以超出正常范围，=DATE(2024,13,1) 会自动进位成 2025 年 1 月 1 日，=DATE(2024,1,0) 会变成 2023 年 12 月 31 日。这种特性拿来做月末推算特别顺手。
DATEDIF 是个“隐藏函数”，在函数列表里找不到，但直接敲能用。=DATEDIF(开始日期,结束日期,"Y") 算整年数，把 "Y" 换成 "M" 算整月数，换成 "D" 算天数。算工龄、算年龄、算合同剩余期限都靠它。有个冷知识，"MD" 参数算忽略年月之后的天数差，结果偶尔会出人意料，用它之前最好测试几个案例。
EDATE 加月份，=EDATE(A2,3) 三个月后的同一天。EOMONTH 直接跳到某月最后一天，=EOMONTH(A2,0) 本月月末，=EOMONTH(A2,-1) 上月月末。这两个做账期、做结算周期的场景里出现频率极高。WORKDAY 和 NETWORKDAYS 处理工作日。=WORKDAY(A2,10) 从今天往后推十个工作日是哪天，=NETWORKDAYS(A2,B2) 两个日期之间有多少个工作日。它们都有第三个可选参数，把法定节假日列表指定进去，能排除春节、国庆这些特殊日子。
动态数组与数据清洗
动态数组是 Excel 近几年最大的变化。UNIQUE 一键去重，=UNIQUE(A2:A100) 把不重复的值全列出来，自动向下溢出。FILTER 按条件过滤，=FILTER(A2:C100, B2:B100="销售部") 把销售部的所有行筛出来，源数据变化结果跟着变。SORT 排序，=SORT(A2:C100, 3, -1) 按第三列降序排。SORTBY 更灵活，能按另一个区域的大小来排，=SORTBY(A2:A100, B2:B100, -1) 让 A 列按 B 列的数值排序。
这几个函数组合起来能替代很多手动操作。以前做“去重后排序”得复制、数据、删除重复项、排序，好几步。现在 =SORT(UNIQUE(A2:A100)) 一个公式搞定。SEQUENCE 生成序列，=SEQUENCE(10) 出来 1 到 10，=SEQUENCE(5,3) 出来五行三列的矩阵。做编号、做日期序列、做乘法表的辅助列都方便。
LET 和 LAMBDA 是给公式“编程”的工具。LET 允许在公式里定义变量，=LET(x, A2+B2, y, x*1.1, y)，先算出 x，再基于 x 算 y，最后返回 y。复杂公式拆成有名字的中间步骤，可读性大幅提升，同一个中间值被多次引用时还能少算几遍。LAMBDA 更进一步，能自定义函数，=LAMBDA(x,y,x*y)(3,4) 返回 12。配合“名称管理器”把 LAMBDA 存起来，就能像用内置函数一样用自己造的函数。
数据清洗里最头疼的是文本和数字混在一起。动态数组配合 TEXTSPLIT、TEXTBEFORE、TEXTAFTER 这几个新函数，处理地址拆分、姓名分离、编码解析这类活儿轻松很多。这些函数目前只有 Microsoft 365 和较新版本的 WPS 支持，发给用旧版的同事会显示错误。我自己的做法是，给对方发文件之前先用“选择性粘贴-数值”把动态数组的结果固化下来，公式留着，但对方看到的是静态数据。
那些红色的错误值刚出现在屏幕上时，我第一反应总是点一下单元格看看公式是不是敲错了。后来次数多了才发现，绝大多数报错根本不是手抖打错字，而是数据本身藏着问题、引用方式不对、或者两边的类型压根对不上。这一章我把这些年踩过的坑整理出来。每个错误值都像是一种症状，认得出它，才有可能找到真正的病灶。
常见错误值识别
DIV/0!：拿零去当分母了
#DIV/0! 的意思直白到不用翻译，公式里出现了除以零的情况。分子是多少不重要，分母是 0 或者指向了一个空单元格，Excel 就没法算。我做销售报表时经常遇到，某个业务员当月没出单，平均值算下来分母就是零，满屏红字。
处理方式看场景。确实不能算的时候，用 =IF(B2=0,"",A2/B2) 提前拦一道。也可以用 =IFERROR(A2/B2,0) 把错误吞掉换成 0。要提醒一句，被除数所在单元格如果是文本，Excel 会先尝试转成数字，转不动的话报的是 #VALUE! 而不是这个。
VALUE!：类型对不上
#VALUE! 出现的频率仅次于 #N/A。典型的触发方式是拿文本去参与算术运算，比如 A 列存着“100 元”这种带单位的文字，B 列去乘它，结果必然是错的。表面上看格子里的数字整整齐齐，点进去编辑栏才发现是文本格式的数字，或者前后带了看不见的空格。
日期运算里也会撞上这个。=A2-B2 两个日期相减本应该得到天数，如果其中一个日期是文本写的“2024.1.1”，减法就崩了。解决办法是用 DATEVALUE 或者手工拆年份月份日期，把文本还原成 Excel 认得的序列值。我碰到这种数据的习惯是先拿 =ISNUMBER(A2) 扫一遍全列，哪些是真数字哪些是伪装的一目了然。
REF!：引用被人挖走了
这个错误是我在整理别人发来的表格时见得最多的。公式本来引用了 D 列，有人把 D 列整列删掉了，公式里的引用就悬空，显示成 =SUM(A1:#REF!)。它不是数据的问题，是结构被动过了。
想修复只能重新指向正确的区域。批量场景里有个土办法，按 Ctrl+H 把 #REF! 替换成想要的列引用，前提是你清楚原来指的是哪一列。预防的办法是删行删列之前先看一眼有没有公式依赖它，或者干脆把源数据转为表格（Ctrl+T），结构化引用对插入删除的容忍度高得多。
NAME?：Excel不认识这个名字
#NAME? 分两种。一种是函数名拼错了，VLOOKUP 敲成 VLOKUP，Excel 找不到这个函数。另一种是引用了不存在的命名区域，或者函数在当前版本里根本没有——比如在 Excel 2016 里敲 XLOOKUP，返回的就是这个错误。
我自己犯过的错是把中文括号当成英文括号用了。=SUM（A1:A10） 里的括号是全角的，Excel 直接判为无法识别的名称。检查时把公式里所有标点都放大看一眼，中文逗号、中文引号都是常见嫌疑犯。
NUM!：数字大到装不下
公式算出来的结果超出了 Excel 能表达的范围，或者给了函数一个它没法接受的数值参数。=SQRT(-1) 求负数的平方根，=LOG(0) 对零取对数，都会触发。迭代运算里结果来回震荡不收敛，也会冒出来。
这个错误相对少见，遇到时先看函数的参数范围。DATE 函数里月份写 -1000 这种离谱数字也可能报，虽然它本身能处理超范围的进位。
NULL!：两个区域根本没交集
#NULL! 只在一种情况下出现：公式里用了空格作为交叉运算符，但两个区域其实不相交。比如 =SUM(A1:A5 C1:C5)，冒号表示连续区域，空格表示取交集。A 列和 C 列之间没有重叠，Excel 找不到公共部分就报错。
这个错误值很冷门，我做了快十年表也就见过几次。基本都出现在手写多区域引用时不小心敲了空格，原本想写逗号。
N/A：查找找不到
#N/A 是查找函数的专属报错。VLOOKUP、MATCH、XLOOKUP 精确匹配时没找到目标值，就返回它。数据源里确实没有这个值，这个错误是合理的，它诚实告诉了你“查无此人”。
麻烦的是很多时候我们认为数据源里有。同名不同空格、大小写差异、数字存成了文本，都会导致匹配不上。我通常在 VLOOKUP 外面套 IFNA 显示一个提示文字，但不会套 IFERROR——因为其他错误应该暴露出来让我知道公式哪里写坏了。
语法与引用错误
括号不匹配是新手最容易犯的。=IF(A1>0,SUM(B1:B10)，右括号少了一个，Excel 弹窗问你是不是想输入 =IF(A1>0,SUM(B1:B10)) 并帮你补上。它猜对的时候还好，猜错的时候补在奇怪的位置，公式表面通过了，结果却是错的。我的习惯是写完嵌套公式先数左括号和右括号个数，数量对不上就一定有窟窿。
分隔符的坑更隐蔽一些。中文版 Excel 通常用逗号做参数分隔符，但有些区域设置下用的是分号。从别人那儿复制的公式，或者从网页上扒下来的，逗号分号混在一起就报错。跨版本时这个问题尤其烦，同一台机器打开不同来源的文件，分隔符可能就不统一。
循环引用是另一种。A1 的公式里写了 A1，或者 A1 引用 B1，B1 又绕回来引用 A1，形成了一个闭环。Excel 会弹窗警告，状态栏也会提示循环引用的位置。有时候这个环跨了好几个工作表，甚至藏在条件格式里，找起来得用“公式”选项卡下的“错误检查-循环引用”来定位。真要主动用循环计算（比如迭代求值），得去选项里打开迭代计算并设置次数，但这类需求极少数场景才用得上。
区域引用错误往往跟 $ 符号有关。相对引用、绝对引用、混合引用，复制公式时谁变谁不变，全看那几个美元符号加在哪儿。=A1 往下拖变成 =A2，=$A$1 怎么拖都不变，=A$1 往下拖行号锁死列会动。区域引用写错一大片的情况，多半是复制前忘了锁引用位置。
数据类型与格式问题
文本数字是表格世界里的伪装大师。单元格左上角那个绿色小三角就是它的标记，对齐方式也暴露了：数字默认右对齐，文本默认左对齐。肉眼扫一眼大量数据很难发现，用 =ISNUMBER(A2) 或者 =ISTEXT(A2) 批量检查才靠谱。
处理文本数字最直接的办法是选中整列，用“分列”功能一路下一步，最后一步选“常规”，Excel 会自动把它们转成真正的数字。用 VALUE 函数也能转换，=VALUE(A2) 把文本形式的数字变成数值。需要注意，转换前如果单元格里带了空格、逗号、货币符号，得先清理干净，=VALUE("1,000") 会报错。
日期序列值这件事，背后其实是 Excel 把日期当成数字在存。1900 年 1 月 1 日对应数字 1，2024 年的某一天对应四万多的整数。所以两个日期相减得到天数，日期加数字得到偏移后的日期。一旦单元格被识别为文本，这些运算就全乱了。判断方法是用 =ISNUMBER(A2) 检查某个看起来像日期的格子，返回 FALSE 就说明它被当文本处理了。
不可见字符是最难缠的。从网页复制过来的数据里经常带着清零宽空格、换行符、不间断空格，这些字符在单元格里看不见，编辑栏里也未必显示得出来。用 =LEN(A2) 对比 =LEN(TRIM(A2))，两个结果不一样就说明有隐形字符。CLEAN 能去掉大多数不可打印字符，TRIM 能去掉首尾多余空格和中间重复空格，两个函数搭配着用最保险。
前后空格单独说一下。=TRIM(A2) 是清理两端的标准工具，但它只处理 ASCII 空格，遇到不间断空格（字符码 160）就无能为力了。这种空格从网页复制时特别常见，用 =SUBSTITUTE(A2,CHAR(160),"") 才能清掉。我处理外部数据时经常写一串清洗公式，把 TRIM、CLEAN、SUBSTITUTE 套起来用。
前导零是另一个维度的麻烦。工号“007”存成数字就变成 7，前导零没了。这种情况要么把单元格格式预先设成文本再录入，要么用 =TEXT(A2,"000") 输出时补零。如果数据已经丢了零，只能按原长度规则重新拼，=TEXT(A2,"000000") 这种。
查找匹配错误
精确匹配和近似匹配的选择，是 VLOOKUP 报错和出错的头号原因。第四参数写 0 或 FALSE 才是精确匹配，忘了写默认就是近似匹配，结果会找出一堆看起来对实际不对的数据。近似匹配本身是为区间查找设计的，比如根据分数段给等级，用的时候数据源必须按查找列升序排列，否则结果完全不可预测。
大小写在 VLOOKUP 里默认不区分。“apple”和“Apple”会被当成同一个值匹配上。想要区分，得借助 EXACT 函数或者用 INDEX+MATCH 配合数组运算。日常业务里这个差异一般不构成问题，涉及编码、区分大小写的用户名时才会有影响。
重复值导致的问题很微妙。VLOOKUP 只会返回第一个匹配到的结果，后面重复的视而不见。想取最后一个匹配，得用 LOOKUP 的向量形式或者 XLOOKUP 的倒序搜索参数。如果本意是汇总所有匹配项，用查找函数本身就选错了工具，该换 SUMIFS 或者 FILTER。
返回列偏移是说第三个参数。=VLOOKUP(A2,B:F,4,0) 里的 4 指的是在 B 到 F 范围内的第四列，也就是 E 列，不是工作表的 E 列。插删列之后这个数字不会自动调整，很容易错位。改用 XLOOKUP 直接指定返回区域，或者用 MATCH 动态算出列号传给 VLOOKUP，都能规避这个问题。
调试与排错
公式求值是我用得最顺手的一个排查工具。选中出问题的单元格，在“公式”选项卡下点“公式求值”，Excel 会一步步把公式拆开，展示每部分算出来的中间结果。哪一段开始冒错误值，问题就出在那一段。嵌套五六层的公式靠这个功能可以一层一层剥开看，比盯着编辑栏发呆高效得多。
F9 键是临时预览局部结果的快捷键。在编辑栏里选中公式的某一段，按 F9 就能看到那段算出来什么。比如 =IF(ISNUMBER(FIND("-",A2)),...) 里选中 FIND("-",A2) 按 F9，如果返回数字说明找到了横杠，返回 #VALUE! 说明没找到。看完按 Esc 撤销，千万别按回车，否则那段结果就被硬写进公式里了。
追踪引用和追踪从属是可视化工具。选中单元格点“追踪引用单元格”，Excel 会用蓝色箭头画出这个公式用了哪些格子的数据。点“追踪从属单元格”，箭头反过来，指向所有依赖于当前格子的公式。排查一个大表里谁在引用谁，靠肉眼翻是完全不现实的，用这两个按钮几秒钟就能理清关系。前提是工作表别太复杂，箭头超过二十根就开始眼花。
错误检查这个功能我用的频率不高，但它在某些场景下很有用。它按规则扫描整张表，把可疑的公式列出来，类型包括公式不一致、数字被当文本、引用空单元格等。新版的 Excel 智能一些，会给出修改建议。监视窗口适合盯几个重点单元格，它们在别的工作表或者屏幕外时，把关键的几个值加到监视窗口里，实时看变化，不用来回切表。
拆公式是排查复杂问题的通用套路。把一个长公式复制出来，按运算符优先级一段一段拆到辅助列里算，算到哪一步结果不对，问题就在那里。这个方法笨，可几乎万能，尤其是面对别人写的、自己看不懂的公式时。
预防策略
数据验证是第一道关卡。在录入阶段就把格式卡死，后面报错的概率断崖式下降。日期列设成日期验证，数字列设成数字范围，下拉列表限制选项值。从源头上不让文本混进数字列，比事后拿函数去清洗省事太多倍。
规范数据源是长期主义的做法。把原始数据整理成一张表，一行一条记录，列名不重复不合并单元格，不要小计行掺在明细里。这种“一维表”结构是用公式的前提。看到有人把报表做成带合并单元格、有多层表头的花哨样子，我就知道接下来公式一定会写得非常痛苦。
模块化公式是说别一上来就写超长嵌套。用 LET 把中间结果定义成变量，用辅助列把复杂计算拆成几步，每一步都看得明白。出了问题时定位到具体哪一步，改起来也快。我见过有人一个公式套十几层 IF 加 VLOOKUP 加 TEXT，跑起来 CPU 风扇都响，出错了根本无从下手。
版本兼容检查是发给别人之前的最后一道工序。自己电脑上装的 365，用 XLOOKUP、TEXTSPLIT、动态数组写得飞起，对方可能是 2016 或者 WPS 某个老版本，打开全是 #NAME? 和 #VALUE!。发文件前用旧版本开一次试试，或者干脆把动态数组的结果选择性粘贴成数值再发。跨版本传文件时这一步不能省。
排错这件事心态比技巧更重要。看到红字别急着删掉重写，先想清楚它想告诉你什么。每个错误值背后都有明确的原因，顺着原因去查，通常几分钟就能解决。慌着乱改反而会把本来对的公式改坏。
我刚开始用Excel时，总觉得公式写得越长越厉害。一个单元格里塞进十几个函数嵌套，自我感觉良好。直到有次改一个销售报表，公式跑了三分钟还没算完，电脑风扇嗡嗡响，我才意识到把公式写复杂跟把公式写好完全是两码事。这一章想聊的几个进阶工具，都是让我从“能算出来”往“算得漂亮、算得快”过渡的关键。动态数组让一个公式输出一片区域，LET和LAMBDA把公式拆成可读的零件，条件格式和数据验证能跟公式联动起来，跨表引用和性能优化则决定了你的表格能不能扛住真实业务的量级。
数组公式与动态数组溢出机制
传统数组公式给我留下的印象是仪式感太重。写完公式不能直接回车，得按Ctrl+Shift+Enter，Excel自动在公式两边加上花括号。更麻烦的是，如果公式要返回多个结果，你得提前选中足够大的区域，输入同一个公式，再按三键。有次我选少了区域，结果只显示了第一个值，后面全被截断，查了半天才发现是区域选小了。这种写法的好处是兼容性好，Excel 2019甚至更早版本都支持，坏处是操作门槛高，改公式时还得整片区域一起改。
Office 365带来的动态数组完全改变了这个局面。现在写个 =UNIQUE(A2:A100) 直接回车，结果会自动往下溢出，相邻单元格不需要预先选中。更妙的是，溢出区域可以用 # 符号引用，比如 =COUNTA(B2#) 能直接统计B2溢出范围里的项目数。我去年做项目清单去重时，一个UNIQUE公式就替代了以前“高级筛选复制到新位置”的整套操作。
动态数组的溢出机制也带来新问题。假如溢出区域下方有数据，Excel会报 #SPILL! 错误，而不是覆盖掉原有内容。这个设计很合理，它逼着你把目标区域清空。有时候我明明觉得下面没东西，还是报错，仔细一看是某个单元格里藏了个空格。处理这类报错，先点一下错误提示，Excel会画出一个虚线框，告诉你哪个格子挡住了溢出。传统数组和动态数组还有一个差异体现在函数行为上。在老版本里，=VLOOKUP(A2,B:C,2,0) 只能查一个值，想批量查得配合数组公式。而现在直接把查找值写成一个区域，比如 =VLOOKUP(A2:A10,B:C,2,0)，结果会自动溢出成十个值。这个变化让很多以前需要辅助列的活儿变得极其简单。我在处理订单匹配时，经常用 =XLOOKUP(A2:A100,产品表!A:A,产品表!C:C) 一次性拉回整列数据，省掉了下拉填充的机械动作。
模块化公式：LET定义变量、LAMBDA自定义函数、递归思路
重复计算是复杂公式里最常见的浪费。以前写一个带条件判断的提成公式，同一个 SUMIFS 可能要在不同分支里出现三四次，每次修改条件都得把每个位置改一遍。LET函数的出现，就是让我把中间结果先存到变量里，后面直接引用变量名。比如 =LET(销售额,SUM(B2:B10),目标,10000,IF(销售额>目标,销售额*0.1,销售额*0.05))。销售额只算一次，后面两次引用都是直接取值。公式短了，读起来也像一段有变量声明的代码。
LAMBDA把我带进了另一个层次。它允许把一段公式封装成一个自定义函数，在名称管理器里注册一个名字，之后就能像用SUM一样用自己定义的函数。我做过一个计算阶梯电费的LAMBDA，传入用电量，返回电费。注册完之后，财务同事只要输入 =电费(A2) 就能得到结果，完全不用懂背后的公式。递归是LAMBDA的高级玩法。LAMBDA内部可以调用自己，前提是把函数名注册好。我试过用递归实现一个反向文本函数，虽然实际业务中用得不多，它帮我理解了循环在函数式写法里怎么落地。递归必须设好终止条件，不然Excel会一直算到栈溢出。
模块化思路不限于LET和LAMBDA。辅助列是最老牌的模块化手段，把复杂计算拆成几步，每一步放在单独的列里。有人觉得辅助列让表格变丑，我倒觉得它比一个两百字符的长公式好维护得多。现在我的做法是，能用LET拆清楚的就在公式内部拆，需要多行输出结果的才用辅助列。LAMBDA更适合那种在多个文件、多个工作表里反复用到的计算逻辑，一次定义，到处调用。
条件格式、数据验证与公式联动
条件格式和公式配合，能把静态表格变成会说话的仪表盘。我做过一个库存表，用条件格式公式 =$D2<$C2 来标记库存低于安全库存的行，整行变红。这里的关键是引用方式，$D2 锁列不锁行，公式应用到整个区域时，每一行都会拿自己那行的D列跟C列比较。设好之后，补货数据一录入，低于安全线的行立刻变色，不用我去数。这个功能比手动筛选快太多了。
数据验证的下拉联动是另一套组合拳。普通下拉列表用逗号分隔选项，但省份和城市这种二级联动就得靠公式。老办法是定义名称加INDIRECT，把每个省份的城市列表定义成一个名称，第一个下拉选省份，第二个下拉用 =INDIRECT(A2) 动态取对应列表。新版Excel里，用FILTER和UNIQUE更直接。比如城市列表在A列，省份在B列，第二个下拉的验证公式写 =FILTER(A:A,B:B=D2)，选哪个省就只显示哪个省的城市。动态数组溢出的结果能不能直接用于数据验证，取决于版本。我试过365里可以，但发给用2019的同事，文件打开后下拉就失效了。
条件格式和数据验证还能联动起来。有次做报销单模板，我想让“其他”类别的输入框自动出现说明文字。做法是用条件格式公式判断类别单元格是否等于“其他”，是的话把下方单元格的边框和背景设成提示色。数据验证再用自定义公式限制，类别选“其他”时，说明栏必须填写。两个功能配合，录入时的引导就完整了。公式在条件格式和数据验证里的写法跟在单元格里不太一样，它们返回的是逻辑值，Excel根据TRUE或FALSE来决定是否应用格式或允许输入。
跨工作表与工作簿引用
跨工作表引用本身不难，Sheet2!A1 这种写法谁都会。麻烦的是工作表名字里有空格或者特殊符号，得用单引号包起来，'销售 数据'!A1。我见过同事把工作表名改成“1月”“2月”，结果公式里写成 1月!A1，Excel以为1月是个函数或者名称，报 #NAME?。名字里带数字开头也会出问题，最好加个单引号保险。跨工作簿引用更脆弱，文件关掉之后，公式里的路径会变成完整路径，对方把文件挪个位置或者改个名字，链接就断了，满屏 #REF!。
INDIRECT函数让跨表引用变得动态。它把文本字符串解释成引用，比如 =INDIRECT("Sheet"&A2&"!B1")，A2里填1就取Sheet1的B1，填2就取Sheet2的B1。这个能力在做多月汇总时特别有用，我不用写十二个公式，一个INDIRECT配合ROW就能拉出全年数据。INDIRECT的代价是易失性，任何计算都会导致它重新求值，表一大就卡。而且它引用的工作表如果被重命名或删除，公式立刻报错，Excel没法追踪依赖关系。OFFSET和ADDRESS也常跟跨表引用搭配。OFFSET从一个起点偏移指定行列，返回一个引用。ADDRESS则根据行列号拼出地址文本，通常再套INDIRECT才能实际取值。我以前用OFFSET做动态图表的数据源，因为它能根据COUNTA算出行数，图表自动扩展。后来发现动态数组的溢出引用#更简单，就很少用OFFSET了。
三维引用是指 Sheet1:Sheet3!A1 这种写法，同时对多张表的同一位置求和。它要求这些表在物理顺序上连续，插入新表时容易打乱。我在做季度汇总时用过，插入新月份的表之后，公式范围没自动包含新表，结果漏算了一个月。
公式性能优化
整列引用是我早期表格变慢的头号原因。=SUMIF(A:A,"苹果",B:B) 看起来简洁，Excel实际会计算A列和B列所有一百多万行。数据量小的时候没感觉，一旦表里有了几万行，每次重算都像在拖车。后来我改用表格结构化引用，=SUMIF(销售表[产品],"苹果",销售表[金额])，Excel只计算表格范围内的行，速度快了一个量级。如果非要用区域引用，把范围限制在实际数据边界内，比如 A2:A50000，比整列引用好得多。
易失函数是性能的隐形杀手。NOW、TODAY、OFFSET、INDIRECT、RAND、RANDBETWEEN，这些函数的特点是不管依赖的单元格有没有变，每次工作表重算都会重新求值。一个表里如果散落着几十个TODAY，每次按F9或者改一个无关单元格，它们全都重算一遍。我有个预算表用了大量OFFSET做动态范围，打开要等十几秒。后来把OFFSET换成INDEX，INDEX不是易失函数，只有依赖的单元格变了才重算，打开速度回到正常。TODAY和NOW这种如果只是用来显示日期，可以考虑改成手动输入的日期，或者放在一个单元格里让其他地方引用。
嵌套层级过深也会拖慢计算。Excel对公式嵌套层数有限制，七层还是六十四层取决于版本，但真正的问题不是限制，是每次修改都像在拆炸弹。我见过一个同事写的公式，IF里面套VLOOKUP，VLOOKUP里面套IF，七八层叠在一起，算得慢不说，他自己都说不清逻辑。用LET把中间步骤定义成变量，把嵌套摊平成顺序执行，计算次数少了，可读性也上来了。辅助列在性能上通常比深层嵌套好，因为Excel可以单独缓存每列的结果。控制嵌套层级还有一个好处，调试的时候能一眼看出哪一步开始出错。
写公式跟写代码一样，能跑通只是及格线。动态数组让输出更灵活，LET和LAMBDA让结构更清晰，条件格式和数据验证把公式的触角伸到了交互层面，跨表引用和性能优化则决定了这套方案能不能在真实数据量下稳定工作。我现在的习惯是，写完一个复杂公式先问自己：能不能拆成几步？有没有重复计算？会不会被整列引用或者易失函数拖慢表格？这几个问题问下来，公式的质量通常能上一个台阶。
我入行第三年才真正体会到，公式学得再多，不落到具体业务里就是一堆花架子。前面几章聊的都是零件，这一章我想把这些零件组装起来，看看它们在真实工作里怎么用。每个月做报表的人、每天核对数据的人、被老板追着要动态看板的人，遇到的麻烦其实高度相似。我把自己踩过的坑和后来摸索出来的做法整理一下，尽量说得具体些。
数据汇总与报表自动化
多表合并是我最早遇到的自动化需求。公司有十几个门店，每个店每周发一个销售表过来，我要把十几张表汇总成一张总表。早期做法是复制粘贴，后来用SUM函数跨表逐个加，=Sheet1!B2+Sheet2!B2+...，表一多就写疯了。再后来发现如果所有分表结构完全一致，可以用SUM直接做三维引用，=SUM(Sheet1:Sheet12!B2)，一次求和十二张表的同一个位置。这个方法在分表数量固定、命名规范时特别好用。新版本里我更推荐用VSTACK把多张表竖向拼起来，再套GROUPBY或者数据透视表。VSTACK的写法是 =VSTACK(表1,表2,表3)，如果表是超级表对象，直接写表名就行。
动态看板的核心是让数据自己更新，人不用去动公式。我做过一个区域销售看板，数据源是一张持续追加的明细表。以前每次更新数据都要手动调整图表范围，烦得很。用动态数组之后，图表的数据源写成 =销售表，表格新增行时图表自动扩展。再配合FILTER做筛选，比如只看华东区的数据，公式写成 =FILTER(销售表,销售表[区域]="华东")，图表就跟着筛选结果走。下拉框切换区域，整个看板联动刷新。这套做法比用OFFSET定义动态名称简单多了，后者动不动就报引用错误。
月报季报的自动化我走过一段弯路。最开始想用一个超级公式搞定所有汇总，IF里面套SUMIFS，SUMIFS里面套INDIRECT，写完自己都不敢改。后来把月报拆成三个层次：明细层用表格结构化引用保持数据干净，汇总层用SUMIFS按月份和部门拉数，展示层用条件格式和图表做呈现。每个月只需要把新数据粘贴到明细表末尾，汇总层公式自动扩展。季度报就是在月报基础上用SUMIFS按季度汇总，条件写 ">="&DATE(2024,1,1) 和 "<="&DATE(2024,3,31)。这样处理的好处是每个环节都能单独验证，哪一步出错了一看便知。
数据清洗与核对
去重这件事，UNIQUE出来之前我用的是“数据”选项卡里的删除重复项，或者高级筛选。这些方法的问题是每次数据更新都要重新操作一遍。UNIQUE公式 =UNIQUE(A2:A1000) 把结果溢出到一片区域，源数据增加时结果自动更新。配合SORT还能去重后排序，=SORT(UNIQUE(A2:A1000))。如果要去重后统计每个值出现的次数，用 =GROUPBY(A2:A1000,A2:A1000,COUNTA)，GROUPBY是365里的新函数，直接输出分组和计数。我也用COUNTIF配合UNIQUE做过，=COUNTIF(A2:A1000,UNIQUE(A2:A1000))，效果一样，公式稍微绕一点。
拆分文本我用得最多的是TEXTSPLIT和TEXTBEFORE/TEXTAFTER。有次收到一份供应商名单，公司名和联系人挤在一个单元格里，中间用全角空格隔开。以前要用FIND定位空格位置，再套LEFT和RIGHT分别截取。TEXTSPLIT一个公式就搞定，=TEXTSPLIT(A2," ")，结果自动分成两列溢出。遇到地址拆分更麻烦，省市区混在一起没有统一分隔符。这种情况我一般先用TEXTSPLIT按“省”字拆，再对结果按“市”字拆，分步处理。拆完之后用TRIM清掉多余空格，用SUBSTITUTE把不可见字符替换掉。不可见字符最阴险，从网页复制来的数据里常带着CHAR(160)这种不换行空格，TRIM清不掉，得用 =SUBSTITUTE(A2,CHAR(160),"") 专门处理。
匹配和差异比对是我每月必做的功课。两份表核对差异，VLOOKUP和XLOOKUP都能做，XLOOKUP更顺手。=XLOOKUP(A2,表2!A:A,表2!B:B,"未找到")，第四个参数指定找不到时返回什么，比VLOOKUP的IFERROR包装简洁。找出两表差异的公式可以用FILTER配合COUNTIF，=FILTER(表1[姓名],COUNTIF(表2[姓名],表1[姓名])=0)，返回表1里有而表2里没有的名字。反过来再写一个，双向差异就齐了。差异比对时大小写和空格是常见的坑，我习惯在匹配前用TRIM和UPPER统一格式，减少假差异。
异常值标记我用条件格式配合公式。销售数据里超出均值三倍标准差的值算异常，公式写成 =ABS(B2-AVERAGE(B:B))>3*STDEV(B:B)。这个公式直接写在条件格式规则里，应用范围选B列数据区，符合条件的单元格自动变色。有人觉得STDEV计算量大会拖慢表格，我用STDEV.S或者干脆只对实际数据范围算，不用整列引用。异常值不一定是错误数据，有时候是真实的大单，标记出来让人工判断比自动剔除更稳妥。
常见业务案例
销售提成计算是IF和SUMIFS的经典组合。阶梯提成写法是 =IF(B2>100000,B2*0.1,IF(B2>50000,B2*0.07,B2*0.03))，新版Excel用IFS更直观，=IFS(B2>100000,B2*0.1,B2>50000,B2*0.07,TRUE,B2*0.03)。IFS按顺序判断，第一个为真的条件返回对应结果，最后的TRUE相当于兜底。如果提成规则是按超出部分分段计算，就得用SUMIFS配合辅助表，或者用SUMPRODUCT做数组运算。我见过更复杂的提成方案，不同产品线不同费率，那就得用SUMIFS按产品线分别算再汇总。提成公式最怕的是规则改了没人通知，所以我在表格里专门留一块区域写当前规则，公式引用那块区域而不是硬编码数字。
考勤统计的核心是时间计算和条件计数。上班时间在A列，下班时间在B列，工时就是 =(B2-A2)*24，乘以24把时间格式转成小时数。如果跨天，公式得改成 =(B2-A2+(B2<A2))*24，当B2小于A2时加一天。迟到早退的判断用TIME函数，=IF(A2>TIME(9,0,0),"迟到","")。统计每个人迟到次数用COUNTIFS，=COUNTIFS(姓名列,A2,迟到列,"迟到")。考勤表里日期格式经常出问题，从系统导出的日期可能是文本，用DATEVALUE转换一下再参与计算。还有一种坑是时间显示为“9:00”但实际是文本，A2>TIME(9,0,0) 这种比较会出错，得先用TIMEVALUE或者VALUE把它转成真正的时间序列值。
库存预警靠条件格式和IF组合。安全库存放在C列，当前库存放在D列，预警公式 =IF(D2<C2,"补货","充足") 写在E列。条件格式规则应用在D列，公式 =$D2<$C2，库存低于安全线的单元格变红。更精细一点的做法是用数据条，库存越低数据条越短，视觉上比单纯变色更直观。如果要做补货建议，可以用 =MAX(0,C2-D2) 算出需要补多少。库存表最大的麻烦是数据更新不及时，我做过一个自动提醒，用TODAY函数和最后更新日期做对比，超过三天没更新就标记黄色。TODAY是易失函数，表大了会卡，我用一个单元格专门放今天日期，其他地方引用那个单元格。
财务对账是公式密度最高的场景。银行流水和账面记录核对，匹配条件可能不止一个，金额、日期、摘要都要对上。SUMIFS在这里派上用场，=SUMIFS(银行流水[金额],银行流水[日期],A2,银行流水[摘要],B2)，用多条件求和来匹配。如果对应不上会返回0，再跟账面金额比较就能发现差异。对账最头疼的是摘要不完全一致，比如银行写“支付宝转入”，账面写“支付宝”，这种情况SUMIFS匹配不上。我的处理办法是加一列辅助列，用SEARCH查找关键词，把两边都归一化成通用描述再匹配。对账做完之后用条件格式把差异行标红，一目了然。
版本兼容与迁移
Excel 365和其他版本的公式差异，我是在给同事发文件时被教育过的。我在365里用了FILTER和XLOOKUP，自信满满地发出去，同事用2019打开，满屏 #NAME?。XLOOKUP在2019里不存在，FILTER也是365才有的动态数组函数。动态数组的溢出行为在老版本里完全没有，公式只能返回第一个值。后来我养成习惯，给别人发文件之前先问清楚对方用什么版本。如果对方版本低，要么把动态数组公式改成传统数组公式按三键输入，要么用INDEX+MATCH替代XLOOKUP，用IFERROR+SMALL+INDEX替代FILTER。
Excel 2021是个分水岭。2021支持XLOOKUP、FILTER、SORT、UNIQUE这些动态数组函数，但不支持LET和LAMBDA。我有个用了LET的提成公式，在2021里打开就报错，只能拆回重复计算的老写法。LAMBDA和TEXTSPLIT、GROUPBY、PIVOTBY这些是365独占的，版本更新比较快。Excel 2019及更早版本支持传统数组公式，但需要Ctrl+Shift+Enter，而且没有溢出机制。给低版本用户写公式时，我尽量只用INDEX、MATCH、SUMIFS、IF这些基础函数，虽然啰嗦但兼容性好。
WPS的公式兼容性这几年进步很大，常用的SUMIFS、VLOOKUP、INDEX+MATCH都没问题。但WPS对动态数组的支持分版本，老版本WPS没有溢出功能，UNIQUE和FILTER可能用不了。WPS里有些函数的参数顺序跟Excel不太一样，比如WPS的DATEDIF参数是反的。我遇到过最离谱的问题是WPS里 @ 符号的处理，在结构化引用里 [@[金额]] 这种写法在WPS里有时识别不了。跨软件迁移的稳妥做法是，把公式里的结构化引用改成普通区域引用，把动态数组公式改成辅助列加传统公式，功能会弱一点但至少不会报错。
版本迁移我总结了一个检查清单。第一，列出所有用到的函数，逐个确认目标版本是否支持。第二，检查是否有动态数组溢出，低版本需要改成三键数组或者辅助列。第三，检查结构化引用，必要时转成绝对引用区域。第四，检查LAMBDA注册的自定义函数，低版本完全没有这个功能。第五，检查条件格式和数据验证里的公式，这些地方的公式兼容性问题最隐蔽，经常是文件打开了但规则不生效。迁移完之后自己先用目标版本打开测试一遍，别等同事反馈才发现问题。
学习路线与自查清单
学Excel公式我觉得不用贪多，先把手头的工作用公式处理顺了，再往外扩。我的学习路径大致分三段。入门阶段就练五类函数：SUMIFS做条件求和，IF做判断，VLOOKUP或XLOOKUP做查找，LEFT/RIGHT/MID做文本截取，TODAY/DATEDIF做日期计算。这五类能覆盖日常八成的工作。练习方法很简单，找一张自己的实际工作表，把重复操作的地方用公式替代。比如每天手动加总的，换成SUMIFS。每周手动查找的，换成XLOOKUP。用公式解决一个真实问题，比看十个教程视频都管用。
排错阶段的核心是学会看错误值和用调试工具。错误值那章讲过的七种常见错误，至少要知道每种大概是什么原因。#N/A多半是查找值不存在，#VALUE!多半是数据类型不对，#REF!是引用被删了。调试工具里我用得最多的是公式求值，一步一步看公式怎么算的，哪一步开始出问题。F9键在编辑栏里选中公式的一部分按下去，能临时看到那部分的结果，看完按Esc退出，千万别按回车，按了就把公式改掉了。追踪引用和从属的箭头有时候画得满屏都是，但顺着箭头能找到源头。监视窗口适合盯着几个关键单元格，一边改数据一边看它们怎么变。
进阶阶段我建议围绕一个具体业务做深。有人在电商行业，就把销售分析做透，用动态数组做自动排名、同比环比、异常检测。有人在财务岗位，就把对账和报表自动化做透，用LET和LAMBDA把重复逻辑封装起来。我自己是在库存管理上花的时间最多，从简单的条件格式预警做到用FILTER和SORT做动态补货清单，再到用LET把安全库存的计算逻辑封装成一个公式。每深入一层都会遇到新问题，解决新问题的过程就是进步。
自查清单我列了十个问题，写完公式之后过一遍能避开大部分坑。公式引用的区域对不对，有没有整列引用拖慢速度？绝对引用和相对引用用对了吗，下拉填充时会不会跑偏？查找公式的匹配模式选对了吗，精确匹配还是近似匹配？数据类型一致吗，文本和数字有没有混在一起比较？有没有重复计算的部分可以提出来用LET定义？辅助列能不能让公式更简单？易失函数用多了吗，能不能换成非易失的替代方案？错误处理做了吗，IFERROR包装了吗？给别人用的文件，目标版本支持这些函数吗？公式写到这个程度，三个月后自己还看得懂吗？
这十个问题我刚开始觉得麻烦，问多了变成习惯，写公式的速度反而快了。因为大部分返工都发生在没想清楚的时候就动手写，写完才发现方向不对。先想后写，比写完再改省时间。
