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

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

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

3、單位”、“進價”和“售價”等信 息，并計算出工作表中的“銷售額”和“毛利潤”等信息。蝕2用“分類匯總”統(tǒng)計出各連鎖店和各個區(qū)中各種飲料的“銷售額”、“毛利潤”。螇3用“數(shù)據(jù)透視表”分析各個區(qū)中每種飲料的銷售情況和各個區(qū)中銷售情況最好的飲料。/" /I I f W I I/ , 711 |' / |' |莄4另外，為了提高效率、避免出錯，小李還想制作一張可以從下拉列表中選擇飲料名稱， 并能自動計算出顧客應(yīng)交款及應(yīng)找回款的“新銷售記錄”工作表。X /4 I X、. /y I J / y-肂4.制作過程荿STEPVLOOKU函數(shù)的使用XJ,X-Vl 'J八.*/ &

4、#39;|! j.j 、* i螇設(shè)計目標螅 參照下圖，根據(jù)“銷售記錄”表中的 “飲料名稱”列，利用VLOOKU函數(shù)在“飲料基本信息” 表中查找其他列（單位、進價和售價）的值。袃飲料名稱単位進價售價1可口可樂聽k 702. 102百事可樂聽1. 602. 003可口可樂（1L）瓶2. lu2. 604百事可樂1L）瓶2. 00r 2. 505雪碧聽k 702. 206雪碧（1L）瓶2. 00£ 507七喜聽1. 702. 20 8七喜（1L）瓶2. 002. 60g匯源果汁合J 80r 9. 8010美年迖1. 702. 2011芬達聽1. 602. 0012醒目聽2. 002. 50

5、13健力寶聽1. 702. 2014王老吉合L 702. 20日擁籀在區(qū)飲料店售賢 常怦緬 毛利潤1. 9腿根據(jù)以上兩個表中的數(shù)據(jù)得到下圖中的結(jié)果K2007-1-1南山西麗厲統(tǒng)一奶茶702007-1-1南山西麗店紅牛782007-1-1南山西麗珞茨蘿啤162007-L-l南山西麗店非常可樂82007-1-1南山西麗底百事可樂（1L）592007-3-1南山西麗店娃哈哈果奶232007-1-1南山西麗店葡萄汁862007-L-l南山西麗店雪碧482007-1-1南山西麗店王老吉172007-L-l南山西麗店怡寶純浄水G22007-1-1南山西麗岳272007-1-1南山西麗店果粒橙181. 21

6、, Q21- 44. 51, 71. 7山922- 42007-W2007-L-12007-W2007-L-12007-W2帥卜1-12007-ItI2財十】2007-1-12QQT-1-12007-11西麗珞 可口可樂（1L） 西麗店統(tǒng)一録茶西麗店 蛙哈哈純浄水 西麗店 樂百氏純待水 西麗店 七喜西西店 七喜仃L）西麗珞 可口可樂 西麗店 醍目西麗店 旭日升冰紅荼 西麗店 樂百氏果奶 西麗店 芬27946S36349582872171922. 1L 82.44.2L. 622. 5L862.22. 2L22. 632. 62.3232615272 * 112222212007-1-12007

7、-1-12007-1-12007-1-12的十】2007-1-12財十】2007-1-12Q(J 卜 1T2007-1-12007-1-12007-3-12007-1-12007-1-12007-1-12007-1-12007-1-12財十】2007-1-12007-1-12007-1-1西麗厲統(tǒng)一般茶西麗店 紅牛 西麗馬 茨蘿啤西麗店非常可樂西麗店百事可樂g西麗店 娃哈哈果奶西麗店 葡萄汁 西麗店 雪碧 西麗店 王老吉 西麗店怡寶熨浄水西麗店 脈動 西麗店 果粒橙西呼曲麗店西呼西西店西匹店西西店西匹店西西店西麗匡707S1685923昕3* 2聽1. 2聽1, 621. 92.4J. 622

8、. 586 臺_4. 564E 昕1,T2. 21T 臺1. 72. 262 XK山9L227無皂2. 613 XC2.4327 JE94瓶蛙哈哈純洋水56樂百氏純浄水S3七喜76七喜皿）23可口可樂14醒目79旭日升冰紅荼15樂百氏果奶98芬達可口可樂口口統(tǒng)一綠茶2. 11. 80. 92. 62.3L.2聽1. 72. 22N 61. 72. 1聽2Z 5JK1. 72. 2龜1. 3L7聽1. 62芁（1） VLOOKUP函數(shù)是干什么用的膀VLOOKU函數(shù)的功能:168. 0035.00327.6078. DO25. 606.4016. 003. 20147. 5029.5041.40P

9、 20516.00129.00IDE. 6Q扯U037.408.5074. 4D18.6070. 2016. 2C54 0D10.0070. 2013. 5C216.2047.0067.2016.80107. 9024.90167.2033.00E9.8013.8029.405.60197. GQ39. 5C33. DD7.5010- 6039. 2044. 008.80羇查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回數(shù)據(jù)區(qū)域當(dāng)前行中指定列處的值。節(jié)VLOOKU的語法:芆要查找的對象（參數(shù) ）一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù) ）的第一列。蒅下面來看看 VLOOKU函數(shù)是怎么用的。蟻（2）如何查找“單位”

