统计公式的生成与验证流程
第三章的五个问题都出在数据的形态上，这一章开始让 AI 真正产出东西。产出物是公式，公式和格式设置有一个本质区别，格式错了看得见，公式错了往往看不见，一个错误的合计数安安静静地待在单元格里，和正确的数字长得一模一样。所以本章在第二章“空白列试算加边界检验”的基础上再往前提一步，每个需求在发给 AI 之前，先用最笨的办法手工算出答案，这个数字叫底账。AI 返回的公式执行后与底账相符，公式才算通过。五个案例覆盖求和、计数、平均、排名和公式解读，全部在同一份三十行的销售明细上完成，提示词与 AI 回复照旧原文照录。
手工底账与练习数据
本章反着第二章的次序做事。第二章是拿到公式之后再验证，本章是需求一确定就先把答案手工算出来，公式还没进对话框，正确得数已经写在纸上。这样做多花三五分钟，换来的是每条公式都有一个独立于 AI 的参照物，对得上就收工，对不上立刻知道要追问。
三十行销售明细的构成
练习文件的第一张工作表叫“销售明细”，A 列到 F 列依次是订单编号、下单日期、区域、业务员、产品、金额，数据在第 2 行到第 31 行。区域有华东、华北、华南三个，业务员八名，产品是打印机、碎纸机、投影仪三种。F 列里有 3 行金额是 0，对应样机赠送单，这三个零是特意留的，第三个案例要靠它们说事。另外两张工作表“业务员汇总区”和“区域考核区”分别服务排名与公式解读两个案例，用到时再介绍。
模板下载：第四章公式练习文件
五笔底账的算法与得数
动笔提问之前，五个需求的答案先用筛选、点数、计算器这些不会骗人的老办法各算一遍。每一笔的算法都写在下表里，正文执行公式时逐一回来对。
底账要用和公式不同的路径算出来。用 SUMIFS 去验证 SUMIFS 等于没验证，两次都错在同一个地方时根本发现不了，筛选加状态栏、逐行点数这类笨办法与函数的计算路径完全独立，对上了才有说服力。
多条件求和公式的获取与执行
销售主管要的第一个数是华北区打印机的金额合计，这是一个典型的双条件求和，两个条件分别落在 C 列和 E 列上，求和对象是 F 列。底账已经立好，23500。
结构转写与提问原文
表格结构按第二章的列名加类型写法转写，这是本章第一次发这张表，结构要写全，后面四个案例接着这段对话问，就不必重贴了。
text
我在用Office LTSC专业增强版2024。有一张销售明细表，结构如下：
A列 订单编号（文本）
B列 下单日期（日期）
C列 区域（文本，取值为华东、华北、华南）
D列 业务员（文本）
E列 产品（文本，取值为打印机、碎纸机、投影仪）
F列 金额（数值，其中3行是0，是样机赠送单）
数据在第2行到第31行。示例行：
X25040001 2025/4/1 华东 陈立 打印机 4200
X25040002 2025/4/1 华北 刘洋 打印机 6800
请写一个公式，统计华北区打印机的金额合计，结果放在H2单元格。
AI 的回复如下。
text
在 H2 输入：
=SUMIFS(F2:F31,C2:C31,"华北",E2:E31,"打印机")
参数说明：
* F2:F31 → 求和区域，写在第一个参数
* C2:C31,"华北" → 第一组条件：区域列等于华北
* E2:E31,"打印机" → 第二组条件：产品列等于打印机
两组条件是"且"的关系，只有同时满足两个条件的行才会计入合计。
注意 SUMIFS 与 SUMIF 的参数顺序相反：SUMIF 把求和区域放在
最后一个参数，SUMIFS 把求和区域放在第一个参数。建议多条件
求和一律用 SUMIFS，它在单条件下同样能用，不必在两个函数
之间来回切换记两套顺序。
参数结构的逐项拆读
把公式粘进 H2 之前，先按 AI 的说明把三段参数在自己表里各自指认一遍，这个动作花不了一分钟，却能把“看懂了”和“以为看懂了”区分开。
-
F2:F31是被加的数，指认 F 列金额，没有问题。 -
C2:C31,"华北"是第一组条件，条件区域和条件值成对出现，区域在前，值在后。 -
E2:E31,"打印机"是第二组条件，写法与第一组完全一样，需要更多条件时照这个格式继续往后成对追加。
三段都指认无误，公式进 H2，回车，返回 23500，与底账一致。再用独立路径复核一遍，筛选 C 列等于华北、E 列等于打印机，选中筛出的 5 个金额单元格，状态栏求和显示 23500，两条路径会师。
SUMIF 求和区域在尾、SUMIFS 求和区域在头，这个相反的顺序是抄公式时最常见的错位点。拿到 AI 的公式先看第一个参数是不是要加的那一列，是 SUMIFS 就该是，不是就要停下来核对。
计数条件中比较式的书写格式
第二个需求从求和换成计数，统计华东区金额大于 5000 的订单有几笔。计数不关心金额加起来是多少，只关心有几行满足条件，底账是逐行数出来的 5 笔。这个案例真正要盯的是一个细节，比较条件“大于 5000”在函数里到底怎么写。
提问与返回的公式
对话接着上一节，表格结构不再重贴。
text
接着用上面的销售明细表。统计华东区且金额大于5000的订单有几笔，
结果放在I2单元格。
AI 的回复如下。
text
在 I2 输入：
=COUNTIFS(C2:C31,"华东",F2:F31,">5000")
说明：
* COUNTIFS 只数满足条件的行数，不求和，所以没有求和区域，
全部参数都是"条件区域,条件值"的成对结构
* 条件 ">5000" 必须整体包在一对英文双引号里，比较符号和
数字都写在引号内部，这是 COUNTIF/SUMIFS 这一族函数的
固定写法，写成 F2:F31>5000 会直接报错
* ">5000" 不含等于，金额恰好是 5000 的订单不会被数进去；
要含 5000 本身，条件改成 ">=5000"
公式进 I2，返回 5，与底账一致。回复里主动提到的临界值问题正好可以顺手验证，在 J2把条件改成 ">=5000" 再执行，返回 6，多出来的一笔正是周敏那张恰好 5000 的订单 X25040011。“大于”和“大于等于”在这张表上差出一整笔订单，提需求的人嘴里的“5000 以上”到底含不含 5000，动笔之前要问清楚。
手工重敲引出的报错
公式从聊天窗口复制粘贴一次成功，隔天在另一台电脑上凭记忆手工重敲，敲完回车，Excel 弹窗说公式存在错误，光标退回编辑栏。敲的内容是下面这样。
text
=COUNTIFS(C2:C31,"华东",F2:F31,“>5000”)
盯着看了两遍没找出毛病，把两条公式一起发给 AI。
text
我把公式手动敲了一遍：=COUNTIFS(C2:C31,"华东",F2:F31,“>5000”)，
Excel弹窗提示公式存在错误，不让我回车。它和你之前给我的
=COUNTIFS(C2:C31,"华东",F2:F31,">5000")哪里不一样？
AI 的回复如下。
text
两条公式的差别在最后一对引号上：
你敲的是 “>5000”，用的是全角引号（中文引号），
原来的是 ">5000"，用的是半角引号（英文引号）。
Excel 的公式只认半角引号。手工输入时中文输入法处于中文
标点状态，敲出来的引号是全角字符，Excel 无法把它识别为
文本定界符，于是判定公式语法错误。
避免的办法：
1. 敲公式前把输入法切到英文，或关闭中文标点
2. 公式里的中文内容（如"华东"）可以先敲好引号再切中文输入
3. 从聊天窗口、网页复制公式时通常没有这个问题，但要警惕
某些聊天软件会自动把半角引号"美化"成全角引号
按回复把全角引号删掉，切英文输入法重敲 ">5000"，回车通过，返回 5。两对引号在屏幕上几乎长得一样，放大看才能发现全角引号更宽、位置偏下。
条件计数、条件求和这一族函数的比较条件必须整体放在英文半角引号里。手敲公式报语法错误又查不出原因时，第一个检查对象就是引号和逗号是不是被中文输入法换成了全角字符，这两种字符肉眼极难分辨。
平均值统计中零值样本的排除
财务要一个平均单笔成交金额，放进月报。最直接的写法是 =AVERAGE(F2:F31)，返回 4260。这个数算得没错，但它把 3 笔 0 元的样机赠送单也当成了成交订单，30 个数一起平均，赠送单一分钱没成交却占着分母。真实的成交平均值应该只看那 27 笔，底账是 127800 除以 27 等于 4733.33。
排除条件的提问与回复
text
接着用上面的销售明细表。我要算平均每笔订单的成交金额。
F列有3行金额是0，是样机赠送单，不是真实成交，
不应该参与平均。公式怎么写？
AI 的回复如下。
text
用 AVERAGEIF 加一个"不等于0"的条件：
=AVERAGEIF(F2:F31,"<>0")
* "<>0" 是"不等于0"的意思，<> 是 Excel 里的不等号
* 满足条件的只有27行，分子分母同步变化：
=AVERAGE(F2:F31) → 金额合计÷30，赠送单被当作"成交了0元"
=AVERAGEIF(F2:F31,"<>0") → 非零金额合计÷27
两点提醒：
1. AVERAGE 本来就会忽略空单元格，但 0 是一个真实的数值，
不是空，它会老老实实参与平均。"单元格是空的"和
"单元格里是0"在统计上是两回事
2. 以后要再叠加条件（比如只算华东区的非零平均），换成
AVERAGEIFS：
=AVERAGEIFS(F2:F31,F2:F31,"<>0",C2:C31,"华东")
注意 AVERAGEIFS 和 SUMIFS 一样，被平均的区域写在第一个参数
两个平均值的对照与口径判断
两条公式并排放进 H6 和 H7，=AVERAGE(F2:F31) 返回 4260，=AVERAGEIF(F2:F31,"<>0") 返回 4733.33，后者与底账一致。两个数差了 473.33，差异全部来自分母，3 笔零元订单没有给分子贡献一分钱，却把分母从 27 撑到了 30，平均值被平白稀释了一成。数据量越大、零值占比越高，这种稀释越严重，而月报的读者只看见一个“平均 4260”，不会知道里面掺着赠送单。
要不要排除零值不是公式问题，是口径问题。这张表里的 0 是“本来就不该算”的赠送单，排除是对的。换一张学生成绩表，0 分是真实考出来的成绩，排除它平均分反而失真。判断标准只有一条，这个 0 代表“无效样本”还是代表“取值恰好为零的有效样本”，前者排除，后者保留。
把 AVERAGEIF 的结果和 AVERAGE 的结果并排放着看是个便宜的自检办法。两个数一样，说明区域里根本没有零值，条件白加了但无害；两个数差得离谱，先回头确认零值的笔数和来历，再决定用哪个数进报告。
并列名次不跳号的排名方案
第四个需求换到“业务员汇总区”工作表，A 列是八名业务员，B 列是各自的销售额合计，数据在第 2 行到第 9 行，B 列的数字已经事先用 SUMIF 从明细汇总完成。人事要按销售额排名发通报，点名要求并列的算同一名、后面的名次不跳号。底账里已经埋了一个考验，周敏和刘洋同为 19400，正好并列。
第一版回复与实测跳号
提问把不跳号的要求原原本本写了进去。
text
接着用这个文件，业务员汇总区工作表里A列是8名业务员，
B列是各自的销售额合计，数据在第2行到第9行。请按销售额
从高到低排名次，放在C列。要求并列的算同一名，并且后面的
名次不跳号，比如两人并列第2，下一个人是第3不是第4。
AI 的回复如下。
text
在 C2 输入以下公式，下拉填充到 C9：
=RANK.EQ(B2,$B$2:$B$9)
RANK.EQ 默认按降序排名，销售额最高的是第1名。
数值相同的行会得到相同名次，正好满足你"并列算同一名"的要求。
$B$2:$B$9 用绝对引用锁定比较范围，下拉时不会错位。
公式下拉到 C9，并列的部分确实对了，周敏和刘洋都是第 2 名。但接着往下看，郑凯 17000 排在第 4，整列里没有第 3 名。提问里专门举过“两人并列第 2，下一个人是第 3”的例子，AI 在回复里只接了“并列算同一名”半句，跳号的另半句被无视了。把实测结果发回去追问。
text
我下拉后周敏和刘洋并列第2没问题，但郑凯是第4，整列没有
第3名。我在问题里明确要求过不跳号，并列第2之后下一名
应该是第3。RANK.EQ能做到吗？
修正回复与替代公式
AI 的修正回复如下。
text
抱歉，我漏掉了你不跳号的要求。RANK.EQ 做不到这一点：
它的名次定义是"比我大的数值有几个，再加1"。两人并列第2时，
比第四个人大的数有3个，所以他直接是第4。这是函数的固有
规则，没有参数可以改。
不跳号的名次（也叫中国式排名）需要自己构造，在 D2 输入
并下拉到 D9：
=SUMPRODUCT(($B$2:$B$9>B2)/COUNTIF($B$2:$B$9,$B$2:$B$9))+1
思路是数"比我大的不重复数值有几个"再加1：
* ($B$2:$B$9>B2) 逐行判断谁比当前销售额大
* 除以 COUNTIF($B$2:$B$9,$B$2:$B$9)，把同一个数值出现的
多行折算成1个，19400出现两次，每次各算二分之一
* SUMPRODUCT 把折算结果加总，得到"压在我前面的名次个数"
两套名次可以并存：对外评奖常用 RANK.EQ 的跳号口径，
名次总数等于人数；内部通报常用不跳号口径，名次连续。
用哪套取决于制度规定，公式本身没有对错。
两版名次的并排验证
按修正回复把 SUMPRODUCT 公式填进 D 列，与 C 列的 RANK.EQ 结果并排对照，八个人的两版名次如下。
两列从郑凯开始逐行错开一位，错位的源头就是那对 19400。C 列的最大名次是 8，等于人数；D 列的最大名次是 7，等于不重复销售额的个数。这两个特征可以当作快速自检，拿到一列名次先看最大值，就知道它是哪种口径。
提示词里写了的要求不等于 AI 一定照办，本节的第一版回复就把“不跳号”四个字整个丢掉了。要求越具体，越要在结果里逐条回查，尤其是那种举了例子的要求，直接拿例子里的数字去结果里找，找不到就是没做到。
三层嵌套公式的逐层拆解
最后一个案例反过来，不让 AI 写公式，让它读公式。同事休产假，她的区域考核表交接了过来，“区域考核区”工作表 B2 里躺着一条长公式，往下填充到 B4，输出达标、良好这类字样。接手的人不敢乱动，因为看不懂它在算什么，这正是适合发给 AI 的场面，公式原文就是最完整的提问材料。
公式原文与拆解请求
提问附上公式原文和所在位置，并且把真实目的说出来，之后要改考核阈值，需要知道改哪里、注意什么。
text
我接手了同事的一张Excel考核表，下面这条公式在区域考核区
工作表的B2，A列是区域名。我看不懂它在算什么，请从最里层
开始逐层解释，并告诉我以后调整50000和40000这两个数时
要注意什么：
=IF(SUMIFS(销售明细!$F$2:$F$31,销售明细!$C$2:$C$31,A2)>=50000,"达标",
IF(SUMIFS(销售明细!$F$2:$F$31,销售明细!$C$2:$C$31,A2)>=40000,"良好","需改进"))
AI 的回复如下。
text
这条公式共三层，从最里层往外读：
第1层 SUMIFS(销售明细!$F$2:$F$31,销售明细!$C$2:$C$31,A2)
跨表到"销售明细"工作表，把C列区域等于A2（当前行的区域名）
的所有F列金额加起来，得到该区域的销售额合计。
第2层 IF(合计>=50000,"达标",…)
合计达到50000，返回"达标"，判断到此结束，不再往里走。
第3层 IF(合计>=40000,"良好","需改进")
只有没达到50000的区域才会走进这一层：达到40000返回"良好"，
否则返回"需改进"。
整条公式翻译成一句话：区域销售额50000及以上为达标，
40000到50000之间为良好，不足40000需改进。
调整阈值时注意两点：
1. 两层IF里各有一条一模一样的SUMIFS，是同一个数算了两遍。
改条件时两处要保持一致，只改一处不会报错，但判断逻辑
会悄悄乱掉
2. 嵌套IF的判断顺序必须从大到小。如果把40000那层挪到外面，
一个53700的区域会先命中">=40000"返回"良好"，
永远轮不到"达标"那一层
你的Office LTSC 2024支持LET函数，可以把SUMIFS的结果存成
变量，只算一次也只改一处：
=LET(s,SUMIFS(销售明细!$F$2:$F$31,销售明细!$C$2:$C$31,A2),
IF(s>=50000,"达标",IF(s>=40000,"良好","需改进")))
拆解结论的抽样对账
AI 的解释是否可靠，用底账里的三个区域合计逐一对。
先验第 1 层。在空白格输入 =SUMIFS(销售明细!$F$2:$F$31,销售明细!$C$2:$C$31,A2)，公式末尾分别改成A2、A3、A4，三行分别返回 53700、31800、42300，与底账一致，三个数相加是 127800，正好等于全表合计，第 1 层的口径确认无误。
再验第 2、3 层。B 列现有输出是华东达标、华北需改进、华南良好，把三个合计对着阈值人工走一遍，53700 过 50000 线、31800 不足 40000、42300 落在两线之间，三行全对。
最后验证 AI 提醒的顺序问题。在空白列故意把两层 IF 对调，写成先判 >=40000 的版本，华东立刻从达标变成良好，53700 被外层的 40000 线截胡，AI 说的“永远轮不到达标”当场复现，验完删掉这列。
顺手把回复末尾的 LET 版本也粘进空白列试了一遍，三行输出与原公式完全相同，而 SUMIFS 只出现一次，以后调阈值不存在改漏的问题。原公式先不动，等下个考核周期调阈值时再换成 LET 版本。
读别人的嵌套公式，固定从最里层的函数往外剥，每剥一层就用一行真实数据把这一层的输出手算出来。让 AI 拆解时把这个要求写进提示词，“从最里层开始逐层解释”，回复的结构就会和验证的次序天然对齐。
本章提示词模板
五个案例的提问归成两类，写公式和读公式，各自的必备信息与对账动作汇总如下。
两类问题的空白模板如下，方括号内容替换成实际情况。
text
【让AI写公式】
我在用Office LTSC专业增强版2024。有一张[表名]，结构如下：
[列号] [列名]（[类型]，[取值说明，没有可不写]）
数据在第[起]行到第[止]行。示例行：
[原样粘贴一到两行数据]
请写一个公式，[统计口径；多个条件之间是且还是或；
临界值算不算在内；有特殊行时说明如何处理]，
结果放在[单元格位置]。
【让AI读公式】
我接手了一张表，下面这条公式在[工作表名]的[单元格位置]，
[相关列的含义]。我看不懂它在算什么，请从最里层开始
逐层解释，并告诉我以后调整[要改的部分]时要注意什么：
[公式原文，原样粘贴不要改写]
写公式模板里方括号最多的一格是统计口径，本章一半的坑都埋在这一格里，大于还是大于等于、零值算不算、并列跳不跳号，全是口径问题。口径在提问时写死，AI 的公式和底账才有对得上的前提。