Excel按规格自动一一匹配库存与订单的实现方法

基础数据规范 #

提前整理两个源表，统一放在同一工作簿内，注意同规格条目必须按你需要的匹配优先级从上到下排列，不要随意打乱顺序：

• 库存表：建议命名为Stock，包含2个字段：Stock ID（库存编号）、Specification（规格），共10条记录
• 订单表：建议命名为Order，包含2个字段：Order ID（订单编号）、Specification（需求规格），共6条记录

注意：如果源表顺序错乱，会直接导致匹配结果错位，操作前先确认顺序符合业务要求

Power Query 落地步骤 #

此前用Power Query未达预期，核心原因是没有给同规格的库存、订单添加组内顺序号，直接按规格关联会出现多对多错配，按以下步骤操作即可得到正确结果：

1. 导入源数据
   • 选中库存表任意单元格，点击顶部菜单栏「数据」→「从表格/区域」，导入Power Query编辑器后，将该查询命名为库存源，确认仅保留Stock ID、Specification两列
   • 用相同操作导入订单表，将查询命名为订单源，确认仅保留Order ID、Specification两列
2. 为两个源表添加同规格组内序号
   • 切换到库存源查询，选中Specification列，点击「分组依据」，分组字段选择Specification，新列名设置为分组内容，操作选择「所有行」，点击确定
   • 新增自定义列，列名设为带序号，输入公式 = Table.AddIndexColumn([分组内容], "组内序号", 1, 1)，点击确定
   • 点击带序号列右上角的展开图标，仅勾选Stock ID、组内序号，取消勾选「使用原始列名作为前缀」，点击确定，即可得到每个规格下库存从1开始的连续顺序编号
   • 切换到订单源查询，重复上述分组、加组内序号、展开的操作，得到每个规格下订单从1开始的连续顺序编号
3. 全外关联匹配两类数据
   • 点击「主页」→「合并查询」，弹出设置框后，上方选择订单源的Specification、组内序号两列，下方选择库存源的Specification、组内序号两列作为匹配条件，联接种类选择「完全外部（两者中的所有行）」，点击确定
   • 点击合并生成的新列右上角的展开图标，仅勾选Stock ID，取消勾选「使用原始列名作为前缀」，点击确定
4. 空值补全与字段整理
   • 选中Stock ID列，将所有null值替换为N/A
   • 新增自定义列规格匹配，输入公式 = if [Specification] = null then [库存源.Specification] else [Specification]，补全剩余无对应订单库存的规格值，删除原Specification和库存源.Specification列，将规格匹配重命名为Specification
   • 调整列顺序为Order ID、Stock ID、Specification，无对应订单的剩余库存行Order ID字段为空，和业务要求一致
   • 点击「关闭并上载」，即可导出完整结果表

结果校验 #

按给出的样例数据，最终结果完全符合预期：

• 144PRMP规格：组内序号1、2的2条订单匹配对应2件库存，组内序号3、4的2条订单Stock ID标记为N/A
• 177BASI规格：2条订单和2件库存序号完全对应，全部匹配成功，无N/A
• 剩余无对应订单的123ELTP、144BASI、123BASI共6件库存，会按原库存顺序排列在结果表末尾，Order ID为空，对应正确的库存编号和规格，如需设置行号标识，直接在结果表最左侧插入序号列即可，对应提到的A129、A130行位置不会出现错位

内容的提问来源于stack exchange，提问作者Marco Lo