excel銷售數(shù)據(jù)分析-實訓練習參考模板

1、1 / 37EXCELEXCEL 實訓練習三實訓練習三商場銷售數(shù)據(jù)的分析處理商場銷售數(shù)據(jù)的分析處理1. 問題的提出問題的提出小李在深圳市開了若干家飲料連鎖店，為了提高管理水平，他打算用 Excel 工作表來管理銷售數(shù)據(jù)。下圖是他制作的各飲料店的銷售記錄流水帳表。為了統(tǒng)計“毛利潤” ，他必須去 “飲料基本信息”表中查找每種飲料的“進價”和“售價” 。這個工作量實在太大，而且還容易出錯?，F(xiàn)在希望：能否輸入飲料名稱后，讓 Excel 根據(jù)這個名稱自動去查找該飲料的“單位” 、 “進價” 、“售價”等信息，并存放到表“銷售記錄”的相應列中。2解決方案解決方案通常情況下，如果不借助其它方法的幫助，要想在

2、 Excel 中解決這個問題，只能到“飲料基本信息”表中一條一條地查找各種飲料的“進價”和“售價” 。如果不想這么做，你有什么更好的辦法嗎？這個實際需求，開發(fā) Excel 的工程師，已經(jīng)為我們想到了。在 Excel 中有一個函數(shù)，就是專門為解決這類問題設計的，這個函數(shù)就是 VLOOKUP。小李這個問題，可利用 Excel 中的查找函數(shù) VLOOKUP 來解決。它的功能是，在數(shù)據(jù)區(qū)域的第一列中查找指定的數(shù)值，并返回數(shù)據(jù)區(qū)域當前行中指定列處的數(shù)值。下面來看看應用 VLOOKUP 函數(shù)是如何解決上述問題的。3.實現(xiàn)方法實現(xiàn)方法本案例要解決如下幾個問題：1在“銷售記錄”工作表中用 VLOOKUP 函數(shù)

3、計算飲料的“單位”、“進價”和“售價”等信息，并計算出工作表中的“銷售額”和“毛利潤”等信息。2用“分類匯總”統(tǒng)計出各連鎖店和各個區(qū)中各種飲料的“銷售額”、“毛利潤”。3用“數(shù)據(jù)透視表”分析各個區(qū)中每種飲料的銷售情況和各個區(qū)中銷售情況最好的飲料。4另外，為了提高效率、避免出錯，小李還想制作一張可以從下拉列表中選擇飲料名稱，并能自動計算出顧客應交款及應找回款的“新銷售記錄”工作表。4制作過程制作過程STEP 1VLOOKUP函數(shù)的使用函數(shù)的使用設計目標設計目標參照下圖，根據(jù)“銷售記錄”表中的“飲料名稱”列，利用 VLOOKUP 函數(shù)在“飲料基本信息”表中查找其他列（單位、進價和售價）的值。根據(jù)以

4、上兩個表根據(jù)以上兩個表中的數(shù)據(jù)得到下中的數(shù)據(jù)得到下圖中的結果圖中的結果最后計算銷售額最后計算銷售額、毛利潤、毛利率毛利潤、毛利率（1）VLOOKUP 函數(shù)函數(shù)是干什么用的是干什么用的VLOOKUPVLOOKUP 函數(shù)函數(shù)的的功能：功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回數(shù)據(jù)區(qū)域當前行中指定列處的查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回數(shù)據(jù)區(qū)域當前行中指定列處的值。值。VLOOKUPVLOOKUP 的語法：的語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)注意：注意：要查找的對象（參數(shù)）一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù)

5、）的第一列。下面來看看VLOOKUP 函數(shù)是怎么用的。查找什么？到哪個區(qū)域查找？查找區(qū)域第幾列的值？精確查找還是模糊查找？（2）如何查找）如何查找“單位單位”？打開文件“飲料銷售(素材).xls” ，將文件另存為“姓名_飲料銷售.xls” 。在“銷售記錄”工作表 中，選中 F3 單元格 選擇“插入函數(shù)VLOOKUP” 單擊“確定”。由于要根據(jù)飲料的名稱查找“單位”，所以 VLOOKUP 函數(shù)的第一個參數(shù)應該選擇飲料名稱“D3”。在“Table_array”區(qū)域中選擇“飲料價格”工作表中的 B2:E44。由于“單位”數(shù)據(jù)存放在“飲料信息”數(shù)據(jù)區(qū)域的第 2 列，所以輸入數(shù)字“2”。由于要求飲料名稱

