编按：

近期部落窝分享了先入先出物料管理模板，有很多领取模板的伙伴留言希望能出一篇教程。小窝今天就分享一下先入先出模板中的核心：自动根据出库物料总数匹配批次和出货量。

要实现先入先出物料管理至少需要满足下方三点：

（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：

相关推荐：

忽略文本和错误值求和的SUMIF

Excel万金油公式INDEX-SMALL-IF-ROW解读

Frequency函数用法解析

SCAN函数用法详解

版权申明：

本文作者小窝；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。