让 AI 替你写 Excel 公式：告别背函数的六步实操法-知了AI

为什么 AI 写出来的 Excel 公式总是”一按回车就报错”

过去两年里，我帮不下两百个团队处理过同一类问题：同事兴冲冲地把需求贴给 AI，AI 三秒钟回一段花里胡哨的 Excel 公式，复制、粘贴、回车——屏幕上跳出 #NAME?、#VALUE!、#REF!，或者更阴险的：不报错，但算出来的数是错的。人们往往把这归结为”AI 不行”，于是放弃。这个判断错得离谱。AI 写 Excel 公式的能力，早就超过绝大多数只会 SUM 和 VLOOKUP 的办公室用户；真正的瓶颈在于提问者交出去的信息量太少，少到模型只能靠猜。而公式这个领域，恰恰是最经不起猜的。

本章先把”为什么会错”拆解清楚。只有搞明白了失败的结构性原因，后面六步法里的每一步才知道自己是在堵哪个窟窿。很多教程跳过这一层，直接甩一堆提示词模板，读者照着抄一遍能用，换个场景又崩，原因就在这里。

列引用靠猜：AI 根本不知道你的表长什么样

当你对一个语言模型说”帮我统计每个销售员的本月订单额”，它面对的信息是零。你的工作簿里可能有 A 列到 Q 列，也可能只有 B 列到 F 列；销售员可能在 C 列，也可能在 A 列。模型没有打开你的文件，它只能依据”常见表格长相”做一个概率最高的猜测。这个猜测在它训练语料里出现频率最高的那种表结构上大概率是对的，在你的表上则完全是掷骰子。

更麻烦的是，这种猜测会伪装成确定性。模型输出的 =SUMIF(C:C,A2,F:F) 看起来无比笃定，你根本看不出哪一部分是它真知道、哪一部分是它编的。这是 Excel 公式生成里最危险的特征：错误的公式和正确的公式长得一模一样。

区域边界不明：整列引用 vs 精确区间

假设你说”统计 A 列里的销售额”，AI 可能给出 SUM(A:A)，也可能给出 SUM(A2:A1000)。两者在简单场景都能跑通，但后果完全不同：整列引用会把表头文本、备注行、合计行全部吞进去（文本被忽略还好，但如果你在底部放了一个”小计”行，就会重复计算）；写死的区间则在你插入新行时彻底失效。这个选择本应由”数据的实际生命周期”决定，而模型对此一无所知。

绝对与相对引用搞混：填充时的隐形炸弹

公式在单个单元格里验证通过，往下填充 500 行后全错——这是最高频的投诉。根源几乎总是 $ 放错了位置。查找区域应该锁死（$A$2:$D$500），查找值应该跟着行走（A2），初学者和 AI 都容易搞反。关键在于：相对引用的问题在第一个单元格里永远看不出来，只有填充之后才会暴露，所以验证必须在”至少三行”上做，这一点后面第六步会详细展开。

数组公式与版本兼容：一个函数名背后的两条技术路线

Excel 在 2019 版和 Microsoft 365 之间发生了一次底层变化：动态数组。在这之前，要返回一列结果必须按 Ctrl+Shift+Enter 输入数组公式（公式两端会自动包上花括号），或者用 INDEX 配合 SMALL 这类”土办法”。在动态数组版本里，直接回车就会自动溢出。AI 训练语料里两种写法混杂，给你的公式可能带着旧时代的烙印，在同事的老电脑上一粘就废。

中文版函数名与翻译差异：XLOOKUP 不是到处都有

很多人以为中文版 Excel 里函数名是中文的，其实函数名保持英文，但参数分隔符、函数名显示、部分新增函数的中文译名都存在差异。更实质的问题是版本存量：XLOOKUP、FILTER、UNIQUE、LET、LAMBDA 只在 Microsoft 365 与 Excel 2021 及以上可用；WPS 个人版和大量企业还在用 Excel 2016/2019。你让 AI 写了一个漂亮的 XLOOKUP，发给财务同事，对方打开只看到 #NAME?。这不是 AI 的错，是你没告诉它”收件人用的是什么版本”。

总结：错误不是随机的，而是六个信息缺口

把上面这些症状归拢，会发现 AI 生成 Excel 公式失败的原因高度收敛，基本就是六个信息缺口的排列组合。这六个缺口，正好对应后面的六个步骤。

失败症状	根因	提问者缺失的信息	对应步骤
#NAME?	用了当前版本不存在的函数	Excel / WPS 版本号	第五步
#REF!	引用了被删列或错位区域	列字母与列名的对应关系	第一步
#VALUE!	文本参与了算术运算	每列的数据类型	第一步、第四步
结果偏大 / 重复计算	把合计行、表头卷进了区域	数据的真实起止行	第一步
填充后全错	相对 / 绝对引用错位	填充方向与目标区域	第三步、第六步
空值显示 0 或报错	没有边界处理	脏数据的分布情况	第四步

看清这张表，你就理解了本文的核心主张：生成可靠的 Excel 公式，本质是一场信息交付，而不是一次咒语许愿。你交出去的结构信息越完整，AI 的输出就越接近可直接落地。下面进入六步法。

第一步：先交”表格结构地图”，再谈公式

这是六步法里最容易被跳过、也最能决定成败的一步。绝大多数人提问时只写”我的表有订单号和金额”，这在 AI 看来约等于什么都没说。结构地图要做的事，是把”人眼一看就懂、模型完全看不到”的信息，用最紧凑的方式翻译成文本。

结构地图必须包含的五项信息

一份合格的结构地图，至少要交代清楚五件事，缺一项就多一个猜测点。

列字母：A、B、C 到底对应什么。这是防止 #REF! 和”区域错位”的第一道防线。
列名：用真实表头文字，而不是你脑子里的简称。表头叫”客户名称”，你就别写”客户”。
数据类型：文本、日期、数值、百分比、布尔。特别注意”看起来是数字其实是文本”的列，这是 #VALUE! 的头号来源。
数据范围：从第几行开始、到第几行结束、现在有多少行、以后会不会追加。
合并单元格：有还是没有，在哪些行。合并单元格是公式世界的地雷，涉及它的公式必须专门设计。

如何快速把表变成地图（不靠肉眼数）

手工写结构地图很痛苦，但其实有两个半自动的办法。第一个是直接复制表头行粘贴到对话框，AI 能从制表符推断出列序，但推不出字母，你补上字母即可。第二个更快：选中数据区，在名称框里输入一个区域名，Excel 会告诉你这块区域的确切地址（比如 A1:F238），再按列扫一眼数据类型的”长相”，通常三十秒就能写出一份地图。

这里有个反常识的技巧：不要只写”数据到第 238 行”，而要写”当前 238 行，每周追加约 40 行”。前者让 AI 倾向于写死区间，后者会促使它选择整列引用或结构化引用。这句话只多了十几个字，却直接改变了公式的架构。

给 AI 的结构地图模板（可直接复制）

下面这份模板我用了两年，几乎覆盖了所有日常结构。使用时把方括号里的内容替换掉，不需要的部分可以删。

