編按：

今天分享一個(gè)簡(jiǎn)單又實(shí)用的出入庫(kù)管理模板制作，模板具有以下功能：日常出入庫(kù)登記、庫(kù)存量實(shí)時(shí)自動(dòng)統(tǒng)計(jì)、自動(dòng)補(bǔ)貨提醒、物料庫(kù)存查詢。物料庫(kù)存查詢，支持模糊查詢，借助通配符實(shí)現(xiàn)一對(duì)多查詢。譬如，可以查所有包含字符“PG”的物料庫(kù)存。當(dāng)物品的庫(kù)存量低于安全庫(kù)存數(shù)量時(shí)突出顯示為，提醒需要補(bǔ)貨。一起來(lái)看看吧！

一個(gè)簡(jiǎn)單又實(shí)用的庫(kù)存管理模板是由四部分構(gòu)成：基礎(chǔ)數(shù)據(jù)表（也叫基本信息表）、出入庫(kù)記錄表（也叫流水明細(xì)表）、庫(kù)存統(tǒng)計(jì)表（也叫結(jié)果匯總表）、庫(kù)存查詢表。在文章中，為了簡(jiǎn)化，將基礎(chǔ)數(shù)據(jù)表與庫(kù)存統(tǒng)計(jì)表合二為一了。

今天，我們就來(lái)給大家講解詳細(xì)的制作過(guò)程。

一、基礎(chǔ)數(shù)據(jù)表的做法

基礎(chǔ)數(shù)據(jù)表一定要規(guī)范，關(guān)于數(shù)據(jù)源的問(wèn)題，之前有給大家分享過(guò)好幾篇教程了，還不了解的同學(xué)可以點(diǎn)擊下面的鏈接查看：

《Excel運(yùn)用規(guī)范1：一個(gè)單元格只記錄一條信息》

《Excel規(guī)范2：如何做一張能滿足后續(xù)分析需求的表格？》

《數(shù)據(jù)源表規(guī)范》

庫(kù)存管理模板的基礎(chǔ)數(shù)據(jù)可以根據(jù)自己公司的實(shí)際需求進(jìn)行設(shè)計(jì)，但是要遵守一個(gè)基本原則：表格要能夠體現(xiàn)出物品的所有屬性，并且每個(gè)屬性單獨(dú)一列進(jìn)行存放，一定不要出現(xiàn)合并單元格。下圖就是一個(gè)比較標(biāo)準(zhǔn)的基礎(chǔ)數(shù)據(jù)表。

二、出入庫(kù)記錄表的做法

出庫(kù)和入庫(kù)可以分為兩個(gè)sheet分別登記，也可以合并在一起登記。它們實(shí)際上就是一個(gè)流水賬，必須要有的是產(chǎn)品的基本信息，發(fā)生出入庫(kù)的日期（或具體時(shí)間），出入庫(kù)的數(shù)量。除此外，通常還包括入庫(kù)經(jīng)辦人、出庫(kù)領(lǐng)料部門(mén)、領(lǐng)料人等。

示例如下：

在這個(gè)記錄表中，只有藍(lán)色的這幾列需要手工登記。分類、名稱和單位等基本信息都是通過(guò)公式來(lái)自動(dòng)生成的。

公式為：=IFERROR(VLOOKUP($A2,基礎(chǔ)數(shù)據(jù)表!$B:$E,COLUMN(B1),0),"編碼有誤請(qǐng)核查！")

公式是利用A列的產(chǎn)品編碼在基礎(chǔ)數(shù)據(jù)表中匹配對(duì)應(yīng)的信息，如果匹配不到則返回錯(cuò)誤提示，公式用到的函數(shù)比較簡(jiǎn)單，就不再贅述了。

三、庫(kù)存統(tǒng)計(jì)表

庫(kù)存統(tǒng)計(jì)表有兩種形式，一種是在基礎(chǔ)數(shù)據(jù)表中添加一些統(tǒng)計(jì)字段，使基礎(chǔ)表具備一些統(tǒng)計(jì)的信息。另外一種是根據(jù)出入庫(kù)記錄表生成一些統(tǒng)計(jì)匯總表，這個(gè)就沒(méi)有統(tǒng)一的模板了，但是可以使用數(shù)據(jù)透視表來(lái)靈活設(shè)計(jì)。

