ecel实训练习3销售数据分析.docxVIP

EXCEL实训练习三

商场销售数据的分析处理

问题的提出

小李在深圳市开了若干家饮料连锁店，为了提高管理水平，他打算用Excel工作表来管理销售数据。下图是他制作的各饮料店的销售记录流水帐表。

为了统计“毛利润”，他必须去“饮料基本信息”表中查找每种饮料的“进价”和“售价”。这个工作量实在太大，而且还容易出错。

现在希望：能否输入饮料名称后，让Excel根据这个名称自动去查找该饮料的

1

PAGE

PAGE3

“单位”、“进价”、“售价”等信息，并存放到表“销售记录”的相应列中。

解决方案

通常情况下，如果不借助其它方法的帮助，要想在Excel中解决这个问题，只能到“饮料基本信息”表中一条一条地查找各种饮料的“进价”和“售价。”如果不想这么做，你有什么更好的办法吗？

这个实际需求，开发Excel的工程师，已经为我们想到了。在Excel中有一个函数，就是专门为解决这类问题设计的，这个函数就是VLOOKUP。

小李这个问题，可利用Excel中的查找函数VLOOKUP来解决。它的功能是，在数据区域的第一列中查找指定的数值，并返回数据区域当前行中指定列处的数值。下面来看看应用VLOOKUP函数是如何解决上述问题的。

实现方法

本案例要解决如下几个问题：

在“销售记录”工作表中用VLOOKUP函数计算饮料的“单位”、“进价”和“售价”等信息，并计算出工作表中的“销售额”和“毛利润”等信息。

用“分类汇总”统计出各连锁店和各个区中各种饮料的“销售额”、“毛利润”。

用“数据透视表”分析各个区中每种饮料的销售情况和各个区中销售情况最好的饮料。

另外，为了提高效率、避免出错，小李还想制作一张可以从下拉列表中选择饮料名称，并能自动计算出顾客应交款及应找回款的“新销售记录”工作表。

制作过程

STEP1—VLOOKUP函数的使用

设计目标

参照下图，根据“销售记录”表中的“饮料名称”列，利用VLOOKUP函数在“饮料基本信息”表中查找其他列（单位、进价和售价）的值。

根据以上两个表中的数据得到下图中的结果

5

PAGE

PAGE7

言日期 所在区饮料店 饮料名称 数盘单位进价 告价 销告酘 毛利涓

言

酉

西丽店

归

西丽后

归

西而店

西丽后

三言三

三言三

西而后

.．．一·可一

统一奶茶

红牛

菹萝氓非常可乐

百再可乐{lL)娃吩咕果奶语萄汁

雪若

可口可乐(lL

70瓶

1.9.J.

1.9

.J.2

1.2

1.6

2

2.4

4.2

1.6

2

2.5

16呏

8呏

59瓶

23瓶 1.4.I 1.B

86台 q.51 6

48呏 1.71 2.2

17今 1.71 2.2

沉瓶 0.91 1.2

27FHli. I 21 2.6

18瓶 2.41 3

27l:lf.l. I 2.1

最后计算销售额、毛利润、毛利率

西丽后 统一绿茶西丽店＿— 姓呤 咭扽净苏

西丽店 乐百氏扽净水

西丽店 七菩

西丽店 七吾(lL)

西丽店 可口可乐

酉醒目

西丽店 旭目升冰红夺西丽店 乐百氏果奶西丽店 芬达

94瓶 1.8 ;::

56llm I O.91 1.2

83瓶 11 1.3

76口 I 1.71 2.2

23瓶 21 2.6

14l□fr I 1.7I 2.1

丙□fr I 21 -6

15瓶 1.71 2.2

郊瓶 1.31 1.7

221Pfr I 1.6I 2

l lI lI l

吕；！＇ Jfir.iallill.i:o:l 了 七:: ，:L :-

I I-＇.

、ii

--?- --西刊店西商店西

--

?

- --

西刊店

西商店西罚店西商店

西商店

-- -

纥一画

红牛

夜萝啤

--

__

,

I2am-1-1I南山

2007-1-1南山

2007-1一1南山

2007-1-1南山

2007一1一1南山

非尝可乐

百宰可乐(lL)

70扰

78[)斤

16听

8IJJj-

59栽

1,9|

3,2

1,2

1.6

2

,

2.4

4..2

I.6

2

2.5

2007-1-1 山

2007-1-1南山

2007-1-1南山

2aor-1-1南山2aor-1一1南山200?－l-1南山

2007-1-1南山

2007-1-1南山

2aor-1-1黜律讥」

2007-1-