数据分析师训练营/ 第二篇 Excel/ 第八章
第八章 · 透视表

透视表:把明细压成结论

336 行明细,4 个地区、6 个月、5 个品类、2 条渠道。老板只问:哪个地区最好? 哪个月最差?线上线下差多少?

用 SUMIFS,光“地区 × 月份”就要 24 个公式;换口径全要重写。 透视表把这件事压成三次拖拽:地区拖到行,月份拖到列,销售额拖到值。 但它有两个脾气:源数据不是一维表就错;改了不刷新就看不见。 这一章先讲透“分组 + 聚合”,再做透视表,加切片器和刷新。

理论 : 实操 = 5 : 5 约 6 学时 前置 第 6、7 章

8.1原理:透视表到底在算什么

透视表不是 Excel 的专利。SQL 的 GROUP BY、Python 的 groupby 都在做同一件事。 先把计算逻辑讲清楚,拖字段只是动作。

8.1.1分组 + 聚合:透视表只做这两件事

336 行明细,一行一笔订单。你想知道“华南卖了多少”,人脑分两步: 先挑出“地区 = 华南”的行,再把销售额加起来。这就是透视表的全部: 分组和聚合。

把地区换成“地区 + 月份”,336 行会分成 4 × 6 = 24 组,每组正好 14 笔订单。 华南 1 月的 597,072.94,就是这 14 笔订单的销售额之和。

维度是切数据的角度,比如地区、品类、渠道、订单日期; 度量是被计算的数字,比如销售额、数量。字段是维度还是度量,取决于你怎么用。

1

透视表只做两件事:先分组,再聚合

分组靠行、列、筛选区域,聚合靠值区域。值区域必须是数值;文本放进去, Excel 只能计数。聚合还有计数、平均、最大、最小。

如果不这样会怎样:销售额是文本时,值区域显示“计数项:销售额”, 总计是 336,不是 11,733,617.19。336 是订单数,不是金额。

336 行明细
→
分组:4 地区 × 6 月
→
SUM(销售额)
→
24 个数字
ABC
1地区1月2月
2华南597,072.94303,652.90
3华北695,977.40407,672.18
图 8.1.1 透视表的计算链:明细先分组,再聚合,最后落到交叉表的格子里

8.1.2四个区域:行、列、值、筛选

字段列表里有四个方框:行、列、值、筛选。同一个字段放错位置,结果完全不同。行负责纵向展开,放地区就有四行;换成品类,就是五行:手机 2,683,650.98、家电 2,608,703.00、配件 2,396,211.70、电脑 2,074,359.25、服饰 1,970,692.26。 列负责横向展开,放月份就有六列;行 × 列才有矩阵。

值决定格子里填什么,可以放多个字段,各自设置聚合和格式。 筛选在分组之前圈定数据;切片器就是它的可视化版本。

ABCDEFGH
1筛选区域:渠道 = 线上
2列区域:订单日期(按月)
3行区域:地区1月2月3月4月5月6月值区域:销售额求和
4华南597,072.94…………549,567.66
5华北………………
6华东………………
7西南………………
8总计2,428,851.22…………11,733,617.19
图 8.1.2 四个区域各就各位:行放地区,列放月份,值放销售额,筛选放渠道
2

行和列决定“怎么切”,值决定“算什么”,筛选决定“看哪一块”

四个区域里,只有值区域必须是数值。把销售额拖到行区域,你会得到 336 行明细; 把地区拖到值区域,只会得到“华东出现了 84 次”这种计数。

如果不这样会怎样:你想看“每个地区卖了多少”,却把地区拖进了值区域, 结果是一列“计数项:地区”,每个地区 84。数字没错,但答的不是老板的问题。

8.1.3计算字段 vs 计算项:为什么“占比”要用计算字段

占比最容易做错:在“地区”上右键 → 添加计算项 → 写除法。四行变五行, 总计立刻不对。

计算字段是新增一个度量,比如“客单价 = 销售额 / 数量” “毛利率 =(销售额 − 成本)/ 销售额”。拖进值区域,就多一列。 计算项是在维度里新增一个成员,比如“华南 + 西南”。 它让行或列多一个标签,改的是维度,不是度量。

表 8.1.1 计算字段与计算项的分工

