ecel销售大数据分析报告实训练习.docx

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

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

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

3、息。2. 用“分类汇总”统计出各连锁店和各个区中各种饮料的“销售额”、“毛利润”。3. 用“数据透视表”分析各个区中每种饮料的销售状况和各个区中销售状况最好的饮料。4. 另外，为了提高效率、避开出错，小李还想制作一张可以从下拉列表中选择饮料名称，并能自动计算出顾客应交款及应找回款的“销售记录”工作表。4. 制作过程STEP 1VLOOKUP函数的使用设计目标参照以下图，依据“销售记录”表中的“ 饮料名称”列，利用 VLOOKUP 函数在“饮料根本信息”表中查找其他列 单位、进价和售价的值。依据以上两个表中的数据得到以下图中的结果最终计算销售额、毛利润、毛利率（1） VLOOKUP 函数是干什么

4、用的 VLOOKUP 函数的功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的 值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)查找什么？到哪个区域查找？查找区域第几列的值？准确查找还是模糊查找？ VLOOKUP 的语法：留意：要查找的对象参数肯定要定义在查找数据区域参数的第一列。下面来看看VLOOKUP 函数是怎么用的。（2） 如何查找“单位”？翻开文件“饮料销售(素材).xls”，将文件另存为“ 姓名_饮料销售.xls”。在“销售记录”工作表 中，选中 F3 单元格 选择“插入函数VLOOKUP

5、” 单击“确定”。由于要依据饮料的名称查找“单位”，所以 VLOOKUP 函数的第一个参数应中选择饮料名称“D3”。在“Table_array”区域中选择“ 饮料价格”工作表中的 B2:E44。由于“单位”数据存放在“饮料信息”数据区域的第 2 列，所以输入数字“ 2”。由于要求饮料名称准确匹配，所以最终一个参数输入“ FALSE”。单击“确定”，可以看到函数准确地返回了“统一奶茶”的单位“瓶”。复制公式,消灭了什么问题为什么？如何解决呢？提示：留意在复制公式时，假设沿着列拖动时，列标要用确定引用，想想看为什么？ 修改公式后，重复制公式。（3） 创立区域名称F 温馨提示：插入名称定义姓名 A选

6、择“饮料名称”工作表。选中饮料名称、单位、进价、售价所在的区域，即单元格区域 B2:E44。在名称框中输入“自己的姓名 A”，肯定要按回车确认。号”列吗？为什么？2假设名称定义错误了，如何将其删除。思 考问题：1区域的定义可以包含“序（4） 利用区域名查找“进价”和 “售价” 选择“销售记录”工作表 选中 G3 单元格 输入等号“=”选择函数“ VLOOKUP” 单击“确定”。 由于要依据饮料的名称查找“售价”，所以VLOOKUP 函数的第一个参数应中选择饮料名称“ D3”。 在“Table_array”区域中输入“ 姓名 A”。 由于“进价”数据存放在第 3 列，所以输入数字“ 3”。 由于

7、要求饮料名称准确匹配，所以最终一个参数输入“ FALSE” 单击“确定”，可以看到函数准确地返回了“统一奶茶”的“进价”数据“ 1.9”。怎么样，对于VLOOKUP 函数你是否有了肯定的生疏？假设还不会， 请多做几遍，渐渐去体会。复制公式，看一看有什么不同同样道理在 H3 单元格中建立查找饮料名称“售价”的公式。（5） 计算销售额、毛利润温馨提示： STEP 2分类汇总首先，建立表“销售记录”的二个副本“ 销售记录(2)”、“销售记录(3)”和“销售记录(4)”。F 温馨提示：（1） 依据“所在区”进展分类汇总 在表“销售记录(2)”中按“所在区”对销售额和毛利润进展分类汇总，汇总结果显示在数

