第4篇物流篇

1、第第4 4篇篇 物流物流篇篇 學(xué)習(xí)目標學(xué)習(xí)目標1利用Excel創(chuàng)建公司的庫存統(tǒng)計表，靈活設(shè)置各部分的格式。2自定義數(shù)據(jù)格式。3通過數(shù)據(jù)有效性的設(shè)置來控制錄入符合規(guī)定的數(shù)據(jù)。4學(xué)會合并多表數(shù)據(jù)，得到匯總結(jié)果。5利用Excel制作公司產(chǎn)品進銷存管理表、對產(chǎn)品銷售和成本進行分析。6在Excel中利用自動篩選和高級篩選實現(xiàn)顯示滿足條件的數(shù)據(jù)行。7利用分類匯總來分類統(tǒng)計某些字段的匯總函數(shù)值。8靈活地構(gòu)造和使用圖表來滿足各種需要的數(shù)據(jù)結(jié)果的顯示要求。9能靈活使用條件格式突出顯示數(shù)據(jù)結(jié)果。主要案例主要案例案例案例15 15 制作產(chǎn)品庫存管理表制作產(chǎn)品庫存管理表案例案例16 16 制作產(chǎn)品進銷制作產(chǎn)品進銷存存

2、匯總匯總表表案例案例17 17 制作產(chǎn)品銷售與成本分析制作產(chǎn)品銷售與成本分析案例案例15 15 制作產(chǎn)品庫存管理表制作產(chǎn)品庫存管理表案例分析案例分析解決方案解決方案拓展案例拓展案例拓展訓(xùn)練拓展訓(xùn)練案例小結(jié)案例小結(jié)案例分析案例分析 本案例通過制作“公司庫存管理表”來介紹Excel軟件在庫存管理方面的應(yīng)用，效果分別如圖4.1、圖4.2、圖4.3、圖4.4、圖4.5和圖4.6所示。解決方案解決方案步驟步驟1 1 創(chuàng)建工作簿，重命名工作表創(chuàng)建工作簿，重命名工作表（1）啟動Excel 2003，新建一個空白工作簿。（2）將創(chuàng)建的工作簿以“公司庫存管理表”為名保存到“D:科源有限公司物流部”文件夾中。（3

3、）將“公司庫存管理表” 工作簿中的Sheet1工作表重命名為“產(chǎn)品明細表”。步驟步驟2 2 創(chuàng)建創(chuàng)建“產(chǎn)品明細表產(chǎn)品明細表”（1）選中“產(chǎn)品明細表”工作表。（2）輸入產(chǎn)品明細數(shù)據(jù)。步驟步驟3 3 創(chuàng)建創(chuàng)建“第一倉庫入庫第一倉庫入庫”表表（1）將Sheet2工作表重命名為“第一倉庫入庫”。解決方案解決方案（2）在“第一倉庫入庫”工作表中創(chuàng)建表格框架。（3）輸入“編號”。（4）參照圖4.12輸入“日期”和“產(chǎn)品編號”數(shù)據(jù)。（5）輸入“產(chǎn)品類別”數(shù)據(jù)。 （6）用同樣的方式，參照圖4.16設(shè)置參數(shù)，輸入“產(chǎn)品型號”數(shù)據(jù)。（7）輸入入庫“數(shù)量”數(shù)據(jù)。 為保證輸入的數(shù)量值均為正整數(shù)、不會出現(xiàn)其他數(shù)據(jù)，我們

4、需要對這列進行數(shù)據(jù)有效性設(shè)置。（8）設(shè)置完成后，參照圖4.1所示，在工作表中進行數(shù)量數(shù)據(jù)的輸入，完成“第一倉庫入庫”表的創(chuàng)建。 解決方案解決方案步驟步驟4 4 創(chuàng)建創(chuàng)建“第二倉庫入庫第二倉庫入庫”表表（1）將Sheet3工作表重命名為“第二倉庫入庫”。（2）參照創(chuàng)建“第一倉庫入庫”表的方法創(chuàng)建如圖4.2所示的“第二倉庫入庫”表。步驟步驟5 5 創(chuàng)建創(chuàng)建“第一倉庫出庫第一倉庫出庫”表表（1）在“第二倉庫入庫”表之后插入一張新的工作表，并將新工作表重命名為“第一倉庫出庫”。（2）參照創(chuàng)建“第一倉庫入庫”表的方法創(chuàng)建如圖4.3所示的“第一倉庫出庫”表。步驟步驟6 6 創(chuàng)建創(chuàng)建“第二倉庫出庫第二倉庫出