【表格结构地图】
工作簿：销售管理.xlsx
工作表：订单明细（当前活动表）
数据区域：A1:H238（第 1 行为表头，数据从第 2 行开始）
未来增长：每周追加约 40 行，可能插入新列

列结构：
A 列｜订单编号｜文本（如 SO-20260112-001）｜无重复
B 列｜下单日期｜日期（yyyy-mm-dd）｜无空值
C 列｜客户名称｜文本｜存在空值（约 12 行）
D 列｜销售员｜文本（8 人固定名单）｜无空值
E 列｜产品类别｜文本（5 类）｜无空值
F 列｜数量｜数值（整数）｜无空值
G 列｜单价｜数值（2 位小数）｜无空值
H 列｜金额｜数值｜无空值，公式列（=F*G）

其他工作表：
「销售员」A1:B9：A 列姓名、B 列所属大区
「产品」A1:C6：A 列类别、B 列标准单价、C 列毛利率

特殊说明：
- 无合并单元格
- 第 238 行之后为空，底部无合计行
- 客户名称列有前后空格，需要 TRIM 处理

注意最后那行”有前后空格”。这类脏数据说明看似琐碎，价值却极高，因为它直接决定了是写 COUNTIFS 还是 SUMPRODUCT(TRIM(...))。你不说，AI 永远不会问。

结构化表格 vs 普通区域：地图写法要分两种

如果你的数据已经 Ctrl+T 转成了”表格”（即超级表），结构地图的写法要改：此时应该告诉 AI”数据位于名为订单明细的结构化表格中，列名为「客户名称」「金额」”，而不是给列字母。因为在这个场景下，写 SUMIF(订单明细[客户名称],...) 远比写 SUMIF(C2:C238,...) 稳健——前者会自动随数据增长扩展，后者不会。第一步就埋下这个差异，后面的公式质量会有代差。

一张清单：结构地图自检

交出去之前，用下面五个问题快速自检。

我能准确说出目标列是第几列吗？
这一列有没有空值、有没有”文本存的数字”？
数据以后会增长吗？增长方式是追加行还是追加列？
有没有合并单元格横跨了我要计算的范围？
底部有没有被人手动加了”合计”或”备注”行？

这五个问题答得出来，第一步就合格了。很多”AI 写的 Excel 公式不准”的抱怨，追到源头都是这一关没过。

第二步：用业务语言描述意图，别让 AI 猜你的函数

这是最反直觉的一步。大多数人的提问是”用 SUMIFS 写一个公式”，而不是”我要算出华东区 6 月的销售额总和，条件是订单状态为已发货”。前者看似专业，实际上把最关键的决策——该用什么函数——交给了你，而你可能选错了。

为什么”直接点名函数”反而是减分项

点名函数的问题有三个。第一，你可能选错了：本该用 SUMIFS 的场景你说了 VLOOKUP，AI 会硬着头皮满足你，给出一个勉强能跑但脆弱无比的写法。第二，你限制了优化空间：同样的”多条件求和”，AI 本来可以用 SUMPRODUCT 一行搞定，也可以用辅助列加透视，你一句”用 SUMIFS”就把路堵死了。第三，也是最致命的，函数名承载不了业务语义。你说 SUMIFS，AI 知道你要求和，但不知道”未发货的订单不算”这种业务规则。

所以正确的做法是把业务规则摊开：算的是哪个指标、筛选条件有哪些、口径是什么、结果放在哪。函数交给 AI 选，规则由你提供。

业务语言的四要素：对象、指标、条件、口径

一段好的业务描述，可以按”对象—指标—条件—口径”四段式来组织。

要素	要回答的问题	反面例子	正面例子
对象	在哪个范围里算	“这个表里”	“订单明细表，A1:H238 区域”
指标	算什么	“算一下金额”	“H 列金额的求和”
条件	筛什么	“华东的”	“D 列销售员属于华东大区，且 B 列日期在 2026-06-01 至 06-30 之间”
口径	怎么算才算对	“大概的”	“订单状态为已发货；退货单不计；空客户名称计为’散客'”

四要素里的”口径”最容易被忽略，却恰恰是中高级 Excel 公式的分水岭。举一个真实例子：某团队算”活跃客户数”，问 AI 要公式，AI 给了 COUNTA(客户名称列)，得到 3000。但业务口径是”本月有下单的独立客户”，正确答案是 842。公式没错，口径错了，这种错误永远不会报错，只会悄悄把报表送错地方。

避免三种”伪精确”的描述

描述意图时，有三种说法看似精确，实则制造混乱：

“除了某些行之外”：哪些行？必须列清楚，或者给出判定规则。
“按上次那个逻辑”：AI 没有记忆，即使在同一段对话里，上次的逻辑也可能被截断。
“大概就是那种区间筛选”：模糊描述会让 AI 在含 / 不含边界之间随机选择。

把需求写成”一段给同事看的话”

检验描述是否合格，有个土办法：把你写的需求原样发给一个不熟悉这张表的同事，对方能否照着在 Excel 里手工算出来。如果他能，AI 也能；如果他看完还要追问三遍，AI 同样只能猜。

示例（合格）：
“在订单明细表中，统计 D 列销售员 = ‘李伟’、B 列日期属于 2026 年 6 月、且 H 列金额大于 0 的记录，对 H 列求和。客户名称为空的行也要计入（散客订单）。结果应为 286,450.00。”

看到没，最后那句”结果应为 286,450.00″是我预先手算出来的。它不是必须的，但如果你手头刚好有个可以手工抽样验证的总数，加进去会显著提升准确率——AI 看到目标值，会主动反推条件是否漏掉。

第三步：要求”公式 + 逐段解释 + 预期结果示例”三件套

很多人止步于”给我公式”。拿到一串 =SUMIFS(...) 就直接粘。这一步的价值在于：把公式从”黑箱”变成”可审阅的对象”。当 AI 被要求解释每一段在干什么，它自己也会更谨慎，因为解释和公式必须自洽。

为什么要强制 AI 逐段解释

逐段解释有三个实际作用。第一，让它暴露自己的假设。当 AI 写下”这里假设 H 列不含空值”，你就抓住了一个可验证的点。第二，便于你快速定位错误：公式跑错了，先看哪一段的解释和你的理解不符，往往一次就找到病根。第三，能拦下一种隐蔽错误——AI 用了一个你没见过的函数或语法，解释会迫使它说明白，你能立刻判断”这玩意我的版本不支持”。

要求的输出结构：三段式

我在提示词里固定要求 AI 按三段输出，格式稳定，便于复用。

请按以下三段输出，不要省略任何一段：

【1. 公式】
直接给出可粘贴到目标单元格的完整公式（含等号），
并注明放在哪个单元格、向下填充到哪一行。

【2. 逐段解释】
把公式按参数和括号层级拆开，逐段说明：
- 每一段引用了哪一列、什么范围
- 为什么用绝对引用 / 相对引用
- 这一段算出来的中间结果是什么（用样本数据举例）

