excel销售数据分析-实训练习

1、EX CEL实训练习三商场销售数据得分析处理1、问题得提由小李在深圳市开了若干家饮料连锁店，为了提高管理水平，她打算用Ex cel工作表来管理销售数据。所在区政科店日期1卜图就是她制作得各饮料店得销售记录流水帐表。tt料名秫魏里 单位 进价 售价 播售酒 毛利润10111213141KIC1718200T-1-12CJJ7-L12007-1-12007-1-1200T-1-1之 o(r-ii 200,-1-1 zuir-i-i200T-1-12UU7-112001-1-1店店店店店店 DE F HE 口 T T T R II日凡fnf1二茶io红牛1E装萝啤ie非常可乐R百事可乐QL）59握哈

2、哈臬划23苗萄汁564g南山西丽店南山百丽店2001-1-1200,-1-12OD7-1-1 舸山王:&吉 怡宝电净水 挑动 果粒也可口可乐。口 第一缥菜坤口向哈临务攻 乐口氐岫争死17 空 Li 16 27后号饮科名徐单位选阶售价11可口可乐所1. 702102白事口1乐听1*602.叩3可口可乐（1L）瓶2102.604白事引乐（1L）瓶2.002.505噌白 =1 -1!=诉1, 702,20行9eC1D2. 002.507七善听k 702.20S七喜（1L）瓶2. 002。3汇源果汁4 7. 305, S010美年达听1. ?ioj2.2011芬达听1.6020012醒目听2. 003

3、ECIr 13健力宝听1. 70 J2.20 -14王老吉.吕1, 702.2015鲜橙多瓶2. 403.0016鲜桃多瓶2*403.0017统一物茶瓶1. 9。2.4018统一冰红茶瓶k 802. 3019统一球茶瓶1. ao2.3U2 口旭日升冰红素瓶1. 702.20Ffi 也J为了统计“毛利润”，她必须去“饮料基本信息”表中查找每种饮料得“进价”与“售价”。这个工作量实在太大，而且还容易出错。现在希望：能否输入饮料名称后，让Exce 1根据这个名称自动去查找该饮料得“单位”、“进价”、“售价”等信息，并存放到表“销售记录”得相应列中解决万案通常情况下，如果不借助其它方法得帮助，要想在E

4、x c el中解决这个问题，只能到“饮料基本信息”表中一条一条地查找各种饮料得“进价”与“售价”.如果不想这么做，您有什么更好得办法吗？这个实际需求，开发 Excel得工程师，已经为我们想到了。在 Excel中有一个函数，就就是专门为 解决这类问题设计得，这个函数就就是VL O OKUP。小李这个问题，可利用Excel中得查找函数 VLOOK UP来解决。它得功能就是，在数据区域得第一 列中查找指定得数值，并返回数据区域当前行中指定列处得数值。下面来瞧瞧应用VLOOK UP函数就是如何解决上述问题得。实现方法本案例要解决如下几个问题：.在“销售记录”工作表中用 VLOOKUP函数计算饮料得“单

5、位”、“进价”与“售价”等信息 ， 并计算出工作表中得“销售额”与“毛利润”等信息。2。用分类汇总”统计出各连锁店与各个区中各种饮料得“销售额”、“毛利润”。3。用“数据透视表”分析各个区中每种饮料得销售情况与各个区中销售情况最好得饮料4。另外，为了提高效率、避免出错，小李还想制作一张可以从下拉列表中选择饮料名称，并能自动计算出顾客应交款及应找回款得“新销售记录”工作表。4。制作过程S TEP 1 VLO OKU P函数得使用设计目标参照下图根据“销售记录”表中得“ 饮料名称”歹U,利用VLOOK UP函数在“饮料基本信息 ”表 中查找其她列（单位、进价与售价）得值。日期 所在区钦科店 饮料名

6、称罂筮2007-1-1 南山2007-1-1 南山2D07-1-1 南山2007-1-12007-1-12007-1-12007-L-12007-1-12007-1-12007-1-12007-L-12007-1-12007-1-12007-1-1西西店西丽陪山山山山山山山山南南南南南南南南南山统一奶茶 红牛 康萝啤 非常可乐 百事可乐UL） 姓哈哈果奶 葡萄汁 雪誓 王老吉 怡宝县净水 脉动 果粒橙可口可乐（1L） 铳一塌茶 娃哈哈纯净水售检毛利涧1可口可抵听1. 702. 102百事可乐听L602,00_ 3可口可乐(IL)瓶2. 102. 60i百事可乐(1L)瓶九002, 5D_ 5雪

7、碧听1. 702. 206雪碧UL)瓶九002. 507七喜听1. 702. 208七喜UD瓶九002. 609汇源果汁合7. 809. 2010美年达听1.702. 2011芬达听1. 602. 00_12醒目听2. 002. 5013健力宝听1. 702. 2014王老吉合L 702. 20性价在料名防致量所崔区长料疼日地根据以上两个表 中得数据得到下 图中得结果DO，害价 赭售辙 毛利润2007-1-1 2007-1-1 2007-1-13001TT 20DV-| 007-1-1 200V -t 2007-1-1 2007HH 2007-1-1 2007HH 2007-1-1 2007Z

