Excel:用 Power Query 替代每月手动粘贴
学习如何连接定期导出的文件、清洗并合并数据,同时减少列名变化导致的错误,之后只需刷新即可更新每月 Excel 报表。
每月初,许多企业都会从软件导出数据,打开 Excel 文件,删除多余行,再粘贴到汇总报告中。如果只做一次,这种方法并不难,但需要合并多个文件或重复相同操作时,就很容易出错。Power Query 适合处理这类工作:记录处理步骤,之后只需更新源文件并刷新数据。需要提前准备的不是复杂公式,而是从一开始就组织好文件并统一列规范。
何时应改用 Power Query
当报告的输入内容定期重复,且数据清洗步骤相对稳定时,适合改用 Power Query。例如,软件每月导出一份结构相同的往来账款文件,或者各分支机构分别提交一份销售文件,需要合并成一张表。如果数据在进入 PivotTable 之前还需要筛选合计行、删除空行、转换日期类型、统一客户编码,Power Query 同样很有用。
- 适合使用:定期导出的文件、多个同模板文件、重复的数据清洗操作、需要追溯来源的报告。
- 暂不需要使用:只处理一次的小表、数据模板频繁变化,或报告要求尚未统一。
- 需要先处理:文件中存在合并单元格、多行标题,或一个工作表内叠放多个表格。Power Query 虽然可以读取,但处理步骤会难以维护。
不要从规模最大的报告开始。应选择一个来源清晰、负责人了解核对方法、输出结果经常使用的表格。
根据软件导出文件构建查询的四个步骤
保留前几期的几个文件用于测试,其中包括一份出现空行或异常数据的文件。不要直接在当前用于提交的报告文件上测试。
- 隔离源文件区域。为导出文件创建单独的文件夹,例如按报告类型划分。此文件夹只放需要合并的文件;不要将草稿、临时文件或结果报告放在同一位置。如果每期只有一个文件,可以直接连接该文件,而不必使用文件夹。
- 连接并选择正确的数据对象。在 Excel 中,进入 Data > Get Data > From File,需要合并多个文件时选择 From Folder,处理单个文件时选择 From Workbook。对于 Excel 文件,应选择包含明细数据的表格或工作表,不要选择带有报告标题和汇总行的工作表。
- 在示例查询中清洗数据。将标题行用作列名,删除空行和合计行,筛选掉不属于报告范围的记录。为日期、数字和文本设置数据类型,并根据源文件核对日期格式。从文件夹合并时,应在 Transform Sample File 中执行这些操作,让 Excel 对每个文件应用相同的处理方式。
- 加载结果并确定后续刷新方式。选择 Close & Load To。如需在工作表上查看和调整报告,则加载为表格;如果数据仅用于 PivotTable 或数据模型,则选择仅创建连接。后续月份只需将新文件放入相应文件夹,然后选择 Refresh All,无需逐个打开文件进行复制。
根据内容命名查询,例如“CongNo_ChiTiet”,不要保留默认名称。Applied Steps 窗格中的步骤,如果操作不易辨认,也应重命名,尤其是筛选步骤和列重命名步骤。
列名变化时保持查询正常运行
常见问题是软件将“客户编号”改为“客户代码”,而后续步骤仍在查找旧列名。Excel 自动生成的 Changed Type 步骤也可能包含一长串列名,因此即使只有一个小的改动,也会导致查询报错。降低依赖的方法是,在读取数据后立即统一列名,并让后续所有处理都使用标准名称。
- 创建包含两列的映射表:文件中的名称和标准名称。例如,“客户编号”和“客户代码”都统一为“MaKhachHang”;每个文件中,一个标准名称只能对应一个源名称。
- 将映射表导入 Power Query,并用于重命名步骤。如果熟悉 M 代码编辑,可以使用 Table.RenameColumns 和 MissingField.Ignore,忽略当前文件中未出现的旧名称。
- 仅在重命名步骤之后设置数据类型并编写计算。不要通过“第三列”之类的位置引用列,因为导出文件中的列顺序可能会发生变化。
- 建立必需列清单,并在处理前使用 Table.ColumnNames 进行检查。如果缺少列,查询应明确报告缺失列的名称,而不是在不提示的情况下生成数据不完整的表格。
当文件使用新列名时,先在一个副本上测试刷新。如果只需补充映射,就更新列名表;如果数据含义已经变化,必须重新审视报告逻辑,而不能只修改名称让查询能够运行。
每次刷新后进行检查
Power Query 会自动重复操作,但不会自动确认结果在业务上是否正确。执行 Refresh All 后,发布报告前需要与源文件核对。
- 检查筛选后的行数,并解释被排除的行。
- 将总发生额或期末余额等若干汇总指标与原始报告核对。
- 打开各查询的错误通知;如果刷新尚未成功,不要使用旧的结果表。
- 按期间保存源文件,便于日后核查数据时进行追溯。
现在就该做的事
- 选择一份定期手动粘贴的报告,并保存源文件样本。
- 按照四个步骤构建查询,然后将结果与当前报告核对。
- 建立标准列名表,并明确每次刷新后的检查方法。
易于维护的查询,比包含许多巧妙处理步骤的查询更重要。需要梳理导出文件与管理报告之间的数据流时,VASCOM 可以与企业共同先从输入环节着手标准化,再实施自动化。