如何在Power Query中实现库存收发场景的加权平均成本计算

移动加权平均成本需要按交易时间顺序逐行迭代计算，无法通过普通聚合函数直接实现，以下是可直接复用的实现步骤，完全匹配给定的出入库计算规则：

• 入库场景：新加权成本 =（原有库存总成本 + 本次入库库存总成本）/ 入库后总库存数量
• 出库场景：加权成本保持出库前的单位值，等价于 出库后剩余库存总成本 / 剩余库存数量

前置准备 #

首先确认业务表包含以下基础字段，且提前做好类型校验：

1. 物料唯一标识（如物料编码）：用于区分不同物料的独立库存核算
2. 交易时间：用于确定交易发生的先后顺序
3. 交易类型：区分入库/出库场景
4. 交易数量：入库记增加、出库记减少
5. 交易单位成本：仅入库行填写对应采购/入库单价，出库行可留空

操作步骤 #

1. 数据预处理 #

将业务数据导入Power Query编辑器后，先按「物料编码」做分组，保证每个物料的交易序列独立计算，避免跨物料混算问题：

• 点击【主页】选项卡 -> 【分组依据】-> 切换到高级模式
• 分组字段选择「物料编码」
• 新增聚合列：列名设为交易明细，操作选择「所有行」，点击确定完成分组

2. 编写迭代计算逻辑 #

使用List.Accumulate函数逐行遍历每个物料的交易序列，实时维护当前库存数量、库存总成本、加权单价三个核心状态值。
选中上一步生成的分组表，点击【添加列】->【自定义列】，输入以下M公式：

= Table.AddColumn(分组后的表, "成本计算结果", (currentMat)=>
  let
    // 按交易时间升序排序，同时间入库排在出库前，避免顺序错误
    sortedTrx = Table.Sort(currentMat[交易明细],{{"交易时间", Order.Ascending}, {"交易类型", Order.Descending}}),
    trxRecordList = Table.ToRecords(sortedTrx),
    // 逐行迭代计算
    calcRes = List.Accumulate(trxRecordList, 
      [stockQty=0, stockTotalCost=0, wac=0, resList={}], 
      (state, row)=>
        let
          isInbound = row[交易类型] = "入库",
          newQty = if isInbound then state[stockQty] + row[交易数量] else state[stockQty] - row[交易数量],
          newTotalCost = if isInbound 
            then state[stockTotalCost] + (row[交易数量] * row[交易单位成本])
            else newQty * state[wac],
          newWac = if newQty = 0 then 0 else newTotalCost / newQty,
          mergedRow = Record.Combine({row, [
            交易后库存数量 = newQty,
            交易后库存总成本 = newTotalCost,
            交易后加权平均成本 = newWac
          ]}),
          newResList = state[resList] & {mergedRow}
        in
        [stockQty=newQty, stockTotalCost=newTotalCost, wac=newWac, resList=newResList]
    )
  in
    Table.FromRecords(calcRes[resList])
)

注意：请将公式中的字段名替换为自己表中实际的字段名称，如果表中出库数量已经是负值，可以去掉出库分支里row[交易数量]前面的减号

3. 展开结果 #

点击自定义列右上角的展开按钮，选择需要展示的字段，确认后即可得到每一笔交易对应的实时加权平均成本。

异常场景适配 #

如果业务存在先出库后入库导致的负库存场景，上述公式会按给定规则继续计算；如果需要拦截负库存报错，可以在迭代分支里新增判断，当newQty<0时返回自定义错误提示即可。

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