第8章 Excel综合应用2—饮料店销售数据分析

计算机应用基础主编：许晞副主编：刘艳丽曾煌兴郑杰秦高德第8章Excel综合应用2—饮料店销售数据分析22019/8/18学习目标熟练掌握VLOOKUP函数的使用方法熟练掌握分类汇总的使用方法掌握ISERROR函数的用法及ISERROR函数与IF函数的嵌套使用方法能够用数据透视表分析、统计数据能够进行数据有效性设置第8章Excel综合应用2—饮料店销售数据分析32019/8/18目录8.1饮料店销售案例分析8.2实现方法8.3案例总结8.4课后练习第8章Excel综合应用2—饮料店销售数据分析42019/8/188.1饮料店销售案例分析任务的提出小李在深圳市开了若干家饮料连锁店，为了提高管理水平，他打算用Excel工作表来管理销售数据。解决方案利用查找与引用函数VLOOKUP和分类汇总以及数据透视表来解决。返回目录第8章Excel综合应用2—饮料店销售数据分析52019/8/188.2实现方法8.2.1用VLOOKUP函数计算“单位”、“进价”和“售价”8.2.2用“分类汇总”统计“销售额”和“毛利润”8.2.3用“数据透视表”分析各区每种饮料销售情况8.2.4用“两轴线-柱图”比较“销售额”和“毛利润”8.2.5制作更方便、更实用的“新销售记录”表8.2.6使“新销售记录”表更完美返回目录第8章Excel综合应用2—饮料店销售数据分析62019/8/188.2.1用VLOOKUP函数计算“单位”、“进价”和“售价”1.VLOOKUP函数VLOOKUP函数的功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。VLOOKUP函数的语法：第8章Excel综合应用2—饮料店销售数据分析72019/8/188.2.1用VLOOKUP函数计算“单位”、“进价”和“售价”2．在“饮料价格”工作表中创建“价格”区域，操作步骤如下：（1）选中饮料名称、单位、进价、售价所在的区域（2）在菜单栏中选择“插入”→“名称”→“定义”命令，在名称框中输入“价格”，单击“确定”按钮。第8章Excel综合应用2—饮料店销售数据分析82019/8/188.2.1用VLOOKUP函数计算“单位”、“进价”和“售价”3．用VLOOKUP函数查找饮料“单位”，操作步骤如下：（1）选择“销售记录”工作表，选中目标单元格F3。选择“插入→函数”命令，打开“插入函数”对话框，在“选择函数”列表框中选择“VLOOKUP”。第8章Excel综合应用2—饮料店销售数据分析92019/8/188.2.1用VLOOKUP函数计算“单位”、“进价”和“售价”（2）在“函数参数”对话框中填写各个参数查找数据的过程演示第8章Excel综合应用2—饮料店销售数据分析102019/8/188.2.1用VLOOKUP函数计算“单位”、“进价”和“售价”在G3单元格中用VLOOKUP函数在“价格”区域中查找饮料的“进价”。在H3单元格中用VLOOKUP函数在“价格”区域中查找饮料的“售价”。用公式计算“销售额”和“毛利润”2列的值。其中：销售额=售价×数量毛利润=(售价-进价)×数量第8章Excel综合应用2—饮料店销售数据分析112019/8/188.2.1用VLOOKUP函数计算“单位”、“进价”和“售价”（3）对“销售记录”工作表进行格式设置返回第8章Excel综合应用2—饮料店销售数据分析122019/8/188.2.2用“分类汇总”统计“销售额”和“毛利润”1．创建“销售记录”工作表的副本鼠标右键单击“销售记录”工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”，并选中“建立副本”复选框。第8章Excel综合应用2—饮料店销售数据分析132019/8/188.2.2用“分类汇总”统计“销售额”和“毛利润”2．在“销售记录(2)”工作表中，用“分类汇总”计算各个区的饮料“销售额”和“毛利润”（1）在“销售记录(2)”工作表中，对“所在区”字段进行排序第8章Excel综合应用2—饮料店销售数据分析142019/8/188.2.2用“分类汇总”统计“销售额”和“毛利润”（2）用“分类汇总”统计各区的“销售额”和“毛利润”第8章Excel综合应用2—饮料店销售数据分析152019/8/188.2.2用“分类汇总”统计“销售额”和“毛利润”3．利用“分类汇总”统计各种饮料的“销售额”和“毛利润”，并找出“毛利润”最大的饮料（1）在“销售记录(3)”工作表中，用“分类汇总”统计每种饮料的“销售额”和“毛利润”第8章Excel综合应用2—饮料店销售数据分析162019/8/188.2.2用“分类汇总”统计“销售额”和“毛利润”（2）将“毛利润”按降序排序，找出“毛利润”最大的饮料第8章Excel综合应用2—饮料店销售数据分析172019/8/188.2.2用“分类汇总”统计“销售额”和“毛利润”4．利用“嵌套分类汇总”统计各个区中各饮料店的“销售额”和“毛利润”返回第8章Excel综合应用2—饮料店销售数据分析182019/8/188.2.3用“数据透视表”分析各区每种饮料销售情况1．用“数据透视表”统计各个区每种饮料的销售情况在“销售记录”工作表中，用“数据透视表”统计各个区每种饮料的“销售额”，对“总计”列按“降序”排序，找出各个区“销售额”最大的饮料。第8章Excel综合应用2—饮料店销售数据分析192019/8/188.2.3用“数据透视表”分析各区每种饮料销售情况1．查找每个区销售额最大的饮料第一步，先用MAX函数找出每个区销售额最大的饮料所对应的销售额；第二步，再用VLOOKUP函数找出各区销售额最大的饮料所对应的饮料名称。返回第8章Excel综合应用2—饮料店销售数据分析202019/8/188.2.4用“两轴线-柱图”比较“销售额”和“毛利润”1．选中“销售记录(2)”工作表中的隐藏分类汇总明细数据后的数据2．在“图表类型”列表框中选择“两轴线-柱图”第8章Excel综合应用2—饮料店销售数据分析212019/8/188.2.4用“两轴线-柱图”比较“销售额”和“毛利润”3．对图表区域进行格式化设置返回第8章Excel综合应用2—饮料店销售数据分析222019/8/188.2.5制作更方便、更实用的“新销售记录”表1．创建“销售记录”工作表的副本，将其重命名为“新销售记录”2．对“饮料名称”列应用数据有效性第8章Excel综合应用2—饮料店销售数据分析232019/8/188.2.5制作更方便、更实用的“新销售记录”表3．将不需要显示的列（“单位”、“进价”、“销售额”）隐藏起来返回第8章Excel综合应用2—饮料店销售数据分析242019/8/188.2.6使“新销售记录”表更完美如何使“新销售记录”表中的“售价”、“应收”和“找回”列的错误值不显示？第8章Excel综合应用2—饮料店销售数据分析252019/8/188.2.6使“新销售记录”表更完美利用IF函数和ISERROR函数使“新销售记录”表中的错误值不显示返回第8章Excel综合应用2—饮料店销售数据分析262019/8/188.3案例总结本章通过对饮料销售数据的处理，介绍了如下内容：VLOOKUP函数、名称的定义、分类汇总、数据透视表、IF函数和ISERROR函数的嵌套应用以及数据有效性等。下面把学习过程中经常遇到的问题及处理方法列于下表中。第8章Excel综合应用2—饮料店销售数据分析272019/8/188.3案例总结常见问题出错原因处理方法“数据区域”名称无法重新定义该“数据区域”名称已被定义将其删除，重新定义。方法：选择“插入”→“名称”→“定义”，打开“定义名称”对话框，选择已定义的名称，单击“删除”命令VLOOKUP函数返回错误值“#N/A”（1）没有把要查找的对象定义在“数据区域”的第1列重新定义“数据区域”，把要查找的内容定义在“数据区域”的第1列（2）查找的内容在定义的“数据区域”中不存在用ISERROR函数使错误值不显示使用“填充柄”沿列拖动复制公式时出现错误值#N/A”要查找的对象（VLOOKUP函数的第一个参数）列标没有用绝对引用在使用“填充柄”沿列拖动前先将第一个参数的列标绝对引用分类汇总结果不正确（1）在“分类汇总”之前没有先按要“分类”的字段排序先按要分类的字段排序，然后再进行“分类汇总”（2）虽然已先按要“分类”的字段排序，但在“分类汇总”时，分类字段选择不正确（没有选择已排序的字段）在“分类汇总”时，“分类”字段选择已排序的字段返回目录第8章Excel综合应用2—饮料店销售数据分析282019/8/188.4课后练习打开“MLC考试报名表(素材).xls”，将其另存为“MLC考试报名表(学号姓名).xls”，参见教材第205～206页，完成对报名表的考生信息提取和报名费及报名人数的统计工作。返回目录第8章Excel综合应用2—饮料店销售数据分析292019/8/18销售记录商品信息表商品基本信息VLOOKUP函数查找数据的过程演示可口可乐(1L)2≠＝≠B2FALSE返回