10、？薀打開文件“飲料銷售（素材）.xls ”，將文件另存為“姓名_飲料銷售.xls莆在“銷售記錄”工作表中，選中F3單元格一選擇“插入-函數(shù)一 VLOOKLTP-單擊“確祎由于要根據(jù)飲料的名稱 查找“單位”，所以 VLOOKU函數(shù)的第一個參數(shù)應(yīng)該選擇飲料 名稱“ D3'。莃在“ Table_array ”區(qū)域中選擇“飲料價格”工作表中的B2:E44 I艿由于“單位”數(shù)據(jù)存放在“飲料信息”數(shù)據(jù)區(qū)域的第2列，所以輸入數(shù)字“ 2”莆由于要求飲料名稱精確匹配，所以最后一個參數(shù)輸入“FALSE肅單擊“確定”，可以看到函數(shù)準確地返回了 “統(tǒng)一奶茶”的單位“瓶”螀復(fù)制公式，出現(xiàn)了什么問題？ ？為什么？

11、如何解決呢?肇提示：注意在復(fù)制公式時，如果沿著列拖動時，列標要用絕對引用，想想看為什么?蒆修改公式后，重新復(fù)制公式蒄（3）創(chuàng)建區(qū)域名稱蒂選擇“飲料名稱”工作表肁選中飲料名稱、單位、進價、售價所在的區(qū)域，即單元格區(qū)域B2:E44薆在名稱框中輸入“自己的姓名+ A”，一定要按回車確認螞（1）區(qū)域的定義可以包含“序號”列嗎？為什么?芅思考問題：蚈（2）如果名稱定義錯誤了，如何將其刪除。螆(4 )利用區(qū)域名查找“進價”和“售價”蚆選擇“銷售記錄”工作表一選中G3單元格一輸入等號“=” 一選擇函數(shù)“ VLOOKUP-單擊 “確定”。I膀由于要根據(jù) 飲料的名稱查找“售價”，所以 VLOOKU函數(shù)的第一個參

12、數(shù)應(yīng)該選擇飲料名稱 “D3'。IjrC/ 丿 ' j - - - _ «!/八蟻在“ Table_array ”區(qū)域中輸入“姓名A”。裊由于“進價”數(shù)據(jù)存放在第 3列，所以輸入數(shù)字“ 3”。'、丿 1螃由于要求飲料名稱精確匹配，所以最后一個參數(shù)輸入“false.；* 11莄單擊“確定”，可以看到函數(shù)準確地返回了“統(tǒng)一奶茶”的“進價”數(shù)據(jù)“ 1.9 ”。莇復(fù)制公式，看一看有什么不同？ ？袂怎么樣，對于VLOOKU函數(shù)你是否有了一定的認識？如果還蚆同樣道理在H3單元格中建立查找飲料名稱“售價”的公式。(5)計算銷售額、毛利潤蕆 溫馨提示:STEP2-分類匯總“銷售

