第5章 EXCEL综合应用2——饮料店销售数据分析 学习目标•熟练掌握VLOOKUP函数的使用方法•熟练掌握分类汇总的使用方法•能够用数据透视表分析、统计数据 本章内容Ø用VLOOKUP函数计算“单位”、“进价”和“售价”Ø用“分类汇总”统计“销售额”和“毛利润”Ø用“数据透视表”分析各区每种饮料销售情况Ø用“两轴线-柱图”比较“销售额”和“毛利润” 9.1 饮料销售案例分析•任务的提出 小李在深圳市开了若干家饮料连锁店，为了提高管理水平，他打算用Excel工作表来管理销售数据、统计各连锁店的销售额和毛利润、并对销售数据进行分析•解决方案利用VLOOKUP函数查找各种饮料的价格；用分类汇总统计“销售额”和“毛利润”；用数据透视表分析饮料销售数据2024/8/24第9章 饮料销售数据分析4 任务一:用VLOOKUP函数计算“单位”、“进价”和“售价” 用VLOOKUP函数计算“单位”、“进价”和“售价”1.VLOOKUP函数ÒVLOOKUP函数的功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值ÒVLOOKUP函数的语法： 用VLOOKUP函数计算“单位”、“进价”和“售价”语法说明：•“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。
• “区域”：数组所在的区域 •“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推； •“逻辑值”：为TRUE或FALSE它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配Ø 如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；Ø 如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值Ø 如果找不到，则返回错误值 #N/A如果“查找值”为文本时，“逻辑值”一般应为 FALSE 用VLOOKUP函数计算“单位”、“进价”和“售价”2．在“饮料价格”工作表中创建“价格”区域，操作步骤如下： （1）选中饮料名称、单位、进价、售价所在的区域 （2）在菜单栏中选择“插入”→“名称”→“定义”命令，在名称框中输入“价格”，单击“确定”按钮 用VLOOKUP函数计算“单位”、“进价”和“售价”3．用VLOOKUP函数查找饮料“单位”，操作步骤如下： （1）选择“销售记录”工作表，选中目标单元格F3。
选择“插入→函数”命令，打开“插入函数”对话框，在“选择函数”列表框中选择“VLOOKUP” （2）在“函数参数”对话框中填写各个参数 查找数据的过程演示查找数据的过程演示 销售记录销售记录销售记录销售记录饮料价格表饮料价格表饮料价格表饮料价格表价格VLOOKUP函数查找数据的过程演示可口可乐可口可乐(1L)2≠＝＝≠B2FALSE 完成任务一–在F3单元格中用VLOOKUP函数在“价格”区域中查找饮料的“单位”–在G3单元格中用VLOOKUP函数在“价格”区域中查找饮料的“进价”–在H3单元格中用VLOOKUP函数在“价格”区域中查找饮料的“售价”–用公式计算“销售额”和“毛利润”2列的值其中：销售额=售价×数量 毛利润=(售价-进价)×数量对“销售记录”工作表进行格式设置：将“进价”、“售价”、“销售额”、“毛利润”的值设置为货币型，结果保留2位小数 任务二、用“分类汇总”统计“销售额”和“毛利润” 1．准备工作： 创建“销售记录”工作表的副本 用“分类汇总”统计“销售额”和“毛利润” 2．在“销售记录(2)”工作表中，用“分类汇总”计算各个区的饮料“销售额”和“毛利润” （1）在“销售记录(2)”工作表中，对“所在区”字段进行排序 （2）用“分类汇总”统计各区的“销售额”和“毛利润” 隐藏明细数据行 用“分类汇总”统计“销售额”和“毛利润” 3．利用“分类汇总”统计各种饮料的“销售额”和“毛利润”，并找出“毛利润”最大的饮料 （1）在“销售记录(3)”工作表中，用“分类汇总”统计每种饮料的“销售额”和“毛利润” 用“分类汇总”统计“销售额”和“毛利润” （2）将“毛利润”按降序排序，找出“毛利润”最大的饮料 用“分类汇总”统计“销售额”和“毛利润” 4．在“销售记录(4)”工作表中,利用“嵌套分类汇总”统计各个区中各饮料店的“销售额”和“毛利润” 任务三、用“数据透视表”分析各区每种饮料销售情况 用““数据透视表””统计各个区每种饮料的销售情况 在“数据透视表字段列表”任务窗格中进行布局设置如下：将“饮料店”字段拖动到“列标签”区域中。
将“饮料名称”字段拖动到“行标签”区域中将“销售额”字段拖动到“∑数值”区域中，将数据透视表标签“Sheet1”重命名为“销售数据查询” 1. 利用分类汇总数据插入二维簇状柱形图 在“饮料店汇总”工作表中选择数据源，插入“簇状柱形图”图表，操作步骤如下： （1）分别选择“福田店”、“罗湖店”和“南山店”相对应的“销售额”数值和“毛利润”数值；2024/8/24第9章 饮料销售数据分析18任务四、 用“两轴线-柱图”比较“销售额”和“毛利润” （2）在“插入”选项卡的“图表”组中单击“柱形图”按钮 ，在弹出的下拉列表中选择“二维柱形图”中的“簇状柱形图”，插入簇状柱形图 （3）将图表移动到新工作表中2024/8/24第9章 饮料销售数据分析19 案例总结 本章通过对饮料销售数据的处理，介绍了如下内容： VLOOKUP函数、名称的定义、分类汇总、数据透视表等 巩固练习打开“学生成绩(素材).xls”，将其另存为“学生成绩(姓名).xls”，完成各门课程考试成绩的统计工作。