《计算机应用基础教学全套课件第3版 许晞 教学课件 第9章 Excel综合应用2——饮料店销售数据分析》由会员分享，可在线阅读，更多相关《计算机应用基础教学全套课件第3版 许晞 教学课件 第9章 Excel综合应用2——饮料店销售数据分析（33页珍藏版）》请在金锄头文库上搜索。
1、计算机应用基础 学习目标 熟练掌握VLOOKUP函数的使用方法熟练掌握SUMIF和SUMIFS函数的使用方法熟练掌握分类汇总使用方法能够用数据透视表分析 统计数据 2020 4 21 第9章饮料销售数据分析 2 目录 9 1饮料销售案例分析9 2实现方法9 2 1用VLOOKUP函数查找 单位 进价 和 售价 9 2 2用SUMIF函数和SUMIFS函数统计各饮料店销售额9 2 3用分类汇总统计 销售额 和 毛利润 9 2 4用两轴线 柱图比较 销售额 和 毛利润 9 2 5用数据透视表深入分析饮料销售数据9 3案例总结9 4课后练习 2020 4 21 第9章饮料销售数据分析 3 9 1饮料
2、销售案例分析 任务的提出小李在深圳市开了若干家饮料连锁店 为了提高管理水平 他打算用Excel工作表来管理销售数据 统计各连锁店的销售额和毛利润 并对销售数据进行分析 解决方案利用VLOOKUP函数查找各种饮料的价格 利用SUMIF和SUMIFS函数统计销售各种饮料的销售额和毛利润 用分类汇总统计 销售额 和 毛利润 用数据透视表分析饮料销售数据 2020 4 21 第9章饮料销售数据分析 4 9 2 1用VLOOKUP函数查找 单位 进价 和 售价 VLOOKUP函数VLOOKUP函数的功能 查找数据区域首列满足条件的元素 并返回数据区域当前行中指定列处的值 VLOOKUP函数的语法 202
3、0 4 21 第9章饮料销售数据分析 5 9 2 1用VLOOKUP函数计算 单位 进价 和 售价 2 在 饮料价格 工作表中创建 价格 区域 操作步骤如下 1 在 饮料价格 工作表中选择饮料名称 单位 进价 售价所在的区域 B3 E38 2 在名称框中输入 价格 后 按Enter键确认 创建 价格 数据区域 2020 4 21 第9章饮料销售数据分析 6 9 2 1用VLOOKUP函数计算 单位 进价 和 售价 3 用VLOOKUP函数查找饮料的 单位 进价 和 售价 操作步骤如下 1 在 销售记录 工作表中选择目标单元格E3 插入VLOOKUP函数 2020 4 21 第9章饮料销售数据分
4、析 7 9 2 1用VLOOKUP函数计算 单位 进价 和 售价 2 在G3单元格中用VLOOKUP函数在 价格 区域中查找饮料的 进价 3 在H3单元格中用VLOOKUP函数在 价格 区域中查找饮料的 售价 4 选择数据区域E3 G3 双击填充柄 复制公式 2020 4 21 第9章饮料销售数据分析 8 9 2 1用VLOOKUP函数计算 单位 进价 和 售价 3 用 数据有效性 制作饮料价格查询系统在 销售统计 工作表中对B2单元格设置 数据有效性 操作步骤如下 1 在 饮料价格 工作表中 选中区域 B2 B38 定义为 饮料名称 2 在 销售统计 工作表中选中单元格B2 在 数据有效性
5、对话框中 选择 设置 选项卡 在 允许 下拉列表中选择 序列 在 来源 中输入 饮料名称 2020 4 21 第9章饮料销售数据分析 9 9 2 2用SUMIF函数和SUMIFS函数统计各饮料店销售额 1 用SUMIF函数统计各个饮料店的销售额和毛利润SUMIF函数的功能 在条件区域中根据指定条件对满足条件的单元格求和 SUMIF函数的语法 2020 4 21 第9章饮料销售数据分析 10 9 2 2用SUMIF函数和SUMIFS函数统计各饮料店销售额 用SUMIF函数统计各个饮料店的销售额和毛利润 操作步骤如下 1 在 销售记录 工作表中 以 饮料店 销售额 与 毛利润 为名称定义3个区域
6、2 在 销售统计 工作表中 在B9单元格中 插入SUMIF函数 5 鼠标指针指向B9单元格的填充柄 向右拖动填充柄至F9 复制公式 得到各饮料店的销售额 2020 4 21 第9章饮料销售数据分析 11 9 2 2用SUMIF函数和SUMIFS函数统计各饮料店销售额 3 选中B10单元格 用相同的方法可用SUMIF函数统计各饮料店的毛利润 4 向右拖动B10单元格的填充柄至F10 复制公式 得到各饮料店的毛利润 2020 4 21 第9章饮料销售数据分析 12 9 2 2用SUMIF函数和SUMIFS函数统计各饮料店销售额 2 用SUMIFS函数统计5种饮料在各饮料店的销售额SUMIFS函数的
7、功能 对区域中满足多个条件的单元格求和 SUMIFS的语法 2020 4 21 第9章饮料销售数据分析 13 用SUMIFS函数统计各饮料店中5种饮料的销售额 操作步骤如下 1 在 销售统计 工作表中 选中B14单元格 插入SUMIFS函数 2 鼠标指针指向B14单元格的填充柄 向右拖动填充柄至D14单元格后 再向下拖动至D18单元格 复制公式 2020 4 21 第9章饮料销售数据分析 14 9 2 2用SUMIF函数和SUMIFS函数统计各饮料店销售额 9 2 3用分类汇总统计 销售额 和 毛利润 1 利用分类汇总统计各个饮料店的 销售额 和 毛利润 在 饮料店汇总 工作表中 用 分类汇总
8、 统计各个饮料店的 销售额 和 毛利润 操作步骤如下 1 在 饮料店汇总 工作表中 对 饮料店 字段进行排序 2 按 饮料店 对 销售额 和 毛利润 进行分类汇总 3 隐藏分类汇总表中的明细数据行 2020 4 21 第9章饮料销售数据分析 15 9 2 3用分类汇总统计 销售额 和 毛利润 2 利用分类汇总统计各种饮料的销售额和毛利润 并找出毛利润最大的饮料在 饮料汇总 工作表中 用 分类汇总 统计各种饮料的 销售额 和 毛利润 并隐藏分类汇总表中的明细数据行 将 毛利润 按降序排序 找出 毛利润 最大的饮料 2020 4 21 第9章饮料销售数据分析 16 9 2 3用分类汇总统计 销售额
9、 和 毛利润 3 利用嵌套分类汇总统计每种饮料在所有饮料店的 销售额 和 毛利润 在 嵌套汇总 工作表中 用嵌套分类汇总统计每种饮料在所有饮料店的 销售额 和 毛利润 操作步骤如下 1 在 嵌套汇总 工作表中 按 饮料名称 为 主要关键字 饮料店 为 次要关键字 进行排序 2020 4 21 第9章饮料销售数据分析 17 9 2 3用分类汇总统计 销售额 和 毛利润 2 按 饮料名称 对 销售额 和 毛利润 进行分类汇总 2020 4 21 第9章饮料销售数据分析 18 9 2 3用分类汇总统计 销售额 和 毛利润 3 在前面分类汇总的基础上 再进行第2次 分类汇总 其中 分类字段 为 饮料店
10、 汇总方式 为 求和 选定汇总项 为 销售额 和 毛利润 取消勾选 替换当前分类汇总 复选框 2020 4 21 第9章饮料销售数据分析 19 9 2 4用两轴线 柱图比较 销售额 和 毛利润 1 利用分类汇总数据插入二维簇状柱形图在 饮料店汇总 工作表中选择数据源 插入 簇状柱形图 图表 操作步骤如下 1 分别选择 福田店 罗湖店 和 南山店 相对应的 销售额 数值和 毛利润 数值 2020 4 21 第9章饮料销售数据分析 20 9 2 4用两轴线 柱图比较 销售额 和 毛利润 2 在 插入 选项卡的 图表 组中单击 柱形图 按钮 在弹出的下拉列表中选择 二维柱形图 中的 簇状柱形图 插入
11、簇状柱形图 3 将图表移动到新工作表中 2020 4 21 第9章饮料销售数据分析 21 9 2 4用两轴线 柱图比较 销售额 和 毛利润 2 将二维簇状柱形图更改为两轴线 柱图将二维簇状柱形图更改为两轴线 柱图 操作步骤如下 1 将数据系列 毛利润 设置为次坐标轴 2020 4 21 第9章饮料销售数据分析 22 9 2 4用两轴线 柱图比较 销售额 和 毛利润 2 将 销售额 数据系列显示为柱形图 毛利润 数据系列显示为折线图后的两轴线 柱图 2020 4 21 第9章饮料销售数据分析 23 9 2 4用两轴线 柱图比较 销售额 和 毛利润 3 设置图表格式为 毛利润 添加系列名称标签 2
12、020 4 21 第9章饮料销售数据分析 24 9 2 5用数据透视表深入分析饮料销售数据 1 用数据透视表统计各种饮料在各饮料店的销售额在 销售记录 工作表中 用数据透视表统计各种饮料在各饮料店的销售额 操作步骤如下 1 在 销售记录 工作表中单击数据区中的任一单元格 在 插入 选项卡的 表格 组中单击 数据透视表 按钮 打开 创建数据透视表 对话框 2 在对话框中系统会自动选择数据区 在 选择放置数据透视表的位置 区域中选择 新工作表 创建数据透视表 Sheet1 2020 4 21 第9章饮料销售数据分析 25 9 2 5用数据透视表深入分析饮料销售数据 3 在 数据透视表字段列表 任务
13、窗格中进行布局设置如下 将 饮料店 字段拖动到 列标签 区域中 将 饮料名称 字段拖动到 行标签 区域中 将 销售额 字段拖动到 数值 区域中 将数据透视表标签 Sheet1 重命名为 销售数据查询 2020 4 21 第9章饮料销售数据分析 26 9 2 5用数据透视表深入分析饮料销售数据 用 切片器 查询5种饮料在各饮料店中的销售额 操作步骤如下 1 在 销售数据查询 工作表中 打开 插入切片器 对话框 勾选 饮料店 和 饮料名称 2个复选框 单击 确定 按钮 在数据透视表中插入了 饮料店 和 饮料名称 2个 切片器 将2个 切片器 并排显示 2 在 饮料店 切片器中 单击 福田店 后 再
14、按住Ctrl键不放 依次单击 罗湖店 和 南山店 在 饮料名称 切片器中 单击 百事可乐 后 再按住Ctrl键不放 依次选中 果粒橙 红牛 可口可乐 和 雪碧 5种饮料 在透视表中显示的数据就是满足条件的数据 2020 4 21 第9章饮料销售数据分析 27 9 2 5用数据透视表深入分析饮料销售数据 2 用数据透视表分析各饮料店销售数据在 销售记录 工作表中创建新的数据透视表 分析各饮料店销售数据 操作步骤如下 1 在 销售记录 工作表中 创建新的数据透视表 并将数据透视表标签重命名为 销售数据分析 2 将 饮料店 字段拖动到 列标签 区域中 将 销售额 字段拖动到 数值 区域中 2020
15、4 21 第9章饮料销售数据分析 28 9 2 5用数据透视表深入分析饮料销售数据 3 第2次将 销售额 字段拖动到 数值 区域中 将 列标签 中的 数值 拖动到 行标签 中 4 单击 数值 区域中的 求和项 销售额2 选项 在弹出的列表中选择 值字段设置 打开 值字段设置 对话框 在对话框中设置如下 在 自定义名称 文本框中更改内容为 求和项 销售额 百分比 选择 值显示方式 选项卡 在 值显示方式 下拉列表中选择 全部汇总百分比 2020 4 21 第9章饮料销售数据分析 29 9 2 5用数据透视表深入分析饮料销售数据 5 第3次将 销售额 字段拖动到 数值 区域中 在 值字段设置 对话
16、框中设置如下 在 值汇总方式 中选择 平均值 并将 自定义名称 的内容更改为 平均值项 销售额 单击 数字格式 按钮 在打开的 设置单元格格式 对话框中 将数值的 小数位数 设置为 2 6 用同样的方法 分别对各饮料店销售额的最大值 最小值以及各饮料店的毛利润的求和项进行统计 2020 4 21 第9章饮料销售数据分析 30 9 2 5用数据透视表深入分析饮料销售数据 在 销售数据分析 工作表中 用 迷你图 表示销售数据 操作步骤如下 1 在 销售数据分析 工作表中 选中H5单元格 打开 创建迷你图 对话框 在对话框的 选择所需的数据 区域中的 数据范围 中选择单元格区域B5 F5 在H5单元格中插入 柱形迷你图 2 选中H5单元格 鼠标指向H5单元格右下角的填充柄 向下拖动填充柄至H10 复制 柱形迷你图 3 选中 柱形迷你图 区域 H5 H10 将高点的颜色设置为 主题颜色 区域中的 红色 强调文字颜色2 将低点的颜色设置为 主题颜色 区域中的 橄榄色 强调文字颜色3 2020 4 21 第9章饮料销售数据分析 31 9 3案例总结 2020 4 21 第9章饮料销售数据分析 32
