数据分析师训练营/ 第二篇 Excel/ 第七章
第七章 · 查找与聚合

查找与条件聚合

上一章把订单表洗干净了,但洗干净不等于能分析。订单表里只有用户ID,没有城市、没有会员等级; 要回答“哪个城市卖得最好”“金卡用户贡献了多少”,你得先把用户表的信息接到订单表上。这一步叫查找。

接完之后还有第二步:按城市把金额加起来。听起来就是一行 SUMIFS,但这份 120 行的订单里偏偏有 13 行查不到用户、2 个用户ID重复,正好把 VLOOKUP 的五个限制全部演一遍。 本章用六成篇幅讲清“为什么”,四成时间带你动手。原理没吃透,你只会得到一列看着像城市、实际错位的文本。

理论 : 实操 = 6 : 4 约 6 学时 前置 第 6 章

7.1原理:引用、查找与条件聚合

查找函数的报错,九成不是函数名拼错,而是两件事:引用没锁对、区域没对齐。 所以这一节先讲引用,再讲 VLOOKUP,最后讲 SUMIFS 和 COUNTIFS。顺序不能反——前一个是后一个的地基。

7.1.1引用的三种方式:相对、绝对、混合

在 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只锁列
图 7.1.1 $ 放在谁前面,谁就不动;拖公式出错,多半是这里少了一个 $

套到 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 行,仍是原区域。

1

拖公式时,Excel 搬的是偏移量,不是公式本身

$ 锁住的是坐标,不是数值。该锁不锁,公式会“看起来正常地”算错。

如果不这样会怎样:把 $A$2:$E$63 写成 A2:E63,拖到第 121 行时, 区域滑成 A121:E182;62 行的用户表根本覆盖不到,整列变成 #N/A。

7.1.2VLOOKUP 的工作机制与五个限制

VLOOKUP 的语法是 =VLOOKUP(查找值, 表格区域, 返回第几列, 精确还是近似)。 它的执行过程固定为五步:

  1. 拿查找值,去表格区域的第 1 列,从上往下逐行比较。
  2. 找到第一个相等的值,记住它在第几行。
  3. 在同一行,向右数到第 col_index_num 列。
  4. 把这个格子的值返回。
  5. 找不到,返回 #N/A。
行A 用户IDB 注册日期C 城市D 来源渠道E 会员等级
列序号12345
42U0412024-04-16北京朋友推荐金卡
43U0422024-05-05深圳朋友推荐普通
44U0432024-05-06杭州朋友推荐普通
45U0442024-04-15杭州自然搜索黑卡
46U0452024-04-03深圳信息流普通
图 7.1.2 在 A 列第 44 行找到 U043,向右数到第 3 列,返回“杭州”

注意图 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 笔订单因此记到第一条记录的城市。
2

VLOOKUP 是“在首列找、向右数第几列”,不是“智能匹配”

四个参数,少对一个就出错。尤其记住:列号从区域左边缘数,第四参数必须显式写 0。

如果不这样会怎样:省略第四参数,查 U012 会走近似匹配;首列没排序时,返回哪一行不可预测, 还不报错,你可能拿到 U011 的城市还以为查的是 U012。

7.1.3XLOOKUP 与 INDEX+MATCH:什么时候用哪个

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 个是杭州。它不依赖“第几列”,插入列不会错;但两个区域必须 同起点、同高度。

U043
→
MATCH = 43
→
INDEX 第 43 个
→
杭州
图 7.1.3 INDEX+MATCH 分两步:先找位置,再按位置取值
场景用哪个理由
自己用新版 Excel / Microsoft 365XLOOKUP短、默认精确、自带找不到处理、左右都能查。
要发给用 Excel 2016 的同事INDEX+MATCH全版本可用;XLOOKUP 在旧版直接 #NAME?。
一次返回城市、等级、渠道三列XLOOKUP返回区域写三列,一次溢出;INDEX+MATCH 要写三遍。
要取最后一个匹配(重复 ID)XLOOKUPsearch_mode=-1 从下往上找;INDEX+MATCH 要配合 LOOKUP,公式难读。
一句话选型:新环境优先 XLOOKUP,旧版用 INDEX+MATCH;VLOOKUP 要会读会改,但新表不必再写。 老文件里八成还是它。

7.1.4SUMIFS / COUNTIFS:区域对齐与通配符

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
图 7.1.4 区域大小不同会报错;大小相同但起点错位,Excel 静默算错

为什么少 1,563.25?D2 是第一笔金额,D3:D122 从第二笔开始加,正好漏掉它; D122 是空行,补不回来。总额变成 215,494.12。

* 代表任意多个字符(包括 0 个),? 代表恰好一个字符: "*发票*" 匹配含“发票”的备注,"U0??" 匹配 U0 开头的 4 位 ID。 要匹配真正的星号或问号,写成 ~*、~?。