以下重點(diǎn)介紹第一種情況，在基礎(chǔ)表中添加統(tǒng)計(jì)字段。

根據(jù)實(shí)際需要可以設(shè)計(jì)統(tǒng)計(jì)字段，本例添加了6個(gè)字段，具體如下圖所示。

初始庫(kù)存和安全庫(kù)存需要人工錄入，初始庫(kù)存原則是只填一次，后期不需要修改，安全庫(kù)存可以根據(jù)具體情況隨時(shí)做調(diào)整。

其他四個(gè)字段都是公式計(jì)算的，以下分別介紹。

累計(jì)出庫(kù)數(shù)量的公式：=SUMIF(出入庫(kù)記錄表!A:A,B2,出入庫(kù)記錄表!F:F)

累計(jì)入庫(kù)數(shù)量的公式：=SUMIF(出入庫(kù)記錄表!A:A,B2,出入庫(kù)記錄表!G:G)

當(dāng)前庫(kù)存數(shù)量是用初始庫(kù)存-累計(jì)出庫(kù)數(shù)量+累計(jì)入庫(kù)數(shù)量，公式為=F2-G2+H2

庫(kù)存狀態(tài)包括庫(kù)存充足、庫(kù)存不足、已缺貨三種，可以使用IF函數(shù)判斷，公式為：=IF(I2<=0,"已缺貨",IF(I2
J2,"庫(kù)存不足","庫(kù)存充足"))

為庫(kù)存狀態(tài)添加條件格式，“已缺貨”顯示為紅色，“庫(kù)存不足”顯示為土黃色，如此醒目，方便安排補(bǔ)貨。

四、庫(kù)存查詢表
新建一個(gè)“庫(kù)存查詢”工作表，參考樣式如下。

用VLOOKUP函數(shù)查找輸入的產(chǎn)品編號(hào)，即可獲得需要的庫(kù)存數(shù)據(jù)。但是直接這么寫(xiě)公式，則要求用表的人必須輸入完整編號(hào)或者物料名稱才能查詢，增加了使用難度，不夠人性化。因此，我們這里做成一對(duì)多模糊查詢，只要輸入部分編碼即可實(shí)現(xiàn)查詢。

返回到“基礎(chǔ)數(shù)據(jù)表”工作表，插入一個(gè)“輔助查詢”列。在A2中輸入公式=IF(庫(kù)存查詢!$B$2="","",COUNTIF($C$2:C2,"*"&庫(kù)存查詢!$B$2&"*"))并下拉填充。此處采用了通配符，為所有包含輸入編碼的產(chǎn)品生成不同的數(shù)字，方便后續(xù)實(shí)現(xiàn)查詢。

回到“庫(kù)存查詢”工作表。在A4中輸入公式=IFERROR(VLOOKUP(ROW($A1),基礎(chǔ)數(shù)據(jù)表!$A$1:$L$62,MATCH(A$3,基礎(chǔ)數(shù)據(jù)表!$A$1:$L$1,0),0),"")，右拉填充；然后再下拉填充。

在B2中輸入“PGL”，即可查詢所有編碼中包含“PGL”的物料庫(kù)存，如下。

此處的庫(kù)存的模糊查詢實(shí)質(zhì)就是利用通配符和COUTIF函數(shù)生成符合條件的數(shù)字，然后再用VLOOKUP查詢數(shù)字實(shí)現(xiàn)一對(duì)多模糊查詢。到這里，一個(gè)簡(jiǎn)單的出入庫(kù)管理模板就做好了。歡迎到部落窩教育網(wǎng)看Excel實(shí)用教程。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

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

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

Excel做庫(kù)存電子臺(tái)賬案例：倉(cāng)庫(kù)出入庫(kù)臺(tái)賬匯總

Excel里的條件格式圖標(biāo)集，你會(huì)用嗎？

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者老菜鳥(niǎo)；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。