【3. 预期结果示例】
用下面提供的 3 行样本数据，手工推演公式的计算过程，
并给出这 3 行应该出现的准确结果。如果结果不对，明确说明原因。

注意第三段里的”手工推演”。这是整份提示词里价值最高的一句。它迫使 AI 真正”跑一遍”逻辑，而不只是拼装函数名。实测下来，加上这一句，公式正确率有明显提升，尤其在中嵌套深度的场景。

如何判断解释是否”真的懂”

AI 的解释也可以很敷衍，比如”这里对区域求和，返回满足条件的总和”。判断标准很简单：好的解释一定有具体的数字和具体的单元格。如果解释里出现”SUMIFS(H2:H238, ...) 中，H2:H238 代表金额列，条件区域 D2:D238 匹配 D5 的’李伟’，其余条件叠加后，H2:H238 中满足的只有第 14、37、52 行，合计 12,300″，这就是真懂；如果只有”对金额列求和”，就是没懂。

识别”看似合理”的错误解释

有些解释错得特别漂亮。例如 AI 说”COUNTIF(B:B, ">="&DATE(2026,6,1)) 统计 6 月 1 日之后的订单”，听起来没问题，但如果你要的是”6 月”，应该同时有上限 <=DATE(2026,6,30)。这种错误公式和解释是一致的，只有你能从业务语义上发现边界不对。所以逐段解释只是辅助，最终责任在提问者。

让 AI 标注”高置信度”与”需要你确认”的部分

有个进阶技巧：在提示词末尾追加一句”请标注出哪些部分依赖了我的假设，哪些部分是确定无疑的”。这样你会得到一份带注释的公式，比如”（确定） H2:H238 是金额列；（假设） D 列的销售员名称为规范写法，不含前后空格”。假设清单就是你的验证清单雏形。

一个完整示例：从请求到三段式输出

假设我们要算”李伟在 6 月的销售额”。按第三步要求的输出大致是这样：

【1. 公式】
放在 I2 单元格：=SUMIFS(H2:H238, D2:D238, "李伟", B2:B238, ">="&DATE(2026,6,1), B2:B238, "<="&DATE(2026,6,30))

【2. 逐段解释】
SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2, ...)
- H2:H238：金额列，绝对定位到数据末行 238，避免整列引用把底部合计行算进来（若确定无合计行，可改为 H:H）
- D2:D238 与 "李伟"：销售员精确匹配，文本条件无需引号转义
- B2:B238 与 ">="&DATE(...)：日期区间下界，用 DATE 构造避免"文本日期"比较失效
- B2:B238 与 "<="&DATE(...)：上界，必须加上，否则会统计到 7 月

【3. 预期结果示例】（样本 3 行）
第 5 行：李伟 / 2026-06-03 / 4,500 → 计入，累计 4,500
第 14 行：李伟 / 2026-06-28 / 7,800 → 计入，累计 12,300
第 37 行：李伟 / 2026-07-02 / 3,200 → 不计入（月份不符）
预期公式结果：12,300

这份输出你几乎不需要再看 Excel 报错就能判断对错。这就是三段式的全部意义：把验证成本从”跑一遍看结果”降到”读一遍看逻辑”。

第四步：逼 AI 交出”边界情况处理”方案

公式在干净数据上跑通，只能说明它会写字；在脏数据上不崩，才说明它真的能用。真实工作簿的数据质量远比模型默认假设的要糟。第四步就是把”脏数据”这件事摆到台面上。

空值：最容易被忽略的隐形错误

空值有三种表现，后果各不相同。真空单元格在算术中被当 0，在 COUNTA 中不被计数；空字符串（公式返回 ""）在 COUNTA 中被计数，在比较中也不等于空单元格；只含空格的单元格既不是真空也不是空串，ISBLANK 返回 FALSE，会让 COUNTIF 出现意外。

所以要求 AI 处理空值时，要指定口径：”客户名称为空的行，算作’散客’还是排除？”如果算作散客，公式里就有一个 IF(C2="", "散客", C2) 的分支，或者用 SUMIFS(H:H, C:C, "") 单独统计。这些细节不写清楚，AI 给的公式结果永远对不上数。

错误值：把 #N/A 当作正常值来对待

查找类公式的最大敌人是 #N/A。VLOOKUP 时代的标准解法是 IFERROR，但这个词有个陷阱：它会把所有错误（包括除零、类型错误）都吞掉，隐藏真实问题。更好的做法是只捕获预期错误，比如 IFNA 只处理 N/A，其余错误照常报出。我会在提示词里明确”请用 IFNA 而不是 IFERROR，除非你说明理由”。

重复值：求和、计数、查找三种口径完全不同

同一列有重复，不同业务对它的处理截然不同。求和通常要全算（每个订单都是一笔）；计数通常要去重（算客户数而不是订单数）；查找则有歧义——找到第一个还是最后一个？XLOOKUP 支持 -1 参数取最后匹配，VLOOKUP 做不到。提示词里必须写明”重复时取最后一次”或”重复视为异常值应排除”，否则 AI 会默认取第一条，往往不是你要的。

文本数字：一个看似无害的陷阱

“00123” 和 123 在你的眼里是同一个，在 Excel 眼里完全不同。文本数字不参与 SUM、不参与 > 比较（会导致 “9 > 10” 成立的笑话），但能参与 COUNTIF（COUNTIF 会自动把文本数字当数字看，这一点反而害人）。处理方式是要求 AI 在公式里显式做类型转换，或者直接说明”该列需要先转数值再计算”。

边界处理的提示词片段

把下面这段粘贴到任何一次公式请求的末尾，会让输出质量提升一档：

【边界情况要求】
请在给出公式后，单独列出"边界处理"一节，逐条说明：
1. 空值：目标列有空值时，公式行为是什么？是否需要用 IF/IFS 兜底？显示为空还是 0？
2. 错误值：查找失败 / 除零 / 类型错误分别如何处理？优先用 IFNA 而不是 IFERROR。
3. 重复值：遇到重复项时取第一条、最后一条，还是聚合？请说明依据。
4. 文本数字：如果关键列是文本格式的数字，公式是否需要 -- / VALUE 转换？
5. 日期：是否已确认为真日期而非文本？区间是否包含端点？
对于无法确定的边界，请明确写出"需要你确认：……"，不要自行假设。

最后一句”需要你确认”非常关键。它把”默默猜测”变成”显式追问”，你拿到输出时能一眼看出哪里还没对齐业务口径。很多人收到 AI 的公式就急着粘贴，反过来怪 AI 不准；其实 AI 早就标出了”这里我假设了 XYZ”，只是你没看。

一个真实的边界处理对照

