excel销售数据分析实训练习.docx

excel销售数据分析实训练习.docx

《excel销售数据分析实训练习.docx》由会员分享，可在线阅读，更多相关《excel销售数据分析实训练习.docx（24页珍藏版）》请在冰点文库上搜索。

excel销售数据分析实训练习

EXCEL实训练习三

商场销售数据的阐发处置惩罚

1.问题的提出

小李在深圳市开了若干家饮料连锁店，为了提高治理水平，他计划用Excel事情表来治理销售数据。

下图是他制作的各饮料店的销售记载流水帐表。

为了统计“毛利润”，他必须去“饮料根本信息”表中查找每种饮料的“进价”和“售价”。

这个事情量实在太大，并且还容易堕落。

现在希望：

能否输入饮料名称后，让Excel凭据这个名称自动去查找该饮料的“单位”、“进价”、“售价”等信息，并存放到表“销售记载”的相应列中。

2．解决方案

通常情况下，如果不借助其它要领的资助，要想在Excel中解决这个问题，只能到“饮料根本信息”表中一条一条地查找种种饮料的“进价”和“售价”。

如果不想这么做，你有什么更好的措施吗？

这个实际需求，开发Excel的工程师，已经为我们想到了。

在Excel中有一个函数，就是专门为解决这类问题设计的，这个函数就是VLOOKUP。

小李这个问题，可利用Excel中的查找函数VLOOKUP来解决。

它的功效是，在数据区域的第一列中查找指定的数值，并返回数据区域当前行中指定列处的数值。

下面来看看应用VLOOKUP函数是如何解决上述问题的。

3.实现要领

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

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

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

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

4．另外，为了提高效率、制止堕落，小李还想制作一张可以从下拉列表中选择饮料名称，并能自动盘算出主顾应交款及应找回款的“新销售记载”事情表。

4．制作历程

★STEP1—VLOOKUP函数的使用

设计目标

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

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

★VLOOKUP函数的功效：

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

★VLOOKUP的语法：

注意：

要查找的东西（参数①）一定要界说在查找数据区域（参数②）的第一列。

下面来看看VLOOKUP函数是怎么用的。

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

★打开文件“饮料销售（素材）.xls”，将文件另存为“姓名_饮料销售.xls”。

★在“销售记载”事情表中，选中F3单位格→选择“插入→函数→VLOOKUP”→单击“确定”。

★由于要凭据饮料的名称查找“单位”，所以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）盘算销售额、毛利润

☞温馨提示：

★表格中数据之间的干系为：

销售额＝售价*数量

毛利润＝（售价－进价）×数量

★STEP2—分类汇总

首先，创建表“销售记载”的二个副本“销售记载

（2）”、“销售记载（3）”和“销售记载（4）”。

☞温馨提示：

★按住Ctrl键拖动事情表标签

（1）凭据“所在区”进行分类汇总

★

☞温馨提示：

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

★单击“升序”按钮

进行排序（排序的作用是什么？

）

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

在表“销售记载

（2）”中按“所在区”对销售额和毛利润进行分类汇总，汇总结果显示在数据下方（结果见“（样例）饮料销售.xls”）。

★将销售记载

（2）更名为“所在区汇总”

★结果拜见样例中的事情表——“所在区汇总”

（2）凭据“饮料名称”进行分类汇总

★在表“销售记载（3）”中按“饮料名称”对销售额和毛利润进行分类汇总（汇总方法为求和）

★对汇总结果中的毛利润列按降序排序，找出毛利润最大的饮料。

★将销售记载（3）更名为“饮料名称汇总”

★结果拜见样例中的事情表——“饮料名称汇总”

☞温馨提示：

★首先凭据“饮料名称”对“销售额”和“毛利润”进行分类汇总；

★然后，对“毛利润”进行降序排序。

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

★在“销售记载（4）”事情表中对“主要要害字”选择“所在区”，“次要要害字”选择“饮料店”排序。

★进行第一次“分类汇总”（分类字段为“所在区”）。

★进行第二次“分类汇总”（分类字段为“饮料店”）。

★将销售记载（4）更名为“嵌套汇总”

★

☞温馨提示：

★首先，选择“数据”→“排序”命令，在主要要害字中选择“所在区”，在次要要害字中选择“饮料店”；

★然后，先凭据“所在区”进行分类汇总；

★再凭据“饮料店”进行汇总，但是第二次汇总时，必须取消“替换当前分类汇总”。

结果拜见样例中的事情表——“嵌套汇总”

★STEP3—利用数据透视表实现统计阐发

设计目标

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

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

要求：

为表“销售记载”创建数据透视表，将数据透视表显示在新事情表中，并将数据透视表命名为“销售统计”

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

