Excel · 第 2 篇

透视表与数据汇总

如果只能学 Excel 的一个功能，那一定是透视表。它能在 10 秒内完成你用公式写 30 分钟才能完成的分析。

透视表（Pivot Table）是 Excel 中最强大、也是数据分析师使用频率最高的功能。它能让你用拖拽的方式，快速完成分组统计、交叉分析、多维汇总等操作。

什么是透视表

透视表的本质是一个动态的分组汇总工具。你把字段拖到不同区域，Excel 就自动帮你按照指定维度聚合数据。

它和 SQL 的 GROUP BY 功能非常类似：

创建第一个透视表

准备数据

透视表要求数据是标准的表格格式：

• 第一行是列名（字段名），不能有合并单元格
• 每一行是一条记录
• 没有空行、空列
• 数据类型一致（同一列不能既有数字又有文本）

示例数据——电商订单表：

创建步骤

1. 选中数据区域：点击数据区域任意单元格，然后 Ctrl + A 选中全部
2. 插入透视表：插入 → 数据透视表 → 选择”新工作表”或”现有工作表”
3. 拖拽字段：在右侧的字段列表中，把字段拖到四个区域

透视表的四个区域

基础操作实战

场景一：按城市统计总销售额

操作：

• 行：城市
• 值：销售额（求和）

结果：

这就是最简单的分组汇总，等价于：

SELECT city, SUM(amount) AS total_amount
FROM orders
GROUP BY city
ORDER BY total_amount DESC;

场景二：按城市 + 品类交叉分析

操作：

• 行：城市
• 列：品类
• 值：销售额（求和）

结果：

这就是交叉分析——同时按两个维度汇总，能快速发现”哪个城市的哪个品类卖得好”。

场景三：按月统计订单量趋势

操作：

• 行：日期（自动按月分组）
• 值：订单编号（计数）

技巧：在透视表中，右键点击日期字段 → 组合 → 选择”月”，Excel 会自动按月聚合。

结果：

透视表进阶技巧

技巧一：更改汇总方式

默认情况下，数值字段用”求和”，文本字段用”计数”。你可以更改为：

操作：点击值区域的字段 → 值字段设置 → 选择汇总方式。

技巧二：显示为百分比

在值字段设置中，切换到”值显示方式”选项卡：

案例：查看每个城市的品类销售占比。

行：城市 → 列：品类 → 值：销售额（显示为”行总计的百分比”）

洞察：广州的食品占比明显高于其他城市（20.5% vs 12-15%），可以针对广州用户加大食品品类的推荐力度。

技巧三：计算字段

透视表可以创建基于现有字段的计算字段。

操作：透视表工具 → 分析 → 字段、项目和集 → 计算字段

案例：创建”客单价”计算字段

客单价 = 销售额 / 数量

技巧四：使用切片器（Slicer）

切片器是透视表的可视化筛选器，比下拉筛选更直观。

操作：选中透视表 → 插入 → 切片器 → 选择字段

切片器的优势：

• 可视化：一眼看到所有选项
• 多选：按住 Ctrl 点击多个选项
• 联动：一个切片器可以同时控制多个透视表
• 美观：适合做仪表板展示

技巧五：数据透视图

透视表可以直接生成图表：

操作：选中透视表 → 插入 → 数据透视图

透视图会随着透视表的更新自动更新，非常适合做动态报表。

多透视表联动分析

在实际工作中，你经常需要从不同角度分析同一份数据。可以基于同一份数据创建多个透视表，并用切片器联动。

案例：电商销售分析仪表板

创建一个”月份”切片器，连接到所有三个透视表。当你选择某个月份时，三个透视表同时更新。

连接切片器到多个透视表：

1. 右键点击切片器 → 报表连接
2. 勾选所有需要联动的透视表

透视表常见问题与解决

问题一：数据更新后透视表没有同步

原因：透视表不会自动刷新。

解决：右键透视表 → 刷新，或者 Alt + F5 快捷键刷新。

问题二：新增的数据行没有被包含

原因：透视表的数据源范围是创建时确定的。

解决：

• 方案一：先把数据转换为”表”（Ctrl + T），表会自动扩展范围
• 方案二：手动更新数据源范围（透视表工具 → 分析 → 更改数据源）

问题三：日期没有自动分组

原因：日期列中有非日期格式的值（比如文本）。

解决：先清洗日期列，确保全部是日期格式，然后重新创建透视表。

问题四：想要去重计数但找不到

原因：标准透视表不支持 DISTINCT COUNT。

解决：创建透视表时勾选”将此数据添加到数据模型”，然后在值区域选择”非重复计数”。

实战案例：用透视表做月度销售分析

数据背景

你是一家快消品公司的数据分析师，拿到了 2025 年 Q1 的销售数据（约 5 万行），包含字段：日期、区域（华北/华东/华南/华西）、产品线（饮料/零食/日化）、渠道（线上/线下）、销售额、销售数量。

分析需求

1. Q1 整体销售趋势如何？
2. 哪个区域表现最好？
3. 各产品线的增长情况如何？
4. 线上 vs 线下的占比变化趋势？

操作步骤

分析一：月度销售趋势

• 行：日期（按月分组）
• 值：销售额（求和）、订单数（计数）

洞察：2 月受春节假期影响，销售额环比下降 12.5%，但客单价上升（礼盒装拉动）。3 月强势恢复。

分析二：区域销售排名

• 行：区域
• 值：销售额（求和）、销售额（行总计的百分比）
• 排序：按销售额降序

分析三：产品线增长分析

• 行：产品线
• 列：月份
• 值：销售额（显示为”差异百分比”，基本字段选”月份”）

洞察：饮料品类波动最大，季节性明显。零食品类最稳定。

分析四：线上 vs 线下占比趋势

• 行：月份
• 列：渠道
• 值：销售额（列总计的百分比）

洞察：2 月线上占比明显上升（春节期间线下门店休息，线上需求增加）。

透视表 vs SQL vs Python

最佳实践：用 SQL 从数据库拉出中间表 → 导出为 Excel → 用透视表做交互式分析和汇报。

练习题

1. 使用一份包含”日期、城市、产品、销售额、数量”的数据，创建一个透视表，展示各城市各月的平均客单价。

2. 创建两个透视表（一个展示月度趋势，一个展示城市排名），并用一个”产品”切片器联动控制。

3. 运营同学问你”3 月华东区域食品品类的销售额是多少”。请用透视表在 10 秒内回答。

小结

下一篇我们将学习 常用函数与公式——VLOOKUP、IF、SUMIFS 等数据分析师最常用的 Excel 函数。