使用PowerQuery解决发出存货月末一次平均计价问题

张文洲

今天和大家分享一个使用PowerQuery解决发出存货月末一次平均计价问题的案例，希望某一天有用的着。

• 问题的提出：
  

从事财务行业，尤其是管理会计的朋友，对于发出存货月末平均计价法一定不陌生：我们有一份某单位库存商品购入和销售的明细数据，知道每种商品的年初库存数量和成本金额，知道当年每一批次购入的数量和成本（单价），知道每一批次的销售数量，需要计算的是每个月销售商品的成本。
因为单位规模属于中小型单位，没有像大型单位如超市那样采用比较规范的个别计价法（每件商品都有自己的条码），并不知道每次销售的商品属于哪一批次购入的，购入的单价或成本是多少。


因此采用相对粗略的计算方式：

当月销售商品的成本=当月商品的平均成本*当月销售数量

当月商品平均成本=（月初商品成本+当月购入商品成本）/（月初商品数量+当月购入商品数量）

当月结存月末成本=月初商品成本-当月销售商品成本

• 问题的难点：
  

计算每个月的销售商品成本，都需要依赖于上一个月份的计算结果，即需要知道月初库存成本（也就是上期期末结存成本）
如果不借助辅助区域，常规的Excel工作表函数是较难实现类似的计算的，
这是一个递归问题。

• 问题的解决：
  

Excel2010及以后的版本支出PowerQuery这一功能，2016版已经内置为Excel自身功能了。使用PowerQuery强大的M引擎，我们可以实现较为复杂的数据查询、转换和数据计算分析。
在PowerQuery为我们提供了大量功能强大的内置函数，也允许我们创建自定义函数，尤为重要的是，允许递归调用。


以下是需要实现的效果



将原始数据导入到PowerQuery中，可以创建比较复杂的自定义函数



在高级编辑器里面可以编辑查询语句

• 题外话：
  

当然，我们知道PowerQuery主要在于数据获取和转换，数据分析并非它的强项；PowerPivot作为数据分析的专门组件，对于这个问题自然也有自己的解决方法，而且效率更高，在后续的帖子中，我将分享一些方法。
08 快速创建自定义函数解决实际问题.rar (178.53 KB, 下载次数: 148)

2016-9-1 13:30 上传

点击文件名下载附件

function2046

谢谢，分享