銷(xiāo)售表操作指導(dǎo)

PAGE2PAGE5Excel綜合應(yīng)用1——產(chǎn)品銷(xiāo)售數(shù)據(jù)分析任務(wù)提出小王在湘潭市開(kāi)了若干家家電產(chǎn)品銷(xiāo)售連鎖店，為了提高管理水平，他打算用Excel工作表來(lái)管理銷(xiāo)售數(shù)據(jù)。其中“商品信息”工作表給出了每種家電產(chǎn)品的“單位”、“進(jìn)價(jià)”和“售價(jià)”，如圖所示。“銷(xiāo)售記錄”工作表記錄了2010圖1“商品信息”工作表圖2“銷(xiāo)售記錄”工作表目的:統(tǒng)計(jì)各連鎖店的銷(xiāo)售情況，計(jì)算2010困難:“銷(xiāo)售記錄”工作表中只記載了家電名稱(chēng)和銷(xiāo)售數(shù)量。每種家電的“進(jìn)價(jià)”和“售價(jià)”必須去“商品信息”工作表中一個(gè)個(gè)查找。這個(gè)工作量實(shí)在太大，而且還容易出錯(cuò)。要求：輸入家電名稱(chēng)后，讓Excel根據(jù)家電名稱(chēng)自動(dòng)查找該家電的“單位”、“進(jìn)價(jià)”和“售價(jià)”等信息。其它要求：為了提高效率、減少失誤，前面的“銷(xiāo)售記錄”工作表改進(jìn)，可以直接從列表中選擇家電名稱(chēng)，并可以自動(dòng)計(jì)算出顧客應(yīng)交款額和返還款。實(shí)現(xiàn)方法一、“商品信息表”建立操作關(guān)鍵：新建EXCEL工作薄“銷(xiāo)售表.xls”,在sheet2建立“商品信息表”將工作表名“sheet2”重命名為“商品信息表”標(biāo)題要求“合并及居中”、“序號(hào)”采用自動(dòng)序列填充表格加邊框；文字內(nèi)容上下、左右居中；行高調(diào)整合適高度二、“銷(xiāo)售記錄表”的建立（在sheet1中操作，并將“sheet1”重命名為“銷(xiāo)售記錄表”1、建立表格“標(biāo)題”和“表頭”，格式要求同上2、“日期”列可以用快捷鍵“Ctrl+；”輸入當(dāng)前日期。3、對(duì)“所在區(qū)”利用“數(shù)據(jù)有效性”設(shè)置序列自動(dòng)點(diǎn)擊填充。選中“銷(xiāo)售記錄表”的“所在區(qū)”的B3單元格或B列，然后在菜單欄中選擇“數(shù)據(jù)”→“有效性”命令，打開(kāi)“數(shù)據(jù)有效性”對(duì)話(huà)框。選擇“設(shè)置”選項(xiàng)卡，在“允許”下拉列表中選擇“序列”，在“來(lái)源”中填寫(xiě)“雨湖區(qū)，岳塘區(qū)”(注意：中間用英文半角逗號(hào))，單擊“確定”按鈕，如圖3所示。圖3設(shè)置“所在區(qū)”列的數(shù)據(jù)有效性、4、用同樣的方法，可以對(duì)“店名”列進(jìn)行數(shù)據(jù)有效性設(shè)置。（板塘店,基建營(yíng)店,建設(shè)路店,金橋店,解放路店,金都店,金湘潭店）5、“家電名稱(chēng)”列可參照上面方法設(shè)置。也可以先選擇要定義的區(qū)域，然后在名稱(chēng)框中直接輸入定義的名稱(chēng)。(1)選擇(2)點(diǎn)擊“插入”→“名稱(chēng)”→“定義”命令，打開(kāi)“定義名稱(chēng)”對(duì)話(huà)框，在名稱(chēng)框中輸入“家電名稱(chēng)”后，單擊“添加”“確定”按鈕，圖4所示圖4（3）選擇“數(shù)據(jù)”→“有效性”命令，打開(kāi)“數(shù)據(jù)有效性”對(duì)話(huà)框。選擇“設(shè)置”選銷(xiāo)售額=售價(jià)×數(shù)量毛利潤(rùn)=(售價(jià)-進(jìn)價(jià))×數(shù)量應(yīng)收=銷(xiāo)售額找回=實(shí)收-應(yīng)收9、選擇“銷(xiāo)售記錄”工作表中的“進(jìn)價(jià)”、“售價(jià)”、“銷(xiāo)售額”和“毛利潤(rùn)”等列，將其單元格數(shù)字格式設(shè)置為“貨幣”，小數(shù)位數(shù)設(shè)置為“1”10、使“銷(xiāo)售記錄”工作表更完美上面制作的“銷(xiāo)售記錄”工作表使用起來(lái)雖然方便了很多，但還不是很完美。因?yàn)橛杏涗浀男欣锩嬗泻芏噱e(cuò)誤值“#N／A”，讓人看起來(lái)很不舒服。利用IF函數(shù)和ISERROR函數(shù)的嵌套可以很容易地解決這個(gè)問(wèn)題。ISERROR(value)函數(shù)函數(shù)的功能：檢測(cè)一個(gè)值是否為錯(cuò)誤值。函數(shù)的用法：當(dāng)變量value是錯(cuò)誤值，如“#N／A”時(shí)，返回邏輯真(TRUE)，否則返回邏輯假(FALSE)。當(dāng)與IF函數(shù)結(jié)合在一起使用時(shí)，可以檢查公式是否為錯(cuò)誤值。如上圖中，從第4行開(kāi)始，“家電名稱(chēng)”和“數(shù)量”為空時(shí)，“進(jìn)價(jià)（F4）”“售價(jià)(G4)”等是錯(cuò)誤值“#N／A”，即ISERROR(F4)=TRUE。要讓F4是錯(cuò)誤值“#N／A”時(shí)不顯示錯(cuò)誤值，而F4不是錯(cuò)誤值時(shí)顯示F4本身，可用IF函數(shù)與ISERROR函數(shù)的嵌套來(lái)實(shí)現(xiàn)，即：=IF(ISERROR(F4),"",F4)具體操作：（1）因?yàn)镕3=VLOOKUP(D3,價(jià)格,3,FALSE)，所以使用IF函數(shù)與IsERROR函數(shù)的嵌套后，F(xiàn)3中的公式應(yīng)寫(xiě)成：=IF(ISERROR(VLOOKUP(D3,價(jià)格,3,FALSE)),"",VLOOKUP(D3,價(jià)格,3,FALSE))（2）在“進(jìn)價(jià)”列雙擊填充柄復(fù)制公式。(3)按照同樣的方法，用IF函數(shù)與ISERROR函數(shù)的嵌套，其它列的錯(cuò)誤值(#Value)不顯示?！笆蹆r(jià)”列的公式為=IF(ISERROR(VLOOKUP(D3,價(jià)格,4,FALSE)),"",VLOOKUP(D3,價(jià)格,4,FALSE))“銷(xiāo)售額”列的公式為：=IF(ISERROR(G3*E3),"",G3*E3)“毛利潤(rùn)”列的公式為：=IF(ISERROR((G3-F3)*E3),"",(G3-F3)*E3)“應(yīng)收”列(即J3)中的公式為：=IF(ISERROR(H3),"",H3)“找回”列(即K3)中的公式為：=IF(ISERROR(J3-K3),"",J3-K3)三、用“分類(lèi)匯總”統(tǒng)計(jì)“銷(xiāo)售額”和“毛利潤(rùn)”圖9建立工作表副本在對(duì)“銷(xiāo)售額”和“毛利潤(rùn)”進(jìn)行統(tǒng)計(jì)之前，先創(chuàng)建3個(gè)“銷(xiāo)售記錄圖9建立工作表副本操作步驟如下：右鍵單擊“銷(xiāo)售記錄”工作表標(biāo)簽，在彈出的快捷菜單中選擇“移動(dòng)或復(fù)制工作表”，并選中“建立副本”復(fù)選框，建立“銷(xiāo)售記錄”工作表的3個(gè)副本“銷(xiāo)售記錄(2)”、“銷(xiāo)售記錄(3)”和“銷(xiāo)售記錄(4)”，如圖9所示。1.利用“分類(lèi)匯總，統(tǒng)計(jì)各店的家電“銷(xiāo)售額”和“毛利潤(rùn)”“分類(lèi)匯總”含有兩層意思：按什么分類(lèi)——“所在區(qū)”和對(duì)什么匯總——“銷(xiāo)售額”和“毛利潤(rùn)”。在進(jìn)行“分類(lèi)匯總”之前，應(yīng)先對(duì)要分類(lèi)的“所在區(qū)”列進(jìn)行排序，目的是為了把“所在區(qū)”相同的記錄放到一起，然后再對(duì)要匯總列的“銷(xiāo)售額”和“毛利潤(rùn)”進(jìn)行求和。（1）在“銷(xiāo)售記錄(2)”工作表中，對(duì)“所在區(qū)”字段進(jìn)行排序。（2）用“分類(lèi)匯總”統(tǒng)計(jì)各區(qū)的“銷(xiāo)售額”和“毛利潤(rùn)”?！皵?shù)據(jù)”→“分類(lèi)匯總”命令，打開(kāi)“分類(lèi)匯總”對(duì)話(huà)框?！胺诸?lèi)匯總”對(duì)話(huà)框四、用“數(shù)據(jù)透視表”分析各區(qū)每種家電銷(xiāo)售情況“分類(lèi)匯總”的結(jié)果雖然給出了各個(gè)區(qū)每個(gè)家電店的家電“銷(xiāo)售額”和“毛利潤(rùn)”，但還不能很直觀地反映出各個(gè)區(qū)的家電銷(xiāo)售情況，如果用Excel中的“數(shù)據(jù)透視表”進(jìn)行數(shù)據(jù)分析，就可以非常方便地解決這個(gè)問(wèn)題。1.統(tǒng)計(jì)各個(gè)區(qū)每種家電的銷(xiāo)售情況操作步驟如下：(1)單擊“銷(xiāo)售記錄”工作表數(shù)據(jù)清單中的任一單元格，在菜單欄中選擇“數(shù)據(jù)”→“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令，打開(kāi)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂?duì)話(huà)框。(2)單擊“下一步”按鈕，打開(kāi)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б?步驟之2”圖7.18“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)е?”(3)單擊“下一步”按鈕，打開(kāi)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?步驟之3”(4)單擊“布局”按鈕，打開(kāi)“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)б徊季帧睂?duì)話(huà)框，將“家電名稱(chēng)”拖動(dòng)到左邊圖形的“行”上，“所在區(qū)”拖動(dòng)到“列”上，“銷(xiāo)售額”拖動(dòng)到“數(shù)據(jù)”區(qū)域中，如圖所示。圖7.19數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)б徊季?5)單擊“確定”按鈕，返回透視表向?qū)?duì)話(huà)框，選擇“數(shù)據(jù)透視表顯示位置”為“新建工作表”。(6)單擊“完成”按鈕，將數(shù)據(jù)透視表重命名為“銷(xiāo)售統(tǒng)計(jì)”。(7)對(duì)“在數(shù)據(jù)透視表中找出“銷(xiāo)售額”最大的家電五、用“兩軸線(xiàn)-柱圖”比較“銷(xiāo)售額”和“毛利潤(rùn)”兩軸線(xiàn)一柱圖是一種組合圖表，組合圖表使用兩種或多種圖表類(lèi)型，以強(qiáng)調(diào)圖表中含有不同類(lèi)型的信息。操作步驟如下：(1)選中“銷(xiāo)售記錄(2)”工作表中的隱藏分類(lèi)匯總明細(xì)數(shù)據(jù)后的數(shù)據(jù)，如圖7.25所示。圖7.25數(shù)據(jù)區(qū)的選擇(2)在“常用”工具欄中單擊“圖表向?qū)А卑粹o，打開(kāi)“圖表向?qū)?4步驟之l-圖表類(lèi)型”對(duì)話(huà)框，選擇“自定義類(lèi)型”選項(xiàng)卡，在“圖表類(lèi)型”列表框中選擇“兩軸線(xiàn)-柱圖”。(3)單擊“下一步”按鈕，打開(kāi)“圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)”對(duì)話(huà)框，在“系列產(chǎn)生在”中選擇“列”。(4)單擊“下一步”按鈕，打開(kāi)“圖表向?qū)?4步驟之3-圖表選項(xiàng)”對(duì)話(huà)框，設(shè)置如下：圖表標(biāo)題為“銷(xiāo)售額與毛利潤(rùn)關(guān)系圖”；分類(lèi)(X)軸為“所在區(qū)”；數(shù)值(