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

《excel销售数据分析-实训练习4129.docx》由会员分享，可在线阅读，更多相关《excel销售数据分析-实训练习4129.docx（23页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、EXCEEL实训训练习三商场销售售数据的的分析处处理1. 问问题的提提出小李在深深圳市开开了若干干家饮料料连锁店店，为了了提高管理理水平，他打算算用Exxcell工作表表来管理理销售数数据。下下图是他他制作的的各饮料料店的销销售记录录流水帐帐表。为了统计计“毛利润润”，他必须须去“饮料基基本信息息”表中查找找每种饮饮料的“进价”和“售价”。这个个工作量量实在太太大，而而且还容容易出错错。现在希望望：能否输输入饮料料名称后后，让EExceel根据据这个名名称自动动去查找找该饮料料的“单位”、“进价”、“售价”等信息息，并存放到表“销售记记录”的相应列中中。2解决决方案通常情况况下，如如果不借借助

2、其它它方法的的帮助，要要想在EExceel中解解决这个个问题，只只能到“饮料基基本信息息”表中一一条一条条地查找找各种饮饮料的“进价”和“售价”。如果果不想这这么做，你你有什么么更好的的办法吗吗？这个实际际需求，开开发Exxcell的工程程师，已已经为我我们想到到了。在Exxcell中有一一个函数数，就是是专门为为解决这这类问题题设计的的，这个个函数就就是VLLOOKKUP。小李这个个问题，可可利用EExceel中的的查找函数数VLOOOKUUP来解决。它它的功能能是，在在数据区区域的第第一列中中查找指指定的数数值，并并返回数数据区域域当前行行中指定定列处的的数值。下面来看看应用VLOOKUP

3、函数是如何解决上述问题的。3. 实现方方法本案例要要解决如如下几个个问题：1在“销售记记录”工作表表中用VVLOOOKUPP函数计计算饮料料的“单位”、“进价”和“售价”等信息息，并计算算出工作作表中的的“销售额额”和“毛利润润”等信息息。2用“分类汇汇总”统计出出各连锁锁店和各各个区中中各种饮饮料的“销售额额”、“毛利润润”。3用“数据透透视表”分析各各个区中中每种饮饮料的销销售情况况和各个个区中销售情情况最好好的饮料料。4另外外，为了了提高效效率、避免出出错，小李还还想制作作一张可可以从下下拉列表表中选择择饮料名名称，并并能自动计计算出顾顾客应交交款及应应找回款款的“新销售记录录”工作表。

4、4制作作过程STEPP 1VLOOOKUUP函数数的使用用设计目标标参照下下图，根根据“销售记记录”表中的的“饮料名名称”列，利利用VLLOOKKUP函函数在“饮料基基本信息息”表中查查找其他他列（单单位、进进价和售售价）的的值。根据以上两个表中的数据得到下图中的结果最后计算销售额、毛利润、毛利率（1）VVLOOOKUPP函数是是干什么么用的 VLOOOKUPP函数的的功能：查找数据据区域首首列满足足条件的的元素，并并返回数数据区域域当前行行中指定定列处的的值。 VLOOOKUPP的语法法：VLOOKUP(lookup_value,table_array,col_index_num,range

5、_lookup)精确查找还是模糊查找？查找区域第几列的值？到哪个区域查找？查找什么？注意：要查找的的对象（参参数）一定定要定义义在查找找数据区区域（参参数）的第第一列。下面来看看看VLLOOKKUP函函数是怎怎么用的的。（2）如如何查找找“单位”？ 打开文件件“饮料销销售(素素材).xlss”，将文件件另存为为“姓名_饮料销销售.xxls”。 在“销售售记录”工作表表中，选中中F3单元元格选择“插入函数VLOOOKUUP”单击“确定”。 由于要根根据饮料料的名称称查找“单位”，所以VLLOOKKUP函函数的第第一个参参数应该该选择饮饮料名称称“D3”。 在“Taablee_arrrayy”区域