场景	不加处理的写法	加了边界处理	差异
查找客户大区	`=VLOOKUP(A2,销售员!A:B,2,0)`	`=IFNA(VLOOKUP(TRIM(A2),销售员!$A:$B,2,0),"未分配")`	不会把 #N/A 带进报表；空白姓名被 TRIM 后仍能匹配
计算客单价	`=H2/F2`	`=IFERROR(H2/IF(F2=0,"",F2),"")`	数量为 0 时不显示 #DIV/0!
统计客户数	`=COUNTA(C2:C238)`	`=SUMPRODUCT((C2:C238<>"")/COUNTIF(C2:C238,C2:C238&""))`	去重；真空不计数
日期区间求和	`=SUMIF(B:B,">=2026-06-01",H:H)`	`=SUMIFS(H:H,B:B,">="&DATE(2026,6,1),B:B,"<="&DATE(2026,6,30))`	文本日期也能比较；有明确上界

表格里第四行那个写法值得多说一句：">=2026-06-01" 这种直接拼字符串的写法，在日期列为”存成文本的日期”时会静默失效，结果是 0，不报错。这类”静默错误”比报错更可怕，因为它不会提醒你。

第五步：同时要”兼容版”和”新版”两套写法

这一步解决了大多数团队协作场景里的隐形冲突：你自己是 Microsoft 365，拿着 XLOOKUP 爽得飞起；发到公司群里，财务那边的 Excel 2016 直接 #NAME?。更糟的是，对方不会来问你”这个函数我这里没有”，而是默默地把整列删掉，自己重新算一遍。于是你精心设计的 Excel 公式，最终没能被任何人用上。

版本分界线到底在哪里

Excel 的函数可用性大致分三代，记住这条线就能判断哪些写法”只能自己用”。

代际	代表函数	最低版本	能否向下兼容
经典函数	SUM / SUMIFS / VLOOKUP / INDEX / MATCH / SUMPRODUCT	Excel 2007 及以前	全部版本通用
微软 365 新函数	XLOOKUP / XMATCH / FILTER / SORT / UNIQUE / SEQUENCE	Excel 2021 / Microsoft 365	否，2019 及以前报 #NAME?
函数式编程层	LET / LAMBDA / BYROW / MAP / REDUCE	Microsoft 365（持续推送）	否，且 WPS 支持度不一

注意第二行和第三行的差别：XLOOKUP 在 Excel 2021 就有，但 LET 和 LAMBDA 要 Microsoft 365 订阅且经常是分批推送。所以”兼容版”不能只问”2016 行不行”，要具体到收件人的实际环境。

兼容写法的等价替换表

让 AI 同时给出两套写法，最实用的是让它提供”新版 → 兼容版”的等价替换，并说明降级后的功能损失。

新版写法	兼容写法（2016/2019 可用）	降级损失
`=XLOOKUP(A2,D:D,H:H,"")`	`=IFNA(INDEX(H:H,MATCH(A2,D:D,0)),"")`	功能等价，写法更绕；无法用通配符
`=UNIQUE(C2:C238)`	`=INDEX($C$2:$C$238,MATCH(0,COUNTIF($J$1:J1,$C$2:$C$238),0))` 配合 Ctrl+Shift+Enter	需要预留溢出区域；易出现 #N/A 占位
`=FILTER(C2:C238,D2:D238="李伟")`	`=IFERROR(INDEX(C:C,SMALL(IF($D$2:$D$238="李伟",ROW($D$2:$D$238)),ROW(A1))),"")` 数组输入	需下拉；大数据量极慢
`=LET(x,H2:H238,x*1.13)`	把中间结果写入辅助列，公式引用辅助列	多占一列；公式可读性下降
`=SUM(FILTER(H:H,B:B>=TODAY()))`	`=SUMIFS(H:H,B:B,">="&TODAY())`	几乎无损失，这种场景本就不必用 FILTER

表格最后一行藏着一个重要判断：不是所有新函数都值得用。上面那个”近 30 天销售额”用 SUMIFS 明显更简单、兼容性更好、性能更高。新函数的价值在于”之前做不到的事”，而不是”之前能做的事换个写法”。FILTER 真正不可替代的是”返回不固定长度的多列结果”，SUMIFS 能做到的事，用它纯属炫技。

怎么问，才能一次拿到两套

提示词里加一句就够，但要把损失讲清楚：

“请给两套写法：（A）新版，使用 XLOOKUP/FILTER/LET 等 Microsoft 365 函数，追求简洁可读；（B）兼容版，只能使用 Excel 2016 及以前就有的函数，按 Ctrl+Shift+Enter 数组公式也可以，但要注明输入方式。并逐条说明 B 版相比 A 版在哪方面有损失（性能、可读性、功能缺失）。”

如果目标用户明确，也可以直接要求”只给兼容版”。很多企业内网的老电脑，兼容版才是唯一能落地的答案。

WPS 用户的额外注意事项

WPS 表格近两年进步很快，XLOOKUP、FILTER、UNIQUE 都有支持，但细节差异不少：部分动态数组的溢出行为与 Excel 不完全一致；LAMBDA 支持较晚；部分函数的参数分隔符在中文环境下可能显示为逗号，但你输入时用逗号也可能被自动转换。最稳妥的做法是——如果你的输出要发给 WPS 用户，直接要求”兼容版”，不要赌对方的 WPS 版本够新。

第六步：三行样本上验证，再整体填充

前五步都是为了这一步服务。但如果你按常规做法——拿到公式，选中整列，双击填充柄——那么无论前五步做得多好，只要有一处引用没对齐，你的错误就会被放大 238 倍。第六步的核心思想就是：把验证范围从”全表”缩小到”三行”，把错误暴露在扩散之前。

为什么是”三行”而不是一行

一行只能验证”公式写对了吗”；三行才能验证”填充对吗”。三行里应该包含：

一行典型数据：所有条件都满足，结果是最常见的值。
一行边界数据：刚好卡在条件边界上（比如日期正好是 6 月 30 日）。
一行异常数据：空值、错误值或类型不符，用来检验兜底是否生效。

三行的选择本身就是一次业务校正。能挑出这三行，说明你对数据分布有认识；挑不出来，说明你对表格还不够熟，此时任何公式都不该信。

手工推演 vs 工具验证

验证方式有两种。第一种是手工推演：把每行的中间结果写在旁边，对照 AI 的解释逐段核对。第二种是工具验证：用 F9 键选中公式中的一段，Excel 会显示该段的计算结果，这比大脑推演可靠得多，尤其是嵌套超过两层时。

用 F9 逐段检查

在编辑栏里选中 SUMIFS 的条件区域，按 F9，会显示这个区域被解析成的数组。如果是 {FALSE;FALSE;TRUE;...} 这样的一串布尔值，说明条件在正常执行；如果显示 #VALUE!，说明条件本身就有问题。这个技巧在排查”结果不对但不报错”时几乎是核弹级工具，值得单独练熟。

用错误检查做交叉验证

还有一种笨但有效的方法：用完全不同的实现路径算一遍同一个数。比如 SUMIFS 算出来的结果，再用透视表拖一遍，或者手选几行看合计。两个独立方法得到同一个数，可信度远高于一个方法得到正确的数。我管这个叫”双保险原则”，尤其在汇报关键数字前必做。

验证通过后的填充策略

填充不是简单的下拉。三种情况要区别对待：

