2023年ecel销售数据分析-实训练习.docx

《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)呻按“所在区”对销售额和毛利润进展分类温馨提示：选择“所在区”列中的任意单元格单击“升序”按钮冽进展排序(排序的作用是什么？)然后选择“数据”一“分类汇总”.汇忘，汇忘结果显小仕数据卜万结果见“(恃例)饮料销售.xls”o将销售记录(2)改名为“所在区汇总”结果参见样例中的工作表“所在区汇总”(2)依据“饮料名称”进展分类汇总在表“销售记录(3)呻按“饮料名称”对销售额和毛利润进展分类汇总汇总方式为求和对汇总结果中的毛利润列按降序排序，找出毛利润最大的饮料。招销售记录(