6、中中选择“饮料价格格”工作表表中的BB2:E444。 由于“单单位”数据存存放在“饮料信信息”数据区区域的第第2列，所所以输入入数字“2”。 由于要求求饮料名名称精确确匹配，所以最后一个参数输入“FALSE”。 单击“确确定”，可以看看到函数数准确地地返回了了“统一奶奶茶”的单位“瓶”。 复制公式式,出现现了什么么问题？为为什么？如何解解决呢？提示：注注意在复复制公式式时，如如果沿着着列拖动动时，列列标要用用绝对引引用，想想想看为为什么？ 修改公式式后，重重新复制制公式。（3）创创建区域域名称 选择“饮饮料名称称”工作表表。 选中饮料料名称、单单位、进进价、售售价所在在的区域域，即单单元格区区

7、域B2:E444。F温馨提示：也可以采用以下方法定义区域名称： 选择菜单“插入名称定义”命令，打开“定义名称”对话框， 在名称框中输入“姓名A”后，单击“确定”按钮，“姓名A”区域名称创建完成 在名称框框中输入入“自己的的姓名A”，一定定要按回回车确认认。思考问题题：（1）区区域的定定义可以以包含“序号”列吗？为什么么？（2）如如果名称称定义错错误了，如如何将其其删除。（4）利利用区域域名查找找“进价”和“售价” 选择“销销售记录录”工作表表选中G33单元格格输入等等号“=”选择函函数“VLOOOKUUP”单击“确定”。 由于要根根据饮料料的名称称查找“售价”，所以VLLOOKKUP函函数的第

8、第一个参参数应该该选择饮饮料名称称“D3”。 在“Taablee_arrrayy”区域中中输入“姓名AA”。 由于“进进价”数据存存放在第第3列，所所以输入入数字“3”。 由于要求求饮料名名称精确确匹配，所以最后一个参数输入“FALSE”怎么样，对于VLOOKUP函数你是否有了一定的认识？如果还不会，请多做几遍，慢慢去体会。 单击“确确定”，可以看看到函数数准确地地返回了了“统一奶奶茶”的“进价”数据“1.99”。 复制公式式，看一一看有什什么不同同？ 同样道理理在H3单元格格中建立立查找饮饮料名称称“售价”的公式式。（5）计计算销售售额、毛毛利润F温馨提示： 表格中数据之间的关系为：销售额

9、售价*数量毛利润 (售价进价)数量STEPP 2分类汇汇总首先，建建立表“销售记记录”的二个个副本“销售记记录(22)”、“销售记记录(33)”和“销售记记录(44)”。F温馨提示： 按住Ctrl键拖动工作表标签（1）按按照“所在区区”进行分分类汇总总F温馨提示： 选择“所在区”列中的任意单元格 单击“升序”按钮进行排序（排序的作用是什么？） 然后选择“数据”“分类汇总”。 在表“销销售记录录(2)”中按“所在区区”对销售售额和毛毛利润进进行分类类汇总，汇汇总结果果显示在在数据下下方（结结果见“(样例)饮料销销售.xxls”）。 将销售记记录(22)改名名为“所在区区汇总” 结果参见见样例中中

10、的工作作表“所在在区汇总总”（2）按按照“饮料名名称”进行分分类汇总总 在表“销销售记录录(3)”中按“饮料名称称”对销售额额和毛利润润进行分分类汇总总（汇总总方式为为求和） 对汇总结结果中的的毛利润润列按降序排序序，找出出毛利润润最大的的饮料。 将销售记记录(33)改名为为“饮料名名称汇总总” 结果参见见样例中中的工作作表“饮料料名称汇总总”F温馨提示： 首先按照“饮料名称”对“销售额”和“毛利润”进行分类汇总； 然后，对“毛利润”进行降序排序。（3）用用“嵌套分分类汇总总”统计各各个区和和各饮料料店的饮饮料“销售额额”和“毛利润润” 在“销售售记录(4)”工作表表中对“主要关关键字”选择“

11、所在区区”，“次要关关键字”选择“饮料店店”排序。 进行第一一次“分类汇汇总”（分类类字段为为“所在区区”）。 进行第二二次“分类汇汇总”（分类类字段为为“饮料店店”）。 将销售记记录(44)改名为为“嵌套汇总总”F温馨提示： 首先，选择“数据”“排序”命令，在主要关键字中选择“所在区”，在次要关键字中选择“饮料店”； 然后，先按照“所在区”进行分类汇总； 再按照“饮料店”进行汇总，但是第二次汇总时，必须取消“替换当前分类汇总”。 结果参见见样例中中的工作作表“嵌套套汇总”STEPP 3利用数数据透视视表实现现统计分分析设计目标标如下图所所示，利利用数据据透视表表生成三三维数据据统计报报表。利

