Excel Power Query教程 | 数据清洗到多文件批量合并实战指南

还在每个月手动复制粘贴30个分公司的Excel月报？Power Query就是Excel内置的ETL流水线，从数据获取、清洗转换到多文件汇总，全程可视化操作，不用写一行VBA。这套教程把8大核心模块拆得明明白白——逆透视、追加查询、文件夹批量合并，全都有。

---

Power Query到底是什么东西

做过报表的人都懂那种崩溃：系统导出的Excel格式一塌糊涂，空行、合并单元格、字段混在一起，手动清理半天就没了。

Power Query本质上是一条可视化的ETL流水线——Extract（提取）、Transform（转换）、Load（加载）。它最早是Excel 2010/2013的免费插件，从Excel 2016起直接集成进”数据”选项卡，零安装成本。

最狠的一点：所有操作步骤都被记录为可复用查询。下个月数据更新了，点一下”刷新”，整套清洗逻辑自动重跑。这才是它跟手动操作的根本区别。

---

数据获取：Power Query支持的数据源清单

很多人以为Power Query只能读Excel，实际上它的数据源覆盖面相当广：

本地文件类型

Excel工作簿、CSV、TXT、JSON、XML、PDF表格——全都能直接吃进去。

数据库对接

SQL Server、MySQL、Oracle、Access、PostgreSQL，主流关系型数据库一个不落。

在线数据抓取

网页表格、SharePoint列表、OData接口、Azure服务。”从Web获取”这个功能被严重低估了——汇率、行政区划、行业统计这些公开数据，直接从网页抓取，省掉手动复制粘贴的体力活。

---

数据转换操作：日常使用频率最高的核心战场

这块是整个教程篇幅最重的部分，也是实际工作中真正救命的环节。

行列管理与条件筛选

删除空行空列、按条件筛选、保留前N行。看似简单，但在Power Query里每一步都被记录为可复用步骤，下次数据更新直接重跑。

拆分、合并与字段提取

一个字段里塞了”姓名-工号-部门”？用分隔符拆分一秒搞定。反过来，多列拼接成一个字段也只需一步合并操作。不用再写LEFT、MID、FIND这些嵌套公式。

逆透视与透视切换

这是很多人卡住的地方。业务系统导出的数据往往是”一维长表”，报表需要”二维宽表”。逆透视就是在这两种形态之间自由切换的开关——用过一次就再也回不去手动操作了。

分组依据与聚合计算

相当于SQL里的GROUP BY。按某个字段聚合求和、计数、取最大最小值，也能做嵌套分组。

日期时间处理

从日期中提取年、月、季度、星期几，计算两个日期之间的天数差，Power Query内置函数直接搞定，告别DATE、YEAR、DATEDIF的嵌套地狱。

---

多表关联与数据组合：追加查询和合并查询的区别

数据分散在多张表里时，组合功能就派上用场了：

追加查询（UNION ALL）

把结构相同的多张表上下拼接。比如12个月的销售明细表，追加成一张年度总表。

合并查询（JOIN）

按关键字段将两张表左右关联。这里提供了六种联接类型：左外部、右外部、完全外部、内部、左反、右反。

新手最常踩的坑：想找出”A表有但B表没有”的记录，该用左反联接，而不是左外部联接再手动筛空值。选错联接类型，结果集直接翻倍。

---

实测体验：30个分公司月报批量合并的真实工作流

说个我实际用过的场景。

公司每月收到30个分公司发来的Excel月报，格式完全一致，需要合并成一张总表做分析。以前用VBA写循环，代码维护成本高，换个文件路径就得改。

换成Power Query之后，操作路径是这样的：指定一个文件夹路径→自动识别文件夹内所有同格式文件→批量读取并合并→加载到数据模型。整个过程不到两分钟。

下个月新增了一个分公司？把文件扔进文件夹，点”刷新”，完事。不用改任何代码，不用动任何配置。

底层逻辑其实不复杂：Power Query在后台对文件夹执行异步遍历，逐文件解析表结构，校验列名一致性后执行追加操作。内存占用很低，30个文件、每个几千行数据，整个过程Excel界面完全不卡顿。支持Excel工作簿和CSV两种格式的批量合并。

---

数据加载方式：工作表、数据模型还是仅连接

清洗完成的数据有三种输出去向：

• 加载到工作表：直接输出为Excel表格，适合数据量不大、需要肉眼查看的场景。
• 加载到数据模型：存入内存中的Power Pivot模型，百万级数据量的分析场景用这个。
• 仅创建连接：不输出任何结果，只保留查询逻辑，适合作为中间步骤被其他查询引用。

选错加载方式会直接影响文件体积和刷新速度。数据量超过10万行，别犹豫，直接走数据模型。

---

高频实战案例：中国式排名与笛卡尔积

教程里还收录了一批业务场景的解法，挑几个高频的说：

中国式排名

分数相同时名次并列，下一名跳过（1、1、3而非1、1、2）。Power Query里需要借助分组和计数逻辑实现，比数组公式清爽得多。

笛卡尔积表生成

将两个维度表做全组合，常用于构建完整的分析框架。比如”产品×区域”的全量矩阵。

多行属性合并

用Text.Combine函数将同一ID下的多行文本合并为一个单元格，替代那些让人头皮发麻的数组公式。

---

M语言进阶：界面操作覆盖不了的那20%

Power Query背后跑的是M语言（Power Query Formula Language）。界面操作能覆盖80%的日常需求，但遇到复杂条件分支、自定义函数封装、动态参数传递时，手写M代码的灵活性是拖拽操作给不了的。

官方文档和社区论坛是进阶阶段最好的参考。不用一开始就啃语法，先把界面操作玩熟，遇到瓶颈了再去查对应的M函数，这个学习路径效率最高。

---

这套教程适合谁

符合以下任何一条，都值得花时间过一遍：

• 每周花大量时间在Excel里做重复性数据清洗
• 需要定期合并多个来源、多个文件的数据
• 想从Excel平滑过渡到Power BI，但不想一上来就啃DAX
• 受够了VLOOKUP/INDEX+MATCH的性能瓶颈

学习曲线相当友好——界面操作为主，不需要编程基础，两三天就能上手解决实际问题。真正的门槛不在于”会不会用”，而在于”知不知道它能做这些事”。