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

1、1. EXCEL實(shí)訓(xùn)練習(xí)商場(chǎng)銷售數(shù)據(jù)的分析處理問題的提出小李在深圳市開了若十家飲料連鎖店，為了提高管理水平，他打算用Excel工作表來管理銷售數(shù)據(jù)。下圖是他制作的各飲料店的銷售記錄流水帳表。為了統(tǒng)計(jì)“毛利潤(rùn)”，他必須去“飲料基本信息”表中查找每種飲料的“進(jìn)價(jià)”和“售價(jià)”。這個(gè)工作量實(shí)在太大，而且還容易出錯(cuò)。現(xiàn)在希望：能否輸入飲料名稱后，讓Excel根據(jù)這個(gè)名稱自動(dòng)去查找該飲料的“單位”“進(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ù)就是VLOOKUPo小李這個(gè)問題，可利用Excel中的查找函數(shù)VLOOKUP來解決。它的功能是，在數(shù)據(jù)區(qū)域的第一歹0中查找指定的數(shù)值，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指定歹0處的數(shù)值。下面來看看應(yīng)用VLOOKUP函數(shù)是如何解決上述問題的。3. 實(shí)現(xiàn)方法本案例要解決如下幾個(gè)問題：1.在“銷售記錄”工作表中用VLOOKUP函數(shù)計(jì)算飲料的“單位”、“進(jìn)價(jià)”和“售價(jià)”等信息，并計(jì)算出工作表中的“銷售額”和“毛利潤(rùn)”等信息。2. 用“分類匯總”統(tǒng)計(jì)出各連鎖

3、店和各個(gè)區(qū)中各種飲料的“銷售額”、“毛利潤(rùn)”。3. 用“數(shù)據(jù)透視表”分析各個(gè)區(qū)中每種飲料的銷售情況和各個(gè)區(qū)中銷售情況最好的飲料。4. 另外，為了提高效率、避免出錯(cuò)，小李還想制作一張可以從下拉列表中選擇飲料名稱，并能自動(dòng)計(jì)算出顧客應(yīng)交款及應(yīng)找回款的“新銷售記錄”工作表。4. 制作過程STEP1VLOOKUffl數(shù)的使用設(shè)計(jì)目標(biāo)在“飲料基本信息”表中查找其他列（單位、進(jìn)價(jià)和售價(jià)）的值序號(hào)飲料名稱單位Mt價(jià)售價(jià)II1可口可樂聽1.702.102百事可樂聽1.602.003可口可樂（1L）瓶2.102.604百事可樂（IL）瓶幻002,505雪碧聽1.702.206雪碧QL）瓶幻002.507七喜聽1

4、.702.208七喜（LL）瓶2,002.6Q9匯源果汁含7.909.S010美年達(dá)一聽L702.2011芬達(dá)聽1.602.0012醒目聽幻002.5013健力寶聽1.702.2014.王老吉含L702.20日集躋在已伎拜廟竹料名障蒙量單位進(jìn)怕200-1-12007-1-12叩TT-12001-1-120011-12001-1-12001-1-1也5-1-1200F-1-1200>S£007-1-12001-1-1山山U山世西fflE西ffig西茹一姐茶紅牛非常可岳燮哈里圓王走吉怡生垣凈*2001-1-12001-1-12001-1-12001-1-1腐hi果航橙HTmrtiD

5、流一綠薈旌昭瑁'蛇凈業(yè)樂百虎坎凈一水?noi-I2001-1-12001-1-12001-1-12001-112007-1-1西亟屆而西居西麗腐西麗后世E盤七喜可匚可殘51巨旭日分訊紅桀樂而氏果奶根據(jù)以上兩個(gè)表中的數(shù)據(jù)得到下圖中的結(jié)果121.21.G2L44.5I.220363214聽LTA1花聽2£.5IETALT2.298瓶1,31,722聽1,ei2白期單住20U7-1-1簡(jiǎn)心西麗店域-奶釜70J.J2.44.口2CO7-1-1南山茵麗居783.22LU7-1-1肉山茵而居10所J-2l.ti2CO7-1-1南山茜KU居非常司采8J.S2JCO7-1-1南山西而店百季可

6、樂（1L）5922-52CO7-1-I南山百麗店垃吟略里奶23L.4l.S2CO7-1-1WillWW刖4.562007-1-1WU空41481.72.22CO711南山茵麗店工老石r合J.72007-1-1他1山|茵麗店620.922007-1-1兩心茵而舊畫麗店西麗居MU7-1-J國(guó)心2007-1-1WlLi2UUF-1H南山20O7-P1有山2007-1-1肉山2007-1-1國(guó)山20O7-H1同山甬山2007-11雨山2007-1-1MEWdS-1-l宙近西面店20U7-1-1南山I也酣店西業(yè)店西麗店西麗廟商麗序西麗店茵麗居西麗府西麗后可口可岳HL）頊一綠親垃喈哈緒凈也樂百氏純凈龍七喜