整列填充：如果公式用了 IF(B2="","",...) 之类的兜底，可以放心下拉到底；否则会得到一堆看起来正常的 0。
限定区域填充：选中三行验证区加后续区域，一次性粘贴，能保证引用相对关系一致。
结构化表格填充：超级表会自动把公式扩展为”计算列”，唯一的例外是你在表外手动粘贴，可能导致一列里出现两种公式，必须用”显示公式”模式扫一遍。

验证清单（可直接复用）

下面这份清单我打印出来贴在显示器旁边，每次交付前过一遍。

【Excel 公式交付前验证清单】
□ 1. 目标单元格：公式放在哪一格？填充范围是多少？
□ 2. 三行样本：典型行 / 边界行 / 异常行，结果是否都用 F9 核实过？
□ 3. 引用锁定：查找区域是否用了 $ 绝对引用？填充后首尾两行抽查过吗？
□ 4. 空值兜底：空行、空单元格的显示结果是否符合业务预期？
□ 5. 错误值：查找失败时显示 #N/A 还是空？有没有用 IFERROR 吞掉不该吞的错？
□ 6. 边界条件：日期区间是否包含两端？"大于"还是"大于等于"核对过吗？
□ 7. 数据增长：表尾新增 10 行，公式是否自动覆盖？
□ 8. 版本兼容：收件人的 Excel 版本能否支持所用函数？
□ 9. 口径核对：结果与人工抽样 / 透视表交叉验证一致吗？
□ 10. 可读性：三个月后的你，还能看懂这个公式吗？

第十条常被跳过，却是最能省时间的一条。公式的代价从来不在写的时候，而在半年后改的时候。

高频场景公式模板：能直接改的骨架

有了六步法作为方法论，模板就是效率催化剂。下面七个场景覆盖了日常 80% 的 Excel 公式需求。每个都按”业务意图 → 新版写法 → 兼容写法”的格式给出，你可以把它们当作第六步”三行验证”的练习素材。

场景一：条件求和（单条件与多条件）

条件求和是 Excel 公式的入门场景，但也是错误率最高的——因为大家习惯写 SUMIF 而忘了 SUMIFS 参数顺序是反的。

单条件：=SUMIF(D:D,"李伟",H:H)（SUMIF 格式：条件区域、条件、求和区域）
多条件：=SUMIFS(H:H,D:D,"李伟",B:B,">="&DATE(2026,6,1),B:B,"<="&DATE(2026,6,30))（SUMIFS 格式：求和区域在前！）
通配符：=SUMIFS(H:H,C:C,"*科技*")，匹配客户名称含”科技”的所有记录

那个”参数顺序反了”的坑，我见过太多次。SUMIF 的条件区域在前，SUMIFS 的求和区域在前。凡是 AI 给出的公式，第一件事就是扫一眼参数顺序对不对。

场景二：多条件查找（往左、往右、模糊）

标准写法：XLOOKUP 嵌套条件

=XLOOKUP(1,(D:D="李伟")*(B:B=DATE(2026,6,3)),H:H,"未找到")。这里把两个条件相乘，得到 1 或 0 的数组，只有同时满足的位置才是 1，再查找 1 即可命中。这是多条件 XLOOKUP 的标准范式，比 (D:D&"|"&B:B)=(D2&"|"&B2) 拼接法快得多。

兼容写法：INDEX + MATCH 双条件

{=INDEX(H:H,MATCH(1,(D:D=D2)*(B:B=B2),0))}，花括号代表数组公式，需 Ctrl+Shift+Enter 输入。老版本里没有 IFNA 的可以用 IF(ISNA(...),0,...) 包一层。

查找方向：从右往左怎么办

VLOOKUP 只能往右找，这是它最大的设计缺陷。老版本的解法是 INDEX+MATCH 或 CHOOSE 构造虚拟列；新版本直接用 XLOOKUP。永远不要再为了”往左查找”而复制一列辅助列，那是十年前的招式。

场景三：去重计数（客户数、SKU 数、独立用户数）

去重计数是 Excel 公式里”没有标准答案”的典型。旧版本只有一条路：=SUMPRODUCT(1/COUNTIF(C2:C238,C2:C238))，但它有个致命缺陷——一旦区域里有空单元格，就会 #DIV/0!。所以标准写法必须过滤空值：=SUMPRODUCT((C2:C238<>"")/COUNTIF(C2:C238,C2:C238&""))。

新版本就简单多了：=COUNTA(UNIQUE(FILTER(C2:C238,C2:C238<>"")))。先 FILTER 去空，再 UNIQUE 去重，再算个数。三步逻辑清晰，而且不依赖辅助区域。

场景四：文本拆分（姓名拆姓与名、地址拆省市）

文本拆分的难度取决于分隔符是否规整。

单一分隔符：=TEXTSPLIT(A2,"-") 直接溢出成多列（新版本）。兼容版用 =TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",99)),1,99)) 之类的中规中矩写法，或者数据分列工具。
取第 N 段：=INDEX(TEXTSPLIT(A2,"-"),,2)，取第二段。兼容版：=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",99)),(N-1)*99+1,99))。
没有分隔符：中文姓名拆姓与名，只能靠 LEFT(A2,1) 和 RIGHT(A2,LEN(A2)-1)，且无法处理复姓。这种情况老实说该用 Power Query 或者人工，别硬凑公式。

场景五：日期差（工龄、账龄、逾期天数）

日期差有三个容易搞混的函数，含义完全不同：

函数	含义	常用公式	注意
直接相减	日历天数差	`=B2-A2`	结果需要设为”常规”或”数值”格式
DATEDIF	整年/整月/整日	`=DATEDIF(A2,B2,"y")`	隐藏函数，无提示；起始日期大于结束日期会 #NUM!
NETWORKDAYS	工作日天数	`=NETWORKDAYS(A2,B2,节假日表)`	含头含尾，与直觉差一天

尤其是账龄计算，业务上常见的口径是”以月末为截止”，那么公式里就不该用 TODAY()，而要用 EOMONTH(TODAY(),-1) 或指定的截止日。很多”日期差算不对”的投诉，根源都是口径没定，而非函数用错。

场景六：跨表引用（带工作表名与动态表名）

跨表引用看着简单，坑却不少。工作表名含空格或特殊字符时必须加单引号：='销售明细 2026'!H2。写死表名的隐患在于，如果每月新建一张表，12 月份就有 12 个公式要手动改。

动态表名的写法是 =INDIRECT("'"&$A$1&"'!H2")，A1 里放月份表名。但 INDIRECT 有两个硬伤：一是 Excel 无法追踪依赖，表名改了不会自动更新；二是它是”易失性函数”，每次重算都重新计算，大数据量下会显著变慢。我的建议是能不用 INDIRECT 就不用，跨表汇总优先用 Power Query 的”合并查询”或者三维引用 =SUM(1月:12月!H2)（要求表结构完全一致）。

场景七：动态区域（随数据增长自动扩展）

动态区域的经典解法是 OFFSET + COUNTA，但 OFFSET 同样易失，大数据下是性能杀手。更现代的方案有三种：

