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

1、EXCELS訓(xùn)練習(xí)三商場(chǎng)銷(xiāo)售數(shù)據(jù)的分析處理1. 問(wèn)題的提出Excel工作表來(lái)小李在深圳市開(kāi)了若干家飲料連鎖店，為了提高管理水平，他打算用 管理銷(xiāo)售數(shù)據(jù)。下圖是他制作的各飲料店的銷(xiāo)售記錄流水帳表。山 IbIcI d IeIfgIh iIj鈉售記錄表廿期2007-1-L891011121314151617182007-1-1 2DQ7-1-L2007-1-12007-1-12007-1-12007-1-12007-1-12001-1-12007-1-120D7-1-1 2007-! 2007-1-1 2007-1-1山山山山山山山西西西西西16非?？蓸?lè)8百事可樂(lè)（IL）59娃哈哈果奶23藺知十86

2、雪碧4817622718 £79456 S3王老吉 恰寶昴機(jī) 昧動(dòng) 果粒橙 可口可樂(lè)（1L） 統(tǒng)翊 娃哈哈窺凈水 樂(lè)百氏翊凈水族科名稱(chēng)皴童單位進(jìn)價(jià)售偷銷(xiāo)售糧毛利潤(rùn)統(tǒng)一奶茶10紅牛18序號(hào)飲料名稱(chēng)單位進(jìn)價(jià)售價(jià)1可口可樂(lè)聽(tīng)1.7。2. 102百事可樂(lè)聽(tīng)L602. 00_3可口可樂(lè)（1L）ffi2. 102. 604百事可樂(lè)（1L）瓶2.002, 50E聽(tīng)LTD2.206雪碧（1L）瓶2- 002. 507七喜聽(tīng)1.?02, 203七喜UL）瓶2*002. 609匯源果汁7. 809. 8010美年達(dá)聽(tīng)L702, 2011芬達(dá)聽(tīng)1.60N 0012醒目聽(tīng)2. 002. 5013健力寶聽(tīng)L7

3、02. 2014王老吉1.702. 2015鮮橙多2.如3. 00鮮桃多瓶2.403. 0017統(tǒng)一奶茶瓶L902,4018統(tǒng)一冰紅茶瓶L802. 3019統(tǒng)一綠茶瓶L802. 3020旭日升冰紅茶瓶L702, 20為了統(tǒng)計(jì)“毛利潤(rùn)”，他必須去“飲料基本信息”表中查找每種飲料的“進(jìn)價(jià)”和“售 價(jià)”。這個(gè)工作量實(shí)在太大，而且還容易出錯(cuò)?，F(xiàn)在希望：能否輸入飲料名稱(chēng)后，讓Excel根據(jù)這個(gè)名稱(chēng)自動(dòng)去查找該飲料的 "單位"、 “進(jìn)價(jià)”、“售價(jià)”等信息，并存放到表“銷(xiāo)售記錄”的相應(yīng)列中。2. 解決方案通常情況下，如果不借助其它方法的幫助， 要想在Excel中解決這個(gè)問(wèn)題，只能到“飲

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

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

6、銷(xiāo)售額 毛利洞2007-1-1 南山2007-1-1 南山2007-1-1 南山2007-1-1 南山2007-1-120071-12007-1-1山山山南南南西皿店 西皿店 西皿店 西皿店西皿店 西麗店統(tǒng)一奶茶紅牛菠蘿啤非?？蓸?lè)百事可樂(lè)（1L）娃哈哈果奶707S1685923862007-1-12007-1-12007-1-1山山山南南南2007-1-1 南山2007-1-1 南山西皿店西四店 西皿店 西皿店 西麗店雪碧王老吉怡寶純凈水脈動(dòng)果粒橙43176227182007-1-1 南山2007-1-1 南山2007-1-1 南山西皿店西皿店西麗店可口可樂(lè)（1L）27統(tǒng)一綠釜94娃哈哈純凈水