比较运算符要加引号:">=1000"、"<>杭州";阈值在单元格里用 ">"&K2。还有两点:COUNTIFS 的 "*" 只数文本,空单元格不算。

3

查找值必须唯一,否则函数替你“挑”了一条

VLOOKUP、XLOOKUP、INDEX+MATCH 在遇到重复值时,都不会报错。它们按各自的方向取第一条或最后一条, 然后安静地返回。数据有没有重复,只能由你先查。

如果不这样会怎样:用户表 62 行里有 60 个唯一 ID,U012 和 U027 各出现两次。 3 笔订单合计 4,889.54 元会被固定算到第一条记录所在的城市;如果真实归属是另一条,城市汇总会整体偏移, 而总额一分不差,你很难发现。

4

条件区域和求和区域必须同起点、同高度

SUMIFS 按行号配对,不按内容配对。两个区域都从第 2 行开始、都到第 121 行结束,才是一一对应。

如果不这样会怎样:条件写 F2:F121、求和写 D3:D122,两个区域都是 120 格, Excel 不报错,但每行都错开一格,合计少掉第一笔 1,563.25 元。

7.2分析:#N/A 的四种成因

#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,没有争议。

IFERROR:全吞

连 #REF!、#VALUE! 一起吞。公式写错也显示“未建档”,你以为是用户没建档。

IFNA:只接 #N/A

只处理“找不到值”,其他错误继续报警。错误被看见,才有机会被修。

7.3实验:订单表补用户信息,再算各城市销售额

这次要交付一张能回答“各城市卖了多少”的表。路线只有两条:先用查找把城市接到订单上, 再用 SUMIFS 按城市汇总。中间会遇到 13 个 #N/A 和 2 个重复 ID,别绕开,它们是本章的主角。

实验数据 1

orders_with_uid.xlsx

120 行订单 · 5 列 · 13 行用户ID缺失

下载
实验数据 2

users.xlsx

62 行用户 · 5 列 · 2 个重复 ID

下载
先别修数据。用户表的重复、订单表的缺失,都是这次实验要观察的现象。提前去重或补 ID, 后面的 #N/A 和城市偏移就全看不见了——那不是实验成功,是把问题藏起来了。

7.3.1实验环境

项目要求
软件Microsoft Excel 2016 及以上,或 WPS 表格。XLOOKUP 需要 Excel 2021 / Microsoft 365。
设备电脑。手机版做不了跨工作簿引用和填充柄双击。
准备把两个文件放在同一个文件夹,分别打开;订单表另存为 订单分析.xlsx。
已完成 0 / 8 预计 50 分钟
01

体检:62 行不等于 60 个用户

5 分钟 · 对应规则 3
做什么
数清用户表的行数和唯一 ID 数,找出重复项。
为什么
规则 3:查找值必须唯一。不先确认,VLOOKUP 返回哪一条你根本不知道。
怎么做
  1. 打开 users.xlsx,在 F1 输入 =COUNTA(A2:A63) → 62
  2. F2 输入 =SUMPRODUCT(1/COUNTIF(A2:A63,A2:A63)) → 60
  3. G2 输入 =COUNTIF($A$2:$A$63,A2),双击填充柄到 G63
  4. 对 G 列筛选,只看 2
看到什么
62 行、60 个唯一 ID;U012 在第 13 和 62 行,U027 在第 28 和 63 行。
02

建三列,把用户表区域锁死

5 分钟 · 对应规则 1
做什么
把用户表复制进订单工作簿,加三列,并给 用户表!A2:E63 起名“用户信息”。
为什么
规则 1:不锁区域,下拉 119 行后第二参数滑到 A121:E182,整列作废。
怎么做
  1. 在 users.xlsx 右键“用户表”标签 → 移动或复制 → 勾“建立副本” → 选订单分析.xlsx
  2. 在订单表 F1、G1、H1 输入“城市”“会员等级”“来源渠道”
  3. 点 公式 → 名称管理器 → 新建
  4. 名称填 用户信息,引用位置填 =用户表!$A$2:$E$63 → 确定
  5. 在订单表名称框输入 用户信息 回车,确认选中 62 行 × 5 列
看到什么
名称管理器里出现“用户信息”;回到订单表,在名称框选它,62 行 5 列被一次选中。
03

写第一条 VLOOKUP 核心

8 分钟 · 对应规则 2
做什么
在 F2 用用户ID查出城市,先只做一行。
为什么
规则 2:B2 是 U023,第 3 列是城市。先用一行验证参数,比整列报错再查快。
怎么做
  1. 点 F2,输入 =VLOOKUP($B2,用户信息,3,FALSE)
  2. 逐个参数核对:$B2 是查找值;用户信息 是区域;3 是城市;FALSE 是精确匹配
  3. 按 Enter
