GrowbitLab学习笔记04｜数据透视表 + Power Query：让 Excel 真正起飞

GrowbitLab学习笔记04｜数据透视表 + Power Query：让 Excel 真正起飞

真正拉开数据分析效率差距的，从来不是"会不会写函数"，而是"会不会用数据透视表和 Power Query 做自动化处理"。

📖 前言

学完上一篇文章里讲的公式、函数、引用、数据类型之后，基础的 Excel 操作已经够用了。

但说实话，真正让我对 Excel 彻底改观的，不是函数，而是数据透视表和 Power Query。

当你面对一张几万行的表格，需要按月份、部门、品类分别统计销售额，还要生成交叉报表的时候——

如果还用 SUMIF 一个一个去算，光公式就能写到你怀疑人生。

而数据透视表，几个拖拽动作就能搞定。

🚀 为什么数据透视表是 Excel 的"分水岭"

在实习和实际工作里，我观察到一个很有意思的现象：

能不能熟练使用数据透视表，往往是 Excel 水平的一道明显分界线。

会用的人，几秒钟就能从几万行数据里拉出一份清晰的多维度汇总。

不会用的人，可能还在手动筛选、复制、粘贴、手动求和、换着公式往上堆——耗时还是其次，关键是容易出错，而且难以追溯。

数据透视表解决的核心问题是：

原始明细数据（几万行）
↓
按维度自动归类、汇总、计算
↓
多角度交叉分析
↓
动态调整布局，一键更新
↓
生成可交互的分析报表

它最厉害的地方在于：不需要写一行公式，就能完成绝大多数汇总分析场景。

📌 数据透视表到底是什么？

如果只用一句话来概括：

通俗地讲，数据透视表就是把一张密密麻麻的明细表，变成一张按你想要的维度自动汇总的"可视化报表"。

举个例子：你有一张销售记录表，包含日期、地区、产品、销售额、数量。你想知道——

• 每个月的销售总额是多少？
• 每个地区的销售额占比？
• 每个产品在不同地区的销售情况？

用数据透视表，这些问题都能在几十秒内得到答案。

⭐ 数据透视表四大核心区域

要真正用好数据透视表，必须先理解它的四个核心区域。这四块搞清楚了，后面怎么拖拽都不会乱。

① 行标签（Rows）

决定按什么字段"分行显示"。比如按"月份"分行，每一行就是一个月。

② 列标签（Columns）

决定按什么字段"分列显示"。比如按"地区"分列，每一列就是一个地区。

③ 值区域（Values）

你要统计什么数据，放在这里。比如"销售额求和"、“订单数量计数”、"平均单价"等。

④ 筛选器（Filters）

对整个透视表做全局筛选。比如只看"2025 年"的数据，或者只看"张三"负责的客户。

🔢 数据透视表的五种核心计算方式

很多人以为数据透视表只能求和，其实它的计算方式远不止这一种。选对计算方式，同一份数据能讲出完全不同的故事。

我们用一张销售明细表来举例，字段包括：日期、地区、销售员、产品、销售额、数量。

① 求和（SUM）

最常用的方式，统计数值总和。在值区域拖入"销售额"，默认就是求和。

实际操作：

• 行标签 → 拖入"地区"
• 值区域 → 拖入"销售额"（默认求和）

立刻得到各地区的销售总额。再把"产品"拖到列标签，就变成地区×产品的交叉求和表。

常见花样：

不只是简单求和，还可以做占比分析——右键值区域 → “值显示方式” → “列汇总的百分比”，每个地区占总销售额的比例就出来了。

还可以做累计求和——“值显示方式” → “按某一字段汇总”，选择按日期累计，就能看到销售额随时间的累计增长曲线。

实战组合：各地区销售额 + 占总销售额百分比 + 累计销售额
→ 一张表同时看到"卖了多少""占比多少""增长趋势"

② 计数（COUNT）

统计非空单元格的数量。这个看起来简单，但用好了能发现很多隐藏信息。

实际操作：

• 行标签 → 拖入"销售员"
• 值区域 → 拖入"销售员"（默认会变成"计数"）

每个销售员的成交笔数就出来了。注意，这里计数的是订单行数，不是客户数。

常见花样：

想知道每个销售员服务了多少个不同客户？需要先把数据整理成"一行=一个客户+销售员"的结构，再做计数。或者用 Power Query 先去重，再加载到透视表。

计数还可以帮你发现异常——某个销售员的订单数突然暴涨，可能是刷单；某个地区的订单数骤降，可能是渠道出了问题。

实战组合：各销售员成交笔数 + 各产品被订购次数
→ 发现谁最活跃、哪个产品最受欢迎

③ 平均值（AVERAGE）

计算数值的平均水平。均值经常被误用，但配合透视表用好了，能看出很多趋势。