7、56序號(hào)飲料名稱(chēng)單位進(jìn)價(jià)售價(jià)1可可樂(lè)聽(tīng)1. 702. 102百事可樂(lè)聽(tīng)1. 602. 003可可樂(lè)（1L）瓶2. 102.604百事可樂(lè)（1L）瓶2. 002. 505雪碧聽(tīng)1. 702.206雪碧（1L）瓶2. 002. 507七喜聽(tīng)1. 702.208七喜（1L）瓶2. 002.609匯源果汁合7.809. 8010美年達(dá)聽(tīng)1. 702.2011芬達(dá)聽(tīng)1. 602.0012醒目聽(tīng)2. 002. 5013健力寶聽(tīng)1. 702. 2014王老吉合1. 702. 2C西麗居日期所在區(qū)佐料店佐料名稱(chēng)JI, m整待講價(jià)南山南山2007-1-12Q07-I-1西麗店根據(jù)以上兩個(gè)表 中的數(shù)據(jù)得到下 圖中

8、的結(jié)果銷(xiāo)售顫 毛利潤(rùn)2007-1-12007-3-12007-1-12007-1-12007-1-12007-L-12007-1-12007-L-12007-1-12QC7-L-12D07-1-12QC7-L-12007-1-12007-L-12007-1-12007-L-12007-1-12007-1-12007-1-12007-1-12007-1-1In西西麗店麗店西西麗 麗店店西 西麗 麗店 店西西麗店麗店西戕店 西典居 西四店 西皿唇 西四店 西皿店 西四店 西麗店菠蘿呷It非常可樂(lè)E百事可樂(lè)（1L）5E姓哈哈果奶2：葡萄汁8(717!統(tǒng)一奶茶 紅牛雪碧王老吉r怡寶純凈水賣(mài)脈動(dòng)2,果粒

9、橙可口可樂(lè)（1L）2r統(tǒng)一昇茶頂娃哈哈純凈水5t店店18 -RU-nH -nu西西樂(lè)百氏純凈水 七喜 七喜（1L） 可口可樂(lè) 喔目旭日升冰紅荼 樂(lè)百氐果奶 芬達(dá)8；7t2：1<7£1E9E幻3. 2L21. 21. 61. 6222.51.4L 84. 561. 72.21.2.20, 91, 222.62.432. 12.61,82.30. 9L211, 31. 72.222.61. 7J_2. 122.51. 7J_2.2L 31, 7.621. 92007-1-12007-1-12007-1-12M7TT2007-1-12。葉-172007-1-12Q07TT2007-1

10、-12DQ7TT 2007-1-12007-1-1山山山山山山'1J山'1J店店 西西店店-HU-B -R西西店店 日 一EZ2 -R -n 西西西西店店-R店店-B -n西西統(tǒng)一蠣茶70瓶1. 92.4紅牛78所3. 243菠蘿啤16所1. 21. 6非?？蓸?lè)8所1, 62百事可樂(lè)（1L）59規(guī)22. 5娃哈哈果奶23瓶1,41,8商鄢十86合4. 56雷碧48茄1, 72. 2王老吉17臺(tái)1. 72. 2怡寶藐凈水62£。9L 2脈動(dòng)27瓶22. 6果粒橙18W.4327瓶94瓶2007-1-12007-1-1西HI店頊口可樂(lè)（IL）西麗店統(tǒng)一綠茶2007-1-1

11、2DQTTT2007-1-12 皿 7TT2007-1-12皿7十】2007-1-12DQTT-】2007-1-1西皿店 西四店 西皿店 西四店 西萸腐 西皿店 西四腐 西皿店 西麗居娃哈哈純祥水 樂(lè)百氐純凈水 七喜 七喜（1L） 可口可樂(lè) 醒目旭日升冰紅茶 樂(lè)百氐果奶 芬達(dá)56S376231479159822瓶11.31. 72. 2瓶22M1. 72. 1聽(tīng)22. 5JK1. 72. 21. 3L7所1. 62168.00327.6025. 6016. 00147.5041.40516. 00 LO5. 60 37.40 74.407C. 2054. 007C. 20216. 2067.