对比项计算字段计算项
本质新增一个度量字段在维度里新增一个成员
加在哪字段列表 → 值区域行或列字段 → 计算项
结果值区域多一列数字行或列多一个标签
总计作为独立度量参与会进入总计,容易重复计算
典型用途客单价、毛利率、占比华南 + 西南、手机 + 电脑
不要用来逐行算完再平均占比、增长率等比例

占比的分子和分母都是度量,不是“地区”的第五个成员。硬做成计算项, 会多出一行“占比”,总计把这个百分数也加进销售额,金额列混进比例,量纲直接乱掉。 正确位置是值区域:用「值字段设置 → 值显示方式 → 占总计的百分比」,或写一个计算字段。 结论:占比要用计算字段(或等价的“值显示方式”),不能用计算项。

计算字段在“汇总后的值”上运算,不是逐行。 客单价 = 销售额 / 数量 得到的是 2,155.73,不是 336 笔订单各自单价的平均。 要逐行平均,得先在源数据加“单价”列。

3

占比是值层面的计算,不是维度成员

想的是“分子 ÷ 分母”,放值区域;想的是“把几个成员并成一个”,才用计算项。 计算项只适合合并成员,合并后要检查总计。

如果不这样会怎样:在地区里加“占比”计算项,4 行变 5 行, 总计不再是 11,733,617.19,金额列里混进一行百分数,财务一对就发现。

8.1.4缓存与刷新:源数据改了,为什么数字没变

源表改了一个数,透视表不动,不是 Excel 坏了,是它读缓存。 创建透视表时,Excel 把源数据复制一份压进缓存。缓存不只存值,还存字段类型; 先建表再改日期类型,刷新后可能仍不能组合。

三个操作:刷新(范围不变,重读源数据,Alt + F5); 更改数据源(范围变了,先改范围再刷新); 刷新全部(多个透视表,Ctrl + Alt + F5)。

如果源数据是 Ctrl + T 创建的“表格”,新增行会自动纳入范围; 普通区域写死 A1:F337,追加在 337 行下,刷新也进不来。

操作源数据缓存透视表显示
1把 E2 从 70,341.32 改成 71,341.32变了不变不变
2按 Alt + F5 刷新不变重新读取变了,华东 +1,000
3在第 337 行下追加 20 行变了范围仍是 A1:F337刷新也不变
图 8.1.3 缓存是源数据的快照:改值要刷新,改范围要更改数据源
4

刷新只重读旧范围,改范围要用“更改数据源”

前者解决“值变了”,后者解决“行数变了”。只做前者,新数据进不来。

如果不这样会怎样:6 月补录 20 单,接在 337 行下。 你只按刷新,透视表还是旧数;发进月报,少的正好是这 20 单。Excel 不报错。

8.2分析:源数据的三条铁律

透视表的怪毛病,几乎都能追溯到源数据的形状。检查顺序:一维表 → 空行 → 合并单元格。

8.2.1铁律一:一维表,一行一条记录

一维表:一行一条记录,一列一个字段,只有一个表头行。sales.xlsx 就是标准一维表:一行一笔订单,六列对应六个字段。

反直觉的是:透视表输出二维表,输入必须一维表。把交叉表当数据源, 会报错或只认第一列。

表 8.2.1 二维表与一维表:一个是对账单,一个是数据源

对比项二维表(交叉表)一维表(明细表)
一行代表一个地区(或一个品类)一笔订单
列代表月份变成了列名:1月、2月、3月每列一个字段:地区、月份、销售额
新增一个月加一列,所有公式重写加行,结构不变
筛选 / 排序只能按整行,不能按月份任意字段都能筛
能否直接做透视表不能能
角色透视表的输出透视表的输入

8.2.2铁律二:无空行,无小计行

空行会改变 Excel 对边界的判断。插入透视表时默认选当前区域;中间一整行空白, 区域在这里断开,后面的数据不进数据源。手动拉到最后,空行也会变成“(空白)”分组。

小计行、合计行更常见。它们是汇总结果,不是明细;留在源数据里会被当成真实订单 再加一遍,总额直接翻倍。

如果不这样会怎样:336 条记录第 200 条空着,Ctrl + A 只选到 199 条,后面 136 条全丢,总额可能少四成。

8.2.3铁律三:无合并单元格,无多级表头

