🔄 第06讲 · 库存汇总报表：认识数据透视表

📌 阶段二 · 数据管理 · 第6讲 认识数据透视表 · 约 45 分钟

用透视表把出入库流水拖成进销存月报：按月汇总、按供应商下钻

学习目标

• 理解透视表四大区域：报表筛选、行、列、值各管什么
• 会三步创建透视表并拖字段出结果
• 会更改值字段的汇总方式并重命名
• 会把日期组合成月/季度/年，知道组合失败的常见原因
• 会用报表筛选页按供应商快速拆分报表

知识点讲解

数据透视表：汇总流水的「外挂」

一句话说透：数据透视表就是把一张流水表按你指定的维度重新摆一遍并自动汇总。流水是「一天一行行记录」，透视表能秒变「一个月一行合计」。

先认识四个专业名词（它们就是原始表格里的列标题，也叫字段名称）：

报表筛选：拖进来的字段变成整张透视表的总开关，选谁就看谁的数据；

行：想在行方向上摆什么维度，就把它拖进行区域；

列：想在列方向上摆的维度；

值：要算什么数（金额、数量），有求和、计数、平均值、最大值等多种汇总依据。

三步创建第一张透视表

第一步：点流水表数据区任意一个单元格（不用选区域，Excel 自动识别整表）；

第二步：【插入-数据透视表】，确认数据区域，选「新工作表」，确定；

第三步：在右侧字段列表里把字段拖进行/列/值区域，结果立刻出来。

习惯建议：点中透视表后，在【数据透视表选项-显示】里勾选「经典数据透视表布局」，字段可以直接往表格里拖，更直观（可做可不做，看个人习惯）。

💡 提示:

• 数据源首行必须是字段名（表头），且不能有合并单元格——第 3 讲清洗的功夫在这兑现

更改汇总方式与字段名

数值字段默认「求和」。想改？双击值字段表头（比如「求和项:金额」），在值字段设置里换成计数、平均值、最大值、最小值等。也可以右键-值字段设置。

字段名可以改：双击后在「字段名称」里改名，或在编辑栏里直接改，但注意不能与原有字段名重复（比如原来就有「金额」，新名可以叫「入库金额合计」）。

彩蛋：双击透视表里的任何一个汇总数值，Excel 会自动生成一张新表，列出这笔汇总背后的全部明细记录——查账利器。

日期组合：从 365 行到 12 行

把「日期」拖进行区域，默认按天显示，一年 365 行没法看。救星是组合：在任一日期单元格上右击-组合，统计维度可以选月、季度、年（可多选，如同时按年和月）。确定后流水立刻折叠成 12 个月一行的月报——这就是进销存月报的核心动作。

大坑预警：如果日期列里混有空单元格或者「2024.1.2」这种文本日期，组合会直接失败报错。这就是第 2 讲反复强调日期必须是真日期的原因。组合失败先回去查日期列。

💡 提示:

• 一级字段放行区域靠前的位置，二级字段放后面，层级才对

数值区间组合：看进货批量分布

组合不只是日期的专利。把「数量」既拖到行区域又拖到值区域（值区域选求和），然后在行标签的任意数量上右击-组合，设定起止值和步长（比如从 0 到 1000、每 100 一档），透视表就变成分布统计：0100 的单子几笔、100200 的几笔……分析采购批量大小用它。

多值字段与表格布局

想一次看三个数？把「金额」拖进行值区域三次，分别改成求和、平均值、计数（双击改）。如果多个值字段上下挤成一列，可以把值字段标题拖到「汇总」那一列上变成并排（不同版本显示不同，有的本来就并排）。

布局美化：点透视表任意单元格，顶部出现「设计」选项卡：报表布局选「以表格形式显示」，行字段左右并排更像正常表格；如果行字段之间出现了小计，用「设计-分类汇总-不显示分类汇总」隐藏，或双击该行字段把分类汇总设为「无」。