12、20 107.90 167.20E9.8029.40197.5033. 00 Iflifi. SO44. OD35.0078. 006.401 2029.509. 20129.0024.008.5C18.6016. 2C10.8013.5047.0016.8024.9038.0013.805.fi039.607.5C39.208.80（1） VLOOKUP函數(shù)是干什么用的VLOOKUH數(shù)的功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回?cái)?shù)據(jù)區(qū)域當(dāng)前行中指 定列處的值。VLOOKUH 語(yǔ)法：VLOOKUP(lookup_value,table_array,col_index_num,range_lo

13、okup)注意：要查找的對(duì)象（參數(shù) ）一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù) ）的第一列。下面來(lái)看看 VLOOKUH數(shù)是怎么用的。(2) 如何查找“單位” ？打開(kāi)文件“飲料銷(xiāo)售(素材).xls”，將文件另存為“姓名_飲料銷(xiāo)售.xls”。在“銷(xiāo)售記錄”工作表中，選中F3單元格r選擇“插入r函數(shù)r VLOOKUP 單擊“確定”。由于要根據(jù)飲料的名稱(chēng)查找“單位”，所以 VLOOKUP函數(shù)的第一個(gè)參數(shù)應(yīng)該 選擇飲料名稱(chēng)“ D3” 。在“ Table_array ”區(qū)域中選擇“飲料價(jià)格”工作表中的 B2:E44。由于“單位”數(shù)據(jù)存放在“飲料信息”數(shù)據(jù)區(qū)域的第2列，所以輸入數(shù)字“ 2” 。由于要求飲料名稱(chēng)精確匹配

14、，所以最后一個(gè)參數(shù)輸入“ FALSE單擊“確定”，可以看到函數(shù)準(zhǔn)確地返回了 “統(tǒng)一奶茶”的單位“瓶”。復(fù)制公式，出現(xiàn)了什么問(wèn)題？ ？ ？為什么？如何解決呢？提示：注意在復(fù)制公式時(shí)，如果沿著列拖動(dòng)時(shí)，列標(biāo)要用絕對(duì)引用，想想看為什么？修改公式后，重新復(fù)制公式。(3) 創(chuàng)建區(qū)域名稱(chēng)選擇“飲料名稱(chēng)”工作表。選中飲料名稱(chēng)、單位、進(jìn)價(jià)、售價(jià)所在的區(qū)域，即單元格區(qū)域B2E44。在名稱(chēng)框中輸入“自己的姓名+A”，一定要按回車(chē)確認(rèn)。思考問(wèn)題:(1) 區(qū)域的定義可以包含“序號(hào)”列嗎？為什么?(2) 如果名稱(chēng)定義錯(cuò)誤了，如何將其刪除。(4) 利用區(qū)域名查找“進(jìn)價(jià)”和“售價(jià)”選擇“銷(xiāo)售記錄”工作表 選中G3單元格輸入

15、等號(hào)" =”選擇函數(shù)“ VLOOKUP ” 單擊“確定”。由于要根據(jù)飲料的名稱(chēng)查找“售價(jià)”，所以 VLOOKUP函數(shù)的第一個(gè)參數(shù)應(yīng)該選擇 飲料名稱(chēng)“ D3” 。在"Table_array ”區(qū)域中輸入"姓名A”。由于“進(jìn)價(jià)”數(shù)據(jù)存放在第3列，所以輸入數(shù)字“ 3” 。由于要求飲料名稱(chēng)精確匹配，所以最后一個(gè)參數(shù)輸入“FALSE”單擊“確定”，可以看到函數(shù)準(zhǔn)確地返回了 “統(tǒng)一奶茶”的“進(jìn)價(jià)”數(shù)據(jù)“1.9”。函敷參數(shù)vwoku?搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在區(qū)域中的行序號(hào)，再步返回選定單元格的值。默認(rèn)情況下，表是以升序排序的g"100kup指定

