1. 首頁 > 生活百科 > >

Excel小技巧：制作簡易的倉庫管理系統

生活百科

相信很多從事倉儲物流的小伙伴們肯定是少不了庫存登記管理，今天小編為大家實例分享如何使用Excel表格制作一個簡易的進銷存系統：區別顯示出入庫明細自動統計累計庫存以及金額根據關鍵字查詢某產品匯總明細連續不間斷的序號，產品編碼下拉菜單選擇后自動匹配相關信
相信很多從事倉儲物流的小伙伴們肯定是少不了庫存登記管理，今天小編為大家實例分享如何使用Excel表格制作一個簡易的進銷存系統：
區別顯示出入庫明細自動統計累計庫存以及金額根據關鍵字查詢某產品匯總明細連續不間斷的序號，產品編碼下拉菜單選擇后自動匹配相關信息希望這樣的實例在大家看過之后都是有用處的！如果你需要源文件的話可以留言交流一下

Excel小技巧：制作簡易的倉庫管理系統

文章插圖
1、制作好基礎的Excel表格創建產品的信息
①在A10中輸入公式
=IF(B10="","",SUBTOTAL(103,$B$10:B10))下拉填充公式即可
公式解釋：如果B10中是空值就填充空值，否則就是填充連續的序號，這樣設置之后如果刪除某行的時候序號也不會間斷!
②設置數據的有效性：選擇C10：D23點擊數據——有效性——允許下拉填充為序列——在引用位置輸入內容即可（√） 。同樣也可以設置編碼的有效性，就可以避免錄入錯誤了
Excel小技巧：制作簡易的倉庫管理系統

文章插圖
③導入產品基礎信息：在F10中輸入公式
=IFERROR(VLOOKUP($E10,商品信息!$B:$F,MATCH(F$8,商品信息!$1:$1,0)-1,),"")
向右填充至J列后下拉填充公式即可 。公式解釋：根據E10中錄入的產品編碼，到信息表中查找匹配該商品的詳細情況：
第一參數：$E10作為查找值第二參數：查找區域商品信息!$B:$F第三參數：返回列數MATCH(F$8,商品信息!$1:$1,0)-1,)查找F8在商品信息中的列數第四參數：0或者省略代表精確查找最外層嵌套一個IFERROR函數將錯誤值轉化為空值
Excel小技巧：制作簡易的倉庫管理系統

文章插圖
2、 統計商品出入庫情況
①在K10中輸入公式=IF(J10="","",J10*I10)，一個簡單的判斷函數計算入庫的金額
②統計累計入庫的庫存：在L10中輸入公式
=IF(J10<>"",SUMIFS($J$10:$J10,$D$10:$D10,"√",$F$10:$F10,F10)-SUMIFS($J$10:$J10,$C$10:$C10,"√",$F$10:$F10,F10),"-")通過一個多條件求和的公式來計算入庫的累計及庫存，首先判斷D列中手否有“√”即入庫，求出總入庫的數量，再減掉出庫的數量即為累計庫存
Excel小技巧：制作簡易的倉庫管理系統

文章插圖
同樣計算累計金額:在M10中輸入公式
=IFERROR(SUMIFS($K$10:$K10,$D$10:$D10,"√",$F$10:$F10,F10)/SUMIFS($J$10:$J10,$D$10:$D10,"√",$F$10:$F10,F10)*L10,"-")
3、 制作自適應的下拉菜單：根據關鍵字查詢商品明細
①首先我們的每天的進出明細中商品中肯定會存在許多重復的，所以要先提取不重復值作為查找值的來源，那么先創建一個輔助列
在T10中輸入公式=INDEX($F$10:$F$1000,MATCH(0,COUNTIF($T$9:T9,$F$10:$F$1000),0))&"" 下拉填充公式
注意：這是一個數組公式，所以輸完需要按CTRL+SHIFT+ENTER三鍵結束才可以得出正確的結果 。
Excel小技巧：制作簡易的倉庫管理系統

文章插圖
②設置數據有效性：
首先根據提取出來的不重復值來驗證一下有效性，在G6中點擊數據——有效性——允許下拉填充為序列——引用位置中輸入公式
=OFFSET($T$9,MATCH("*"&$G$6&"*",$T$10:$T$1000,0),,COUNTIF($T$10:$T$1000,"*"&$G$6&"*"),1)，在輸入信息中輸入提示的內容確定即可
當你的商品名稱較多的時候，此時在G6單元格中只要輸入包含某個商品的關鍵字就可以只顯示所有的名字，這樣是不是就方便多了 。刪除多余的輔助列即可 。
Excel小技巧：制作簡易的倉庫管理系統

文章插圖
4、制作出入庫簡易查詢統計
根據商品查詢入庫情況，確定好入庫開始和結束的日期作為查詢的條件，在J6中輸入公式
=IFERROR(SUMPRODUCT((J$10:J$1000)*(($B$10:$B$1000)>=$C$5)*(($B$10:$B$1000)<=$C$6)*(($D$10:$D$1000)="√")*(($F$10:$F$1000)=$G$6)),"-")填充至K6單元格
Excel小技巧：制作簡易的倉庫管理系統

文章插圖
同理出庫的情況只需將D列更改為C列即可，雖然公式很長，但是只要理解了就相當簡單多了!如果你了解到SUMPRODUCT函數的多條件統計求和就很容易理解這個公式的含義了 。有的朋友可能覺得公式太難怎么辦，那么你知道數據透視表也可以制作庫存管理嗎？這樣就可以變很多公式，做起來也比較簡單 。數據透視表的應用：制作簡易的進銷存統計表

推薦閱讀