问题排查

Excel Power Query 怎么用?导入、清洗和自动刷新数据的入门教程

Windows 11 / 10发布于

每个月从系统导出的报表格式乱七八糟:多余的表头、合并单元格、日期是文本、一列里塞了好几个信息。手工整理一次要半小时,下个月还要再来一遍。Power Query 的价值就在于:整理一次,以后一键刷新。

打开 Power Query

  • 数据 → 获取数据 → 来自文件(Excel 工作簿、CSV、文本、PDF、文件夹)
  • 已经在表格里的数据:选中区域 → 数据 → 来自表格 / 区域(会先转换为超级表,见 超级表)
  • Excel 2010 / 2013 需要单独安装 Power Query 加载项

编辑器中最常用的操作

操作 位置 用途
将第一行用作标题 主页 导入后表头不对时
删除行 → 删除空行 / 删除最前面几行 主页 去掉多余的表头和空行
删除重复项 主页 去重
拆分列 主页 / 转换 按分隔符或字符数拆分,见 分列
更改数据类型 列标题左侧图标 文本转数字、文本转日期
替换值 转换 批量替换
逆透视列 转换 把“月份横排”的表变成规范的一维表
分组依据 转换 按类别汇总求和、计数
合并查询 主页 类似 VLOOKUP,按关键字匹配两张表

一个完整的例子

  1. 数据 → 获取数据 → 来自文件 → 从文本 / CSV,选择导出的销售明细
  2. 点“转换数据”进入编辑器
  3. 删除最前面 2 行 → 将第一行用作标题
  4. “日期”列更改类型为日期,“金额”列更改类型为小数
  5. 筛选掉“状态”为“作废”的行
  6. 主页 → 关闭并上载,结果输出为一张新的表格
  7. 下个月把新的 CSV 覆盖原文件,回到 Excel 点 数据 → 全部刷新

右侧的“应用的步骤”

每一个操作都会记录在右侧“应用的步骤”中。点某一步可以查看当时的结果,点前面的 × 可以删除这一步,点齿轮可以修改参数。这就是 Power Query 可以重复执行的原因。

合并多个文件

一个文件夹里有 12 个月的表格,用“来自文件夹”可以一次合并,以后放进新文件再刷新即可,详见 合并多个 Excel 文件。

常见问题

  • 刷新时报错“找不到文件”:源文件移动或改名了,在 数据 → 查询和连接 中右键查询 → 编辑,修改“源”步骤的路径
  • 刷新后列名变了:源数据的列名发生了变化,需要同步调整步骤
  • 数据量很大时,可以“仅创建连接”并加载到数据模型,配合数据透视表使用,见 数据透视表

和函数、宏的区别

函数适合实时计算,宏适合自动化操作,Power Query 适合“把乱数据变成干净的表”。三者可以配合使用,宏见 Excel 宏;用整理好的数据做报表见 Excel 数据看板。

常见问题

WPS 有 Power Query 吗?

WPS 目前没有完整的 Power Query,相关功能需要 Microsoft Excel。

修改了源文件会影响查询结果吗?

只有刷新后才会更新结果。