整列引用：=SUMIF(D:D,"李伟",H:H)，最简单，但要求表内无汇总行。
超级表结构化引用：=SUMIF(订单明细[销售员],"李伟",订单明细[金额])，会自动扩展，性能优于整列引用。
动态数组 + FILTER：=SUM(FILTER(H:H,D:D="李伟"))，语法最优雅，但仅新版本可用，且 H:H 整列会拖慢。

如果数据量超过 10 万行，请毫不犹豫地把区域限定为 H2:H100000 这样的具体范围，或者转用 Power Query。整列引用在 10 万行以内还可以接受，再多就明显卡顿。

动态数组与溢出：哪些函数值得用，哪些是炫技

动态数组是近十年 Excel 公式体系最重要的变化，但也是最被滥用的功能。这一节想澄清一件事：动态数组改变的不是”能不能算”，而是”算完放哪儿”。理解这个区别，你就知道什么时候该用、什么时候不该用。

溢出机制到底是怎么运作的

在动态数组版本里，一个公式返回多个值时会自动”溢出”到相邻单元格。比如 =UNIQUE(C2:C238) 放在 J2，会自动占据 J2 到 J(2+去重客户数)。如果这些单元格里有数据，就会报 #SPILL!。这个错误常常让人抓狂，解法只有一个：清空溢出区域，或者用 @ 前缀强制返回单值。

FILTER：不是所有”筛选”都该用它

FILTER 的不可替代之处在于返回的行数由数据决定。要做”按销售员筛出全部订单明细”，这是唯一优雅的解。但如果你只是”按条件求和”，用 FILTER 反而绕远路。判断标准很简单：结果是一张表，用 FILTER；结果是一个数，用 SUMIFS。

UNIQUE / SORT：一对好搭档

这两个函数的组合拳几乎能复刻大多数透视表操作：=SORT(UNIQUE(FILTER(C2:C238,D2:D238="华东"))) 就是”华东区有哪些独立客户，排序输出”。这类需求过去要开辅助列再手动去重，现在一个公式搞定。

但要注意：UNIQUE 会把空值也算作一个独立值（返回 0）。所以务必加 FILTER 过滤，或者用 UNIQUE(FILTER(...)) 外套。

XLOOKUP 的真实优势不只是”替代 VLOOKUP”

很多人把 XLOOKUP 当作”VLOOKUP 的新版”，其实它的核心优势有四个：

能往左查；
支持从后往前找（-1 参数）；
可以一次返回多列（查找区域多选即可）；
默认精确匹配，不用再写那个 0。

第三条被严重低估。给 =XLOOKUP(A2,$A$2:$A$500,$C$2:$E$500) 一个公式，就能返回三列的匹配结果，一次写完，溢出填充。这在做”按订单号带出客户、金额、日期”这种场景时，效率是 VLOOKUP 的 N 倍。

LET：把”中间计算”搬进公式

LET 的价值在于消灭重复计算。比如你要算”毛利率超过 30% 的订单数”，传统写法里 (H2:H238-F2:F238*VLOOKUP(...)) 这种长表达式要写两遍，一处改漏就出错。用 LET：

=LET(
  qty, F2:F238,
  price, G2:G238,
  cost, VLOOKUP(E2:E238, 产品!$A:$B, 2, 0),
  margin, (price - cost) / price,
  COUNTIF(margin, ">0.3")
)

逻辑一目了然，而且只计算一次 margin。这是 LET 最实在的用法——不是为了短，而是为了可维护。如果你的公式超过 200 个字符，就该考虑用 LET 重构。

LAMBDA：什么情况下真的值得用

LAMBDA 允许你把常用公式做成自定义函数，比如 =LAMBDA(x,y,x*y) 定义成一个”乘法函数”。听起来很酷，但我的建议是：除非你所在的团队有稳定的 Excel 能力，否则慎用 LAMBDA。

原因有三：一，LAMBDA 定义的名字存在名称管理器里，发给别人时可能丢失；二，它不在公式里，别人看完公式也不知道调用的逻辑是什么；三，调试极其困难。我的经验是，只有当同一个复杂公式要在工作簿里重复用 20 次以上，且逻辑完全一致时，LAMBDA 才划算。

边界判断表：什么时候该用动态数组，什么时候用老办法

需求	推荐方案	不推荐方案	理由
按条件求和 / 计数	SUMIFS / COUNTIFS	SUM(FILTER(…))	老函数更快、更兼容、出错信息更明确
返回完整的明细列表	FILTER	INDEX+SMALL 数组	FILTER 在数据量大时快一个数量级
获取去重清单	UNIQUE	复杂 MATCH+COUNTIF	可读性差距几个数量级
按序号取值	INDEX	XLOOKUP	INDEX 不需要查找值，更直接
多次重复计算同一个中间结果	LET	重复书写 / 辅助列	可读性显著提升
团队内统一的复杂业务规则	LAMBDA + 名称管理器	到处复制公式	前提：团队有能力维护
多表数据合并 / 清洗	Power Query	任何公式	公式在这里是错误工具，详见下一节

这张表最有价值的是第一行。每当有人兴奋地跟你说”用 FILTER 重写一下求和公式”，你可以回答：”谢谢，SUMIFS 挺好。”这句话背后的判断，就是动态数组的适用边界。

公式维护：让三个月后的你还看得懂自己的表

写公式的人少，维护公式的人多。绝大多数 Excel 灾难，不是在公式写出来的那一刻发生的，而是在它们被接手、被复制、被改动的第 N 个月发生的。这一节讲的三种手段，核心目标只有一个：把公式的知识从人脑搬到表里。

命名区域：让公式说人话

把 H2:H238 命名为 金额，公式就从 =SUMIFS(H2:H238,...) 变成了 =SUMIFS(金额,...)。这个改动看起来微小，但当公式嵌套三层、引用五个区域时，可读性差异是指数级的。

更进一步，用”名称管理器”（公式 → 名称管理器）可以定义动态名称，例如让”金额”始终等于”H 列从第 2 行到最后一个非空行”：=OFFSET(订单明细!$H$2,0,0,COUNTA(订单明细!$H:$H)-1,1)。不过 OFFSET 是易失函数，数据量大时性能堪忧。现代做法是直接用超级表的结构化引用替代动态名称。

结构化引用：超级表的隐藏红利

把区域转成表格（Ctrl+T）后，公式自动变成 =SUMIF(订单明细[销售员],"李伟",订单明细[金额])。这里面有三个好处：

自动扩展：新增行后公式自动覆盖，不用改区域；
自解释：列名直接写进公式，任何人一眼看懂；
动态性能：Excel 只需要处理表格范围内的行，比整列引用快得多。

辅助列：最朴素也最被低估的方法

很多教程教人”用一个大公式搞定”，我持相反意见：该用辅助列的时候就用，不要追求”一列公式”。一条 300 字符的复杂公式，拆成三列各 100 字符，可读性、调试难度、复用性全部提升。在实战中，辅助列的维护成本远低于复杂公式的”优化”成本。

什么时候该拆成辅助列

