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

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

2、果不想這么做，你有什么更好的辦法嗎？這個(gè)實(shí)際需求，開發(fā)Excel的工程師，已經(jīng)為我們想到了。在Excel中有一個(gè)函數(shù)，就是專門為解決這類問題設(shè)計(jì)的，這個(gè)函數(shù)就是VLOOKUP。小這個(gè)問題，可利用Excel中的查找函數(shù)VLOOKUP來解決。它的功能是，在數(shù)據(jù)區(qū)域的第一列中查找指定的數(shù)值，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指定列處的數(shù)值。下面來看看應(yīng)用VLOOKUP函數(shù)是如何解決上述問題的。3. 實(shí)現(xiàn)方法本案例要解決如下幾個(gè)問題：1在“銷售記錄”工作表中用VLOOKUP函數(shù)計(jì)算飲料的“單位”、“進(jìn)價(jià)”和“售價(jià)”等信息，并計(jì)算出工作表中的“銷售額”和“毛利潤”等信息。2用“分類匯總”統(tǒng)計(jì)出各連鎖店和各個(gè)區(qū)中各種

3、飲料的“銷售額”、“毛利潤”。3用“數(shù)據(jù)透視表”分析各個(gè)區(qū)中每種飲料的銷售情況和各個(gè)區(qū)中銷售情況最好的飲料。4另外，為了提高效率、避免出錯(cuò)，小還想制作一可以從下拉列表中選擇飲料名稱，并能自動計(jì)算出顧客應(yīng)交款及應(yīng)找回款的“新銷售記錄”工作表。4制作過程STEP 1VLOOKUP函數(shù)的使用設(shè)計(jì)目標(biāo)參照下圖，根據(jù)“銷售記錄”表中的“飲料名稱”列，利用VLOOKUP函數(shù)在“飲料基本信息”表中查找其他列（單位、進(jìn)價(jià)和售價(jià)）的值。根據(jù)以上兩個(gè)表中的數(shù)據(jù)得到下圖中的結(jié)果最后計(jì)算銷售額、毛利潤、毛利率（1）VLOOKUP函數(shù)是干什么用的 VLOOKUP函數(shù)的功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回?cái)?shù)據(jù)區(qū)域

4、當(dāng)前行中指定列處的值。 VLOOKUP的語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)精確查找還是模糊查找？查找區(qū)域第幾列的值？到哪個(gè)區(qū)域查找？查找什么？注意：要查找的對象（參數(shù)）一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù)）的第一列。下面來看看VLOOKUP函數(shù)是怎么用的。（2）如何查找“單位”？ 打開文件“飲料銷售(素材).xls”，將文件另存為“_飲料銷售.xls”。 在“銷售記錄”工作表中，選中F3單元格選擇“插入函數(shù)VLOOKUP”單擊“確定”。 由于要根據(jù)飲料的名稱查找“單位”，所以VLOOKUP函數(shù)的第一個(gè)參數(shù)應(yīng)該

5、選擇飲料名稱“D3”。 在“Table_array”區(qū)域中選擇“飲料價(jià)格”工作表中的B2:E44。 由于“單位”數(shù)據(jù)存放在“飲料信息”數(shù)據(jù)區(qū)域的第2列，所以輸入數(shù)字“2”。 由于要求飲料名稱精確匹配，所以最后一個(gè)參數(shù)輸入“FALSE”。 單擊“確定”，可以看到函數(shù)準(zhǔn)確地返回了“統(tǒng)一奶茶”的單位“瓶”。 復(fù)制公式,出現(xiàn)了什么問題？為什么？如何解決呢？提示：注意在復(fù)制公式時(shí)，如果沿著列拖動時(shí)，列標(biāo)要用絕對引用，想想看為什么？ 修改公式后，重新復(fù)制公式。（3）創(chuàng)建區(qū)域名稱 選擇“飲料名稱”工作表。 選中飲料名稱、單位、進(jìn)價(jià)、售價(jià)所在的區(qū)域，即單元格區(qū)域B2:E44。F溫馨提示：也可以采用以下方法定義

6、區(qū)域名稱： 選擇菜單“插入名稱定義”命令，打開“定義名稱”對話框， 在名稱框中輸入“某A”后，單擊“確定”按鈕，“某A”區(qū)域名稱創(chuàng)建完成 在名稱框中輸入“自己的A”，一定要按回車確認(rèn)。思考問題：（1）區(qū)域的定義可以包含“序號”列嗎？為什么？（2）如果名稱定義錯(cuò)誤了，如何將其刪除。（4）利用區(qū)域名查找“進(jìn)價(jià)”和“售價(jià)” 選擇“銷售記錄”工作表選中G3單元格輸入等號“=”選擇函數(shù)“VLOOKUP”單擊“確定”。 由于要根據(jù)飲料的名稱查找“售價(jià)”，所以VLOOKUP函數(shù)的第一個(gè)參數(shù)應(yīng)該選擇飲料名稱“D3”。 在“Table_array”區(qū)域中輸入“A”。 由于“進(jìn)價(jià)”數(shù)據(jù)存放在第3列，所以輸入數(shù)字“

