今日学习目标
- 会从一张明细表插入数据透视表
- 理解行、列、值、筛选四个区域的分工
- 会改值字段的汇总方式(求和/计数/平均)
- 知道源数据变化后如何刷新
知识点
1. excel 数据透视表教程:解决什么问题
一千行销售明细,要按地区看销售额、按业务员数订单、按月份看趋势。用函数得套 SUMIF 甚至多条件组合;透视表不用写一个公式——拖字段,Excel 当场汇总。它和 VLOOKUP 是 Excel 数据处理的两大支柱:一个负责"取",一个负责"汇"。
2. 源数据三条规矩
- 第一行是标题,每列一个字段且不重名
- 区域内没有合并单元格、没有整行空行
- 一行一条记录,不夹"小计"行(小计会让汇总翻倍)
先把源数据规范好,再做透视表——新手少走弯路的关键一步。
3. 四个区域怎么分工
选中数据 → 插入 → 数据透视表,右侧字段列表往四个区域拖:
- 行:分类维度,如"地区"
- 列:第二个维度,如"季度",可以空着
- 值:被统计的数字,如"销售额",默认求和
- 筛选:整表过滤,如"年份"
同一个字段可以拖到多个区域;单击值区域里的字段 → "值字段设置",可改成计数、平均值、最大值。
4. 刷新与扩展
源数据改了,透视表不会自己动:在透视表上右键 → 刷新(快捷键 Alt+F5)。数据追加新行后,用"更改数据源"扩大范围;更省事的做法是把源数据先按 Ctrl+T 转成表格,追加行会自动进统计范围。
今日代码
透视表没有公式,布局用一张示意图记住:
源:1000 行明细(日期/地区/业务员/销售额)
│ 插入透视表
├─ 行:地区
├─ 列:(空)
├─ 值:销售额(求和) → 每个地区一行合计
└─ 筛选:年份把"业务员"拖进行区域替掉"地区",立刻变成按人汇总;把"业务员"同时拖到行和值(值字段设为计数),就是每人的订单笔数。改布局只需拖一下,这是透视表比函数快的原因。
练习题
- 用透视表统计每个地区的销售总额。
- 在第 1 题基础上加列维度"季度",做出地区×季度交叉表。
- 统计每个业务员的订单笔数(不是金额)。
习题解答
- 行拖"地区",值拖"销售额",默认求和即总额。
- 列区域拖入"季度",行仍是"地区",值不变。
- 行拖"业务员",值拖"订单编号"并把值字段设置改为计数——对文本计数,数的是非空个数。
实战场景
背景:运营每月收到 3000 行渠道流水,要给老板一页汇总:各渠道销售额、各渠道订单数、Top 渠道。建三个透视表放同一张汇总页:① 行=渠道、值=销售额;② 行=渠道、值=订单编号(计数);③ 把 ① 的结果降序排序,看第一行。以后每月把新流水粘进源表,按 Alt+F5 刷新三次,一页报表就绪——比手写三十个 SUMIF 快,后期也好维护。
常见错误与排查
| 报错/现象 | 原因 | 解决方法 |
|---|---|---|
| 提示"字段名无效" | 源数据有空列标题或合并单元格 | 补齐标题,取消合并 |
| 新增行没进统计 | 数据源范围没扩展 | 更改数据源;或源表先 Ctrl+T 转表格 |
| 汇总数字异常翻倍 | 源数据里混着"小计"行 | 删掉源表小计行再刷新 |
| 数字被计数而不是求和 | 值列混有文本(空格、单位) | 清洗源数据列,值字段改回求和 |
| 字段列表不见了 | 点到了透视表外面 | 点回透视表内任意单元格 |
延伸练习
- 把"销售额"同时拖进值区域两次:一个求和、一个求平均,对比两列数字各说明什么。
- 设计选项卡 → 报表布局 → 以表格形式显示,对比压缩布局,看哪种更适合打印和截图汇报。
学习总结
透视表三步:规范源数据 → 插入并拖字段 → 改完刷新。行管分类、列管交叉、值管数字、筛选管范围;文本混进值区域会变成计数,是新手最常撞见的"异常"。下一课给汇总表加自动报警:条件格式。
由在线工具箱(www.vba.net)整理制作