365新函数：使用TAKE和DROP函数轻松搞定先入先出法计算

shaowu459

先进先出法 （FIFO，first in, first out）是指根据先入库先发出的原则，对于发出的存货以先入库存货的单价计算发出存货成本的方法。

如下图所示，E3单元格中出库的3个A产品来自B2单元格入库的5个，单价是10元，因此出库成本是30元。E8单元格出库的3个A产品是B2单元格入库剩余的2个（单价10元）和B6单元格入库的1个（单价11元），因此出库成本是10*2+11=31元。

shaowu459

使用低版本函数可以解决此问题，但公式稍微复杂，细节处理比较多，但使用365新函数中的TAKE函数和DROP函数则可轻松实现相关计算，计算逻辑变得非常简单。

F2单元格输入以下公式，向下填充到F9单元格：

1. =SUM(IF(E2,TAKE(DROP(TOCOL(IF((B$2:B2>=COLUMN(A:ZZ))*(A$2:A2=A2),C$2:C2,\),3),SUMIF(A$1:A1,A2,E$1)),E2)))

复制代码

公式的总体思路说明如下：

1）把每次入库的产品单价按入库数量堆叠起来。例如，入库了5个A产品，单价是10，那么就转化成5个10的数组{10;10;10;10;10}，再入库3个A产品，单价是11，就继续堆叠，变成一个7行的数组：{10;10;10;10;10;11;11;11}。

2）假设第一次出库了3个A产品，那么就用TAKE函数取数组{10;10;10;10;10;11;11;11}中的前3行{10;10;10}，再用SUM函数求和即可。假设第二次又出库了3个A产品，首先用DROP函数将已出库的3个产品单价去掉（去掉数组前3行），将数组变成{10;10;11;11;11}，再用TAKE函数提取前3行{10;10;11}求和即可。

3）简单总结一下，思路就是将入库产品单价按个排列，一个一个的出库。将本次出库之前出库的行数去掉后，再从剩余的数组中提取本次出库数量的行数后求和。

shaowu459

以F8单元格的公式为例简单拆解一下公式：

1. =IF((B$2:B8>=COLUMN(A:ZZ))*(A$2:A8=A8),C$2:C8,\)

复制代码

这部分公式利用COLUMN(A:ZZ)将2-8行各产品的入库单价展开，如果A列产品是A8单元格（A产品）的话就保留入库单价，否则返回“\”制造错误值。结果如下图：

1. =TOCOL(IF((B$2:B8>=COLUMN(A:ZZ))*(A$2:A8=A8),C$2:C8,\),3)

复制代码

而后使用TOCOL函数将刚才的数组转化成一列，并使用第二参数的3去掉错误值：

1. =SUMIF(A$1:A7,A8,E$1)

复制代码

使用SUMIF函数获得当前行之前A产品已出库数量：

1. =DROP(TOCOL(IF((B$2:B8>=COLUMN(A:ZZ))*(A$2:A8=A8),C$2:C8,\),3),SUMIF(A$1:A7,A8,E$1))

复制代码

使用DROP函数将单价数组中已出库的行数去掉，简化来说是DROP(单价数组, SUMIF(A$1:A7,A8,E$1))，剩余的数组就是尚未出库的每个产品的单价，排在上面的是先入库的。

1. =TAKE(DROP(TOCOL(IF((B$2:B8>=COLUMN(A:ZZ))*(A$2:A8=A8),C$2:C8,\),3),SUMIF(A$1:A7,A8,E$1)),E8)

复制代码

使用TAKE函数从上一步生成的未出库A产品单价列表中提取本次出库的数量3个（E8单元格的值），生成一个3行的数组，也即本次出库的3个产品的单价：

1. =SUM(IF(E8,TAKE(DROP(TOCOL(IF((B$2:B8>=COLUMN(A:ZZ))*(A$2:A8=A8),C$2:C8,\),3),SUMIF(A$1:A7,A8,E$1)),E8)))

复制代码

因为只有出库列有数值的时候（大于0）才需要进行计算出库成本，因此使用IF函数判断一下，当E8单元格值大于0时才进行上述运算。提取出出库产品的单价之后，用SUM函数求和即可。

shaowu459

由于公式中使用的是每个产品的单价列表，因此如果需要在计算出出库成本之前计算出每次出库的平均单价，把最外层的SUM函数替换成AVERAGE函数即可：

1. =AVERAGE(IF(E2,TAKE(DROP(TOCOL(IF((B$2:B2>=COLUMN(A:ZZ))*(A$2:A2=A2),C$2:C2,\),3),SUMIF(A$1:A1,A2,E$1)),E2)))

复制代码

另外，如果入库统计在一起，出库统计在一起，公式基本不用调整，改变引用即可：

满坛皆为吾师

先顶后看，超人老师这速度要去写小说的话日更上万啊

shaowu459

满坛皆为吾师 发表于 2022-11-25 00:33
先顶后看，超人老师这速度要去写小说的话日更上万啊

还没睡，早点休息～这个是搬过来的，所以快。明天有空写写Bom成本计算，慢慢写

FeiF140

第一步按照数量和单价column平铺，如果入库数量是50000，就会显示溢出？不知道是不是我理解错了？

shaowu459

FeiF140 发表于 2022-11-27 13:07
第一步按照数量和单价column平铺，如果入库数量是50000，就会显示溢出？不知道是不是我理解错了？

是这样只把B2改成50000吗？首先，如果数量特别多，后面column生成的一行多列的自然数数组，可以用sequence函数来替代，要不然不够。


按说不会出现溢出错误，可以上传或者截图出错的公式和原数据。

丢丢表格

先进分子，  我是这样做的。（如果出货量大时，计算量就有点大）

=IF(E2,SUM(LOOKUP(ROW(INDIRECT("1:"&E2))+SUMIF(A$1:A1,A2,E$1),SUMIF(OFFSET(A$1,,,ROW($1:1)),A2,B$1)+1,C$2:C2)),)

shaowu459

丢丢表格 发表于 2022-11-27 17:39
先进分子，  我是这样做的。（如果出货量大时，计算量就有点大）

=IF(E2,SUM(LOOKUP(ROW(INDIRECT("1:"& ...

对，老版函数是能做出来的。不过有了新函数，感觉理解起来会更直接一些所以也发了这个帖子，也算一种方法吧。