5、庫”表表（1）在“第一倉庫出庫”表之后插入一張新的工作表，并將新工作表重命名為“第二倉庫出庫”。（2）參照創(chuàng)建“第一倉庫入庫”表的方法創(chuàng)建如圖4.4所示的“第二倉庫出庫”表。解決方案解決方案步驟步驟7 7 創(chuàng)建創(chuàng)建“入庫匯總表入庫匯總表” 這里，我們將采用“合并計算”來完成匯總出所有倉庫中各種產(chǎn)品的入庫數(shù)據(jù)。 （1）在“第二倉庫入庫”表之后插入一張新的工作表，并將新工作表重命名為“入庫匯總表”。（2）選中A1單元格，將合并計算的結(jié)果從這個單元格開始填列。（3）選擇【數(shù)據(jù)】【合并計算】命令，打開“合并計算”對話框。（4）在“函數(shù)”下拉列表框中選擇合并的方式“求和”。（5）添加第一個引用位置區(qū)域為

6、“第一倉庫入庫”工作表中的C3:F17。（6）添加第二個引用位置區(qū)域為“第二倉庫入庫”工作表中C3:F19。解決方案解決方案（7）選中“標簽位置”中的【首行】和【最左列】選項，單擊【確定】按鈕，完成合并計算。（8）調(diào)整表格。將合并后不需要的“產(chǎn)品類別”和“產(chǎn)品型號”列刪除，將“產(chǎn)品編號”標題添上，在適當調(diào)整列寬后得到最終效果，如圖4.5所示。步驟步驟8 8 創(chuàng)建創(chuàng)建“出庫匯總表出庫匯總表”（1）采用創(chuàng)建“入庫匯總表”的方法，在“第二倉庫出庫”表之后插入一張新的工作表。（2）將新工作表重命名為“出庫匯總表”，匯總出所有倉庫中各種產(chǎn)品的出庫數(shù)據(jù)。拓展案例拓展案例材料采購明細表，如圖4.27所示。拓

7、展訓(xùn)練拓展訓(xùn)練制作一份公司出貨明細單，效果圖如圖4.28所示。在這個明細表中，會涉及數(shù)據(jù)有效性設(shè)置、自定義序列來構(gòu)造下拉列表進行數(shù)據(jù)輸入、自動篩選數(shù)據(jù)、凍結(jié)網(wǎng)格線等操作。案例小結(jié)案例小結(jié)本案例通過制作“公司庫存管理表”，主要介紹了工作簿的創(chuàng)建、工作表重命名，自動填充、有效性設(shè)置、使用VLOOKUP函數(shù)導(dǎo)入數(shù)據(jù)。在此基礎(chǔ)上，使用“合并計算”對多個倉庫的出、入庫數(shù)據(jù)進行匯總統(tǒng)計。案例案例16 16 制作產(chǎn)品進銷存制作產(chǎn)品進銷存匯總匯總表表案例分析案例分析解決方案解決方案拓展案例拓展案例拓展訓(xùn)練拓展訓(xùn)練案例小結(jié)案例小結(jié)案例分析案例分析 在一個經(jīng)營性企業(yè)中，物流部門的基本業(yè)務(wù)流程就是產(chǎn)品的進銷存管理過

8、程，產(chǎn)品的進貨、銷售和庫存的各個環(huán)節(jié)直接影響到企業(yè)的發(fā)展。 對企業(yè)的進銷存實行信息化管理，不僅可以實現(xiàn)數(shù)據(jù)之間的共享，保證數(shù)據(jù)的正確性，還可以實現(xiàn)對數(shù)據(jù)的全面匯總和分析，促進企業(yè)的快速發(fā)展。本案例通過制作“產(chǎn)品進銷存管理表”來介紹Excel軟件在進銷存管理方面的應(yīng)用。產(chǎn)品進銷存管理表的設(shè)計效果圖如圖4.38。解決方案解決方案步驟步驟1 1 創(chuàng)建工作簿創(chuàng)建工作簿（1）啟動Excel 2003，新建一個空白工作簿。（2）將創(chuàng)建的工作簿以“產(chǎn)品進銷存匯總表”為名保存在“D:科源有限公司物流部”中。步驟步驟2 2 復(fù)制工作表復(fù)制工作表（1）打開“公司庫存管理表”工作簿。（2）選中“產(chǎn)品明細表”、“入庫