16、在查找時(shí)是要求精確匹配，還是大致匹配-如果為FALSE,大致匹配.如果為T(mén)RUE或忽略精確匹配計(jì)算結(jié)果二1.9有關(guān)該函數(shù)的幫助0()確定取消 復(fù)制公式，看一看有什么不同？ ？同樣道理在H3單元格中建立查找飲料名稱(chēng)“售價(jià)”的公式。(5) 計(jì)算銷(xiāo)售額、毛利潤(rùn)STEP 2 一分類(lèi)匯總首先，建立表“銷(xiāo)售記錄”的二個(gè)副本“ 銷(xiāo)售記錄（2）”、“銷(xiāo)售記錄（3）”和“銷(xiāo)售記 錄"。（1）按照“所在區(qū)”進(jìn)行分類(lèi)匯總在表“銷(xiāo)售記錄（2）”中按“所在區(qū)”對(duì)銷(xiāo)售額和毛利潤(rùn)進(jìn)行分類(lèi)匯總，匯總結(jié) 果顯示在數(shù)據(jù)下方（結(jié)果見(jiàn)“（樣例）飲料銷(xiāo)售.xls”）。將銷(xiāo)售記錄（2）改名為“所在區(qū)匯總”結(jié)果參見(jiàn)樣例中的工作表

17、一一“所在區(qū)匯總（2）按照“飲料名稱(chēng)”進(jìn)行分類(lèi)匯總在表“銷(xiāo)售記錄（3）”中按“飲料名稱(chēng)”對(duì) 銷(xiāo)售額和毛利潤(rùn)進(jìn)行分類(lèi)匯總（匯總方式為求和）對(duì)匯總結(jié)果中的毛利潤(rùn)列按降序排序，找出毛利潤(rùn)最大的飲料。將銷(xiāo)售記錄（3）改名為“飲料名稱(chēng)匯總”結(jié)果參見(jiàn)樣例中的工作表一一“飲料名稱(chēng)匯總用嵌套分類(lèi)匯總”統(tǒng)計(jì)各個(gè)區(qū)和各飲料店的飲料 銷(xiāo)售額”和毛利潤(rùn)在銷(xiāo)售記錄（4）”工作表中對(duì) 上要關(guān)鍵字”選擇所在區(qū)”，次要關(guān)鍵字”選擇飲 料店”排序。進(jìn)行第一次分類(lèi)匯總”（分類(lèi)字段為所在區(qū)”）。進(jìn)行第二次 分類(lèi)匯總”（分類(lèi)字段為 飲料店”）。將銷(xiāo)售記錄（4）改名為“嵌套匯總”結(jié)果參見(jiàn)樣例中的工作表“嵌套匯總”STEP 3 利用數(shù)據(jù)

18、透視表實(shí)現(xiàn)統(tǒng)計(jì)分析設(shè)計(jì)目標(biāo)如下圖所示，利用數(shù)據(jù)透視表生成三維數(shù)據(jù)統(tǒng)計(jì)報(bào)表。求和項(xiàng):銷(xiāo)售萩1所在反tLLLL福田11龍岡南山總計(jì)匯源果汁1215*1989.41783.6LOST.81773* 8仆紋-a葡萄汁108S16361831117616667194蘋(píng)果汁1006.81918.81315, 61877,21055,67176田排畝 此1125.61558.276C, 21230,62443, 67123,2刊J790.4434. 26372056.6686,44604,6出銷(xiāo)售額最873147668452897S4539大1的飲料96058211199577204338y VJ Y/vx

19、356* 567L 62509.3552227* 74317. 1七喜t508. 2530. S140B759902415S晾林1832530.4975642.2777,4J75T 1370,6481.8763,4400,4710,33726,8J/693636. 3852,6684. 6703.53570七吾HLj 1/813. 81258.4444. 6657.8361.43536醒目'9606906407554053450百事可樂(lè)(1L)E42. 5850785737*5502* 53417. 5旭日升冰紅茶774*4737294. S673*2662* 2314L 6健力寶64

20、4. 6134. 8E94534M1108.33066.8鮮橙多5555527683098493033伊利純牛奶530. 15319915,8638.4345.82964'寶壹 -福由趨崗羅湖南山總計(jì)百季可樂(lè)532462424385T44明48百事可樂(lè)UL)5d2. 5850785737.5502. 5317.5256508. S450.836Q25K61843.2非?？蓸?lè)440306228頓29616766424204305383202400光明純牛奶503. 2647,7678.3156.4683.42659光明酸奶475. 9376.2517.2744. S31T. 32462,