6、精確匹配，所以最后一個參數(shù)輸入“FALSE”。單擊“確定”，可以看到函數(shù)準確地返回了“統(tǒng)一奶茶”的單位“瓶”。復制公式,出現(xiàn)了什么問題？為什么？如何解決呢？提示：注意在復制公式時，如果沿著列拖動時，列標要用絕對引用，想想看為什么？修改公式后，重新復制公式。（3）創(chuàng)建區(qū)域名稱創(chuàng)建區(qū)域名稱選擇“飲料名稱”工作表。選中飲料名稱、單位、進價、售價所在的區(qū)域，即單元格區(qū)域 B2:E44。在名稱框中輸入“自己的姓名A”，一定要按回車確認。思 考思 考問題：問題：（1）區(qū)域的定義可以包含“序號”列嗎？為什么？（2）如果名稱定義錯誤了，如何將其刪除。溫馨提示：提示：也可以采用以下方法定義區(qū)域名稱：選擇菜單“插

7、入名稱定義”命令，打開“定義名稱”對話框，在名稱框中輸入“姓名 A”后，單擊“確定”按鈕，“姓名 A”區(qū)域名稱創(chuàng)建完成（4）利用區(qū)域名查找）利用區(qū)域名查找“進價進價”和和 “售價售價”選擇 “銷售記錄” 工作表 選中 G3 單元格 輸入等號 “=” 選擇函數(shù) “ VLOOKUP” 單擊“確定”。由于要根據(jù)飲料的名稱查找 “售價” ， 所以 VLOOKUP 函數(shù)的第一個參數(shù)應該選擇飲料名稱 “D3” 。在“Table_array”區(qū)域中輸入“姓名 A”。由于“進價”數(shù)據(jù)存放在第 3 列，所以輸入數(shù)字“3”。由于要求飲料名稱精確匹配，所以最后一個參數(shù)輸入“FALSE”單擊“確定”，可以看到函數(shù)準確

8、地返回了“統(tǒng)一奶茶”的“進價”數(shù)據(jù)“1.9”。復制公式，看一看有什么不同？同樣道理在 H3 單元格中建立查找飲料名稱“售價”的公式。怎么樣，對于 VLOOKUP 函數(shù)你是否有了一定的認識？如果還不會，請多做幾遍，慢慢去體會。（5）計算銷售額、）計算銷售額、毛利潤毛利潤溫馨溫馨提示：表格中數(shù)據(jù)之間的關系為：銷售額 售價*數(shù)量毛利潤 (售價進價)數(shù)量STEP 2分類匯總分類匯總首先，建立表“銷售記錄”的二個副本“銷售記錄(2)”、“銷售記錄(3)”和“銷售記錄(4)”。溫馨溫馨提示：按住 Ctrl 鍵拖動工作表標簽（1）按照）按照“所在區(qū)所在區(qū)”進行分類匯總進行分類匯總在表“銷售記錄(2)”中按“

9、所在區(qū)”對銷售額和毛利潤進行分類匯總，匯總結果顯示在數(shù)據(jù)下方（結果見“(樣例)飲料銷售.xls”）。將銷售 記 錄(2)改名為 “ 所在 區(qū) 匯總”結果參見樣例中的工作表“所在區(qū)匯總”溫馨溫馨提示：選擇“所在區(qū)”列中的任意單元格單擊“升序”按鈕進行排序（排序的作用是什么？）然后選擇“數(shù)據(jù)”“分類匯總”。（2）按照）按照“飲料名稱飲料名稱”進行分類匯總進行分類匯總在表“銷售記錄(3)”中按“飲料名稱”對銷售額和毛利潤進行分類匯總（匯總方式為求和）對匯總結果中的毛利潤列按降序排序，找出毛利潤最大的飲料。將銷售記錄(3)改名為“飲料名稱匯總”結果參見樣例中的工作表“飲料名稱匯總”溫馨溫馨提示：首先按

