excel实训练习-3(销售数据分析)

恩

EXCEL实训练习三 商场销售数据的分析处理

1. 问题的提出

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

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

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

恩

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

恩

2．解决方案

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

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

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

恩

3. 实现方法

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

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

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

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

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

恩

4．制作过程

STEP 1—VLOOKUP函数的使用

设计目标

参照下图，根据“销售记录”表中的“饮料名称”列，利用VLOOKUP函数

在“饮料基本信息”表中查找其他列（单位、进价和售价）的值。

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

恩

最后计算销售额、

恩

（1）VLOOKUP函数是干什么用的

VLOOKUP函数的功能：

查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。

VLOOKUP的语法：

注意：

要查找的对象（参数①）一定要定义在查找数据区域（参数②）的第一列。 下面来看看VLOOKUP函数是怎么用的。

恩

（2）如何查找“单位”？

打开文件“饮料销售(素材).xls”，将文件另存为“姓名_饮料销售.xls”。 在“销售记录”工作表 中，选中

VLOOKUP” → 单击“确定”。

F3单元格 → 选择“插入→函数→

由于要根据饮料的名称查找“单位”，所以VLOOKUP函数的第一个参

数应该选择饮料名称“D3”。

在“Table_array”区域中选择“饮料价格”工作表中的B2:E44。 由于“单位”数据存放在“饮料信息”数据区域的第2列，所以输入数

字“2”。

由于要求饮料名称精确匹配，所以最后一个参数输入“FALSE”。

单击“确定”，可以看到函数准确地返回了“统一奶茶”的单位“瓶”。 复制公式,出现了什么问题？？？为什么？如何解决呢？

提示：注意在复制公式时，如果沿着列拖动时，列标要用绝对引用，想想看为什么？

修改公式后，重新复制公式。

恩

（3）创建区域名称

选择“饮料名称”工作表。

选中饮料名称、单位、进价、售价所在的区域，即单元格区域B2:E44。 在名称框中输入“自己的姓名＋A”，一定要按回车确认。

温馨提示：

也可以采用以下方法定义区域名称：

选择菜单“插入→名称→定义”命令，打开“定义名称”对话框， 在名称框中输入“姓名A”后，单击“确定”按钮，“姓名A”区域名

称创建完成

思考问题：

（1）区域的定义可以包含“序号”列吗？为什么？ （2）如果名称定义错误了，如何将其删除。

恩

（4）利用区域名查找“进价”和 “售价”

选择“销售记录”工作表 → 选中G3单元格 → 输入等号“=”→选择函数

“ VLOOKUP” → 单击“确定”。

由于要根据饮料的名称查找“售价”，所以VLOOKUP函数的第一个参数应

该选择饮料名称“D3”。

在“Table_array”区域中输入“姓名A”。

由于“进价”数据存放在第3列，所以输入数字“3”。 由于要求饮料名称精确匹配，所以最后一个参数输入“FALSE”

单击“确定”，可以看到函数准确地返回了“统一奶茶”的“进价”数据“1.9”。

复制公式，看一看有什么不同？？？ 同样道理在H3单元格中建立查找饮料名称“售价”的公式。

恩

（5）计算销售额、毛利润

温馨提示：

表格中数据之间的关系为：

销售额 ＝ 售价*数量

毛利润 ＝ (售价－进价)×数量

恩

STEP 2—分类汇总

首先，建立表“销售记录”的二个副本“销售记录(2)”、“销售记录(3)”和“销售记录(4)”。

温馨提示：

按住Ctrl键拖动工作表标签

恩

（1）按照“所在区”进行分类汇总

在表“销售记录(2)”中按“所在区”对销售额和毛利润进行分类汇总，

汇总结果显示在数据下方（结果见“(样例)饮料销售.xls”）。

温馨提示：

选择“所在区”列中的任意单元格

单击“升序”按钮

然后选择“数据”→“分类汇总”。