实际操作：

• 行标签 → 拖入"产品"
• 值区域 → 拖入"销售额"，右键 → “值汇总依据” → 改为"平均值"

每个产品的平均订单金额就出来了。

常见花样：

均值最怕被极端值拉偏。一个大单 100 万，九个小单 1000 均，均值直接被拉到 10 万+，完全不代表真实水平。

所以实战中，均值经常要配合中位数或计数一起看——在同一个值区域拖入两次"销售额"，一个设为平均值，一个设为计数，就能同时看到"平均多少"和"有多少笔"，交叉验证数据是否可靠。

实战组合：平均订单金额 + 订单笔数 + 最大值
→ 三个指标一起看，才能判断"平均"是否可信

④ 最大值 / 最小值（MAX / MIN）

找出分组中的极端值。在汇报和复盘场景中非常实用。

实际操作：

• 行标签 → 拖入"销售员"
• 值区域 → 拖入两次"销售额"，一个设为最大值，一个设为最小值

每个销售员的单笔最高和最低业绩就一目了然。

常见花样：

最大值常用来做标杆分析——谁的单笔最高？哪个地区出过大单？这在销售复盘中非常有用。

最小值常用来做风险排查——最低库存是多少？最小订单金额是否低于成本线？

还可以在透视表上加条件格式：最大值标红、最小值标蓝，一眼看出极端情况。

实战组合：各销售员 MAX + MIN + 平均值
→ 看出谁的业绩波动大、谁最稳定

⑤ 百分比

按比例展示数据分布。这是透视表里最强大也最容易被忽略的功能。

实际操作：

• 行标签 → 拖入"地区"
• 列标签 → 拖入"产品"
• 值区域 → 拖入"销售额"
• 右键值区域 → “值显示方式” → 选择"行汇总的百分比"或"列汇总的百分比"

常见花样：

透视表的百分比有好几种模式，每种讲的故事不一样：

"总计的百分比" → 各地区各产品占整体的比例（市场全景）
"行汇总的百分比" → 同一地区内各产品的占比（产品结构）
"列汇总的百分比" → 同一产品在各地区的分布（区域分布）
"父行汇总的百分比" → 嵌套维度下，子项占父项的比例

比如用"行汇总的百分比"看华北地区，发现 A 产品占 60%、B 产品占 40%，说明华北以 A 产品为主。换成"列汇总的百分比"看 A 产品，发现华北占 45%、华南占 35%，说明 A 产品的主力市场在北方。

同一个透视表，换个百分比模式，分析角度完全不同。

📍 条件统计进阶：透视表 + 条件函数的组合拳

上一篇文章我们学了 COUNTIF、SUMIF、AVERAGEIF 这些条件统计函数。但当需求变复杂时，单纯用函数就有点力不从心了。

什么时候用函数，什么时候用透视表？

实战组合技巧

很多时候，最好的方式不是二选一，而是透视表出汇总结果，函数做进一步加工。

典型场景：用 GETPIVOTDATA 从透视表取数

假设你做了一个数据透视表，汇总了各地区的销售额。现在你想在另一个单元格里引用"华北地区的销售额"去做进一步计算，直接引用单元格（比如 =B3）有个问题——透视表布局一变，B3 的数据可能就不是华北了。

这时候用 GETPIVOTDATA() 更稳定：

=GETPIVOTDATA("销售额",$A$1,"地区","华北")

含义：从透视表中提取"地区=华北"的"销售额"值
即使透视表行列调换了位置，这个公式依然能正确取到华北的数据

更进阶的组合用法：

透视表汇总出各产品销售额
↓
GETPIVOTDATA() 提取特定产品的数据
↓
用 IF / VLOOKUP 做条件判断：达标了吗？差多少？
↓
用 TEXT 格式化成汇报用的文字："本月华北销售额 128 万，完成率 106%"

透视表 + 条件格式的组合：

在透视表结果上直接加条件格式，效果非常好：

• 销售额 > 目标 → 绿色加粗
• 销售额 < 目标的 80% → 红色预警
• 完成率 100% 旁边自动打 ✓

这样做的好处是：透视表刷新后，条件格式自动跟着更新，不需要每次手动标色。

原始数据
↓
数据透视表 → 按维度汇总
↓
GETPIVOTDATA() 提取透视表结果
↓
条件函数进一步计算
↓
条件格式 + 图表展示

这种组合方式，既有透视表的灵活汇总能力，又有函数公式的精确计算能力。

📊 图表与数据可视化：让数字自己说话

数据汇总做完之后，下一步就是让数据"可视化"——让不懂数据的人也能一眼看懂你想表达什么。

Excel 图表的核心不是"好看"，而是"准确"

