Power Query 移動加權(quán)平均計算

Power Query 移動加權(quán)平均設(shè)置全解析：從原理到實戰(zhàn)

一、移動加權(quán)平均法的核心邏輯

移動加權(quán)平均法是存貨計價的重要方法，其核心公式為：

該方法要求每次進貨后重新計算平均成本，形成遞歸依賴鏈。在 Power Query 中，通過List.Accumulate函數(shù)可完美解決這種遞歸計算問題。

二、數(shù)據(jù)準(zhǔn)備與預(yù)處理

1. 數(shù)據(jù)結(jié)構(gòu)要求

   需包含以下字段：

• 日期 / 年月（排序依據(jù)）

• 期初數(shù)量 / 采購數(shù)量 / 發(fā)出數(shù)量

• 采購成本（數(shù)量 × 單價）

• 期末數(shù)量（= 期初數(shù)量 + 采購數(shù)量 - 發(fā)出數(shù)量）

1. 數(shù)據(jù)導(dǎo)入與類型調(diào)整

   在 Power Query 編輯器中加載數(shù)據(jù)后，需確保：

• 日期字段為Date類型

• 數(shù)量、成本字段為Number類型

• 空值（如首次記錄的期初數(shù)量）需處理為 0

1. 關(guān)鍵排序操作

   必須按日期升序排列，確保計算順序正確?？赏ㄟ^Table.Sort函數(shù)實現(xiàn)：

= Table.Sort(更改的類型, {{"日期", Order.Ascending}})

三、核心計算步驟實現(xiàn)

1. 遞歸計算公式

   使用List.Accumulate函數(shù)逐行處理數(shù)據(jù)：

let

   Source = 排序后的數(shù)據(jù),

   Result = List.Accumulate(

       Table.ToRecords(Source),

       [Data = {}, PrevCost = 0], // 初始狀態(tài)：空結(jié)果集+初始成本0

       (state, row) => let

           總成本 = state[PrevCost] + row[采購成本],

           總數(shù)量 = row[期初數(shù)量] + row[采購數(shù)量],

           加權(quán)單位成本 = if 總數(shù)量 = 0 then 0 else 總成本 / 總數(shù)量,

           期末成本 = row[期末數(shù)量] * 加權(quán)單位成本,

           NewRow = Record.Combine(row, [加權(quán)單位成本 = 加權(quán)單位成本, 期末成本 = 期末成本])

       in

           [Data = state[Data] & {NewRow}, PrevCost = 期末成本]

   )[Data],

   FinalTable = Table.FromRecords(Result)

in

   FinalTable

1. 公式解析

• 初始狀態(tài)：Data存儲結(jié)果行，PrevCost傳遞上期期末成本

• 迭代邏輯：逐行計算總成本、總數(shù)量，避免除數(shù)為零錯誤

• 狀態(tài)更新：將當(dāng)前行結(jié)果追加到Data，并更新PrevCost供下行使用

四、異常處理與優(yōu)化

1. 空值處理

   若期初數(shù)量為 null，需提前替換為 0：

= Table.ReplaceValue(排序后的數(shù)據(jù), null, 0, Replacer.ReplaceValue, {"期初數(shù)量"})

1. 負數(shù)庫存處理

   通過條件判斷限制庫存數(shù)量：

= Table.AddColumn(Source, "調(diào)整后數(shù)量", each if [期末數(shù)量] < 0 then 0 else [期末數(shù)量])

1. 性能優(yōu)化

   大數(shù)據(jù)量場景下，建議使用Table.Buffer緩存數(shù)據(jù)：

= Table.Buffer(Table.Sort(Source, {{"日期", Order.Ascending}}))

五、結(jié)果驗證與對比

1. 手動驗證

   選取某一行數(shù)據(jù)，按公式計算加權(quán)單位成本：

例：某行期初數(shù)量50，采購數(shù)量100，采購成本1500，則加權(quán)單位成本=(0+1500)/(50+100)=10元

1. 與 FIFO 對比

• 移動加權(quán)：成本平滑，反映實時價格變動

• FIFO：利潤波動大，庫存價值接近市價

  企業(yè)可根據(jù)行業(yè)特性選擇：零售業(yè)推薦移動加權(quán)，制造業(yè)傾向 FIFO

六、高級應(yīng)用場景

1. 多產(chǎn)品分組計算

   使用Table.Group按產(chǎn)品分組后分別計算：

= Table.Group(Source, {"產(chǎn)品ID"}, {

   {"移動加權(quán)結(jié)果", each let

       分組數(shù)據(jù) = _,

       排序后數(shù)據(jù) = Table.Sort(分組數(shù)據(jù), {{"日期", Order.Ascending}}),

       遞歸計算 = List.Accumulate(...) // 復(fù)用核心公式

   in

       遞歸計算

   }}

)

1. 與 Power BI 集成

   將結(jié)果加載到 Power BI 后，可通過 DAX 公式實現(xiàn)動態(tài)庫存監(jiān)控：

實時庫存價值 = SUMX('移動加權(quán)表', [加權(quán)單位成本] * [期末數(shù)量])

七、常見問題與解決方案

1. 錯誤提示：除數(shù)為零

• 原因：總數(shù)量為 0 時未處理

• 解決：在公式中添加if 總數(shù)量 = 0 then 0 else ...判斷

1. 結(jié)果與 Excel 不一致

• 檢查排序順序是否正確

• 確認空值已處理為 0

• 驗證公式中字段引用是否準(zhǔn)確

1. 性能瓶頸

• 啟用Table.Buffer

• 減少不必要的列

• 對大數(shù)據(jù)集使用數(shù)據(jù)庫直連模式

八、總結(jié)

通過 Power Query 實現(xiàn)移動加權(quán)平均法，既能保持財務(wù)核算的精確性，又能利用自動化處理提升效率。關(guān)鍵步驟包括：

1. 數(shù)據(jù)結(jié)構(gòu)化與排序

2. 使用List.Accumulate遞歸計算

3. 完善異常處理機制

4. 結(jié)合業(yè)務(wù)場景優(yōu)化公式

企業(yè)可根據(jù)自身需求擴展應(yīng)用，例如與 ERP 系統(tǒng)集成實現(xiàn)實時成本監(jiān)控，或通過 Power BI 構(gòu)建動態(tài)庫存儀表盤。這種方法尤其適合電商、制造業(yè)等庫存周轉(zhuǎn)頻繁的行業(yè)，能有效平衡成本核算的準(zhǔn)確性與時效性。

學(xué)習(xí)入口：https://edu.cda.cn/goods/show/3814?targetId=6587&preview=0

推薦學(xué)習(xí)書籍 《CDA一級教材》適合CDA一級考生備考，也適合業(yè)務(wù)及數(shù)據(jù)分析崗位的從業(yè)者提升自我。完整電子版已上線CDA網(wǎng)校，累計已有10萬+在讀~ !

免費加入閱讀：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0