7、3”。 由于要求飲料名稱精確匹配，所以最后一個(gè)參數(shù)輸入“FALSE”怎么樣，對于VLOOKUP函數(shù)你是否有了一定的認(rèn)識？如果還不會，請多做幾遍，慢慢去體會。 單擊“確定”，可以看到函數(shù)準(zhǔn)確地返回了“統(tǒng)一奶茶”的“進(jìn)價(jià)”數(shù)據(jù)“1.9”。 復(fù)制公式，看一看有什么不同？ 同樣道理在H3單元格中建立查找飲料名稱“售價(jià)”的公式。（5）計(jì)算銷售額、毛利潤F溫馨提示： 表格中數(shù)據(jù)之間的關(guān)系為：銷售額 售價(jià)*數(shù)量毛利潤 (售價(jià)進(jìn)價(jià))數(shù)量STEP 2分類匯總首先，建立表“銷售記錄”的二個(gè)副本“銷售記錄(2)”、“銷售記錄(3)”和“銷售記錄(4)”。F溫馨提示： 按住Ctrl鍵拖動工作表標(biāo)簽（1）按照“所在區(qū)”

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

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

10、必須取消“替換當(dāng)前分類匯總”。 結(jié)果參見樣例中的工作表“嵌套匯總”STEP 3利用數(shù)據(jù)透視表實(shí)現(xiàn)統(tǒng)計(jì)分析設(shè)計(jì)目標(biāo)如下圖所示，利用數(shù)據(jù)透視表生成三維數(shù)據(jù)統(tǒng)計(jì)報(bào)表。利用排序，找出銷售額最大的飲料（1）為“銷售記錄”表創(chuàng)建數(shù)據(jù)透視表要求：為表“銷售記錄”建立數(shù)據(jù)透視表，將數(shù)據(jù)透視表顯示在新工作表中，并將數(shù)據(jù)透視表命名為“銷售統(tǒng)計(jì)”行顯示“飲料名稱”，列顯示“所在區(qū)”，“銷售額”顯示在數(shù)據(jù)區(qū)。結(jié)果參見樣例中的“銷售統(tǒng)計(jì)”工作表。F溫馨提示： 選中“銷售記錄”工作表的任意單元格，選擇“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”； 單擊“下一步”，選擇銷售數(shù)據(jù)所在區(qū)域； 單擊“下一步”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視表

11、視圖向?qū)е?”； 單擊“布局”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)Р季帧睂υ捒?，將“飲料名稱”拖到左邊圖形的“行”上，“所在區(qū)”拖到“列”上，銷售額拖到“數(shù)據(jù)”中，如下圖所示； 單擊“確定”，數(shù)據(jù)透視表顯示位置：“新建工作表”。 單擊“完成”， 將數(shù)據(jù)透視表命名為“銷售統(tǒng)計(jì)”。（2）根據(jù)數(shù)據(jù)透視表找出銷售額最大的飲料 對毛利潤按降序排序，找出銷售額最大的三種飲料。 結(jié)果參見樣例中的“銷售統(tǒng)計(jì)”工作表F 溫馨提示： 選中“總計(jì)”列的數(shù)值部分（不包含最后一行數(shù)值） 然后單擊工具欄中的排序按鈕進(jìn)行排序。（3）在“銷售統(tǒng)計(jì)”工作表中，找出各個(gè)區(qū)“銷售額”最大的飲料 在“銷售統(tǒng)計(jì)”工作表中，用MA

12、X函數(shù)找出每個(gè)區(qū)“銷售額”最大的飲料的“銷售額”。 在“銷售統(tǒng)計(jì)”工作表中，用VLOOKUP函數(shù)找出各區(qū)“最大銷售額”所對應(yīng)的“飲料名稱”。STEP 4用“兩軸線-柱圖”比較“銷售額”和“毛利潤”設(shè)計(jì)目標(biāo)現(xiàn)在，小想用“兩軸線-柱圖”比較“南山區(qū)”、“福田區(qū)”和“羅湖區(qū)”的銷售額和毛利潤之間的關(guān)系。如下圖所示，用兩軸線柱圖表來比較銷售額和毛利潤：（1）制作“兩軸線圖” 在表“所在區(qū)匯總”分別選擇“所在區(qū)”、“銷售額”和“毛利潤”三列（如下圖）所示； 單擊“圖表向?qū)А卑粹o，打開“圖表向?qū)?4-圖表類型”對話框，選擇“自定義類型”選項(xiàng)卡，選擇“兩軸線-柱圖”。 單擊“下一步”，打開“圖表向?qū)?4-圖

