《营销策略培训实训练习销售数据分析》由会员分享,可在线阅读,更多相关《营销策略培训实训练习销售数据分析(35页珍藏版)》请在金锄头文库上搜索。
1、EXCEL实训练习三商场销售数据的分析处理1. 问题的提出小李在深圳市开了若干家饮料连锁店,为了提高管理水平,他打算用Excel工作表来管理销售数据。下图是他制作的各饮料店的销售记录流水帐表。为了统计“毛利润”,他必须去 “饮料基本信息”表中查找每种饮料的“进价”和“售价”。这个工作量实在太大,而且还容易出错。现在希望:能否输入饮料名称后,让Excel根据这个名称自动去查找该饮料的“单位”、“进价”、“售价”等信息,并存放到表“销售记录”的相应列中。2解决方案通常情况下,如果不借助其它方法的帮助,要想在Excel中解决这个问题,只能到“饮料基本信息”表中一条一条地查找各种饮料的“进价”和“售价
2、”。如果不想这么做,你有什么更好的办法吗?这个实际需求,开发Excel的工程师,已经为我们想到了。在Excel中有一个函数,就是专门为解决这类问题设计的,这个函数就是VLOOKUP。小李这个问题,可利用Excel中的查找函数VLOOKUP来解决。它的功能是,在数据区域的第一列中查找指定的数值,并返回数据区域当前行中指定列处的数值。下面来看看应用VLOOKUP函数是如何解决上述问题的。3. 实现方法本案例要解决如下几个问题:1在“销售记录”工作表中用VLOOKUP函数计算饮料的“单位”、“进价”和“售价”等信息,并计算出工作表中的“销售额”和“毛利润”等信息。2用“分类汇总”统计出各连锁店和各个
3、区中各种饮料的“销售额”、“毛利润”。3用“数据透视表”分析各个区中每种饮料的销售情况和各个区中销售情况最好的饮料。4另外,为了提高效率、避免出错,小李还想制作一张可以从下拉列表中选择饮料名称,并能自动计算出顾客应交款及应找回款的“新销售记录”工作表。4制作过程STEP 1VLOOKUP函数的使用设计目标参照下图,根据“销售记录”表中的“饮料名称”列,利用VLOOKUP函数在“饮料基本信息”表中查找其他列(单位、进价和售价)的值。根据以上两个表中的数据得到下图中的结果最后计算销售额、毛利润、毛利率(1)VLOOKUP函数是干什么用的 VLOOKUP函数的功能:查找数据区域首列满足条件的元素,并
4、返回数据区域当前行中指定列处的值。 VLOOKUP的语法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)精确查找还是模糊查找?查找区域第几列的值?到哪个区域查找?查找什么?注意:要查找的对象(参数)一定要定义在查找数据区域(参数)的第一列。下面来看看VLOOKUP函数是怎么用的。(2)如何查找“单位”? 打开文件“饮料销售(素材).xls”,将文件另存为“姓名_饮料销售.xls”。 在“销售记录”工作表 中,选中F3单元格 选择“插入函数VLOOKUP” 单击“确定”。 由于要根据饮料的名称查找“单位”,所以VLOOKU
5、P函数的第一个参数应该选择饮料名称“D3”。 在“Table_array”区域中选择“饮料价格”工作表中的B2:E44。 由于“单位”数据存放在“饮料信息”数据区域的第2列,所以输入数字“2”。 由于要求饮料名称精确匹配,所以最后一个参数输入“FALSE”。 单击“确定”,可以看到函数准确地返回了“统一奶茶”的单位“瓶”。 复制公式,出现了什么问题?为什么?如何解决呢?提示:注意在复制公式时,如果沿着列拖动时,列标要用绝对引用,想想看为什么? 修改公式后,重新复制公式。(3)创建区域名称 选择“饮料名称”工作表。 选中饮料名称、单位、进价、售价所在的区域,即单元格区域B2:E44。F温馨提示:
6、也可以采用以下方法定义区域名称: 选择菜单“插入名称定义”命令,打开“定义名称”对话框, 在名称框中输入“姓名A”后,单击“确定”按钮,“姓名A”区域名称创建完成 在名称框中输入“自己的姓名A”,一定要按回车确认。思考问题:(1)区域的定义可以包含“序号”列吗?为什么?(2)如果名称定义错误了,如何将其删除。 (4)利用区域名查找“进价”和 “售价” 选择“销售记录”工作表 选中G3单元格 输入等号“=”选择函数“ VLOOKUP” 单击“确定”。 由于要根据饮料的名称查找“售价”,所以VLOOKUP函数的第一个参数应该选择饮料名称“D3”。 在“Table_array”区域中输入“姓名A”。
7、 由于“进价”数据存放在第3列,所以输入数字“3”。 由于要求饮料名称精确匹配,所以最后一个参数输入“FALSE”怎么样,对于VLOOKUP函数你是否有了一定的认识?如果还不会,请多做几遍,慢慢去体会。 单击“确定”,可以看到函数准确地返回了“统一奶茶”的“进价”数据“1.9”。 复制公式,看一看有什么不同? 同样道理在H3单元格中建立查找饮料名称“售价”的公式。(5)计算销售额、毛利润F温馨提示: 表格中数据之间的关系为:销售额 售价*数量毛利润 (售价进价)数量STEP 2分类汇总首先,建立表“销售记录”的二个副本“销售记录(2)”、“销售记录(3)”和“销售记录(4)”。F温馨提示: 按
8、住Ctrl键拖动工作表标签(1)按照“所在区”进行分类汇总F温馨提示: 选择“所在区”列中的任意单元格 单击“升序”按钮进行排序(排序的作用是什么?) 然后选择“数据”“分类汇总”。 在表“销售记录(2)”中按“所在区”对销售额和毛利润进行分类汇总,汇总结果显示在数据下方(结果见“(样例)饮料销售.xls”)。 将销售记录(2)改名为“所在区汇总” 结果参见样例中的工作表“所在区汇总”(2)按照“饮料名称”进行分类汇总 在表“销售记录(3)”中按“饮料名称”对销售额和毛利润进行分类汇总(汇总方式为求和) 对汇总结果中的毛利润列按降序排序,找出毛利润最大的饮料。 将销售记录(3)改名为“饮料名称
9、汇总” 结果参见样例中的工作表“饮料名称汇总”F温馨提示: 首先按照“饮料名称”对“销售额”和“毛利润”进行分类汇总; 然后,对“毛利润”进行降序排序。(3)用“嵌套分类汇总”统计各个区和各饮料店的饮料“销售额”和“毛利润” 在“销售记录(4)”工作表中对“主要关键字”选择“所在区”,“次要关键字”选择“饮料店”排序。 进行第一次“分类汇总”(分类字段为“所在区”)。 进行第二次“分类汇总”(分类字段为“饮料店”)。 将销售记录(4)改名为“嵌套汇总”F温馨提示: 首先,选择“数据”“排序”命令,在主要关键字中选择“所在区”,在次要关键字中选择“饮料店”; 然后,先按照“所在区”进行分类汇总;
10、 再按照“饮料店”进行汇总,但是第二次汇总时,必须取消“替换当前分类汇总”。 结果参见样例中的工作表“嵌套汇总”STEP 3利用数据透视表实现统计分析设计目标如下图所示,利用数据透视表生成三维数据统计报表。利用排序,找出销售额最大的饮料(1)为 “销售记录”表创建数据透视表要求:为表“销售记录”建立数据透视表,将数据透视表显示在新工作表中,并将数据透视表命名为“销售统计” 行显示“饮料名称”,列显示“所在区”,“销售额”显示在数据区。结果参见样例中的“销售统计”工作表。F温馨提示: 选中“销售记录”工作表的任意单元格,选择“数据”“数据透视表和数据透视图”; 单击“下一步”,选择销售数据所在区
11、域; 单击“下一步”,打开“数据透视表和数据透视表视图向导之3”; 单击“布局”按钮,打开“数据透视表和数据透视表视图向导布局”对话框,将“饮料名称”拖到左边图形的“行”上,“所在区”拖到“列”上,销售额拖到“数据”中,如下图所示; 单击“确定”,数据透视表显示位置:“新建工作表”。 单击“完成”, 将数据透视表命名为“销售统计”。(2)根据数据透视表找出销售额最大的饮料 对毛利润按降序排序,找出销售额最大的三种饮料。 结果参见样例中的“销售统计”工作表F 温馨提示: 选中“总计”列的数值部分(不包含最后一行数值) 然后单击工具栏中的排序按钮进行排序。(3)在“销售统计”工作表中,找出各个区“
12、销售额”最大的饮料 在“销售统计”工作表中,用MAX函数找出每个区“销售额”最大的饮料的“销售额”。 在“销售统计”工作表中,用VLOOKUP函数找出各区“最大销售额”所对应的“饮料名称”。STEP 4用“两轴线-柱图”比较“销售额”和“毛利润”设计目标现在,小李想用“两轴线-柱图”比较“南山区”、“福田区”和“罗湖区”的销售额和毛利润之间的关系。如下图所示,用两轴线柱图表来比较销售额和毛利润:(1)制作“两轴线图” 在表“所在区汇总”分别选择“所在区”、“销售额”和“毛利润”三列(如下图)所示; 单击“图表向导”按钮,打开“图表向导-4-图表类型”对话框,选择“自定义类型”选项卡,选择“两轴
13、线-柱图”。 单击“下一步”,打开“图表向导-4-图表源数据”对话框; 单击“下一步”,打开“图表向导-4-图表选项”对话框,按下图进行设置; 单击“下一步”,在“图表位置”对话框中,选择“作为其中的对象插入”,单击“完成”按钮。(2)美化图表 参照“饮料销售(样例).xls”,完成对图表格式的设置(格式可以自定义)。STEP 5利用数据有效性制作更方便、更实用的“新销售记录”工作表设计目标在“饮料销售.xls”中制作一张“新销售记录表”,并应用数据有效性设置,使得在填写了销售“数量”和选取了“饮料名称”后,可以自动计算出“销售额”、“毛利润”和“毛利率”。(1)制作“销售记录”表副本 在“饮
14、料销售.xls”中建立一个“销售记录”表的副本,并将其重命名为“新销售记录”,然后将其前五列的内容删除(只保留标题行),如下图所示。 并增加3个新列“实收”、“应收”和“找回”(2)对“饮料名称”应用数据有效性设置 选中“饮料价格”表中的“饮料名称”区域,并将其定义为“饮料名称”,如下图所示。 选中“新销售记录”的第4列(“饮料名称”列),然后再选择“数据”“有效性”打开数据有效性对话框。 在有效性条件中选择“序列”。 在“来源”中填写“=饮料名称”(注意:“饮料名称”是定义的“饮料名称”区域),如图下所示。 (3)数据有效性的使用制作完成,如下图所示,试试看,是不是在填写了销售“数量”和选取了“饮料名称”后,可以自动计算出“销售额”、“毛利润”和“毛利率”,很方便吧!(4)对 “饮料店”列进行数据有效性设置参照“饮料名称”的设置对“饮料店”进行设置(5)创建 “应收”和“找回”列的公式,算法如下:应收=销售额,找回=实收应收。STEP 6让查找公式更完美(1)隐藏列在“新销售记录”工作表中将不需要显示的列(“单位”、“进价”、“销售额”)隐藏起来。(2)前面的结果有缺陷在上面应用了查找函数VL