【原】Excel函數(shù)式編程，輕松完成FIFO（先入先出法）庫(kù)存成本計(jì)算難題

ExcelEasy 2022-10-31 發(fā)布于北京

展開(kāi)全文

在使用Excel進(jìn)行庫(kù)存管理時(shí)，我們需要計(jì)算庫(kù)存成本（包括當(dāng)前庫(kù)存成本以及每次出庫(kù)的成本）。當(dāng)涉及到同一物品多次出庫(kù)入庫(kù)時(shí)，這種計(jì)算需要按照預(yù)先指定的規(guī)則進(jìn)行，常用的規(guī)則有：

FIFO（先入先出法）：即每次出庫(kù)都從庫(kù)存中最早的入庫(kù)物資開(kāi)始，如果最早的入庫(kù)數(shù)量不足以保證出庫(kù)數(shù)量，那么剩余數(shù)量的物資依次從以后的每條中進(jìn)行出庫(kù)。
LIFO（后入先出法）：與FIFO相反，每次出庫(kù)從最近的入庫(kù)開(kāi)始。
平均法

不管是FIFO，還是LIFO，實(shí)際計(jì)算的時(shí)候都比較復(fù)雜。主要是很難利用Excel公式進(jìn)行自動(dòng)化的計(jì)算工作。

背景及數(shù)據(jù)

用Excel進(jìn)行庫(kù)存管理時(shí)，我們建議維護(hù)兩張表：

入庫(kù)表
出庫(kù)表

其中，入庫(kù)表的數(shù)據(jù)是手工輸入的。出庫(kù)表的前4列是手工輸入的，出庫(kù)單件是需要計(jì)算的（即出庫(kù)成本）。

很明顯，要計(jì)算入庫(kù)后的平均單價(jià)很容易。比如，要計(jì)算2022/2/10的庫(kù)存成本（在這一天，所有的入庫(kù)已經(jīng)完成，并且沒(méi)有發(fā)生任何出庫(kù)操作）

只要將入庫(kù)量和價(jià)格做加權(quán)平均就好了：

=SUMPRODUCT(入庫(kù)量, 價(jià)格)/SUM(入庫(kù)量)

但是一旦發(fā)生一次出庫(kù)，比如要計(jì)算2022/3/2的庫(kù)存成本，就需要知道2022/3/1這次出庫(kù)是從哪些入庫(kù)記錄中進(jìn)行的。尤其是，如果出庫(kù)涉及多條入庫(kù)時(shí)，這個(gè)計(jì)算并不容易。

注：這兩個(gè)表是簡(jiǎn)化后的表，只有必要的字段，實(shí)際場(chǎng)景可能需要更多的字段。不過(guò)不影響我們對(duì)這個(gè)問(wèn)題的理解。

注：很多使用Excel管理庫(kù)存的朋友喜歡在一張表上同時(shí)維護(hù)入庫(kù)和出庫(kù)信息，實(shí)際上也沒(méi)有問(wèn)題。本文介紹的方法很容易就可以移植到這種場(chǎng)景中。

注：還有很多朋友喜歡在一張表上同時(shí)維護(hù)入庫(kù)、出庫(kù)和當(dāng)前庫(kù)存信息。強(qiáng)烈不建議這么做。在Excel中這么做會(huì)帶來(lái)很多不利的影響。在“Excel工作的標(biāo)準(zhǔn)模式”課程中，我們?cè)敿?xì)介紹過(guò)，這里就不重復(fù)了。實(shí)際上，當(dāng)前庫(kù)存可以很容易通過(guò)函數(shù)得到。

注：本文案例中入庫(kù)表和出庫(kù)表只有一種物品，并且是按照日期順序進(jìn)行升序排列。實(shí)際情況并不如此，但是我們?nèi)匀豢梢赃@么假設(shè)，因?yàn)槲覀兛梢酝ㄟ^(guò)FILTER函數(shù)和SORT函數(shù)輕松達(dá)到這樣的要求。

FIFO分析

下面我們先以手工的方式分析第一次出庫(kù)時(shí)FIFO的操作。

在計(jì)算庫(kù)存成本時(shí)，其實(shí)最重要的就是要知道每次出庫(kù)需要從哪些入庫(kù)記錄中進(jìn)行操作，所以下面的分析就集中在這個(gè)環(huán)節(jié)。

最左邊是入庫(kù)記錄，按照入庫(kù)日期升序。最右邊是出庫(kù)數(shù)量：100。

