用活Excel函數(shù)　先進(jìn)先出計(jì)算更簡單

俞木發(fā)

1. 問題分析

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

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

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

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

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

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

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

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

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

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

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

由于不同批次的產(chǎn)品價(jià)格不同，在累計(jì)庫存不足需要提取下一批次的數(shù)量時(shí)，需要對(duì)不同批次數(shù)量分別統(tǒng)計(jì)。定位到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時(shí)，即表示當(dāng)天沒有出庫;當(dāng)D2=0時(shí)，即表示這一批次全部出庫，銷售額=“入庫數(shù)*入庫單價(jià)”;當(dāng)C2-D2<=0時(shí)，即表示累計(jì)庫存小于等于實(shí)時(shí)庫存，本批次沒有出庫，即銷售額也為0。當(dāng)A2>B2且C2-D2>0時(shí)，即表示累計(jì)庫存數(shù)大于出庫數(shù)，且累計(jì)庫存大于實(shí)時(shí)庫存，表示這批次有部分出庫銷售，這時(shí)“銷售額=（當(dāng)日出庫-實(shí)時(shí)庫存）*入庫單價(jià)”。

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

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

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

完成各批次產(chǎn)品的出庫金額統(tǒng)計(jì)后，定位到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是實(shí)際求和區(qū)域。

8. 實(shí)際使用

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