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

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