12、用排序，找出销售额最大的饮料（1）为为“销售记记录”表创建数数据透视视表要求：为表“销销售记录录”建立数数据透视视表，将将数据透透视表显显示在新新工作表表中，并并将数据据透视表表命名为为“销售统统计”行显示“饮料名名称”，列显显示“所在区区”，“销售额额”显示在在数据区区。结果参参见样例例中的“销售统统计”工作表表。F温馨提示： 选中“销售记录”工作表的任意单元格，选择“数据”“数据透视表和数据透视图”； 单击“下一步”，选择销售数据所在区域； 单击“下一步”，打开“数据透视表和数据透视表视图向导之3”； 单击“布局”按钮，打开“数据透视表和数据透视表视图向导布局”对话框，将“饮料名称”拖到左

13、边图形的“行”上，“所在区”拖到“列”上，销售额拖到“数据”中，如下图所示； 单击“确定”，数据透视表显示位置：“新建工作表”。 单击“完成”， 将数据透视表命名为“销售统计”。（2）根根据数据据透视表表找出销销售额最最大的饮饮料 对毛利润润按降序序排序，找找出销售售额最大大的三种种饮料。 结果参见见样例中中的“销售统统计”工作表表F 温馨提示： 选中“总计”列的数值部分（不包含最后一行数值） 然后单击工具栏中的排序按钮进行排序。（3）在在“销售统统计”工作表表中，找找出各个个区“销售额额”最大的的饮料 在“销售售统计”工作表表中，用用MAXX函数找找出每个个区“销售额额”最大的的饮料的的“销

14、售额额”。 在“销售售统计”工作表表中，用用VLOOOKUUP函数数找出各各区“最大销销售额”所对应应的“饮料名名称”。STEPP 4用“两轴线线-柱图图”比较“销售额额”和“毛利润润”设计目标标现在，小小李想用用“两轴线线-柱图图”比较“南山区区”、“福田区区”和“罗湖区区”的销售额额和毛利润润之间的的关系。如下图所所示，用用两轴线线柱图表表来比较较销售额额和毛利利润：（1）制制作“两轴线线图” 在表“所所在区汇汇总”分别选选择“所在区区”、“销售额额”和“毛利润润”三列（如如下图）所所示； 单击“图图表向导导”按钮，打打开“图表向向导-44-图表表类型”对话框框，选择择“自定义义类型”选项

15、卡卡，选择择“两轴线线-柱图图”。 单击“下下一步”，打开开“图表向向导-44-图表表源数据据”对话框框； 单击“下下一步”，打开开“图表向向导-44-图表表选项”对话框框，按下下图进行行设置； 单击“下下一步”，在“图表位位置”对话框框中，选选择“作为其其中的对对象插入入”，单击击“完成”按钮。（2）美美化图表表 参照“饮饮料销售售(样例例).xxls”，完成成对图表表格式的设设置（格格式可以以自定义义）。STEPP 5利用数数据有效效性制作作更方便便、更实实用的“新销售售记录”工作表表设计目标标在“饮料料销售.xlss”中制作作一张“新销售记录录表”，并应应用数据据有效性性设置，使使得在填

16、填写了销销售“数量”和选取取了“饮料名名称”后，可可以自动动计算出出“销售额额”、“毛利润润”和“毛利率”。（1）制制作“销售记记录”表副本本 在“饮料料销售.xlss”中建立立一个“销售记记录”表的副副本，并并将其重重命名为为“新销售记录录”，然后后将其前前五列的内内容删除除（只保保留标题题行），如下图所示。 并增加33个新列列“实收”、“应收”和“找回”（2）对对“饮料名名称”应用数据据有效性性设置 选中“饮饮料价格格”表中的的“饮料名名称”区域，并并将其定定义为“饮料名名称”，如下图所示示。 选中“新新销售记记录”的第4列（“饮料名名称”列），然然后再选选择“数据”“有效效性”打开数数据

