ecel销售数据分析-实训练习.doc

《ecel销售数据分析-实训练习.doc》由会员分享，可在线阅读，更多相关《ecel销售数据分析-实训练习.doc（13页珍藏版）》请在三一办公上搜索。

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

2、有什么更好的办法吗？这个实际需求，开发E*cel的工程师，已经为我们想到了。在E*cel中有一个函数，就是专门为解决这类问题设计的，这个函数就是VLOOKUP。小李这个问题，可利用E*cel中的查找函数VLOOKUP来解决。它的功能是，在数据区域的第一列中查找指定的数值，并返回数据区域当前行中指定列处的数值。下面来看看应用VLOOKUP函数是如何解决上述问题的。3. 实现方法本案例要解决如下几个问题：1在销售记录”工作表中用VLOOKUP函数计算饮料的单位”、进价”和售价”等信息，并计算出工作表中的销售额”和毛利润”等信息。2用分类汇总”统计出各连锁店和各个区中各种饮料的销售额”、毛利润”。3

3、用数据透视表”分析各个区中每种饮料的销售情况和各个区中销售情况最好的饮料。4另外，为了提高效率、避免出错，小李还想制作一张可以从下拉列表中选择饮料名称，并能自动计算出顾客应交款及应找回款的新销售记录”工作表。4制作过程STEP 1VLOOKUP函数的使用设计目标参照下图，根据销售记录”表中的饮料名称”列，利用VLOOKUP函数在饮料基本信息”表中查找其他列（单位、进价和售价）的值。根据以上两个表中的数据得到下图中的结果最后计算销售额、毛利润、毛利率（1）VLOOKUP函数是干什么用的 VLOOKUP函数的功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。 VLOOKUP

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

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

6、输入姓名A”后，单击确定”按钮，姓名A”区域名称创建完成 在名称框中输入自己的姓名A”，一定要按回车确认。思考问题：（1）区域的定义可以包含序号”列吗？为什么？（2）如果名称定义错误了，如何将其删除。（4）利用区域名查找进价”和售价” 选择销售记录”工作表选中G3单元格输入等号=”选择函数VLOOKUP”单击确定”。 由于要根据饮料的名称查找售价”，所以VLOOKUP函数的第一个参数应该选择饮料名称D3”。 在Table_array”区域中输入姓名A”。 由于进价”数据存放在第3列，所以输入数字3”。 由于要求饮料名称精确匹配，所以最后一个参数输入FALSE”怎么样，对于VLOOKUP函数你是

7、否有了一定的认识？如果还不会，请多做几遍，慢慢去体会。 单击确定”，可以看到函数准确地返回了统一奶茶”的进价”数据1.9”。 复制公式，看一看有什么不同？ 同样道理在H3单元格中建立查找饮料名称售价”的公式。（5）计算销售额、毛利润F温馨提示： 表格中数据之间的关系为：销售额 售价*数量毛利润 (售价进价)数量STEP 2分类汇总首先，建立表销售记录”的二个副本销售记录(2)”、销售记录(3)”和销售记录(4)”。F温馨提示： 按住Ctrl键拖动工作表标签（1）按照所在区”进行分类汇总F温馨提示： 选择所在区”列中的任意单元格 单击升序”按钮进行排序（排序的作用是什么？） 然后选择数据”分类汇

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

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

10、售记录”表创建数据透视表要求：为表销售记录”建立数据透视表，将数据透视表显示在新工作表中，并将数据透视表命名为销售统计”行显示饮料名称”，列显示所在区”，销售额”显示在数据区。结果参见样例中的销售统计”工作表。F温馨提示： 选中销售记录”工作表的任意单元格，选择数据”数据透视表和数据透视图”； 单击下一步”，选择销售数据所在区域； 单击下一步”，打开数据透视表和数据透视表视图向导之3”； 单击布局”按钮，打开数据透视表和数据透视表视图向导布局”对话框，将饮料名称”拖到左边图形的行”上，所在区”拖到列”上，销售额拖到数据”中，如下图所示； 单击确定”，数据透视表显示位置：新建工作表”。 单击完成