9、匯總表”和“出庫匯總表”工作表。（3）選擇【編輯】【移動或復(fù)制工作表】命令，打開如圖4.39所示的“移動或復(fù)制工作表”對話框。（4）從“工作簿”的下拉列表中選擇“產(chǎn)品進銷存匯總表”工作簿，在”下列選定工作表之前”中選擇“Sheet1”工作表，再選中【建立副本】選項。解決方案解決方案（5）單擊【確定】按鈕，將選定的工作表“產(chǎn)品明細表”、“入庫匯總表”和“出庫匯總表”復(fù)制到“產(chǎn)品進銷存匯總表”工作簿中。步驟步驟3 3 創(chuàng)建創(chuàng)建“進銷存匯總表進銷存匯總表”工作表框架工作表框架（1）將“Sheet1”工作表重命名為“進銷存匯總表”。（2）建立如圖4.40所示的“進銷存匯總表”框架。步驟步驟4 4 輸入

10、輸入 “進銷存匯總表進銷存匯總表”中的數(shù)據(jù)中的數(shù)據(jù)（1）計算“期初庫存額”。這里，期初庫存額 =期初庫存量 進價。（2）導(dǎo)入“本月入庫量”。這里，本月入庫量為本月入庫匯總表中的數(shù)量。解決方案解決方案（3）計算“本月入庫額”。這里，本月入庫額 = 本月入庫量進價。（4）導(dǎo)入“本月銷售量”。這里，本月銷售量為本月出庫匯總表中的數(shù)量。（5）計算“本月銷售額”。這里，本月銷售額 = 本月銷售量售價。（6）計算“期末庫存量”。這里，期末庫存量 = 期初庫存量+本月入庫量-本月銷售量。（7）計算“期末庫存額”。這里，期末庫存額 = 期末庫存量進價。步驟步驟5 5 對對“進銷存匯總表進銷存匯總表”進行格式設(shè)

11、置進行格式設(shè)置（1）設(shè)置表格標題格式。將表格標題進行合并及居中，字體設(shè)置為“宋體”、“18磅”、“加粗”，設(shè)置行高為30。解決方案解決方案（2）將表格標題字段添加“海綠”色底紋，并設(shè)置字體為白色、加粗、居中對齊。（3）為表格添加內(nèi)細外粗的邊框。（4）將“單位”、“期初庫存量”、“本月入庫量”、“本月銷售量”和“期末庫存量”的數(shù)據(jù)設(shè)置為居中對齊。（5）將“期初庫存額”、“本月入庫額”、“本月銷售額”和“期末庫存額”的數(shù)據(jù)設(shè)置“會計專用”格式，且“無貨幣符號”和“小數(shù)位數(shù)”。步驟步驟6 6 條件格式條件格式“期末庫存量期末庫存量” 為了更方便地了解庫存信息，我們可以為相應(yīng)的期末庫存量設(shè)置條件格式，

12、根據(jù)不同庫存量等級設(shè)置不同的顯示顏色，如庫存量過少以淺橙色顯示，庫存量過多用黃色顯示，正常時顯示為淺綠色。（1）選中K3:K16單元格區(qū)域。解決方案解決方案（2）選擇【格式】【條件格式】命令，打開【條件格式】對話框，按圖4.45所示設(shè)置條件格式。（3）單擊【確定】按鈕，完成條件格式設(shè)置。結(jié)果如圖4.24所示。拓展案例拓展案例公司產(chǎn)品生產(chǎn)成本預(yù)算表設(shè)計，如圖4.46和圖4.47所示。拓展訓(xùn)練拓展訓(xùn)練設(shè)計一份科源有限公司的生產(chǎn)預(yù)算表，如圖4.48所示。案例小結(jié)案例小結(jié) 本案例通過制作“產(chǎn)品進銷存匯總表”，主要介紹了工作簿的創(chuàng)建、工作簿之間工作表的復(fù)制、工作表重命名，使用VLOOKUP函數(shù)導(dǎo)入數(shù)據(jù)，

