Excel函数式编程，轻松完成FIFO（先入先出法）库存成本计算难题
在使用Excel进行库存管理时，我们需要计算库存成本（包括当前库存成本以及每次出库的成本）。当涉及到同一物品多次出库入库时，这种计算需要按照预先指定的规则进行，常用的规则有：
FIFO（先入先出法）：即每次出库都从库存中最早的入库物资开始，如果最早的入库数量不足以保证出库数量，那么剩余数量的物资依次从以后的每条中进行出库。
LIFO（后入先出法）：与FIFO相反，每次出库从最近的入库开始。
平均法
不管是FIFO，还是LIFO，实际计算的时候都比较复杂。主要是很难利用Excel公式进行自动化的计算工作。
用Excel进行库存管理时，我们建议维护两张表：
入库表
出库表
其中，入库表的数据是手工输入的。出库表的前4列是手工输入的，出库单件是需要计算的（即出库成本）。
很明显，要计算入库后的平均单价很容易。比如，要计算2022/2/10的库存成本（在这一天，所有的入库已经完成，并且没有发生任何出库操作）
只要将入库量和价格做加权平均就好了：
=SUMPRODUCT(入库量,价格)/SUM(入库量)但是一旦发生一次出库，比如要计算2022/3/2的库存成本，就需要知道2022/3/1这次出库是从哪些入库记录中进行的。尤其是，如果出库涉及多条入库时，这个计算并不容易。
注：这两个表是简化后的表，只有必要的字段，实际场景可能需要更多的字段。不过不影响我们对这个问题的理解。
注：很多使用Excel管理库存的朋友喜欢在一张表上同时维护入库和出库信息，实际上也没有问题。本文介绍的方法很容易就可以移植到这种场景中。
注：还有很多朋友喜欢在一张表上同时维护入库、出库和当前库存信息。强烈不建议这么做。在Excel中这么做会带来很多不利的影响。在“Excel工作的标准模式”课程中，我们详细介绍过，这里就不重复了。实际上，当前库存可以很容易通过函数得到。
注：本文案例中入库表和出库表只有一种物品，并且是按照日期顺序进行升序排列。实际情况并不如此，但是我们仍然可以这么假设，因为我们可以通过FILTER函数和SORT函数轻松达到这样的要求。
下面我们先以手工的方式分析第一次出库时FIFO的操作。
在计算库存成本时，其实最重要的就是要知道每次出库需要从哪些入库记录中进行操作，所以下面的分析就集中在这个环节。
最左边是入库记录，按照入库日期升序。最右边是出库数量：100。
首先去到第一条入库记录，即“15”这一条，因为15<100，所以全部的数量用来出库，即左边第二列中的第一个数据。同时，需要出库的数量变成了100-15=85，即右边倒数第二列中的第一个数据。
然后去到第二条入库记录，即“20”这一条，因为20<85，所以全部的数量用来出库，得到第二列中的第二个数据，同时，需要出库的数量变成了85-20=65。
依次类推，直到第五次操作结束，即需要出库的数量为16。
此时，需要对第六条入库记录进行出库操作，即“22”这一条，因为22>16，所以，只需要出库16个物品就可以了。而需要出库的数量变成了16-16=0。这就意味着出库任务已经完成，不再需要后续的操作了。
此时，左侧第二列就是本次出库的明细，而用入库（左侧第一列）减去这个明细，即得到出库后的库存明细。
这是非常清晰的一个过程。如果用VBA写程序，只需要一个循环即可。可惜，VBA的门槛稍高，而且在很多公司，VBA是禁用的。
如果改用传统的Excel函数来做这件事情，会发现非常困难，几乎没有办法做到自动化的处理，需要依赖中间表和手工的干预，很不理想。
但是借助LAMBDA函数，我们可以轻松实现库存成本的计算。
我们要借助于递归，不熟悉这个概念的朋友可以参见Excel这个函数功能竟然暗合孙子兵法 - 详说递归函数：什么是递归？递归能干什么？递归怎么做？。
我们的目标是计算需要出库的数量（OUT_QTY）在入库记录表S中的分布情况，假设这个计算可以通过函数：
F(out_qty, S)
来完成。
我们将S分成两部分：q和Q，
其中q就是S的第一条记录，Q是剩余的记录。
根据FIFO的原则，我们首先处理q，使用函数：
F(out_qty,q)
由于q只有一条记录，因此它很简单就可以解决：
IF(out_qty<=q,out_qty,q)处理完q后，剩余需要出库的数量变成了：
out_qty_rest =out_qty -F(out_qty,q)
这些数量就需要在Q中进行出库：
F(out_qty_rest, Q)
我们需要的结果就是：
VSTACK(F(out_qty,q),F(out_qty_rest, Q))根据上面的分析，我们可以实现下面的自定义函数：
可以直接调用这个函数：
=FIFO(D4:D11,G2)得到这样的结果：
出库明细有了，我们是本次出库的平均单价，还是出库后的库存平均单价都很容易计算了。
我们这里只是处理了一次出库的情况。要处理后续出库的情况，还需要进行多一些的处理。传统的函数方案在进行到这一步时，也会遇到比较大的问题。不过使用我们这里的方案，就很简单了，只要计算出在本次出库前库存明细，并且将这个库存明细作为当前出库的依据，从而使用FIFO进行出库即可。
这个自定义函数计算给定日期之前的库存明细。其中，
1.in_qty_col，为入库记录的数量列
2.qty_out，为给定日期之前所有出库的数量总和
3. 通过FIFO函数计算历史上所有出库数量总和在入库记录上的明细，并用入库记录减去这个出库明细，即得到给定日期时的库存分布。
通过下面的自定义函数即可计算本次出库的平均单价：
其中，
计算本次出库日期之前的库存明细
取出库存明细中的数量列
用库存明细数量列作为出库依据，进行当前要求的出库，并计算出出库明细。
最后，用这个出库明细，结合入库时每次记录的单价，加权平均即可得到本次出库的平均单价了。这个公式在本文开头就介绍过了。
详细解释请看视频
加入E学会，永久免费学习更多Excel应用技巧
/portal/learn/class_list
Excel+Power Query+Power Pivot+Power BI
自定义函数底部菜单：知识库->自定义函数
面授培训底部菜单：培训学习->面授培训
Excel企业应用底部菜单：企业应用
也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。
最新文章
-
揭秘：伤寒汗出病解的方式有哪些
6分钟前 -
洗髓修炼中的：找病、攻病、翻病、愈病！
21分钟前 -
（原创）颤证的经方治疗案。。。
27分钟前 -
中医教你：看指甲上的月牙诊病
39分钟前 -
中药方论（24）
42分钟前 -
仅一味中药就可以降血糖 ，一味中药的单药方！
44分钟前 -
解郁安神颗粒：抑郁症、焦虑症都是因为肝的疏泄功能出现了失调
55分钟前 -
有一种痛苦叫牙疼，有种止疼药叫艾灸！收藏起来，以备不时之需！
1小时前
有话要说...