21、4果粒橙87314766 &45289784539t9363b. 3852.6684. 6703. S25701125. 61558.2760. 21230. 62448. 67123.2121E. 21989.41783. 610®7.B1773.87849. Sbd'l. 6184. S洶534, 61109. 83066.8S5G, 5663.65帥73(1. 34202874.9可口可親門(mén)L)790-4434.2637網(wǎng)5& &跖6,44604.6樂(lè)百氏純凈術(shù)23C. 1101.4322.4425. L243. 1322, 1樂(lè)百氏果奶525.

22、 3537,2397.8130.2443. 72084.2832530,497564?. 277T.43757美年達(dá)440367.2654.4393. B505.42450.8蒙牛純生場(chǎng)304400428314458J9&4將項(xiàng)目捕至數(shù)據(jù)透視表_區(qū)店名 顫« 期在料料量位價(jià)價(jià)售利 日所伏伏數(shù)單進(jìn)售銷(xiāo)毛 5-0目目目Bs§修加到1能域教誓透禪表宇段列表(1)為“銷(xiāo)售記錄”表創(chuàng)建數(shù)據(jù)透視表要求:為表“銷(xiāo)售記錄”建立數(shù)據(jù)透視表，將數(shù)據(jù)透視表顯示在新工作表中，并將數(shù)據(jù)透 視表命名為"銷(xiāo)售統(tǒng)計(jì)”行顯示“飲料名稱(chēng)”，列顯示“所在區(qū)”，“銷(xiāo)售額”顯示在數(shù)據(jù)區(qū)。結(jié)果參見(jiàn)樣

23、例中的“銷(xiāo)售統(tǒng)計(jì)”工作表。溫馨提示：選中“銷(xiāo)售記錄”工作表的任意單元格，選擇“ ,數(shù)據(jù)r據(jù)透視表和數(shù)據(jù)透單擊下一步”，選擇銷(xiāo)售數(shù)據(jù)所在區(qū)域；單擊 卜一步，打開(kāi)"數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)е?” ；單擊 臨局I按鈕，打開(kāi)“數(shù)據(jù)透視表和數(shù)據(jù)透視表視圖向?qū)б徊季?quot;對(duì)話框，數(shù)據(jù)透屈表和巍據(jù)誘得圖向?qū)ざ?2)根據(jù)數(shù)據(jù)透視表找出銷(xiāo)售額最大的飲料對(duì)毛利潤(rùn)按降序排序，找出銷(xiāo)售額最大的三種飲料。結(jié)果參見(jiàn)樣例中的“銷(xiāo)售統(tǒng)計(jì)”工作表(3) 在“銷(xiāo)售統(tǒng)計(jì)”工作表中，找出各個(gè)區(qū)“銷(xiāo)售額”最大的飲料在“銷(xiāo)售統(tǒng)計(jì)”工作表中，用MAX函數(shù)找出每個(gè)區(qū)“銷(xiāo)售額”最大的飲料的“銷(xiāo) 售額”。在“銷(xiāo)售統(tǒng)計(jì)”工

24、作表中，用 VLOOKUP函數(shù)找出各區(qū)“最大銷(xiāo)售額”所對(duì)應(yīng) 的“飲料名稱(chēng)”。STEP 4用“兩軸線-柱圖”比較“銷(xiāo)售額”和“毛利潤(rùn)”設(shè)計(jì)目標(biāo)現(xiàn)在，小李想用“兩軸線 -柱圖”比較“南山區(qū)”、“福田區(qū)”和“羅湖區(qū)”的 銷(xiāo)售額 和毛利潤(rùn)之間的關(guān)系。銷(xiāo)售額與毛利潤(rùn)關(guān)系圖福田匯總羅湖匯總南山匯總飲料店¥6,200 00) ¥6p00.00) ¥5.800 00)¥6.600 00)盤(pán)¥5.400 00)州¥5.200.00) ¥5,000.00)一銷(xiāo)售額毛利潤(rùn)如下圖所示，用兩軸線柱圖表來(lái)比較銷(xiāo)售額和毛利潤(rùn):¥28h000.0