17、有效效性对话话框。 在有效性性条件中中选择“序列”。 在“来源源”中填写写“=饮料料名称”（注意意：“饮料名名称”是定义义的“饮料名名称”区域），如如图下所示。（3）数数据有效效性的使使用制作完成成，如下图所示示，试试试看，是是不是在在填写了了销售“数量”和选取取了“饮料名名称”后，可可以自动动计算出出“销售额额”、“毛利润润”和“毛利率”，很方方便吧！（4）对对 “饮料店店”列进行行数据有有效性设设置参照“饮饮料名称称”的设置置对“饮料店店”进行设设置（5）创创建 “应收”和“找回”列的公式式，算法法如下：应收=销售额额，找回回=实收收应收。STEPP 6让查找找公式更完完美（1）隐隐藏列在

18、“新销销售记录录”工作表表中将不不需要显显示的列列（“单位”、“进价”、“销售额额”）隐藏藏起来。（2）前前面的结结果有缺缺陷在上面应应用了查查找函数数VLOOOKUUP的“销售记记录”表中，如如果把D3单元元格中的的饮料名名称删去去以后，可可以看到到F3、G3、H3单元元格（即即：单位位、售价价和进价价）中均返回错错误值“#N/A”。大家可以以试一下下，当VVLOOOKUPP函数在在“饮料价格格”表中没没有找到到D3单元元格中的的饮料名名称时都都要返回回错误值值“#N/A”。能不能让让当VLLOOKKUP函函数在“饮料信信息”表中没没有找到到D3单元元格中的的饮料名名称时不不返回错错误值“#

19、N/A”，而只只什么都都不显示示（即显显示空格格）呢？问题：利用IFF函数和和ISEERROOR函数数，使“销售额额”、“毛收入入”和“毛利润润”三列的的值，在在没有输输入饮料料名称时时，不显显示“#VAALUEE!”错误值值。（2）解解决办法法F利用IFF和ISSERRROR函函数可以以解决上上面的问问题。试试试看，你你能解决决这个问问题吗？上面的问问题变成成两种情情况： 如果当“VLOOOKUUP(DD3,yylmcc,2,FALLSE)”部分返返回错误误值的话话，则在在F3中显显示空字字符串。 如果“VVLOOOKUPP(D33,yllmc,2,FFALSSE)”部分工工作正常常，则在在

20、F3单元元格中显显示饮料料对应的的“售价”（即VLLOOKKUP(D3,ylmmc,22,FAALSEE)的值值）。F温馨提示：ISERROR(value)函数：当变量value是错误值“#N/A”时，返回逻辑真（TRUE），因此，当它与函数 IF 结合在一起使用时，可以用于在公式中查出错误值。IF函数与ISERROR函数联合使用的通用表示法:IF(ISERROR(VALUE),”,VALUE)STEPP 7冻结窗窗口保留留标题行行在“销售售记录”工作表表中，当销售售记录增增多后，为为了保留留标题行行的内容容，可单单击A11单元格格，执行行“窗口”“冻结结窗口”命令，当当滚动屏屏幕时，可可以把

21、第第一行（即即表头行行）的内内容一直直保留在在窗口中中。4.重点点难点（1） 重点l VLOOOKUPP函数的的使用。l 区域名称称的定义义l 分类汇总总l 数据透视视表（2） 难点l VLOOOKUPP函数的的参数的的选择l 数据透视视表5.案例例总结与与常见问问题本案例通通过对学学生信息息表数据据的处理理，介绍绍了查找找与引用用类函数数VLOOOKUUP的用用法及分分类汇总总和数据据透视表表的用法法。（一）你你知道了了吗？通过本案案例的学学习，你你能回答答如下问问题吗？1VLLOOKKUP函函数是干干什么的的？2用VVLOOOKUPP函数进进行查找找时，所所要查找找的元素素为什么么一定要要

