EXCEL实训课件2-饮料店销售数据分析

1EXCEL实训课件二饮料店销售数据分析课前准备1．在D盘新建个人文件夹学号最后2位+姓名，如“10张三”;2．打开IE，进入计算机基础教学网作业提交、资料下载【登录】；将操作素材“EXCEL实训课件2”下载到D盘的个人文件夹中，并解压缩；实训目标利用“饮料销售(素材).xls”学会用图表、分类汇总、数据透视表对饮料销售数据进行分析处理，结果参见“饮料销售(样例).xls”。任务一：在《销售记录》工作表里添加饮料的相关数据（如图1所示）实现目标：在任务一中，我们主要完成以下任务：1．查找函数VLOOKUP的使用。2．名称的定义。3．公式的使用。请采用“文档结构图”的方式浏览课件（【视图】→【文档结构图】）通过函数计算得到通过公式计算得到图12制作过程一、VLOOKUP函数1．VLOOKUP函数的功能：查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。2．VLOOKUP的语法：注意：要查找的对象（参数①）一定要定义在查找数据区域（参数②）的第一列。下面来看看VLOOKUP函数是怎么用的。二、如何查找“单位”先在“饮料价格”工作表中创建一个“价格”区域名称，后面就可以在VLOOKUP的table_array参数中使用这个区域名称，由于这个数据区域的第2列存放了“单位”数据，因此可以在这个数据区域中通过查找“饮料名称”得到对应的“单位”数据。实现步骤如下：（参考教材P185～P187）1．创建“价格”区域名称（1）打开“饮料销售(素材).xls”，选择“饮料价格”工作表，选中饮料名称、单位、进价、售价所在的区域（参见图2）。提示：与Shift键配合，可实现对多个不间断单元格的选择问题：区域的定义可以包含“序号”列吗？为什么？答案：不行。原因：被查找的关键数据（饮料名称）必须排列在被定义区域的首列。否则，将无法正确实现查找。（2）选择菜单“插入→名称→定义”命令（见图3），打开“定义名称”对话框，在名称框中输入“价格”后（见图4），单击“确定”按钮，“价格”区域名称创建完成（试试看，也可以在名称框中直接输入定义的名称）。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)①要查找的内容么？②到哪个区域的第一列查找？③返回区域第几列的值？④精确查找还是模糊查找？图23图3图4问题：如果名称定义错误了，如何将其删除。2．创建“单位”的查找公式（1）打开“销售记录”工作表，选中目标单元格F3，选择“插入→函数→VLOOKUP”，单击“确定”，弹出VLOOKUP函数的“函数参数”对话框。（2）由于要根据饮料的名称查找“单位”，所以VLOOKUP函数的第一个参数应该选择饮料名称“D3”（见图5）。图5（3）单击“函数参数”对话框的第二个文本框右边的折叠按钮，接着选择菜单“插入→名称→粘贴”命令（见图6），打开“粘贴名称”对话框，选中名称“价格”（见图7），单击“确4定”，区域名称“价格”被插入到公式中，VLOOKUP函数的第二个参数的位置。图6图7用这种方法在公式中输入区域名称，即快捷又不容易出错。（4）“函数参数”对话框中的第三个参数是决定VLOOKUP函数找到匹配饮料名称所在行以后，该行的哪列数据被返回，由于“单位”数据存放在第二列，所以在这里通过键盘输入数字“2”。（5）由于要求饮料名称精确匹配，所以最后一个参数输入“FALSE”（见图8），单击“确定”，可以看到函数准确地返回了“统一奶茶”的“单位”是“瓶”。图85（6）复制公式（注意在复制公式时，如果沿着列拖动时，列标要用绝对引用，想想看为什么？）复制公式时：可以通过使用填充柄（填充柄：位于选定区域右下角的小黑方块。将用鼠标指向填充柄时，鼠标的指针更改为黑十字。）将公式复制到相邻的单元格中。选择包含公式的单元格，再拖动填充柄，使之覆盖要填充的区域。按照与上面查找“单位”相同的方法，自己完成下面的内容：三、创建“进价”和“售价”的查找公式1．在G3单元格中建立查找饮料名称“进价”的公式。2．在H3单元格中建立查找饮料名称“售价”的公式。四、用公式计算“销售额”和“毛利润”2列的值其中：销售额=售价×数量毛利润=(售价-进价)×数量设置结果：将工作表中的“进价”、“售价”、“销售额”和“毛利润”4列的数据加“￥”符号，小数位数设置为“2”。各列设置为最适合的列宽。五、数据输出──对“销售额”和“毛利润”进行分类汇总任务二：利用分类汇总统计“销售额”和“毛利润”图9实现目标：在任务二中，我们主要完成以下任务：1．用“分类汇总”计算各个区的饮料“销售额”和“毛利润”（图9）。2．用“分类汇总”统计每种饮料的“销售额”和“毛利润”（图10）。6图10请参见P188～P192，完成对“销售额”和“毛利润”进行分类汇总的操作。什么是分类汇总？现在要统计出各个区饮料的销售额和毛利润，如何来做呢？这个问题可以用“分类汇总”来解决。“分类汇总”含有两个意思，既按什么分类（各个区）和对什么汇总（销售额和毛利润）。注意：进行“分类汇总”之前，要先对分类的列（所在区）进行排序（想想为什么？），然后再对要汇总的列（销售额和毛利润）进行总和。问题：汇总时，不想计算总和，能不能计算平均值？图12按照与上面介绍的“分类汇总”的方法，自己完成下面的内容：1．建立表“销售记录”的三个副本“销售记录(2)”、“销售记录(3)”和“销售记录(4)”。提示：右单击“销售记录”工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”并选中“建立副本”复选框（见图13）。提示：更简单的操作方法是：按住Ctrl键，再用鼠标拖动要复制的工作表。7图132．在表“销售记录(2)”中用分类汇总统计出各个区的“销售额”和“毛利润”，汇总结果显示在数据下方（结果见图14）。提示：先对“所在区”进行排序（排序的作用是什么？），然后选择“数据”→“分类汇总”。汇总后2级汇总结果如下所示。图14方法：1）打开“销售记录(2)”工作表2）对“所在区”进行排序（单击数据清单“所在区”列中任一单元格，在工具栏中点击“升序排序”按钮）3）在菜单栏中选择“数据”→“分类汇总”命令，打开“分类汇总”对话框，（见图12）4）单击“确定”按钮5）点击数据清单左上角的显示符号“2”，可看到分级的效果（见图14）3．在表“销售记录(3)”中用分类汇总统计各种饮料的“销售额”和“毛利润”，并对毛利润按降序排序，找出毛利润最大的饮料。（注意：汇总前是否也要先排序？按什么排序？）隐藏明细数据行8六、还有方便的办法吗？──为表“销售记录”创建数据透视表任务三：用“数据透视表”分析各区每种饮料销售情况进行“分类汇总”（如图16所示）图16请参见“饮料销售（样例）.xls”中的“销售统计”工作表，并参阅P192～P196，完成以下操作：1．用“数据透视表”进行“分类汇总”上面的问题，如果用“数据透视表”则更方便。“数据透视表”可以很方便地对数据进行汇总和比较。为表“销售记录”建立数据透视表，并将数据透视表显示在新工作表中，并将数据透视表命名为“销售统计”。数据透视表的布局要求：1）．行显示“饮料名称”，列显示“所在区”，“销售额”显示在数据区；操作方法：（1）选中“销售记录”工作表的任一单元格，选择“数据”→“数据透视表和数据透视图”；实现目标：在任务三中，我们主要完成以下任务：1．用“数据透视表”进行“分类汇总”。2．工作表的重命名。3．在“数据透视表”工作表中创建一个如右图16所示的表格。4．单元格的引用。5．MAX函数的使用9（2）单击“下一步”，选择销售数据所在区域；（3）单击“下一步”，打开“数据透视表和数据透视表视图向导之3”；（4）单击“布局”按钮，打开“数据透视表和数据透视表视图向导—布局”对话框，将“饮料名称”拖到左边图形的“行”上，所在区拖到“列”上，销售额拖到“数据”中；10（5）单击“确定”，数据透视表显示位置：“新建工作表”。（6）单击“完成”，将数据透视表命名为“销售统计”。2）．对销售额按降序排序，找出销售额最大的饮料。提示：选中“总计”列的数值部分（不包含最后一行数值），然后进行排序，如图17所示。图17问题：怎样修改数据透视表的字段？如果不想求和，如何改变汇总方式？112．在“销售统计”工作表中创建一个如图18所示的表格，然后将“所在区”行引用到J4～N4。图18提示：同一工作表的单元格引用3．用MAX函数找出各区“销售额”最大的饮料“销售额”。七、用两轴线柱图表来比较“销售额”和“毛利润”任务四：利用图表向导创建“南山区”、“福田区”和“罗湖区”的“销售额”和“毛利润”的图表（如图20所示）图20请完成以下操作：1．在表“销售记录(2)”分别选择所需的“所在区”、“销售额”和“毛利润”（如图21所示）。实现目标：在任务四中，我们主要完成以下任务：1．用两轴线柱图表来比较“销售额”和“毛利润”2．格式化图表。12图21提示：选择单元格的同时按住Ctrl键，可对多个间断单元格进行选择2．单击“图表向导”按钮，打开“图表向导-4-图表类型”对话框，选择“自定义类型”选项卡，如下图所示。选择“两轴线-柱图”。3．单击“下一步”，打开“图表向导-4-图表源数据”对话框；134．单击“下一步”，打开“图表向导-4-图表选项”对话框，按下图进行设置；5．单击“下一步”，在“图表位置”对话框中，选择“作为其中的对象插入”，单击“完成”按钮。14按照“饮料销售(样例).xls”，完成对图表区域的设置。图表区域的填充效果用“麦浪滚滚”；绘图区格式的填充效果用“茵茵绿原”图例格式的填充效果用纹理“纸莎草纸”自我测试通过本案例的学习，你能回答如下问题吗？1．VLOOKUP函数是干什么的？2．如何定义数据区域，如何删除数据区域？3．VLOOKUP函数中第二个参数的含义是什么？在定义第二个参数时要注意什么？4．当沿着列复制VLOOKUP函数时应注意什么？5．如何让VLOOKUP函数在没有查找到所查找的内容时不返回错误值“#N/A”，而显示“没找到”呢？6．什么是分类汇总？它有什么作用？7．在分类汇总之前要注意什么？8．利用“图表向导”创建图表共有几步？如何修改数据源？如何美化图表？9．在选择数据区域时，要选择不连续的数据区域，应该如何操作？（答：按住Ctrl键，然后用鼠标拖动来选择。）常见问题及处理方法下面把大家在本案例的学习过程中容易遇到的一些问题及处理方法列于下表：常见问题可能原因处理方法“数据区域”名称无法重新定义。该“数据区域”名称已被定义。将其删除，重新定义，方法：选择“插入”→“名称”→“定义”，打开“定义名称”对话框，选择已定义的名称，单击“删除”命令。VLOOKUP函数返回错误值“#N/A”。（1）没有把要查找的对象定义在“数据区域”的第一列。重新定义“数据区域”，把要查找的内容定义在“数据区域”的第一列。（2）查找的内容在定义的“数据区域”中不存在。用ISERROR函数使错误值不显示。使用“填充柄”沿列拖动复制公式时出现错误值“#N/A”。要查找的对象（VLOOKUP函数的第一个参数）列标没有用绝对引用。在使用“填充柄”沿列拖动前先将第一个参数的列标绝对引用。分类汇总结果不正确。（1）在“分类汇总”之前没先按要“分类”的字段排序。先按要分类的字段排序，然后再进行“分类汇总”。（2）虽然已先按要“分类”的字段排序，但在“分类汇总”在“分类汇总”时，“分类”字段选择已排序的字段。15时，分类字段选择不正确（没有选择已排序的字段）。提交课堂实训结果1、打开IE，进入、.登录，上传本次课的Excel文档