《2023年ecel销售数据分析-实训练习.docx》由会员分享，可在线阅读，更多相关《2023年ecel销售数据分析-实训练习.docx（23页珍藏版）》请在三一办公上搜索。
1、*欧阳光明*创编2023.03.07EXCEL实训练习三欧阳光明2023.03.07)商场销售数据的分析处理1 .问题的提出小李在深圳市开了假设干家饮料连锁店，为了提高治理水平，他打算用Excel工作表来治理销售数据。以下图是他制作的各饮料店的销售记录流水帐表。为了统计“毛利润”，他必需去“饮料根本信息”表中查找每种饮料的“进价”和“售价”。这个工作量实在太大，而且还简洁出错。现在期望：能否输入饮料名称后，让EXCel依据这个名称自动去查找该饮料的“单位”、“进价”、“售价”等信息，并存放到表“销售记录”的相应列中。2 .解决方案通常状况下，假设不借助其它方法的帮助，要想在Excel中解决这个
2、问题，只能到“饮料根本信息”表中一条一条地查找各种饮料的“进价”和“售价”。假设不想这么做，你有什么更好的方法吗？这个实际需求，开发Excel的工程师，已经为我们想到了。在Excel中有一个函数，就是特地为解决这类问题设计的，这个函数就是VLooKUP。*欧阳光明*创编2023.03.07小李这个问题，可利用EXCel中的查找国数VLoOKUP来解决。它的功能是，在数据区域的第一列中查找指定的数值，并返回数据区域当前行中指定列处的数值。下面来看看应用VL00KUP函数是如何解决上述问题的。3 .实现方法本案例要解决如下几个问题：1 .在“销售记录”工作表中用Vlookup函数计算饮料的“单位”
3、、“进价”和唯价”等信息，并计算出工作表中的“销售额”和“毛利润”等信息。2 .用“分类汇总”统计出各连锁店和各个区中各种饮料的“销售额二“毛利润”。3 .用“数据透视表”分析各个区中每种饮料的销售状况和各个区中销售状况最好的饮料。4 .另外，为了提高效率、避开出错，小李还想制作一张可以从下拉列表中选择饮料名称，并能自动计算出顾客应交款及应找回款的“销售记录”工作表。4 .制作过程STEP1VLOOKUP函数的使用设计目标参照以下图，依据“销售记录”表中的“饮料名称”列，利用VL00KUP函数在“饮料根本信息”表中查找其他列单位、进价和售价的值。序号饮料名称单位进价售价1可口可乐听1.702.
4、IO2百事可乐听1.602.003可口可乐（IL）瓶2.IO2.604百事可乐（IL）瓶2.002.505雪碧听1.702.206雪碧（IL）瓶2.OO2.507七喜听1.702.208七喜（IL）瓶2.OO2.609汇源果汁合7.809.80IO美年达听1.702.2011芬达听1.602.0012醒目听2.OO2.5013健力宝听1.702.2014王老吉合1.702.20日期所在区饮料店饮料名院歙量单位进价售价2001-1-1南LJ统一斯茶711.92.42001-1-1南山红牛7；3.24.22001-1-1南Ll拓琴嚏Ii1.21.62001-11南山非常可乐1.622001-1-1
5、南Ll百事可乐（IL）5!22.52001-1-1南Ll姓哈哈果坊2：1.41.82007-1-1南山曲堂汁34.562001-1-1南山雪碧4；1.12.22001-1-1南LJ王老告1.12.22007-1-1南山怡宝纯洋水5：0.91.22001-1-1南U排动2,22.62007-1-1南山果粒橙1：2.432001-1-1南LJ可口可乐UL）2.12.62007-1-1南LJ统一绿茶1.82.32001-1-1南山姓哈哈兜净水0.91.22001-1-1南山乐百氏纯净水11.32001-1-1南山七喜/11.12.22001-11南U七书（1D22.62001-1-1南LJ可口可乐1
6、，1.12.1200T-1-1南山醒目7!22.52001-1-1南LJ旭日升冰红茶1!1.12.22001-1-1南山乐百氏果明y.1.31.72007-1-1南山芬达2：1.62日期所在区伏制店饮科名称致量单位逆价售价销售项毛利游2007-1-12007-1-12007-1-12007-1-12007-1-12007-1-12007-1-12007-1-12CO7112007-1-12007-112007-1-12007-1-12007-1-12007-1-12007-1-12007-1-12007-1-12C07-1-1112007-1-12007-112007-1-1山山山山山山山山山
7、山山山山山山山山山山山山山山.用两周可肖raHIEF)IE两两两周南肉画百面F)百两向覆至啤非常可乐百事可乐（HJ*哈哈里奶葡萄汁空契王老古怡室兜冷水脏劫乐粒橙可口可乐（1L统一绿茶好哈哈班争冰.乐百氐兜，争水七里七察UL）豆口可乐醒目旭日升汴红茶年百氐果加70瓶听听听瓶瓶合听合1.92.47S3.24.2161.21.681625922.5231.41.8864.56481.72.2171.12.2620.91.22122.6182.43212.12.69456瓶瓶瓶听瓶听听瓶瓶听1.52.30.P1.28311.3761.72.22322.6141.7ZI7922.5151.72.2981
8、.31.7221.62依据以上两个表中的数据得到以168.0035.00327.6078.0025.606.4016.003.20147.5029.5041.409.2G510.00129.00105.6024.0037.408.5074.4018.6070.2D16.2054.0010.8070.2013.50216.2D47.0067.2016.80107.9024.90167.2038.0059.8013.8029.405.60197.5039.5033.001.50166.6039.2044.008.8。(1) VLooKUP函数是干什么用的VLOOKUP函数的功能：查找数据区域首列满
9、足条件的元素，并返回数据区域当前行中指定列处的值。VLOOKUP的语法:留音M1/Q要查找的对象参数肯定要定义在查找数据区域参数的第一列。下面来看看VLOOKUP函数是怎么用的。(2)如何查找“单位”？ 翻开文件“饮料销售(素材)ls”,符文件另存为“姓名_饮料销售xls% 在“销售记录”工作表中，选中F3单元格T选择“插入一函数TVLoOKUP”一单击“确定”。 由于要依据饮料的名称查找“单位”，所以VLoOKUP函数的第一个参数应中选择饮料名称“D3”。 在“Table_array”区域中选择“饮料价格”工作表中的B2E44o 由于“单位”数据存放在“饮料信息”数据区域的第2歹1*欧阳光明
10、*创编2023.03.07所以输入数字“2”。“由于要求饮料名称准确匹配，所以最终一个参数输入“FALSE”。k单击“确定”，可以看到函数准确地返回了“统一奶茶”的单位“瓶”。k复制公式,消灭了什么问题为什么？如何解决呢？提示：留意在复制公式时，假设沿着列拖动时，列标要用确定引用，想想看为什么？“修改公式后，重复制公式。(3)创立区域名称选择“饮料名称”工作表。选中饮料名称、单位、进价、售价所在的区域，即单元格区域B2:E44。在名称框中输入“自己的姓名+A”,肯定要按回车确sx/皿善提示:插入一名称T定义姓名AUo思考问题:*欧阳光明*创编2023.03.07(1)区域的定义可以包含“序号”
11、歹J吗？为什么？(2)假设名称定义错误了，如何将其删除。(4)利用区域名查找“进价”和“售价” 选择“销售记录”工作表一选中G3单元格一输入等号“=”一选择函数“VLOOKUP”一单击“确定”。 由于要依据饮料的名称查找“售价”，所以VLOOKUP函数的第一个参数应中选择饮料名称“D3”。 在“Table_array”区域中输入“姓名A”。 由于“进价”数据存放在第3歹I，所以输入数字“3”。 由于要求饮料名称准确匹配，所以最终一个参数输入“FALSE” 单击“确定”，可以看到函数准确地返回了“统一奶茶”的“进价”数据“19”。 怎么样，对于VLOOKUP函数你是复制公式，看一看有什么不同否有
12、了肯定的生疏？假设还不会， 同样道理在H3单元格中建立查找饮购蝴的曾便，的分式。温馨提示，表格中数据之间的关系为：销售额=售价*数量毛利润=(售价一进价)x数量计算销售额、毛利润JjSTEP2分类汇总首先，*欧阳光明*创编2023.03.07建立表“销售记录”的二个副本“销售记录(2)二“销售记录(3)”和“销售记录(4)”。叱温馨提示：*按住Ctrl键拖开工作表标签(1)依据“所在区”进展分类汇总在表“销售记录(2)呻按“所在区”对销售额和毛利润进展分类温馨提示：选择“所在区”列中的任意单元格单击“升序”按钮冽进展排序(排序的作用是什么？)然后选择“数据”一“分类汇总”.汇忘，汇忘结果显小仕
13、数据卜万结果见“(恃例)饮料销售.xls”o将销售记录(2)改名为“所在区汇总”结果参见样例中的工作表“所在区汇总”(2)依据“饮料名称”进展分类汇总在表“销售记录(3)呻按“饮料名称”对销售额和毛利润进展分类汇总汇总方式为求和对汇总结果中的毛利润列按降序排序，找出毛利润最大的饮料。招销售记录(3)改名为“饮料名称汇总”结果参见样例中的工作表“饮料名称汇总”行温馨提示：首先依据“饮料名称”对“销售额”和“毛利润”进展分类汇总;然后，对“毛利润”进展降序排序。(3)用“嵌套分类汇总”统计各个区和各饮料店的饮料“销售额”和“毛利润”在“销售记录(4)”工作表中对“主要关键字”选择“所在区”,“次要
14、关键字”选择“饮料店”排序。进展第一次“分类汇总”分类字段为“所在区”O进展其次次“分类汇总”分类字段为“饮料店为O，仔销售记录(4)改名为“嵌套汇总”结果参见样例中的工作表“嵌套汇总”温馨提示:STEP3利用“所在区”，在次要关键字中选择“饮料店”；数据透然后，先依据“所在区”进展分类汇总;再依据“饮料店”进展汇总，但是其次次汇总时，必需取消“已视表实摸当前分类汇总”。现统计分析设计目标首先，选择“数据”-“排序”命令，在主要关键字中选择如以下图所示，利用数据透视表生成三维数据统计报表。求和项:摘售额I所在区6)欣科三标jT爰福田龙的罗湖南山I总计汇源果汁葡萄汁利畜靠鼠找出销售额最大的饮料植
15、 FLD茶 d红 娣 L)乐冰 牛 U可升宝多纯 吾日事日力橙利 七醒百旭健鲜伊1215. 210861008. 81125. 6790.4873960356. 5508.28321370. 6693813. 8960542.5774.41989.415361918.81558.2434.21-476582671.6580.8530.4481.8636.31258.46908507371783. 618301315. 6760. 263768411192509. 31408975763.4852. 6444. 6640785294.81087.811761877.21230.62056.652
16、8957552759642.2400.4684.6657.8755737.5673.21773. J156(1055.(2448.(686,，97J72(227.90：777.，710. 6703. 5361.4405502.5662.27849.571%e7123.246Q4.E4538433E4317.141553726.83570353634503417.53141.6644.6184.8555552530. 1533.9594768915. 8534.61108.83066.83098493033638.4345.82964(1)为“销售记录”表创立数据透视表要求：为表“销售记录”建立
17、数据透视表，将数据透视表显示在工作表中，并将数据透视表命名为“销售统计”行显示“饮料名称”，列显示所在区”，“销售额”显示在数据Ko结果参见样例中的“销售统计”工作表。温馨提示： 选中“销售记录”工作表的任意单元格，选择“a三-剧薇虢瓢KnrCfflXH 单击“IS1,选择销售数据所在区域； 单击“IBI”，翻开“数据透视表和数据透视表视图向导之3”； 单击“.”按钮，翻开“数据透视表和数据透视表视图向导一布局”对话框，将“饮料名称”拖到左边图形的“行”上，“所在区”拖到“列”上，销售额拖到“数据”中，如以下图所示； 单击“标”，数据透视表显示位置：“建工作表”。 单击，剧!”，将数据透视表命
18、名为“销售统计”。*欧阳光明*创编2023.03.07(2)依据数据透视表找出销售额最大的饮料对毛利润按降序排序，找出销售额最大的三种饮料。结果参见样例中的“销售统计”工作表温馨提示：选中“总计”列的数值局部(不包含最终一行数值)然后单击工具栏中的排序按钮进展排序。(3)在“销售统计”工作表中，找出各个区“销售额”最大的饮料在“销售统计”工作表中，用MAX函数找出每个区“销售额”最大的饮料的“销售额工在“销售统计”工作表中，用VLOOKUP函数找出各区“最大销售额”所对应的“饮料名称”。STEP4-用“两轴线.柱图”比较“销售额%“毛利润”设计目标现在，小李想用“两轴线-柱图”比较“南山区工“
19、福田区”和“罗湖区”的销售额和毛利润之间的关系。如以下图所示，用两轴线柱图表来比较销售额和毛利润：(1)制作，两轴线图”在表“所在区汇总”分别选择“所在区”、“销售额”和“毛利润”三列如以下图所示；单击“图表向导”按钮，翻开“图表向导4图表类型”对话框，选择“自定义类型”选项卡，选择“两轴线-柱图工*欧阳光明*创编2023.03.07 单击“下一步”，翻开“图表向导4图表源数据”对话框； 单击“下一步”，翻开“图表向导4图表选项”对话框，按以下图进展设置； 单击“下一步”，在“图表位置”对话框中，选择“作为其中的对象插入”，单击“完成”按钮。(2)美化图表 参照“饮料销售(样例Is，完成对图表
20、格式的设置格式可以自定义。STEP5利用数据有效性制作更便利、更有用的“销售记录”工作表设计目标在“饮料销售.xls”中制作一张“销售记录表”，并应用数据有效性设置，使得在填写了销售“数量”和选取了“饮料名称”后，可以自动计算出“销售额，“毛利润”和“毛利率工(1)制作“销售记录”表副本 在“饮料销售.ls”中建立一个“销售记录”表的副本，并将其重命名为“销售记录”，然后将其前五列的内容删除只保存标题行，如以下图所示。 并增加3个列“实收”、“应收”和“找回”(2)对“饮料名称”应用数据有效性设置 选中“饮料价格”表中的“饮料名称”区域，并将其定义为“饮料名称二如以下图所示。*欧阳光明*创编2
21、023.03.07 选中“销售记录”的第4列“饮料名称”列，然后再选择“数据”一“有效性”翻开数据有效性对话框。 在有效性条件中选择“序列”。 在“来源”中填写“=饮料名称”留意：“饮料名称”是定义的“饮料名称”区域，如图下所示。(3)数据有效性的使用制作完成，如以下图所示，试试看，是不是在填写了销售“数量”和选取了“饮料名称”后，可以自动计算出“销售额二“毛利润”和“毛利率二很便利吧！(4)对“饮料店”列进展数据有效性设置参照“饮料名称”的设置对“饮料店”进展设置(5)创立“应收”和“找回”列的公式，算法如下：应收避肖售额，找回=实收-应收。STEP6让查找公式更完善(1)隐蔽列在“销售记录
22、”工作表中将不需要显示的列“单位”、“进价”、“销售额”隐蔽起来。(2)前面的结果有缺陷在上面应用了查找函数VLOOKUP的“销售记录”表中，假设把D3单元格中的饮料名称删去以后，可以看到F3、G3、H3单元格即：单位、售价和进价中均返回错误值#N/A”。*欧阳光明*创编2023.03.07大家可以试一下，当VLOOKUP函数在“饮料价格”表中没有找到D3单元格中的饮料名称时都要返回错误值N/A”。能不能让当VLOOKUP函数在“饮料信息”表中没有找到D3单元格中的饮料名称时不返回错误值#N/A”,而只什么都不显示即显示空格呢？问题：利用IF函数和ISERROR函数，使“销售额工”毛收入”和“
23、毛利润”三列的值，在没有输入饮料名称时，不显示“#VALUEF错误值。解决方法利用IF和ISERROR函数可以解决上面的问题。试试看，你能解决这个问题吗？上面的问题变成两种状况：假设当VLOOKUP(D3,ylmc,2,FALSE)”局部返回错误值的话，则在F3中显示空字符串。假设“VLOOKUP(D3,ylmc,2,FALSE厂局部工作正常，则在F3单元格中显示饮料对应的“售价”即VLooKUP(D3,ylmc,2,FALSE)的值o妹温馨提示：ISERROR(Value)函数：当变量value是错误值“甘N/A”时，返回规律真(TRUE),因此，当它与函数IF结合在一起使用时，可以用于在公
24、式中查出错误值。IF函数与ISERROR函数联合使用的通用表示法：IF(ISERR0R()STEP7冻结窗口保存标题行在“销售记录”工作表中，当销售记录增多后，为了保存标题行的内容，可单击Al单元格，执行“窗口”一“冻结窗口”命令，当滚动屏幕时，可以把第一行即表头行的内容始终保存在窗口中。4.重点难点重点 VLOOKUP函数的使用。 区域名称的定义 分类汇总 数据透视表(2)难点VLOoKUP函数的参数的选择数据透视表5 .案例总结与常见问题本案例通过对学生信息表数据的处理，介绍了查找与引用类函数VLOoKUP的用法及分类汇总和数据透视表的用法。一你知道了吗？通过本案例的学习，你能答复如下问题
25、吗？1 .VLoe)KUP国数是干什么的？2 .用VLOOKUPM数进展查找时，所要查找的元素为什么肯定要在所定义的数据区域的第一列？3 .VLOOKUP函数中其次个参数的含义是什么？在定义其次个参数时要留意什么？4 .在对VLOoKPUM数沿列进展复制时，一般状况下，为什么列标要用确定地址？5,如何定义数据区域，如何删除数据区域？6,用VLoOKPU函数时，可以不定义数据区域吗？7 .什么是分类汇总、？它有什么作用？8 .在分类汇总之前要留意什么？二常见问题及处理方法下面把大家在本案例的学习过程中简洁遇到的一些问题及处理方法列于下表:常见问题可能缘由处理方法“数据区域”名称无法重定义。该“数
26、据区域名称已被定义。将其删除，重定义，方法：选择“插入”一“名称”一“定义”，翻开“定义名称”对话框，选择已定义的名称，单击“删除”命令。VLOOKUP函数返回错误值（1）没有把要查找的对象定义在“数据区域”的第一列。重定义“数据区域”，把要查找的内容定义在“数据区域”的第一列。（2）查找的内容在定义的用ISERROR函数使错误值不显不O使用“填充柄”沿列拖动复制公式时消灭错误值“数据区域”中不存在。要查找的对象（VLe）OKUP函数的第一个参数）列标没在使用“填充柄”沿列拖动前先将第一个参数的列标确定引用。分类汇总结果不正确。有用确定引用。（1）在“分类汇总之前没先先按要分类的字段排序，然后
27、再进展“分类汇总按要“分类”的字段排序。（2）虽然己先按要“分类”的字段排序，但在“分类汇总”时，分类字段选择不正确（没有选择己排序的字在“分类汇总”时，“分类”字段选择已排序的字段。段）。6 .课后作业：学生成绩的查询和统计请大家用查找函数VLOOKUP完成下面的案例。（1）问题的提出：学期刚开头，班主任王教师遇到了一个难题，学校依据学生填报的志愿重分了班。教务处给出了一个只包含班级学生的学号的Excel表格，见素材。现在他要填写班级学生的信息。依据以往的阅历，他只能一个一个去查找然后粘贴了，但这既简洁出错，工作量又很大。于是他向教EXCel的老师请教，经过教师的教导，才知道用VLooKUP
28、可以很简洁地解决这个问题。要求：*欧阳光明*创编（八）在表“一班呻,依据学号,2023.03.07查找“姓名”、“大学英语”和总分。(B)另外学校要对上学期“大学英语”考试成绩是“优秀”和“良好”的学生进展嘉奖。嘉奖方法见素材中表“奖品及加分”，其中“大学英语”考试成绩是“优秀”的同学总分增加2%、“良好”的总分增加1%。(2)解决方法请利用“学生信息(素材)Ms”,在表“一班”中，依据学号，应用VLOOKUP函数填写出一班学生的“姓名”、大学英语”和“总分”，对没有成绩的填写“缺考”。用IF函数填写一班学生的“英语等级”，对缺考的学生填写“英语缺考”。英语成绩=90,“优秀”80V=英语成绩90,“良好”70二英语成绩80,“一般”60二英语成绩70,“及格”英语成绩60,“不及格”依据表“奖品及加分”，用VLOOKUP函数计算一班学生的“奖品”和“加分率”，其中“加分率”用百分比格式表示。用IF和ISERRoRm数，使英语等级不是“优秀”和“良好”的学生的“奖品”为空、“加分率”是0,而不显示错误值“#N/A”。计算出每个学生的“总分”。提示：总分=总分X+加分率。