25、0)¥27500.00)¥27,000.00)新 ¥26.500 00)地。那 Y26J00D D0)¥25,500 00)¥25 皿 00)¥24,500.00)（1）制作“兩軸線圖”在表“所在區(qū)匯總”分別選擇“所在區(qū)”、“銷(xiāo)售額”和“毛利潤(rùn)”三列（如下圖）安田所示;5522.205987. 5025488. 002762S. 0029934. 906542.90125547.90 15479.2027632. 106054.90136232.E0 29586.70單擊“圖表向?qū)А卑粹o，打開(kāi)“圖表向?qū)?4-圖表類(lèi)型”對(duì)話框，選擇“

26、自定義類(lèi)型選項(xiàng)卡，選擇“兩軸線 -柱圖”。網(wǎng)表向?qū)? a步薜N 1 -囹淺類(lèi)垂標(biāo)準(zhǔn)矣型日宸燼型 囹表纏©:示例.伊的裂的怵屈*國(guó)白湖圖 黑白面積圈.庶日折踐國(guó)TT間到售 H黑白柱形屈 .蠟里圖 0藍(lán)色桃囹 岫皿皿, TH I TlTropin wmnro25D00ffi5KDQO)nlnlnlnlJMnlJWnlnnlaysz嵩ZO自定史內(nèi)部但）典型的祀吉團(tuán)表，折我g形分別繪制 在西1坐標(biāo)珀上職消 下二步®_>1 |充隴舊單擊“下一步”，打開(kāi)“圖表向?qū)?4-圖表源數(shù)據(jù)”對(duì)話框;風(fēng)表向?qū)?2圖表湛數(shù)據(jù)171岡單擊“下一步”，打開(kāi)“圖表向?qū)?4-圖表選項(xiàng)”對(duì)話框，按下圖進(jìn)

27、行設(shè)置;圖表向?qū)?4步之3 -圖表透頂?lfx|標(biāo)題 坐標(biāo)軸網(wǎng)格戲 圖例數(shù)據(jù)標(biāo)志數(shù)據(jù)表圖表標(biāo)題（I）鎧售甑與利潤(rùn)關(guān)系分類(lèi)QO袖©:所在區(qū)數(shù)值（I）軸匹）:鎧售顫29000 BD 275D0H1 zroxiJii2KD0JXI2E000DD anni ooo JXI215：!：'.1：1：':IJjlJxUiiJ T iBFiS? ft 啟山 fl. jE匚作為新工作表插入堡）：作為其中的對(duì)象插入迫）： 取消 上一步值）|完成如取消 < 上一步魚(yú)）下一步但）> 完成堡）單擊“下一步”，在“圖表位置”對(duì)話框中，選擇“作為其中的對(duì)象插入”，單擊“完 成”按鈕。圖

28、表向?qū) 4步哀Z 4 -圖表位置01區(qū)IChartl（2）美化圖表參照“飲料銷(xiāo)售（樣例）.xls”，完成對(duì)圖表格式的設(shè)置（格式可以自定義）2E000 00納售額與利潤(rùn)關(guān)系27500.0027000.0026500 002GQ00 002S500 0025000.0024500.00褊田匯總南山匯總羅湖匯總 所在區(qū)STEP 5 利用數(shù)據(jù)有效性制作更方便、記錄”工作表設(shè)計(jì)目標(biāo)在“飲料銷(xiāo)售.xls”中制作一張“新銷(xiāo)售記錄表”6100.00600D 005900 005800.005700 005600.005600 005400 005300.005200 005100.00捎售額更實(shí)用的“新銷(xiāo)售

29、,并應(yīng)用數(shù)據(jù)有效性設(shè)置，使得在填寫(xiě)了銷(xiāo)售“數(shù)量”和選取了 “飲料名稱(chēng)”后，可以自動(dòng)計(jì)算出“銷(xiāo)售額”、“毛利潤(rùn)”和“毛利率”。日期星期I偵科名稱(chēng)I宮獸LL)七喜UL）匯謔果汁 美年達(dá)售價(jià)數(shù)量銷(xiāo)售額進(jìn)價(jià)毛利潤(rùn)毛利率¥2,002¥4, 00¥L6Q¥0. 3020. 00%54112（1）制作“銷(xiāo)售記錄”表副本在“飲料銷(xiāo)售.xls”中建立一個(gè)“銷(xiāo)售記錄”表的副本，并將其重命名為“新銷(xiāo)售記 錄”，然后將其前五列的內(nèi)容刪除（只保留標(biāo)題行），如下圖所示。N.-1偷A(chǔ) A A A N/n/N/N/iAAA N/HZH/# # #A A A A W/N/H/K- 花律系