8、S 2OOTTT 2UU1-1-1 2OOT-1T 2001-1-1 ZWI-l-l 2001-1-1 2007-1-1 2001-1-1统一斯莱7C红牛3延蕈咀H非常可乐f百摹呆1L)5!娃哈哈臬班E：曲蜡广才雪碧4E+ *吉L-伯空区建水工崛2臬轼橙L 可口可乐1口”西祠后统一缘茶近的底蛙啥喑她净.水苫而后乐百氏婉净水城顼苑1.92.44.2200-1-120D1-1-1房后盾后JfillJ UUJ 13 -m 33 JflJ 13 日百日-日日-H 西s应西百35-tWUL)可口司今眼日电tl升漆红茶 乐百氏臭奶苫丽店听版听听地瓶听22,5I.S02.22.2L.22.632.62.31

9、.21.32.22. 62.12.E?.2最后计算销售额、毛利润、毛利率日期2LU7-1-1向山2CO7-1-1西山20117-1=1 阴山2CO7-1-1Will2007-1-1陶山2CO7-1-1南山2007-1-1南山2CO7-1-1南山2CO7 1 1南山2CO7-1-1南山2CO7- 1 1两山JLU7-1-1 由山2CO7-1-1南山南山2007-1-1沟山2CO7-1-1阳山2ro7i-i西山2007-1-1两山“3+1西山2007： 1 1西山2007-1-1西山2007 -1 1西山2UUF-1-1国山房店店店店店Bfe .* 一 1 . - 1 :一 1 一 一 . 1 ,

10、 “. 一 ,;一 :HBJ . nH -n -B -N -R -H -A _B A 而西西a西a西西西西饮料店 饮料包端熟星单位进的统一时茶 幺牛 置要啤 非常可呆 百寻可呆11， 旌哈哈里昉空碧 工老百 怡生穿净水西丽玄就劫卤朋店 见超楂 西丽后 可口可乐门口 西丽店绕一绿茶 西丽后山哈哈匏净水皿画冯 乐百氏生净力. 西丽店 七宴 西画区 L(IL) 丽丽叵 可口可乐 西丽店 醒目也期出旭H升雄盯察西丽后 阡百良果仍四川店 芬达瓶听听听瓶瓶白听白电瓶孤瓶翅瓶瓶听瓶听听电塞第u S 6 8 QU 3 0 8 7 n-i TVUT46Jfi-34 9 -b 8 Ui7 7 1 5 2 8 4

