先入先出出入库模板中出库批次的自动匹配
作者:小窝来源:部落窝教育发布时间:2024-11-14 20:58:56点击:196
编按:
近期部落窝分享了先入先出物料管理模板,有很多领取模板的伙伴留言希望能出一篇教程。小窝今天就分享一下先入先出模板中的核心:自动根据出库物料总数匹配批次和出货量。
要实现先入先出物料管理至少需要满足下方三点:
(1)具备批次库存表,并按日期升序排列各批次物料;
(2)实时统计各批次物料的库存量;
(3)自动根据物料出库总数匹配物料批次和出货量。
第一点,按日期升序排列,很简单。在Excel表格中可以选择数据后单击“升序”功能键排序,也可以录制一个升序排列的宏,执行宏进行排序。
第二点,按批次统计库存,也很简单,就是用各批物料的入库量减去出库量。
下面重点说一下第三点:自动匹配物料批次和出货量。
图中A1:D14类似一个简化的批次库存表,物料按日期升序排列;右侧F2:G2是需要出库的物料编码和出库总数。现在需要获得物料批次以及出货量。
我们建立辅助区域逐步来解决。先说批次。
出货的批次有两个条件需满足:
(1)产品编码一致;
(2)库存量大于0。
在H10用函数FILTER可以快速得到能用于出货的批次:
=FILTER(C2:C14,(A2:A14=F2)*(D2:D14>0))
有了批次,出货量就简单了。各批次出货量等于批次实际库存(用VLOOKUP函数可以查到)、出库总数减去各批次出库量累计值的差之间的最小值。可以下拉填充下方的公式获取:
=MIN(VLOOKUP(H10,$C$2:$D$14,2,),$G$2-SUMIF($I$9:I9,"<>",$I$9:I9))
此处之所以用SUMIF求出库量累计和,是因为I9是文本,用SUM求和会出错,而SUMIF可以忽略文本与错误值进行求和。
最后在H2处引用出货量不为0的批次和出货量:
=FILTER(H10:I12,I10:I12<>0)
先入先出物料模板就是采用上方的思路做的。只不过为了照顾低版本用户,没有用FILTER函数,而是用经典的INDEX+SMALL+IF+ROW一对多套路公式。
还有一个小细节:出库总数不可能大于实际库存。
所以为了提醒出库人员,我们在产品编码后增加了一个“现有库存”数据。在他输入产品编码后,就能看到当前的库存总数。
再分享三个不用辅助区域即可获得批次的公式。
低版本:
=MID(CONCAT(IFERROR(IF(($A$2:$A$14=$F$2)*($D$2:$D$14>0),INDIRECT("C2:C"&MATCH(LOOKUP($H$2-0.01,SUMIF(OFFSET($A$1,,,ROW($1:$14)),$F$2,$D$1),$C$2:$C$14),$C$2:$C$14,0)+1),""),"")),ROW(A1)*12-11,12)
数组公式,三键输入后下拉填充。公式中数字12是批号的字符数。
高版本:
=TOCOL(IF((A2:A14=F2)*(D2:D14>0),INDIRECT("C2:C"&MATCH(LOOKUP(H2-0.1,SUMIF(OFFSET($A$1,,,ROW(A1:A14)),F2,D1),C2:C14),C2:C14,)+1),NA()),2)
或者
=FILTER(FILTER(C2:C14,(A2:A14=F2)*(D2:D14>0)),DROP(FREQUENCY(ROW(1:380),SCAN(0,FILTER(D2:D14,(A2:A14=F2)*(D2:D14>0)),LAMBDA(x,y,x+y))),-1))
先入先出出入库模板出库批次和出货量的自动匹配就说到这里。对模板有兴趣的伙伴可以联系部落窝客服。
本文配套的练习课件请添加客服微信buluowojiaoyu索取。
做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
Excel万金油公式INDEX-SMALL-IF-ROW解读
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。