10、照“飲料名稱”對“銷售額”和“毛利潤”進行分類匯總；然后，對“毛利潤”進行降序排序。（3）用）用“嵌套分類匯總嵌套分類匯總”統(tǒng)計各個區(qū)和各飲料店的飲料統(tǒng)計各個區(qū)和各飲料店的飲料“銷售額銷售額”和和“毛利潤毛利潤”在“銷售記錄(4)”工作表中對“主要關鍵字”選擇“所在區(qū)”，“次要關鍵字”選擇“飲料店”排序。進行第一次“分類匯總”（分類字段為“所在區(qū)”） 。進行第二次“分類匯總”（分類字段為“飲料店”） 。將銷售記錄(4)改名為“嵌套匯總”結果參見樣例中的工作表“嵌套匯總”溫馨溫馨提示：首先，選擇“數(shù)據(jù)”“排序”命令，在主要關鍵字中選擇“所在區(qū)”，在次要關鍵字中選擇“飲料店”；然后，先按照“所在區(qū)

11、”進行分類匯總；再按照“飲料店”進行匯總，但是第二次匯總時，必須取消“替換當前分類匯總” 。STEP 3利用數(shù)據(jù)透視表實現(xiàn)統(tǒng)計分析利用數(shù)據(jù)透視表實現(xiàn)統(tǒng)計分析設計目標設計目標如下圖所示，利用數(shù)據(jù)透視表生成三維數(shù)據(jù)統(tǒng)計報表。利用排序利用排序， 找找出 銷 售 額 最出 銷 售 額 最大的飲料大的飲料（1）為）為 “銷售記錄銷售記錄”表創(chuàng)建數(shù)據(jù)透視表表創(chuàng)建數(shù)據(jù)透視表要求：為表“銷售記錄”建立數(shù)據(jù)透視表，將數(shù)據(jù)透視表顯示在新工作表中，并將數(shù)據(jù)透視表命名為“銷售統(tǒng)計”行顯示“飲料名稱飲料名稱”，列顯示“所在區(qū)所在區(qū)”，“銷售額銷售額”顯示在數(shù)據(jù)區(qū)。結果參見樣例中的“銷售統(tǒng)計”工作表。溫馨溫馨提示：選中“

12、銷售記錄”工作表的任意單元格，選擇“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”；單擊“下一步”，選擇銷售數(shù)據(jù)所在區(qū)域；單擊“下一步”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)е?3”；單擊“布局”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)Р季帧睂υ捒?，將“飲料名稱”拖到左邊圖形的“行”上，“所在區(qū)”拖到“列”上，銷售額拖到“數(shù)據(jù)”中，如下圖所示；單擊“確定”，數(shù)據(jù)透視表顯示位置：“新建工作表”。單擊“完成”， 將數(shù)據(jù)透視表命名為“銷售統(tǒng)計”。（2）根據(jù)數(shù)據(jù)透視表找出銷售額最大的飲料）根據(jù)數(shù)據(jù)透視表找出銷售額最大的飲料對毛利潤按降序排序，找出銷售額最大的三種飲料。結果參見樣例中的“銷售統(tǒng)計”工作表溫馨溫馨提示

13、：選中“總計”列的數(shù)值部分（不包含最后一行數(shù)值）然后單擊工具欄中的排序按鈕進行排序。（3）在）在“銷售統(tǒng)計銷售統(tǒng)計”工作表中，找出各個區(qū)工作表中，找出各個區(qū)“銷售額銷售額”最大的飲料最大的飲料在“銷售統(tǒng)計”工作表中，用 MAX 函數(shù)找出每個區(qū)“銷售額”最大的飲料的“銷售額” 。在“銷售統(tǒng)計”工作表中，用 VLOOKUP 函數(shù)找出各區(qū)“最大銷售額”所對應的“飲料名稱” 。STEP 4用用“兩軸線兩軸線-柱圖柱圖”比較比較“銷售額銷售額”和和“毛利潤毛利潤”設計目標現(xiàn)在，小李想用“兩軸線-柱圖”比較“南山區(qū)” 、 “福田區(qū)”和“羅湖區(qū)”的銷售額和毛利潤之間的關系。如下圖所示，用兩軸線柱圖表來比較銷

14、售額和毛利潤：（1）制作）制作“兩軸線圖兩軸線圖”在表“所在區(qū)匯總”分別選擇“所在區(qū)”、“銷售額”和“毛利潤”三列（如下圖）所示；單擊“圖表向?qū)А卑粹o，打開“圖表向?qū)?4-圖表類型”對話框，選擇“自定義類型自定義類型”選項卡，選擇“兩軸線-柱圖” 。單擊“下一步” ，打開“圖表向?qū)?4-圖表源數(shù)據(jù)”對話框；單擊“下一步” ，打開“圖表向?qū)?4-圖表選項”對話框，按下圖進行設置；單擊“下一步” ，在“圖表位置”對話框中，選擇“作為其中的對象插入” ，單擊“完成”按鈕。（2）美化圖表）美化圖表參照“飲料銷售(樣例).xls” ，完成對圖表格式的設置（格式可以自定義） 。STEP 5利用數(shù)據(jù)有效性制

