Excel Power Query 自动化：把每周重复的报表变成一键刷新

Excel Power Query：把重复劳动做成刷一下就好

什么信号说明你该用 Power Query 了

• 每个月重复同一套操作：打开文件、删前几行、改列名、拆列、粘贴到总表
• 数据来源是固定的几个后台导出，格式每周都一样
• 你已经用透视表，但每次都要先手工把源数据洗一遍才能用

Power Query 的核心是：你把清洗步骤录一遍，Excel 记下来，以后每次原样重放。它记录的不是结果，是过程。

第一个例子：合并文件夹里所有结构相同的文件

这是最典型的场景——后台按天或按店导出几十个结构相同的表。

操作步骤：

1. 数据 → 获取数据 → 来自文件 → 从文件夹 2. 选择存放导出文件的文件夹 → 确定 3. 在弹出的预览里点「组合」→「合并并加载」或「合并并转换数据」 4. 选择其中一个文件作为示例 → Excel 会把它的结构应用到所有文件

之后往这个文件夹里丢新文件，回到 Excel 点「刷新」，新数据自动进来。文件名本身也可以保留：组合时勾选「文件名」列，方便追溯每一行来自哪个文件。

注意：所有文件的列名必须一致。列名不一致的话，合并后会出现大量空列，需要在 Power Query 里再清洗。

Power Query 编辑器界面怎么说人话

打开编辑器（数据 → 查询和连接 → 右键编辑）后，重点记住三块：

• 中间的表格：当前这一步的数据预览，看到的不是全部数据，是前 1000 行的样子
• 右侧「应用的步骤」：你做过的每一步都在这。点任意一步可以看回当时的数据，点错了删掉那一步即可，这是它比普通 Excel 操作安全的地方
• 左侧查询列表：一个查询就是一条流水线，可以互相引用

开始 → 导航 → 更改的类型 是每次进来的默认三步。其中「更改的类型」一定要检查：Power Query 会猜类型，猜错的（尤其是日期）要在这里改回来。

高频清洗操作对照表

| 想做的事 | 在哪 | 备注 | | --- | --- | --- | | 删掉顶部无用的标题/说明行 | 开始 → 删除行 → 删除最前面几行 | 比在源表里删更可靠，不影响原文件 | | 让第一行当表头 | 开始 → 将第一行用作标题 | 常跟上面这步连用 | | 删除某几列 | 选中列 → 右键 → 删除列 | 也可以「删除其他列」反向选择 | | 拆分一列 | 转换 → 拆分列 → 按分隔符 | 选最右边的分隔符或指定出现次数 | | 替换脏字符 | 转换 → 替换值 | 支持替换 null | | 去除空格 | 转换 → 格式 → 修整 | 相当于 TRIM，清洗第一步就该做 | | 改类型 | 列名左边图标 → 选类型 | 日期强烈建议手动确认 | | 筛选掉无用行 | 列筛头下拉 | 步骤会被记录，刷新时自动重放 | | 多个表纵向拼接 | 开始 → 追加查询 | 结构相同的表合并 | | 两个表横向关联 | 开始 → 合并查询 | 相当于 SQL JOIN |

关键技巧：逆透视（把二维表转成一维流水表）

后台经常导出这样的表：产品为行、月份为列。这种表透视表没法直接用，必须转成流水表。

1. 先选中作为标识的列（比如产品列） 2. 转换 → 逆透视列 → 逆透视其他列 3. 得到三列：产品、属性（原列名，也就是月份）、值

这一步之后，就可以按任意维度做透视分析了。这是从「能看的报表」到「能分析的数据」的分水岭。

让它在别人电脑也能跑：参数化路径

查询里写死了绝对路径的话，换台电脑就崩。改成参数：

1. 编辑器 → 开始 → 管理参数 → 新建参数，命名 数据文件夹，类型「文本」，值填你的路径 2. 在源步骤里把写死的路径替换成这个参数（在公式栏或高级编辑器里改）

以后只要改参数里这一处路径，整条流水线就能换个目录继续用。

刷新：让它自己跑

三种方式任选：

• 手动：数据 → 全部刷新（Ctrl + Alt + F5）
• 打开文件时自动刷新：查询属性 → 勾选「打开文件时刷新」
• 定时刷新：数据 → 查询属性 → 每隔 N 分钟刷新（适合挂着的看板）

注意：Power Query 的结果要「上载」到工作表才看得到。数据量大时选「仅创建连接」+「添加到数据模型」，不让明细占满表格，直接喂给透视表。

常见问题

刷新后报错「找不到文件」？ 源文件被移动或重命名了。把新文件放回原路径，或用参数化路径统一改。

步骤里有 Source 报错 DataSource.Error？ 权限问题。数据 → 获取数据 → 数据源设置 → 编辑权限，改成「组织」级别。

合并文件夹后列名带了「.1」「.2」后缀？ 不同文件的列名不完全一致（可能多了空格）。回到源 Ukraine 文件统一列名最好，或在查询里加一步统一 Text.Trim。

刷新特别慢？ 关闭「后台刷新预览」，或先把某个查询设为「仅创建连接」减少中间落地；数据量极大时考虑把数据源换成数据库。

什么时候不该用 Power Query

• 一次性处理，不会再重复：直接手工操作更快
• 需要复杂条件循环、调用外部 API：这类逻辑更适合 Python
• 数据量超过百万行且要做复杂计算：考虑 Power Pivot 或直接上数据库

下一步

• 清洗逻辑更复杂 → 学一点点 M 语言，直接改公式栏能做很多界面做不到的事
• 想全公司共用 → Power Query 逻辑可以迁移到 Power BI，写法几乎一致
• 想彻底解放双手 → 见本站 Python 批量处理 Excel 那篇，配合系统定时任务连点都不用点