《第8章 Excel综合应用2—饮料店销售数据分析》由会员分享，可在线阅读，更多相关《第8章 Excel综合应用2—饮料店销售数据分析（30页珍藏版）》请在装配图网上搜索。
1、主编：许晞 副主编：刘艳丽 曾煌兴 郑杰 秦高德学习目标学习目标熟练掌握熟练掌握VLOOKUP函数的使用方法函数的使用方法熟练掌握分类汇总的使用方法熟练掌握分类汇总的使用方法掌握掌握ISERROR函数的用法及函数的用法及ISERROR函数与函数与IF函数的嵌套使用方法函数的嵌套使用方法能够用数据透视表分析、统计数据能够用数据透视表分析、统计数据能够进行数据有效性设置能够进行数据有效性设置1/29/20232目目 录录8.1 饮料店销售案例分析饮料店销售案例分析8.2 实现方法实现方法8.3 案例总结案例总结8.4 课后练习课后练习1/29/202338.1 饮料店销售案例分析饮料店销售案例分析
2、任务的提出任务的提出 小李在深圳市开了若干家饮料连锁店，为了提高管小李在深圳市开了若干家饮料连锁店，为了提高管理水平，他打算用理水平，他打算用Excel工作表来管理销售数据。工作表来管理销售数据。解决方案解决方案 利用查找与引用函数利用查找与引用函数VLOOKUP和分类汇总以及数和分类汇总以及数据透视表来解决。据透视表来解决。返回目录1/29/202348.2 实现方法实现方法8.2.1 用用VLOOKUP函数计算函数计算“单位单位”、“进价进价”和和“售价售价”8.2.2 用用“分类汇总分类汇总”统计统计“销售额销售额”和和“毛利润毛利润”8.2.3 用用“数据透视表数据透视表”分析各区每种
3、饮料销售情况分析各区每种饮料销售情况8.2.4 用用“两轴线两轴线-柱图柱图”比较比较“销售额销售额”和和“毛利润毛利润”8.2.5 制作更方便、更实用的制作更方便、更实用的“新销售记录新销售记录”表表8.2.6 使使“新销售记录新销售记录”表更完美表更完美返回目录1/29/202358.2.1 8.2.1 用用VLOOKUPVLOOKUP函数计算函数计算“单位单位”、“进价进价”和和“售价售价”1.VLOOKUP函数函数VLOOKUPVLOOKUP函数的功能：查找数据区域首列满足条件函数的功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。的元素，并返回数据区域当前行中
4、指定列处的值。VLOOKUPVLOOKUP函数的语法：函数的语法：1/29/202368.2.1 8.2.1 用用VLOOKUPVLOOKUP函数计算函数计算“单位单位”、“进价进价”和和“售价售价”2在在“饮料价格饮料价格”工作表中创建工作表中创建“价格价格”区域，操区域，操作步骤如下：作步骤如下：（1 1）选中饮料名称、单位、进价、售价所在的区域）选中饮料名称、单位、进价、售价所在的区域 （2 2）在菜单栏中选择）在菜单栏中选择“插入插入”“”“名称名称”“”“定义定义”命命令，在名称框中输入令，在名称框中输入“价格价格”，单击，单击“确定确定”按钮。按钮。1/29/202378.2.1
5、8.2.1 用用VLOOKUPVLOOKUP函数计算函数计算“单位单位”、“进价进价”和和“售价售价”3用用VLOOKUP函数查找饮料函数查找饮料“单位单位”，操作步骤，操作步骤如下：如下：（1 1）选择）选择“销售记录销售记录”工作表，选中目标单元格工作表，选中目标单元格F3F3。选择选择“插入插入函数函数”命令，打开命令，打开“插入函数插入函数”对话框，对话框，在在“选择函数选择函数”列表框中选择列表框中选择“VLOOKUP”“VLOOKUP”。1/29/202388.2.1 8.2.1 用用VLOOKUPVLOOKUP函数计算函数计算“单位单位”、“进价进价”和和“售价售价”（2）在）在
6、“函数参数函数参数”对话框中填写各个参数对话框中填写各个参数 查找数据的过程演示查找数据的过程演示1/29/202398.2.1 8.2.1 用用VLOOKUPVLOOKUP函数计算函数计算“单位单位”、“进价进价”和和“售价售价”在在G3G3单元格中用单元格中用VLOOKUPVLOOKUP函数在函数在“价格价格”区域区域中查找饮料的中查找饮料的“进价进价”。在在H3H3单元格中用单元格中用VLOOKUPVLOOKUP函数在函数在“价格价格”区域区域中查找饮料的中查找饮料的“售价售价”。用公式计算用公式计算“销售额销售额”和和“毛利润毛利润”2”2列的值。列的值。其中：销售额其中：销售额=售价
7、数量售价数量毛利润毛利润=(=(售价售价-进价进价)数量数量 1/29/2023108.2.1 8.2.1 用用VLOOKUPVLOOKUP函数计算函数计算“单位单位”、“进价进价”和和“售价售价”（3）对对“销售记录销售记录”工作表进行格式设置工作表进行格式设置 返回1/29/2023118.2.2 8.2.2 用用“分类汇总分类汇总”统计统计“销售额销售额”和和“毛利毛利润润”1创建创建“销售记录销售记录”工作表的副本工作表的副本 鼠标右键单击鼠标右键单击“销售记录销售记录”工作表标签，在弹出的快捷工作表标签，在弹出的快捷菜单菜单中选择中选择“移动或复制工作表移动或复制工作表”，并选中，并
8、选中“建立副本建立副本”复复选框。选框。1/29/2023128.2.2 8.2.2 用用“分类汇总分类汇总”统计统计“销售额销售额”和和“毛利润毛利润”2在在“销售记录销售记录(2)”工作表中，用工作表中，用“分类汇总分类汇总”计算计算各个区的饮料各个区的饮料“销售额销售额”和和“毛利润毛利润”（1）在）在“销售记录销售记录(2)”工作表中，对工作表中，对“所在区所在区”字段进行排序字段进行排序 1/29/2023138.2.2 8.2.2 用用“分类汇总分类汇总”统计统计“销售额销售额”和和“毛利润毛利润”（2）用）用“分类汇总分类汇总”统计各区的统计各区的“销售额销售额”和和“毛利润毛利
9、润”1/29/2023148.2.2 8.2.2 用用“分类汇总分类汇总”统计统计“销售额销售额”和和“毛利润毛利润”3利用利用“分类汇总分类汇总”统计各种饮料的统计各种饮料的“销售额销售额”和和“毛利润毛利润”，并找出，并找出“毛利润毛利润”最大的饮料最大的饮料（1）在）在“销售记录销售记录(3)”工作表中，用工作表中，用“分类汇总分类汇总”统计每种饮统计每种饮料的料的“销售额销售额”和和“毛利润毛利润”1/29/2023158.2.2 8.2.2 用用“分类汇总分类汇总”统计统计“销售额销售额”和和“毛利润毛利润”（2）将）将“毛利润毛利润”按降序排序，找出按降序排序，找出“毛利润毛利润”
10、最大的饮料最大的饮料 1/29/2023168.2.2 8.2.2 用用“分类汇总分类汇总”统计统计“销售额销售额”和和“毛利润毛利润”4利用利用“嵌套分类汇总嵌套分类汇总”统计各个区中各饮料店的统计各个区中各饮料店的“销售额销售额”和和“毛利润毛利润”返回1/29/2023178.2.38.2.3用用“数据透视表数据透视表”分析各区每种饮料销售情分析各区每种饮料销售情况况 1用用“数据透视表数据透视表”统计各个区每种饮料的销售情况统计各个区每种饮料的销售情况 在在“销售记录销售记录”工作表中，用工作表中，用“数据透视表数据透视表”统计各个区每统计各个区每种饮料的种饮料的“销售额销售额”，对，
11、对“总计总计”列按列按“降序降序”排序，找出排序，找出各个区各个区“销售额销售额”最大的饮料最大的饮料。1/29/2023188.2.3 8.2.3 用用“数据透视表数据透视表”分析各区每种饮料销售情分析各区每种饮料销售情况况 1查找每个区销售额最大的饮料查找每个区销售额最大的饮料 第一步，先用第一步，先用MAX函数找出每个区销售额最大的饮料所对应函数找出每个区销售额最大的饮料所对应的销售额；的销售额；第二步，再用第二步，再用VLOOKUP函数找出各区销售额最大的饮料所函数找出各区销售额最大的饮料所对应的饮料名称。对应的饮料名称。返回1/29/2023198.2.4 8.2.4 用用“两轴线两
12、轴线-柱图柱图”比较比较“销售额销售额”和和“毛毛利润利润”1选中选中“销售记录销售记录(2)”工作表中的隐藏分类汇总明细数据后工作表中的隐藏分类汇总明细数据后的数据的数据 2在在“图表类型图表类型”列表框中选择列表框中选择“两轴线两轴线-柱图柱图”1/29/2023208.2.4 8.2.4 用用“两轴线两轴线-柱图柱图”比较比较“销售额销售额”和和“毛毛利润利润”3对图表区域进行格式化设置对图表区域进行格式化设置 返回1/29/2023218.2.5 8.2.5 制作更方便、更实用的制作更方便、更实用的“新销售记录新销售记录”表表 1创建创建“销售记录销售记录”工作表的副本，将其重命名为工
13、作表的副本，将其重命名为“新销售记录新销售记录”2 对对“饮料名称饮料名称”列应用数据有效性列应用数据有效性 1/29/2023228.2.5 8.2.5 制作更方便、更实用的制作更方便、更实用的“新销售记录新销售记录”表表 3将不需要显示的列（将不需要显示的列（“单位单位”、“进价进价”、“销销售额售额”）隐藏起来）隐藏起来 返回1/29/2023238.2.6 8.2.6 使使“新销售记录新销售记录”表更完美表更完美 如何使如何使“新销售记录新销售记录”表中的表中的“售价售价”、“应收应收”和和“找回找回”列的错误值不显示？列的错误值不显示？1/29/2023248.2.6 8.2.6 使
14、使“新销售记录新销售记录”表更完美表更完美 利用利用IF函数和函数和ISERROR函数使函数使“新销售记录新销售记录”表中的表中的错误值不显示错误值不显示 返回1/29/2023258.3 8.3 案例总结案例总结 本章通过对饮料销售数据的处理，介绍了如下内本章通过对饮料销售数据的处理，介绍了如下内容：容：VLOOKUP函数、名称的定义、分类汇总、数函数、名称的定义、分类汇总、数据透视表、据透视表、IF函数和函数和ISERROR函数的嵌套应用以及函数的嵌套应用以及数据有效性等。数据有效性等。下面把学习过程中经常遇到的问题及处理方法列下面把学习过程中经常遇到的问题及处理方法列于下表中于下表中 。
15、1/29/2023268.3 8.3 案例总结案例总结 常见问题出错原因处理方法“数据区域数据区域”名称名称无法重新定义无法重新定义该该“数据区域数据区域”名称已被定义名称已被定义将其删除，重新定义。方法：选择将其删除，重新定义。方法：选择“插入插入”“名称名称”“定义定义”，打开，打开“定义名称定义名称”对话对话框，选择已定义的名称，单击框，选择已定义的名称，单击“删除删除”命命令令VLOOKUP函数返函数返回错误值回错误值“#N/A”（1）没有把要查找的对象定义在）没有把要查找的对象定义在“数数据区域据区域”的第的第 1 列列重新定义重新定义“数据区域数据区域”，把要查找的内容，把要查找的
16、内容定义在定义在“数据区域数据区域”的第的第 1 列列（2）查找的内容在定义的）查找的内容在定义的“数据区域数据区域”中不存在中不存在用用ISERROR函数使错误值不显示函数使错误值不显示使用使用“填充柄填充柄”沿沿列列拖动复制公式时拖动复制公式时出现错误值出现错误值#N/A”要查找的对象（要查找的对象（VLOOKUP函数的第函数的第一个参数）列标没有用绝对引用一个参数）列标没有用绝对引用在使用在使用“填充柄填充柄”沿列拖动前先将第一个沿列拖动前先将第一个参数的列标绝对引用参数的列标绝对引用分类汇总结果分类汇总结果不正确不正确（1）在）在“分类汇总分类汇总”之前没有先按要之前没有先按要“分类分
17、类”的字段排序的字段排序先按要分类的字段排序，然后再进行先按要分类的字段排序，然后再进行“分类汇总分类汇总”（2）虽然已先按要）虽然已先按要“分类分类”的字段排的字段排序，但在序，但在“分类汇总分类汇总”时，分类时，分类字段选择不正确（没有选择已排字段选择不正确（没有选择已排序的字段）序的字段）在在“分类汇总分类汇总”时，时，“分类分类”字段选择已字段选择已排序的字段排序的字段返回目录1/29/2023278.4 8.4 课后练习课后练习打开打开“MLC考试报名表考试报名表(素材素材).xls”，将，将其另存为其另存为“MLC考试报名表考试报名表(学号姓名学号姓名).xls”，参见教材第参见教
18、材第205 206页，完成对报名表的考页，完成对报名表的考生信息提取和报名费及报名人数的统计工作。生信息提取和报名费及报名人数的统计工作。返回目录1/29/202328商品基本信息VLOOKUP函数查找数据的过程演示函数查找数据的过程演示可口可乐可口可乐(1L)2B2FALSE返回1/29/202329谢谢观看/欢迎下载BY FAITH I MEAN A VISION OF GOOD ONE CHERISHES AND THE ENTHUSIASM THAT PUSHES ONE TO SEEK ITS FULFILLMENT REGARDLESS OF OBSTACLES.BY FAITH I BY FAITH
第8章 Excel综合应用2—饮料店销售数据分析
