銷售表操作指導

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