[饮料店数据分析.docVIP

饮料店数据的分析处理 问题的提出 小王开了一个饮料店，为了更好的管理每天的销售情况，他用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、对毛利润按照降序排列，找出毛利润最大的三种饮料。 提示：选中“总计”列的