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。

1. 首先去到第一条入库记录，即“15”这一条，因为15<100，所以全部的数量用来出库，即左边第二列中的第一个数据。同时，需要出库的数量变成了100-15=85，即右边倒数第二列中的第一个数据。

2. 然后去到第二条入库记录，即“20”这一条，因为20<85，所以全部的数量用来出库，得到第二列中的第二个数据，同时，需要出库的数量变成了85-20=65。

3. 依次类推，直到第五次操作结束，即需要出库的数量为16。

4. 此时，需要对第六条入库记录进行出库操作，即“22”这一条，因为22>16，所以，只需要出库16个物品就可以了。而需要出库的数量变成了16-16=0。这就意味着出库任务已经完成，不再需要后续的操作了。

5. 此时，左侧第二列就是本次出库的明细，而用入库（左侧第一列）减去这个明细，即得到出库后的库存明细。

这是非常清晰的一个过程。如果用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函数计算历史上所有出库数量总和在入库记录上的明细，并用入库记录减去这个出库明细，即得到给定日期时的库存分布。

通过下面的自定义函数即可计算本次出库的平均单价：

其中，

1. 计算本次出库日期之前的库存明细
   

2. 取出库存明细中的数量列

3. 用库存明细数量列作为出库依据，进行当前要求的出库，并计算出出库明细。

最后，用这个出库明细，结合入库时每次记录的单价，加权平均即可得到本次出库的平均单价了。这个公式在本文开头就介绍过了。

详细解释请看视频

加入E学会，永久免费学习更多Excel应用技巧

/portal/learn/class_list

Excel+Power Query+Power Pivot+Power BI

自定义函数底部菜单：知识库->自定义函数

面授培训底部菜单：培训学习->面授培训

Excel企业应用底部菜单：企业应用

也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。