合并单元格只保留左上角一个值,其余是空。地区列每四行合并成“华南”, 透视表只认第一行,后三行进空白组。多级表头同理:第一行当字段名,第二行当数据, 字段名变成“列1、列2”,没法拖。

如果不这样会怎样:华南原本 3,094,471.53,合并后只剩第一行金额, 剩下三行的钱全进空白组,实际是上百万销售额消失。

检查顺序:Ctrl + End 看最后一行 → 找空行和小计行 → 取消合并 → 确认表头只有一行。五分钟检查,省三小时返工。

8.3实验:地区 × 月份透视表

目标:一张“地区 × 月份”的销售额透视表,加一个渠道切片器;最后改一个源数据, 看看刷新前后差多少。约 40 分钟。

8.3.1实验准备

项目要求
软件Excel 2016 及以上,或 WPS 表格(菜单名略有差异)
数据sales.xlsx:336 行、6 列,2024-01-01 至 2024-06-27
字段订单日期 / 地区 / 品类 / 渠道 / 销售额 / 数量
交付销售透视表_姓名.xlsx,含透视表和切片器
实验数据

sales.xlsx

336 行 · 6 列 · 2024 年上半年销售明细

下载
做完的样子

地区 × 月份 + 渠道切片器

4 个地区 × 6 个月,总计 11,733,617.19;切片器可切线上 / 线下。

先复制一份:把 sales.xlsx 复制为 sales_练习.xlsx。 第 7 步要手动改 E2,副本方便撤回。

8.3.2实验步骤(8 步)

每步四段:做什么 → 为什么 → 怎么做 → 看到什么。“为什么”对应 8.1 的规则和 8.2 的铁律。

Excel 插入选项卡:数据透视表、图表、切片器
图 8.3.1 「插入 → 数据透视表」的位置:先选中数据区任意单元格,再点按钮
已完成 0 / 8 预计 40 分钟
01

先体检,再把日期转成真日期

5 分钟 · 对应 8.2 三条铁律与第 6 章
做什么
确认 336 行、6 列,无空行和合并,销售额是数值;把 A 列文本日期转成真日期。
为什么
透视表不修数据;文本日期不能按月组合,先修类型再建透视表。
怎么做
  1. Ctrl + End 看最后一行是不是 337
  2. 选中 E2:E337,看状态栏求和
  3. 点 A2,看编辑栏和格子里是不是一样;靠左就是文本
  4. 选中 A2:A337 → 数据 → 分列 → 下一步 → 下一步 → 日期、YMD → 完成
看到什么
最后一行 337;求和 11,733,617.19;A 列日期靠右,显示 2024/4/3。
卡住了 / 出错了

求和不对 → 回第 6 章转数值;提示替换 → 点确定;分列后还有靠左的 → 单独选中那几格再分列。

02

插入一张空白透视表

3 分钟 · 对应 8.1.1
做什么
以 A1:F337 为源,在新工作表创建透视表。
为什么
范围覆盖全部 336 行;新表不破坏源数据。
怎么做
  1. 点数据区 → 插入 → 透视表
  2. 区域应显示 销售明细!$A$1:$F$337
  3. 选 新工作表 → 确定
看到什么
左侧空白透视表,右侧字段窗格:六个字段、四个区域——行、列、值、筛选。
03

拖出骨架:地区在行,月份在列

4 分钟 · 对应规则 2
做什么
地区拖到行,订单日期拖到列,销售额拖到值。
为什么
行纵切、列横切、值聚合,三个位置对了才有交叉表。
怎么做
  1. 在字段窗格依次拖动 地区、订单日期、销售额
看到什么
四行地区;日期列(可能自动分层,也可能是一串日期);总计 11,733,617.19。
04

日期按月组合

5 分钟 · 对应 8.1.1
做什么
把订单日期按“月”分组,只保留 1—6 月六列。
为什么
日期是序列号,组合是按月装桶;不组合,只能看到具体日期。
怎么做
  1. 右键日期表头 → 组合
  2. 起始 2024-01-01,终止 2024-06-30,步长勾 月,取消年、季度
  3. 若已自动分层,把“年”“季度”拖出列区域,只留“月”
看到什么
1月…6月 六列;华南 1 月 597,072.94,华南 6 月 549,567.66;每行合计等于地区总额。
05

