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