俞木發(fā)

1. 問題分析

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

2. 統(tǒng)計累計庫存數(shù)

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

公式解釋：這里使用SUMIF函數(shù)進(jìn)行條件求和，其中F$2：F2作為求和條件區(qū)域，求和條件是F2的產(chǎn)品名稱，H$2：H2是實際求和區(qū)域。

3. 引用今日出庫數(shù)

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

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

4. 引用入庫數(shù)量和計算實時庫存

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

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

5. 統(tǒng)計當(dāng)日各批次產(chǎn)品的出庫金額

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

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

6. 引用去重產(chǎn)品數(shù)據(jù)

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

7. 統(tǒng)計當(dāng)天產(chǎn)品的總計金額

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

公式解釋：這里使用SUMI F函數(shù)進(jìn)行條件求和，其中$F$2：$F$10作為求和條件區(qū)域，求和條件是L 2的產(chǎn)品名稱，$E$2：$E$10是實際求和區(qū)域。

8. 實際使用

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