很多人在做图表的时候，第一步就去调颜色、调字体、加阴影。

但图表真正的核心，永远是选择合适的图表类型来准确表达数据的含义。

常见图表类型与适用场景

柱形图 / 条形图
→ 适合：不同类别之间的数值对比
→ 示例：各部门销售额对比、各产品销量排名

折线图
→ 适合：随时间变化的趋势
→ 示例：过去 12 个月的销售趋势、日活跃用户变化

饼图 / 圆环图
→ 适合：各部分占总体的比例
→ 示例：各品类市场占比、预算分配比例

散点图
→ 适合：两个变量之间的相关性
→ 示例：广告投入与销售额的关系、价格与销量的关系

组合图
→ 适合：在同一张图里展示不同量纲的数据
→ 示例：销售额（柱形）+ 增长率（折线）

图表制作的三个原则

① 先选对类型，再调细节

图表类型选错了，怎么调都白费。对比用柱形图，趋势用折线图，占比用饼图——这是最基本的规则。

② 减少"图表噪音"

不必要的网格线、过多的数据标签、花哨的 3D 效果——这些都会分散注意力，干扰读者理解数据。好的图表永远是简洁的。

③ 一个图表只讲一个故事

不要在同一个图表里塞太多信息。如果需要传达多个结论，就做多张图表。一张图一件事，读者才能快速理解。

🔋 Power Query 基础：从"手动清洗"到"自动化处理"

如果说数据透视表改变了"汇总分析"的方式，那 Power Query 改变的就是"数据清洗"的方式。

什么是 Power Query

Power Query 是 Excel 内置的数据获取与清洗工具。它的核心理念很简单：

你只需要做一遍清洗操作
↓
Power Query 自动记录每一步
↓
下次数据更新时，自动重复所有步骤
↓
不需要重新清洗

这意味着：你花 10 分钟写好清洗流程，之后每次更新数据，只需要点一下"刷新"，所有清洗操作自动完成。

为什么 Power Query 这么重要

回忆一下，在没有 Power Query 之前，数据清洗通常是这样的：

打开 CSV 文件
↓
删除多余的行和列
↓
调整日期格式、数字格式
↓
处理空值和异常值
↓
拆分或合并列
↓
保存为新的 Excel 文件
↓
下次数据更新 → 从头再来一遍

而有了 Power Query：

连接数据源（CSV、数据库、Web、Excel 等）
↓
在 Power Query 编辑器里完成所有清洗步骤
↓
加载到 Excel
↓
下次数据更新 → 点"刷新"，全部自动完成

Power Query 能做什么

① 数据导入

支持从 Excel 文件、CSV、TXT、数据库（SQL Server、MySQL 等）、Web 页面、文件夹批量导入等多种来源获取数据。

② 数据清洗

• 删除空行、重复行
• 拆分列、合并列
• 更改数据类型
• 替换、筛选、排序
• 添加自定义列（用 M 语言或界面操作）
• 数据分组与聚合

③ 数据合并

• 追加查询：把多个结构相同的表上下拼接
• 合并查询：类似 VLOOKUP 的多表关联，但更灵活、更稳定

④ 自动化处理

所有操作步骤都会记录在"应用的步骤"里，随时可以修改、删除、调整顺序。数据源更新后，一键刷新即可。

Power Query vs 传统手动清洗

🔗 数据透视表 + Power Query：黄金组合

当数据透视表和 Power Query 配合使用时，Excel 的能力上限会被大幅拉高。

典型的自动化分析流程

数据源（多个 CSV / 数据库 / Web 数据）
↓
Power Query 连接 → 自动清洗 → 自动合并
↓
加载到 Excel 数据模型
↓
数据透视表 → 多维度汇总分析
↓
图表可视化
↓
定期更新 → 一键刷新，全流程自动完成

为什么这个组合这么强

① 数据清洗自动化（Power Query）

不管你从哪里拿到的数据，格式多乱、字段多杂，Power Query 都能把它们整理成统一的结构。

② 多表关联（Power Query 合并 + 数据模型）

不再需要写 VLOOKUP 去一张张表关联，Power Query 的合并查询可以在清洗阶段就把多张表关联好。

③ 灵活汇总（数据透视表）

清洗好的数据，用数据透视表做交叉分析，怎么切维度都方便。

④ 自动更新（刷新）

数据源变了？点一下刷新，整个清洗 + 汇总 + 图表全部自动更新。

🎯 实战中真正拉开差距的几个细节

① 数据源规范：源头对了，后面才顺

Power Query 里建立数据连接时，尽量用相对路径或网络路径，避免文件移动后连接失效。给每个查询和步骤起一个清晰的名字，比默认的"查询1""查询2"好维护得多。

② 数据透视表刷新