结果拜见样例中的“销售统计”事情表。

☞温馨提示：

★选中“销售记载”事情表的任意单位格，选择“数据”→“数据透视表和数据透视图”；

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

★单击“下一步”，打开“数据透视表和数据透视表视图向导之3”；

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

★单击“确定”，数据透视表显示位置：

“新建事情表”。

★单击“完成”，将数据透视表命名为“销售统计”。

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

★对毛利润按降序排序，找出销售额最大的三种饮料。

★结果拜见样例中的“销售统计”事情表

☞温馨提示：

★选中“总计”列的数值部门（不包罗最后一行数值）

★然后单击东西栏中的排序按钮进行排序。

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

★在“销售统计”事情表中，用MAX函数找出每个区“销售额”最大的饮料的“销售额”。

★在“销售统计”事情表中，用VLOOKUP函数找出各区“最大销售额”所对应的“饮料名称”。

★STEP4—用“两轴线-柱图”比力“销售额”和“毛利润”

设计目标

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

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

（1）制作“两轴线图”

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

★单击“图表向导”按钮，打开“图表向导-4-图表类型”对话框，选择“自界说类型”选项卡，选择“两轴线-柱图”。

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

★单击“下一步”，打开“图表向导-4-图表选项”对话框，按下图进行设置；

★单击“下一步”，在“图表位置”对话框中，选择“作为其中的东西插入”，单击“完成”按钮。

（2）美化图表

★参照“饮料销售（样例）.xls”，完成对图表格式的设置（格式可以自界说）。

★STEP5—利用数据有效性制作更方便、更实用的“新销售记载”事情表

设计目标

在“饮料销售.xls”中制作一张“新销售记载表”，并应用数据有效性设置，使得在填写了销售“数量”和选取了“饮料名称”后，可以自动盘算出“销售额”、“毛利润”和“毛利率”。

（1）制作“销售记载”表副本

★在“饮料销售.xls”中创建一个“销售记载”表的副本，并将其重命名为“新销售记载”，然后将其前五列的内容删除（只保存标题行），如下图所示。

★并增加3个新列“实收”、“应收”和“找回”

（2）对“饮料名称”应用数据有效性设置

★选中“饮料代价”表中的“饮料名称”区域，并将其界说为“饮料名称”，如下图所示。

★选中“新销售记载”的第4列（“饮料名称”列），然后再选择“数据”→“有效性”打开数据有效性对话框。

★在有效性条件中选择“序列”。

★在“来源”中填写“=饮料名称”（注意：

“饮料名称”是界说的“饮料名称”区域），如图下所示。

★

（3）数据有效性的使用

制作完成，如下图所示，试试看，是不是在填写了销售“数量”和选取了“饮料名称”后，可以自动盘算出“销售额”、“毛利润”和“毛利率”，很方便吧！

（4）对“饮料店”列进行数据有效性设置

参照“饮料名称”的设置对“饮料店”进行设置

（5）创建“应收”和“找回”列的公式，算法如下：

应收=销售额，找回=实收－应收。

★STEP6—让查找公式更完美

（1）隐藏列

在“新销售记载”事情表中将不需要显示的列（“单位”、“进价”、“销售额”）隐藏起来。

（2）前面的结果有缺陷

在上面应用了查找函数VLOOKUP的“销售记载”表中，如果把D3单位格中的饮料名称删去以后，可以看到F3、G3、H3单位格（即：

单位、售价和进价）中均返回错误值“#N/A”。

各人可以试一下，当VLOOKUP函数在“饮料代价”表中没有找到D3单位格中的饮料名称时都要返回错误值“#N/A”。

能不能让当VLOOKUP函数在“饮料信息”表中没有找到D3单位格中的饮料名称时不返回错误值“#N/A”，而只什么都不显示（即显示空格）呢？

问题：

利用IF函数和ISERROR函数，使“销售额”、“毛收入”和“毛利润”三列的值，在没有输入饮料名称时，不显示“#VALUE!

”错误值。

（2）解决措施

☞利用IF和ISERROR函数可以解决上面的问题。

试试看，你能解决这个问题吗？

上面的问题酿成两种情况：

①如果当“VLOOKUP（D3,ylmc,2,FALSE）”部门返回错误值的话，则在F3中显示空字符串。

②如果“VLOOKUP（D3,ylmc,2,FALSE）”部门事情正常，则在F3单位格中显示饮料对应的“售价”（即VLOOKUP（D3,ylmc,2,FALSE）的值）。

☞温馨提示：

ISERROR（value）函数：

当变量value是错误值“#N/A”时，返回逻辑真（TRUE），因此，当它与函数IF结合在一起使用时，可以用于在公式中查堕落误值。