13、表源數(shù)據(jù)”對話框； 單擊“下一步”，打開“圖表向?qū)?4-圖表選項(xiàng)”對話框，按下圖進(jìn)行設(shè)置； 單擊“下一步”，在“圖表位置”對話框中，選擇“作為其中的對象插入”，單擊“完成”按鈕。（2）美化圖表 參照“飲料銷售(樣例).xls”，完成對圖表格式的設(shè)置（格式可以自定義）。STEP 5利用數(shù)據(jù)有效性制作更方便、更實(shí)用的“新銷售記錄”工作表設(shè)計(jì)目標(biāo)在“飲料銷售.xls”中制作一“新銷售記錄表”，并應(yīng)用數(shù)據(jù)有效性設(shè)置，使得在填寫了銷售“數(shù)量”和選取了“飲料名稱”后，可以自動計(jì)算出“銷售額”、“毛利潤”和“毛利率”。（1）制作“銷售記錄”表副本 在“飲料銷售.xls”中建立一個(gè)“銷售記錄”表的副本，并將其

14、重命名為“新銷售記錄”，然后將其前五列的容刪除（只保留標(biāo)題行），如下圖所示。 并增加3個(gè)新列“實(shí)收”、“應(yīng)收”和“找回”（2）對“飲料名稱”應(yīng)用數(shù)據(jù)有效性設(shè)置 選中“飲料價(jià)格”表中的“飲料名稱”區(qū)域，并將其定義為“飲料名稱”，如下圖所示。 選中“新銷售記錄”的第4列（“飲料名稱”列），然后再選擇“數(shù)據(jù)”“有效性”打開數(shù)據(jù)有效性對話框。 在有效性條件中選擇“序列”。 在“來源”中填寫“=飲料名稱”（注意：“飲料名稱”是定義的“飲料名稱”區(qū)域），如圖下所示。（3）數(shù)據(jù)有效性的使用制作完成，如下圖所示，試試看，是不是在填寫了銷售“數(shù)量”和選取了“飲料名稱”后，可以自動計(jì)算出“銷售額”、“毛利潤”和“

15、毛利率”，很方便吧?。?）對 “飲料店”列進(jìn)行數(shù)據(jù)有效性設(shè)置參照“飲料名稱”的設(shè)置對“飲料店”進(jìn)行設(shè)置（5）創(chuàng)建 “應(yīng)收”和“找回”列的公式，算法如下：應(yīng)收=銷售額，找回=實(shí)收應(yīng)收。STEP 6讓查找公式更完美（1）隱藏列在“新銷售記錄”工作表中將不需要顯示的列（“單位”、“進(jìn)價(jià)”、“銷售額”）隱藏起來。（2）前面的結(jié)果有缺陷在上面應(yīng)用了查找函數(shù)VLOOKUP的“銷售記錄”表中，如果把D3單元格中的飲料名稱刪去以后，可以看到F3、G3、H3單元格（即：單位、售價(jià)和進(jìn)價(jià)）中均返回錯(cuò)誤值“#N/A”。大家可以試一下，當(dāng)VLOOKUP函數(shù)在“飲料價(jià)格”表中沒有找到D3單元格中的飲料名稱時(shí)都要返回錯(cuò)誤

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

17、中顯示飲料對應(yīng)的“售價(jià)”（即VLOOKUP(D3,ylmc,2,FALSE)的值）。F溫馨提示：ISERROR(value)函數(shù)：當(dāng)變量value是錯(cuò)誤值“#N/A”時(shí)，返回邏輯真（TRUE），因此，當(dāng)它與函數(shù) IF 結(jié)合在一起使用時(shí)，可以用于在公式中查出錯(cuò)誤值。IF函數(shù)與ISERROR函數(shù)聯(lián)合使用的通用表示法:IF(ISERROR(VALUE),”,VALUE)STEP 7凍結(jié)窗口保留標(biāo)題行在“銷售記錄”工作表中，當(dāng)銷售記錄增多后，為了保留標(biāo)題行的容，可單擊A1單元格，執(zhí)行“窗口”“凍結(jié)窗口”命令，當(dāng)滾動屏幕時(shí)，可以把第一行（即表頭行）的容一直保留在窗口中。4.重點(diǎn)難點(diǎn)（1） 重點(diǎn)l VLO

18、OKUP函數(shù)的使用。l 區(qū)域名稱的定義l 分類匯總l 數(shù)據(jù)透視表（2） 難點(diǎn)l VLOOKUP函數(shù)的參數(shù)的選擇l 數(shù)據(jù)透視表5.案例總結(jié)與常見問題本案例通過對學(xué)生信息表數(shù)據(jù)的處理，介紹了查找與引用類函數(shù)VLOOKUP的用法及分類匯總和數(shù)據(jù)透視表的用法。（一）你知道了嗎？通過本案例的學(xué)習(xí)，你能回答如下問題嗎？1VLOOKUP函數(shù)是干什么的？2用VLOOKUP函數(shù)進(jìn)行查找時(shí)，所要查找的元素為什么一定要在所定義的數(shù)據(jù)區(qū)域的第一列？3VLOOKUP函數(shù)中第二個(gè)參數(shù)的含義是什么？在定義第二個(gè)參數(shù)時(shí)要注意什么？4在對VLOOKPU函數(shù)沿列進(jìn)行復(fù)制時(shí)，一般情況下，為什么列標(biāo)要用絕對地址？5如何定義數(shù)據(jù)區(qū)域，

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