30、#A A A A N/N/HZK- 花律節(jié)#A A A A AAA *M/# *盅A A A A A N/n/n/N/N/k. FFrF A A A A 財(cái)n/n/w * # #A A A A A 由財(cái)n/wwAAA N/N/N/ #+#AN/Bw HZ & W W w w IV w N/ w w w wA£N7&N/N/n/N/N/N/N/n/AAAiFrr草N/ N/ NX Jf/ N/N/ N/AAArrrSf#AAAt比rAr性A#«AAAF -Ft#AAArFFV- ¥AAAtAF-iN/ArN/At # #t tA A A A Ar F

31、 F rr掌*X/N/N/N/N/&AA A AFFF£A#N/N/N/N/N/N/N/n/N/AAA A At#3ww&K/N/AAAArFrrf# ftN/N/N/MX&AAAFFF-F-菖#AAAA3AftJf/Ar£N/AA A A A A并增加3個(gè)新列“實(shí)收”、“應(yīng)收”和“找回”（2）對(duì)“飲料名稱(chēng)”應(yīng)用數(shù)據(jù)有效性設(shè)置選中“飲料價(jià)格”表中的“飲料名稱(chēng)”區(qū)域，并將其定義為“飲料名稱(chēng)”，如下圖 所示。飲料名拔X - 我果粒橙1一BCDE1飲料價(jià)格表序號(hào)單位進(jìn)價(jià)售偷1可口可樂(lè)1.702. 1042百事可樂(lè)聽(tīng)L 602. 0053可口可樂(lè)（1L）瓶2

32、. 102. 6064百事可樂(lè)（1L）瓶2.002.5075弟？ j3白聽(tīng)1.702.2086雪碧（1L）瓶2. 002.5097七喜聽(tīng)L702.20108七喜UL）瓶2. 002. 60119匯源果汁合7. S09. SO1210美年達(dá)LTD2.201311芬達(dá)聽(tīng)1. SO2. 001412醒目聽(tīng)2.002. 501513健力寶聽(tīng)L702.20選中“新銷(xiāo)售記錄”的第4列（“飲料名稱(chēng)”列），然后再選擇“數(shù)據(jù)”“有效性”打開(kāi)數(shù)據(jù)有效性對(duì)話框。在有效性條件中選擇“序列”。在“來(lái)源”中填寫(xiě)“=飲料名稱(chēng)”（注意：“飲料名稱(chēng)”是定義的“飲料名稱(chēng)”區(qū)域），如圖下所示。(3) 數(shù)據(jù)有效性的使用制作完成，如下

