认证主体：遵化市龙源小区微传网络工作室
IP属地：河北
统一社会信用代码/组织机构代码
92130281MA09U3NC1M
1、EXCEL实训练习三商场销售数据得分析处理1、问题得提由小李在深圳市开了若干家饮料连锁店，为了提高管理水平，她打算用Excel工作表来管理销售数据。所在区政科店日期1卜图就是她制作得各饮料店得销售记录流水帐表。tt料名秫魏里单位进价售价播售酒毛利润10111213141KIC1718200T-1-12CJJ7-L12007-1-12007-1-1200T-1-1之o(r-ii200,-1-1zuir-i-i200T-1-12UU7-112001-1-1店店店店店店DEFHE口TTTRII日凡fnf1二茶io红牛1E装萝啤ie非常可乐R百事可乐QL）59握哈
2、哈臬划23苗萄汁564g南山西丽店南山百丽店2001-1-1200,-1-12OD7-1-1舸山王:&吉怡宝电净水挑动果粒也可口可乐。口第一缥菜坤口向哈临务攻乐口氐岫争死17空Li1627后号饮科名徐单位选阶售价11可口可乐所1.702102白事口1乐听1*602.叩3可口可乐（1L）瓶2102.604白事引乐（1L）瓶2.002.505噌白=1-1!=诉1,702,20行9eC1D2.002.507七善听k702.20S七喜（1L）瓶2.002。3汇源果汁47.305,S010美年达听1.ioj2.2011芬达听1.6020012醒目听2.003
3、ECIr13健力宝听1.70J2.20-14王老吉.吕1,702.2015鲜橙多瓶2.403.0016鲜桃多瓶2*403.0017统一物茶瓶1.9。2.4018统一冰红茶瓶k802.3019统一球茶瓶1.ao2.3U2口旭日升冰红素瓶1.702.20Ffi也J为了统计“毛利润”，她必须去“饮料基本信息”表中查找每种饮料得“进价”与“售价”。这个工作量实在太大，而且还容易出错。现在希望：能否输入饮料名称后，让Exce1根据这个名称自动去查找该饮料得“单位”、“进价”、“售价”等信息，并存放到表“销售记录”得相应列中解决万案通常情况下，如果不借助其它方法得帮助，要想在E
4、xcel中解决这个问题，只能到“饮料基本信息”表中一条一条地查找各种饮料得“进价”与“售价”.如果不想这么做，您有什么更好得办法吗？这个实际需求，开发Excel得工程师，已经为我们想到了。在Excel中有一个函数，就就是专门为解决这类问题设计得，这个函数就就是VLOOKUP。小李这个问题，可利用Excel中得查找函数VLOOKUP来解决。它得功能就是，在数据区域得第一列中查找指定得数值，并返回数据区域当前行中指定列处得数值。下面来瞧瞧应用VLOOKUP函数就是如何解决上述问题得。实现方法本案例要解决如下几个问题：.在“销售记录”工作表中用VLOOKUP函数计算饮料得“单
5、位”、“进价”与“售价”等信息，并计算出工作表中得“销售额”与“毛利润”等信息。2。用分类汇总”统计出各连锁店与各个区中各种饮料得“销售额”、“毛利润”。3。用“数据透视表”分析各个区中每种饮料得销售情况与各个区中销售情况最好得饮料4。另外，为了提高效率、避免出错，小李还想制作一张可以从下拉列表中选择饮料名称，并能自动计算出顾客应交款及应找回款得“新销售记录”工作表。4。制作过程STEP1VLOOKUP函数得使用设计目标参照下图根据“销售记录”表中得“饮料名称”歹U,利用VLOOKUP函数在“饮料基本信息”表中查找其她列（单位、进价与售价）得值。日期所在区钦科店饮料名
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王老吉合L702.20性价在料名防致量所崔区长料疼日地根据以上两个表中得数据得到下图中得结果DO，害价赭售辙毛利润2007-1-12007-1-12007-1-13001TT20DV-|007-1-1200V-t2007-1-12007HH2007-1-12007HH2007-1-12007Z
8、S2OOTTT2UU1-1-12OOT-1T2001-1-1ZWI-l-l2001-1-12007-1-12001-1-1统一斯莱7C红牛3延蕈咀H非常可乐f百摹呆1L)5!娃哈哈臬班E：曲蜡广才雪碧4E+*吉L-伯空区建水工崛2臬轼橙L可口可乐1口”西祠后统一缘茶近的底蛙啥喑她净.水苫而后乐百氏婉净水城顼苑1.92.44.2200-1-120D1-1-1房后盾后JfillJUUJ13-m33JflJ13日百日-日日-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南山2CO711南山2CO7-1-1南山2CO7-11两山JLU7-1-1由山2CO7-1-1南山南山2007-1-1沟山2CO7-1-1阳山2ro7i-i西山2007-1-1两山“3+1西山2007：11西山2007-1-1西山2007-11西山2UUF-1-1国山房店店店店店Bfe.*一1.-1:一1一一.1,
10、“.一,;一:HBJ.nH-n-B-N-R-H-A_BA而西西a西a西西西西饮料店饮料包端熟星单位进的统一时茶幺牛置要啤非常可呆百寻可呆11，旌哈哈里昉空碧工老百怡生穿净水西丽玄就劫卤朋店见超楂西丽后可口可乐门口西丽店绕一绿茶西丽后山哈哈匏净水皿画冯乐百氏生净力.西丽店七宴西画区L(IL)丽丽叵可口可乐西丽店醒目也期出旭H升雄盯察西丽后阡百良果仍四川店芬达瓶听听听瓶瓶白听白电瓶孤瓶翅瓶瓶听瓶听听电塞第uS68QU3087n-iTVUT46Jfi-349-b8Ui7715284
11、16210-y5872171921.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,2U4JO67.20iZsoJ07.9D24.90167.2038.OCbbTbd13.3029,405.60197.BEJ39.3033.007.50165.(3)3S.2044.CD1,80（1）VLOOKUP函数就是干什么用得VLOOKUPg数得功能：查找
12、数据区域首列满足条件得元素，并返回数据区域当前行中指定列处得值。VLOOKUP得语法:注息:要查找彳#对象（参数）一定要定义在查找数据区域（参数）得第一列下面来瞧瞧VL8KUP函数就是怎么用得.（2）如何查找“单位打开文件“饮料销售（素材卜xls”，将文件另存为“姓名_饮料销售、xls。在“销售记录”工作表中，选中F3单元格一选择“插入一函数一VLOOKUP”一单击“确定”.由于要根据饮料得名称查找“单位,所以VLOOKUP函数得第一个参数应该选择饮料名称“D3.在“Tab1e_array区域中选择“饮料价格”工作表中得B2:E44。由于“单
13、位”数据存放在“饮料信息”数据区域得第2歹U,所以输入数字“2”。由于要求饮料名称精确匹配，所以最后一个参数输入“FALSE”。单击“确定”，可以瞧到函数准确地返回了“统一奶茶”得单位“瓶”。复制公式，出现了什么问题？为什么？如何解决呢？想想瞧为什么提示：注意在复制公式时，如果沿着列拖动时，列标要用绝对引用,修改公式后，重新复制公式.(3)创建区域名称选择“饮料名称”工作表。选中饮料名称、单位、进价、售价所在得区域，即单元格区域在名称框中输入“自己得姓名+A，一定要按回车确认。问题:区域得定义可以包含“序号”列吗？为什么？(2)如果名称定义错误了，如何将其删除。(4)利用区域名查
15、就是多做几遍，慢慢去体会。，再进ft否有了一定得认识？如果还不会，请L_一一B.angft_loQku指定在查找时是要求精确匹SE还是大致匹配口如果为FALSE大豆匹配.如果为TRUE或忽略，精请匹配计直结果=有关该函数的帮助的确定取消复制公式，瞧一瞧有什么不同？？同样道理在H3单元格中建立查找饮料名称“售价”得公式。(5)计算销售额、毛利润STEP2一分类汇总首先，建立表“销售记录”得二个副本“销售记录（2）”、“销售记录（3）”与销售记录（4）”。方（结果见“（样例）饮料销售、x1s”）。按照“所在区”进行分类汇总在表“销售记录（2）”中按“所在区”对销售额与毛利润进行分类
16、汇总，汇总结果显示在数据下将销售记录（2）改名为“所在区汇总”结果参见样例中得工作表一一“所在区汇总（2）按照“饮料名称”进行分类汇总在表“销售记录（3）”中按“饮料名称”对销售额与毛利润进行分类汇总（汇总方式为求与）对汇总结果中得毛利润列按降序排序，找出毛利润最大得饮料.将销售记录（3）改名为“饮料名称汇总”结果参见样例中得工作表一-“饮料名称汇总”（3）用嵌套分类汇总M统计各个区与各饮料店得饮料销售额”与毛利润在销售记录（4工作表中对主要关键字”选择所在区”，次要关键字”选择饮料店”排序。进行第一次分类汇总”（分类字段为所在区”）。进行第二次分类汇总”（分
17、类字段为饮料店“）。将销售记录（4）改名为“嵌套汇总”结果参见样例中得工作表一一“嵌套汇总STEP3一利用数据透视表实现统计分析设计目标如下图所示，利用数据透视表生成三维数据统计报表。1式和项毋充IiEiT区副将透视表字隹列表|一优朴名林“宝安福田太谊甲湖南山息il将同日横手购僵旃视责口字必乐33246233S6744284E口鼻口I匹UL）542.68507ET37.5B32.63417.5-LF渔装嗨256608.84洱8360257.6：843.211口加非常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)睡Y26500,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器涉江总125547.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净H2-图左源世事JG匚M也做
21、产生也rrm,n-mFritjtiUEFFlTi2Ja*TU3llill2JUOU单击“下一步，打开“图表向导-4图表选项”对话框，按下图进行设置圉表向导-4灰燕之3-阕表这项标题坐标轴网格建丽一|废据福jf数据表nc）：销售颔身利润美系分类尊曲。二啰在区数值正逸。二销售额次分类鼠幅（X）:中快a1*HY231EJU曰国力血Hcmnc3SXUOO253DHJoca次卷值以带dr年制京取消I上一步正一步尤：1完城归单击“下一步”，在“图表位置”对话框中，选择“作为其中得对象插入”，单击“完成”按钮（2）美化图表参照“饮料销售（样例）、x1S”，完成对
22、图表格式得设置（格式可以自定义）。26000.3027500.007nm口口品2D5OD.30肺造260ODJ02E5OD.3D25000JQ照国题与利泅美不福田汇总罚葡汇总南山全总所在区G100006DODOO5TOOT5B000057mITI再5600CD550000由百加口0053000D5200000000匚二I隹物毛利海24500.30STEP5一利用数据有效性制作更方便、更实用得“新销售记录”工作表设计目标在“饮料销售、x1S”中制作一张“新销售记录表”，并应用数据有效性设置，使得在填写了销售“数量”与选取了“饮料名称”后，可以自动
23、计算出“销售额”、“毛利润”与“毛利率”。宝七七汇美芬噩碧喜喜忠年苣目日期星期|饮料名称(1)制作“销售记录表副本在“饮料销售、xls”中建立一个“销售记录”表得副本，并将其重命名为“新销售记录”，然后将其前五列得内容删除(只保留标题行工如下图所示。里位这粉督价制售藏飞第kWAr#N/A，皿靴卜rrw/A1r4M/A中财Ar(W/A4W/AraN/ariJJ/A#NzaftN/ArWA的AFWAWaf槐31rtwarsivartn/a4U/AHK/aiW/Ar*皿rn-ir蝴mr*m/am-AF#即总rtJ4/AratVArt/AriM/A咖/AFZJT/
24、ALWa:#k/a帐0WA1*N/AWALi(附AkiNZA#M/AFrrTWA1r1r趣”.*股息AAAAAwwwwww**/亡*+1-并增加3个新列“实收”、应收”与找回(2)对“饮料名称”应用数据有效性设置选中“饮料价格”表中得饮料名称”区域，并将其定义为“饮料名称”，如下图所示。翘务栋方A果粒橙良CDE1饮料价格表23序苛L11可口可乐42百事可乐听1.602.0053可口可乐门L)啊2.102,6064口本可乐UL)瓶2.002+5075雪碧听L702.2086雪署(1工)瓶2.002.5097信听1.7口2.2Q108七曲口
26、错害颛进价毛利润毛利率4.0U1,6。0.002口.口粕y喜喜调年达目季七七汇美芬里(4)对“饮料店”列进行数据有效性设置参照“饮料名称”得设置对“饮料店”进行设置(5)创建“应收”与“找回”列得公式，算法如下:应收=销售额，找回=实收一应收.STEP6一让查找公式更完美(1)隐藏列在“新销售记录”工作表中将不需要显示得列(单位“、进价”、销售额”)隐藏起来。(2)前面得结果有缺陷在上面应用了查找函数VLOOKUP得“销售记录”表中，如果把D3单元格中得饮料名称删去以后可以瞧到F3、G3、H3单元格(即：单位、售价与进价)中均返回错误值“#N/A”.日期所在区以科居
27、的J*ffl2007-11-23AAAWM/您##世AAA吊比比AAAMfi/灯##i耨15星f/A1H/A大家可以试一下，当VLOOKUP函数在“饮料价格”表中没有找到D3单元格中得饮料名称时都要返回错误值“#N/A”。能不能让当VLOOKUP函数在“饮料信息”表中没有找到D3单元格中得饮料名称时不返回错误值“#N/A”，而只什么都不显示(即显示空格)呢？(2)解决办法利用IF与ISERROR函数可以解决上面得问题。试试瞧，您能解决这个问题吗？上面得问题变成两种情况如果当VLOOKUP(D3,ylmC,2,FALSE)”部分返回错误
28、值得话，则在F3中显示空字符串。如果“VLOOKUP(D3,ylmc,2,FALSE)”部分工作正常，则在F3单元格中显示饮料对应得售价(即VLOOKUP(D3,y1mc,2,FALSE)得值)。STEP7一冻结窗口保留标题行在“销售记录”工作表中，当销售记录增多后，为了保留标题行得内容，可单击A1单元格，执行“窗口一“冻结窗口”命令，当滚动屏幕时，可以把第一行(即表头行)得内容一直保留在窗口中。重点难点（1）重点VLOOKUP函数得使用。区域名称得定义分类汇总数据透视表（2）难点VLOOKUP函数得参数得选择数据透视表5、案例总结与常见问