Excel综合应用2—饮料店销售数据分析.ppt

计算机应用基础计算机应用基础 主编：许晞 副主编：刘艳丽 曾煌兴 郑杰 秦高德 学习目标 v熟练掌握VLOOKUP函数的使用方法 v熟练掌握分类汇总的使用方法 v掌握ISERROR函数的用法及ISERROR函数 与IF函数的嵌套使用方法 v能够用数据透视表分析、统计数据 v能够进行数据有效性设置 Date 2 第8章 Excel综合应用2饮料店销售数据分析 目 录 8.1 饮料店销售案例分析 8.2 实现方法 8.3 案例总结 8.4 课后练习 Date 3 第8章 Excel综合应用2饮料店销售数据分析 8.1 饮料店销售案例分析 v任务的提出 小李在深圳市开了若干家饮料连锁店，为了提 高管理水平，他打算用Excel工作表来管理销售数据。 v解决方案 利用查找与引用函数VLOOKUP和分类汇总 以及数据透视表来解决。 返回目录 Date 4 第8章 Excel综合应用2饮料店销售数据分析 8.2 实现方法 8.2.1 用VLOOKUP函数计算“单位”、“进价”和“售价” 8.2.2 用“分类汇总”统计“销售额”和“毛利润” 8.2.3 用“数据透视表”分析各区每种饮料销售情况 8.2.4 用“两轴线-柱图”比较“销售额”和“毛利润” 8.2.5 制作更方便、更实用的“新销售记录”表 8.2.6 使“新销售记录”表更完美 返回目录 Date 5 第8章 Excel综合应用2饮料店销售数据分析 8.2.1 用VLOOKUP函数计算“单位”、“进价”和“ 售价” vVLOOKUP函数 VLOOKUP函数的功能：查找数据区域首 列满足条件的元素，并返回数据区域当前行中指定 列处的值。 VLOOKUP函数的语法： Date 6 第8章 Excel综合应用2饮料店销售数据分析 8.2.1 用VLOOKUP函数计算“单位”、“进价”和“ 售价” 2在“饮料价格”工作表中创建“价格”区域， 操作步骤如下： （1）选中饮料名称、单位、进价、售价所 在的区域 （2）在菜单栏中选择“插入”“名称 ”“定义”命令，在名称框中输入“价格”，单 击“确定”按钮。 Date 7 第8章 Excel综合应用2饮料店销售数据分析 8.2.1 用VLOOKUP函数计算“单位”、“进价”和“ 售价” 3用VLOOKUP函数查找饮料“单位”，操作 步骤如下： （1）选择“销售记录”工作表，选中目标 单元格F3。 选择“插入函数”命令，打开“插入 函数”对话框，在“选择函数”列表框中选择 “VLOOKUP” 。 Date 8 第8章 Excel综合应用2饮料店销售数据分析 8.2.1 用VLOOKUP函数计算“单位”、“进价”和“ 售价” （2）在“函数参数”对话框中填写各个参数 查找数据的过程演示 Date 9 第8章 Excel综合应用2饮料店销售数据分析 8.2.1 用VLOOKUP函数计算“单位”、“进价”和“ 售价” 在G3单元格中用VLOOKUP函数在“ 价格”区域中查找饮料的“进价”。 在H3单元格中用VLOOKUP函数在“ 价格”区域中查找饮料的“售价”。 用公式计算“销售额”和“毛利润 ”2列的值。 其中：销售额=售价数量 毛利润=(售价-进价)数量 Date 10 第8章 Excel综合应用2饮料店销售数据分析 8.2.1 用VLOOKUP函数计算“单位”、“进价”和“ 售价” （3）对“销售记录”工作表进行格式设置 返回 Date 11 第8章 Excel综合应用2饮料店销售数据分析 8.2.2 用“分类汇总”统计“销售额”和“毛利 润” 1创建“销售记录”工作表的副本 鼠标右键单击“销售记录”工作表标签， 在弹出的快捷菜单 中选择“移动或复制工作表”，并选中“ 建立副本”复选框。 Date 12 第8章 Excel综合应用2饮料店销售数据分析 8.2.2 用“分类汇总”统计“销售额”和“毛利润 ” 2在“销售记录(2)”工作表中，用“分类汇总”计 算各个区的饮料“销售额”和“毛利润” （1）在“销售记录(2)”工作表中，对“所在区”字段进行排 序 Date 13 第8章 Excel综合应用2饮料店销售数据分析 8.2.2 用“分类汇总”统计“销售额”和“毛利润 ” （2）用“分类汇总”统计各区的“销售额”和“毛利润” Date 14 第8章 Excel综合应用2饮料店销售数据分析 8.2.2 用“分类汇总”统计“销售额”和“毛利润 ” 3利用“分类汇总”统计各种饮料的“销售额”和“ 毛利润”，并找出“毛利润”最大的饮料 （1）在“销售记录(3)”工作表中，用“分类汇总”统计每种 饮料的“销售额”和“毛利润” Date 15 第8章 Excel综合应用2饮料店销售数据分析 8.2.2 用“分类汇总”统计“销售额”和“毛利润 ” （2）将“毛利润”按降序排序，找出“毛利润”最大的饮料 Date 16 第8章 Excel综合应用2饮料店销售数据分析 8.2.2 用“分类汇总”统计“销售额”和“毛利润 ” 4利用“嵌套分类汇总”统计各个区中各饮料店 的“销售额”和“毛利润” 返回 Date 17 第8章 Excel综合应用2饮料店销售数据分析 8.2.3用“数据透视表”分析各区每种饮料销售情 况 1用“数据透视表”统计各个区每种饮料的销售 情况 在“销售记录”工作表中，用“数据透视表”统计各个区每 种饮料的“销售额”，对“总计”列按“降序”排序，找出各个区“销 售额”最大的饮料 。 Date 18 第8章 Excel综合应用2饮料店销售数据分析 8.2.3 用“数据透视表”分析各区每种饮料销售情 况 1查找每个区销售额最大的饮料 第一步，先用MAX函数找出每个区销售额最大的饮料 所对应的销售额； 第二步，再用VLOOKUP函数找出各区销售额最大的 饮料所对应的饮料名称。 返回 Date 19 第8章 Excel综合应用2饮料店销售数据分析 8.2.4 用“两轴线-柱图”比较“销售额”和“毛利润” 1选中“销售记录(2)”工作表中的隐藏分类汇总明细数 据后的数据 2在“图表类型”列表框中选择“两轴线-柱图” Date 20 第8章 Excel综合应用2饮料店销售数据分析 8.2.4 用“两轴线-柱图”比较“销售额”和“毛利润” 3对图表区域进行格式化设置 返回 Date 21 第8章 Excel综合应用2饮料店销售数据分析 8.2.5 制作更方便、更实用的“新销售记录”表 1创建“销售记录”工作表的副本，将其重命 名为“新销售记录” 2 对“饮料名称”列应用数据有效性 Date 22 第8章 Excel综合应用2饮料店销售数据分析 8.2.5 制作更方便、更实用的“新销售记录”表 3将不需要显示的列（“单位”、“进价”、“销 售额”）隐藏起来 返回 Date 23 第8章 Excel综合应用2饮料店销售数据分析 8.2.6 使“新销售记录”表更完美 如何使 “新销售记录”表中的“售价”、“应收” 和“找回”列的错误值不显示？ Date 24 第8章 Excel综合应用2饮料店销售数据分析 8.2.6 使“新销售记录”表更完美 利用IF函数和ISERROR函数使“新销售记录”表 中的错误值不显示 返回 Date 25 第8章 Excel综合应用2饮料店销售数据分析 8.3 案例总结 本章通过对饮料销售数据的处理，介绍了 如下内容： VLOOKUP函数、名称的定义、分类汇总、 数据透视表、IF函数和ISERROR函数的嵌套应用以及数 据有效性等。 下面把学习过程中经常遇到的问题及处理 方法列于下表中 。 Date 26 第8章 Excel综合应用2饮料店销售数据分析 8.3 案例总结 常见问题出错原因处理方法 “数据区域”名称 无法重新定义 该“数据区域”名称已被定义 将其删除，重新定义。方法：选择“插入” “名称”“定义”，打开“定义名称”对话 框，选择已定义的名称，单击“删除”命令 VLOOKUP函数返 回错误值“#N/A” （1）没有把要查找的对象定义在“数 据区域”的第 1 列 重新定义“数据区域”，把要查找的内容 定义在“数据区域”的第 1 列 （2）查找的内容在定义的“数据区域” 中不存在 用ISERROR函数使错误值不显示 使用“填充柄”沿列 拖动复制公式时 出现错误值#N/A” 要查找的对象（VLOOKUP函数的第 一个参数）列标没有用绝对引用 在使用“填充柄”沿列拖动前先将第一个 参数的列标绝对引用 分类汇总结果 不正确 （1）在“分类汇总”之前没有先按要“分 类”的字段排序 先按要分类的字段排序，然后再进行 “分类汇总” （2）虽然已先按要“分类”的字段排序 ，但在“分类汇总”时，分类字段选 择不正确（没有选择已排序的字 段） 在“分类汇总”时，“分类”字段选择已 排序的字段 返回目录 Date 27 第8章 Excel综合应用2饮料店销售数据分析 8.4 课后练习 打开“MLC考试报名表(素材).xls”，将其 另存为“MLC考试报