第四章的统计公式错了，得数和底账对不上，错得有声响。查找类公式的错法不一样，它的工作是从另一张表里搬数，搬错了行，单元格里坐着的仍然是一个像模像样的部门名或姓名，不报错、不变色，肉眼看不出破绽。所以本章的验证再换一种做法，叫“回源抽查”，公式填完不急着收工，挑几行拿着结果回源表找原件，逐格比对。五个案例都发生在同一份补贴核算工作簿上，提示词与 AI 回复照旧原文照录。第一个案例就是一次典型的查找翻车，三行 #N/A 把工作拦在半路，AI 给出的五种嫌疑要逐一排除。
薪酬专员月底核算岗位补贴。补贴明细由各部门报上来，表里只有工号、姓名和金额，没有部门归属，部门信息躺在人事系统导出的员工信息表里，第一件事就是把部门跨表取过来。取数的路上会遇到 #N/A，修完还要顺势想清楚这个每月重复的动作该用哪个函数。接下来是驻场外包名单，每月由供应商发来一份，人数没变、人换了几个，要找出增减。然后保安队拿着几个工号来查姓名，他们的门禁表里工号偏偏排在姓名右边。最后按部门分别报补贴合计，要在筛选状态下求和，这一步埋着本章第二个错误案例。
练习文件有五个工作表。“员工信息区”是全部查找动作的源表，A 到 D 列是工号、姓名、部门、基本工资，数据在第 2 行到第 16 行，其中三个工号被故意做了手脚，这正是第一个案例的病灶。“补贴发放区”是各部门报来的明细，A 到 C 列是工号、姓名、补贴金额，同样是十五人。“上月名单”与“本月名单”各有十二个外包人员姓名，数据在第 2 行到第 13 行。“门禁登记区”按门禁系统的固定列序排布，A 列姓名、B 列部门、C 列工号，E 列放着保安队发来的五个待查工号。人名、工号全部虚构。
模板下载：第六章查找核对练习文件
本章的回源抽查固定三个动作，每个查找类公式执行完都走一遍。
挑行。首行、末行各挑一行，再特意挑一行长相特殊的，比如两个字的姓名、被修复过的行、金额最大的行，查找公式在特殊行上最容易露馅。
回源。拿这几行的查找结果值，按 Ctrl+F 回源表定位到原件所在的行。
比对。看结果值与源表原值是否逐字一致，再看这一行的键（工号或姓名）在两张表里指向的是不是同一个人。
查找公式的危险恰恰在于它很少算错，它只会搬错。#N/A 是运气好的那种错，自己会喊；把甲的部门安到乙头上的错一声不吭，能把它揪出来的只有回源抽查。
第一项任务在“补贴发放区”工作表，要把部门从“员工信息区”按工号取过来。D2 输入 =VLOOKUP(A2,员工信息区!$A$2:$D$16,3,FALSE)，下拉到 D16。十五行里十二行正常返回部门，宋砚青、温以宁、祁言三行返回 #N/A。切到员工信息区翻一遍，三个人都在表里躺着，工号一个字符一个字符地看也没差别。眼睛说在，公式说找不到，这就是本案例的错误现场。
提问把两张表的结构、公式原文、错误行的分布规律一次写全，尤其是“这三个人在源表里能看到”这个矛盾点，它是缩小嫌疑范围的关键证词。
我在用Office LTSC专业增强版2024。工作簿里有两张表。
员工信息区工作表：A列工号（文本）、B列姓名（文本）、
C列部门（文本）、D列基本工资（数值），数据在第2行到第16行。
补贴发放区工作表：A列工号（文本）、B列姓名（文本）、
C列补贴金额（数值），数据也在第2行到第16行。
我在补贴发放区D2输入了下面的公式并下拉到D16：
=VLOOKUP(A2,员工信息区!$A$2:$D$16,3,FALSE)
15行里12行正常返回部门，宋砚青、温以宁、祁言3行返回#N/A，
但这3个人在员工信息区里都能看到。可能是什么原因，怎么逐条排查？
AI 的回复如下。
#N/A 的含义是"精确匹配模式下没有找到查找值"。人眼能看到
不等于公式能匹配到，常见原因有五种：
1. 查找值在源表确实不存在（拼写差一个字符、全角半角不同）
2. 两边数据类型不一致（一边是文本型数字，一边是数值）
3. 任意一边的工号带有不可见字符（空格、换行符、不间断空格）
4. 查找区域没加锁定，下拉后区域滑出了数据范围
5. 查找列不在区域的第一列，VLOOKUP 只在区域第一列里找
逐条排查，先排除便宜的：
1. 你的区域写的是 $A$2:$D$16，行列都锁定了，原因4排除
2. 工号列A就是区域第一列，原因5排除
3. 在员工信息区按 Ctrl+F 查找 GH1003（宋砚青的工号），先不勾
任何选项找一次，再勾上"单元格匹配"找一次。不勾能找到、
勾了找不到，说明单元格内容和 GH1003 不完全相等，多了
看不见的东西
4. 用 =ISTEXT(补贴发放区!A4) 和 =ISTEXT(员工信息区!A4)
检查类型，两边都返回 TRUE 才能排除原因2
5. 用 =LEN(补贴发放区!A4) 和 =LEN(员工信息区!A4) 对比字符数，
两个数字不相等就是原因3，多出来的就是不可见字符
前两项 AI 隔着屏幕直接排除了，剩下三项按它给的次序动手。
Ctrl+F 实验。在员工信息区查找 GH1003，不勾选项能找到，落在 A4；点开“选项”勾上“单元格匹配”再找，弹出“找不到正在搜索的数据”。两次结果不同，A4 的内容确定和 GH1003 不完全相等。
类型检查。空白格里 =ISTEXT(补贴发放区!A4) 和 =ISTEXT(员工信息区!A4) 都返回 TRUE，两边都是文本，原因二排除。
字符数对比。=LEN(补贴发放区!A4) 返回 6，=LEN(员工信息区!A4) 返回 7。GH1003 本身六个字符，员工信息区多出一个。点开 A4，在编辑栏里按一下 End 键，光标停在 3 的后面还空着一格，尾部空格现形。用同样的办法看温以宁、祁言的工号，LEN 也都是 7。
三行 #N/A 的病因收敛到同一个，员工信息区的工号列有三个单元格结尾多一个空格。这张表是人事系统导出的，导出文件带尾部空格是常见病，眼睛在这种病上是瞎的，LEN 不是。
病因确定，追问清除办法。
找到了，员工信息区的工号列有3个单元格结尾多一个空格，
LEN是7不是6。怎么把它们批量清掉？
AI 的回复如下。
三种办法按场景选：
1. 查找替换：选中员工信息区A2:A16，Ctrl+H，"查找内容"里
敲一个空格，"替换为"留空，点"全部替换"。工号内部不含
空格时这是最快的，你的表会提示"完成3处替换"
2. TRIM辅助列：空白列输入 =TRIM(A2) 下拉，再把结果按值
粘贴回A列。TRIM只清首尾空格不动中间，适合内容里本来
就该有空格的列（比如英文姓名）
3. 如果替换和TRIM之后LEN还是7，多出来的可能是从网页或
系统带来的不间断空格（字符编码160），普通空格匹配不到
它，先用 =SUBSTITUTE(A2,CHAR(160)," ") 把它转成普通
空格再按第1种处理
清完源表就行，补贴发放区的3个#N/A不用动，公式会自动重算。
工号内部没有空格，走第一种。选中 A2:A16，Ctrl+H，查找内容敲一个空格，替换为留空，全部替换，弹出“全部完成。完成 3 处替换”，与 AI 预告的数字一致。切回补贴发放区，三个 #N/A 已经自己换成了运营部、客服部、运营部。
替换前多看一眼对话框：如果点了“全部替换”却弹出“找不到任何要替换的内容”，先展开“选项”，检查“单元格匹配”有没有被勾上——勾上它意味着整个单元格的内容要恰好等于一个空格才算匹配，“工号 + 末尾空格”这种自然一个都找不到。这个勾选是 Excel 记忆的，上次谁勾过就一直留着。去掉勾还找不到，才轮到 AI 说的第三种情况：不间断空格，按 SUBSTITUTE 那条路走。
修复过的公式列要走回源抽查。抽宋砚青（修复行）、陆知行（首行）、苏晚晴（末行）三行，拿 D 列取回的部门回员工信息区 Ctrl+F 定位各自工号，逐格比对，三行全部一致。再补一个整列复核，空白格输入 =SUMPRODUCT(--ISNA(D2:D16)) 数整列还剩几个 #N/A，返回 0，十五行全部落地。
#N/A 集中出现在“源表里明明看得见”的行上时，第一嫌疑就是不可见字符。LEN 是最便宜的测谎仪，两边各数一次字符数，数字不等，眼睛就输给了空格。
#N/A 修完，公式能用了，但跨表取数这个动作以后每个月都要重复。VLOOKUP 是十几年前学的，隐约记得新版本里有更好用的替代品，趁着对话还热，把选型问题抛给 AI，让它把几个候选摆在一起说清楚。
还是上面这两张表的查找需求，用工号从员工信息区把部门取到
补贴发放区。除了VLOOKUP，我知道XLOOKUP和INDEX+MATCH也能
做查找。这三种写法各自的优点和限制是什么，我的Office LTSC
专业增强版2024该用哪个？
AI 的回复如下。
三个方案都能完成"用工号查部门"，差别在约束条件和出错概率上：
方案一 VLOOKUP
=VLOOKUP(A2,员工信息区!$A$2:$D$16,3,FALSE)
优点：所有版本都能运行，会的人最多
限制：查找列必须在区域最左边；第三参数是写死的列序号，
源表插入或删除一列后返回值会整体错位；第四参数漏写FALSE时
按近似匹配运行，在未排序的表上会返回不可预测的结果
方案二 INDEX+MATCH
=INDEX(员工信息区!$C$2:$C$16,MATCH(A2,员工信息区!$A$2:$A$16,0))
优点：所有版本都能运行；查找列位置不受限制；返回列单独
指定，源表插入列不影响结果
限制：两个函数套着写，公式长，新手读起来吃力
方案三 XLOOKUP
=XLOOKUP(A2,员工信息区!$A$2:$A$16,员工信息区!$C$2:$C$16,"未找到")
优点：默认精确匹配，不存在漏写FALSE的隐患；查找列和返回列
各自独立指定，方向不限；第四参数直接定义找不到时显示什么，
不用再在外面套IFERROR
限制：Excel 2019及更早版本没有这个函数，文件发给旧版本
用户打开，公式所在单元格会显示 #NAME? 错误
你的Office LTSC 2024支持XLOOKUP。文件只在你自己和同版本
同事之间流转的话，建议直接用方案三。
回复里三个方案的关键差异抽成一张表，选型时对着查。
三行看下来，XLOOKUP 的优势不在功能多，在于能悄悄出错的地方少。VLOOKUP 最常见的两种翻车，第四参数漏写和源表插列后错位，在 XLOOKUP 的参数设计里根本不存在。
E2 输入 =XLOOKUP(A2,员工信息区!$A$2:$A$16,员工信息区!$C$2:$C$16,"未找到")，下拉到 E16，与 D 列的 VLOOKUP 结果十五行逐行比对，完全一致。回源抽查照走，抽孟繁星、盛夏、鲁星河三行，部门值回员工信息区定位核对，全部对上。
第四参数值得单独实测。在 A17 临时敲一个不存在的工号 GH1099，把 D、E 两列公式各下拉一行，D17 的 VLOOKUP 返回 #N/A，E17 的 XLOOKUP 返回“未找到”三个字。同一件事的两种告知方式，交给不认识错误值的同事看表时，后者不会引来一句“你这表是不是坏了”。验完删掉第 17 行。
XLOOKUP 默认精确匹配这一条就值回票价，VLOOKUP 的翻车有一半出在第四参数漏写 FALSE 上。旧文件里的 VLOOKUP 和 INDEX+MATCH 不必翻新，能看懂就行，新写的公式直接上 XLOOKUP。
第三项任务换到外包名单。驻场外包客服的名单每月由供应商发来一份，“上月名单”十二人，“本月名单”十二人，总人数没变，供应商只说“有几个人调整了”。谁走了谁来了，结算工时前必须点清楚。十二行的表眼睛还能硬对，供应商的另一个项目名单有一百多行，得有一个不靠眼睛的办法。
我在用Office LTSC专业增强版2024。两张工作表各有一列姓名：
上月名单工作表A列是上个月的12个外包人员，本月名单工作表
A列是这个月的12个，数据都在第2行到第13行。两边人数一样
但人员有变动，我要找出本月新增了谁、减少了谁，怎么做？
AI 的回复如下。
用 COUNTIF 互相标记，两边各做一次：
第一步 找新增。在本月名单B2输入并下拉到B13：
=COUNTIF(上月名单!$A$2:$A$13,A2)
公式数的是"当前这个名字在上月名单里出现几次"：
* 返回0 → 上月没有这个人，是本月新增
* 返回1 → 两个月都在
第二步 找减少。在上月名单B2输入并下拉到B13：
=COUNTIF(本月名单!$A$2:$A$13,A2)
* 返回0 → 本月已经没有这个人，是减少
第三步 分别筛选两边B列等于0的行，就是新增名单和减少名单。
一个前提要先确认：名单里不能有重名。哪边的B列出现2这样的
数字，说明那个名字有两个人在用，COUNTIF按名字计数会把
他们混在一起，这种名单应当改用工号做键再核对。
两条公式分别进两张表的 B 列。本月名单里 B 列为 0 的有三行，韩江雪、秦朗、尤佳，这是新增；上月名单里 B 列为 0 的也是三行，张灵均、孙启帆、陈墨，这是减少。其余行全是 1，没有出现 2，重名的前提在这份名单上成立。
名单核对的回源抽查是一道算术，两边的 0 数完必须过一遍对平等式，上月人数减去减少数、加上新增数，要正好等于本月人数。这份名单是 12 − 3 + 3 = 12，等式成立，六个人的名单可以交给供应商确认。等式不成立就先别交，回头查两件事，一是筛选时有没有漏行，二是同一个人的姓名在两边的写法是否一致。COUNTIF 的匹配和 VLOOKUP 一样认字符不认人，姓名尾部多一个空格，就会同时制造一个假新增和一个假减少，人数对平照样成立，所以拿到来路不明的名单，核对前先按上一个案例的办法用 LEN 抽查几行。
标记完成后必须做人数对平，上月人数减去标 0 的减少数、加上对面标 0 的新增数，正好等于本月人数才算核对完成。等式不成立，先怀疑两边姓名写法不一致，再怀疑重名。
第四项任务来自保安队。“门禁登记区”工作表是门禁系统导出的，列序固定改不了，A 列姓名、B 列部门、C 列工号，数据在第 2 行到第 16 行。保安队发来五个工号放在 E2 到 E6，要在 F 列查出对应的姓名。按老习惯提笔就是 VLOOKUP，写到一半停住了，VLOOKUP 拿工号当查找值，就要求工号在区域最左列，可这张表里姓名在工号的左边，取数的方向反了。
把列序改不了这个约束原样写进提示词，它决定了 AI 能给什么方案。
门禁登记区工作表是门禁系统导出的，列的顺序固定不能改：
A列姓名、B列部门、C列工号，数据在第2行到第16行。现在
E2到E6有5个待查工号，要在F列查出对应的姓名。工号在姓名
的右边，VLOOKUP好像只能从左往右查，这种情况怎么办？
AI 的回复如下。
你的判断是对的，VLOOKUP 要求查找列在区域最左、然后向右
取数，"从右往左"它做不到（不借助数组改造的话）。两个现成
的替代方案，你的2024版本都支持：
方案一 XLOOKUP，在F2输入并下拉到F6：
=XLOOKUP(E2,$C$2:$C$16,$A$2:$A$16)
查找列和返回列是两个独立参数，谁左谁右无所谓。三个参数
按顺序念出来就是需求本身：拿E2，去C列找，从A列回。
方案二 INDEX+MATCH，在G2输入并下拉到G6：
=INDEX($A$2:$A$16,MATCH(E2,$C$2:$C$16,0))
MATCH先在C列里查出E2排在第几行，INDEX再从A列取出同一行
的值。第三参数0表示精确匹配，不能省。
两版结果完全一样。XLOOKUP可读性好；INDEX+MATCH的价值在于
所有版本都能运行，而且大量旧文件里用的都是它，接手别人
的表时要能读懂。
两版并排落地，F 列 XLOOKUP、G 列 INDEX+MATCH，各自下拉到第 6 行，五行结果逐行相同，依次是贺兰亭、柏文轩、颜子越、苏晚晴、路遥。回源抽查抽两行，GH1015 回 C 列 Ctrl+F 定位到第 16 行，同行 A16 是苏晚晴，对上；GH1006 定位到第 7 行，A7 是路遥，两个字的姓名也完好无损。
可读性的差别拿人试了一次。把两条公式念给旁边不用函数的同事听，XLOOKUP 三个参数一口气念完，对方能复述出“用工号在 C 列找、从 A 列拿姓名”；INDEX+MATCH 要先解释 MATCH 返回的是行号，再解释 INDEX 拿行号去取值，理解顺序和书写顺序相反，念到一半对方眼神就散了。公式是写一次读多年的东西，半年后回来改这张表的多半是自己，可读性省下的是未来的时间。
反向查找在 2024 里不必再背数组套路，XLOOKUP 一条平铺直叙的公式就够。INDEX+MATCH 不用主动写，但必须能看懂，旧表里到处是它。
最后一项任务是按部门分别报补贴合计。部门列在前面的选型案例里已经取进了补贴发放区的 D 列，做法看起来很顺手，筛选 D 列等于客服部，屏幕上只剩四行，在 C18 输入 =SUM(C2:C16) 回车，得数 7700。可是选中屏幕上这四个补贴金额，状态栏的求和显示 1250，客服部四个人 300、300、350、300 用心算加也是 1250。同一批数字，公式和状态栏各执一词，差出 6450，这个数不查清楚，报表交不出去。
接着用补贴发放区工作表，D列部门已经用公式从员工信息区
取过来了。我筛选了D列等于客服部，屏幕上只剩4行，然后在
C18输入=SUM(C2:C16)，结果是7700。但我选中这4行的补贴
金额，状态栏显示求和是1250，手工加也是1250。为什么SUM
把筛选掉的行也加进去了，公式该怎么写？
AI 的回复如下。
SUM 的统计范围是"区域里的所有行"。筛选只是把不满足条件
的行隐藏了，行还在区域里，所以7700是全部15个人的合计。
状态栏只统计当前选中的可见单元格，所以它是1250。
要让公式只加筛选后可见的行，改用 SUBTOTAL，在C18输入：
=SUBTOTAL(9,C2:C16)
* 第一参数9代表求和。SUBTOTAL用编号选统计方式，9=SUM、
1=AVERAGE、2=COUNT、4=MAX，编号在输入时会有下拉提示
* 它自动跳过被筛选隐藏的行。筛选条件一变，结果跟着变，
取消筛选就回到全表合计
一个容易踩的区别：第一参数还有一套三位数编号（109、101
等），统计功能相同，差别在对"右键手动隐藏"的行的处理上。
9会把手动隐藏的行照常算进去，109会把它们也排除。纯筛选
场景两套编号结果一样，表里有手动隐藏行时要想清楚用哪套。
验证要在筛选状态下做。先确认 D 列的筛选还停在客服部，屏幕上只剩孟繁星、许清嘉、温以宁、庄若彤四行；如果中途取消过筛选，先点 D1 的筛选按钮重新勾选客服部。然后把 C18 改成 =SUBTOTAL(9,C2:C16)，回车返回 1250，公式、状态栏、心算三方会师。不改公式，把筛选换成技术部，C18 自动变成 3200，正好是四笔 800 的和；取消筛选，回到 7700。同一个单元格在三种筛选状态下给出三个数，SUBTOTAL 跟着可见行走，这正是“按部门分别报数”要的性质，换一次筛选抄一个数，公式一个字不用动。
回复末尾 9 与 109 的区别也当场做实。取消筛选，右键隐藏贺兰亭那一行，C18 的 SUBTOTAL(9,...) 纹丝不动还是 7700，在 C19 临时输入 =SUBTOTAL(109,C2:C16)，返回 6900，差的正是贺兰亭那笔 800。筛选隐藏和手动隐藏在屏幕上长得一样，在这两个编号眼里是两回事。验完取消隐藏，删掉 C19。
筛选状态下交出去的合计数，公式必须是 SUBTOTAL 而不是 SUM。自检办法固定一条，选中可见的金额单元格看状态栏，状态栏求和与公式得数不一致，这个数就不能报出去。
五个案例的提问要点与各自的回源抽查动作汇总如下，查找类公式的结果来自另一张表，抽查也都要回到那张表去。
两类场景的空白模板如下，方括号内容替换成实际情况。
【让AI排查查找类错误】
我在用Office LTSC专业增强版2024。工作簿里有两张表：
[源表名]：[列号] [列名]（[类型]），数据在第[起]行到第[止]行
[结果表名]：[列结构写法同上]
我在[单元格]输入了下面的公式并下拉到第[止]行：
[公式原文，原样粘贴不要改写]
[多少]行正常，[哪些行]返回[错误值]，但这些行的[键]在源表里
都能看到。可能是什么原因，怎么逐条排查？
【让AI做跨表查找】
我在用Office LTSC专业增强版2024。要用[键列]从[源表名]把
[要取的列]取到[结果表名]的[单元格]。[键列]在[哪一列]，
[返回列]在[哪一列]，[列序能不能改；文件要不要兼容旧版本]。
找不到对应值时希望显示[提示语]。请给出公式并说明找不到
时的表现。