套样式：设计选项卡里挑一个喜欢的透视表样式，系统报表统一风格。

计算字段：透视表里自己造指标

透视表不仅能汇总现有列，还能用现有字段算新指标。入口：【数据透视表分析（或选项）-字段、项目和集-计算字段】。比如数据里没有「单笔均价」，就插入计算字段：名称写「件均单价」，公式写 = 金额/数量，确定后透视表多出一列自动算好。

美化：选中该列右键设置单元格格式改两位小数或百分比；如果公式遇到分母为 0 报错，在【数据透视表选项】里勾选「对于错误值，显示为空白」，错误就不刺眼了。

报表筛选页：一键按供应商拆表（选学）

把某字段（如供应商编号）拖入报表筛选区域，透视表顶部出现下拉，可以只看该供应商的数据。

再进一步：点【数据透视表分析-选项-显示报表筛选页】，选供应商编号字段，Excel 会按每个供应商自动生成一张独立工作表，每张表的透视表已自动筛好该供应商——8 家供应商瞬间拆成 8 张表。

这些自动生成的表上还带着透视表，若只想要纯数据：按住 Shift 选中所有新表，复制一片空白区域粘贴上去覆盖，透视表就变成了普通数据。

💡 提示:

• 显示报表筛选页适合按客户拆对账单，配合第 13 讲邮件合并更好用

实战演练

流水规范了，该出报表了。本讲实战用数据透视表拖出「进销存月报」雏形：入库月报看每月进货金额与笔数，出库月报看每月销售，再用报表筛选一键切换查看某个供应商的月度进货——这就是期末成品「销售分析」表的前身。

1. 创建入库月报透视表 — 点入库单流水区任意单元格-插入-数据透视表-新工作表，把新工作表重命名为「入库月报」。先把「日期」拖到行区域，「金额」和「单号」拖到值区域。
2. 改汇总方式并改名 — 双击「求和项:金额」确认为求和并改名为「进货金额」；双击「求和项:单号」把汇总方式改成「计数」，改名为「入库笔数」。现在每月一行：金额和笔数都有了。
3. 日期组合到月 — 在行标签的任一日期上右击-组合，选中「月」（可以顺带选「年」），确定。365 行折叠成 12 个月。如果报错「选定区域不能分组」，回去检查日期列是否混入了空白或文本。
4. 调整布局与样式 — 设计-报表布局-以表格形式显示；设计-分类汇总-不显示分类汇总；再挑一个报表样式。看起来像张正经报表了。
5. 同法做出库月报 — 出库单流水重复刚才四步，新表命名「出库月报」，值区域放金额（求和，改名「销售金额」）和数量（求和，改名「销售数量」），日期组合到月。两张月报并排放，进销存月度全貌出来了。
6. 报表筛选按供应商下钻 — 回入库月报，把「供应商编号」拖到筛选区域。点 B1 的下拉选 GYS001，整张月报立刻只显示该供应商的月度进货——老板问「宏达这家今年每月进多少货」，三秒作答。
7. （选做）计算字段与数量分布 — 练习一：字段、项目和集-计算字段，名称「件均单价」、公式 =金额/数量，看看每月进货的平均单价走势。练习二：把「数量」拖进行+值区域，行标签右击组合，步长 100，看进货批量分布。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

配套练习文件:lecture-06-extra-1.xlsx、lecture-06-extra-2.xlsx、lecture-06.xlsx(见本页底部「附件下载」)。

验收清单

逐项自查,全部通过即完成本讲:

• ☐ 入库月报、出库月报两张透视表已建好并重命名
• ☐ 值字段会改汇总方式（计数）并重命名，没有与原字段重名
• ☐ 日期已组合为月，12 个月一行不缺
• ☐ 报表布局为表格形式，多余分类汇总已隐藏
• ☐ 报表筛选可以切换供应商查看月度进货
• ☐ 能说出日期组合失败的两个原因：空白、文本日期