第4篇物流篇

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

17、 = 存貨數(shù)量*加權(quán)平均采購(gòu)價(jià)格。（1）選中F3單元格。（2）輸入公式“ = D3*E3”。（3）按【Enter】鍵確認(rèn)，計(jì)算出相應(yīng)的存貨占用資金。（4）選中F3單元格，用鼠標(biāo)拖動(dòng)其填充句柄至F16單元格，將公式復(fù)制到F4:F16單元格區(qū)域中，可得到所有產(chǎn)品的存貨占用資金。解決方案解決方案步驟步驟7 7 計(jì)算計(jì)算“銷售成本銷售成本”這里，銷售成本 = 銷售數(shù)量*加權(quán)平均采購(gòu)價(jià)格。（1）選中G3單元格。（2）輸入公式“ = 進(jìn)銷存匯總表!I3*E3”。（3）按【Enter】鍵確認(rèn)，計(jì)算出相應(yīng)的銷售成本。（4）選中G3單元格，用鼠標(biāo)拖動(dòng)其填充句柄至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單元格，用鼠標(biāo)拖動(dòng)其填充句柄至H16單元格，將公式復(fù)制到H4:H16單元格區(qū)域中，可得到所有產(chǎn)品的銷售收入。步驟步驟9 9 計(jì)算計(jì)算“銷售毛利銷售毛利”這里，銷售毛利 = 銷售收入-銷售成本。（1）選中I3單元格。（2）輸入公式“ = H3-G3”。（3）按【Enter】鍵確認(rèn)，計(jì)算出相應(yīng)的銷售毛利。解決方案解決方案（4）選中

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

20、體、20磅、加粗，行高35。（2）將表格標(biāo)題字段的字設(shè)置為加粗、居中，添加“深藍(lán)”底紋，將字體顏色設(shè)置為“白色”，并設(shè)置行高為20。（3）為表格的A2:J16數(shù)據(jù)區(qū)域添加內(nèi)細(xì)外粗的邊框。（4）將“加權(quán)平均采購(gòu)價(jià)格”、“存貨占用資金”、“銷售成本”、“銷售收入”和“銷售毛利”的數(shù)據(jù)設(shè)置“會(huì)計(jì)專用”格式，且無(wú)“貨幣符號(hào)”和“小數(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 通過(guò)分類匯總分析各類產(chǎn)品的銷售與成本情況通過(guò)分類匯總分析各類產(chǎn)品的銷售與成本情況（1）選中“銷售毛利分