数据源变了，数据透视表不会自动刷新——需要右键"刷新"，或者用"全部刷新"一次性更新所有透视表和查询连接。

③ 切片器和日程表

在数据透视表里加上切片器（按字段筛选）和日程表（按时间筛选），点一下就能切换分析视角，比下拉筛选直观得多。

④ GETPIVOTDATA 函数

当你需要引用透视表中的某个汇总结果作为其他公式的输入时，GETPIVOTDATA() 函数是标准做法——它比直接引用单元格位置更稳定，透视表布局调整后不容易出错。

基本语法：
=GETPIVOTDATA("字段名", 透视表引用, "筛选字段1", "筛选值1", …)

实际例子：
=GETPIVOTDATA("销售额",$A$1,"地区","华北")
→ 提取华北地区的销售额

=GETPIVOTDATA("销售额",$A$1,"地区","华北","产品","A产品")
→ 提取华北地区A产品的销售额

小技巧： 在透视表里直接点击某个单元格，Excel 会自动生成对应的 GETPIVOTDATA() 公式。不用手写，点一下就行。

⑤ 数据透视图

数据透视表 + 图表 = 数据透视图。透视表怎么变，图表就跟着变。在做动态仪表盘（Dashboard）时非常好用。

🤖 AI + 数据透视表 & Power Query：效率再翻倍

在上一篇文章里，我聊到了 AI 怎么辅助学习 Excel 函数。到了数据透视表和 Power Query 这一层，AI 能帮的忙更多了。

AI 能帮你做什么

① 帮你选择合适的分析维度

把数据结构告诉 AI，让它建议怎么设计透视表布局。

"我有一张销售表，字段包含：日期、地区、产品、销售额、数量、销售员。
我想分析各地区的销售趋势和产品结构，怎么设计数据透视表？"

② 帮你理解 Power Query 的 M 语言

界面操作不能满足需求的时候，让 AI 帮你写 M 语言公式。

"在 Power Query 里，我想根据'订单金额'列新增一列'金额等级'，
金额 < 1000 为'小额'，1000-5000 为'中额'，> 5000 为'大额'。
帮我写 M 语言的公式。"

③ 帮你排查数据透视表问题

透视表结果不对、数据刷新后异常、汇总值不符合预期——把问题描述给 AI，它能帮你快速定位原因。

④ 帮你优化图表选择

不知道用什么图表合适？把数据特点和想表达的结论告诉 AI，让它推荐最佳图表类型和设计建议。

⑤ 帮你设计 Power Query 清洗流程

面对一张脏数据不知道从哪里下手？描述数据结构给 AI，让它帮你规划清洗步骤。

💼 实习中最真实的感受

实习越久，越体会到一件事：真正的数据分析工作，80% 的时间花在数据处理上，只有 20% 的时间在做真正的"分析"。

而这 80% 里，又有一大半是在做重复性的清洗和汇总。

数据透视表和 Power Query 的价值，就是把那些重复、耗时、容易出错的工作自动化掉。

以前可能需要花一下午才能做好的周报，用 Power Query 搭好清洗流程 + 数据透视表搭好分析框架之后，数据一更新，几分钟就能出结果。

这省出来的时间，才是真正可以投入到业务理解和深度分析上的时间。

🌱 我的学习感悟

这四篇学习笔记写下来，我对"学工具"这件事的理解已经完全不一样了。

最初的心态是：“多学几个工具，技多不压身。”

现在的心态是：“学工具的目的不是囤积技能，而是解决实际问题。”

Markdown → 让写作有结构
Mermaid → 让逻辑可视化
Excel 基础 → 让数据可计算
数据透视表 → 让汇总分析变快
Power Query → 让数据清洗自动化

每一层解决一层的问题，层层叠加，最终构成一套完整的数据处理能力。

而且我发现一个有意思的规律：越是基础的工具，组合使用后的威力越大。

单独看数据透视表，只是一个汇总功能。单独看 Power Query，只是一个清洗功能。但把它们和 Excel 基础公式、图表功能组合在一起，就是一套可以处理绝大多数日常分析需求的"轻量级数据分析平台"。

📢 你在工作或学习中用过数据透视表和 Power Query 吗？有没有被重复性数据清洗折磨过的经历？欢迎在评论区聊聊你的故事。

🚀 下期预告

GrowbitLab学习笔记05｜从 Excel 到 Python：数据分析的下一个台阶

继续结合真实工作场景，深入整理：

• 什么时候该从 Excel 切换到 Python
• pandas 与 Excel 的对比学习路径
• 用 Python 自动化处理 Excel 文件
• 实战：批量处理多个 Excel 工作簿
• AI + Python 数据分析实战技巧

欢迎关注 GrowbitLab。

一起记录成长，一起见证进步。