电子表格函数(电子表格函数公式大全详解视频)深度揭秘

每天一点excel技能，跨表查询

 

↑入群，免费领取全套Excel技巧视频↑ 每天一点小技能职场打怪不得怂

编按：入库清单和订购清单常常是两个独立的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函数对结果进行四舍五入修正。

好了，今天和大家分享的内容就是这些了！各种公式应用，只有多练才能学会！扫一扫添加老师微信

在线咨询Excel课程

Excel教程相关推荐超实用！公司大神用Excel做了一张自动查询表，我跪着收藏了！我花了5小时，整理出这13个办公中最实用的Excel技巧（建议收藏）做了五年财务的小姐姐，竟然被Excel里的这个符号坑了？

《10天学会Excel》课程：带你学遍Excel技巧、函数、透视表、图表、数据分析等实用功能全套Excel办公技能助力职场升级！让你在职场中实现逆风翻盘！原价99元，限时购买只需9元！

让工作提速百倍的「Excel极速贯通班」↓ 点击阅读原文，免费试听。