每个月从系统导出的报表格式乱七八糟:多余的表头、合并单元格、日期是文本、一列里塞了好几个信息。手工整理一次要半小时,下个月还要再来一遍。Power Query 的价值就在于:整理一次,以后一键刷新。
打开 Power Query
- 数据 → 获取数据 → 来自文件(Excel 工作簿、CSV、文本、PDF、文件夹)
- 已经在表格里的数据:选中区域 → 数据 → 来自表格 / 区域(会先转换为超级表,见 超级表)
- Excel 2010 / 2013 需要单独安装 Power Query 加载项
编辑器中最常用的操作
| 操作 | 位置 | 用途 |
|---|---|---|
| 将第一行用作标题 | 主页 | 导入后表头不对时 |
| 删除行 → 删除空行 / 删除最前面几行 | 主页 | 去掉多余的表头和空行 |
| 删除重复项 | 主页 | 去重 |
| 拆分列 | 主页 / 转换 | 按分隔符或字符数拆分,见 分列 |
| 更改数据类型 | 列标题左侧图标 | 文本转数字、文本转日期 |
| 替换值 | 转换 | 批量替换 |
| 逆透视列 | 转换 | 把“月份横排”的表变成规范的一维表 |
| 分组依据 | 转换 | 按类别汇总求和、计数 |
| 合并查询 | 主页 | 类似 VLOOKUP,按关键字匹配两张表 |
一个完整的例子
- 数据 → 获取数据 → 来自文件 → 从文本 / CSV,选择导出的销售明细
- 点“转换数据”进入编辑器
- 删除最前面 2 行 → 将第一行用作标题
- “日期”列更改类型为日期,“金额”列更改类型为小数
- 筛选掉“状态”为“作废”的行
- 主页 → 关闭并上载,结果输出为一张新的表格
- 下个月把新的 CSV 覆盖原文件,回到 Excel 点 数据 → 全部刷新
右侧的“应用的步骤”
每一个操作都会记录在右侧“应用的步骤”中。点某一步可以查看当时的结果,点前面的 × 可以删除这一步,点齿轮可以修改参数。这就是 Power Query 可以重复执行的原因。
合并多个文件
一个文件夹里有 12 个月的表格,用“来自文件夹”可以一次合并,以后放进新文件再刷新即可,详见 合并多个 Excel 文件。
常见问题
- 刷新时报错“找不到文件”:源文件移动或改名了,在 数据 → 查询和连接 中右键查询 → 编辑,修改“源”步骤的路径
- 刷新后列名变了:源数据的列名发生了变化,需要同步调整步骤
- 数据量很大时,可以“仅创建连接”并加载到数据模型,配合数据透视表使用,见 数据透视表
和函数、宏的区别
函数适合实时计算,宏适合自动化操作,Power Query 适合“把乱数据变成干净的表”。三者可以配合使用,宏见 Excel 宏;用整理好的数据做报表见 Excel 数据看板。
常见问题
WPS 有 Power Query 吗?
WPS 目前没有完整的 Power Query,相关功能需要 Microsoft Excel。
修改了源文件会影响查询结果吗?
只有刷新后才会更新结果。