13、工作表間數(shù)據(jù)的引用以及公式的使用。在此基礎(chǔ)上，利用“條件格式”對表中的數(shù)據(jù)進行突出顯示，以方便合理地入庫管理。案例案例17 17 制作產(chǎn)品銷售與成本分制作產(chǎn)品銷售與成本分析析案例分析案例分析解決方案解決方案拓展案例拓展案例拓展訓(xùn)練拓展訓(xùn)練案例小結(jié)案例小結(jié)案例分析案例分析 在企業(yè)的經(jīng)營管理過程中，成本的管理和控制成為關(guān)注的焦點?？茖W(xué)分析企業(yè)的各項成本構(gòu)成及影響利潤的關(guān)鍵要素，了解公司的成本構(gòu)架和盈利情況，有利于把握正確的決策方向，實現(xiàn)有效的成本控制。 物流管理部門在產(chǎn)品進銷存的管理過程中，通過分析產(chǎn)品的存貨量、平均采購價格以及存貨占用資金，可對產(chǎn)品的銷售和成本進行分析，從而為產(chǎn)品的庫存管理提供決

14、策支持。本案例通過制作“產(chǎn)品銷售與成本分析”來介紹Excel軟件在成本控制方面的應(yīng)用。效果如圖4.65、圖4.66和圖4.67所示。解決方案解決方案步驟步驟1 1 創(chuàng)建工作簿創(chuàng)建工作簿（1）啟動Excel 2003，新建一個空白工作簿。（2）將新建的工作簿以“產(chǎn)品銷售與成本分析”為名保存在“D:科源有限公司物流部”文件夾中。步驟步驟2 2 復(fù)制工作復(fù)制工作表表（1）打開“產(chǎn)品進銷存匯總表”工作簿。（2）選中“進銷存匯總表”工作表。（3）選擇【編輯】【移動或復(fù)制工作表】命令，打開【移動或復(fù)制工作表】對話框。（4）從“工作簿”的下拉列表中選擇“產(chǎn)品銷售與成本分析”工作簿，在“下列選定工作表之前”中

15、選擇“Sheet1”工作表，再選中“建立副本”選項，如圖4.68所示。（5）單擊【確定】按鈕，將選定的工作表“進銷存匯總表”復(fù)制到“產(chǎn)品銷售與成本分析”工作簿中。解決方案解決方案步驟步驟3 3 創(chuàng)建創(chuàng)建“產(chǎn)品銷售與成本分析產(chǎn)品銷售與成本分析”工作表框架工作表框架（1）將“Sheet1”工作表重命名為“銷售與成本分析”。（2）建立 “銷售與成本分析”表框架。步驟步驟4 4 計算計算“存貨數(shù)量存貨數(shù)量”這里，存貨數(shù)量 = 入庫數(shù)量-銷售數(shù)量。（1）選中D3單元格。（2）輸入公式“ = 進銷存匯總表!G3-進銷存匯總表!I3”。（3）按【Enter】鍵確認，計算出相應(yīng)的存貨數(shù)量。（4）選中D3單元格

16、，用鼠標拖動其填充句柄至D16單元格，將公式復(fù)制到D4:D16單元格區(qū)域中，可得到所有產(chǎn)品的存貨數(shù)量。解決方案解決方案步驟步驟5 5 計算計算“加權(quán)平均采購價格加權(quán)平均采購價格”這里，我們設(shè)定“加權(quán)平均采購價格 = 入庫金額/入庫數(shù)量”。（1）選中E3單元格。（2）輸入公式“ = 進銷存匯總表!H3/進銷存匯總表!G3”。（3）按【Enter】鍵確認，計算出相應(yīng)的加權(quán)平均采購價格。（4）選中E3單元格，用鼠標拖動其填充句柄至E16單元格，將公式復(fù)制到E4:E16單元格區(qū)域中，可得到所有產(chǎn)品的加權(quán)平均采購價格。解決方案解決方案步驟步驟6 6 計算計算“存貨占用資金存貨占用資金”這里，存貨占用資金

