系列第 3 天 主题名称：AI 写 Excel 公式（知道要算什么但不会函数，或嵌套多层写不出来，用一句话描述需求换一个可用公式） 与前一版输入结构差异：第 2 天是「照抄书的目录原文 + 我的三个疑问」的双轨减法式，第一手输入是书本身的骨架，AI 先做删减定位；本篇换成「物理坐标 + 两步编译」：第一步必须把每张表的物理地址钉死（哪个工作表、哪一列、标题在第几行、数据到第几行），第二步要求 AI 先把我的大白话翻译成一段中文算式给我审逻辑，逻辑确认后才允许它写公式。第一手输入从"内容骨架"换成了"表格的地址与脏数据形态"，且输出被强制拆成两次——第一次只出算式、不出公式，这是前两版都没有的结构。

AI 写 Excel 公式：把一句大白话换成一个能拖下去用的公式

一、极简入口

只有三条信息也能跑。把下面整段复制给 AI，把【】连同样例文字整个替换成你自己的内容（方括号连同里面的"如：xxx"一起删掉，只留你写进去的那句话）。

三条信息分别是表长什么样（坐标）、我要算的那句话（含分档规则）、哪里不干净（例外）。第三条是绝大多数人会漏掉的一条——漏掉它，AI 会默认你的表格是干净的，给出的公式能算对 99% 的行，剩下 1% 悄悄算错，而你不会发现。
新手最常见的卡点是看不懂 AI 给的公式，所以先把六个词说清，下文含义不变：
公式
：以等号开头、让单元格自动算出结果的那行字。
函数
：公式里现成的工具，比如 SUM
是求和、IF 是做判断。公式里出现的大写英文单词都是函数。
嵌套
：一个函数装在另一个函数里面，像套娃。套三层以上就很容易少写一个括号，这就是"嵌套多层写不出来"的由来。
绝对引用
：公式里带 $ 的写法，如 $E:$E。作用是往下拖公式时，这一截不会跟着往下跑。不带 $ 的叫相对引用，会跟着跑——拖拽后结果错位基本都是这里出的问题。
文本型数字
：看着是数字、实际被表格当成文字的单元格（通常左对齐、左上角有绿色小三角）。求和、条件求和都会直接跳过它们，导致结果偏小。
脏数据
：表里妨碍计算的那些毛病——空值、前后空格、重复行、格式不统一。不是"错误数据"，是"格式不整齐、会让公式算歪的数据"。
二、素材预填表
动笔前先填这张表。填不满的空着，但要知道空的是哪一格。
要素
怎么找
例子
表格软件与版本
打开软件，点"文件→账户→关于"能看到版本号；WPS 看左上角"WPS Office"旁的下拉菜单
Excel 2016，同事还有用 WPS 的
数据在哪个工作表、标题在第几行
看表格底部的标签名；标题行看第一行是不是列名（"订单号""金额"这种词）；把光标放到数据最后一行按 Ctrl+End 可直接跳到表尾
明细表，标题在第 1 行，数据第 2–3000 行
参与计算的列与含义
点一下列标（A、B、C 那一排字母），整列变灰就是选中了；记下字母和这一列的业务含义
C 列＝销售员，E 列＝订单金额，F 列＝回款状态
结果写在哪个单元格
想好结果放哪，写清工作表名和单元格地址（列字母+行号，如 C2）
汇总表 C2，往下拖到 C9
一行代表什么（唯一识别字段）
问自己：这张表里哪一列的值是不重复的？通常是单号、工号、身份证这类。"唯一"指不会出现两次
一行＝一个订单，A 列订单号唯一
我看到的表格毛病
选中整列看窗口底部状态栏的"计数"和"求和"：如果计数大于你肉眼看到的数字个数，说明混了文字
E 列求和比手工加总少约 6000，怀疑有文本型数字
判定规则（分档阈值）
把规则的边界写清：80% 算不算进低档？等于 100% 归哪档？边界说不清，结果必然差一档
<80% 提成 0；80%–100% 按 1%；>100% 按 1.5%
这张表和前两天的用途不同：第 1 天那张是"盘点我手上有哪些素材"，第 2 天是"确认我面对的是哪一本书"，这张是给我自己的表画一张地址图——公式写错的根源，九成不是逻辑错，是地址错。
三、分段落提示词
以下四段按真实的写公式顺序排列：先定坐标，再定算式，再处理例外，最后压版本和长度。可以一次全贴，也可以分四次贴——强烈建议分次，因为第 2 段一旦逻辑就错了，后面三段全白做。
第 1 段：表格物理坐标
怎么填
：列字母必须写准。点一下列标整列变灰，看那一排字母就知道 A、B、C 各自的含义，别凭印象写。数据结束行要知道，否则 AI 只能给你整列范围。
别这样写
：别写"我的 Excel 里有销售数据，帮我算提成"。AI 不知道你的金额在第几列，只能假设，而假设一旦错位，公式不报错、只算错——这是最危险的一类错误。
填不出来时
：不知道数据到第几行，就写"不确定行数，数据可能继续增加"，让 AI 用整列范围（如 $E:$E
）并说明整列范围的代价（计算变慢、表尾若有合计行会被误算）。这条代价必须让它写出来，不能默默替你决定。
第 2 段：一句话需求的算式拆解
怎么填
：大白话就按你平时跟同事解释的口吻写，但分档规则必须带上下限符号（<
、≤）。边界含糊是提成类公式最常见的错源：80% 到底算 0 还是 1%，取决于你写不写那个等号。
别这样写
：别一上来就说"给我一个 SUMIFS 加 IF 的公式"。你要的是结果，不是函数名；先点名函数，等于替 AI 做了它该做的选型，一旦选错，你还会以为是自己需求没说清。
填不出来时
：如果连规则都说不清（比如"提成按公司制度来"），就写"规则我不确定，请给我三档的常见做法各一套，并标注哪些是行业惯例、哪些必须由我确认"。这时它给的是可选项，不是结论，你自己挑一档再去问公式。
第 3 段：例外与脏数据
怎么填
：写你实际看到的现象，不写猜测。"有 37 行是空的"比"可能有些空值"有用得多。数字不用精确，量级对就行。
别这样写
：别写"数据有点乱，你帮我处理一下"。"有点乱"无法转成具体写法，AI 只能加一堆 IFERROR
把错误值藏起来——错误被藏掉不等于被解决，你看到的 0 可能是"真空"也可能是"算错了"。
填不出来时
：只写一条最要命的（通常是文本型数字或跨表对不上），其余写"暂无"。宁可只防一处，也不要用一堆容错函数把公式撑到看不懂。
第 4 段：版本、长度与可读性
怎么填
：版本一定要写。LET
、IFS、XLOOKUP 这些较新的函数在 2016 里直接报 #NAME?（意思是"我不认识这个词"），而在 2021 和 Microsoft 365 里能用。同一句话，版本不同，答案完全不同。
别这样写
：别写"给我一个最简洁的公式"。最简洁往往意味着嵌套最深、最难维护；你三个月后打开它，只会看到一个一百多字符的长串，不敢改也不敢删。
填不出来时
：不知道版本，就写"不确定版本，请给两版：一版只用 2007 就能用的老函数，一版用新函数，并标明各自要求的最低版本"。两版并排，你打开试一下就知道该用哪版。
四、输出硬性约束
把下面这段追加在任意一段提示词末尾，四段都适用：
第 5 条在这里要单独说清：在本篇里，口径指"这个结果的统计范围"——哪些行被算进来了、哪些被排除了。前面第 1 天的口径是"数字怎么算出来的"（÷30 天、含税），第 2 天的口径是"这句话的来路"（原文还是推测），这一篇是"哪些行参与了计算"。三篇口径的含义各不相同，但作用一致：让你在结果被质疑时，能一句话说清它到底是什么。
五、追问指令表
拿到初稿后，用这几条做局部调整。一次只用一条，改完看效果再决定下一条。
想改什么
直接发这句
公式报 #NAME?
我这边版本不支持你用的函数，把它们换成 2007 就能用的老写法（IF 嵌套 + SUMIFS），并告诉我嵌套了几层、括号一共几对
太长，看不懂
拆成辅助列，一列一个中间量，逐列给列名和公式，并说明这一列为什么值得单列
不想加辅助列
合并成一个公式，但按层换行缩进，每层前面用中文标出这一层在算什么
结果和手工算的对不上
分四步核对：先只算已回款金额、再算月目标、再算达成率、再算系数，每步输出中间值，指出从哪一步开始和我的数不一致
拖下去以后错位
检查引用：凡是跨表、跨固定区域的，都要加 $ 锁住；逐处列出哪些该锁、哪些不该锁，并说明不锁会跑偏到哪
空值显示成 0，看不出是没数据还是算错
把 IFERROR 改成显式判断：真空显示 未填、计算失败显示 算错，两者不许都显示 0
三个月后要交给别人维护
给每个关键区域起中文名（公式里用名字代替 $E:$E），并列一张对照表：名字、对应区域、含义
六、自定义槽位
下面三项整段可以留空，留空不影响输出。有特殊要求时才填。
留空的原因很实际：多数人只是要一个能算对的公式，硬填这些约束，AI 会把注意力放在格式上。等你改到第二版、发现自己每次都要手动删掉"建议清洗数据"这句话时，再把禁忌填进去，效率最高。
七、完整示例
填好的输入（用的是极简入口那段）
示例中的人名、店名、金额均为虚构占位，不对应任何真实主体。
输出节选
第一步｜中文算式（此时不出公式）
已回款金额 ＝ 把明细表 E 列里，C 列等于当前这个人、且 F 列是"已回款"的那些格加起来
月目标 ＝ 去目标表找 A 列等于当前这个人的那一行，取 B 列
达成率 ＝ 已回款金额 ÷ 月目标
提成比例 ＝ 达成率 < 80% 取 0；80% ≤ 达成率 ≤ 100% 取 1%；达成率 > 100% 取 1.5%
提成 ＝ 已回款金额 × 提成比例
这里有两个中间量会被用到两次：已回款金额（第 1 步算出来，第 5 步还要用）和达成率（第 3 步算出来，第 4 步要用）。下面第一版公式就是把它们反复抄——这也是它被否掉的原因；第二版各给它们一列，抄写就此消失。
歧义一处：你说"已回款"是否包含部分回款？【假设：不含，只认 F 列文字正好是"已回款"的行。若你的表里还有"部分回款"这个值，分母分子都会变，公式需改。】
第二步｜公式（一格里写完的版本，Excel 2016 可直接用）放在汇总表 C2，往下拖：
=SUMIFS(明细!$E:$E,明细!$C:$C,$A2,明细!$F:$F,"已回款")*IF(SUMIFS(明细!$E:$E,明细!$C:$C,$A2,明细!$F:$F,"已回款")/SUMIFS(目标!$B:$B,目标!$A:$A,$A2)>=1,0.015,IF(SUMIFS(明细!$E:$E,明细!$C:$C,$A2,明细!$F:$F,"已回款")/SUMIFS(目标!$B:$B,目标!$A:$A,$A2)>=0.8,0.01,0))
这个写法太长（224 个字符，已回款金额被抄了三遍、达成率被抄了两遍），超过你定的 200 字符上限，不推荐。 按你允许加辅助列的条件，改成三列，一列一个中间量：
列
列名（写在 B1、C1、D1）
公式（写在 B2，往下拖）
B
已回款金额
=SUMIFS(明细!$E:$E,明细!$C:$C,$A2,明细!$F:$F,"已回款")
C
达成率
=IFERROR(B2/SUMIFS(目标!$B:$B,目标!$A:$A,$A2),"目标缺失")
D
提成
=IF(C2="目标缺失",0,B2*IF(C2>=1,0.015,IF(C2>=0.8,0.01,0)))
三列各自只干一件事，出问题时你能一眼看出是哪一列算歪了。若你的版本是 2021 或 Microsoft 365，另有 LET 写法可以把三列压回一格，但可读性会下降，我不建议在需要长期维护的表里用。
示例数据下的核对结果（虚构数据，用来验证公式而非演示效果）：
销售员
月目标
已回款金额
达成率
提成比例
提成
张一
100000
83000（30000+45000+8000）
83%
1%
830
李二
80000
95000
118.75%
1.5%
1425
赵三
目标表里没有
12000
目标缺失
—
0
三行的结果都能手算复核：83000 ÷ 100000 ＝ 83%，落在 80%–100% 档，取 1%，83000 × 1% ＝ 830；95000 ÷ 80000 ＝ 118.75%，超过 100%，取 1.5%，95000 × 1.5% ＝ 1425。
第三步｜逐段说明
SUMIFS(明细!$E:$E, ...)
：条件求和。第一个参数是"加哪一列"，后面成对出现"在哪一列判断、判断等于什么"。
$E:$E
、$C:$C：整列引用，前面加了 $，所以往下拖时这一截不会跑。这里的 $ 不是货币符号，是"锁住"的意思。
两个条件 明细!$C:$C,$A2 与 明细!$F:$F,"已回款" 是"并且"关系：这个人 并且 已回款。
IFERROR(...,"目标缺失")
：除法分母为 0 或找不到目标时会报错，这里把报错换成一句看得懂的中文，而不是显示成 0——0 和"找不到人"是两件事，混在一起会掩盖问题。
提成那一列的 IF 从高往低判：先判是否 ≥100%，再判是否 ≥80%，剩下的自然就是 <80%。顺序反过来写会全部落到第一档，这是嵌套判断最常见的错。
第四步｜口径与待补
已回款金额口径：仅统计 F 列文字为"已回款"的订单，状态为空的行不计入，时间不设限。这个口径直接影响分子，请照此复核。
[待补：E 列文本型数字的准确影响额] —— 示例里张一有一条 6000 的已回款订单被存成文本，公式没把它算进去，所以显示 83000 而不是 89000。建议先做一步：选中 E 列 → 数据 → 分列 → 直接点完成（不改任何选项），文本型数字会变成真数字，左上角绿色小三角消失。清洗后张一的达成率从 83% 变 89%，仍在 80%–100% 档，提成从 830 变 890——档位没变，但金额差 60 元。如果你的表里文本型数字占比更大，档位就可能整体跳一档，这就是必须先把这步做完再谈公式的原因。
[待补：F 列除"已回款""未回款"外还有哪些取值] —— 若存在"部分回款"，当前公式会把它整单排除，分子偏小。
这个公式在什么情况下会算错：目标表里同一个销售员出现两行时，SUMIFS(目标!$B:$B, ...) 会把两行月目标相加，分母翻倍，达成率直接掉一半，而公式不会报错。
为什么这样写
对照四处看，能看出这套结构和前两天的根本区别。