7、七喜口口司口可樂醒目旭日洲述紅茶樂百良果始芬達(dá)2"182"JM56837fi23H791598222.j1.2.92.31.7168.UU327.6010.ODJ47.5D41.40516.OD105,6037-4。74.407O,2D54.OD70,2021d,2067.20J0T.9D167.2D53.6D29,401S7-SD33.00166.6044.OU35.007«.0(6.403.2029.509.2C12S.J024.308.5018.60ie.2010.3013.504J01£.3024."3土3013.30F.6039.50

8、7.5039.208«80（1）VLOOKUP函數(shù)是干什么用的VLOOKUP數(shù)的功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指定列處的值。VLOOKUP語(yǔ)法:VLOOKUP(lookUPvalue,tabearray,coli查找什么？到哪個(gè)區(qū)域查找？查找區(qū)域精確查找第幾列的值？還是模糊查找？注息:）的第一列。要查找的對(duì)象（參數(shù)）一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù)卜面來看看VLOOKUP數(shù)是怎么用的。（2）如何查找“單位”？打開文件“飲料銷售（素材）.xls”，將文件另存為“姓名_飲料銷售.xls”在“銷售記錄”工作表中，選中F3單元格選擇“插入函數(shù)VLOOKUP單擊“確

9、定”。應(yīng)該選擇飲料名稱“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í)，如果沿著列拖動(dòng)時(shí)，列標(biāo)要用絕對(duì)引用，想想看為什么?修改公式后，重新復(fù)制公式。創(chuàng)建區(qū)域名稱選擇“飲料名稱”工作表。選中飲料名稱、單位、進(jìn)價(jià)、售價(jià)所在的區(qū)域，即單元格區(qū)域B2:E44在名稱框中輸入“白己的姓名+A”，一定要按回

10、車確認(rèn)。思考問題：(1)區(qū)域的定義可以包含“序號(hào)”列嗎？為什么?(2)如果名稱定義錯(cuò)誤了，如何將其刪除。(3) 利用區(qū)域名查找“進(jìn)價(jià)”和“售價(jià)”選擇“銷售記錄”工作表-選中G3單元格-輸入等號(hào)“=”-選擇函該選擇飲料名稱“D3'在“Table_array”區(qū)域中輸入“姓名A”。由于“進(jìn)價(jià)”數(shù)據(jù)存放在第3列，所以輸入數(shù)字“3”。由于要求飲料名稱精確匹配，所以最后一個(gè)參數(shù)輸入“FALSE數(shù)據(jù)1.9”。單擊“確定”，可以看到函數(shù)準(zhǔn)確地返回了“統(tǒng)一奶茶”的“進(jìn)價(jià)怎么樣，對(duì)丁VLOOKUP復(fù)制公式，看一看有什么不同？你是否有了一定的認(rèn)識(shí)？如果同樣道理在H3單元格中建立查找飲料名稱，日還售備”年孩