11、16 2 10- y 5 8 7 2 1 7 1 9 21.7168.UU35.00327.6D78.00乱如IS.OD3. 20147.502S.5041.4Dg一 jo516.OD12S.OO105,0024.1037.4。e.so74.4018.3(70,21. 20M.CJD10.8070,2D13. dO21d,2U4 JO67.20iZsoJ07.9D24.90167. 2038. OCbbTbd13.3029,405.60197.BEJ39. 3033.007.50165.(3)3S.2044.CD1,80（1）V LOOKUP函数就是干什么用得V LO OKUPg数得功能：查找

12、数据区域首列满足条件得元素，并返回数据区域当前行中指定列处得值。VLO OK UP得语法:注息:要查找彳#对象（参数）一定要定义在查找数据区域 （参数）得第一列 下面来瞧瞧 VL8 KUP函数就是怎么用得.（2 ）如何查找“单位?打开文件“饮料销售（素材 卜xls”，将文件另存为“姓名_饮料销售、xls。在“销售记录”工作表 中，选中F3单元格一选择“插入一函数一 VLOOK UP” 一 单 击“确定”.由于要根据 饮料得名称 查找“单位,所以VL O OK UP函数得第一个参数应该选择饮料名称“ D3.在“Tab 1 e_ar ray区域中选择“饮料价格”工作表中得 B2:E4 4。由于“单

13、位”数据存放在“饮料信息”数据区域得第2歹U,所以输入数字“2”。由于要求饮料名称精确匹配，所以最后一个参数输入“F A L SE”。单击“确定”，可以瞧到函数准确地返回了 “统一奶茶”得单位“瓶”。复制公式，出现了什么问题？?为什么？如何解决呢？想想瞧为什么?提示：注意在复制公式时 ，如果沿着列拖动时，列标要用绝对引用,修改公式后，重新复制公式.(3)创建区域名称选择“饮料名称”工作表。选中饮料名称、单位、进价、售价所在得区域，即单元格区域在名称框中输入“自己得姓名+A，一定要按回车确认。问题:区域得定义可以包含“序号”列吗？为什么？(2)如果名称定义错误了，如何将其删除。(4)利用区域名查

14、找“进价”与售价”选择“销售记录”工作表一选中G3单元格一输入等号“=一选择函数“ V LOOK UP” 一单击“确定”.由于要根据 饮料得名称 查找“售价”，所以V LO O K UP函数得第一个参数应该选择饮料名称“D3”。在Tab 1 e_arr ay”区域中输入“姓名A。由于“进价”数据存放在第3歹U,所以输入数字“ 3”。由于要求饮料名称精确匹配，所以最后一个参数输入“ FAL SE”单击“确定”，可以瞧到函数准确地返回了 “统一奶茶”得“进价”数据“1.9”。VLQQKVTLooktrpB3Tabi e_air4y 唐静AFALSE馨藕勰魔般薛:矍懿怎么样，对于VLOOKUP函数您

15、就是多做几遍，慢慢去体会。，再进ft否有了一定得认识？如果还不会，请L_ 一一B.angft_loQku指定在查找时是要求精确匹SE 还是大致匹配口如果为FALSE 大豆匹配.如果为TRUE或忽略，精请匹配计直结果=有关该函数的帮助的确定取消复制公式，瞧一瞧有什么不同？ ？同样道理在H3单元格中建立查找饮料名称“售价”得公式。(5)计算销售额、毛利润STEP 2 一分类汇总首先，建立表“销售记录”得二个副本“销售记录（2）”、“销售记录（3）”与销售记录（4）”。方（结果见“（样例）饮料销售、x 1 s”）。按照“所在区”进行分类汇总在表“销售记录（2）”中按“所在区”对销售额与毛利润进行分类

16、汇总，汇总结果显示在数据下将销 售记录（2 ）改 名为“所 在区汇 总”结果参见样例中得工作表一一“所在区汇总?（2）按照“饮料名称”进行分类汇总在表“销售记录（3）”中按“饮料名称”对 销售额与毛利润进行分类汇总（汇总方式为 求与）对汇总结果中得毛利润列按降序排序，找出毛利润最大得饮料.将销售记录（3）改名为“饮料名称汇总”结果参见样例中得工作表一-“饮料名称汇总”（3）用 嵌套分类汇总M统计各个区与各饮料店得饮料销售额”与 毛利润在 销售记录（4工作表中对 主要关键字”选择 所在区”，次要关键字”选择 饮料店”排序。进行第一次 分类汇总”（分类字段为 所在区”）。进行第二次 分类汇总”（分

17、类字段为 饮料店“）。将销售记录（4）改名为“嵌套汇总”结果参见样例中得工作表一一“嵌套汇总ST E P 3一利用数据透视表实现统计分析设计目标如下图所示，利用数据透视表生成三维数据统计报表。1式和项毋充Ii EiT区副将透视表字隹列表|一优朴名林“宝安福田太谊甲湖南山息il将同日横手购僵旃视责口字必乐33246233S6744284E口鼻口 I匹UL）542.68507ET37.5B32. 63417.5-LF渔装嗨256608. 84洱8360257.6：843. 21 1 口加非常K云4030622iMOSZ9G176口所右区苓达5424204M而3；0MM日饮料店日饮料名称光明鲂初50

18、3,26419. 7678b3：G,4633.42669光明照期475x937-2E4T.2w.s317. 3M旌.d果料造7E45Jy口数量1杂69J6肛H703. BJ67U口单位红牛1125.GJ0C.6.275(1. 2121OL02446. flP23.W汇源果汁121&.2JOED- 417B3.61037,31773. 87846. 8口的所 日售价 EJ销售领 E毛利润健力宏61吊1E4. g丽634.6U3B. S30E6.E可口可不S56,fi663. 6504T3C84202814.9可口可乐门口790.44乳2旧E明丽qq况5方乐百凭兜净水23

19、0, 11QK4际,q27,000.00)睡 Y 26 500,00) 26,000.00)25,500,00)25,000,00)鞘害款与毛洵关系图福田汇总罗湖汇总南山汇总 饮料店6.200.00)6.000.00)5.800.00)5：600.00)通 邛5.400.00)5.200.00)V5JOOO.OO)1=1稍售颔毛利润24,500,010)(如下图)所示；? (1)制作“两轴线图”在表“所在区汇总”分别选择“所在区”、“销售额”与“毛利润”三列销售纪录表臼期所在区快料店竹科名称数量单位造价售附箱善髓毛利润宝安扎总25458. 005522. 20塞田汇直27620. 0055E7

