订单表用久后,常出现收货信息挤在一列、订单号重复、每次整理都要复制公式等问题。Power Query 可以把清洗步骤保存下来:刷新时重新读取原表并更新结果,而不是让结果表覆盖原表。下面以常见的“订单号—下单日期—收货信息—金额”表为例。
适用设备与系统版本
本教程适用于 Windows 10/11 桌面版 Microsoft 365、Excel 2021、Excel 2019。Excel for Mac 也有 Power Query,但菜单入口略有不同;WPS 表格通常没有与 Microsoft Excel 同版的查询编辑器,不建议照搬本文路径。手机端不适合完成这套流程。工作簿使用 .xlsx 或 .xlsm 格式,并保留一张原始订单表。
一、准备订单原表
- 先备份并检查数据结构。 每一行对应一笔订单,列标题不要重复,也不要在订单区域中合并单元格。
- 将数据区域转换为 Excel 表。 选中整个数据区域,按
Ctrl+T,在“表设计”中把名称改为订单原表。Ctrl+T 会套用表格样式,但不会改变单元格中的订单数据。以后把新订单录入这张表,刷新时更容易自动纳入范围。 - 导入 Power Query。 在原表内单击任意单元格,依次选择“数据”→“获取数据”→“从表格/区域”,确认首行为标题后进入查询编辑器。
- 给查询命名。 在右侧“查询和连接”窗格中右键新建的查询,选择“重命名”,输入
订单拆分,便于后续查找。
二、拆分收货信息列
- 假设收货信息类似
广东省-深圳市-南山区。选中“收货信息”列,在“开始”→“转换列”中选择“拆分列”→“按分隔符”,分隔符选自定义字符-。如果原数据使用/、|等符号,就选择实际使用的符号。 - 拆分后会生成
Column1、Column2、Column3。依次双击列标题,改名为“省份”“城市”“区县”。如果只需要省市两列,删除多余的空列即可。 - 选中“订单号”列,通过“转换”→“数据类型”设为“文本”,避免带前导零的编号被当作数字。若原表中的
000123已经显示为123,还要先检查 Excel 源单元格是否本来就存成了数值。 - 检查“下单日期”和“金额”的数据类型;已是真日期和数值时不用反复转换。最后可使用“开始”菜单中的“删除空行”,清理查询中的空记录。
三、统计并标记重复订单
- 在右侧“查询和连接”窗格中右键
订单拆分,选择“复制”,把副本重命名为订单号计数。 - 打开
订单号计数,只保留“订单号”一列:按住Ctrl选中其他列标题,右键选择“删除列”。 - 选择“开始”→“分组依据(Group By)”,按“订单号”分组;操作选择表示行数的计数功能,英文界面为
Row Count,新列名填写出现次数。这样可以得到每个订单号在原表中出现了几次。 - 再复制一份
订单拆分,命名为订单标记。打开它,选择“开始”→“合并查询”→“将查询合并为新查询”:左侧选择订单标记,右侧选择订单号计数,两边都勾选“订单号”,采用默认的左外连接。生成的新查询重命名为订单清洗结果。 - 在
订单清洗结果中,点击右侧嵌套表列标题上的展开箭头,勾选“出现次数”。如果同时出现重复的“订单号”字段,可以取消勾选,只保留计数列。 - 选择“添加列”→“条件列”,把条件设为“出现次数大于 1”,那么填写“重复”,否则填写“唯一”。最终结果会保留全部订单行,并用“重复”或“唯一”标记订单号状态。
四、把结果加载到独立工作表
- 在查询编辑器右上角选择“关闭并加载(上载)到”(不同版本译法不同),目标选择 Excel 工作表,输出方式选“表”。
- 选择“现有工作表”,再点“新工作表”,加载后把新工作表命名为
订单清洗结果。不要选择原始订单工作表,也不要使用“替换”选项。 订单号计数、订单标记等辅助查询可以保留在查询窗格中,因为最终结果刷新时仍会依赖它们。最终只把订单清洗结果加载到表格中即可。
五、刷新并核对结果
- 完成后回到 Excel,选择“数据”→“全部刷新”;也可以在“查询和连接”窗格中右键
订单清洗结果,单独选择“刷新”。Power Query 会按原顺序重新执行拆分、计数和标记步骤。 - 在原表中增加一笔测试订单,再次刷新并检查新行是否出现,同时核对被判定为“重复”的订单号。
- 确认无误后保存工作簿。以后只需更新原表并刷新,结果表就会同步更新,原始订单表的数据值不会被回写。
注意事项
- Ctrl+T 会改变原表的显示样式,但不会修改订单内容。如果样式也不能改变,应使用工作簿中已有的 Excel 表作为数据源。
- 当前方法按“订单号”判断重复,同一订单号的每一行都会标为“重复”,便于集中核对。如果业务要求按“客户+金额+日期”判断,可先用“合并列”生成组合键,再按组合键分组。
- 空白订单号会被当成相同值参与计数,应先清理;删除空行也不会自动删除字段为空但整行有内容的记录。
- 查询结果是自动生成区域,刷新时会被重新写入。不要直接在
订单清洗结果中补录或修改数据,否则下次刷新可能覆盖这些手工改动。 - 查询输出区域不要与原始订单表重合。若刷新后提示列名不存在,通常是原表改了标题,需要回到对应查询步骤重新指定字段。
小结
用 Power Query 清洗订单表的核心是“保留原表、在查询中处理、加载到新表”。完成一次拆分、计数和标记后,以后更新订单只需点击“全部刷新”,既省去重复复制公式,也能避免结果表误覆盖原始数据。