11、幽&，慢慢去(5)計(jì)算銷售額、毛利潤(rùn)r1首先，建立表“銷售記錄”的二個(gè)副本“銷售記錄（2）”、“銷售記錄（3）和“銷售記錄（4）”。例)飲料銷售.xls”)在表“銷售記錄(2)”中按“所在區(qū)”對(duì)銷售額和毛利潤(rùn)進(jìn)行分類匯總,匯總結(jié)果顯示在數(shù)據(jù)下方(結(jié)果見“(樣將銷售記錄(2)改名為“所在區(qū)匯總”結(jié)果參見樣例中的工作表一一“所在區(qū)匯總在表“銷售記錄(3)”中按“飲料名稱”對(duì)銷售額和毛利潤(rùn)進(jìn)行分類匯總(匯總方式為求和)對(duì)匯總結(jié)果中的毛利潤(rùn)列按降序排序，找出毛利潤(rùn)最大的飲料。將銷售記錄(3)改名為“飲料名稱匯總”結(jié)果參見樣例中的工作表一一“飲料名稱匯總”（3）用嵌套分類匯總”統(tǒng)計(jì)各個(gè)區(qū)和各飲料

12、店的飲料銷售額”和毛利潤(rùn)”在“銷售記錄（4）”工作表中對(duì)“主要關(guān)鍵字”選擇“所在區(qū)”，“次要關(guān)鍵字”選擇“飲料店”排序。進(jìn)行第一次“分類匯總”（分類字段為“所在區(qū)”）。進(jìn)行第二次“分類匯總”（分類字段為“飲料店”）。將銷售記錄（4）改名為“嵌套匯總”結(jié)果參見樣例中的工作表一一“嵌套匯總”STEP3利用數(shù)據(jù)透視表實(shí)現(xiàn)統(tǒng)計(jì)分析設(shè)計(jì)目標(biāo)如下圖所示，利用數(shù)據(jù)透視表生成三維數(shù)據(jù)統(tǒng)計(jì)報(bào)表。求而項(xiàng)吊詹額所在區(qū)I，飲料名稱W福田龍崗羅湖南山匯源臬汁1215.21989.4178361037.SL叮3.«wq如t菊萄汁103S153613301176156(71Si蘋果汁1008.8191&8

13、1315.61877.?1055.e717E利用排序，找1125.61558.2760.21230.62446.fT123_2790.4434.26372056.6686.-：4604"出銷售額最67514766B45269T£14539SG0&6211195577214獺L#V-JT?J1.356.56TL62509.3552227.14317.1七喜»FD8.2580.81408759902415F底孫1332534975642.2777.<Tm<1570.S4SL3763.4400.4710.63725.8<693636.3852.

14、63S4.6702.53570七呂口LJ1/313-81258.4444.SS57.835L4353&醒目k9S069。64076540S3450百事可樂HL）£42.58E0785131.56盅53417_5旭日升冰紅茶774.4737294-8S73.2652.2314LS健力寶644,6184.8554534.Gno®.83066.3鮮橙多555&527633098493033伊利純牛奶530.1633.9916,3638.4345.829S4(1) 為“銷售記錄”表創(chuàng)建數(shù)據(jù)透視表要求：為表“銷售記錄”建立數(shù)據(jù)透視表，將數(shù)據(jù)透視表顯示在新工作表中，并將

15、數(shù)據(jù)透視表命名為“銷售統(tǒng)計(jì)”行顯示“飲料名稱”，列顯示“所在區(qū)”，“銷售額”顯示在數(shù)據(jù)區(qū)。結(jié)果參見樣例中的“銷售統(tǒng)計(jì)”工作表。根據(jù)數(shù)據(jù)透視表找出銷售額最大的飲料對(duì)毛利潤(rùn)按降序排序，找出銷售額最大的三種飲料。結(jié)果參見樣例中的“銷售統(tǒng)計(jì)”工作表(2) 在“銷售統(tǒng)計(jì)”工作表中，找出各個(gè)區(qū)“銷售額”最大的飲料在“銷售統(tǒng)計(jì)”工作表中，用MAX®數(shù)找出每個(gè)區(qū)“銷售額”最大的飲料的“銷售額”。在“銷售統(tǒng)計(jì)”工作表中，用VLOOKUP數(shù)找出各區(qū)“最大銷售額”所對(duì)應(yīng)的“飲料名稱”。STEP4用“兩軸線-柱圖”比較“銷售額”和“毛利潤(rùn)”設(shè)計(jì)目標(biāo)現(xiàn)在，小李想用“兩軸線-柱圖”比較“南山區(qū)”、“福田區(qū)”和“

16、羅湖區(qū)”的銷售額和毛利潤(rùn)之間的關(guān)系。如下圖所示，用兩軸線柱圖表來比較銷售額和毛利潤(rùn)：（1）制作“兩軸線圖”在表“所在區(qū)匯總”分別選擇“所在區(qū)”、“銷售額”和“毛利潤(rùn)”三列（如下圖）所示；單擊“圖表向?qū)А卑粹o，打開“圖表向?qū)?4-圖表類型”對(duì)話框，選擇“白定義類型”選項(xiàng)卡，選擇“兩軸線-柱圖”。單擊“下一步”，打開“圖表向?qū)?4-圖表源數(shù)據(jù)”對(duì)話框；單擊“下一步”，打開“圖表向?qū)?4-圖表選項(xiàng)”對(duì)話框，按下圖進(jìn)行設(shè)置；單擊“下一步”，在“圖表位置”對(duì)話框中，選擇“作為其中的對(duì)象插入”，單擊“完成”按鈕。（2）美化圖表參照“飲料銷售（樣例）.xls”，完成對(duì)圖表格式的設(shè)置（格式可以白定義）。STE

