336 行明细,4 个地区、6 个月、5 个品类、2 条渠道。老板只问:哪个地区最好?
哪个月最差?线上线下差多少?
用 SUMIFS,光“地区 × 月份”就要 24 个公式;换口径全要重写。
透视表把这件事压成三次拖拽:地区拖到行,月份拖到列,销售额拖到值。
但它有两个脾气:源数据不是一维表就错;改了不刷新就看不见。
这一章先讲透“分组 + 聚合”,再做透视表,加切片器和刷新。
透视表不是 Excel 的专利。SQL 的 GROUP BY、Python 的 groupby 都在做同一件事。 先把计算逻辑讲清楚,拖字段只是动作。
336 行明细,一行一笔订单。你想知道“华南卖了多少”,人脑分两步: 先挑出“地区 = 华南”的行,再把销售额加起来。这就是透视表的全部: 分组和聚合。
把地区换成“地区 + 月份”,336 行会分成 4 × 6 = 24 组,每组正好 14 笔订单。 华南 1 月的 597,072.94,就是这 14 笔订单的销售额之和。
维度是切数据的角度,比如地区、品类、渠道、订单日期; 度量是被计算的数字,比如销售额、数量。字段是维度还是度量,取决于你怎么用。
分组靠行、列、筛选区域,聚合靠值区域。值区域必须是数值;文本放进去, Excel 只能计数。聚合还有计数、平均、最大、最小。
如果不这样会怎样:销售额是文本时,值区域显示“计数项:销售额”, 总计是 336,不是 11,733,617.19。336 是订单数,不是金额。
| A | B | C | |
|---|---|---|---|
| 1 | 地区 | 1月 | 2月 |
| 2 | 华南 | 597,072.94 | 303,652.90 |
| 3 | 华北 | 695,977.40 | 407,672.18 |
字段列表里有四个方框:行、列、值、筛选。同一个字段放错位置,结果完全不同。行负责纵向展开,放地区就有四行;换成品类,就是五行:手机 2,683,650.98、家电 2,608,703.00、配件 2,396,211.70、电脑 2,074,359.25、服饰 1,970,692.26。 列负责横向展开,放月份就有六列;行 × 列才有矩阵。
值决定格子里填什么,可以放多个字段,各自设置聚合和格式。 筛选在分组之前圈定数据;切片器就是它的可视化版本。
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 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 | |
四个区域里,只有值区域必须是数值。把销售额拖到行区域,你会得到 336 行明细; 把地区拖到值区域,只会得到“华东出现了 84 次”这种计数。
如果不这样会怎样:你想看“每个地区卖了多少”,却把地区拖进了值区域, 结果是一列“计数项:地区”,每个地区 84。数字没错,但答的不是老板的问题。
占比最容易做错:在“地区”上右键 → 添加计算项 → 写除法。四行变五行, 总计立刻不对。
计算字段是新增一个度量,比如“客单价 = 销售额 / 数量” “毛利率 =(销售额 − 成本)/ 销售额”。拖进值区域,就多一列。 计算项是在维度里新增一个成员,比如“华南 + 西南”。 它让行或列多一个标签,改的是维度,不是度量。
| 对比项 | 计算字段 | 计算项 |
|---|---|---|
| 本质 | 新增一个度量字段 | 在维度里新增一个成员 |
| 加在哪 | 字段列表 → 值区域 | 行或列字段 → 计算项 |
| 结果 | 值区域多一列数字 | 行或列多一个标签 |
| 总计 | 作为独立度量参与 | 会进入总计,容易重复计算 |
| 典型用途 | 客单价、毛利率、占比 | 华南 + 西南、手机 + 电脑 |
| 不要用来 | 逐行算完再平均 | 占比、增长率等比例 |
占比的分子和分母都是度量,不是“地区”的第五个成员。硬做成计算项, 会多出一行“占比”,总计把这个百分数也加进销售额,金额列混进比例,量纲直接乱掉。 正确位置是值区域:用「值字段设置 → 值显示方式 → 占总计的百分比」,或写一个计算字段。 结论:占比要用计算字段(或等价的“值显示方式”),不能用计算项。
计算字段在“汇总后的值”上运算,不是逐行。
客单价 = 销售额 / 数量 得到的是 2,155.73,不是 336 笔订单各自单价的平均。
要逐行平均,得先在源数据加“单价”列。
想的是“分子 ÷ 分母”,放值区域;想的是“把几个成员并成一个”,才用计算项。 计算项只适合合并成员,合并后要检查总计。
如果不这样会怎样:在地区里加“占比”计算项,4 行变 5 行, 总计不再是 11,733,617.19,金额列里混进一行百分数,财务一对就发现。
源表改了一个数,透视表不动,不是 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 | 刷新也不变 |
前者解决“值变了”,后者解决“行数变了”。只做前者,新数据进不来。
如果不这样会怎样:6 月补录 20 单,接在 337 行下。 你只按刷新,透视表还是旧数;发进月报,少的正好是这 20 单。Excel 不报错。
透视表的怪毛病,几乎都能追溯到源数据的形状。检查顺序:一维表 → 空行 → 合并单元格。
一维表:一行一条记录,一列一个字段,只有一个表头行。sales.xlsx
就是标准一维表:一行一笔订单,六列对应六个字段。
反直觉的是:透视表输出二维表,输入必须一维表。把交叉表当数据源, 会报错或只认第一列。
| 对比项 | 二维表(交叉表) | 一维表(明细表) |
|---|---|---|
| 一行代表 | 一个地区(或一个品类) | 一笔订单 |
| 列代表 | 月份变成了列名:1月、2月、3月 | 每列一个字段:地区、月份、销售额 |
| 新增一个月 | 加一列,所有公式重写 | 加行,结构不变 |
| 筛选 / 排序 | 只能按整行,不能按月份 | 任意字段都能筛 |
| 能否直接做透视表 | 不能 | 能 |
| 角色 | 透视表的输出 | 透视表的输入 |
空行会改变 Excel 对边界的判断。插入透视表时默认选当前区域;中间一整行空白, 区域在这里断开,后面的数据不进数据源。手动拉到最后,空行也会变成“(空白)”分组。
小计行、合计行更常见。它们是汇总结果,不是明细;留在源数据里会被当成真实订单 再加一遍,总额直接翻倍。
如果不这样会怎样:336 条记录第 200 条空着,Ctrl + A 只选到 199 条,后面 136 条全丢,总额可能少四成。
合并单元格只保留左上角一个值,其余是空。地区列每四行合并成“华南”, 透视表只认第一行,后三行进空白组。多级表头同理:第一行当字段名,第二行当数据, 字段名变成“列1、列2”,没法拖。
如果不这样会怎样:华南原本 3,094,471.53,合并后只剩第一行金额, 剩下三行的钱全进空白组,实际是上百万销售额消失。
目标:一张“地区 × 月份”的销售额透视表,加一个渠道切片器;最后改一个源数据, 看看刷新前后差多少。约 40 分钟。
| 项目 | 要求 |
|---|---|
| 软件 | Excel 2016 及以上,或 WPS 表格(菜单名略有差异) |
| 数据 | sales.xlsx:336 行、6 列,2024-01-01 至 2024-06-27 |
| 字段 | 订单日期 / 地区 / 品类 / 渠道 / 销售额 / 数量 |
| 交付 | 销售透视表_姓名.xlsx,含透视表和切片器 |
地区 × 月份 + 渠道切片器
4 个地区 × 6 个月,总计 11,733,617.19;切片器可切线上 / 线下。
sales.xlsx 复制为 sales_练习.xlsx。
第 7 步要手动改 E2,副本方便撤回。
每步四段:做什么 → 为什么 → 怎么做 → 看到什么。“为什么”对应 8.1 的规则和 8.2 的铁律。
求和不对 → 回第 6 章转数值;提示替换 → 点确定;分列后还有靠左的 → 单独选中那几格再分列。
销售明细!$A$1:$F$3372024-01-01,终止 2024-06-30,步长勾 月,取消年、季度0.00%显示“计数项” → 销售额是文本,选中列 → 数据 → 分列 → 完成;占比显示 0.2637 → 数字格式设成 0.00%;总计不是 100.00% → 检查是不是做成了计算项。
切片器没反应 → 右键 → 报表连接,勾上这张透视表;要多表联动 → 它们必须共用同一缓存。
刷新后没变 → 确认点的是透视表;检查“公式 → 计算选项”是不是手动;追加了新行 → 刷新不够,要更改数据源。
销售透视表_姓名.xlsx| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | 地区 | 1月 | 2月 | 3月 | 4月 | 5月 | 6月 | 总计 |
| 2 | 华东 | 567,507.72 | 613,397.60 | 273,578.31 | 384,928.03 | 588,203.76 | 476,255.80 | 2,903,871.22 |
| 3 | 华北 | 695,977.40 | 407,672.18 | 436,250.28 | 573,211.80 | 537,272.26 | 376,460.94 | 3,026,844.86 |
| 4 | 华南 | 597,072.94 | 303,652.90 | 445,732.18 | 482,808.90 | 715,636.95 | 549,567.66 | 3,094,471.53 |
| 5 | 西南 | 568,293.16 | 440,534.59 | 371,548.74 | 474,203.97 | 508,832.61 | 345,016.51 | 2,708,429.58 |
| 6 | 总计 | 2,428,851.22 | 1,765,257.27 | 1,527,109.51 | 1,915,152.70 | 2,349,945.58 | 1,747,300.91 | 11,733,617.19 |
先对四个数,再做五道题。四个数对不上,别往下走——透视表算错了也不报错。
按“现象 → 原因 → 解决”查。前三条是高频错误。
| 现象 | 原因 | 解决 |
|---|---|---|
| 值区域显示“计数项:销售额”,总计是 336 | 销售额列是文本型数字,Excel 只能计数 | 选中整列 → 数据 → 分列 → 完成;刷新透视表 |
| 源数据追加了行,刷新后总计没变 | 数据源范围仍是 A1:F337,新行在范围外 | 透视表分析 → 更改数据源 → 选到新末行 → 刷新;或 Ctrl+T 转表格 |
| 右键日期没有“组合”,或“组合”是灰色 | 日期列是文本;或列内有空白 | 数据 → 分列 → 日期 YMD;补空值或删空行后刷新 |
| 组合后出现“年/季度/月”三层,列太多 | Excel 自动组合了日期层级 | 把“年”“季度”拖出列区域,只留“月” |
| 切片器点了没反应,或只筛一张表 | 切片器没有连接到目标透视表 | 右键切片器 → 报表连接 → 勾选;多表要共用同一缓存 |
| 计算字段算出来和逐行算再平均不一样 | 计算字段在汇总值上运算,不是逐行 | 明确口径;要逐行平均就在源数据加辅助列,再对辅助列求平均 |
| 两个透视表刷新不同步 | 缓存不同,或只刷新了当前表 | Ctrl+Alt+F5 刷新全部;用同一数据源或表格共享缓存 |
| 总计和明细 SUM 对不上 | 源数据有空行、合并、范围缺行,或透视表还有筛选 | Ctrl+End 查范围;取消合并;清除筛选;更改数据源 |
| 数字显示 #### | 列宽不够 | 拉宽列,或双击列边界 |
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 转表格,再刷新。