8、据下方结果见“(样例)饮料销售.xls”。F 温馨 提示：选择“所在区”列中的任意单元格单击“升序”按钮进展排序排序的作用是什么？然后选择“数据”“分类汇总”。中的工作表“ 所在区汇总” 将 销售记录(2) 改 名 为“ 所在区汇总” 结 果参见样例（2） 依据“饮料名称”进展分类汇总 在表“销售记录(3)”中按“饮料名称”对销售额和毛利润进展分类汇总汇总方式为求和 对汇总结果中的毛利润列按降序排序，找出毛利润最大的饮料。 将销售记录(3)改名为“饮料名称汇总” 结果参见样例中的工作表“ 饮料名称汇总”F 温馨提示：（3） 用“嵌套分类汇总”统计各个区和各饮料店的饮料“销售额”和“毛利润” 在

9、“销售记录(4)”工作表中对“主要关键字”选择“所在区”，“次要关键字”选择“饮料店”排序。 进展第一次“分类汇总”分类字段为“所在区”。 进展其次次“分类汇总”分类字段为“饮料店”。 将销售记录(4)改名为“嵌套汇总” 结果参见样例中的工作表“ 嵌套汇总”F温馨提示：“数据”“排序”“所在区”“饮料店”饮料店替换当前分类汇总STEP 3利用数据透视表实现统计分析设计目标如以下图所示，利用数据透视表生成三维数据统计报表。利用排序，找出销售额最大的饮料（1） 为 “销售记录”表创立数据透视表要求：为表“销售记录”建立数据透视表，将数据透视表显示在工作表中，并将数据透视表命名为“销售统计”行显示“

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

11、对毛利润按降序排序，找出销售额最大的三种饮料。 结果参见样例中的“ 销售统计”工作表F温馨提示：（3） 在“销售统计”工作表中，找出各个区“销售额”最大的饮料 在“销售统计”工作表中，用 MAX 函数找出每个区“销售额”最大的饮料的“销售额”。 在“销售统计”工作表中，用 VLOOKUP 函数找出各区“最大销售额”所对应的“饮料名称”。STEP 4用“两轴线-柱图”比较“销售额”和“毛利润”设计目标现在，小李想用“两轴线-柱图”比较“南山区”、“福田区”和“罗湖区”的销售额和毛利润之间的关系。如以下图所示，用两轴线柱图表来比较销售额和毛利润：（1） 制作“两轴线图” 在表“所在区汇总” 分别选

12、择“所在区”、“销售额”和“毛利润”三列如以下图所示； 单击“图表向导”按钮，翻开“图表向导-4-图表类型”对话框，选择“自定义类型”选项卡，选择“两轴线-柱图”。 单击“下一步”，翻开“图表向导-4-图表源数据”对话框； 单击“下一步”，翻开“图表向导-4-图表选项”对话框，按以下图进展设置；单击“下一步”，在“图表位置”对话框中，选择“作为其中的对象插入”，单击“完成”按钮。（2） 美化图表参照“饮料销售(样例).xls”，完成对图表格式的设置格式可以自定义。STEP 5利用数据有效性制作更便利、更有用的“销售记录”工作表设计目标在“饮料销售.xls”中制作一张“销售记录表”，并应用数据有

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

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

15、3 单元格中的饮料名称删去以后， 可以看到 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 单元格中显示饮料对应的“售价F 温馨提示：ISERROR(value) 函数：当变量 value 是错误值“#N/A”时，返回规律真 TRUE，因此，当它与函数 IF 结合在一起使用时，可以用于在公式中查出错误值。IF 函数与 ISERROR 函数联合使用的通用表示法:IF(ISERROR( VALUE),”,VALUE)出错吗？ISERROR？是否空

17、字符串VLOOKUP(C2,姓名 A,3,FALSE)” 即 V L O O K U P ( D 3,ylmc,2,FALSE)的值。STEP 7冻结窗口保存标题行在“销售记录”工作表中，当销售记录增多后，为了保存标题行的内容，可单击A1 单元格，执行“窗口”“冻结窗口”命令，当滚动屏幕时，可以把第一行即表头行的内容始终保存在窗口中。4. 重点难点（1） 重点l VLOOKUP 函数的使用。l 区域名称的定义l 分类汇总l 数据透视表（2） 难点l VLOOKUP 函数的参数的选择l 数据透视表5. 案例总结与常见问题本案例通过对学生信息表数据的处理，介绍了查找与引用类函数VLOOKUP 的用