17、P5利用數(shù)據(jù)有效性制作更方便、更實(shí)用的“新銷售記錄”工作表設(shè)計(jì)目標(biāo)在“飲料銷售.xls”中制作一張“新銷售記錄表”，并應(yīng)用數(shù)據(jù)有效性設(shè)置，使得在填寫了銷售“數(shù)量”和選取了“飲料名稱”后，可以自動(dòng)計(jì)算出“銷售額”、“毛利潤(rùn)”和“毛利率”。(1)制作“銷售記錄”表副本在“飲料銷售.xls”中建立一個(gè)“銷售記錄”表的副本，并將其重命名為“新銷售記錄”，然后將其前五列的內(nèi)容刪除(只保留標(biāo)題行)，如下圖所示。并增加3個(gè)新列“實(shí)收”、“應(yīng)收”和“找回”(2)對(duì)“飲料名稱”應(yīng)用數(shù)據(jù)有效性設(shè)置選中“飲料價(jià)格”表中的“飲料名稱”區(qū)域，并將其定義為“飲料名稱”，如下圖所示。選中“新銷售記錄”的第4列(“飲料名稱”

18、列)，然后再選擇“數(shù)據(jù)”T“有效性”打開數(shù)據(jù)有效性對(duì)話框。在有效性條件中選擇“序列”。在“來源”中填寫“=飲料名稱”(注意：“飲料名稱”是定義的“飲料名稱”區(qū)域)，如圖下所示。(3) 數(shù)據(jù)有效性的使用制作完成，如下圖所示，試試看，是不是在填寫了銷售“數(shù)量”和選取了“飲料名稱”后，可以自動(dòng)計(jì)算出“銷售額”、“毛利潤(rùn)”和“毛利率”，很方便吧！(4) 對(duì)“飲料店”列進(jìn)行數(shù)據(jù)有效性設(shè)置參照“飲料名稱”的設(shè)置對(duì)“飲料店”進(jìn)行設(shè)置(5) 創(chuàng)建“應(yīng)收”和“找回”列的公式，算法如下：應(yīng)收=銷售額，找回=實(shí)收一應(yīng)收。STEP6一讓查找公式更完美(1) 隱藏列在“新銷售記錄”工作表中將不需要顯示的列(“單位”、“

19、進(jìn)價(jià)”、“銷售額”)隱藏起來。(2) 前面的結(jié)果有缺陷在上面應(yīng)用了查找函數(shù)VLOOKU的“銷售記錄”表中，如果把D3單元格中的飲料名稱刪去以后，可以看到F3、G3H3單元格(即：?jiǎn)挝弧⑹蹆r(jià)和進(jìn)價(jià))中均返回錯(cuò)誤值"#N/A”。大家可以試一下，當(dāng)VLOOKUP數(shù)在“飲料價(jià)格”表中沒有找到D3單元格中的飲料名稱時(shí)都要返回錯(cuò)誤值“#N/A”。能不能讓當(dāng)VLOOKUP數(shù)在“飲料信息”表中沒有找到D3單元格中的飲料名稱時(shí)不返回錯(cuò)誤值"#N/A”，而只什么都不顯示(即顯示空格)呢？問題：利用函數(shù)和iserroR輸入飲料名稱時(shí)，不顯示#VALUE!(2)解決辦法利用IF和ISERROf&#

20、174;數(shù)可以解決上面的問題。試試看，你能解決這個(gè)問題嗎？上面的問題變成兩種情況：如果當(dāng)“VLOOKUP(D3,ylmc,2,FALSE)'部分返回錯(cuò)誤值的話，則在F3中顯示空字符串。如果“VLOOKUP(D3,ylmc,2,FALSE)'部分工作正常，則在F3單元格中顯示飲料對(duì)應(yīng)的“售價(jià)”(即VLOOKUP(D3,ylmc,2,FALSE的值)。溫馨提示:IF(ISERROR(VALU&一,VALUESTEP7一凍結(jié)窗口保留標(biāo)題行在“銷售記錄”工作表中，當(dāng)銷售記錄增多后，為了保留標(biāo)題行的內(nèi)容，可單擊A1單元格，執(zhí)行“窗口”T“凍結(jié)窗口”命令，當(dāng)滾動(dòng)屏幕時(shí)，可以把第一行

21、(即表頭行)的內(nèi)容一直保留在窗口中。4. 重點(diǎn)難點(diǎn)重點(diǎn)VLOOKUP函數(shù)的使用。區(qū)域名稱的定義分類匯總數(shù)據(jù)透視表難點(diǎn)VLOOKUP函數(shù)的參數(shù)的選擇數(shù)據(jù)透視表5. 案例總結(jié)與常見問題本案例通過對(duì)學(xué)生信息表數(shù)據(jù)的處理，介紹了查找與引用類函數(shù)VLOOKUP的用法及分類匯總和數(shù)據(jù)透視表的用法。(一) 你知道了嗎？通過本案例的學(xué)習(xí)，你能回答如下問題嗎？1. VLOOKUP數(shù)是干什么的？用VLOOKU®數(shù)進(jìn)行查找時(shí)，所要查找的元素為什么一定要在所定義的數(shù)據(jù)區(qū)域的第一列？VLOOKU函數(shù)中第二個(gè)參數(shù)的含義是什么？在定義第二個(gè)參數(shù)時(shí)要注意什么？在對(duì)VLOOKPUB數(shù)沿列進(jìn)行復(fù)制時(shí)，一般情況下，為什么

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