IF函数与ISERROR函数联合使用的通用表现法:

IF（ISERROR（VALUE）,””,VALUE）

★STEP7—冻结窗口保存标题行

在“销售记载”事情表中，当销售记载增多后，为了保存标题行的内容，可单击A1单位格，执行“窗口”→“冻结窗口”命令，当转动屏幕时，可以把第一行（即表头行）的内容一直保存在窗口中。

4.重点难点

（1）重点

●VLOOKUP函数的使用。

●区域名称的界说

●分类汇总

●数据透视表

（2）难点

●VLOOKUP函数的参数的选择

●数据透视表

5.案例总结与常见问题

本案例通过对学生信息表数据的处置惩罚，介绍了查找与引用类函数VLOOKUP的用法及分类汇总和数据透视表的用法。

（一）你知道了吗？

通过本案例的学习，你能答复如下问题吗？

1．VLOOKUP函数是干什么的？

2．用VLOOKUP函数进行查找时，所要查找的元素为什么一定要在所界说的数据区域的第一列？

3．VLOOKUP函数中第二个参数的寄义是什么？

在界说第二个参数时要注意什么？

4．在对VLOOKPU函数沿列进行复制时，一般情况下，为什么列标要用绝对地点？

5．如何界说数据区域，如何删除数据区域？

6．用VLOOKPU函数时，可以不界说数据区域吗？

7．什么是分类汇总？

它有什么作用？

8．在分类汇总之前要注意什么？

（二）常见问题及处置惩罚要领

下面把各人在本案例的学习历程中容易遇到的一些问题及处置惩罚要领列于下表：

常见问题

可能原因

处置惩罚要领

“数据区域”名称无法重新界说。

该“数据区域”名称已被界说。

将其删除，重新界说，要领：

选择“插入”→“名称”→“界说”，打开“界说名称”对话框，选择已界说的名称，单击“删除”命令。

VLOOKUP函数返回错误值“#N/A”。

（1）没有把要查找的东西界说在“数据区域”的第一列。

重新界说“数据区域”，把要查找的内容界说在“数据区域”的第一列。

（2）查找的内容在界说的“数据区域”中不存在。

用ISERROR函数使错误值不显示。

使用“填充柄”沿列拖动复制公式时出现错误值“#N/A”。

要查找的东西（VLOOKUP函数的第一个参数）列标没有用绝对引用。

在使用“填充柄”沿列拖动前先将第一个参数的列标绝对引用。

分类汇总结果不正确。

（1）在“分类汇总”之前没先按要“分类”的字段排序。

先按要分类的字段排序，然后再进行“分类汇总”。

（2）虽然已先按要“分类”的字段排序，但在“分类汇总”时，分类字段选择不正确（没有选择已排序的字段）。

在“分类汇总”时，“分类”字段选择已排序的字段。

6.课后作业：

学生结果的查询和统计

请各人用查找函数VLOOKUP完成下面的案例。

（1）问题的提出：

★新学期刚开始，班主任王老师遇到了一个难题，学校凭据学生填报的志愿重分了班。

教务处给出了一个只包罗新班级学生的学号的Excel表格，见素材。

现在他要填写新班级学生的信息。

凭据以往的经验，他只能一个一个去查找然后粘贴了，但这既容易堕落，事情量又很大。

于是他向教Excel的老师请教，经过老师的指点，才知道用VLOOKUP可以很容易地解决这个问题。

★要求：

（A）在表“一班（新）”中，凭据学号，查找“姓名”、“大学英语”和总分。

（B）另外学校要对上学期“大学英语”考试结果是“优秀”和“良好”的学生进行嘉奖。

嘉奖措施见素材中表“奖品及加分”，其中“大学英语”考试结果是“优秀”的同学总分增加2%、“良好”的总分增加1%。

（2）解决措施

★请利用“学生信息（素材）.xls”，在表“一班（新）”中，凭据学号，应用VLOOKUP函数填写出一班学生的“姓名”、“大学英语”和“总分”，对没有结果的填写“缺考”。

★用IF函数填写一班学生的“英语品级”，对缺考的学生填写“英语缺考”。

英语结果>=90，“优秀”

80<=英语结果<90，“良好”

70<=英语结果<80，“一般”

60<=英语结果<70，“合格”

英语结果<60，“不合格”

★凭据表“奖品及加分”，用VLOOKUP函数盘算一班学生的“奖品”和“加分率”，其中“加分率”用百分比格式表现。

★用IF和ISERROR函数，使英语品级不是“优秀”和“良好”的学生的“奖品”为空、“加分率”是0，而不显示错误值“#N/A”。

★盘算出每个学生的“新总分”。

提示：

新总分=总分×（1+加分率）。