怎么用EXCEL跨表格查询成本价格并计算移动平均成本

作者：EXCEL应用之家来源：部落窝教育发布时间：2021-03-25 11:42:04点击：7256

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：

日常工作中，入库清单和订购清单往往是两个独立的EXCEL表格。当需要从货物的入库数量跨表格查询到订购清单中的成本价格，并计算出移动平均成本时，必不可少的会用到各种公式。接下来，就让我们一起看看大神们是用什么样的操作实现这种查询匹配并统计的工作吧！

在采购实物中，有些原材料的价格受到供需关系的影响，具有非常强的“弹性”，不同时间段所购买的原材料的单价是不同的。比如说，有色金属、石油等商品的价格永远都处在一个波动的过程中。因此，财务在操作中为了准确界定库存的采购成本，会使用“移动平均价”这个概念。简单来说，就是库存中储存的原材料的采购成本应该是多次采购价格的平均值。

今天向大家介绍的这个例子，就体现了上面所介绍的概念。

对于某几款原料，有不同的采购日期和数量，也有不同的入库时间和数量。

现在要根据“入库数量”来匹配到相对应的订单表单中的“成本价格”。如果一次的“入库数量”大于一个及以上的“订单数量”，那么需要自动计算该原料的移动平均价格。

问题：如何计算D列中的移动平均价格？

逻辑思路：

① 在11月15日，入库产品A，数量是1000件。在F和G列中寻找相应的A产品订单后，确认入库产品来自于11月6日的A产品的订单，其成本单价为50元。因此在11月15日入库时的移动平均成本是50元。

② 在11月18日，再次入库产品A，数量是1500件。在F和G列中寻找相应的A产品订单，并结合①后，确认这批入库量是来自于两个订单。其中1000件来自于11月6日的订单，成本单价为50元；500件来自于11月20日的订单，成本单价为45元。这时候，产品A的移动平均成本是（1000*50+500*45）/1500=48.33元。

相同的逻辑，其它产品的移动平均成本的计算过程是相同的，这是题目的数学逻辑和思路。

那么，这个问题用EXCEL又该怎样实现呢？

在单元格D2中输入公式：

“=ROUND((SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9)-IFERROR(SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$1:$B1,B2,$C$1:C1))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9),))/C2,2)”，按三键（SHIFT+CTRL+ENTER）并向下拖曳即可。

大家可千万不要被看起来这么长的公式给吓到了。刨除最外层的ROUND函数，其实这么长的一段可以分为三部分：

第一部分：SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9)

第二部分：IFERROR(SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$1:$B1,B2,$C$1:C1))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9),)

第三部分：((…)-(…))/C2

公式虽然长，但大家仔细看可以发现，它主要运用的就是几个简单函数：SUM函数、FREQUENCY函数、INDIRECT函数和SUMIF(S)函数。

下面，笔者将利用庖丁解牛的方法给大家一层一层来分析这个公式！

公式详解↓↓

第一部分公式思路：

① 根据入库的数量来匹配的相应的订单。以订单数量为依据，然后根据入库的数量来定位并获取订单数量。要实现这个目的，使用FREQUENCY函数是最合适的了。

② 用FREQUENCY函数实现“入库数量超过一个订单数量时，超出部分自动匹配到下一个订单”的目的，不能使用简单一个数字（入库数量）作为参数，这样会让FREQUENCY函数只能定位并计频在一个点上。因此，这里，大家需要用一个自然数序列——从“1”到入库数量的自然数序列。

最后得出：FREQUENCY函数的第一个参数应该用ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2)))

③ SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)部分，依据F列中的时间段，对单元格B2(产品A)汇总，其结果是{2000;2000;2000;2500;2500;2500;3500}；SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2)部分，刨除掉不是A产品的订单，其结果是{2000;0;0;2500;0;0;3500}。

④ 利用FREQUENCY函数来计频。FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))部分的结果是{1000;0;0;0;0;0;0;0}，表示入库的1000件A产品都来自于11月6日的订单，数量是1000件。

⑤ SUM函数。SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9)部分，先用{1000;0;0;0;0;0;0;0}乘以采购成本{50;20;35;45;30;25;40;0}，再用SUM函数求和，得到了总的A产品的入库金额。

到这里为止，公式的第一部分我们拆解分析完成了。

第二部分公式思路：

这部分公式的目的是要把当次入库之前的同类产品的入库金额计算出来。

例如，当大家把鼠标定位在单元格D4上时，D4单元格内的公式，第一部分得到的结果是122500，第二部分计算出本次入库前A产品已经有过的所有入库货品的总成本。

计算如下：

A产品之前有一次入库记录，入库数量是1000件，成本50元，成本总金额是50000元。用第一部分得出的122500减去第一次入库的50000元后，再除以本次的入库数量1500件，移动平均成本就是48.33元。

注意：

这里和第一部分有少许差异，两段动态的单元格区域的起始部分不一样，分别是$B$1:$B1和$C$1:C1。

为什么要做这样的变动呢？

因为公式中的单元格区域$B$1:$B1对单元格B2条件求和时，在$B$1:$B1中找不到“A”这个数值后，会返回了错误值。这时，再利用IFERROR函数将错误值转换为“0”即可。

第三部分公式思路：

当次入库的A产品的总金额除以当次入库数量，就得到了移动平均成本。最后，利用ROUND函数对结果进行四舍五入修正。