公式里出现超过 2 个嵌套的 IF；
同一个子表达式出现 2 次以上（其实用 LET 更好，但辅助列对老版本友好）；
计算过程需要对中间结果做人工检查；
公式需要经常改动条件或口径。

辅助列的”原罪”可以避免

反对辅助列的人常说”辅助列会让表变丑”，这个担心其实可以解决：把辅助列放在独立工作表里，或者把辅助列宽度设为 0.5 并折叠列组。同时，辅助列要有清晰的表头，比如”辅助_单价折后”，而不是”辅助1″。另外，很多辅助列其实就是数据表中的”计算列”——如果用超级表，Excel 会自动把整列公式统一，一次修改，全列生效。

哪些事不该用公式做：Power Query 与透视表的判断题

这是本文最想让人记住的一节。几乎所有 Excel 培训都在教”公式能做多复杂”，但很少有人说清”公式不该做什么”。选错工具，比写错公式的代价更大——写错公式报个错，选错工具要花三个月返工。

公式的天然边界

公式擅长两件事：小规模计算和单元格级别的即时响应。一旦需求跨过这两条边界，公式就开始吃力：

数据源多于 3 个且结构不一致；
需要反复执行的清洗流程（去重、格式统一、补缺失值）；
数据量超过 10 万行，且公式引用整列；
流程需要被审计、被回溯、被交接。

这四条红线，跨过任何一条，都该认真考虑换工具。

三方案对比：什么时候必须换

需求场景	公式	透视表	Power Query	结论
单表条件求和	✔ 最优	✔ 可行	✘ 杀鸡用牛刀	用公式
多表合并（结构相同）	△ 可用但慢	△ 需多表合并	✔ 最优	用 PQ
多表合并（结构不同）	✘ 极难	✘ 不支持	✔ 唯一选择	用 PQ
定期报表（每月重跑）	✘ 手工重复	△ 需手动刷新	✔ 一键刷新	用 PQ
按维度多维汇总	△ 一堆 SUMIFS	✔ 最佳	✔ 可出结果表	用透视表
一次性分析（临时需求）	✔ 灵活	✔ 使用	✘ 配置成本高	用公式或透视
单元格级即时联动	✔ 唯一选择	✘ 无法联动	✘ 无法联动	用公式
数据清洗（统一格式）	△ 公式能做但繁琐	✘ 不支持	✔ 最优	用 PQ
10 万行以上整列计算	✘ 卡到崩溃	✔ 较快	✔ 极快	用 PQ
查找并返回明细列表	△ FILTER 可用	△ 需钻取	△ 需配置	视情况

看这张表时，可以用一句话总结判断标准：公式问”这一格是多少”，透视表问”按什么分组是多少”，Power Query 问”数据该怎么来”。三个问题对应三个工具，清楚得很。

一个真实案例：为什么改用 PQ 后省了 3 天

某零售公司每月要做”门店销售日报”。最初做法：12 家门店各一个 CSV，复制粘贴到一个总表，用 SUMIFS 算各门店、各品类日报。每个月手工操作两天，还经常出错。

换成 Power Query 后：配置一次”文件夹合并 + 维度表关联”，之后每月把新 CSV 丢进文件夹，点一下刷新，五秒出结果。第一月的配置成本是半天，后面每月省两天。三个月后，原来做表的同事被调去做分析（他真正想做的事）。

不该用公式的三种典型症状

如果你发现自己在做以下三件事，就该停手换工具：

用 Ctrl+C 复制数据到另一个工作表——这是 PQ 的第一步；
为一个报表写 20 条 SUMIFS——这是透视表该干的；
公式超过 300 字符，还在想办法再优化——这是该用辅助列或 PQ 的信号。

大多数人做错的地方：Excel 公式选题的四个反常识

写了二十年 Excel 教程，我发现关于 Excel 公式的讨论里，有大量被反复灌输但其实是错的观念。这一节集中讲讲。

反常识一：公式越”高级”不代表越专业

很多人的潜意识里，SUMPRODUCT 比 SUMIFS 高级，FILTER 比 SUMIFS 高级，LAMBDA 又比 FILTER 高级。于是他们专挑”高级”的用，觉得这体现水平。恰恰相反，专业的人用最简单的公式。判断标准是：这条公式，接手的人能不能读懂、改得动、在别的版本里开得了。最简单的公式，往往最能经受这三个考验。

反常识二：动态数组不是未来，而是”选项”

铺天盖地的课程在说”动态数组是未来，赶紧学”。我完全同意要学，但强烈反对”用它替代一切”。诸如”按条件求和”、”单值查找”这类需求，SUMIFS 和 INDEX+MATCH 依然是最优解。那些说”再用 VLOOKUP 就是落后”的说法，纯属话术。VLOOKUP 会用得对，永远不会过时，它只是有一个”只能往右查”的限制，而 90% 的场景根本不需要往左查。

反常识三：AI 生成 Excel 公式，验证比生成更重要

这两年我观察到，很人把 AI 当作”公式生成器”，拿到就跑。实际上 AI 把写公式的成本从 15 分钟降到了 15 秒，验证成本却没降——反而因为写得太快、数量太多，验证量暴增。真正稀缺的能力已经不是”会不会写公式”，而是“能不能快速判断一个公式对不对”。这套六步法的核心，其实就是把这套验证能力流程化、模板化。会用 AI 写公式的人很多，能用 AI 写对的却不多，差的就是这一层。

反常识四：别怕公式丑，怕公式聪明

职场里有一个现象值得警惕：越不好读的公式，越容易失控。一条 400 字符、嵌套五层的”聪明”公式，三个月后大概率没人敢动，最终结果是被删掉重写。而那些用三行辅助列做出的”笨”公式，反而活得久、改得动。

我的建议是：让公式有点”笨”。能用辅助列的，别硬塞到一个格里；能用 LET 分步的，别写成一行；能用表格列名的，别写 A1 引用。这些”笨”的做法，恰恰是让表在团队里活过一年的关键。

实操案例：从零完成一个真实报表的 Excel 公式

把六步法串起来跑一遍，才能体会它的价值。下面用一个我认为极有代表性的真实案例——月度销售汇总表——完整演示一遍。

需求拆解

场景：你是一位销售运营，每月要给老板做一张”销售员业绩汇总”表。需求描述如下：每位销售员一行，展示该销售员本月的订单数、销售总额、平均单价、去重客户数、所属大区。数据源：订单明细表（238 行），销售员表（8 人），产品表（5 类）。要求以后每月更新数据能自动刷新。

按六步法执行

第一步：结构地图

直接复用本章前文的模板，把”当前 238 行、每周追加 40 行”写进去。这个信息决定了后续必须用结构化引用或整列引用。

第二步：业务语言

“统计每位销售员在本月的订单数、销售额合计、单笔平均金额、独立客户数、所属大区。本月指 6 月 1 日到 6 月 30 日。散客订单（客户名称空）也算独立客户。”

第三步：三段式输出

要求 AI 对每个指标分别给公式、解释和预期结果。这里要注意：五个指标让 AI 一口气写，容易混；分五次提问，或者明确”按指标分段给出”，效果更好。

