Power Query按物料分组计算累计库存与移动加权平均成本

前置要求 #

原始库存交易表必须包含以下核心字段：

• 物料标识：物料编码/物料ID，用于区分不同物料
• 交易排序字段：交易日期+流水号，用于确定出入库先后顺序
• 交易数量：入库为正，出库为负
• 交易单价：入库行填实际采购单价，出库行可留空

操作步骤 #

1. 数据清洗与预处理 #

• 将原始数据表导入Power Query编辑器，校验字段类型：交易排序字段设为对应日期/整数类型，交易数量、交易单价设为数值类型，删除字段空值、交易数量为0的无效行。
• 新增自定义列交易成本，输入公式：

  = if [交易数量] > 0 then [交易数量] * [交易单价] else 0
  

• 选中物料标识列、交易排序字段列，按升序完成排序，确保所有交易的先后顺序准确，该步骤直接决定累计计算结果的正确性。

2. 按物料分组聚合 #

• 选中物料标识列，点击菜单栏「分组依据」，选择高级分组模式：
  • 分组字段保留物料标识
  • 新增聚合列，命名为物料交易明细，聚合操作选择「所有行」
  • 点击确定，此时每个物料将对应一个独立子表，存储该物料下所有排序完成的交易记录。

3. 逐组逐行计算累计指标 #

• 新增自定义列计算结果，输入以下M公式，为每个物料的交易序列逐行迭代计算累计库存量、加权平均成本：

  = List.Generate(
      () => [
          行号 = 0,
          累计Qty = 物料交易明细{0}[交易数量],
          累计总成本 = 物料交易明细{0}[交易成本],
          平均成本 = if 累计Qty = 0 then 0 else 累计总成本 / 累计Qty,
          输出行 = Record.AddField(Record.AddField(物料交易明细{0}, "累计库存量", 累计Qty), "加权平均成本", 平均成本)
      ],
      (x) => x[行号] < Table.RowCount(物料交易明细),
      (x) => [
          行号 = x[行号] + 1,
          上笔累计Qty = x[累计Qty],
          上笔总成本 = x[累计总成本],
          上笔均价 = x[平均成本],
          本次Qty = 物料交易明细{行号}[交易数量],
          本次成本 = 物料交易明细{行号}[交易成本],
          累计Qty = 上笔累计Qty + 本次Qty,
          累计总成本 = if 本次Qty > 0 then 上笔总成本 + 本次成本 else 上笔均价 * 累计Qty,
          平均成本 = if 累计Qty = 0 then 0 else 累计总成本 / 累计Qty,
          输出行 = Record.AddField(Record.AddField(物料交易明细{行号}, "累计库存量", 累计Qty), "加权平均成本", 平均成本)
      ],
      (x) => x[输出行]
  )
  

• 选中计算结果列，点击「扩展到新表」，删除分组阶段生成的物料交易明细冗余列，即可得到所有交易行对应的累计库存量、加权平均成本字段。

计算规则匹配说明 #

• 入库场景（交易数量为正）：累计库存量为上笔库存加本次入库量，累计总成本为上笔库存总成本加本次入库的采购总成本，加权平均成本=（原库存总成本+新入库成本）/新总库存，完全匹配入库计算规则。
• 出库场景（交易数量为负）：累计库存量为上笔库存扣减本次出库量，累计总成本为上笔加权平均成本乘以扣减后的剩余库存，加权平均成本=剩余总成本/剩余可用库存，完全匹配出库计算规则。

注意：该公式默认业务场景中不存在负库存异常交易，若需处理负库存场景，可在迭代逻辑中补充对应分支的业务规则。

内容的提问来源于stack exchange，提问作者Dinesh Suranga