EXCEL实训练习三商场销售数据的分析处理.doc

该【EXCEL实训练习三商场销售数据的分析处理 】是由【windurst】上传分享，文档一共【12】页，该文档可以免费在线阅读，需要了解更多关于【EXCEL实训练习三商场销售数据的分析处理 】的内容，可以使用淘豆网的站内搜索功能，选择自己适合的文档，以下文字是截取该文章内的部分文字，如需要获得完整电子版，请下载此文档到您的设备，方便您编辑和打印。EXCEL实训练****三商场销售数据的分析处理
EXCEL实训练****三
商场销售数据的分析处理
6></a><Bstyle='color:white;background-color:#00aa00'>13></a>.问题的提出小李在深圳市开了若干家饮料连锁店,为了提高管理水平,他打算用Excel工作表来管理销售数据。下图是他制作的各饮料店的销售记录流水帐表。
为了统计“毛利润”,他必须去“饮料基本信息”表中查找每种饮料的“进价”和“售价”。这个工作量实在太大,而且还容易出错。
现在希望:能否输入饮料名称后,让Excel根据这个名称自动去查找该饮料的“单位”、“进价”、“售价”等信息,并存放到表“销售记录”的相应列中。
2(解决方案
通常情况下,如果不借助其它方法的帮助,要想在Excel中解决这个问题,只能到“饮料基本信息”表中一条一条地查找各种饮料的“进价”和“售价”。如果不想这么做,你有什么更好的办法吗,
这个实际需求,开发Excel的工程师,已经为我们想到了。在Excel中有一个函数,就是专门为解决这类问题设计的,这个函数就是VLOOKUP。
小李这个问题,可利用Excel中的查找函数VLOOKUP来解决。它的功能是,在数据区域的第一列中查找指定的数值,并返回数据区域当前行中指定列处的数值。下面来看看应用VLOOKUP函数是如何解决上述问题的。

本案例要解决如下几个问题:
<Bstyle='color:white;background-color:#00aa00'>1(在“销售记录”工作表中用VLOOKUP函数计算饮料的“单位”、“进价”和“售价”等信息,并计算出工作表中的“销售额”和“毛利润”等信息。
2(用“分类汇总”统计出各连锁店和各个区中各种饮料的“销售额”、“毛利润”。
3(用“数据***表”分析各个区中每种饮料的销售情况和各个区中销售情况最好的饮料。
4(另外,为了提高效率、避免出错,小李还想制作一张可以从下拉列表中选择饮料名称,并能自动计算出顾客应交款及应找回款的“新销售记录”工作表。
4(制作过程
(STEP<Bstyle='color:white;background-color:#00aa00'>1—VLOOKUP函数的使用
设计目标
参照下图,根据“销售记录”表中的“饮料名称”列,利用VLOOKUP函数在“饮料基本信息”表中查找其他列(单位、进价和售价)的值。
(<Bstyle='color:white;background-color:#00aa00'>1)VLOOKUP函数是干什么用的VLOOKUP函数的功能:
查找数据区域首列满足条件的元素,并返回数据区域当前行中指定列处的值。VLOOKUP的语法:
注意:
要查找的对象(参数?)一定要定义在查找数据区域(参数?)的第一列。
下面来看看VLOOKUP函数是怎么用的。
(2)如何查找“单位”,
打开文件“饮料销售(素材).xls”,将文件另存为“”。
在“销售记录”工作表中,选中F3单元格?选择“插入?函数?VLOOKUP”?单击“确定”。
由于要根据饮料的名称查找“单位”,所以VLOOKUP函数的第一个参数应该选择饮料名称“D3”。
在“Table_array”区域中选择“饮料价格”工作表中的B2:E44。
由于“单位”数据存放在“饮料信息”数据区域的第2列,所以输入数字“2”。
由于要求饮料名称精确匹配,所以最后一个参数输入“FALSE”。
单击“确定”,可以看到函数准确地返回了“统一奶茶”的单位“瓶”。复制公式,出现了什么问题,,,为什么,如何解决呢,
提示:注意在复制公式时,如果沿着列拖动时,列标要用绝对引用,想想看为什么,修改公式后,重新复制公式。
(3)创建区域名称
选择“饮料名称”工作表。
选中饮料名称、单位、进价、售价所在的区域,即单元格区域B2:E44。
在名称框中输入“自己的姓名,7></a><Bstyle='color:white;background-color:#886800'>A”,
一定要按回车确认。
思考问题:
(<Bstyle='color:white;background-color:#00aa00'>1)区域的定义可以包含“序号”列吗,为什么,
(2)如果名称定义错误了,如何将其删除。
(4)利用区域名查找“进价”和“售价”
选择“销售记录”工作表?选中G3单元格?输入等号“=”?选择函数“VLOOKUP”?单击“确定”。
由于要根据饮料的名称查找“售价”,所以VLOOKUP函数的第一个参数应该选择饮料名称“D3”。
在“Table_array”区域中输入“姓名<Bstyle='color:white;background-color:#886800'>A”。
由于“进价”数据存放在第3列,所以输入数字“3”。
由于要求饮料名称精确匹配,所以最后一个参数输入“FALSE”
单击“确定”,可以看到函数准确地返回了“统一奶茶”的“进价”数据“<Bstyle='color:white;background-color:#00aa00'>”。
复制公式,看一看有什么不同,,,
同样道理在H3单元格中建立查找饮料名称“售价”的公式。
(5)计算销售额、毛利润
(STEP2—分类汇总
首先,建立表“销售记录”的二个副本“销售记录(2)”、“销售记录(3)”和“销售记录(4)”。
(<Bstyle='color:white;background-color:#00aa00'>1)按照“所在区”进行分类汇总
在表“销售记录(2)”中按“所在区”对销售额和毛利润进行分类汇总,汇总结果显示在数据下方(结果见“(样例)”)。
将销售记录(2)改名为“所在区汇总”
结果参见样例中的工作表——“所在区汇总”
(2)按照“饮料名称”进行分类汇总
在表“销售记录(3)”中按“饮料名称”对销售额和毛利润进行分类汇总(汇总方式为求和)对汇总结果中的毛利润列按降序排序,找出毛利润最大的饮料。
将销售记录(3)改名为“饮料名称汇总”
结果参见样例中的工作表——“饮料名称汇总”
(3)用“嵌套分类汇总”统计各个区和各饮料店的饮料“销售额”和“毛利润”在“销售记录(4)”工作表中对“主要关键字”选择“所在区”,“次要关键字”选择“饮料店”排序。
进行第一次“分类汇总”(分类字段为“所在区”)。
进行第二次“分类汇总”(分类字段为“饮料店”)。
将销售记录(4)改名为“嵌套汇总”
结果参见样例中的工作表——“嵌套汇总”
(STEP3—利用数据***表实现统计分析
设计目标
如下图所示,利用数据***表生成三维数据统计报表。
(<Bstyle='color:white;background-color:#00aa00'>1)为“销售记录”表创建数据***表要求:
为表“销售记录”建立数据***表,将数据***表显示在新工作表中,并将数据***表命名为“销售统计”
行显示“饮料名称”,列显示“所在区”,“销售额”显示在数据区。
结果参见样例中的“销售统计”工作表。
(2)根据数据***表找出销售额最大的饮料
对毛利润按降序排序,找出销售额最大的三种饮料。
结果参见样例中的“销售统计”工作表
(3)在“销售统计”工作表中,找出各个区“销售额”最大的饮料
在“销售统计”工作表中,用MAX函数找出每个区“销售额”最大的饮料的“销售额”。
在“销售统计”工作表中,用VLOOKUP函数找出各区“最大销售额”所对应的“饮料名称”。
(STEP4—用“两轴线-柱图”比较“销售额”和“毛利润”
设计目标
现在,小李想用“两轴线-柱图”比较“南山区”、“福田区”和“罗湖区”的销售额和毛利润之间的关系。
如下图所示,用两轴线柱图表来比较销售额和毛利润:
(<Bstyle='color:white;background-color:#00aa00'>1)制作“两轴线图”
在表“所在区汇总”分别选择“所在区”、“销售额”和“毛利润”三列(如下图)所示;
单击“图表向导”按钮,打开“图表向导-4-图表类型”对话框,选择“自定义类型”选项卡,选择“两轴线-柱图”。
单击“下一步”,打开“图表向导-4-图表源数据”对话框;
单击“下一步”,打开“图表向导-4-图表选项”对话框,按下图进行设置;
单击“下一步”,在“图表位置”对话框中,选择“作为其中的对象插入”,单击“完成”按钮。
(2)美化图表
参照“饮料销售(样例).xls”,完成对图表格式的设置(格式可以自定义)。
(STEP5—利用数据有效性制作更方便、更实用的“新销售记录”工作表设计目标
在“”中制作一张“新销售记录表”,并应用数据有效性设置,使得在填写了销售“数量”和选取了“饮料名称”后,可以自动计算出“销售额”、“毛利润”和“毛利率”。
(<Bstyle='color:white;background-color:#00aa00'>1)制作“销售记录”表副本在“”中建立一个“销售记录”表的副本,并将其重命名为“新销售记录”,然后将其前五列的内容删除(只保留标题行),如下图所示。
并增加3个新列“实收”、“应收”和“找回”
(2)对“饮料名称”应用数据有效性设置
选中“饮料价格”表中的“饮料名称”区域,并将其定义为“饮料名称”,如下图所示。
选中“新销售记录”的第4列(“饮料名称”列),然后再选择“数据”?“有效性”打开数
据有效性对话框。
在有效性条件中选择“序列”。
在“来源”中填写“=饮料名称”(注意:“饮料名称”是定义的“饮料名称”区域),如图下所示。
(3)数据有效性的使用
制作完成,如下图所示,试试看,是不是在填写了销售“数量”和选取了“饮料名称”后,可以自动计算出“销售额”、“毛利润”和“毛利率”,很方便吧~
(4)对“饮料店”列进行数据有效性设置
参照“饮料名称”的设置对“饮料店”进行设置
(5)创建“应收”和“找回”列的公式,算法如下:应收=销售额,找回=实收,应收。
(STEP6—让查找公式更完美
(<Bstyle='color:white;background-color:#00aa00'>1)隐藏列
在“新销售记录”工作表中将不需要显示的列(“单位”、“进价”、“销售额”)隐藏起来。(2)前面的结果有缺陷
在上面应用了查找函数VLOOKUP的“销售记录”表中,如果把D3单元格中的饮料名称删去以后,可以看到F3、G3、H3单元格(即:单位、售价和进价)中均返回错误值“#N/<Bstyle='color:white;background-color:#886800'>A”。
大家可以试一下,当VLOOKUP函数在“饮料价格”表中没有找到D3单元格中的饮料名称时都要返回错误值“#N/<Bstyle='color:white;background-color:#886800'>A”。
能不能让当VLOOKUP函数在“饮料信息”表中没有找到D3单元格中的饮料名称时不返回错误值“#N/<Bstyle='color:white;background-color:#886800'>A”,而只什么都不显示(即显示空格)呢,
问题:
利用IF函数和ISERROR函数,使“销售额”、“毛收入”和“毛利润”三列的值,在没有输入饮料名称时,不显示“#VALUE!”错误值。
(2)解决办法
(利用IF和ISERROR函数可以解决上面的问题。试试看,你能解决这个问题吗,
上面的问题变成两种情况:
如果当“VLOOKUP(D3,ylmc,2,FALSE)”部分返回错误值的话,则在F3中显示空字符串。如果“VLOOKUP(D3,ylmc,2,FALSE)”部分工作正常,则在F3单元格中显示饮料对应的“售价”(即VLOOKUP(D3,ylmc,2,FALSE)的值)。
(STEP7—冻结窗口保留标题行
在“销售记录”工作表中,当销售记录增多后,为了保留标题行的内容,可单击<Bstyle='color:white;background-color:#886800'>A<B
style='color:white;background-color:#00aa00'>1单元格,执行“窗口”?“冻结窗口”命令,当滚动屏幕时,可以把第一行(即表头行)的内容一直保留在窗口中。

重点
VLOOKUP函数的使用。
区域名称的定义
分类汇总
数据***表
难点
VLOOKUP函数的参数的选择
数据***表

本案例通过对学生信息表数据的处理,介绍了查找与引用类函数VLOOKUP的用法及分类汇总和数据***表的用法。
(一)你知道了吗,
通过本案例的学****你能回答如下问题吗,
<Bstyle='color:white;background-color:#00aa00'>1(VLOOKUP函数是干什么的,
2(用VLOOKUP函数进行查找时,所要查找的元素为什么一定要在所定义的数据区域的第一
列,
3(VLOOKUP函数中第二个参数的含义是什么,在定义第二个参数时要注意什么,
4(在对VLOOKPU函数沿列进行复制时,一般情况下,为什么列标要用绝对地址,
5(如何定义数据区域,如何删除数据区域,
6(用VLOOKPU函数时,可以不定义数据区域吗,
7(什么是分类汇总,它有什么作用,
8(在分类汇总之前要注意什么,
(二)常见问题及处理方法
下面把大家在本案例的学****过程中容易遇到的一些问题及处理方法列于下表:
常见问题
可能原因
处理方法
“数据区域”名称无法重新定义。
该“数据区域”名称已被定义。
将其删除,重新定义,方法:
选择“插入”?“名称”?“定义”,打开“定义名称”对话框,选择已定义的名称,单击“删除”命令。
VLOOKUP函数返回错误值“#N/<Bstyle='color:white;background-color:#886800'>A”。
(<Bstyle='color:white;background-color:#00aa00'>1)没有把要查找的对象定义在“数据区域”的第一列。
重新定义“数据区域”,把要查找的内容定义在“数据区域”的第一列。
(2)查找的内容在定义的“数据区域”中不存在。
用ISERROR函数使错误值不显示。