17、 = 存貨數(shù)量*加權(quán)平均采購價格。（1）選中F3單元格。（2）輸入公式“ = D3*E3”。（3）按【Enter】鍵確認，計算出相應(yīng)的存貨占用資金。（4）選中F3單元格，用鼠標拖動其填充句柄至F16單元格，將公式復(fù)制到F4:F16單元格區(qū)域中，可得到所有產(chǎn)品的存貨占用資金。解決方案解決方案步驟步驟7 7 計算計算“銷售成本銷售成本”這里，銷售成本 = 銷售數(shù)量*加權(quán)平均采購價格。（1）選中G3單元格。（2）輸入公式“ = 進銷存匯總表!I3*E3”。（3）按【Enter】鍵確認，計算出相應(yīng)的銷售成本。（4）選中G3單元格，用鼠標拖動其填充句柄至G16單元格，將公式復(fù)制到G4:G16單元格區(qū)域中

18、，可得到所有產(chǎn)品的銷售成本。步驟步驟8 8 導(dǎo)入導(dǎo)入“銷售收入銷售收入”數(shù)據(jù)數(shù)據(jù)這里，銷售收入 = 銷售金額。（1）選中H3單元格。解決方案解決方案（2）插入“VLOOKUP”函數(shù)，設(shè)置如圖4.70所示的函數(shù)參數(shù)。（3）單擊【確定】按鈕，導(dǎo)入相應(yīng)的銷售收入。（4）選中H3單元格，用鼠標拖動其填充句柄至H16單元格，將公式復(fù)制到H4:H16單元格區(qū)域中，可得到所有產(chǎn)品的銷售收入。步驟步驟9 9 計算計算“銷售毛利銷售毛利”這里，銷售毛利 = 銷售收入-銷售成本。（1）選中I3單元格。（2）輸入公式“ = H3-G3”。（3）按【Enter】鍵確認，計算出相應(yīng)的銷售毛利。解決方案解決方案（4）選中

19、I3單元格，用鼠標拖動其填充句柄至I16單元格，將公式復(fù)制到I4:I16單元格區(qū)域中，可得到所有產(chǎn)品的銷售毛利。步驟步驟10 10 計算計算“銷售成本率銷售成本率”這里，銷售成本率 = 銷售成本/銷售收入。（1）選中J3單元格。（2）輸入公式“ = G3/H3”。（3）按【Enter】鍵確認，計算出相應(yīng)的銷售成本率。（4）選中J3單元格，用鼠標拖動其填充句柄至J16單元格，將公式復(fù)制到J4:J16單元格區(qū)域中，可得到所有產(chǎn)品的銷售成本率。解決方案解決方案步驟步驟11 11 設(shè)置設(shè)置“產(chǎn)品銷售與成本分析產(chǎn)品銷售與成本分析”表格式表格式（1）設(shè)置表格標題格式。將表格標題進行合并及居中，字體設(shè)置為宋

20、體、20磅、加粗，行高35。（2）將表格標題字段的字設(shè)置為加粗、居中，添加“深藍”底紋，將字體顏色設(shè)置為“白色”，并設(shè)置行高為20。（3）為表格的A2:J16數(shù)據(jù)區(qū)域添加內(nèi)細外粗的邊框。（4）將“加權(quán)平均采購價格”、“存貨占用資金”、“銷售成本”、“銷售收入”和“銷售毛利”的數(shù)據(jù)設(shè)置“會計專用”格式，且無“貨幣符號”和“小數(shù)位數(shù)”。（5）將“銷售成本率”數(shù)據(jù)設(shè)置為百分比格式，1位小數(shù)。解決方案解決方案步驟步驟12 12 復(fù)制復(fù)制“產(chǎn)品銷售與成本分析產(chǎn)品銷售與成本分析”工作表工作表（1）選中“產(chǎn)品銷售與成本分析”工作表。（2）將該工作表復(fù)制兩份，并分別重命名為“銷售毛利分析”和“占用資金少或銷售成本低的產(chǎn)品”。步驟步驟13 13 通過分類匯總分析各類產(chǎn)品的銷售與成本情況通過分類匯總分析各類產(chǎn)品的銷售與成本情況（1）選中“銷售毛利分