出入庫表應用十分廣泛,是每個公司都用到的表格,下面我們來看看怎麼從一張空白表一步一步實現《出入庫表》的制作,目的是做到隻需要記錄出庫入庫流水,自動對庫存及累計出入庫數量進行計算、實時統計。
出入庫表構成做一個出入庫表,我們一般希望報表能夠:根據我們記錄的出庫數量、入庫數量,自動統計出每種物品當前的實時數量,所以一份完整的出入庫表,基本具備以下内容:
1、每種物品的自身屬性信息包括 名稱、型号或規格、單位等;
2、物品出庫流水記錄、入庫流水記錄;
3、物品當前庫存量;
有時候為了統計庫存資金及監控庫存數,還會需要下列信息:
4、物品出庫入庫總金額,當前庫存餘額;
5、物品庫存量不足其安全數量時自動告警。
接下來,就手把手教你如何制作一份自動統計貨品出入庫表。
/ 01.物品信息建立 /首先,要對物品進行信息化整理。為了規範管理,公司一般都會按一定可識别含義的方式對物品進行統一編碼,比如某物品為“經過電鍍工藝的U形03号材質的鋼材料”,可以編碼為:GUDD003。
▲物品信息見上表,包含了物品的基礎屬性信息
/ 02.出入庫記錄表 /接下來,就需要制作貨品出入庫的記錄表。出庫和入庫流水可以分開在兩張表裡來記,也可以合在一張表,看實際使用的方便程度。這裡以後者來示例:
▲表格包含:物品信息,及每次出入庫的日期、數量。
第一步,創建查找函數。産品屬性信息在「物品信息表」中都是登記過的,這裡我們希望記錄時通過選擇編碼後,自動生成名稱、型号、單位。隻要在後面對應屬性單元格分别使用VLOOKUP查找函數就可以實現,見以下動圖教程:
▲利用VLOOKUP函數,自動得到了與前面編碼對應的信息。
函數公式:
=VLOOKUP($C3,物品信息表!$B:$E,2,0)
函數解答:
第一個參數$C3表示想要查找的内容;
第二個參數物品信息表!$B:$E表示要查找的區域(物品信息區);
第三個參數2表示返回的内容為查找區域的第幾列,最後一個參數0表示精确查找。
公式中($)符号代表該公式所引用(指向)的單元格在拖拽填充時不會發生行或列的移動。
第三個參數是指定返回内容,那麼在“型号/規格”、“單位”對應單元格中将上述VLOOKUP函數的2分别改為3、4就可以實現型号和單位的查找了:
可以看到第一條記錄在編碼确定之後,通過在“物品名稱”的D3單元格中使用VLOOKUP函數就自動得到了與前面編碼對應的信息。
第二步,優化函數公式,避免錯誤值。如果物品信息為空,那麼出入庫表後面對應的VLOOKUP函數返回了錯誤值#N/A,這時候我們用IF函數進行優化。
▲優化公式,避免表格出現錯誤值#N/A
函數公式:
=IF($C3=””,””,VLOOKUP($C3,物品信息表!$B:$E,2,0))
函數解答:
若查找單元格為空時返回空,為物品編碼時返回該編碼對應名稱、型号、單位。
第三步,将編碼做成下拉列表選擇。将物品信息編碼制作成下拉列表,以來可以免去多餘的手動輸入,及手動輸入可能帶來的填寫錯誤,二來既省力又規範,見下圖操作:
▲下拉列表選擇,不僅避免了錯誤而且非常高效
簡單三步後,一份完整的物品出入庫記錄表就順利制作完成了。實際應用的過程中,選擇物品編碼自動顯示物品信息,非常方便。如下圖操作:
接着,我們繼續對表格進行升級!每個登記在冊的物品信息後面,增加出庫數、入庫數、當前庫存,均實時顯示!
在「物品信息表」後部再增加以下幾個内容:
1、“前期結轉”,表格在新啟用時可以登記倉庫物品原有庫存;
2、累計出庫、入庫數量
3、當前倉庫庫存量
▲增加的内容,利用函數可以自動化生成
雖然新增了統計項目,但累計出庫、累計入庫可利用SUMIF函數從「出入庫記錄表」中獲取,并沒有增加工作量,見以下教程:
函數公式:
=SUMIF(出入庫流水!$C:$C,$B3,出入庫流水!$G:$G)
函數解析:
第一個參數出入庫流水!$C:$C表示條件列;
第二個參數$B3表示前面條件列應該滿足的條件(對應該行物品編碼);
第三個參數出入庫流水!$G:$G表示對滿足條件的在此列求和。
同樣的方法将第三個參數出入庫流水!$G:$G換成出入庫流水!$H:$H得到累計入庫數量:
接下來,我們就可以利用簡單的求和公式,實現當前庫存自動填入:當前庫存=前期結轉 累計入庫-累計出庫,見下圖教程:
實際工作當中,我們常常需要對物品的庫存進行監控,假如A物品需要保有的安全數量為500,低于500有影響生産的風險,低于500時醒目顔色提示存量告警,并顯示當前欠數,以便及時發現提前做采購計劃。
因此,繼續對表格進行升級!在「物品信息表」後面繼續增加“安全庫存”、“是否緊缺”和“欠數”,如下圖:
▲新增安全庫存、是否緊缺、欠數信息。
庫存告警要好用,表格需要做到以下兩點:
1、庫存足夠時顯示不緊缺;
2、庫存小于“安全庫存”時顯示緊缺,并标出欠數,緊缺的用黃顔色提示:
是否緊缺函數公式:
=IF(J3="","",IF(J3>I3,"是","否"))
函數解析:
表示“安全庫存”中不設置,則不做後面的提示;“安全庫存”中設置了數量,則緊缺時顯示“是”,不緊缺時顯示“否”。
欠數函數公式:
=IF(K3="是",J3-I3,"")
函數解析:
表示如果緊缺顯示欠數,不緊缺(或不需提示)時顯示為空。
通過調整後,隻要設置了物品的安全庫存,就可以自動進行提醒及限時欠數,能夠提前對物品的補貨及采購進行計劃,非常直觀。效果如下圖:
到這裡,一個自動統計的出入庫表就能夠輕松實現了!有了這個工具再也不用擔心上千個物品的倉庫庫存算錯了,庫存一緊張就告訴采購去買,效率也提高了!另外,還有4個升級優化的小tips,可根據自己的實際情況進行調整:
1、對于空行函數返回錯誤值或0值的,可用上面所講到的IF(A=””,””,B)來優化;
2、需要計算“金額”,則每個數量後增加“單價”和“金額”,金額裡公式=數量*單價,即可;
3、物品編碼具有唯一性,在錄入時應防止重複,可以選中編碼所在列(B列),點擊“數據”--“拒絕錄入重複項”,來規範錄入,輸入重複編碼時表格将阻止錄入;
4、公式保護:選中含有公式的單元格,點擊“審閱”保持“鎖定單元格”處于激活狀态,而其他需要用來填寫的單元格保持非激活狀态。 然後點擊“保護工作表”,在彈出的對話框中取消第一個“選定鎖定單元格”前面的勾,确定即可。
/ 06.出入庫模闆下載 /
通過教程,大家可以制作适合自己的出入庫表格,小編也整理了3份超實用的出入庫存表,提供給大家使用。
▲通用月出入庫表(按月記錄、自動統計)
▲全自動出入庫表(自動實時庫存、自定義設置)
▲全面的進銷存管理系統(實時統計、靈活設置)
小編說
Hi,大家好。
還有不到10天,就會迎來2018。
大家想好了元旦去哪嗨了嗎?
上周收到很多粉絲後台留言,要求出函數教程,
馬上聯系了設計師整理出了這篇教程以及模闆,希望能夠幫助大家解決工作上的難題。
留言點贊前10名,小編送出這3套進銷存模闆!(截止至12.22日中午12點公布)
2018,大家有什麼想要求小編整理的教程,模闆,資源,可以點擊“寫留言”,我會盡力幫大家,希望能夠人人都領到福利。
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!