EXCEL实训练习三
商场销售数据的分析处理
1问题的提出
小李在深圳市开了若干家饮料连锁店为了提高管理水平他打算用Excel工作表来管理销售数据。下图是他制作的各饮料店的销售记录流水帐表。
为了统计“毛利润”他必须去“饮料基本信息”表中查找每种饮料的“进价”和“售价”。这个工作量实在太大而且还容易出错。
现在希望能否输入饮料名称后让Excel根据这个名称自动去查找该饮料的
f“单位”、“进价”、“售价”等信息并存放到表“销售记录”的相应列中。
f2解决方案
通常情况下如果不借助其它方法的帮助要想在Excel中解决这个问题只能到“饮料基本信息”表中一条一条地查找各种饮料的“进价”和“售价”。如果不想这么做你有什么更好的办法吗
这个实际需求开发Excel的工程师已经为我们想到了。在Excel中有一个函数就是专门为解决这类问题设计的这个函数就是VLOOKUP。
小李这个问题可利用Excel中的查找函数VLOOKUP来解决。它的功能是在数据区域的第一列中查找指定的数值并返回数据区域当前行中指定列处的数值。下面来看看应用VLOOKUP函数是如何解决上述问题的。
f3实现方法
本案例要解决如下几个问题
1在“销售记录”工作表中用VLOOKUP函数计算饮料的“单位”、“进价”和“售价”等信息并计算出工作表中的“销售额”和“毛利润”等信息。
2用“分类汇总”统计出各连锁店和各个区中各种饮料的“销售额”、“毛利润”。
3用“数据透视表”分析各个区中每种饮料的销售情况和各个区中销售情况最好的饮料。
4另外为了提高效率、避免出错小李还想制作一张可以从下拉列表中选择饮料名称并能自动计算出顾客应交款及应找回款的“新销售记录”工作表。
f4制作过程
STEP1VLOOKUP函数的使用
设计目标
参照下图根据“销售记录”表中的“饮料名称”列利用VLOOKUP函数
在“饮料基本信息”表中查找其他列单位、进价和售价的值。
根据以上两个表
中的数据得到下
图中的结果
f最后计算销售额、毛利润、毛利率
f1VLOOKUP函数是干什么用的
★VLOOKUP函数的功能
查找数据区域首列满足条件的元素并返回数据区域当前行中指定列处的值。
★VLOOKUP的语法
注意
要查找的对象参数①一定要定义在查找数据区域参数②的第一列。
下面来看看VLOOKUP函数是怎么用的。
f2如何查找“单位”
★打开文件“饮料销售素材xls”将文件另存为“姓名_饮料销售xls”。
★在“销售记录”工作表中选中F3单元格→选择“插入→函数→VLOOKUP”→单击“确定”。
★由于要根据饮料的名称查找“单位”所以VLOOKUP函数的第r