当前位置: 首页 > 办公技巧 > 正文

excel办公技巧批次求和(如何用Excel函数实现\u0026#34;先进先出\u0026#34;货物管理?)

  • 叁碗诸角 叁碗诸角
  • 办公技巧
  • 2023-08-15 18:29:15
  • 0

以下文章来源于Excel进修与实战 ,作者随风

先进先出”这个词想必(emmmm可能、也许、大概、似乎?)大家都不陌生。

某度的解释是这样式的:**************。由于字比较多,我就不打了。

先进先出,顾名思义(这个成语我比较喜欢)先来的先走,后来的后走。

从库存管理维度来讲:某一个产品,分别在不同时间入库了4个批次,按照时间的先后顺序分别为第一批,第二批,第三批,第四批,那么在出库的时候,就要优先出第一批的货,直至第一批货消化完,开始出第二批货,依此类推。

特点:

1、先进先出可以对库存的库龄进行精准的统计,以保证相对合理的库龄结构,在制定库存消化方案的时候,库龄是一个必不可少的参考指标。

2、先进先出可以最大限度的确保时令产品的保质期。

举个例子,某超市,先后采购了四批白菜,为了最大限度的降低白菜由于库存时间过长导致的腐烂或者不新鲜,在上架销售的时候,需要优先从最早入库的那批开始出库,具体数据如下图所示:

以批次号保存的库存数据


以物料保存的出库数据


需求解析:白菜合计出库439,按照先进先出的原则,优先将第-01批次的149个库存消耗掉,然后依次消耗-05批次的171个库存与-09批次的103个库存。

也就是:149 171 103=423, 最后剩下的16个库存从-13批次消耗,最终实现先入库的库存优先消耗。最终形成结果如下图所示:


实现方法:

首先我们需要将出库的数据引用过来,在本示例中VLOOKUP即可。


引用之后我们发现,如果发货的数量大于批次库存数量,最多也只能发该批次的库存数量。第一反应:



=IF(VLOOKUP(B3,出库!A:B,2,0)>C3,C3,VLOOKUP(B3,出库!A:B,2,0))

简化后得到如下公式:



=MIN(C3,VLOOKUP(B3,出库!A:B,2,0))

也就是取库存和发货数的最小值,形成结果如下图所示:


这个时候我们发现最后一次批次库存全部扣减了,实际上只应该扣减439-149-171-103=16个才对,也就是说,我们要取的不仅仅是库存和发货数的最小值,而是库存和剩余未发货数的最小值

其中白菜的最后一个批次剩余的发货数量应为439-(149 171 103),其中439已知是我们Vlookup函数引用过来的,剩下的就是前三个批次的发货数量,进一步观察,发现前三个数就是在本批次之前所有白菜的发货数量之和。条件求和SUMIF浮于眼前。

结合单元格区域的混合引用,每次都是扣减本单元格之前的单元格符合条件的和。得到如下公式:



=MIN(C3,VLOOKUP(B3,出库!A:B,2,)-SUMIF(B$2:B2,B3,D$2:D2))


至此,先进先出就已经搞定了,但是下边的错误值看起来有点不美。第一反应:



=IFERROR(原公式,0)

但是转念一想,不对,我引用的是数字,并且我能保证被引用区域中没有重复的项目,因此完全可以。。。。。。。


=MIN(C3,SUMIF(出库!A:A,B3,出库!B:B)-SUMIF(B$2:B2,B3,D$2:D2))

当然了 ,用sumif优化公式还有一个目的,多批次出库时,我们需要对出库数量求和之后,再按照先进先出法匹配出库批次,因此使用sumif,完全可以实现此需求。

最后说一句很重要的话:此方法需要将同一个产品的批次按照升序排列,以保证先入库的批次始终在前边。

打完收工。

随后思考:在出库数据源中,出库的数量是按月的,那么怎样做到下图:


每个批次最后消耗到哪一天,开始消耗下一个批次的库存?


最新文章