看到什么
F2 显示 上海;编辑栏是 =VLOOKUP($B2,用户信息,3,FALSE)。
04

向右、向下填满 120 行

8 分钟 · 对应混合引用
做什么
把城市、会员等级、来源渠道三列一次填到第 121 行。
为什么
$B2 是混合引用:锁住 B 列,往右拖不会跑到 C 列;行号不锁,往下拖才会跟着变。列号 3、4、5 是常数,向右拖不会自动加 1,必须手动改。
怎么做
  1. G2 输入 =VLOOKUP($B2,用户信息,4,FALSE)
  2. H2 输入 =VLOOKUP($B2,用户信息,5,FALSE)
  3. 选中 F2:H2,双击右下角填充柄,填到第 121 行
看到什么
120 行全部填满;F 列出现 13 个 #N/A,G、H 列同一批行也一起报错。
05

数清楚:13 行、20,124.81 元

5 分钟 · 对应 7.2 第一种成因
做什么
统计 #N/A 的行数和这些行的金额合计。
为什么
先量化再处理:13 行是删、是补还是标记,取决于它们值多少钱。
怎么做
  1. J1 输入 =SUMPRODUCT(--ISNA($F$2:$F$121))
  2. J2 输入 =SUMPRODUCT(--ISNA($F$2:$F$121),$D$2:$D$121)
  3. 对 F 列筛选“错误”,看剩下的用户ID
看到什么
13 行,金额合计 20,124.81;用户ID只有 U901、U902、U903、U904、U905 五个。
为什么不用 COUNTIF 数 #N/A

错误值不是文本,COUNTIF 对它的处理随版本而异,容易少数或报错。ISNA 判断“是不是 #N/A”,SUMPRODUCT 负责计数,稳定。

06

用 IFNA 标记,不删除 核心

8 分钟 · 对应 7.2 修复原则
做什么
把 13 个 #N/A 显示为“未建档”,订单行原样保留。
为什么
这 13 笔是真实订单,值 20,124.81。删掉城市合计不变,但订单总额少 2 万,“用户表缺档”也被永久掩盖。
怎么做
  1. F2 改成 =IFNA(VLOOKUP($B2,用户信息,3,FALSE),"未建档")
  2. G2、H2 同样包一层 IFNA,找不到时显示 —
  3. 选中 F2:H2,双击填充柄,覆盖全部 120 行
看到什么
F 列 13 个“未建档”,#N/A 消失;D 列求和仍是 217,057.37,一分没少。
为什么不用 IFERROR

IFERROR 连 #REF!、#VALUE! 一起吞。哪天你把区域写错,整列显示“未建档”,你还以为只是用户没建档。IFNA 只接 #N/A,其他错误继续报警。

07

SUMIFS 算六个城市 核心

10 分钟 · 对应规则 4
做什么
在 K2:K7 列六个城市,L 列算销售额,M 列算订单数。
为什么
规则 4:条件区域 F2:F121 与求和区域 D2:D121 必须同起点、同高度,这是最容易写错的一步。
怎么做
  1. K2:K7 输入杭州、成都、深圳、广州、上海、北京
  2. L2 输入 =SUMIFS($D$2:$D$121,$F$2:$F$121,K2)
  3. M2 输入 =COUNTIFS($F$2:$F$121,K2)
  4. 选中 L2:M2,双击填充柄到第 7 行
看到什么
六个城市金额依次为 53,147.40 / 37,047.80 / 34,693.63 / 33,804.24 / 24,274.45 / 13,965.04。
卡住了 / 出错了

#VALUE!:两个区域大小不一样,检查是不是写成了 D2:D120。

全是 0:K2 的城市名有空格,或 F 列是“未建档”而条件写错。用 =LEN(K2) 看长度。

合计对不上:求和区域整体下移了一行,回图 7.1.4 对照。

08

三方对账:217,057.37 = 196,932.56 + 20,124.81

6 分钟 · 对应规则 3、4
做什么
用订单总额、城市合计、未建档金额三个数互相验证。
为什么
只报城市合计,老板不知道漏了多少;三方对账才能证明少的正好是查不到的那批。
怎么做
  1. N1 输入 =SUM($D$2:$D$121) → 217,057.37
  2. N2 输入 =SUM($L$2:$L$7) → 196,932.56
  3. N3 输入 =SUMIFS($D$2:$D$121,$F$2:$F$121,"未建档") → 20,124.81
  4. N4 输入 =N2+N3-N1,必须是 0
看到什么
三个数相加差 0;行数 120 = 107 行有城市 + 13 行未建档。

7.3.2实验结果

匹配后的表应该长这样。注意第 17 行:U903 不存在,三列同时是 #N/A(第 6 步后会显示“未建档”)。