11、”， 将数据透视表命名为销售统计”。（2）根据数据透视表找出销售额最大的饮料 对毛利润按降序排序，找出销售额最大的三种饮料。 结果参见样例中的销售统计”工作表F 温馨提示： 选中总计”列的数值部分（不包含最后一行数值） 然后单击工具栏中的排序按钮进行排序。（3）在销售统计”工作表中，找出各个区销售额”最大的饮料 在销售统计”工作表中，用MA*函数找出每个区销售额”最大的饮料的销售额”。 在销售统计”工作表中，用VLOOKUP函数找出各区最大销售额”所对应的饮料名称”。STEP 4用两轴线-柱图”比较销售额”和毛利润”设计目标现在，小李想用两轴线-柱图”比较南山区”、福田区”和罗湖区”的销售额和

12、毛利润之间的关系。如下图所示，用两轴线柱图表来比较销售额和毛利润：（1）制作两轴线图” 在表所在区汇总”分别选择所在区”、销售额”和毛利润”三列（如下图）所示； 单击图表向导”按钮，打开图表向导-4-图表类型”对话框，选择自定义类型”选项卡，选择两轴线-柱图”。 单击下一步”，打开图表向导-4-图表源数据”对话框； 单击下一步”，打开图表向导-4-图表选项”对话框，按下图进行设置； 单击下一步”，在图表位置”对话框中，选择作为其中的对象插入”，单击完成”按钮。（2）美化图表 参照饮料销售(样例).*ls”，完成对图表格式的设置（格式可以自定义）。STEP 5利用数据有效性制作更方便、更实用的新

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

14、列”。 在来源”中填写=饮料名称”（注意：饮料名称”是定义的饮料名称”区域），如图下所示。（3）数据有效性的使用制作完成，如下图所示，试试看，是不是在填写了销售数量”和选取了饮料名称”后，可以自动计算出销售额”、毛利润”和毛利率”，很方便吧！（4）对 饮料店”列进行数据有效性设置参照饮料名称”的设置对饮料店”进行设置（5）创建 应收”和找回”列的公式，算法如下：应收=销售额，找回=实收应收。STEP 6让查找公式更完美（1）隐藏列在新销售记录”工作表中将不需要显示的列（单位”、进价”、销售额”）隐藏起来。（2）前面的结果有缺陷在上面应用了查找函数VLOOKUP的销售记录”表中，如果把D3单元格

15、中的饮料名称删去以后，可以看到F3、G3、H3单元格（即：单位、售价和进价）中均返回错误值#N/A”。大家可以试一下，当VLOOKUP函数在饮料价格”表中没有找到D3单元格中的饮料名称时都要返回错误值#N/A”。能不能让当VLOOKUP函数在饮料信息”表中没有找到D3单元格中的饮料名称时不返回错误值#N/A”，而只什么都不显示（即显示空格）呢？问题：利用IF函数和ISERROR函数，使销售额”、毛收入”和毛利润”三列的值，在没有输入饮料名称时，不显示#VALUE!”错误值。（2）解决办法F利用IF和ISERROR函数可以解决上面的问题。试试看，你能解决这个问题吗？上面的问题变成两种情况： 如果

16、当VLOOKUP(D3,ylmc,2,FALSE)”部分返回错误值的话，则在F3中显示空字符串。 如果VLOOKUP(D3,ylmc,2,FALSE)”部分工作正常，则在F3单元格中显示饮料对应的售价”（即VLOOKUP(D3,ylmc,2,FALSE)的值）。F温馨提示：ISERROR(value)函数：当变量value是错误值#N/A”时，返回逻辑真（TRUE），因此，当它与函数 IF 结合在一起使用时，可以用于在公式中查出错误值。IF函数与ISERROR函数联合使用的通用表示法:IF(ISERROR(VALUE),”,VALUE)STEP 7冻结窗口保留标题行在销售记录”工作表中，当销售