值字段设置:求和 + 占比

6 分钟 · 对应规则 1、3
做什么
销售额求和,再加一个“占总计的百分比”,保留两位小数。
为什么
值区域必须是数值;占比是值层面计算,不能做成计算项。
怎么做
  1. 值字段设置 → 求和 → 数字格式 → 数值、两位小数
  2. 再拖一次销售额 → 值字段设置 → 值显示方式 → 占总计的百分比
  3. 名称改“占比”,格式设 0.00%
看到什么
华南 3,094,471.53 / 26.37%,华北 25.80%,华东 24.75%,西南 23.08%;总计 100.00%。
卡住了 / 出错了

显示“计数项” → 销售额是文本,选中列 → 数据 → 分列 → 完成;占比显示 0.2637 → 数字格式设成 0.00%;总计不是 100.00% → 检查是不是做成了计算项。

06

插入切片器:渠道

5 分钟 · 对应规则 2
做什么
加一个“渠道”切片器,点线上 / 线下,观察总计变化。
为什么
切片器是筛选的可视化,筛选发生在聚合之前。
怎么做
  1. 点透视表 → 透视表分析 → 插入切片器 → 勾 渠道
  2. 点 线上,再点 线下,最后点漏斗清除筛选
看到什么
线上 5,821,554.80;线下 5,912,062.39;清除后 11,733,617.19。
卡住了 / 出错了

切片器没反应 → 右键 → 报表连接,勾上这张透视表;要多表联动 → 它们必须共用同一缓存。

07

改一个数,验证缓存

6 分钟 · 对应规则 4
做什么
确认切片器已清除筛选;把 E2 从 70,341.32 改成 71,341.32,先不刷新,再刷新,最后撤销。
为什么
透视表读缓存,不读源单元格;刷新只重读旧范围。
怎么做
  1. 改 E2 → 切回透视表,数字没变
  2. 右键 → 刷新(或 Alt + F5)→ 华东 +1,000
  3. 源表 Ctrl + Z,再刷新一次
看到什么
刷新前 11,733,617.19;刷新后 11,733,718.19,华东 2,903,872.22;撤销后恢复。
卡住了 / 出错了

刷新后没变 → 确认点的是透视表;检查“公式 → 计算选项”是不是手动;追加了新行 → 刷新不够,要更改数据源。

08

整理并保存

4 分钟 · 对应 8.1.2
做什么
改名、隐藏字段按钮、用表格布局、保留总计。
为什么
默认布局自己看得懂,别人看不懂;交付物要一眼看懂。
怎么做
  1. 工作表改名“地区×月份”
  2. 透视表分析 → 字段按钮 → 隐藏全部
  3. 设计 → 报表布局 → 以表格形式显示;设计 → 总计 → 对行和列启用
  4. 另存为 销售透视表_姓名.xlsx
看到什么
干净的表格(4 个地区 + 总计,行标签 + 6 个月 + 销售额 + 占比),切片器在上方,无字段按钮。
ABCDEFG
1地区1月2月3月4月5月6月总计
2华东567,507.72613,397.60273,578.31384,928.03588,203.76476,255.802,903,871.22
3华北695,977.40407,672.18436,250.28573,211.80537,272.26376,460.943,026,844.86
4华南597,072.94303,652.90445,732.18482,808.90715,636.95549,567.663,094,471.53
5西南568,293.16440,534.59371,548.74474,203.97508,832.61345,016.512,708,429.58
6总计2,428,851.221,765,257.271,527,109.511,915,152.702,349,945.581,747,300.9111,733,617.19
图 8.3.2 完成后的地区 × 月份透视表(销售额部分,单位:元)

8.4结果验证

先对四个数,再做五道题。四个数对不上,别往下走——透视表算错了也不报错。

8.4.1数值核对

看透视表右下角的总计。
看“华南”这一行的总计。
看“6月”这一列的总计。
先点“线上”,再看总计。

8.4.2五道选择题