行A 订单号B 用户IDC 下单日期D 金额F 城市G 会员等级H 来源渠道
2O2406001U0232024-06-291,563.25上海银卡线下
3O2406002U0552024-06-191,891.99上海银卡朋友推荐
4O2406003U0532024-06-282,122.08深圳银卡线下
5O2406004U0122024-06-15896.81成都普通朋友推荐
6O2406005U0522024-06-28544.59深圳普通线下
7O2406006U0182024-06-251,497.22北京银卡信息流
8O2406007U0432024-06-133,141.36杭州普通朋友推荐
17O2406016U9032024-06-211,824.58#N/A#N/A#N/A
图 7.3.1 U012 返回“成都”(第一条),U903 三列全部 #N/A

城市汇总表应该长这样。和订单总额差的 20,124.81,正好是 13 行未建档订单;这不是误差,是口径。

城市销售额订单数
杭州53,147.4028
成都37,047.8017
深圳34,693.6319
广州33,804.2421
上海24,274.4513
北京13,965.049
城市合计196,932.56107
未建档20,124.8113
订单总额217,057.37120
图 7.3.2 六个城市合计 196,932.56,加未建档 20,124.81,正好回到 217,057.37
关于 U012 的成都:用户表第 62 行还有一条 U012(上海),VLOOKUP 只取第一条,所以 O2406004 的 896.81 记到成都。若业务确认应归上海,成都 36,150.99、上海 25,171.26,合计仍 196,932.56。

7.4验证:数值核对与选择题

验收标准不是“看起来对”,是“三个数能对上”。先填四个数,再做五道题;全对说明查找和汇总两条链路都通了。

选中 D2:D121,看状态栏求和。
六个城市的销售额相加。
F 列筛选“未建档”。
SUMIFS 第一行。
Q1公式 =VLOOKUP($B2,用户信息,3,FALSE) 从 F2 向右拖到 G2,第一个参数会变成什么?
解析:$B2 只锁列,不锁行;向右拖列被锁住,仍是 B。若写成 B2,向右拖变 C2,查到的是日期。
Q2用户表里 U012 有两条:第 13 行成都、第 62 行上海。VLOOKUP 精确匹配会返回哪个城市?
解析:VLOOKUP 找到第一个相等值就停,不会检查后面还有没有。U012 第一次出现在第 13 行,所以永远返回成都。重复不会报错,这是它最危险的地方之一。
Q3用户表字段顺序是 A 用户ID、B 注册日期、C 城市、D 来源渠道、E 会员等级。把公式第三个参数从 3 改成 4,会返回什么?
解析:列号从区域左边缘数:1 ID、2 注册日期、3 城市、4 来源渠道、5 会员等级。改成 4 返回来源渠道,不报错但全错——这就是插入列会错。
Q4=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!。
Q5订单表 B5 被写成小写 u012,公式 =VLOOKUP(B5,用户信息,3,FALSE) 的结果是?
解析:VLOOKUP 文本比较不区分大小写,u012 能匹配 U012,仍返回第一条成都。如果大小写有业务含义,用 EXACT 或辅助列。
得分:0 / 5

7.5常见问题排查

按“现象 → 原因 → 解决”整理。先查表,再回 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,同起点同高度

7.6本章小结与练习

  1. 引用是偏移量,$ 是钉子。该锁不锁,区域会跟着行号滑,整列 #N/A。
  2. VLOOKUP 五个限制:只能右查、插入列会错、大数据慢、不区分大小写、只返回第一个匹配;前两个不报错。新环境改用 XLOOKUP,旧版用 INDEX+MATCH。
  3. SUMIFS 的区域必须同起点、同高度。大小不同报 #VALUE!,大小相同但错位则静默算错。
  4. 查找值唯一,缺失要标记。62 行用户表只有 60 个 ID;13 行未建档值 20,124.81,删掉就永远发现不了缺档。

7.6.1练习

  1. 把 =VLOOKUP(B2,用户表!$A$2:$E$63,3,FALSE) 写成 =VLOOKUP(B2,用户表!A2:E63,3,FALSE) 后下拉到第 121 行,F121 的第二参数是什么?从第几行开始会大面积出错?
  2. 业务确认 O2406004(U012,896.81)应归上海而不是成都。成都和上海的新销售额分别是多少?六个城市合计会变吗?
  3. 如果直接把 13 行未建档订单删掉,城市销售额合计会变吗?订单总额会变吗?这样做丢了什么信息?
  4. 写出“来源渠道 = 朋友推荐 且 会员等级 = 金卡”的销售额公式,并算出结果与订单数。
参考答案

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 行,同起点同高度。

下一章预告:本章用 SUMIFS 手工算了六个城市。如果要“城市 × 等级 × 渠道”交叉看,得写 72 组公式; 下一章用透视表,拖三个字段、刷一下。
数据分析师训练营 · 第七章
Excel / WPS 均可,菜单名可能略有差异