17、记录增多后，为了保留标题行的内容，可单击A1单元格，执行窗口”冻结窗口”命令，当滚动屏幕时，可以把第一行（即表头行）的内容一直保留在窗口中。4. 重点难点（1） 重点l VLOOKUP函数的使用。l 区域名称的定义l 分类汇总l 数据透视表（2） 难点l VLOOKUP函数的参数的选择l 数据透视表5. 案例总结与常见问题本案例通过对学生信息表数据的处理，介绍了查找与引用类函数VLOOKUP的用法及分类汇总和数据透视表的用法。（一）你知道了吗？通过本案例的学习，你能回答如下问题吗？1VLOOKUP函数是干什么的？2用VLOOKUP函数进行查找时，所要查找的元素为什么一定要在所定义的数据区域的第

18、一列？3VLOOKUP函数中第二个参数的含义是什么？在定义第二个参数时要注意什么？4在对VLOOKPU函数沿列进行复制时，一般情况下，为什么列标要用绝对地址？5如何定义数据区域，如何删除数据区域？6用VLOOKPU函数时，可以不定义数据区域吗？7什么是分类汇总？它有什么作用？8在分类汇总之前要注意什么？（二）常见问题及处理方法下面把大家在本案例的学习过程中容易遇到的一些问题及处理方法列于下表：常见问题可能原因处理方法数据区域”名称无法重新定义。该数据区域”名称已被定义。将其删除，重新定义，方法：选择插入”名称”定义”，打开定义名称”对话框，选择已定义的名称，单击删除”命令。VLOOKUP函数返

19、回错误值#N/A”。（1）没有把要查找的对象定义在数据区域”的第一列。重新定义数据区域”，把要查找的内容定义在数据区域”的第一列。（2）查找的内容在定义的数据区域”中不存在。用ISERROR函数使错误值不显示。使用填充柄”沿列拖动复制公式时出现错误值#N/A”。要查找的对象（VLOOKUP函数的第一个参数）列标没有用绝对引用。在使用填充柄”沿列拖动前先将第一个参数的列标绝对引用。分类汇总结果不正确。（1）在分类汇总”之前没先按要分类”的字段排序。先按要分类的字段排序，然后再进行分类汇总”。（2）虽然已先按要分类”的字段排序，但在分类汇总”时，分类字段选择不正确（没有选择已排序的字段）。在分类汇

20、总”时，分类”字段选择已排序的字段。6. 课后作业：学生成绩的查询和统计请大家用查找函数VLOOKUP完成下面的案例。（1）问题的提出： 新学期刚开始，班主任王老师遇到了一个难题，学校根据学生填报的志愿重分了班。教务处给出了一个只包含新班级学生的*的E*cel表格，见素材。现在他要填写新班级学生的信息。根据以往的经验，他只能一个一个去查找然后粘贴了，但这既容易出错，工作量又很大。于是他向教E*cel的老师请教，经过老师的指点，才知道用VLOOKUP可以很容易地解决这个问题。 要求：（A）在表一班(新)”中，根据*，查找”、大学英语”和总分。（B）另外学校要对上学期大学英语”考试成绩是优秀”和良

21、好”的学生进行奖励。奖励办法见素材中表奖品及加分”，其中大学英语”考试成绩是优秀”的同学总分增加2%、良好”的总分增加1%。（2） 解决办法 请利用学生信息(素材).*ls”，在表一班(新)”中，根据*，应用VLOOKUP函数填写出一班学生的”、大学英语”和总分”，对没有成绩的填写缺考”。 用IF函数填写一班学生的英语等级”，对缺考的学生填写英语缺考”。英语成绩=90，优秀”80=英语成绩90，良好”70=英语成绩80，一般”60=英语成绩70，及格”英语成绩60，不及格” 根据表奖品及加分”，用VLOOKUP函数计算一班学生的奖品”和加分率”，其中加分率”用百分比格式表示。 用IF和ISERROR函数，使英语等级不是优秀”和良好”的学生的奖品”为空、加分率”是0，而不显示错误值#N/A”。 计算出每个学生的新总分”。提示：新总分=总分（1+加分率）。. z.