編按：

所有大型公司都會要求庫存管理中心的員工，在基于先進先出的原則上，學會EXCEL庫存管理表。是否能熟練地使用EXCEL庫存管理表，完成日常進出貨的查詢和記錄工作，更是讓領(lǐng)導可以清晰地洞悉員工們的工作能力高低。今天，小E給大家?guī)淼木褪牵?個公式輕松制作庫存管理表的方法……

庫存管理制度里有一條重要的原則：先進先出。先進先出法是指以先購入的存貨應先發(fā)出(即用于銷售或耗用) 這樣一種存貨實物流動假設為前提，對發(fā)出存貨進行計價的一種方法。先進先出在財務成本管理上有著重要的作用。

下面是一張根據(jù)某公司的庫存管理系統(tǒng)簡化了的表格。

現(xiàn)在要求根據(jù)先進先出的原則出庫。如何利用EXCEL函數(shù)公式來做到先進先出，并顯示庫存結(jié)余？

1.庫存余額的公式

根據(jù)先進先出原則，前一個批次的貨物沒有出完，是不能出后一個批次的。因此，單元格K2“庫存余額”中的公式應該是“=IF(E2-C9<0,0,E2-C9)”。公式比較簡單，這里不再過多解釋了。

2.當前出貨批號和供應商的公式

單元格I2“當前出貨批號”中的公式為“=LOOKUP(1,0/FREQUENCY(SUM($K$2:$K2),SUMIF(OFFSET($E$2,,,ROW($1:3)),">0")),C$2:C$6)”，向右拖曳到單元格J2就可以了。

函數(shù)解析：

1.   SUM($K$2:$K2)部分，是一個動態(tài)求和區(qū)域。

2.   SUMIF(OFFSET($E$2,,,ROW($1:3)),">0")部分，這是一個多維引用的應用。SUMIF函數(shù)配合OFFSET函數(shù)，分別求了求和區(qū)域為1行1列、2行1列和3行1列的數(shù)據(jù)區(qū)域，并返回結(jié)果{23;73;106}。

3.   利用FREQUENCY函數(shù)對SUM($K$2:$K2)計頻，計頻點是SUMIF(OFFSET($E$2,,,ROW($1:3)),">0")，得到的結(jié)果是{1;0;0;0}。

4.   利用LOOKUP函數(shù)的經(jīng)典用法，得到當前的出貨批號是“1130”。

5.   公式向右拖動后得到供應商“年禾”。

3.庫存余額的公式升級版

接下來，一起來看看單元格K3中的公式。

在單元格K3中輸入“=IF(E3>0,IF(K2>0,E3,IF(SUM($E$2:E3)-SUM($C$9:$C$11)<=0,0,SUM($E$2:E3)-SUM($C$9:$C$11))),)”并向下拖曳即可。

函數(shù)解析：

這是一個IF函數(shù)的嵌套公式。本身公式并不復雜，純粹的是一個數(shù)學邏輯的過程，這里不再詳細介紹了。

4.當前出貨批號和供應商的公式升級版

接下來，小伙伴們需要著重理解，和單元格K3相對應的的批號和供應商的公式。

在單元格I3中輸入公式“=IF($K2=0,C3,IF(SUM($C$9:$C10)>SUM($E$2:$E3),C3,LOOKUP(1,0/FREQUENCY(SUM($C$9:$C10),SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1),C$2:C$6)))”，并向下向右拖曳。

函數(shù)解析：

1.   第一層邏輯判斷：當單元格K2=0時，意味著批號“1130”已經(jīng)全部出貨，因此要開始下個批次的出貨；當單元格K2不為0時，意味著還要在當前批次出貨。

2.   接下來執(zhí)行IF(SUM($C$9:$C10)>SUM($E$2:$E3),C3,LOOKUP(1,0/FREQUENCY(SUM($C$9:$C10),SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1),C$2:C$6))這部分。這部分也是一個邏輯判斷過程。

①當SUM($C$9:$C10)>SUM($E$2:$E3)時，意味著當前要出貨的數(shù)量大于當前出貨批次及之前的出貨批次的數(shù)量和，當前批次被出清，同時保留當前批次號。

②當SUM($C$9:$C10)不大于SUM($E$2:$E3)時，則執(zhí)行LOOKUP(1,0/FREQUENCY(SUM($C$9:$C10),SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1),C$2:C$6)這部分。邏輯思路和上面介紹的相同。只不過要注意SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1的妙用。

3.   FREQUENCY函數(shù)計頻，計頻點是左開右閉的，即計頻區(qū)間是大于某數(shù)，小于等于某數(shù)。這里舉個例子，出庫數(shù)量剛剛好等于計頻點時，比如73，當前的出貨批次已經(jīng)出清。如果不減去0.1（其實任何一個非常小的小數(shù)都是可以的），公式仍然會返回當前已出清的批次號。這種情況下我們希望公式返回下一個出貨批次，因此就需要人工來創(chuàng)造一個新的計頻區(qū)間來返回我們希望的批次號。

總結(jié)：設置全部完成后，可以看出這個圖表有以下幾個特點。

1.   當前批次沒有出清時，后一批次是不會被出貨的

2.   動態(tài)顯示每個批次下的庫存數(shù)

同時，通過本例大家也學習到了多維引用的實際應用，以及SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1計頻方式的巧妙構(gòu)思。

多維引用是EXCEL公式應用的一個重要思維，學會它，再復雜的公式，也可以讓它被抽絲剝繭，更可以按自己的需求自由構(gòu)建。所以，小伙伴們平時要多練習哦~本案例的課件，可以在后臺領(lǐng)取哈~

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

僅用四個函數(shù)做一個自動統(tǒng)計庫存數(shù)量的進銷存表

Excel做庫存電子臺賬案例：倉庫出入庫臺賬匯總

用Excel制作一個實現(xiàn)自動提醒補貨和動態(tài)查詢補貨數(shù)量的表

IF函數(shù)，剝洋蔥

版權(quán)申明：

本文作者EXCEL應用之家；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。