Q1透视表“地区销售额”的本质是什么?
解析:透视表只做分组和聚合。336 行按“地区 + 月份”分成 24 组,每组 14 笔,再求和。
Q2销售额拖到值区域,显示“计数项:销售额”,最可能的原因是什么?
解析:数值默认求和,文本默认计数。看对齐和绿三角,用“数据 → 分列 → 完成”转换后刷新。
Q3在第 337 行下追加 30 行,按了刷新,总计却没变。为什么?
解析:刷新只重读旧范围。用“更改数据源”拉到新末行再刷新,或一开始就 Ctrl+T 转成表格。
Q4在“地区”字段上添加计算项“占比”,会发生什么?
解析:计算项加的是维度成员,不是度量。总计会把它加进去,金额列混进比例,量纲就乱了。
Q5日期拖到列区域后,右键“组合”是灰色的,最可能的原因是什么?
解析:真日期是序列号,可以组合;文本日期只是长得像。用“数据 → 分列 → 日期 YMD”转换。
得分:0 / 5

8.5常见问题排查

按“现象 → 原因 → 解决”查。前三条是高频错误。

现象原因解决
值区域显示“计数项:销售额”,总计是 336 销售额列是文本型数字,Excel 只能计数 选中整列 → 数据 → 分列 → 完成;刷新透视表
源数据追加了行,刷新后总计没变 数据源范围仍是 A1:F337,新行在范围外 透视表分析 → 更改数据源 → 选到新末行 → 刷新;或 Ctrl+T 转表格
右键日期没有“组合”,或“组合”是灰色 日期列是文本;或列内有空白 数据 → 分列 → 日期 YMD;补空值或删空行后刷新
组合后出现“年/季度/月”三层,列太多 Excel 自动组合了日期层级 把“年”“季度”拖出列区域,只留“月”
切片器点了没反应,或只筛一张表 切片器没有连接到目标透视表 右键切片器 → 报表连接 → 勾选;多表要共用同一缓存
计算字段算出来和逐行算再平均不一样 计算字段在汇总值上运算,不是逐行 明确口径;要逐行平均就在源数据加辅助列,再对辅助列求平均
两个透视表刷新不同步 缓存不同,或只刷新了当前表 Ctrl+Alt+F5 刷新全部;用同一数据源或表格共享缓存
总计和明细 SUM 对不上 源数据有空行、合并、范围缺行,或透视表还有筛选 Ctrl+End 查范围;取消合并;清除筛选;更改数据源
数字显示 #### 列宽不够 拉宽列,或双击列边界

8.6本章小结与练习

  1. 透视表 = 分组 + 聚合。维度放行、列、筛选,度量放值;值区域必须是数值。
  2. 四个区域:行纵切、列横切、值填数、筛选圈范围。
  3. 占比是值层面的计算。用计算字段或“值显示方式”,不要用计算项。
  4. 缓存是快照:改值要刷新,改范围要“更改数据源”;Ctrl+T 转表格可自动扩展。
  5. 源数据三条铁律:一维表、无空行、无合并单元格。

8.6.1练习

  1. 华东销售额占全国的比重是多少?保留两位小数,写出分子和分母。
  2. 六个月里销售额最低的是哪个月?金额和占比是多少?
  3. 为什么不能在“地区”字段上做“占比”计算项?“华南 + 西南”合并该用什么?
  4. 源数据从 336 行增加到 400 行,新行接在第 337 行下面,刷新后总计没变。为什么?写出两种处理办法。
参考答案

1. 2,903,871.22 ÷ 11,733,617.19 = 24.75%。分子是华东销售额,分母是全国总销售额;分母不能手写,否则新增地区会漏。

2. 3 月,1,527,109.51,占 13.01%。最高是 1 月,2,428,851.22,占 20.70%。

3. 计算项会在“地区”里新增一个成员,多出一行“占比”,总计会把百分数也加进销售额。占比应放在值区域,用“值显示方式 → 占总计的百分比”或计算字段。“华南 + 西南”合并可以用计算项,但合并后要检查总计有没有重复。

4. 数据源范围还是 A1:F337,新行在范围外,刷新只重读旧范围。办法一:更改数据源到 A1:F401 → 刷新;办法二:源数据 Ctrl+T 转表格,再刷新。

下一章预告:第 9 章会拿这张透视表做图:折线看趋势,柱状比地区。 先拆几张“漂亮但错误”的图,再做一页纸月报。
数据分析师训练营 · 第八章
Excel / WPS 均可,菜单名可能略有差异