excel銷售數(shù)據(jù)分析-實訓(xùn)練習(xí)可編輯范本

PAGEPAGE1EＸCEL實訓(xùn)練習(xí)三商場銷售數(shù)據(jù)的分析處理１．問題的提出小李在深圳市開了若干家飲料連鎖店，為了提高管理水平，他打算用Excel工作表來管理銷售數(shù)據(jù)。下圖是他制作的各飲料店的銷售記錄流水帳表。為了統(tǒng)計“毛利潤”，他必須去“飲料基本信息"表中查找每種飲料的“進(jìn)價”和“售價”。這個工作量實在太大,而且還容易出錯。現(xiàn)在希望:能否輸入飲料名稱后，讓Ｅｘcｅl根據(jù)這個名稱自動去查找該飲料的“單位”、“進(jìn)價”、“售價”等信息,并存放到表“銷售記錄”的相應(yīng)列中。2．解決方案通常情況下,如果不借助其它方法的幫助，要想在Exｃeｌ中解決這個問題,只能到“飲料基本信息”表中一條一條地查找各種飲料的“進(jìn)價”和“售價”。如果不想這么做,你有什么更好的辦法嗎?這個實際需求，開發(fā)Ｅxcel的工程師,已經(jīng)為我們想到了。在Eｘcel中有一個函數(shù)，就是專門為解決這類問題設(shè)計的,這個函數(shù)就是VLＯOKUP。小李這個問題，可利用Excel中的查找函數(shù)ＶLOOKUP來解決。它的功能是，在數(shù)據(jù)區(qū)域的第一列中查找指定的數(shù)值，并返回數(shù)據(jù)區(qū)域當(dāng)前行中指定列處的數(shù)值.下面來看看應(yīng)用VLＯOKUP函數(shù)是如何解決上述問題的。３。實現(xiàn)方法本案例要解決如下幾個問題:1．在“銷售記錄”工作表中用VLOＯKＵP函數(shù)計算飲料的“單位”、“進(jìn)價”和“售價”等信息,并計算出工作表中的“銷售額"和“毛利潤”等信息.2.用“分類匯總”統(tǒng)計出各連鎖店和各個區(qū)中各種飲料的“銷售額”、“毛利潤"。3．用“數(shù)據(jù)透視表”分析各個區(qū)中每種飲料的銷售情況和各個區(qū)中銷售情況最好的飲料。4．另外,為了提高效率、避免出錯，小李還想制作一張可以從下拉列表中選擇飲料名稱,并能自動計算出顧客應(yīng)交款及應(yīng)找回款的“新銷售記錄”工作表。4．制作過程STＥP1—VＬOOKUＰ函數(shù)的使用設(shè)計目標(biāo) 參照下圖，根據(jù)“銷售記錄"表中的“飲料名稱＂列，利用VLOOKUP函數(shù)在“飲料基本信息”表中查找其他列(單位、進(jìn)價和售價)的值。根據(jù)以上兩個表中的數(shù)據(jù)得到下圖中的結(jié)果根據(jù)以上兩個表中的數(shù)據(jù)得到下圖中的結(jié)果最后計算銷售額、毛利潤、毛利率最后計算銷售額、毛利潤、毛利率（1)VLＯOＫUP函數(shù)是干什么用的VＬOOKUP函數(shù)的功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素,并返回數(shù)據(jù)區(qū)域當(dāng)前行中指定列處的值。VLOＯKUP的語法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)④精確查找還是模糊查找④精確查找還是模糊查找？③查找區(qū)域第幾列的值？②到哪個區(qū)域查找？①查找什么？注意:要查找的對象（參數(shù)①)一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù)②）的第一列。下面來看看ＶLOOＫＵP函數(shù)是怎么用的。(2)如何查找“單位”?打開文件“飲料銷售（素材)。xlｓ"，將文件另存為“姓名＿飲料銷售.ｘls”.在“銷售記錄"工作表中，選中Ｆ3單元格→選擇“插入→函數(shù)→VLOOKUP＂→單擊“確定”。由于要根據(jù)飲料的名稱查找“單位”，所以VＬOOKUP函數(shù)的第一個參數(shù)應(yīng)該選擇飲料名稱“Ｄ3”。在“Tａble＿ａrｒaｙ"區(qū)域中選擇“飲料價格"工作表中的Ｂ2:E４4。由于“單位"數(shù)據(jù)存放在“飲料信息”數(shù)據(jù)區(qū)域的第2列，所以輸入數(shù)字“2”。由于要求飲料名稱精確匹配,所以最后一個參數(shù)輸入“ＦALSE”。單擊“確定”，可以看到函數(shù)準(zhǔn)確地返回了“統(tǒng)一奶茶”的單位“瓶”。復(fù)制公式，出現(xiàn)了什么問題??？為什么？如何解決呢？提示:注意在復(fù)制公式時，如果沿著列拖動時，列標(biāo)要用絕對引用,想想看為什么?修改公式后,重新復(fù)制公式。（3)創(chuàng)建區(qū)域名稱選擇“飲料名稱”工作表。選中飲料名稱、單位、進(jìn)價、售價所在的區(qū)域，即單元格區(qū)域Ｂ２:E4４。溫馨提示： 也可以采用以下方法定義區(qū)域名稱：選擇菜單“溫馨提示： 也可以采用以下方法定義區(qū)域名稱：選擇菜單“插入→名稱→定義”命令，打開“定義名稱”對話框，在名稱框中輸入“姓名A”后，單擊“確定”按鈕，“姓名A”區(qū)域名稱創(chuàng)建完成思考問題：（1）區(qū)域的定義可以包含“序號”列嗎？為什么?(2)如果名稱定義錯誤了，如何將其刪除。(4）利用區(qū)域名查找“進(jìn)價”和“售價＂選擇“銷售記錄"工作表→選中G3單元格→輸入等號“=”→選擇函數(shù)“VLOOKUP＂→單擊“確定"。由于要根據(jù)飲料的名稱查找“售價”，所以VＬOOKUP函數(shù)的第一個參數(shù)應(yīng)該選擇飲料名稱“D３”。在“Tablｅ_arｒay”區(qū)域中輸入“姓名A”。由于“進(jìn)價”數(shù)據(jù)存放在第3列,所以輸入數(shù)字“3”。由于要求飲料名稱精確匹配,所以最后一個參數(shù)輸入“FＡLSE”怎么樣，對于VLOOKUP函數(shù)你是否有了一定的認(rèn)識？如果還不會，請多做幾遍，慢慢去體會。單擊“確定”,可以看到函數(shù)準(zhǔn)確地返回了“統(tǒng)一奶茶”的“進(jìn)價”數(shù)據(jù)“１．9”怎么樣，對于VLOOKUP函數(shù)你是否有了一定的認(rèn)識？如果還不會，請多做幾遍，慢慢去體會。復(fù)制公式，看一看有什么不同？??同樣道理在H3單元格中建立查找飲料名稱“售價”的公式.(5)計算銷售額、毛利潤溫馨提示：表格中數(shù)據(jù)之間的關(guān)系為：銷售額＝售價*數(shù)量毛利潤＝(售價－進(jìn)價)×數(shù)量ＳＴEＰ2—分類匯總首先,建立表“銷售記錄"的二個副本“銷售記錄（2)"、“銷售記錄（3)”和“銷售記錄（4)"。溫馨提示：按住Ctrl鍵拖動工作表標(biāo)簽（１)按照“所在區(qū)”進(jìn)行分類匯總溫馨提示：選擇“所在區(qū)”列中的任意單元格單擊“升序”按鈕進(jìn)行排序（排序的作用是什么？）然后選擇“溫馨提示：選擇“所在區(qū)”列中的任意單元格單擊“升序”按鈕進(jìn)行排序（排序的作用是什么？）然后選擇“數(shù)據(jù)”→“分類匯總”。將銷售記錄(2)改名為“所在區(qū)匯總"結(jié)果參見樣例中的工作表——“所在區(qū)匯總”（２）按照“飲料名稱”進(jìn)行分類匯總在表“銷售記錄（3)”中按“飲料名稱”對銷售額和毛利潤進(jìn)行分類匯總(匯總方式為求和）對匯總結(jié)果中的毛利潤列按降序排序,找出毛利潤最大的飲料。將銷售記錄(3)改名為“飲料名稱匯總”結(jié)果參見樣例中的工作表——“飲料名稱匯總”溫馨提示：首先按照“飲料名稱”對“銷售額”和“毛利潤”進(jìn)行分類匯總；然后，對“毛利潤”進(jìn)行降序排序。(3）用“嵌套分類匯總”統(tǒng)計各個區(qū)和各飲料店的飲料“銷售額"和“毛利潤”在“銷售記錄(4)＂工作表中對“主要關(guān)鍵字＂選擇“所在區(qū)＂,“次要關(guān)鍵字"選擇“飲料店”排序.進(jìn)行第一次“分類匯總”(分類字段為“所在區(qū)”)。進(jìn)行第二次“分類匯總”(分類字段為“飲料店”）.將銷售記錄(4)改名為“嵌套匯總”溫馨提示：首先，選擇“溫馨提示：首先，選擇“數(shù)據(jù)”→“排序”命令，在主要關(guān)鍵字中選擇“所在區(qū)”，在次要關(guān)鍵字中選擇“飲料店”；然后，先按照“所在區(qū)”進(jìn)行分類匯總；再按照“飲料店”進(jìn)行匯總，但是第二次匯總時，必須取消“替換當(dāng)前分類匯總”。STEP3—利用數(shù)據(jù)透視表實現(xiàn)統(tǒng)計分析設(shè)計目標(biāo)如下圖所示，利用數(shù)據(jù)透視表生成三維數(shù)據(jù)統(tǒng)計報表。利用排序，找出銷售額最大的飲料利用排序，找出銷售額最大的飲料（1）為“銷售記錄"表創(chuàng)建數(shù)據(jù)透視表要求：為表“銷售記錄”建立數(shù)據(jù)透視表，將數(shù)據(jù)透視表顯示在新工作表中,并將數(shù)據(jù)透視表命名為“銷售統(tǒng)計”行顯示“飲料名稱”，列顯示“所在區(qū)",“銷售額"顯示在數(shù)據(jù)區(qū)。?結(jié)果參見樣例中的“銷售統(tǒng)計"工作表。溫馨提示：選中“銷售記錄”工作表的任意單元格，選擇“數(shù)據(jù)”→“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”；單擊“下一步”，選擇銷售數(shù)據(jù)所在區(qū)域；單擊“下一步”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)е?”單擊“布局”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)А季帧睂υ捒?，將“飲料名稱”拖到左邊圖形的“行”上，“所在區(qū)”拖到“列”上，銷售額拖到“數(shù)據(jù)”中，如下圖所示；單擊“確定”，數(shù)據(jù)透視表顯示位置：“新建工作表”。單擊“完成”，將數(shù)據(jù)透視表命名為“銷售統(tǒng)計”。（2)根據(jù)數(shù)據(jù)透視表找出銷售額最大的飲料對毛利潤按降序排序，找出銷售額最大的三種飲料。結(jié)果參見樣例中的“銷售統(tǒng)計"工作表溫馨提示：選中“總計”列的數(shù)值部分（不包含最后一行數(shù)值）然后單擊工具欄中的排序按鈕進(jìn)行排序。(3）在“銷售統(tǒng)計＂工作表中,找出各個區(qū)“銷售額”最大的飲料在“銷售統(tǒng)計"工作表中,用MAX函數(shù)找出每個區(qū)“銷售額”最大的飲料的“銷售額”。在“銷售統(tǒng)計"工作表中，用VLOOKUP函數(shù)找出各區(qū)“最大銷售額”所對應(yīng)的“飲料名稱".SＴＥP４—用“兩軸線-柱圖"比較“銷售額”和“毛利潤”設(shè)計目標(biāo)現(xiàn)在,小李想用“兩軸線-柱圖"比較“南山區(qū)”、“福田區(qū)”和“羅湖區(qū)”的銷售額和毛利潤之間的關(guān)系。如下圖所示,用兩軸線柱圖表來比較銷售額和毛利潤：（１)制作“兩軸線圖”在表“所在區(qū)匯總”分別選擇“所在區(qū)”、“銷售額”和“毛利潤”三列（如下圖）所示;單擊“圖表向?qū)А卑粹o,打開“圖表向?qū)А?－圖表類型”對話框，選擇“自定義類型”選項卡,選擇“兩軸線—柱圖”。單擊“下一步＂,打開“圖表向?qū)А?—圖表源數(shù)據(jù)”對話框;單擊“下一步”，打開“圖表向?qū)В?-圖表選項＂對話框，按下圖進(jìn)行設(shè)置；單擊“下一步”，在“圖表位置”對話框中,選擇“作為其中的對象插入”，單擊“完成”按鈕。(2）美化圖表參照“飲料銷售(樣例)。xls”，完成對圖表格式的設(shè)置(格式可以自定義）。STEP5—利用數(shù)據(jù)有效性制作更方便、更實用的“新銷售記錄”工作表設(shè)計目標(biāo)在“飲料銷售。xｌｓ＂中制作一張“新銷售記錄表",并應(yīng)用數(shù)據(jù)有效性設(shè)置,使得在填寫了銷售“數(shù)量”和選取了“飲料名稱”后，可以自動計算出“銷售額”、“毛利潤"和“毛利率"。（１）制作“銷售記錄”表副本在“飲料銷售。xls"中建立一個“銷售記錄”表的副本,并將其重命名為“新銷售記錄"，然后將其前五列的內(nèi)容刪除（只保留標(biāo)題行）,如下圖所示。并增加３個新列“實收”、“應(yīng)收”和“找回"（2)對“飲料名稱”應(yīng)用數(shù)據(jù)有效性設(shè)置選中“飲料價格”表中的“飲料名稱”區(qū)域，并將其定義為“飲料名稱”，如下圖所示。選中“新銷售記錄”的第4列(“飲料名稱”列）,然后再選擇“數(shù)據(jù)＂→“有效性”打開數(shù)據(jù)有效性對話框。在有效性條件中選擇“序列”。在“來源"中填寫“=飲料名稱"（注意：“飲料名稱"是定義的“飲料名稱”區(qū)域）,如圖下所示.(3）數(shù)據(jù)有效性的使用制作完成，如下圖所示，試試看，是不是在填寫了銷售“數(shù)量”和選取了“飲料名稱"后,可以自動計算出“銷售額"、“毛利潤”和“毛利率”，很方便吧!(４)對“飲料店”列進(jìn)行數(shù)據(jù)有效性設(shè)置參照“飲料名稱”的設(shè)置對“飲料店”進(jìn)行設(shè)置（5）創(chuàng)建“應(yīng)收”和“找回”列的公式,算法如下:應(yīng)收=銷售額，找回=實收－應(yīng)收。ＳTEP6—讓查找公式更完美（1）隱藏列在“新銷售記錄"工作表中將不需要顯示的列(“單位”、“進(jìn)價”、“銷售額”）隱藏起來.(２）前面的結(jié)果有缺陷在上面應(yīng)用了查找函數(shù)VLOOKUP的“銷售記錄”表中,如果把D3單元格中的飲料名稱刪去以后,可以看到Ｆ３、G３、H3單元格(即：單位、售價和進(jìn)價)中均返回錯誤值“＃N／A”。大家可以試一下，當(dāng)ＶLOＯKUP函數(shù)在“飲料價格”表中沒有找到D３單元格中的飲料名稱時都要返回錯誤值“#Ｎ/A”。能不能讓當(dāng)VＬOOKUＰ函數(shù)在“飲料信息”表中沒有找到Ｄ3單元格中的飲料名稱時不返回錯誤值“＃N／A”,而只什么都不顯示（即顯示空格）呢?問題：利用IF函數(shù)和ＩSＥRRＯＲ函數(shù)，使“銷售額”、“毛收入”和“毛利潤”三列的值,在沒有輸入飲料名稱時,不顯示“＃VALUE!”錯誤值.(2)解決辦法利用IF和IＳERROR函數(shù)可以解決上面的問題。試試看,你能解決這個問題嗎?上面的問題變成兩種情況：如果當(dāng)“VＬOＯKUP（D3，yｌmｃ,2,ＦＡLＳＥ）＂部分返回錯誤值的話,則在Ｆ3中顯示空字符串。如果“ＶLOOKＵP(Ｄ3,ｙlmc，2，F(xiàn)ALSE)＂部分工作正常,則在F３單元格中顯示飲料對應(yīng)的“售價"(即VLOOKＵP（D３,ylmc,２，ＦALSE)的值)。溫馨提示：ISERROR(value)函數(shù)：當(dāng)變量value是錯誤值“#N/A”時，返回邏輯真（TRUE），因此，當(dāng)它與函數(shù)IF結(jié)合在一起使用時，可以用于在公式中查出錯誤值。IF函數(shù)與ISERROR函數(shù)聯(lián)合使用的通用表示法:IF(ISERROR(VALUE),””,VALUE)SＴEP7－凍結(jié)窗口保留標(biāo)題行在“銷售記錄”工作表中，當(dāng)銷售記錄增多后，為了保留標(biāo)題行的內(nèi)容,可單擊A1單元格,執(zhí)行“窗口”→“凍結(jié)窗口”命令，當(dāng)滾動屏幕時,可以把第一行(即表頭行)的內(nèi)容一直保留在窗口中。4．重點難點重點VLＯＯKUP函數(shù)的使用。區(qū)域名稱的定義分類匯總數(shù)據(jù)透視表難點VＬOOＫUＰ函數(shù)的參數(shù)的選擇數(shù)據(jù)透視表5．案例總結(jié)與常見問題本案例通過對學(xué)生信息表數(shù)據(jù)的處理,介紹了查找與引用類函數(shù)ＶLOOKUP的用法及分類匯總和數(shù)據(jù)透視表的用法。(一）你知道了嗎?通過本案例的學(xué)習(xí),你能回答如下問題嗎?1．VLＯOKUP函數(shù)是干什么的?2.用VLOOKUP函數(shù)進(jìn)行查找時,所要查找的元素為什么一定要在所定義的數(shù)據(jù)區(qū)域的第一列?3．VLOＯKUP函數(shù)中第二個參數(shù)的含義是什么？在定義第二個參數(shù)時要注意什么?4.在對VLOＯKPU函數(shù)沿列進(jìn)行復(fù)制時，一般情況下，為什么列標(biāo)要用絕對地址？5.如何定義數(shù)據(jù)區(qū)域，如何刪除數(shù)據(jù)區(qū)域？６．用ＶＬOOKPＵ函數(shù)時，可以不定義數(shù)據(jù)區(qū)域嗎?７.什么是分類匯總？它有什么作用？8.在分類匯總之前要注意什么？(二)常見問題及處理方法下面把大家在本案例的學(xué)習(xí)過程中容易遇到的一些問題及處理方法列于下表:常見問題可能原因處理方法“數(shù)據(jù)區(qū)域”名稱無法重新定義.該“數(shù)據(jù)區(qū)域”名稱已被定義.將其刪除,重新定義，方法:選擇“插入”→“名稱”→“定義”，打開“定義名稱”對話框,選擇已定義的名稱,單擊“刪除”命令。VLOOＫUP函數(shù)返回錯誤值“#N/A”．(１）沒有把要查找的對象定義在“數(shù)據(jù)區(qū)域"的第一列。重新定義“數(shù)據(jù)區(qū)域”，把要查找的內(nèi)容定義在“數(shù)據(jù)區(qū)域”的第一列．(2）查找的內(nèi)容在定義的“數(shù)據(jù)區(qū)域"中不存在．用ISERROR函數(shù)使錯誤值不顯示。使用“填充柄”沿列拖動復(fù)制公式時出現(xiàn)錯誤值“#Ｎ/A＂.要查找的對象(ＶLOＯＫUP函數(shù)的第一個參數(shù)）列標(biāo)沒有用絕對引用。在使用“填充柄”沿列拖動前先將第一個參數(shù)的列標(biāo)絕對引用。分類匯總結(jié)果不正確。(1）在“分類匯總”之前沒先按要“分類”的字段排序.先按要分類的字段排序,然后再進(jìn)行“分類匯總＂。（２)雖然已先按要“分類”的字段排序,但在“分類匯總"時,分類字段選擇不正確（沒有選擇已排序的字段)。在“分類匯總＂時,“分類”字段選擇已排序的字段。6。課后作業(yè):學(xué)生成績的查詢和統(tǒng)計請大家用查找函數(shù)