18、法及分类汇总和数据透视表的用法。一你知道了吗？通过本案例的学习，你能答复如下问题吗？1. VLOOKUP 函数是干什么的？2. 用 VLOOKUP 函数进展查找时，所要查找的元素为什么肯定要在所定义的数据区域的第一列？3. VLOOKUP 函数中其次个参数的含义是什么？在定义其次个参数时要留意什么？4. 在对 VLOOKPU 函数沿列进展复制时，一般状况下，为什么列标要用确定地址？5. 如何定义数据区域，如何删除数据区域？6. 用VLOOKPU 函数时，可以不定义数据区域吗？7. 什么是分类汇总？它有什么作用？8. 在分类汇总之前要留意什么？二常见问题及处理方法下面把大家在本案例的学习过程中简

19、洁遇到的一些问题及处理方法列于下表： 常见问题可能缘由处理方法将其删除，重定义，方法： 选择“插入”“名称”“数据区域”名称无法重定义。该“数据区域”名称已被定义。 “定义”，翻开“定义名称”对话框，选择已定义的名称， 单击“删除”命令。重定义“数据区域”，把要VLOOKUP 函数返回错误值“#N/A”。（1） 没有把要查找的对象定义在“数据区域”的第一列。（2） 查找的内容在定义的“ 数据区域”中不存在。查找的内容定义在“数据区域”的第一列。用 ISERROR 函数使错误值不显示。使用“填充柄”沿列 要查找的对象VLOOKUP 函数拖动复制公式时出 的第一个参数列标没有用绝现错误值“#N/A

20、”。 对引用。在使用“填充柄”沿列拖动前先将第一个参数的列标确定引用。分类汇总结果不正确。（1） 在“分类汇总”之前没先按要分类的字段排序，然先按要“分类”的字段排序。 后再进展“分类汇总”。（2） 虽然已先按要“分类”的字段排序，但在“分类汇总” 在“分类汇总”时，“分类” 时，分类字段选择不正确没 字段选择已排序的字段。有选择已排序的字段。6. 课后作业：学生成绩的查询和统计请大家用查找函数 VLOOKUP 完成下面的案例。（1） 问题的提出： 学期刚开头，班主任王教师遇到了一个难题，学校依据学生填报的志愿重分了班。教务处 给出了一个只包含班级学生的学号的 Excel 表格，见素材。现在他

21、要填写班级学生的信息。依据以往的阅历，他只能一个一个去查找然后粘贴了，但这既简洁出错，工作量又很大。于是他 向教 Excel 的教师请教，经过教师的教导，才知道用 VLOOKUP 可以很简洁地解决这个问题。 要求：（A） 在表“一班”中，依据学号，查找“姓名”、“大学英语”和总分。（B） 另外学校要对上学期“大学英语”考试成绩是“优秀”和“良好”的学生进展嘉奖。嘉奖办 法见素材中表“奖品及加分”，其中“大学英语”考试成绩是“优秀”的同学总分增加 2%、“良好”的总分增加 1%。（2） 解决方法 请利用“学生信息(素材).xls”，在表“一班”中，依据学号，应用 VLOOKUP 函数填写出一班学生的“姓名”、“大学英语”和“总分”，对没有成绩的填写“ 缺考”。 用 IF 函数填写一班学生的“英语等级”，对缺考的学生填写“英语缺考”。英语成绩=90，“优秀” 80=英语成绩90，“良好”70=英语成绩80，“一般”60=英语成绩70，“及格”英语成绩60，“不及格” 依据表“奖品及加分”，用 VLOOKUP 函数计算一班学生的“奖品”和“加分率”，其中“加分率”用百分比格式表示。 用 IF 和 ISERROR 函数，使英语等级不是“优秀”和“良好”的学生的“奖品”为空、“加分率”是 0，而不显示错误值“ #N/A”。 计算出每个学生的“总分”。提示：总分=总分1+加分率。