办公软件

Excel 订单表清洗:用 Power Query 拆分列、标记重复,刷新不覆盖原表

约 6 分钟读完
Excel 订单表清洗:用 Power Query 拆分列、标记重复,刷新不覆盖原表

摘要

本文以 Windows 版 Microsoft Excel 为例,演示把订单原表导入 Power Query,拆分收货信息列,按订单号统计并标记重复项,再把结果加载到独立工作表。以后点击“全部刷新”即可同步新增或修改的数据,原表的数据值不会被结果表覆盖,适合长期维护的订单台账。

本文目录

订单表用久后,常出现收货信息挤在一列、订单号重复、每次整理都要复制公式等问题。Power Query 可以把清洗步骤保存下来:刷新时重新读取原表并更新结果,而不是让结果表覆盖原表。下面以常见的“订单号—下单日期—收货信息—金额”表为例。

适用设备与系统版本

本教程适用于 Windows 10/11 桌面版 Microsoft 365、Excel 2021、Excel 2019。Excel for Mac 也有 Power Query,但菜单入口略有不同;WPS 表格通常没有与 Microsoft Excel 同版的查询编辑器,不建议照搬本文路径。手机端不适合完成这套流程。工作簿使用 .xlsx 或 .xlsm 格式,并保留一张原始订单表。

一、准备订单原表

  1. 先备份并检查数据结构。 每一行对应一笔订单,列标题不要重复,也不要在订单区域中合并单元格。
  2. 将数据区域转换为 Excel 表。 选中整个数据区域,按 Ctrl+T,在“表设计”中把名称改为 订单原表。Ctrl+T 会套用表格样式,但不会改变单元格中的订单数据。以后把新订单录入这张表,刷新时更容易自动纳入范围。
  3. 导入 Power Query。 在原表内单击任意单元格,依次选择“数据”→“获取数据”→“从表格/区域”,确认首行为标题后进入查询编辑器。
  4. 给查询命名。 在右侧“查询和连接”窗格中右键新建的查询,选择“重命名”,输入 订单拆分,便于后续查找。

二、拆分收货信息列

  1. 假设收货信息类似 广东省-深圳市-南山区。选中“收货信息”列,在“开始”→“转换列”中选择“拆分列”→“按分隔符”,分隔符选自定义字符 -。如果原数据使用 /、| 等符号,就选择实际使用的符号。
  2. 拆分后会生成 Column1、Column2、Column3。依次双击列标题,改名为“省份”“城市”“区县”。如果只需要省市两列,删除多余的空列即可。
  3. 选中“订单号”列,通过“转换”→“数据类型”设为“文本”,避免带前导零的编号被当作数字。若原表中的 000123 已经显示为 123,还要先检查 Excel 源单元格是否本来就存成了数值。
  4. 检查“下单日期”和“金额”的数据类型;已是真日期和数值时不用反复转换。最后可使用“开始”菜单中的“删除空行”,清理查询中的空记录。

三、统计并标记重复订单

  1. 在右侧“查询和连接”窗格中右键 订单拆分,选择“复制”,把副本重命名为 订单号计数。
  2. 打开 订单号计数,只保留“订单号”一列:按住 Ctrl 选中其他列标题,右键选择“删除列”。
  3. 选择“开始”→“分组依据(Group By)”,按“订单号”分组;操作选择表示行数的计数功能,英文界面为 Row Count,新列名填写 出现次数。这样可以得到每个订单号在原表中出现了几次。
  4. 再复制一份 订单拆分,命名为 订单标记。打开它,选择“开始”→“合并查询”→“将查询合并为新查询”:左侧选择 订单标记,右侧选择 订单号计数,两边都勾选“订单号”,采用默认的左外连接。生成的新查询重命名为 订单清洗结果。
  5. 在 订单清洗结果 中,点击右侧嵌套表列标题上的展开箭头,勾选“出现次数”。如果同时出现重复的“订单号”字段,可以取消勾选,只保留计数列。
  6. 选择“添加列”→“条件列”,把条件设为“出现次数大于 1”,那么填写“重复”,否则填写“唯一”。最终结果会保留全部订单行,并用“重复”或“唯一”标记订单号状态。

四、把结果加载到独立工作表

  1. 在查询编辑器右上角选择“关闭并加载(上载)到”(不同版本译法不同),目标选择 Excel 工作表,输出方式选“表”。
  2. 选择“现有工作表”,再点“新工作表”,加载后把新工作表命名为 订单清洗结果。不要选择原始订单工作表,也不要使用“替换”选项。
  3. 订单号计数、订单标记 等辅助查询可以保留在查询窗格中,因为最终结果刷新时仍会依赖它们。最终只把 订单清洗结果 加载到表格中即可。

五、刷新并核对结果

  1. 完成后回到 Excel,选择“数据”→“全部刷新”;也可以在“查询和连接”窗格中右键 订单清洗结果,单独选择“刷新”。Power Query 会按原顺序重新执行拆分、计数和标记步骤。
  2. 在原表中增加一笔测试订单,再次刷新并检查新行是否出现,同时核对被判定为“重复”的订单号。
  3. 确认无误后保存工作簿。以后只需更新原表并刷新,结果表就会同步更新,原始订单表的数据值不会被回写。

注意事项

  • Ctrl+T 会改变原表的显示样式,但不会修改订单内容。如果样式也不能改变,应使用工作簿中已有的 Excel 表作为数据源。
  • 当前方法按“订单号”判断重复,同一订单号的每一行都会标为“重复”,便于集中核对。如果业务要求按“客户+金额+日期”判断,可先用“合并列”生成组合键,再按组合键分组。
  • 空白订单号会被当成相同值参与计数,应先清理;删除空行也不会自动删除字段为空但整行有内容的记录。
  • 查询结果是自动生成区域,刷新时会被重新写入。不要直接在 订单清洗结果 中补录或修改数据,否则下次刷新可能覆盖这些手工改动。
  • 查询输出区域不要与原始订单表重合。若刷新后提示列名不存在,通常是原表改了标题,需要回到对应查询步骤重新指定字段。

小结

用 Power Query 清洗订单表的核心是“保留原表、在查询中处理、加载到新表”。完成一次拆分、计数和标记后,以后更新订单只需点击“全部刷新”,既省去重复复制公式,也能避免结果表误覆盖原始数据。

常见问题

刷新后,新增加的订单为什么没有出现?

先确认原数据区域是 Excel 表,并把新订单录入表内或紧接在表格下方扩展区域,然后再次点击“全部刷新”。如果查询来源只是固定单元格区域,新增行可能不在刷新范围内,需要先转换为 Excel 表或调整查询范围。

为什么同一订单号的两行都被标成“重复”?

本文采用出现次数进行标记:出现一次为“唯一”,出现两次及以上时,两行都会标为“重复”。这种标记方式便于把所有重复记录一起筛选出来核对。

能否按客户、金额和日期组合判断重复?

可以。先在 Power Query 中用“合并列”把参与判断的字段组合成一列,再按该组合列分组计数。组合键应包含业务上真正代表同一笔订单的字段,字段选择不当可能漏标或误标。

参考来源