13、記錄蒈首先，建立表“銷售記錄”的二個副本“銷售記錄”、“銷售記錄”和膅肁結(jié)果參見樣例中的工作表 “ 所在區(qū)匯總?。? ）按照“所在區(qū)”進行分類匯總蚃在表“銷售記錄”中按“所在區(qū)”對銷售額和毛利潤進行分類匯總，匯總結(jié)果顯示 在數(shù)據(jù)下方（結(jié)果見“（樣例）飲料銷售.Xis ”）。肀將銷售 記錄改名 為“所在區(qū)匯 總”(2)按照“飲料名稱”進行分類匯總袃在表“銷售記錄”中按“飲料名稱”對 銷售額和毛利潤進行分類匯總(匯總方式為求和)肅對匯總結(jié)果中的毛利潤列按 降序排序，找出毛利潤最大的飲料膀?qū)N售記錄 改名為“飲料名稱匯總”螇結(jié)果參見樣例中的工作表一一“飲料名稱匯總”羃（3）用 嵌套分類匯總”統(tǒng)計各個

14、區(qū)和各飲料店的飲料 銷售額”和 毛利潤薁在“銷售記錄”工作表中對“主要關(guān)鍵字”選擇“所在區(qū)”，“次要關(guān)鍵字”選擇“飲料店”排序。莀進行第一次“分類匯總”（分類字段為“所在區(qū)”）蒞進行第二次“分類匯總”（分類字段為“飲料店”）螅將銷售記錄 改名為“嵌套匯總”蒀STEP3-利用數(shù)據(jù)透視表實現(xiàn)統(tǒng)計分析腿設(shè)計目標襖如下圖所示，利用數(shù)據(jù)透視表生成三維數(shù)據(jù)統(tǒng)計報表。求和項;銷售頷 憂料名稱 匯源果汁葡萄汁蘋果汁薀利用排序， 找出銷售額 最大的飲料I關(guān)1215.2198S.41783. 610S7.81773(8oTB491-10861E361830117615i671!4iooe.81018.81315,

15、61877,21055671't；1125.61558.2760.21230.6244867123.)790.4434. 26372056.668644604.687314766S45289rS45；E960582111995771043：8356. 567L 62509* 355222774317.150S. 2530. S1408759g1241!M832530.4976642. 27775“ri * - 南山總計3726,8357035363450所在區(qū)日_福田1370.67693636. 3852* 6684. 6703,6813. 81258.4444. 6657. 8361

16、.4醒目 v960690640755405百事可樂（IL）542. 5850785737. 5502. 5旭日升冰紅茶774.4737294. B673. 2662. 2僅力寶644. 6134. SB94E34. 61108. 3鮮橙多55555276S309849伊利純牛奶530. 1533.9915,8638.4345.8481.8763.4400.4710.6賺3417. 53141.53066.530332964薇（1）為“銷售記錄”表創(chuàng)建數(shù)據(jù)透視表莂要求:羀為表“銷售記錄”建立數(shù)據(jù)透視表，將數(shù)據(jù)透視表顯示在新工作表中，并將數(shù)據(jù)透視表命名為“銷售統(tǒng)計”蝕行顯示“飲料名稱”，列顯示“所

17、在區(qū)”，“銷售額”顯示在數(shù)據(jù)區(qū)。膆 結(jié)果參見樣例中的“銷售統(tǒng)計”工作表。完成蚆(2)根據(jù)數(shù)據(jù)透視表找出銷售額最大的飲料羃對毛利潤按降序排序，找出銷售額最大的三種飲料莁結(jié)果參見樣例中的“銷售統(tǒng)計”工作表蚃（3）在“銷售統(tǒng)計”工作表中，找出各個區(qū)“銷售額”最大的飲料蝿在“銷售統(tǒng)計”工作表中，用 MAX函數(shù)找出每個區(qū)“銷售額”最大的飲料的“銷售額”。I I螈在“銷售統(tǒng)計”工作表中，用 VLOOKUI®數(shù)找出各區(qū)“最大銷售額”所對應(yīng)的“飲料 名稱”。I / .產(chǎn)/ / I I1 '/ / ' hi I ; i' J i蒅STEP用“兩軸線-柱圖”比較“銷售額”和“毛利

18、潤”dd F/ / .'）X / I x' /y I , / 才袀設(shè)計目標薁現(xiàn)在，小李想用“兩軸線-柱圖”比較“南山區(qū)”、“福田區(qū)”和“羅湖區(qū)”的 銷售額和毛利 潤之間的關(guān)系。-Vv J八.'i 'iI! j.j 'x * i,j I蕆如下圖所示，用兩軸線柱圖表來比較銷售額和毛利潤：（1）制作“兩軸線圖”芁在表“所在區(qū)匯總”分別選擇“所在區(qū)”、“銷售額”和“毛利潤”三列（如下圖）所示；罿單擊“圖表向?qū)А卑粹o，打開“圖表向?qū)?-4-圖表類型”對話框，選擇“自定義類型”選項 卡，選擇“兩軸線-柱圖”。I I芆單擊“下一步”，打開“圖表向?qū)?4-圖表源數(shù)據(jù)”對

19、話框；I / I I1 " 廠/I I / n/<I /./ rI |蚄單擊“下一步”，打開“圖表向?qū)?4-圖表選項”對話框，按下圖進行設(shè)置；dd F/ / .'）螞單擊“下一步”，在“圖表位置”對話框中，選擇“作為其中的對象插入”，單擊“完成”按鈕。1.1蟻（2）美化圖表-Vv J八.'i 'iI! j.j 'x * i,j I肅參照“飲料銷售（樣例）.xls ”，完成對圖表格式的設(shè)置（格式可以自定義）。螄STEP5-利用數(shù)據(jù)有效性制作更方便、更實用的“新銷售記錄”工作表肅設(shè)計目標腿在“飲料銷售.Xis ”中制作一張“新銷售記錄表”，并應(yīng)用數(shù)據(jù)

20、有效性設(shè)置，使得在填寫了 銷售“數(shù)量”和選取了“飲料名稱”后，可以自動計算出“銷售額”、“毛利潤”和“毛利率”。I I肇（1）制作“銷售記錄”表副本I / . / /廣襖在“飲料銷售.Xis ”中建立一個“銷售記錄”表的副本，并將其重命名為“新銷售記錄”，然后將其前五列的內(nèi)容刪除（只保留標題行），如下圖所示。匸/ /'. : iX / 嚴 I X'、X |： / y 膀并增加3個新列“實收”、“應(yīng)收”和“找回”袁（2）對“飲料名稱”應(yīng)用數(shù)據(jù)有效性設(shè)置袇選中“飲料價格”表中的“飲料名稱”區(qū)域，并將其定義為“飲料名稱”，如下圖所示。I I羄選中“新銷售記錄”的第 4列（“飲料名稱”

21、列），然后再選擇“數(shù)據(jù)”-“有效性”打 開數(shù)據(jù)有效性對話框。II I. - -薁在有效性條件中選擇“序列”。莈在“來源”中填寫“=飲料名稱”（注意：“飲料名稱”是定義的“飲料名稱”區(qū)域），如 圖下所示。LJ薆（3）數(shù)據(jù)有效性的使用-Vv ' J*八.'i 'i f- J肄制作完成，如下圖所示，試試看，是不是在填寫了銷售“數(shù)量”和選取了“飲料名稱”后， 可以自動計算出“銷售額”、“毛利潤”和“毛利率”，很方便吧！羂 （4 ）對“飲料店”列進行數(shù)據(jù)有效性設(shè)置肀參照“飲料名稱”的設(shè)置對“飲料店”進行設(shè)置蚈（5）創(chuàng)建“應(yīng)收”和“找回”列的公式，算法如下：應(yīng)收 =銷售額，找回=實

22、收一應(yīng)收STEP6-讓查找公式更完美莂（1）隱藏列蒀在“新銷售記錄”工作表中將不需要顯示的列（“單位”、“進價”、“銷售額”）隱藏起來。.I IWl荿（2）前面的結(jié)果有缺陷 I1 l Xt JVJ .丿八_ - z- . * / I r iX-' I .* I I I膆在上面應(yīng)用了查找函數(shù) VLOOKU的“銷售記錄”表中，如果把 D3單元格中的飲料名稱刪去以后，可以看到F3、G3 H3單元格（即：單位、售價和進價）中均返回錯誤值“#N/A”。f J I% . kf f螅大家可以試一下，當(dāng)VLOOKU函數(shù)在“飲料價格”表中沒有找到D3單元格中的飲料名稱時都要返回錯誤值“ #N/A”。膂能

23、不能讓當(dāng)VLOOKU函數(shù)在“飲料信息”表中沒有找到D3單元格中的飲料名稱時不返回錯 誤值“ #N/A”，而只什么都不顯示（即顯示空格）呢？袂（2）解決辦法蝕 利用IF和ISERROF函數(shù)可以解決上面的問題。試試看，你能解決這個問題嗎?袇上面的問題變成兩種情況:蒞如果當(dāng)“ VLOOKUP（D3,ylmc,2,FALSE”部分返回錯誤值的話，則在 F3中顯示空字符串。芃如果“ VLOOKUP（D3,ylmc,2,FALSE”部分工作正常，則在 F3單元格中顯示飲料對 應(yīng)的“售價”（即 VLOOKUP（D3,ylmc,2,FALSE 的值）。STEP7凍結(jié)窗口保留標題行肅在“銷售記錄”工作表中，當(dāng)銷

24、售記錄增多后，為了保留標題行的內(nèi)容，可單擊A1單元格,執(zhí)行“窗口凍結(jié)窗口”命令，當(dāng)滾動屏幕時，可以把第一行(即表頭行)的內(nèi)容一直保留I I在窗口中。I / 盧/廣薂4.重點難點(1)(2) 蒈重點X / I x' . !X |； / y 薅VLOOKUP函數(shù)的使用。LJ蒆區(qū)域名稱的定義 'J八.'l 'i "fj! j.j 、* i I I罿分類匯總薁數(shù)據(jù)透視表(3)(3) 蚅難點薂VLOOKUP函數(shù)的參數(shù)的選擇蟻數(shù)據(jù)透視表5. 案例總結(jié)與常見問題芇本案例通過對學(xué)生信息表數(shù)據(jù)的處理，介紹了查找與引用類函數(shù)VLOOKU的用法及分類匯總 和數(shù)據(jù)透視表的用法

25、。蒅（一）你知道了嗎？裊通過本案例的學(xué)習(xí)，你能回答如下問題嗎？I I薀1. VLOOKU函數(shù)是干什么的？« L一I / .產(chǎn)/廣/" n I J * I I I»'yI .*i I莆2.用VLOOKU函數(shù)進行查找時，所要查找的元素為什么一定要在所定義的數(shù) 據(jù)區(qū)域的第一列？X /I x' /y I y y祎3. VLOOKUF函數(shù)中第二個參數(shù)的含義是什么？在定義第二個參數(shù)時要注意什么？ I LlX, -、r* 、/ 'i ! j.j 、* i I I莃4.在對VLOOKP函數(shù)沿列進行復(fù)制時，一般情況下，為什么列標要用絕對地址？艿5.如何定義數(shù)

26、據(jù)區(qū)域，如何刪除數(shù)據(jù)區(qū)域?莆6.用VLOOKP函數(shù)時，可以不定義數(shù)據(jù)區(qū)域嗎？芇7.什么是分類匯總？它有什么作用?肅8.在分類匯總之前要注意什么?莂（二）常見問題及處理方法蒆下面把大家在本案例的學(xué)習(xí)過程中容易遇到的一些問題及處理方法列于下表:蒄常見問題蒃可能原因肁處理方法1 薆“數(shù)據(jù)區(qū)域”名 稱無法重新定義。裊該“數(shù)據(jù)區(qū)域”名稱已被 定義。 1 -X 41. #芅將其刪除，重新定義， 方法：二一一袀選擇“插入”-“名稱” 一“定義”，打開“定義 名稱”對話框，選擇已定 義的名稱，單擊“刪除” 命令。羀VLOOKU函數(shù)返 回錯誤值“ #N/A”。、二-,1芆（1）沒有把要查找的對 象定義在“數(shù)據(jù)區(qū)域”的 第一列。螞重新定義“數(shù)據(jù)區(qū)域”， 把要查找的內(nèi)容定義在“數(shù)據(jù)區(qū)域”的第一列。i袃（2）查找的內(nèi)容在定義 的“數(shù)據(jù)區(qū)域”中不存在。羀用ISERROF函數(shù)使錯誤 值不顯示。蚆使用“填充柄” 沿列拖動復(fù)制公 式時出現(xiàn)錯誤值“ #N/A”。莄要查找的對象（VLOOKU 函數(shù)的第一個參數(shù)）列標 沒有用絕對引用。P 義在使用“填充柄”沿列 拖動前先將