33、圖所示，試試看，是不是在填寫(xiě)了銷(xiāo)售“數(shù)量”和選取了 “飲料名稱(chēng)”后，可以自動(dòng)計(jì)算出“銷(xiāo)售額”、“毛利潤(rùn)”和“毛利率”，很方便吧!日期七喜UL)匯譙果汁 美年達(dá)1售價(jià)1逾量1銷(xiāo)售額|進(jìn)價(jià)|毛利潤(rùn)|毛利率|¥2,002¥4, 00¥1.60 1¥0. 3020. 00%54112(4) 對(duì)“飲料店”列進(jìn)行數(shù)據(jù)有效性設(shè)置參照“飲料名稱(chēng)”的設(shè)置對(duì)“飲料店”進(jìn)行設(shè)置(5) 創(chuàng)建“應(yīng)收”和“找回”列的公式，算法如下：應(yīng)收 =銷(xiāo)售額，找回=實(shí) 收應(yīng)收。mJSTEP 6 一讓查找公式更完美(1) 隱藏列在“新銷(xiāo)售記錄”工作表中將不需要顯示的列( 單位”、進(jìn)價(jià)”、銷(xiāo)售額”

34、)隱藏起 來(lái)。(2) 前面的結(jié)果有缺陷在上面應(yīng)用了查找函數(shù) VLOOKUP的“銷(xiāo)售記錄”表中，如果把 D3單元格中的飲 料名稱(chēng)刪去以后，可以看到F3、G3、H3單元格(即：?jiǎn)挝?、售價(jià)和進(jìn)價(jià))中均返回錯(cuò)誤值 “ #N/A ” 。大家可以試一下，當(dāng) VLOOKUP函數(shù)在“飲料價(jià)格”表中沒(méi)有找到D3單元格中的歐料名稱(chēng)裁量單位 遂價(jià)日期 所在區(qū)我料店2007-11-23AH/WAR# # #A價(jià)菩1/AI/A倒I/AAN/AAAA N/N/晾 a # #aWALI飲料名稱(chēng)時(shí)都要返回錯(cuò)誤值" #N/A能不能讓當(dāng) VLOOKUP函數(shù)在“飲料信息”表中沒(méi)有找到D3單元格中的飲料名稱(chēng)時(shí)不返回錯(cuò)誤值&

35、quot; #N/A”，而只什么都不顯示(即顯示空格)呢?(2)解決辦法利用IF和ISERROR函數(shù)可以解決上面的問(wèn)題。試試看，你能解決這個(gè)問(wèn)題嗎？上面的問(wèn)題變成兩種情況：如果當(dāng)“ VLOOKUP(D3,ylmc,2,FALSE) ”部分返回錯(cuò)誤值的話，則在 F3中顯示空字符串。如果“ VLOOKUP(D3,ylmc,2,FALSE)”部分工作正常，則在 F3單元格中顯示飲料對(duì)應(yīng)的“售價(jià)”(即 VLOOKUP(D3,ylmc,2,FALSE) 的值)。STEP 7 一凍結(jié)窗口保留標(biāo)題行在“銷(xiāo)售記錄”工作表中，當(dāng)銷(xiāo)售記錄增多后，為了保留標(biāo)題行的內(nèi)容，可單擊A1單元格，執(zhí)行“窗口” “凍結(jié)窗口”命

36、令，當(dāng)滾動(dòng)屏幕時(shí)，可以把第一行（即表頭行） 的內(nèi)容一直保留在窗口中。4. 重點(diǎn)難點(diǎn)（1）重點(diǎn)VLOOKUP函數(shù)的使用。區(qū)域名稱(chēng)的定義分類(lèi)匯總數(shù)據(jù)透視表（2）難點(diǎn)VLOOKUP函數(shù)的參數(shù)的選擇數(shù)據(jù)透視表5. 案例總結(jié)與常見(jiàn)問(wèn)題本案例通過(guò)對(duì)學(xué)生信息表數(shù)據(jù)的處理，介紹了查找與引用類(lèi)函數(shù) VLOOKUP的用法及分類(lèi)匯總和數(shù)據(jù)透視表的用法。（一）你知道了嗎？通過(guò)本案例的學(xué)習(xí)，你能回答如下問(wèn)題嗎？1. VLOOKU函數(shù)是干什么的？2. 用VLOOKUP數(shù)進(jìn)行查找時(shí)，所要查找的元素為什么一定要在 所定義的數(shù)據(jù)區(qū)域的第一列？3. VLOOKU函數(shù)中第二個(gè)參數(shù)的含義是什么？在定義第二個(gè)參數(shù) 時(shí)要注意什么？4. 在對(duì)VLOOKPU數(shù)沿列進(jìn)行復(fù)制時(shí)，一般情況下，為什么列標(biāo)要用絕對(duì)地址？5. 如何定義數(shù)據(jù)區(qū)域，如何刪除數(shù)據(jù)區(qū)域？6. 用VLOOKPU數(shù)時(shí)，可以不定義數(shù)據(jù)區(qū)域嗎?7. 什么是分類(lèi)匯總？它有什么作用？8. 在分類(lèi)匯總之前要注意什么?(二)常見(jiàn)問(wèn)題及處理方法卜面把大家在