用活Excel函數　先進先出計算更簡單

俞木發

1. 問題分析

下表是公司產品的庫存統計。現在需要根據今日出庫數量，統計出實時庫存和今日銷售金額（圖1）。根據“先進先出”的原則，我們在每次發貨時需要根據本次發貨數量和累計入庫數量進行核對，如果發貨數小于截止到當期的累計入庫數，那么實時庫存就是“累計入庫-出庫數”，銷售額為“出庫數*入庫價格”。但是，如果發貨數大于截止到當期的累計入庫數，那么就需要從下一批次入庫數中提取部分補充出庫（以此類推），這樣實時庫存數和銷售額就要分開計算。顯然，解決問題的方法就是先統計累計庫存，然后使用條件函數判斷，最后分批計算銷售金額。

2. 統計累計庫存數

在A 列前插入“入庫累計”“今日出庫”“入庫數量”“ 實時庫存”“產品各批次出庫額”5個新列，接著在A 2單元格中輸入公式“=SUMIF（F$2：F2，F2，H$2：H2）”并下拉填充，即可得到所有產品的入庫累計數量（圖2）。

公式解釋：這里使用SUMIF函數進行條件求和，其中F$2：F2作為求和條件區域，求和條件是F2的產品名稱，H$2：H2是實際求和區域。

3. 引用今日出庫數

量定位到B2單元格，輸入公式“=IFERROR（VLOOKUP（F：F，$L$2：$M$7，2，0），0）”并下拉填充，將當天出庫的數量引用到B列中，將其和A列中的累計入庫數量進行比較（圖3）。

公式解釋：這里使用VLOOKUP函數對今日出庫數量進行引用，其中F：F是要匹配的產品，查找的區域是$L$2：$M$7，即今日出庫的產品列表。最后在外層嵌套IFERROR函數進行判斷，沒有引用值時顯示為“0”，否則顯示引用值。

4. 引用入庫數量和計算實時庫存

定位到C2單元格，輸入公式“=H2”并下拉填充;定位到D2單元格，輸入公式“=IFS（A2<=B2，0，A2>B2，A2-B2）”并下拉填充。這樣通過條件函數的比較可以獲得實時庫存的數據（圖4）。

公式解釋：這里使用I FS函數進行多條件的判斷，如果A2<=B2，即累計入庫的數量小于等于當天的出庫數，也就是截止到當日累計入庫的數量不夠當日的出庫，這時實時庫存就是“0”。如果A 2>B2，即累計入庫數比當天出庫大，實時庫存數就是“累計入庫數-當日出庫”。

5. 統計當日各批次產品的出庫金額

由于不同批次的產品價格不同，在累計庫存不足需要提取下一批次的數量時，需要對不同批次數量分別統計。定位到E2單元格，輸入公式“=IFS（B2= 0，0，D2= 0，C2 *I2，C2-D2<=0，0，AND（A2>B2，C2-D2>0），（C2-D2）*I2）”并下拉填充（圖5）。

公式解釋：這里使用IFS函數進行多條件判斷，當B2= 0時，即表示當天沒有出庫;當D2=0時，即表示這一批次全部出庫，銷售額=“入庫數*入庫單價”;當C2-D2<=0時，即表示累計庫存小于等于實時庫存，本批次沒有出庫，即銷售額也為0。當A2>B2且C2-D2>0時，即表示累計庫存數大于出庫數，且累計庫存大于實時庫存，表示這批次有部分出庫銷售，這時“銷售額=（當日出庫-實時庫存）*入庫單價”。

6. 引用去重產品數據

當日出庫的產品都是從F列中提取，但是F列中入庫的產品名稱會有重復（不同日期入庫同一產品），所以需要去重。選中F2：F10數據區域，復制后粘貼到L 2：L10區域，選中L2：L10區域并依次點擊“數據→去除重復數據→以當前選定的區域排序”即可。

7. 統計當天產品的總計金額

完成各批次產品的出庫金額統計后，定位到N2單元格，輸入公式“=SUMIF（$F$2：$F$10，L2，$E$2：$E$10）”并下拉填充。

公式解釋：這里使用SUMI F函數進行條件求和，其中$F$2：$F$10作為求和條件區域，求和條件是L 2的產品名稱，$E$2：$E$10是實際求和區域。

8. 實際使用

完成上述的設置后，選中A：E列并將其隱藏。定位到J2單元格，輸入公式“=D2”并下拉填充，這樣每天需要出庫時，只要在L、M列中輸入當天的出庫產品和出庫數量，J列和N列中就會自動顯示實時庫存和銷售金額的數據了（圖6）。