15、作更方便利用數(shù)據(jù)有效性制作更方便、更實用的更實用的“新銷售記錄新銷售記錄”工工作表作表設計目標設計目標在“飲料銷售.xls”中制作一張“新銷售記錄表” ，并應用數(shù)據(jù)有效性設置，使得在填寫了銷售“數(shù)量”和選取了“飲料名稱”后，可以自動計算出“銷售額”、“毛利潤”和“毛利率”。（1）制作）制作“銷售記錄銷售記錄”表副本表副本在“飲料銷售.xls”中建立一個“銷售記錄”表的副本，并將其重命名為“新銷售記錄”，然后將其前五列的內(nèi)容刪除（只保留標題行），如下圖所示。并增加 3 個新列“實收”、“應收”和“找回”（2）對）對“飲料名稱飲料名稱”應用數(shù)據(jù)有效性設置應用數(shù)據(jù)有效性設置選中“飲料價格”表中的“飲

16、料名稱”區(qū)域，并將其定義為“飲料名稱”，如下圖所示。選中“新銷售記錄”的第 4 列（“飲料名稱”列），然后再選擇“數(shù)據(jù)”“有效性”打開數(shù)據(jù)有效性對話框。在有效性條件中選擇“序列”。在“來源”中填寫“=飲料名稱”（注意：“飲料名稱”是定義的“飲料名稱”區(qū)域），如圖下所示。（3）數(shù)據(jù)有效性的使用）數(shù)據(jù)有效性的使用制作完成，如下圖所示，試試看，是不是在填寫了銷售“數(shù)量”和選取了“飲料名稱”后，可以自動計算出“銷售額” 、 “毛利潤”和“毛利率” ，很方便吧！（4）對）對 “飲料店飲料店”列進行數(shù)據(jù)有效性設置列進行數(shù)據(jù)有效性設置參照“飲料名稱”的設置對“飲料店”進行設置（5）創(chuàng)建創(chuàng)建 “應收應收”和和

17、“找回找回”列列的公式的公式，算法如下：應收，算法如下：應收=銷售額，找回銷售額，找回=實收應收。實收應收。STEP 6讓查找公式更完美讓查找公式更完美（1）隱藏列）隱藏列在“新銷售記錄”工作表中將不需要顯示的列（“單位”、“進價”、“銷售額”）隱藏起來。（2）前面的結果有缺陷）前面的結果有缺陷在上面應用了查找函數(shù) VLOOKUP 的“銷售記錄”表中，如果把 D3 單元格中的飲料名稱刪去以后，可以看到 F3、G3、H3 單元格（即：單位、售價和進價）中均返回錯誤值“#N/A”。大家可以試一下，當 VLOOKUP 函數(shù)在“飲料價格”表中沒有找到 D3 單元格中的飲料名稱時都要返回錯誤值“#N/A

18、”。能不能讓當 VLOOKUP 函數(shù)在“飲料信息”表中沒有找到 D3 單元格中的飲料名稱時不返回錯誤值“#N/A”，而只什么都不顯示（即顯示空格）呢？問題：利用 IF 函數(shù)和 ISERROR 函數(shù)，使“銷售額”、“毛收入”和“毛利潤”三列的值，在沒有輸入飲料名稱時，不顯示“#VALUE!”錯誤值。（2）解決辦法）解決辦法利用 IF 和 ISERROR 函數(shù)可以解決上面的問題。試試看，你能解決這個問題嗎？上面的問題變成兩種情況：如果當 “VLOOKUP(D3,ylmc,2,FALSE)” 部分返回錯誤值的話， 則在 F3 中顯示空字符串。如果“VLOOKUP(D3,ylmc,2,FALSE)”部

