《物流數(shù)據(jù)分析》課件-任務5.5 收發(fā)存報表編制

任務5.5收發(fā)存報表編制【任務目的】1．能根據(jù)倉儲信息處理思路設計Excel工作表內(nèi)容；2．熟練使用Excel公式及函數(shù)進行相關(guān)計算或數(shù)據(jù)處理；3．學會條件格式的多種使用方法；4．熟悉保護工作表操作?！局R鏈接】1．收發(fā)存簡介收、發(fā)、存是所有倉庫的三個基本業(yè)務環(huán)節(jié)。收是指從上游供應商訂購貨品到貨后，經(jīng)過接運、卸貨、驗收、搬運、入庫、上架的業(yè)務活動；發(fā)是指收到下游客戶訂貨后，按照客戶需求的貨品種類和數(shù)量經(jīng)揀貨、打包、裝車、配送等業(yè)務活動；存是指貨品在倉庫中相對靜止的狀態(tài)，倉庫需要做好盤點和養(yǎng)護工作。收發(fā)存數(shù)據(jù)的準確性在很大程度上影響倉庫運營的效率。2．VLOOKUP函數(shù)【格式】=VLOOKUP（查找值，查找區(qū)域，返回值的列序號，查找類型）【作用】在指定區(qū)域中查找給定值，最終返回找到值所在的行號和指定列號交叉位置的值?！菊f明】查找值必須屬于查找區(qū)域的第一列的值；查找類型有True和False兩種，當取True時表示近似查找，如果找不到精確匹配值，則匹配小于查找值的最大數(shù)值，要求查找區(qū)域的值必須是經(jīng)過排序的。當取值為False時，表示進行精確查找，大多數(shù)情況下均用此類型。3．SUMIF函數(shù)【格式】=SUMIF（條件判斷區(qū)域，過濾條件，求和區(qū)域）【作用】對滿足過濾條件的行進行求和。【工作任務】某汽車配件經(jīng)銷商經(jīng)營如表所示的汽車零配件，現(xiàn)需要準確記錄零部件的入庫及出庫信息，并實時自動計算出零部件的庫存量。因經(jīng)營規(guī)模不大，暫不需要購置專門的管理軟件。由于Excel具有簡單易學，成本低廉的特點，所以Excel是制作收發(fā)存報表的首選工具。該收發(fā)存報表的功能包括：能夠記錄入庫流水賬信息；能夠記錄出庫流水賬信息；能夠自動匯總計算商品庫存量?！緦嵤┎襟E】1．創(chuàng)建“物料清單”表2．創(chuàng)建“物料入庫”表3．創(chuàng)建“物料出庫”表4．設計“庫存匯總”表5．數(shù)據(jù)測試1．創(chuàng)建“物料清單”表物料清單表主要存放商品的編碼、名稱、規(guī)格型號、單價、存放位置等信息，如圖所示，其他所有表中的有關(guān)商品的信息均引用該表。2．創(chuàng)建“物料入庫”表物料入庫表用于存放入庫記錄的流水賬，如圖所示，應包括以下列：（1）入庫單號：要求根據(jù)入庫日期自動編號，格式例如“RK20170526001”，其中，RK表示入庫，20170526表示入庫日期（8位數(shù)字表示），001表示該天入庫的序號，每天之內(nèi)連續(xù)編號；（2）入庫日期：入庫時通過“ctrl+分號”快捷鍵手工輸入；（3）物料編碼：利用數(shù)據(jù)有效性引用物料清單表中的編碼；（4）物料名稱：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的名稱；（5）規(guī)格型號：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的規(guī)格型號；（6）單位：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的單位；（7）入庫數(shù)量：該列根據(jù)實際入庫數(shù)量手工輸入；（8）單價：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的單價；（9）金額：數(shù)量乘以單價；（10）所在庫區(qū)：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的所在庫區(qū)。3．創(chuàng)建“物料出庫”表物料入庫表用于存放入庫記錄的流水賬，如圖所示，應包括以下列：（1）出庫單號：參照入庫單號的方法自動編號，格式例如“CK20170526001”；（2）出庫日期：手動輸入；（3）物料編碼：利用數(shù)據(jù)有效性引用物料清單表中的編碼；（4）物料名稱：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的名稱；（5）規(guī)格型號：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的規(guī)格型號；（6）單位：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的單位；（7）出庫數(shù)量：該列根據(jù)實際出庫數(shù)量手工輸入；（8）單價：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的單價；（9）金額：數(shù)量乘以單價；（10）所在庫區(qū)：利用VLOOKUP函數(shù)通過物料編碼到物料清單表中查詢對應的所在庫區(qū)。思考與點撥：物料入庫表和出庫表設計的關(guān)鍵點是什么？物料入庫表和出庫表中的數(shù)據(jù)大多通過VLOOKUP函數(shù)引用物料清單，使用時只需要通過錄入物料編碼、日期和數(shù)量即可完成一條記錄的錄入，特別需要注意的是為防止錄入錯誤的物料編碼，入庫表和出庫表的物料編碼列使用了數(shù)據(jù)有效性來確保用戶輸入的物料編碼的正確性。4．設計“庫存匯總”表庫存匯總表用于對入庫和出庫數(shù)據(jù)進行計算，實時獲得各種商品的在庫數(shù)量，如圖5-45所示，應包括以下列：（1）物料編號；（2）物料名稱；（3）規(guī)格型號；（4）單位；（5）單價。以上五列數(shù)據(jù)引用自物料清單表。（1）期初數(shù)量：該列根據(jù)盤點情況手工輸入；（2）本期購進：利用SUMIF函數(shù)對物料入庫表的數(shù)據(jù)進行匯總；（3）本期領(lǐng)用：利用SUMIF函數(shù)對物料出庫表的數(shù)據(jù)進行匯總；（4）本期結(jié)存：按照期初數(shù)量+本期購進-本期領(lǐng)用進行計算；（5）金額：本期結(jié)存乘以單價。5．數(shù)據(jù)測試請在入庫表和出庫表中分別錄入幾條記錄，查看庫存表的統(tǒng)計數(shù)據(jù)是否正確?！救蝿招〗Y(jié)】本任務主要針對數(shù)據(jù)量不太大的場合，記錄商品進銷存數(shù)據(jù)的系統(tǒng)設計。首先需要設計一個表用于存儲基礎數(shù)據(jù)，在此基礎上設計入庫表和出庫表，入庫表和出庫表的結(jié)構(gòu)和內(nèi)容基本一致，都用來記錄出入庫的流水賬，庫存匯總表用于對期初庫存、入庫數(shù)量、出庫數(shù)量進行匯總后得出本期結(jié)余庫存。任務中大量使用了VLOOKUP函數(shù)和SUMIF函數(shù)，分別用于商品匹配和條件求和，最后通過手動編輯公式進行了庫存結(jié)余計算?！緦嵺`訓練】公司現(xiàn)已制作出能夠記錄入庫和出庫的收發(fā)月報表，現(xiàn)需對該月報表按照以下要求進行完善：1．打開素材文件，制作出計算庫存結(jié)余的“收發(fā)存總表”的框架結(jié)構(gòu)。2．保護“入庫表”、“出庫表”及“收發(fā)存總表”中的物品信息單元格區(qū)域。3．利用公式及函數(shù)計算每種物品當前的庫存量，并對存貨不足的物品進行預警提示，要求：