首先去到第一條入庫(kù)記錄，即“15”這一條，因?yàn)?5<100，所以全部的數(shù)量用來(lái)出庫(kù)，即左邊第二列中的第一個(gè)數(shù)據(jù)。同時(shí)，需要出庫(kù)的數(shù)量變成了100-15=85，即右邊倒數(shù)第二列中的第一個(gè)數(shù)據(jù)。
然后去到第二條入庫(kù)記錄，即“20”這一條，因?yàn)?0<85，所以全部的數(shù)量用來(lái)出庫(kù)，得到第二列中的第二個(gè)數(shù)據(jù)，同時(shí)，需要出庫(kù)的數(shù)量變成了85-20=65。
依次類推，直到第五次操作結(jié)束，即需要出庫(kù)的數(shù)量為16。
此時(shí)，需要對(duì)第六條入庫(kù)記錄進(jìn)行出庫(kù)操作，即“22”這一條，因?yàn)?2>16，所以，只需要出庫(kù)16個(gè)物品就可以了。而需要出庫(kù)的數(shù)量變成了16-16=0。這就意味著出庫(kù)任務(wù)已經(jīng)完成，不再需要后續(xù)的操作了。
此時(shí)，左側(cè)第二列就是本次出庫(kù)的明細(xì)，而用入庫(kù)（左側(cè)第一列）減去這個(gè)明細(xì)，即得到出庫(kù)后的庫(kù)存明細(xì)。

這是非常清晰的一個(gè)過(guò)程。如果用VBA寫程序，只需要一個(gè)循環(huán)即可。可惜，VBA的門檻稍高，而且在很多公司，VBA是禁用的。

如果改用傳統(tǒng)的Excel函數(shù)來(lái)做這件事情，會(huì)發(fā)現(xiàn)非常困難，幾乎沒(méi)有辦法做到自動(dòng)化的處理，需要依賴中間表和手工的干預(yù)，很不理想。

但是借助LAMBDA函數(shù)，我們可以輕松實(shí)現(xiàn)庫(kù)存成本的計(jì)算。

FIFO自定義函數(shù)

我們要借助于遞歸，不熟悉這個(gè)概念的朋友可以參見(jiàn)Excel這個(gè)函數(shù)功能竟然暗合孫子兵法 - 詳說(shuō)遞歸函數(shù)：什么是遞歸？遞歸能干什么？遞歸怎么做？。

我們的目標(biāo)是計(jì)算需要出庫(kù)的數(shù)量（OUT_QTY）在入庫(kù)記錄表S中的分布情況，假設(shè)這個(gè)計(jì)算可以通過(guò)函數(shù)：

F(out_qty, S)

來(lái)完成。

我們將S分成兩部分：q和Q，

其中q就是S的第一條記錄，Q是剩余的記錄。

根據(jù)FIFO的原則，我們首先處理q，使用函數(shù)：

F(out_qty, q)

由于q只有一條記錄，因此它很簡(jiǎn)單就可以解決：

IF(out_qty <= q, out_qty, q)

處理完q后，剩余需要出庫(kù)的數(shù)量變成了：

out_qty_rest = out_qty - F(out_qty, q)

這些數(shù)量就需要在Q中進(jìn)行出庫(kù)：

F(out_qty_rest, Q)

我們需要的結(jié)果就是：

VSTACK(F(out_qty, q), F(out_qty_rest, Q))

根據(jù)上面的分析，我們可以實(shí)現(xiàn)下面的自定義函數(shù)：

可以直接調(diào)用這個(gè)函數(shù)：

=FIFO(D4:D11,G2)

得到這樣的結(jié)果：

出庫(kù)明細(xì)有了，我們是本次出庫(kù)的平均單價(jià)，還是出庫(kù)后的庫(kù)存平均單價(jià)都很容易計(jì)算了。

后續(xù)出庫(kù)怎么計(jì)算

我們這里只是處理了一次出庫(kù)的情況。要處理后續(xù)出庫(kù)的情況，還需要進(jìn)行多一些的處理。傳統(tǒng)的函數(shù)方案在進(jìn)行到這一步時(shí)，也會(huì)遇到比較大的問(wèn)題。不過(guò)使用我們這里的方案，就很簡(jiǎn)單了，只要計(jì)算出在本次出庫(kù)前庫(kù)存明細(xì)，并且將這個(gè)庫(kù)存明細(xì)作為當(dāng)前出庫(kù)的依據(jù)，從而使用FIFO進(jìn)行出庫(kù)即可。