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各種技巧。
最新文章
-
“豫西中醫界之泰鬥喬保均”治療疑難病60年經驗賞析
5小時前 -
回憶、健忘和灑脫
5小時前 -
弘一法師：“真的不忍心告訴你，這個世界隻是一個夢。你一輩子執
6小時前 -
心理學，準的讓你驚叫
6小時前 -
人生，成大器者有四識，知識、見識、膽識、遠識
6小時前 -
俞和：被遺忘的書法家，以古為師，創新在手！
6小時前 -
2024屆新高考II卷語文真題答案及解析
6小時前 -
生前隻是小人物，死後震驚史學界
7小時前
熱門文章
-
每日一誦傷寒論第241條
1周前 -
老張老李侃門球之140篇
1天前 -
這個穴位可以治療多種胃痛腹痛，還可以減肥
5天前 -
為什麼五點鐘要起床答案讓人吃驚！（現在知道還不晚）
1周前 -
治療坐骨神經痛藥酒５方
1周前 -
美麗中國-2870：中國最大的内陸河，塔裡木河
1天前 -
二十四山開門放水作竈真訣開門放水作竈直訣——子山
1天前 -
門球技巧 隻需五個字讓你打好門球
1天前
有話要說...