excel销售数据分析实训练习

STEP 4—用“两轴线-柱图”比较“销售额”和“毛利润”
设计目标
现在，小李想用“两轴线-柱图”比较“南山区”、“福田区”和“罗湖区”的销售额和毛利润之间的关系。
如下图所示，用两轴线柱图表来比较销售额和毛利润：
（1）制作“两轴线图”
在表“所在区汇总”分不选择“所在区”、“销售额”和“毛利润”三列（如下图）所示；
在上面应用了查找函数VLOOKUP的“销售记录”表中，假如把D3单元格中的饮料名称删去以后，能够看到F3、G3、H3单元格（即：单位、售价和进价）中均返回错误值“#N/A”。
大伙儿能够试一下，当VLOOKUP函数在“饮料价格”表中没有找到D3单元格中的饮料名称时都要返回错误值“#N/A”。
能不能让当VLOOKUP函数在“饮料信息”表中没有找到D3单元格中的饮料名称时不返回错误值“#N/A”，而只什么都不显示（即显示空格）呢？
假如“VLOOKUP(D3,ylmc,2,FALSE)”部分工作正常，则在F3单元格中显示饮料对应的“售价”（即VLOOKUP(D3,ylmc,2,FALSE)的值）。
STEP 7—冻结窗口保留标题行
4.重点难点
（1）重点
VLOOKUP函数的使用。
区域名称的定义
分类汇总
数据透视表
（2）难点
VLOOKUP函数的参数的选择
excel销售数据分析实训练习
商场销售数据的分析处理
1.咨询题的提出
小李在深圳市开了若干家饮料连锁店，为了提高治理水平，他打算用Excel工作表来治理销售数据。下图是他制作的各饮料店的销售记录流水帐表。
为了统计“毛利润”，他必须去“饮料差不多信息”表中查找每种饮料的“进价”和“售价”。那个工作量实在太大，而且还容易出错。
由于“单位”数据存放在“饮料信息”数据区域的第2列，因此输入数字“2”。
由于要求饮料名称精确匹配，因此最后一个参数输入“FALSE”。
单击“确定”，能够看到函数准确地返回了“统一奶茶”的单位“瓶”。
复制公式,显现了什么咨询题？？？什么缘故？如何解决呢？
提示：注意在复制公式时，假如沿着列拖动时，列标要用绝对引用，想想看什么缘故？
那个实际需求，开发Excel的工程师，差不多为我们想到了。在Excel中有一个函数，确实是专门为解决这类咨询题设计的，那个函数确实是VLOOKUP。
3.实现方法
本案例要解决如下几个咨询题：
1．在“销售记录”工作表中用VLOOKUP函数运算饮料的“单位”、“进价”和“售价”等信息，并运算出工作表中的“销售额”和“毛利润”等信息。
2．用“分类汇总”统计出各连锁店和各个区中各种饮料的“销售额”、“毛利润”。
3．用“数据透视表”分析各个区中每种饮料的销售情形和各个区中销售情形最好的饮料。
4．另外，为了提高效率、幸免出错，小李还想制作一张能够从下拉列表中选择饮料名称，并能自动运算出顾客应交款及应找回款的“新销售记录”工作表。
4．制作过程
（1）制作“销售记录”表副本
在“饮料销售.xls”中建立一个“销售记录”表的副本，并将其重命名为“新销售记录”，然后将其前五列的内容删除（只保留标题行），如下图所示。
并增加3个新列“实收”、“应收”和“找回”
（2）对“饮料名称”应用数据有效性设置
选中“饮料价格”表中的“饮料名称”区域，并将其定义为“饮料名称”，如下图所示。
（3）用“嵌套分类汇总”统计各个区和各饮料店的饮料“销售额”和“毛利润”
在“销售记录(4)”工作表中对“要紧关键字”选择“所在区”，“次要关键字”选择“饮料店”排序。
进行第二次“分类汇总”（分类字段为“饮料店”）。
将销售记录(4)改名为“嵌套汇总”
结果参见样例中的工作表——“嵌套汇总”
STEP 3—利用数据透视表实现统计分析
使用“填充柄”沿列拖动复制公式时显现错误值“#N/A”。
分类汇总结果不正确。
（1）在“分类汇总”之前没先按要“分类”的字段排序。
先按要分类的字段排序，然后再进行“分类汇总”。
（2）尽管已先按要“分类”的字段排序，但在“分类汇总”时，分类字段选择不正确（没有选择已排序的字段）。
在“分类汇总”时，“分类”字段选择已排序的字段。
单击“图表向导”按钮，打开“图表向导-4-图表类型”对话框，选择“自定义类型”选项卡，选择“两轴线-柱图”。
单击“下一步”，打开“图表向导-4-图表源数据”对话框；
单击“下一步”，打开“图表向导-4-图表选项”对话框，按下图进行设置；
单击“下一步”，在“图表位置”对话框中，选择“作为其中的对象插入”，单击“完成”按钮。
（4）对“饮料店”列进行数据有效性设置
参照“饮料名称”的设置对“饮料店”进行设置
（5）创建“应收”和“找回”列的公式，算法如下：应收=销售额，找回=实收－应收。
STEP6—让查找公式更完美
（1）隐藏列
在“新销售记录”工作表中将不需要显示的列（“单位”、“进价”、“销售额”）隐藏起来。
（2）前面的结果有缺陷
现在期望：能否输入饮料名称后，让Excel依照那个名称自动去查找该饮料的“单位”、“进价”、“售价”等信息，并存放到表“销售记录”的相应列中。
2．解决方案
通常情形下，假如不借助其它方法的关心，要想在Excel中解决那个咨询题，只能到“饮料差不多信息”表中一条一条地查找各种饮料的“进价”和“售价”。假如不想这么做，你有什么更好的方法吗？
复制公式，看一看有什么不同？？？
同样道理在H3单元格中建立查找饮料名称“售价”的公式。
（5）运算销售额、毛利润
STEP 2—分类汇总
第一，建立表“销售记录”的二个副本“销售记录(2)”、“销售记录(3)”和“销售记录(4)”。
（1）按照“所在区”进行分类汇总
在表“销售记录(2)”中按“所在区”对销售额和毛利润进行分类汇总，汇总结果显示在数据下方（结果见“(样例)饮料销售.xls”）。
STEP 1—VLOOKUP函数的使用
设计目标
参照下图，依照“销售记录”表中的“饮料名称”列，利用VLOOKUP函数在“饮料差不多信息”表中查找其他列（单位、进价和售价）的值。
（1）VLOOKUP函数是干什么用的
VLOOKUP函数的功能：
查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。
选中“新销售记录”的第4列（“饮料名称”列），然后再选择“数据”→“有效性”打开数据有效性对话框。
在有效性条件中选择“序列”。
在“来源”中填写“=饮料名称”（注意：“饮料名称”是定义的“饮料名称”区域），如图下所示。
（3）数据有效性的使用
制作完成，如下图所示，试试看，是不是在填写了销售“数量”和选取了“饮料名称”后，能够自动运算出“销售额”、“毛利润”和“毛利率”，专门方便吧！
要求：
（A）在表“一班(新)”中，依照学号，查找“姓名”、“大学英语”和总分。
（B）另外学校要对上学期“大学英语”考试成绩是“优秀”和“良好”的学生进行奖励。奖励方法见素材中表“奖品及加分”，其中“大学英语”考试成绩是“优秀”的同学总分增加2%、“良好”的总分增加1%。
（2）解决方法
请利用“学生信息(素材).xls”，在表“一班(新)”中，依照学号，应用VLOOKUP函数填写出一班学生的“姓名”、“大学英语”和“总分”，对没有成绩的填写“缺考”。
选择“销售记录”工作表→选中G3单元格→输入等号“=”→选择函数“VLOOKUP”→单击“确定”。
在“Table_array”区域中输入“姓名A”。
由于“进价”数据存放在第3列，因此输入数字“3”。
由于要求饮料名称精确匹配，因此最后一个参数输入“FALSE”
单击“确定”，能够看到函数准确地返回了“统一奶茶”的“进价”数据“1.9”。
VLOOKUP的语法：
注意：
下面来看看VLOOKUP函数是如何用的。
（2）如何查找“单位”？
打开文件“饮料销售(素材).xls”，将文件另存为“姓名_饮料销售.xls”。
在“销售记录”工作表中，选中F3单元格→选择“插入→函数→VLOOKUP”→单击“确定”。
在“Table_array”区域中选择“饮料价格”工作表中的B2:E44。
常见咨询题
可能缘故
处理方法
“数据区域”名称无法重新定义。
该“数据区域”名称已被定义。
将其删除，重新定义，方法：
选择“插入”→“名称”→“定义”，打开“定义名称”对话框，选择已定义的名称，单击“删除”命令。
VLOOKUP函数返回错误值“#N/A”。
（2）查找的内容在定义的“数据区域”中不存在。
用ISERROR函数使错误值不显示。
数据透视表
5.案例总结与常见咨询题
本案例通过对学生信息表数据的处理，介绍了查找与引用类函数VLOOKUP的用法及分类汇总和数据透视表的用法。
（一）你明白了吗？
通过本案例的学习，你能回答如下咨询题吗？
1．VLOOKUP函数是干什么的？
3．VLOOKUP函数中第二个参数的含义是什么？在定义第二个参数时要注意什么？
用IF函数填写一班学生的“英语等级”，对缺考的学生填写“英语缺考”。
英语成绩>=90，“优秀”
80<=英语成绩<90，“良好”
4．在对VLOOKPU函数沿列进行复制时，一样情形下，什么缘故列标要用绝对地址？
5．如何定义数据区域，如何删除数据区域？
6．用VLOOKPU函数时，能够不定义数据区域吗？
7．什么是分类汇总？它有什么作用？
8．在分类汇总之前要注意什么？
（二）常见咨询题及处理方法
下面把大伙儿在本案例的学习过程中容易遇到的一些咨询题及处理方法列于下表：
咨询题：
利用IF函数和ISERROR函数，使“销售额”、“毛收入”和“毛利润”三列的值，在没有输入饮料名称时，不显示“#VALUE!”错误值。