第四步：边界处理

主动告诉 AI：客户名称有空值、销售员名称可能有前后空格、部分订单金额为 0、日期可能是文本格式。让 AI 逐条处理。

第五步：双版本

由于这张表要发给老板和财务，收件人用的是 Excel 2019，直接要兼容版。但如果自己的团队要复用，要求保留一套新版写法。

第六步：三行验证

挑三个典型销售员：一个高业绩、一个低业绩、一个当月无订单。第三个最关键，因为无订单时很多公式会 #DIV/0! 或显示错误值。

最终公式样例（兼容版）

假设销售员名单在 J2:J9，本月日期区间 J1 单元格：
订单数      =COUNTIFS(D:D,J2,B:B,">="&DATE(2026,6,1),B:B,"<="&DATE(2026,6,30))
销售总额    =SUMIFS(H:H,D:D,J2,B:B,">="&DATE(2026,6,1),B:B,"<="&DATE(2026,6,30))
平均单价    =IFERROR(K2/COUNTIFS(D:D,J2,B:B,">="&DATE(2026,6,1),B:B,"<="&DATE(2026,6,30)),"-")
独立客户数  =SUMPRODUCT((D:D=J2)*(B:B>=DATE(2026,6,1))*(B:B<=DATE(2026,6,30))/COUNTIFS(C:C,C:C&"",D:D,J2,B:B,">="&DATE(2026,6,1),B:B,"<="&DATE(2026,6,30)))
所属大区    =IFNA(VLOOKUP(J2,销售员!$A:$B,2,0),"未分配")

看第四条公式，那个”独立客户数”是典型的多条件去重计数，写法很绕但符合逻辑：先用三个条件相乘得到 mask，再用每个客户自己的出现次数做除法，最后求和，得到的就是去重计数。这类公式如果不做分区讲解，几乎没人能读一遍懂。

过程中踩到的坑

实际执行时踩了两个坑，值得记录：

坑一：整列引用导致 #DIV/0!。SUMPRODUCT 里用了 D:D，会从第 1 行开始算，遇到空行时 COUNTIFS 返回 0，除以 0 就报错。解法是把整行限定为 D2:D100000 或改用 IFERROR 包裹，但包裹后又没法区分”正常”和”错误”。最终改成超级表结构化引用，自动只取表格范围内的行。

坑二：AI 默认使用新版函数。第一次提问时忘了限制版本，AI 给了 XLOOKUP 和 FILTER，在 Excel 2019 上全部 #NAME?。这条教训是——第五步”要兼容版”必须在第一次提问就提出，不能等验证时再补，因为 AI 会顺着新版思路一路往下写。

最终交付物的结构

一个好的公式交付，不只是公式本身。我会把它整理成这样的结构：

公式区：每个指标一行公式，单元格引用明确；
口径区：每行对应的业务口径一段文字，比如”订单数含退回订单，销售额不含”；
验证区：三个销售员的手工核对结果和公式结果并列；
维护区：改口径时应该改哪一段，加数据时怎么操作。

这份结构本身就说明，Excel 公式从来不是”一个格子里的秘密”，而是一个可以被交接、被审计的资产。

把六步法变成肌肉记忆

回头看，六步法的每一步都可以用一句话概括：

交出结构，别让 AI 猜列；
讲业务，别讲函数；
要解释，别只要答案；
说脏数据，别假设干净；
要两套，别只顾自己；
先三行，别直接铺满。

这套流程的代价是什么？我的实测是——比直接复制公式多花 2-3 分钟。收益是什么？从”经常返工”变成”一次成型”，从”用得上”变成”用得住”。对一次性的临时计算，多花的时间也许不值；但如果你每月都要做同样一张表和同样一套 Excel 公式，这 2 分钟的投资回报率极高。

最后想说一个观察：AI 出现之后，Excel 公式的门槛不是降低了，而是位移了。从”会不会写函数”位移到了”会不会描述需求、判断对错、选对工具”。前面那项技能，AI 已经帮你跨过去了；后面那三项，恰恰是资深和普通的分水岭。

常见问题（FAQ）

Q1：AI 给出的 Excel 公式版本太高，我的电脑用不了，怎么办？

在提问时就直接写明”目标环境是 Excel 2016 / 2019 / WPS 某版本，请只使用该版本支持的函数，并按 Ctrl+Shift+Enter 数组公式的输入方式标注”。如果拿到的是 XLOOKUP、FILTER、LET 这类新函数，可以要求 AI 逐条给出 INDEX+MATCH、SUMPRODUCT、辅助列等兼容写法。判断是否兼容的最快方法：在 Excel 里打开公式联想，输入前两个字母，联想列表里没有的函数，就是你的版本不支持。

Q2：为什么同一个 Excel 公式，我这边是对的，发给同事就成了 #NAME? 或 #REF! ?

#NAME? 通常是版本问题——函数在对方版本里不存在；#REF! 则多半是公式引用了被删除或重置的列，或者跨表引用时工作表名被改名。解决方法：先统一版本口径（要么都升级，要么都降级），再让公式尽量使用结构化引用而不是写死的单元格地址，因为结构化引用对行列变化更耐受。

Q3：SUMIFS 和 FILTER 到底该用哪个？我总感觉新版更”高级”。

看结果形态。结果是一个数字，用 SUMIFS；结果是一张明细表，用 FILTER。SUMIFS 在兼容性、性能、出错信息清晰度上都优于”SUM(FILTER(…))”，不要因为 FILTER 更新就硬用它做求和。真正需要 FILTER 的场景只有两个：按条件返回多行明细、或者在函数内部需要一个”动态长度”的数组。

Q4：六步法看起来挺麻烦，做一次临时报表也值得这么走吗？

不值得。六步法是为”需要复用、需要交接、需要长期维护”的 Excel 公式设计的。如果只是算一下随手问问，直接让 AI 给个公式再手工核对一下最快。判断标准：这个公式用一次还是用十次以上？只发给朋友还是发到部门群？改一改就用还是每月重做？有超过两项是”长期”的，就值得按六步法走。

Q5：AI 说”这里我假设了某某条件”，我该怎么处理这些假设？

把每条假设逐项改成确认状态：假设成立的，在提问里正式写一遍，让 AI 不再把它当假设；假设不成立或不确定的，立刻追问细节。一个好的做法是把 AI 的假设清单粘贴回给它，并逐条标注”确认 / 否定 / 需要处理”，再让它重写公式。这样重写一次，往往能把 90% 的错误提前消灭。

Q6：我手上的 Excel 公式已经堆了几十个，怎么开始整理？

先做”公式普查”：把每个工作表切换到”显示公式”模式（快捷键 Ctrl+`），截图或导出，找出所有长度超过 200 字符的公式，这些是重点风险区。然后按三个优先级处理：一是公式口径不明确的，先补注释文档；二是重复计算同一个值的，改成辅助列或 LET；三是跨表引用写死的，改成结构化引用或命名区域。建议一个月只处理一个表，避免一次改动过大导致连锁错误。

THE END