饮料店数据分析.doc

饮料店数据的分析处理问题的提出小王开了一个饮料店，为了更好的管理每天的销售情况，他用Excel软件制作了饮料销售的流水帐。在“饮料销售.xls”文件中的“销售流水帐”工作表中记录了5月1日到5月3日的销售记录；工作表“饮料基本信息”中给出了每一种饮料的进价和售价。现在小王想要统计这三天的销售情况，计算一下毛利润有多少。看着长长的销售记录，小王发愁了，因为工作表“销售流水帐”中只记载了饮料名称和销售数量，为了统计毛利润，他必须去“饮料基本信息”工作表中查找每一种饮料的进价和售价。这个工作量实在太大，而且容易出错。现在小王希望，能不能在输入饮料名称后，让Excel根据这个名称去自动查找该饮料的“单位”，“进价”，“售价”等信息，并存放到“销售流水帐”工作表中。解决方案如果不借助其他方法的帮助，只能够到“饮料基本信息”工作表中一条一条地查找各种饮料的“进价”和“售价”，如果不这么做，有更好的办法吗？这个实际需求，在Excel软件中可以利用一个函数来解决，就是VLOOKUP函数。VLOOKUP函数功能VLOOKUP函数是干什么用的？1、函数的功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列的值。2、语法：VLOOKUP（lookup_value,table_array,col_index_num,range_lookup)制作过程一、如何查找“售价”？首先创建一个“饮料信息”的区域名称，后面就可以在VLOOKUP的table_array参数中使用这个区域名称，由于这个数据区域的第三列存放了“售价”数据，因此可以在这个数据区域中通过查找“饮料名称”返回其对应的“售价”数据。步骤：1、创建“饮料信息”区域名称（1）选择“饮料基本信息”工作表，选中饮料名称、单位、进价、售价所在的区域。（能不能选择“序号”这一列，为什么？）（2）在名称框中输入“饮料名称”后，点回车键确定。2、创建“售价”的查找公式（1）打开“销售流水帐”工作表，选中E2单元格，选择“插入”“函数”VLOOKUP，单击确定，弹出VLOOKUP函数的“函数参数”对话框。（2）由于要根据饮料名称查找“售价”，所以VLOOKUP函数的第一个参数应该选择饮料名称C2（3）在第二行参数中输入刚才定义的区域名称“饮料名称”（4）第三个参数是决定VLOOKUP函数找到匹配饮料名称所在行以后，该行的哪一列数据被返回，由于“售价”数据存放在第四列，所以在这里通过键盘输入数字“4”。（5）由于需要饮料名称精确匹配，所以最后一个参数输入“FALSE”，单击“确定”，可以看到函数返回了第一种饮料“百事可乐”的“售价”数据2.00（6）复制公式（注意在复制公式时，如果沿着列拖动时，列标要用绝对引用，想想为什么？）二、创建“单位”和“进价”的查找公式按照上面的方法，完成以下操作：（1）在D2单元格中建立查找饮料名称“单位”的公式。（2）在E2单元格中建立查找饮料名称“进价”的公式。三、用公式计算“销售额”、“毛利润”和“毛利率”三列的值。毛利润=销售额-进价成本毛利率=毛利润/销售额四、数据输出对“销售额”和“毛利润”进行分类汇总现在小王要统计出5月1日、5月2日、5月3日三天每天的销售额和毛利润，怎么来做呢？这个问题可以用“分类汇总”来解决。1、建立“销售流水帐”的二个副本“销售流水帐（2）”和“销售流水帐（3）”。提示：右键单击“销售流水帐”工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”并选中“建立副本”复选框。2、在表“销售流水帐（2）”中按照“日期”对销售额和毛利润进行分类汇总。汇总结果显示在数据下方。（结果见“饮料销售样例.xls”文件。）3、在表“销售流水帐（3）”中按照“商品名称”对销售额和毛利润进行分类汇总，并按照毛利润降序排列，找出毛利润最大的饮料。（结果见“饮料销售样例.xls”文件。）五、还有更方便的方法吗？为表“销售流水帐”创建数据透视表上面的问题，如果用“数据透视表”更加的方便。1、创建数据透视表（1）选中“销售流水帐”工作表的A1单元格，选择“数据”“数据透视表和数据透视图”。（2）单击“下一步”，选择销售数据所在区域。（3）单击下一步，打开“数据透视表和数据透视图向导步骤之3”（4）单击“布局”按钮，打开“布局”对话框。将“饮料名称”拖到“行”上，“日期”拖到“列”上，“毛利润”拖到“数据”中。（5）单击“确定”，数据透视表显示位置为“新建工作表”（6）单击“完成”。2、对毛利润按照降序排列，找出毛利润最大的三种饮料。提示：选中“总计”列的数值部分（不包含最后一行数值），然后进行排序。六、让查找公式更完美1、你发现前面查找函数VLOOKUP的缺陷了吗？在上面应用了查找函数VLOOKUP以后，如果把“销售流水帐”工作表中的C2单元格中的饮料名称删除以后，就可以看到E2、G2、H2、I2、J2单元格中均返回错误值“#N/A”能不能当VLOOKUP函数在“饮料基本信息”表中没有找到C2单元格中的饮料名称时不返回错误值“#N/A”，而只是什么都不显示（即显示空格）呢？2、解决方法（利用if和iserror函数可以解决上述问题）ISERROR（value）函数：当变量value是错误值“#N/A”时，返回逻辑值真（TRUE），因此，当它与if函数结合一起使用时，可以用于在公式中查出错误值。因此，上面的问题变成：如果当VLOOKUP（C2，饮料名称，4，FALSE）返回错误值的话，则在E2单元格显示空字符串，如果工作正常，则在E2单元格中显示饮料对应的“售价”。=