将销售记录(2)改名为“所在区汇总” 结果参见样例中的工作表——“所在区汇总”

恩

（2）按照“饮料名称”进行分类汇总

在表“销售记录(3)”中按“饮料名称”对销售额和毛利润进行分类汇总

（汇总方式为求和）

对汇总结果中的毛利润列按降序排序，找出毛利润最大的饮料。 将销售记录(3)改名为“饮料名称汇总” 结果参见样例中的工作表——“饮料名称汇总” 温馨提示：

首先按照“饮料名称”对“销售额”和“毛利润”进行分类汇总； 然后，对“毛利润”进行降序排序。

恩

（3）用“嵌套分类汇总”统计各个区和各饮料店的饮料“销售额”和“毛利润”

在“销售记录(4)”工作表中对“主要关键字”选择“所在区”，“次要关键字”

选择“饮料店”排序。

进行第一次“分类汇总”（分类字段为“所在区”）。 进行第二次“分类汇总”（分类字段为“饮料店”）。 将销售记录(4)改名为“嵌套汇总” 结果参见样例中的工作表——“嵌套汇总”

温馨提示：

首先，选择“数据”→“排序”命令，在主要关键字中选择“所

在区”，在次要关键字中选择“饮料店”； 然后，先按照“所在区”进行分类汇总；

再按照“饮料店”进行汇总，但是第二次汇总时，必须取消“替

换当前分类汇总”。

恩

STEP 3—利用数据透视表实现统计分析

设计目标

如下图所示，利用数据透视表生成三维数据统计报表。

利用排序，找出销售额最大的饮料

恩

（1）为 “销售记录”表创建数据透视表

要求：

为表“销售记录”建立数据透视表，将数据透视表显示在新工作表中，并将数据透视表命名为“销售统计”

行显示“饮料名称”，列显示“所在区”，“销售额”显示在数据区。

结果参见样例中的“销售统计”工作表。

温馨提示：

选中“销售记录”工作表的任意单元格，选择“数据”→“数据透视表和数据透视图”；

单击“下一步”，选择销售数据所在区域；

单击“下一步”，打开“数据透视表和数据透视表视图向导之3”； 单击“布局”按钮，打开“数据透视表和数据透视表视图向导—布局”对话框，将“饮料名称”拖到左边图形的“行”上，“所在区”拖到“列”上，销售额拖到“数据”中，如下图所示；

单击“确定”，数据透视表显示位置：“新建工作表”。 单击“完成”， 将数据透视表命名为“销售统计”。

恩

（2）根据数据透视表找出销售额最大的饮料

对毛利润按降序排序，找出销售额最大的三种饮料。 结果参见样例中的“销售统计”工作表 温馨提示：

选中“总计”列的数值部分（不包含最后一行数值） 然后单击工具栏中的排序按钮进行排序。

恩

（3）在“销售统计”工作表中，找出各个区“销售额”最大的饮料

在“销售统计”工作表中，用MAX函数找出每个区“销售额”最大的饮

料的“销售额”。

在“销售统计”工作表中，用VLOOKUP函数找出各区“最大销售额”

所对应的“饮料名称”。

恩

STEP 4—用“两轴线-柱图”比较“销售额”和“毛利润”

设计目标

现在，小李想用“两轴线-柱图”比较“南山区”、“福田区”和“罗湖区”的销售额和毛利润之间的关系。

如下图所示，用两轴线柱图表来比较销售额和毛利润：

恩

（1）制作“两轴线图”

在表“所在区汇总”分别选择“所在区”、“销售额”和“毛利润”三列（如

下图）所示；

单击“图表向导”按钮，打开“图表向导-4-图表类型”对话框，选择“自定

义类型”选项卡，选择“两轴线-柱图”。

单击“下一步”，打开“图表向导-4-图表源数据”对话框；

本文来源：https://www.bwwdw.com/article/8o11.html