22、在所定定义的数数据区域域的第一一列？3VLLOOKKUP函函数中第第二个参参数的含含义是什什么？在在定义第第二个参参数时要要注意什什么？4在对对VLOOOKPPU函数数沿列进进行复制制时，一一般情况况下，为为什么列列标要用用绝对地地址？5如何何定义数数据区域域，如何何删除数数据区域域？6用VVLOOOKPUU函数时时，可以以不定义义数据区区域吗？7什么么是分类类汇总？它有什什么作用用？8在分分类汇总总之前要要注意什什么？（二）常常见问题题及处理理方法下面把大大家在本本案例的的学习过过程中容容易遇到到的一些些问题及及处理方方法列于于下表：常见问题题可能原因因处理方法法“数据区区域”名称无无法重新

23、新定义。该“数据据区域”名称已已被定义义。将其删除除，重新新定义，方方法：选择“插插入”“名称”“定义”，打开开“定义名名称”对话框框，选择择已定义义的名称称，单击击“删除”命令。VLOOOKUPP函数返返回错误误值“#N/A”。（1）没没有把要要查找的的对象定定义在“数据区区域”的第一一列。重新定义义“数据区区域”，把要要查找的的内容定定义在“数据区区域”的第一一列。（2）查查找的内内容在定定义的“数据区区域”中不存存在。用ISEERROOR函数数使错误误值不显显示。使用“填填充柄”沿列拖拖动复制制公式时时出现错错误值“#N/A”。要查找的的对象（VVLOOOKUPP函数的的第一个个参数）列

24、列标没有有用绝对对引用。在使用“填充柄柄”沿列拖拖动前先先将第一一个参数数的列标标绝对引引用。分类汇总总结果不不正确。（1）在在“分类汇汇总”之前没没先按要要“分类”的字段段排序。先按要分分类的字字段排序序，然后后再进行行“分类汇汇总”。（2）虽虽然已先先按要“分类”的字段段排序，但但在“分类汇汇总”时，分分类字段段选择不不正确（没没有选择择已排序序的字段段）。在“分类类汇总”时，“分类”字段选选择已排排序的字字段。6.课后后作业：学生成成绩的查查询和统统计请大家用用查找函函数VLLOOKKUP完完成下面面的案例例。（1）问问题的提提出： 新学期刚刚开始，班班主任王王老师遇遇到了一一个难题题，

25、学校校根据学学生填报报的志愿愿重分了了班。教教务处给给出了一一个只包包含新班班级学生生的学号号的Exxcell表格，见见素材。现现在他要要填写新新班级学学生的信信息。根根据以往往的经验验，他只只能一个个一个去去查找然然后粘贴贴了，但但这既容容易出错错，工作作量又很很大。于于是他向向教Exxcell的老师师请教，经经过老师师的指点点，才知知道用VVLOOOKUPP可以很很容易地地解决这这个问题题。 要求：（A）在在表“一班(新)”中，根根据学号号，查找找“姓名”、“大学英英语”和总分分。（B）另另外学校校要对上上学期“大学英英语”考试成成绩是“优秀”和“良好”的学生生进行奖奖励。奖奖励办法法见素

26、材材中表“奖品及及加分”，其中中“大学英英语”考试成成绩是“优秀”的同学学总分增增加2%、“良好”的总分分增加11%。（2） 解决办办法 请利用“学生信信息(素素材).xlss”，在表“一班(新)”中，根根据学号，应用用VLOOOKUUP函数数填写出出一班学学生的“姓名”、“大学英英语”和“总分”，对没没有成绩绩的填写写“缺考”。 用IF函函数填写写一班学学生的“英语等等级”，对缺缺考的学学生填写写“英语缺缺考”。英语成绩绩=990，“优秀”80=英语成成绩990，“良好”70=英语成成绩880，“一般”60=英语成成绩770，“及格”英语成绩绩600，“不及格格” 根据表“奖品及及加分”，用VVLOOOKUPP函数计计算一班班学生的的“奖品”和“加分率率”，其中中“加分率率”用百分分比格式式表示。 用IF和和ISEERROOR函数数，使英英语等级级不是“优秀”和“良好”的学生生的“奖品”为空、“加分率率”是0，而而不显示示错误值值“#N/A”。 计算出每每个学生生的“新总分分”。提示：新总分分=总分分（1+加分率率）。23