19、分工作正常，則在 F3 單元格中顯示飲料對應的“售價”（即 VLOOKUP(D3,ylmc,2,FALSE)的值）。溫馨溫馨提示：提示：ISERROR(value)函數(shù)：當變量 value 是錯誤值“#N/A”時，返回邏輯真（TRUE），因此，當它與函數(shù) IF 結合在一起使用時，可以用于在公式中查出錯誤值。IF 函數(shù)與 ISERROR 函數(shù)聯(lián)合使用的通用表示法:IF(ISERROR(IF(ISERROR(VALUEVALUE),),”, ,VALUEVALUE) )STEP 7凍結窗口保留標題行在“銷售記錄銷售記錄”工作表中，當銷售記錄增多后，為了保留標題行的內(nèi)容，可單擊 A1 單元格，執(zhí)行“

20、窗口”“凍結窗口”命令，當滾動屏幕時，可以把第一行（即表頭行）的內(nèi)容一直保留在窗口中。4. 重點難點重點難點（1）重點VLOOKUP 函數(shù)的使用。區(qū)域名稱的定義分類匯總數(shù)據(jù)透視表（2）難點VLOOKUP 函數(shù)的參數(shù)的選擇數(shù)據(jù)透視表5. 案例總結與常見問題案例總結與常見問題本案例通過對學生信息表數(shù)據(jù)的處理， 介紹了查找與引用類函數(shù) VLOOKUP 的用法及分類匯總和數(shù)據(jù)透視表的用法。（一）你知道了嗎？（一）你知道了嗎？通過本案例的學習，你能回答如下問題嗎？1 1VLOOKUPVLOOKUP 函數(shù)是干什么的？函數(shù)是干什么的？2 2用用 VLOOKUPVLOOKUP 函數(shù)進行查找時函數(shù)進行查找時，所

21、要查找的元素為什么一定要在所定義的所要查找的元素為什么一定要在所定義的數(shù)據(jù)區(qū)域的第一列？數(shù)據(jù)區(qū)域的第一列？3 3VLOOKUPVLOOKUP 函數(shù)中第二個參數(shù)的含義是什么？在定義第二個參數(shù)時要注意函數(shù)中第二個參數(shù)的含義是什么？在定義第二個參數(shù)時要注意什么？什么？4 4在對在對 VLOOKPUVLOOKPU 函數(shù)沿列進行復制時函數(shù)沿列進行復制時，一般情況下一般情況下，為什么列標要用絕對為什么列標要用絕對地址？地址？5 5如何定義數(shù)據(jù)區(qū)域，如何刪除數(shù)據(jù)區(qū)域？如何定義數(shù)據(jù)區(qū)域，如何刪除數(shù)據(jù)區(qū)域？6用 VLOOKPU 函數(shù)時，可以不定義數(shù)據(jù)區(qū)域嗎？7 7什么是分類匯總？它有什么作用？什么是分類匯總？它

22、有什么作用？8 8在分類匯總之前要注意什么？在分類匯總之前要注意什么？（二）常見問題及處理方法（二）常見問題及處理方法下面把大家在本案例的學習過程中容易遇到的一些問題及處理方法列于下表：常見問題可能原因處理方法“數(shù)據(jù)區(qū)域” 名稱無法重新定義。該 “數(shù)據(jù)區(qū)域” 名稱已被定義。將其刪除，重新定義，方法：選擇“插入”“名稱”“定義”，打開“定義名稱”對話框，選擇已定義的名稱，單擊“刪除”命令。VLOOKUP 函 數(shù) 返回錯誤值“#N/A” 。（1）沒有把要查找的對象定義在“數(shù)據(jù)區(qū)域”的第一列。重新定義“數(shù)據(jù)區(qū)域” ，把要查找的內(nèi)容定義在“數(shù)據(jù)區(qū)域”的第一列。（2） 查找的內(nèi)容在定義的 “數(shù)據(jù)區(qū)域”中不存在。用 ISERROR 函數(shù)使錯誤值不顯示。使用“填充柄”沿列拖動復制公式時出現(xiàn)錯誤值“#N/A” 。要查找的對象（VLOOKUP 函數(shù)的第一個參數(shù)）列標沒有用絕對引用。在使用“填充柄”沿列拖動前先將第一個參數(shù)的列標絕對引用。分類匯總結果不正確。（1）在“分類匯總”之前沒先按要“分類”的字段排序。先按要分類的字段排序，然后再進行“分類匯總”。（2）雖然已先按要“分類”的字段排序， 但在 “分類匯總”時，分類字段選擇不正確（沒有選擇已排序的字段） 。在“分類匯總”時，“分類”字段選擇已排序的字段。6. 課后作業(yè)：學