第 5 章：数据透视表¶
从"手动统计"到"拖拽分析"——Excel 最强大的数据分析引擎
数据透视表是 Excel 中最被低估的功能。它不需要任何公式，仅通过拖拽就能完成多维度的数据汇总和分析。如果你只学一章 Excel，就学这一章。
5.1 数据透视表的核心概念¶
四个区域¶
把数据透视表想象成一个"数据魔方"，你可以从任意角度观察数据：
| 区域 | 作用 | 比喻 |
|---|---|---|
| 筛选 | 过滤整个报表的数据 | 选择只看哪个年份 |
| 行 | 在行方向分组 | 每行是一个部门 |
| 列 | 在列方向分组 | 每列是一个季度 |
| 值 | 要计算的数据 | 销售额的求和/平均值 |
创建数据透视表¶
实战：从销售数据创建透视表
- 选中数据源任意单元格
- 插入 → 数据透视表
- 选择放置位置（新工作表或现有工作表）
- 在字段列表中拖拽：
- 行：部门
- 列：季度
- 值：销售额（求和）
几秒钟就完成了"各部门各季度销售额汇总"——如果用公式，需要 SUMIFS 嵌套。
5.2 值字段设置¶
汇总方式¶
| 汇总方式 | 用途 | 适用数据类型 |
|---|---|---|
| 求和 | 合计数值 | 销售额、数量 |
| 计数 | 统计条目数 | 订单数、客户数 |
| 平均值 | 计算均值 | 客单价、评分 |
| 最大值/最小值 | 极值分析 | 最高/最低销售额 |
| 百分比 | 占比分析 | 各部门销售占比 |
一个字段多次使用
同一个字段可以多次拖入"值"区域，分别设置不同的汇总方式。例如：销售额拖入三次，分别设为求和、平均值、最大值。
值显示方式¶
| 显示方式 | 说明 | 示例 |
|---|---|---|
| 总计的百分比 | 占全部总计的比例 | 各部门销售占比 |
| 行汇总的百分比 | 占该行总计的比例 | 某部门各季度占比 |
| 列汇总的百分比 | 占该列总计的比例 | 某季度各部门占比 |
| 差异 | 与基准项的差值 | 与去年同期的差异 |
| 差异百分比 | 与基准项的变化率 | 同比增长率 |
| 按某一字段汇总 | 累计汇总 | 年度累计销售额 |
5.3 分组与计算¶
日期自动分组¶
将日期字段拖入行/列区域后，Excel 会自动创建年月日层级。右键 → 分组，可以按以下粒度分组：
| 分组粒度 | 适用场景 |
|---|---|
| 秒/分/时 | 日志分析、呼叫中心 |
| 日 | 日报 |
| 月 | 月报 |
| 季度 | 季度报告 |
| 年 | 年度趋势 |
数值分组¶
对于数值字段（如年龄、金额），可以手动分组：
实战：按年龄段分组统计客户
- 将"年龄"拖入行区域
- 右键年龄列 → 分组
- 起始：0，终止：80，步长：10
- 自动生成 0-9、10-19、20-29... 等年龄段
计算字段¶
在透视表中创建基于现有字段的计算公式。
实战：添加"客单价"计算字段
- 数据透视表分析 → 字段、项目和集 → 计算字段
- 名称：客单价
- 公式：
= 销售额 / 订单数 - 确定
新字段"客单价"出现在透视表中，像原生字段一样使用。
5.4 切片器与日程表¶
切片器¶
切片器 是可视化的筛选按钮，比下拉筛选更直观。
实战：添加切片器实现交互式筛选
- 选中透视表
- 数据透视表分析 → 插入切片器
- 勾选"部门"、"产品类别"、"年份"
- 调整切片器大小和排列
点击切片器中的按钮，透视表即时更新。多选：按住 Ctrl 点击。
日程表¶
日程表 是专门用于日期筛选的切片器。
实战：添加日程表
- 选中透视表（数据源需包含日期字段）
- 数据透视表分析 → 插入日程表
- 选择日期字段
- 选择时间粒度（月/季度/年）
切片器联动¶
一个切片器可以同时控制多个透视表（前提是它们共享同一数据源）。
报表连接
右键切片器 → 报表连接 → 勾选要控制的所有透视表。一个切片器控制多个图表，实现仪表盘效果。
5.5 数据透视图¶
透视图 vs 普通图表¶
| 对比维度 | 普通图表 | 数据透视图 |
|---|---|---|
| 数据源 | 固定区域 | 透视表（动态） |
| 交互性 | 无 | 自带筛选按钮 |
| 更新方式 | 手动调整数据源 | 刷新透视表自动更新 |
| 适用场景 | 静态报告 | 交互式仪表盘 |
实战：创建交互式销售仪表盘
- 创建数据透视表（行：产品，值：销售额求和）
- 插入数据透视图（柱形图）
- 添加切片器（部门、年份）
- 再创建第二个透视表（行：月份，值：销售额求和）
- 插入数据透视图（折线图）
- 将切片器连接到两个透视表
现在点击切片器，两个图表同步更新——这就是仪表盘的雏形。
要点总结¶
- 理解数据透视表的四个区域（筛选、行、列、值）
- 掌握值字段的多种汇总方式和显示方式
- 能对日期和数值字段进行分组
- 能创建计算字段扩展透视表的分析能力
- 能使用切片器和日程表创建交互式报表
课后练习¶
- 使用销售数据创建透视表：按产品和地区两个维度汇总销售额
- 添加计算字段"毛利率"（公式：毛利/销售额），并在透视表中展示
- 创建切片器（年份、地区）和数据透视图，实现交互式仪表盘
下一章预告： 第 6 章：高级函数与数组公式 —— VLOOKUP 的终结者 XLOOKUP、INDEX-MATCH 黄金组合、动态数组函数。