20、. E0蛇自汇总299M. 9035d2. 90器涉江总1 25547. S05479. 20南山正急Z7B32. 106054. 90总计13(5232.5029586。单击“图表向导”按钮，打开“图表向导一4图表类型”对话框，选择“自定义类型”选项卡，选择“两轴线一柱图”囱击向8 - 4步舞力1 团表布型心凿自定X.非星现表美里睾火盘保jt国A圉海里自弗也.用m面曲图:里三指线图THH国吉,d里刍柱兄.国.晶弟田.G色印图吼Rg曜iiB。日定文5.：.内即俎相 卜下f .聿5 单击“下一步,打开“图表向导一 4 一图表源数据”对话框? X|取手理左向导-4净H 2 -图左源世事JG匚M也做

21、产生也rrm ,n- m Frit jtiU EFFlTi 2Ja*T U3 ll ill2JUOU单击“下一步，打开“图表向导-4图表选项”对话框，按下图进行设置圉表向导-4灰燕之3 -阕表这项标题 坐标轴网格建丽一|废据福jf数据表nc）：销售颔身利润美系分类尊曲。二 啰在区数值正逸。二销售额次分类鼠幅（X）:中快a 1* H Y231EJU 曰国力血 Hcmnc3SXUOO 253DHJ oca次卷值以带dr年制京取消 I上一步正一步尤：1 完城归单击“下一步”，在“图表位置”对话框中，选择“作为其中得对象插入”，单击“完成”按钮（2）美化图表参照“饮料销售（样例）、x 1 S”，完成对

22、图表格式得设置（格式可以自定义）。26000.3027500.00 ?7nm 口 口品 2D5OD.30 肺造 260OD J02E5OD.3D25000 JQ照国题与利泅美不福田汇总 罚葡汇总南山全总所在区G100006DODOO5TOOT5B000057m ITI再 5600 CD 550000 由百加口 005300 0D5200 0000 00匚二I隹物毛利海24500.30STE P 5一利用数据有效性制作更方便、更实用得“新销售记录”工作表设计目标在“饮料销售、x 1S”中制作一张“新销售记录表”，并应用数据有效性设置， 使得在填写了销售“数 量”与选取了 “饮料名称”后，可以自动

23、计算出“销售额”、“毛利润”与“毛利率”。宝七七汇美芬噩碧喜喜忠年苣目日期星期|饮料名称(1 )制作“销售记录表副本在“饮料销售、xls”中建立一个“销售记录”表得副本，并将其重命名为“新销售记录”，然后将其前五列得内容删除(只保留标题行工如下图所示。里位这粉督价制售藏飞第kWA r#N/A，皿靴卜r rw/A1r 4M/A 中财A r (W/A4W/A r aN/a r iJJ/A #Nza f tN/ArWA 的AFWAWaf槐31rtwarsivartn/a4U/AHK/aiW/Ar*皿rn-ir蝴mr*m/am-AF#即总rtJ4/AratVArt/Ar iM/A 咖/A F ZJT/

24、ALWa :#k/a帐0WA1*N/AWA L i(附 A k iNZA#M/A Frr TWA 1r1r 趣”.*股息A A A A Aww w w w w * */亡* +1-并增加3个新列“实收”、应收 ”与找回(2)对“饮料名称”应用数据有效性设置选中“饮料价格”表中得饮料名称”区域，并将其定义为“饮料名称”，如下图所示。翘务栋 方A果粒橙良CDE1饮料价格表23序苛 L 1 1 可口可乐42百事可乐听1.602.0053可口可乐门L)啊2. 102,6064口本可乐UL)瓶2.002+5075雪碧听L702.2086雪署(1工)瓶2.002.5097信听1.7 口2.2Q108七曲口

25、)瓶2.002.6。119汇源果汁合九和3.801210美星达听1.102.2。131L芬达金17602.001412需目听1 2,002.501513健力宝听1.702.20选中“新销售记录得第4歹U (“饮料名称”列，然后再选择“数据” 一 “有效性”打开数据有效性对话框。在有效性条件中选择“序列在“来源”中填写“二饮料名称”(注意：“饮料名称”就是定义得“饮料名称”区域 )，如图下所示。(3)数据有效性得使用，可以自制作完成，如下图所示，试试瞧，就是不就是在填写了销售“数量”与选取了 “饮料名称”后动计算出“销售额”、“毛利润”与“毛利率”,很方便吧!日期|星期欣科名学|单省 者的|数量

26、 错害颛进价毛利润毛利率4. 0U 1,6。0.002口.口粕y喜喜调年达目季七七汇美芬里(4)对“饮料店”列进行数据有效性设置参照“饮料名称”得设置对“饮料店”进行设置(5)创建“应收”与“找回”列得公式，算法如下:应收=销售额，找回=实收一应收.S T EP6一让查找公式更完美(1 )隐藏列在“新销售记录”工作表中将不需要显示得列(单位“、进价”、销售额”)隐藏起来。(2)前面得结果有缺陷在上面应用了查找函数 VLOOK U P得“销售记录”表中，如果把D 3单元格中得饮料名称删去以后 可以瞧到F3、G3、H3单元格(即：单位、售价与进价)中均返回错误值“ #N/A” .日期 所在区以科居

27、的J*ffl2007-11-23A A AWM/您# #世AAA吊比比AAA Mfi/灯 # # i耨15 星f/A 1?H/A大家可以试一下，当VL OOKUP函数在“饮料价格”表中没有找到D3单元格中得饮料名称时都要返回错误值“#N/A”。能不能让当VLOOKUP函数在“饮料信息”表中没有找到D 3单元格中得饮料名称时不返回错误值“#N/A”，而只什么都不显示(即显示空格)呢？(2)解决办法利用IF与ISERROR函数可以解决上面得问题。试试瞧，您能解决这个问题吗 ？上面得问题变成两种情况如果当 VL O OK U P ( D 3 , y lm C, 2 , FA L SE)”部分返回错误

28、值得话，则在F3中显示空字符串。如果“V LOOKUP (D3,ylmc ,2,FALSE) ”部分工作正常，则在F 3单元格中显示饮料对应得售 价(即VLOOKUP(D3 ,y 1 mc, 2, F A LSE)得值)。?STEP7一冻结窗口保留标题行在“销售记录”工作表中，当销售记录增多后，为了保留标题行得内容，可单击 A1单元格，执行“窗口 一 “冻结窗口”命令，当滚动屏幕时，可以把第一行 (即表头行)得内容一直保留在窗口中。重点难点（ 1 ） 重点VLOOKUP 函数得使用。区域名称得定义分类汇总数据透视表（ 2 ） 难点VLOOKUP函数得参数得选择数据透视表?5、 案例总结与常见问

29、题本案例通过对学生信息表数据得处理，介绍了查找与引用类函数V LOOK UP得用法及分类汇总与 TOC o 1-5 h z 数据透视表得用法.（一）您知道了吗?通过本案例得学习,您能回答如下问题吗?1。VLOOC UP函数就是干什么得？2。用VLOOKU函数进行查找时，所要查找得元素为什么一定要在所定义得数据区域得第一列 ？3。VL OOKU函数中第二个参数得含义就是什么？在定义第二个参数时要注意什么？4。在对VLOOKP函数沿列进行复制时，一般情况下，为什么列标要用绝对地址 ？。如何定义数据区域，如何删除数据区域？.用VLOOKPI数时，可以不定义数据区域吗 ？7。什么就是分类汇总？它有什么作用？8。在分类汇总之前要注意什么 ？(二)常见问题及处理方法卜面把大家在本案例得学习过程中容易遇到得一些问题及处理方法列于下表常见问题可能原因处理方法“数据区域”名称无 法重新定义。该“数据区域”名称已被定义。将其删除，重新定义，方法：选择“插入” 一 “名称”-“定 义”，打开“定义名称”对 话框，选择已定义得名称 ，单击删除”命令。VLOOKUP 函数返 回错误值“#N/A”。(1)没有把要查找得对象定义 在“数据区域”得第一列.重新定义“数据区域”，把要 查找得内容定义在“数据区 域得第一列。(2)查找得内容在定义得“数 据区域”中不