上一章把订单表洗干净了,但洗干净不等于能分析。订单表里只有用户ID,没有城市、没有会员等级;
要回答“哪个城市卖得最好”“金卡用户贡献了多少”,你得先把用户表的信息接到订单表上。这一步叫查找。
接完之后还有第二步:按城市把金额加起来。听起来就是一行 SUMIFS,但这份 120 行的订单里偏偏有
13 行查不到用户、2 个用户ID重复,正好把 VLOOKUP 的五个限制全部演一遍。
本章用六成篇幅讲清“为什么”,四成时间带你动手。原理没吃透,你只会得到一列看着像城市、实际错位的文本。
查找函数的报错,九成不是函数名拼错,而是两件事:引用没锁对、区域没对齐。 所以这一节先讲引用,再讲 VLOOKUP,最后讲 SUMIFS 和 COUNTIFS。顺序不能反——前一个是后一个的地基。
在 Excel 里,A1 不是固定格子,而是偏移量:从当前单元格往左一列、往上一行。
你在 B2 写 =A1,Excel 记的是“左边一格”,不是“A1 的值”。所以拖到 C2 变
=B1,拖到 B3 变 =A2。
$ 把这个偏移量钉死:$A$1 行列都锁,A$1 只锁行,
$A1 只锁列。选中引用按 F4,可在四种写法间循环。
| B2 里的公式 | 向右拖到 C2 | 向下拖到 B3 | 锁住了什么 |
|---|---|---|---|
=A1 | =B1 | =A2 | 什么都不锁 |
=$A$1 | =$A$1 | =$A$1 | 行和列都锁 |
=A$1 | =B$1 | =A$1 | 只锁行 |
=$A1 | =$A1 | =$A2 | 只锁列 |
套到 VLOOKUP 上,事故就出现了。F2 写
=VLOOKUP(B2, 用户表!A2:E63, 3, FALSE),往下拖一行,B2 变 B3(正确),
A2:E63 也变成 A3:E64(错误)。拖到 F121,第二参数滑成
A121:E182;用户表只有 63 行,结果整列 #N/A。更隐蔽的是滑动后的区域里
恰好有数据:VLOOKUP 不报错,返回一个看起来合理的值。
正确写法是 用户表!$A$2:$E$63,或把区域命名成“用户信息”,公式写成
=VLOOKUP($B2, 用户信息, 3, FALSE)。名称短,但交接时要说明它指哪片区域。
下拉后 用户表!A2:E63 变 用户表!A121:E182,整列 #N/A。
用户表!$A$2:$E$63 下拉 119 行,仍是原区域。
$ 锁住的是坐标,不是数值。该锁不锁,公式会“看起来正常地”算错。
如果不这样会怎样:把 $A$2:$E$63 写成 A2:E63,拖到第 121 行时,
区域滑成 A121:E182;62 行的用户表根本覆盖不到,整列变成 #N/A。
VLOOKUP 的语法是 =VLOOKUP(查找值, 表格区域, 返回第几列, 精确还是近似)。
它的执行过程固定为五步:
col_index_num 列。#N/A。| 行 | A 用户ID | B 注册日期 | C 城市 | D 来源渠道 | E 会员等级 |
|---|---|---|---|---|---|
| 列序号 | 1 | 2 | 3 | 4 | 5 |
| 42 | U041 | 2024-04-16 | 北京 | 朋友推荐 | 金卡 |
| 43 | U042 | 2024-05-05 | 深圳 | 朋友推荐 | 普通 |
| 44 | U043 | 2024-05-06 | 杭州 | 朋友推荐 | 普通 |
| 45 | U044 | 2024-04-15 | 杭州 | 自然搜索 | 黑卡 |
| 46 | U045 | 2024-04-03 | 深圳 | 信息流 | 普通 |
注意图 7.1.2 的“列序号”:列号从表格区域左边缘数,不是工作表列号。
区域写成 B2:F63,写 3 返回的是 D 列。第四参数写 FALSE 或
0 是精确匹配;写 TRUE 或省略是近似匹配——首列没排序时,
它返回哪一行不可预测,还不报错。
| 限制 | 具体表现 | 反例(本章数据) |
|---|---|---|
| 只能右查 | 返回值必须在查找列的右边;往左查不了。 | ID 在 A、城市在 C,想用城市反查 ID 做不到;只能复制 ID 到城市右边,或换 XLOOKUP。 |
| 插入列会错 | 第 3 个参数是写死的数字,不会随列的位置自动变。 | B 列后插入“手机号”,城市从第 3 列变第 4 列;公式还取 3,整列变成注册日期,不报错。 |
| 大数据慢 | 精确匹配逐行扫描,找不到就扫到底。 | 12 万行订单查 6 万行用户,最坏比较 72 亿次。慢在算法,不在电脑。 |
| 不区分大小写 | u012 能匹配 U012。 |
如果 a01 和 A01 是两个不同客户,VLOOKUP 会把它们当成同一个,返回第一条。 |
| 只返回第一个匹配 | 重复查找值只取从上往下第一条。 | U012 在第 13 行(成都)和第 62 行(上海),永远返回成都;U027 同理。3 笔订单因此记到第一条记录的城市。 |
四个参数,少对一个就出错。尤其记住:列号从区域左边缘数,第四参数必须显式写 0。
如果不这样会怎样:省略第四参数,查 U012 会走近似匹配;首列没排序时,返回哪一行不可预测, 还不报错,你可能拿到 U011 的城市还以为查的是 U012。
VLOOKUP 的五个限制里,前两个最要命,因为它们不报错。新版本 Excel 给的解法是 XLOOKUP:
=XLOOKUP($B2, 用户表!$A$2:$A$63, 用户表!$C$2:$C$63, "未建档")
参数是:查找值、查找区域、返回区域、找不到时显示什么。默认精确匹配,不用写 FALSE;
两个区域可以左右任意摆放,“只能右查”不存在了。返回区域写多列,还能一次取回城市、等级、渠道。
第六个参数 search_mode 写 -1 会从下往上找,U012 返回上海。
但哪条对得问业务,不能让函数替你决定。
旧版本 Excel 的解法是 INDEX+MATCH:
=INDEX(用户表!$C$2:$C$63, MATCH($B2, 用户表!$A$2:$A$63, 0))
MATCH 找位置:U043 在 A2:A63 排第 43 位。INDEX 取第 43 个:
C2:C63 的第 43 个是杭州。它不依赖“第几列”,插入列不会错;但两个区域必须
同起点、同高度。
| 场景 | 用哪个 | 理由 |
|---|---|---|
| 自己用新版 Excel / Microsoft 365 | XLOOKUP | 短、默认精确、自带找不到处理、左右都能查。 |
| 要发给用 Excel 2016 的同事 | INDEX+MATCH | 全版本可用;XLOOKUP 在旧版直接 #NAME?。 |
| 一次返回城市、等级、渠道三列 | XLOOKUP | 返回区域写三列,一次溢出;INDEX+MATCH 要写三遍。 |
| 要取最后一个匹配(重复 ID) | XLOOKUP | search_mode=-1 从下往上找;INDEX+MATCH 要配合 LOOKUP,公式难读。 |
SUMIFS 的语法是 =SUMIFS(求和区域, 条件区域1, 条件1, ...)。求和区域在第一个
位置,和 SUMIF 相反。COUNTIFS 没有求和区域,只有成对的条件。
规则只有一条:所有区域必须同起点、同高度。SUMIFS 按行号配对,不是先筛选再求和。 第 1 行条件成立,就加求和区域第 1 行;区域错位,它只会把“杭州”配到下一行的金额上。
| 写法 | 条件区域 | 求和区域 | 结果 |
|---|---|---|---|
| 正确 | F2:F121(120 格) | D2:D121(120 格) | 逐行对齐,杭州 53,147.40 |
| 少一格 | F2:F121(120 格) | D2:D120(119 格) | #VALUE!,直接报错 |
| 整体下移 | F2:F121(120 格) | D3:D122(120 格) | 不报错,但每行错位,合计少 1,563.25 |
为什么少 1,563.25?D2 是第一笔金额,D3:D122 从第二笔开始加,正好漏掉它;
D122 是空行,补不回来。总额变成 215,494.12。
* 代表任意多个字符(包括 0 个),? 代表恰好一个字符:
"*发票*" 匹配含“发票”的备注,"U0??" 匹配 U0 开头的 4 位 ID。
要匹配真正的星号或问号,写成 ~*、~?。
比较运算符要加引号:">=1000"、"<>杭州";阈值在单元格里用
">"&K2。还有两点:COUNTIFS 的 "*" 只数文本,空单元格不算。
VLOOKUP、XLOOKUP、INDEX+MATCH 在遇到重复值时,都不会报错。它们按各自的方向取第一条或最后一条, 然后安静地返回。数据有没有重复,只能由你先查。
如果不这样会怎样:用户表 62 行里有 60 个唯一 ID,U012 和 U027 各出现两次。 3 笔订单合计 4,889.54 元会被固定算到第一条记录所在的城市;如果真实归属是另一条,城市汇总会整体偏移, 而总额一分不差,你很难发现。
SUMIFS 按行号配对,不按内容配对。两个区域都从第 2 行开始、都到第 121 行结束,才是一一对应。
如果不这样会怎样:条件写 F2:F121、求和写 D3:D122,两个区域都是 120 格,
Excel 不报错,但每行都错开一格,合计少掉第一笔 1,563.25 元。
#N/A 是 Not Available 的缩写。它不是“公式坏了”,而是“我在你给的区域第一列里,没找到这个值”。
看懂这句话,排查就有方向了:要么值真的不在,要么值在但长得不一样,要么你给的区域不对。
| 成因 | 典型特征 | 一分钟检查 | 修复 |
|---|---|---|---|
| 查找值不存在 | 只有部分行错,错误行集中在几个固定 ID。 | =COUNTIF(用户表!$A$2:$A$63,B2) 返回 0。 |
补用户档案;或用 IFNA 标“未建档”,订单不能删。 |
| 有空格 / 不可见字符 | 肉眼看完全一样,复制过去就能查到。 | =LEN(B2) 比正常多 1;=B2=TRIM(B2) 返回 FALSE。 |
TRIM / CLEAN;不间断空格用 SUBSTITUTE(B2,CHAR(160),"")。 |
| 类型不一致 | 一个靠左一个靠右;或 ID 有前导零。 | 两列各取一格做 =ISTEXT(),结果一真一假。 |
分列转数值;或两边统一用 TEXT / VALUE。 |
| 区域没锁定 | 第一条对,越往下错越多;或整列都错。 | 点错误单元格,看第二参数是否滑成 A121:E182;正确写法 $A$2:$E$63。 |
F4 加 $;或改用名称“用户信息”。 |
排查顺序很重要:先看错误分布,再看单个值。整列都错,先怀疑区域;只有几行错,先怀疑值和类型。
本章的 13 行属于第一种:U901 到 U905 在用户表里根本不存在,COUNTIF 返回 0,没有争议。
连 #REF!、#VALUE! 一起吞。公式写错也显示“未建档”,你以为是用户没建档。
只处理“找不到值”,其他错误继续报警。错误被看见,才有机会被修。
这次要交付一张能回答“各城市卖了多少”的表。路线只有两条:先用查找把城市接到订单上,
再用 SUMIFS 按城市汇总。中间会遇到 13 个 #N/A 和 2 个重复 ID,别绕开,它们是本章的主角。
#N/A 和城市偏移就全看不见了——那不是实验成功,是把问题藏起来了。
| 项目 | 要求 |
|---|---|
| 软件 | Microsoft Excel 2016 及以上,或 WPS 表格。XLOOKUP 需要 Excel 2021 / Microsoft 365。 |
| 设备 | 电脑。手机版做不了跨工作簿引用和填充柄双击。 |
| 准备 | 把两个文件放在同一个文件夹,分别打开;订单表另存为 订单分析.xlsx。 |
users.xlsx,在 F1 输入 =COUNTA(A2:A63) → 62=SUMPRODUCT(1/COUNTIF(A2:A63,A2:A63)) → 60=COUNTIF($A$2:$A$63,A2),双击填充柄到 G63用户表!A2:E63 起名“用户信息”。A121:E182,整列作废。users.xlsx 右键“用户表”标签 → 移动或复制 → 勾“建立副本” → 选订单分析.xlsx用户信息,引用位置填 =用户表!$A$2:$E$63 → 确定用户信息 回车,确认选中 62 行 × 5 列=VLOOKUP($B2,用户信息,3,FALSE)$B2 是查找值;用户信息 是区域;3 是城市;FALSE 是精确匹配=VLOOKUP($B2,用户信息,3,FALSE)。$B2 是混合引用:锁住 B 列,往右拖不会跑到 C 列;行号不锁,往下拖才会跟着变。列号 3、4、5 是常数,向右拖不会自动加 1,必须手动改。=VLOOKUP($B2,用户信息,4,FALSE)=VLOOKUP($B2,用户信息,5,FALSE)#N/A 的行数和这些行的金额合计。=SUMPRODUCT(--ISNA($F$2:$F$121))=SUMPRODUCT(--ISNA($F$2:$F$121),$D$2:$D$121)错误值不是文本,COUNTIF 对它的处理随版本而异,容易少数或报错。ISNA 判断“是不是 #N/A”,SUMPRODUCT 负责计数,稳定。
#N/A 显示为“未建档”,订单行原样保留。=IFNA(VLOOKUP($B2,用户信息,3,FALSE),"未建档")—#N/A 消失;D 列求和仍是 217,057.37,一分没少。IFERROR 连 #REF!、#VALUE! 一起吞。哪天你把区域写错,整列显示“未建档”,你还以为只是用户没建档。IFNA 只接 #N/A,其他错误继续报警。
F2:F121 与求和区域 D2:D121 必须同起点、同高度,这是最容易写错的一步。=SUMIFS($D$2:$D$121,$F$2:$F$121,K2)=COUNTIFS($F$2:$F$121,K2)#VALUE!:两个区域大小不一样,检查是不是写成了 D2:D120。
全是 0:K2 的城市名有空格,或 F 列是“未建档”而条件写错。用 =LEN(K2) 看长度。
合计对不上:求和区域整体下移了一行,回图 7.1.4 对照。
=SUM($D$2:$D$121) → 217,057.37=SUM($L$2:$L$7) → 196,932.56=SUMIFS($D$2:$D$121,$F$2:$F$121,"未建档") → 20,124.81=N2+N3-N1,必须是 0
匹配后的表应该长这样。注意第 17 行:U903 不存在,三列同时是 #N/A(第 6 步后会显示“未建档”)。
| 行 | A 订单号 | B 用户ID | C 下单日期 | D 金额 | F 城市 | G 会员等级 | H 来源渠道 |
|---|---|---|---|---|---|---|---|
| 2 | O2406001 | U023 | 2024-06-29 | 1,563.25 | 上海 | 银卡 | 线下 |
| 3 | O2406002 | U055 | 2024-06-19 | 1,891.99 | 上海 | 银卡 | 朋友推荐 |
| 4 | O2406003 | U053 | 2024-06-28 | 2,122.08 | 深圳 | 银卡 | 线下 |
| 5 | O2406004 | U012 | 2024-06-15 | 896.81 | 成都 | 普通 | 朋友推荐 |
| 6 | O2406005 | U052 | 2024-06-28 | 544.59 | 深圳 | 普通 | 线下 |
| 7 | O2406006 | U018 | 2024-06-25 | 1,497.22 | 北京 | 银卡 | 信息流 |
| 8 | O2406007 | U043 | 2024-06-13 | 3,141.36 | 杭州 | 普通 | 朋友推荐 |
| 17 | O2406016 | U903 | 2024-06-21 | 1,824.58 | #N/A | #N/A | #N/A |
城市汇总表应该长这样。和订单总额差的 20,124.81,正好是 13 行未建档订单;这不是误差,是口径。
| 城市 | 销售额 | 订单数 |
|---|---|---|
| 杭州 | 53,147.40 | 28 |
| 成都 | 37,047.80 | 17 |
| 深圳 | 34,693.63 | 19 |
| 广州 | 33,804.24 | 21 |
| 上海 | 24,274.45 | 13 |
| 北京 | 13,965.04 | 9 |
| 城市合计 | 196,932.56 | 107 |
| 未建档 | 20,124.81 | 13 |
| 订单总额 | 217,057.37 | 120 |
验收标准不是“看起来对”,是“三个数能对上”。先填四个数,再做五道题;全对说明查找和汇总两条链路都通了。
=VLOOKUP($B2,用户信息,3,FALSE) 从 F2 向右拖到 G2,第一个参数会变成什么?$B2 只锁列,不锁行;向右拖列被锁住,仍是 B。若写成 B2,向右拖变 C2,查到的是日期。=SUMIFS($D$2:$D$121,$F$2:$F$121,K2) 中,把求和区域改成 $D$3:$D$122,会发生什么?D3:D122 和 F2:F121 都是 120 格,大小相同,所以不报错;但整体下移一行,F2 的条件配到 D3 的金额,D2 的 1,563.25 被漏掉。大小不同才报 #VALUE!。u012,公式 =VLOOKUP(B5,用户信息,3,FALSE) 的结果是?u012 能匹配 U012,仍返回第一条成都。如果大小写有业务含义,用 EXACT 或辅助列。按“现象 → 原因 → 解决”整理。先查表,再回 7.1 找对应的规则。
| 现象 | 原因 | 解决 |
|---|---|---|
整列都显示 #N/A |
表格区域没锁定,下拉时整体滑走(规则 1) | 第二参数改成 $A$2:$E$63 或名称“用户信息” |
值明明存在,却返回 #N/A |
查找值带空格或不可见字符 | LEN 对比长度;TRIM / CLEAN / SUBSTITUTE(CHAR(160),"") |
返回的不是 #N/A,而是明显错的内容 |
用户表插过列,第三个参数还是旧列号 | 改列号,或换 XLOOKUP / INDEX+MATCH |
| 重复 ID 的城市总是同一条 | VLOOKUP 只返回第一个匹配(规则 3) | 先去重并确认哪条为准;要取最后一条用 XLOOKUP search_mode=-1 |
SUMIFS 返回 #VALUE! |
条件区域和求和区域大小不同(规则 4) | 两个区域都写成 2:121,同起点同高度 |
#N/A。#VALUE!,大小相同但错位则静默算错。=VLOOKUP(B2,用户表!$A$2:$E$63,3,FALSE) 写成 =VLOOKUP(B2,用户表!A2:E63,3,FALSE) 后下拉到第 121 行,F121 的第二参数是什么?从第几行开始会大面积出错?1. 下移 119 行,第二参数变成 A121:E182。用户表只有 63 行,从第 3 行起,U001、U002 这类排在前面的用户就开始查不到;越往下漏得越多,最终整列 #N/A。
2. 成都 37,047.80 − 896.81 = 36,150.99;上海 24,274.45 + 896.81 = 25,171.26。合计仍是 196,932.56,只是钱从成都挪到了上海。
3. 城市合计不变,仍是 196,932.56,因为这 13 行没有城市、不参与汇总;订单总额会降到 196,932.56,少 20,124.81。丢的不是钱,是“用户表缺了 5 个 ID”这条线索。
4. =SUMIFS($D$2:$D$121,$H$2:$H$121,"朋友推荐",$G$2:$G$121,"金卡"),结果是 17,965.98,共 8 笔。三个区域都从第 2 行到第 121 行,同起点同高度。