excel销售数据分析-实训练习可编辑范本

PAGEPAGE1EＸCEL实训练习三商场销售数据的分析处理１．问题的提出小李在深圳市开了若干家饮料连锁店，为了提高管理水平，他打算用Excel工作表来管理销售数据。下图是他制作的各饮料店的销售记录流水帐表。为了统计“毛利润”，他必须去“饮料基本信息"表中查找每种饮料的“进价”和“售价”。这个工作量实在太大,而且还容易出错。现在希望:能否输入饮料名称后，让Ｅｘcｅl根据这个名称自动去查找该饮料的“单位”、“进价”、“售价”等信息,并存放到表“销售记录”的相应列中。2．解决方案通常情况下,如果不借助其它方法的帮助，要想在Exｃeｌ中解决这个问题,只能到“饮料基本信息”表中一条一条地查找各种饮料的“进价”和“售价”。如果不想这么做,你有什么更好的办法吗?这个实际需求，开发Ｅxcel的工程师,已经为我们想到了。在Eｘcel中有一个函数，就是专门为解决这类问题设计的,这个函数就是VLＯOKUP。小李这个问题，可利用Excel中的查找函数ＶLOOKUP来解决。它的功能是，在数据区域的第一列中查找指定的数值，并返回数据区域当前行中指定列处的数值.下面来看看应用VLＯOKUP函数是如何解决上述问题的。３。实现方法本案例要解决如下几个问题:1．在“销售记录”工作表中用VLOＯKＵP函数计算饮料的“单位”、“进价”和“售价”等信息,并计算出工作表中的“销售额"和“毛利润”等信息.2.用“分类汇总”统计出各连锁店和各个区中各种饮料的“销售额”、“毛利润"。3．用“数据透视表”分析各个区中每种饮料的销售情况和各个区中销售情况最好的饮料。4．另外,为了提高效率、避免出错，小李还想制作一张可以从下拉列表中选择饮料名称,并能自动计算出顾客应交款及应找回款的“新销售记录”工作表。4．制作过程STＥP1—VＬOOKUＰ函数的使用设计目标 参照下图，根据“销售记录"表中的“饮料名称＂列，利用VLOOKUP函数在“饮料基本信息”表中查找其他列(单位、进价和售价)的值。根据以上两个表中的数据得到下图中的结果根据以上两个表中的数据得到下图中的结果最后计算销售额、毛利润、毛利率最后计算销售额、毛利润、毛利率（1)VLＯOＫUP函数是干什么用的VＬOOKUP函数的功能：查找数据区域首列满足条件的元素,并返回数据区域当前行中指定列处的值。VLOＯKUP的语法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)④精确查找还是模糊查找④精确查找还是模糊查找？③查找区域第几列的值？②到哪个区域查找？①查找什么？注意:要查找的对象（参数①)一定要定义在查找数据区域（参数②）的第一列。下面来看看ＶLOOＫＵP函数是怎么用的。(2)如何查找“单位”?打开文件“饮料销售（素材)。xlｓ"，将文件另存为“姓名＿饮料销售.ｘls”.在“销售记录"工作表中，选中Ｆ3单元格→选择“插入→函数→VLOOKUP＂→单击“确定”。由于要根据饮料的名称查找“单位”，所以VＬOOKUP函数的第一个参数应该选择饮料名称“Ｄ3”。在“Tａble＿ａrｒaｙ"区域中选择“饮料价格"工作表中的Ｂ2:E４4。由于“单位"数据存放在“饮料信息”数据区域的第2列，所以输入数字“2”。由于要求饮料名称精确匹配,所以最后一个参数输入“ＦALSE”。单击“确定”，可以看到函数准确地返回了“统一奶茶”的单位“瓶”。复制公式，出现了什么问题??？为什么？如何解决呢？提示:注意在复制公式时，如果沿着列拖动时，列标要用绝对引用,想想看为什么?修改公式后,重新复制公式。（3)创建区域名称选择“饮料名称”工作表。选中饮料名称、单位、进价、售价所在的区域，即单元格区域Ｂ２:E4４。温馨提示： 也可以采用以下方法定义区域名称：选择菜单“温馨提示： 也可以采用以下方法定义区域名称：选择菜单“插入→名称→定义”命令，打开“定义名称”对话框，在名称框中输入“姓名A”后，单击“确定”按钮，“姓名A”区域名称创建完成思考问题：（1）区域的定义可以包含“序号”列吗？为什么?(2)如果名称定义错误了，如何将其删除。(4）利用区域名查找“进价”和“售价＂选择“销售记录"工作表→选中G3单元格→输入等号“=”→选择函数“VLOOKUP＂→单击“确定"。由于要根据饮料的名称查找“售价”，所以VＬOOKUP函数的第一个参数应该选择饮料名称“D３”。在“Tablｅ_arｒay”区域中输入“姓名A”。由于“进价”数据存放在第3列,所以输入数字“3”。由于要求饮料名称精确匹配,所以最后一个参数输入“FＡLSE”怎么样，对于VLOOKUP函数你是否有了一定的认识？如果还不会，请多做几遍，慢慢去体会。单击“确定”,可以看到函数准确地返回了“统一奶茶”的“进价”数据“１．9”怎么样，对于VLOOKUP函数你是否有了一定的认识？如果还不会，请多做几遍，慢慢去体会。复制公式，看一看有什么不同？??同样道理在H3单元格中建立查找饮料名称“售价”的公式.(5)计算销售额、毛利润温馨提示：表格中数据之间的关系为：销售额＝售价*数量毛利润＝(售价－进价)×数量ＳＴEＰ2—分类汇总首先,建立表“销售记录"的二个副本“销售记录（2)"、“销售记录（3)”和“销售记录（4)"。温馨提示：按住Ctrl键拖动工作表标签（１)按照“所在区”进行分类汇总温馨提示：选择“所在区”列中的任意单元格单击“升序”按钮进行排序（排序的作用是什么？）然后选择“温馨提示：选择“所在区”列中的任意单元格单击“升序”按钮进行排序（排序的作用是什么？）然后选择“数据”→“分类汇总”。将销售记录(2)改名为“所在区汇总"结果参见样例中的工作表——“所在区汇总”（２）按照“饮料名称”进行分类汇总在表“销售记录（3)”中按“饮料名称”对销售额和毛利润进行分类汇总(汇总方式为求和）对汇总结果中的毛利润列按降序排序,找出毛利润最大的饮料。将销售记录(3)改名为“饮料名称汇总”结果参见样例中的工作表——“饮料名称汇总”温馨提示：首先按照“饮料名称”对“销售额”和“毛利润”进行分类汇总；然后，对“毛利润”进行降序排序。(3）用“嵌套分类汇总”统计各个区和各饮料店的饮料“销售额"和“毛利润”在“销售记录(4)＂工作表中对“主要关键字＂选择“所在区＂,“次要关键字"选择“饮料店”排序.进行第一次“分类汇总”(分类字段为“所在区”)。进行第二次“分类汇总”(分类字段为“饮料店”）.将销售记录(4)改名为“嵌套汇总”温馨提示：首先，选择“温馨提示：首先，选择“数据”→“排序”命令，在主要关键字中选择“所在区”，在次要关键字中选择“饮料店”；然后，先按照“所在区”进行分类汇总；再按照“饮料店”进行汇总，但是第二次汇总时，必须取消“替换当前分类汇总”。STEP3—利用数据透视表实现统计分析设计目标如下图所示，利用数据透视表生成三维数据统计报表。利用排序，找出销售额最大的饮料利用排序，找出销售额最大的饮料（1）为“销售记录"表创建数据透视表要求：为表“销售记录”建立数据透视表，将数据透视表显示在新工作表中,并将数据透视表命名为“销售统计”行显示“饮料名称”，列显示“所在区",“销售额"显示在数据区。ﻩ结果参见样例中的“销售统计"工作表。温馨提示：选中“销售记录”工作表的任意单元格，选择“数据”→“数据透视表和数据透视图”；单击“下一步”，选择销售数据所在区域；单击“下一步”，打开“数据透视表和数据透视表视图向导之3”单击“布局”按钮，打开“数据透视表和数据透视表视图向导—布局”对话框，将“饮料名称”拖到左边图形的“行”上，“所在区”拖到“列”上，销售额拖到“数据”中，如下图所示；单击“确定”，数据透视表显示位置：“新建工作表”。单击“完成”，将数据透视表命名为“销售统计”。（2)根据数据透视表找出销售额最大的饮料对毛利润按降序排序，找出销售额最大的三种饮料。结果参见样例中的“销售统计"工作表温馨提示：选中“总计”列的数值部分（不包含最后一行数值）然后单击工具栏中的排序按钮进行排序。(3）在“销售统计＂工作表中,找出各个区“销售额”最大的饮料在“销售统计"工作表中,用MAX函数找出每个区“销售额”最大的饮料的“销售额”。在“销售统计"工作表中，用VLOOKUP函数找出各区“最大销售额”所对应的“饮料名称".SＴＥP４—用“两轴线-柱图"比较“销售额”和“毛利润”设计目标现在,小李想用“两轴线-柱图"比较“南山区”、“福田区”和“罗湖区”的销售额和毛利润之间的关系。如下图所示,用两轴线柱图表来比较销售额和毛利润：（１)制作“两轴线图”在表“所在区汇总”分别选择“所在区”、“销售额”和“毛利润”三列（如下图）所示;单击“图表向导”按钮,打开“图表向导—4－图表类型”对话框，选择“自定义类型”选项卡,选择“两轴线—柱图”。单击“下一步＂,打开“图表向导—4—图表源数据”对话框;单击“下一步”，打开“图表向导－4-图表选项＂对话框，按下图进行设置；单击“下一步”，在“图表位置”对话框中,选择“作为其中的对象插入”，单击“完成”按钮。(2）美化图表参照“饮料销售(样例)。xls”，完成对图表格式的设置(格式可以自定义）。STEP5—利用数据有效性制作更方便、更实用的“新销售记录”工作表设计目标在“饮料销售。xｌｓ＂中制作一张“新销售记录表",并应用数据有效性设置,使得在填写了销售“数量”和选取了“饮料名称”后，可以自动计算出“销售额”、“毛利润"和“毛利率"。（１）制作“销售记录”表副本在“饮料销售。xls"中建立一个“销售记录”表的副本,并将其重命名为“新销售记录"，然后将其前五列的内容删除（只保留标题行）,如下图所示。并增加３个新列“实收”、“应收”和“找回"（2)对“饮料名称”应用数据有效性设置选中“饮料价格”表中的“饮料名称”区域，并将其定义为“饮料名称”，如下图所示。选中“新销售记录”的第4列(“饮料名称”列）,然后再选择“数据＂→“有效性”打开数据有效性对话框。在有效性条件中选择“序列”。在“来源"中填写“=饮料名称"（注意：“饮料名称"是定义的“饮料名称”区域）,如图下所示.(3）数据有效性的使用制作完成，如下图所示，试试看，是不是在填写了销售“数量”和选取了“饮料名称"后,可以自动计算出“销售额"、“毛利润”和“毛利率”，很方便吧!(４)对“饮料店”列进行数据有效性设置参照“饮料名称”的设置对“饮料店”进行设置（5）创建“应收”和“找回”列的公式,算法如下:应收=销售额，找回=实收－应收。ＳTEP6—让查找公式更完美（1）隐藏列在“新销售记录"工作表中将不需要显示的列(“单位”、“进价”、“销售额”）隐藏起来.(２）前面的结果有缺陷在上面应用了查找函数VLOOKUP的“销售记录”表中,如果把D3单元格中的饮料名称删去以后,可以看到Ｆ３、G３、H3单元格(即：单位、售价和进价)中均返回错误值“＃N／A”。大家可以试一下，当ＶLOＯKUP函数在“饮料价格”表中没有找到D３单元格中的饮料名称时都要返回错误值“#Ｎ/A”。能不能让当VＬOOKUＰ函数在“饮料信息”表中没有找到Ｄ3单元格中的饮料名称时不返回错误值“＃N／A”,而只什么都不显示（即显示空格）呢?问题：利用IF函数和ＩSＥRRＯＲ函数，使“销售额”、“毛收入”和“毛利润”三列的值,在没有输入饮料名称时,不显示“＃VALUE!”错误值.(2)解决办法利用IF和IＳERROR函数可以解决上面的问题。试试看,你能解决这个问题吗?上面的问题变成两种情况：如果当“VＬOＯKUP（D3，yｌmｃ,2,ＦＡLＳＥ）＂部分返回错误值的话,则在Ｆ3中显示空字符串。如果“ＶLOOKＵP(Ｄ3,ｙlmc，2，FALSE)＂部分工作正常,则在F３单元格中显示饮料对应的“售价"(即VLOOKＵP（D３,ylmc,２，ＦALSE)的值)。温馨提示：ISERROR(value)函数：当变量value是错误值“#N/A”时，返回逻辑真（TRUE），因此，当它与函数IF结合在一起使用时，可以用于在公式中查出错误值。IF函数与ISERROR函数联合使用的通用表示法:IF(ISERROR(VALUE),””,VALUE)SＴEP7－冻结窗口保留标题行在“销售记录”工作表中，当销售记录增多后，为了保留标题行的内容,可单击A1单元格,执行“窗口”→“冻结窗口”命令，当滚动屏幕时,可以把第一行(即表头行)的内容一直保留在窗口中。4．重点难点重点VLＯＯKUP函数的使用。区域名称的定义分类汇总数据透视表难点VＬOOＫUＰ函数的参数的选择数据透视表5．案例总结与常见问题本案例通过对学生信息表数据的处理,介绍了查找与引用类函数ＶLOOKUP的用法及分类汇总和数据透视表的用法。(一）你知道了吗?通过本案例的学习,你能回答如下问题吗?1．VLＯOKUP函数是干什么的?2.用VLOOKUP函数进行查找时,所要查找的元素为什么一定要在所定义的数据区域的第一列?3．VLOＯKUP函数中第二个参数的含义是什么？在定义第二个参数时要注意什么?4.在对VLOＯKPU函数沿列进行复制时，一般情况下，为什么列标要用绝对地址？5.如何定义数据区域，如何删除数据区域？６．用ＶＬOOKPＵ函数时，可以不定义数据区域吗?７.什么是分类汇总？它有什么作用？8.在分类汇总之前要注意什么？(二)常见问题及处理方法下面把大家在本案例的学习过程中容易遇到的一些问题及处理方法列于下表:常见问题可能原因处理方法“数据区域”名称无法重新定义.该“数据区域”名称已被定义.将其删除,重新定义，方法:选择“插入”→“名称”→“定义”，打开“定义名称”对话框,选择已定义的名称,单击“删除”命令。VLOOＫUP函数返回错误值“#N/A”．(１）没有把要查找的对象定义在“数据区域"的第一列。重新定义“数据区域”，把要查找的内容定义在“数据区域”的第一列．(2）查找的内容在定义的“数据区域"中不存在．用ISERROR函数使错误值不显示。使用“填充柄”沿列拖动复制公式时出现错误值“#Ｎ/A＂.要查找的对象(ＶLOＯＫUP函数的第一个参数）列标没有用绝对引用。在使用“填充柄”沿列拖动前先将第一个参数的列标绝对引用。分类汇总结果不正确。(1）在“分类汇总”之前没先按要“分类”的字段排序.先按要分类的字段排序,然后再进行“分类汇总＂。（２)虽然已先按要“分类”的字段排序,但在“分类汇总"时,分类字段选择不正确（没有选择已排序的字段)。在“分类汇总＂时,“分类”字段选择已排序的字段。6。课后作业:学生成绩的查询和统计请大家用查找函数