做中学 学中做-办公自动化案例教程-模块07 Excel 2010的数据分析功能—制作销售业绩统计表电子课件高教版 .ppt

做中学 学中做-办公自动化案例教程-模块07 Excel 2010 的数据分析功能制作销售业绩统计表电子课件 高教版 模块07 Excel 2010 的数据分析功能制作销售业绩统计表你知道吗？Excel 2010 提供了极强的公式、函数、数据排序、筛选、分类汇总以及图表等功能。使用这些功能，用户可以方便的管理、分析数据。应用场景 人们平常会见到分期付款计算、记账表等电子表格，这些都可以利用Excel 2010的数据分析功能来制作。如图所示就是利用Excel 2010的数据分析功能制作的销售业绩统计表，请读者根据本模块所介绍的知识和技能，完成这一工作任务。背景知识 通过对销售数据的分析，可以及时反应销售计划完成的情况，有助于一线人员分析销售过程中存在的问题，为提高销售业绩及服务等技能提供依据和参考。通过对销售数据的分析，可以及时掌握销售波动，掌握客户需求情况的变化，为管理者对企业的管理提供科学的依据。设计思路 在制作销售业绩统计表的过程中，主要应用到使用公式和函数来计算数据、利用排序功能排序数据、对数据进行筛选、创建分类汇总、最后利用图表分析，制作销售业绩统计表的基本步骤可分解为：（1）使用公式（2）使用函数（3）排序数据（4）筛选数据（5）汇总数据（6）应用图表项目任务7-1 使用公式 公式是在工作表中对数据进行分析和运算的等式，或者是一组连续的数据和运算符组成的序列。公式要以等号（=）开始，用于表明其后的字符为公式。紧随等号之后的是需要进行计算的元素，各元素之间用运算符隔开。动手做1 创建公式 在创建公式时可以直接在单元格中输入，也可以在编辑栏中输入，在编辑栏中输入和在单元格中输入计算结果是相同的。下面我们为销售业绩统计表运用公式，具体步骤如下：（1）打开存放在“案例与素材 模块07 素材”文件夹中名称为“销售业绩统计表（初始）”文件，切换到“Sheet1”工作表。（2）选定单元格“J3”，直接在单元格或编辑栏中输入公式“=D3+E3+F3+G3+H3+I3”。（3）按回车键，或单击编辑栏中的输入按钮 即可在单元格中计算出结果而此时在编辑栏中依然显示输入的公式，如图所示。项目任务7-1 使用公式 动手做2 单元格的引用 引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。在Excel 2010 中，系统提供了三种不同的引用类型：相对引用、绝对引用和混合引用。它们之间既有区别又有联系，在引用单元格数据时，用户一定要弄清楚这三种引用类型之间的区别和联系。相对引用，指的是引用单元格的行号和列标。所谓相对就是可以变化，它的最大特点就是在单元格中使用公式时如果公式的位置发生变化，那么所引用的单元格也会发生变化。绝对引用，顾名思义就是当公式的位置发生变化时，所引用的单元格不会发生变化，无论移到任何位置，引用都是绝对的。绝对引用使用在单元格名前加一符号“$”，如$A$3 表示单元格“A3”是绝对引用。混合引用，就是指只绝对引用行号或者列标，如$B6 表示绝对引用列标，B$6 则表示绝对引用行号。当相对引用的公式发生位置变化时，绝对引用的行号或列标不变，但相对引用的行号或列标则发生变化。如果多行多列地复制公式，则相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用“=A$1”从A2 复制到B2，它将从“=A$1”调整到“=B$1”。项目任务7-2 应用函数 函数是一些预定义的公式，通过使用一些称为参数的特定数值来按特定的顺序或结构执行计算。函数可用于执行简单或复杂的计算。在公式中合理地使用函数，可以大大节省用户的输入时间，简化公式的输入。应用函数有两种方法：直接输入法就是直接在工作表的单元格中输入函数的名称及语法结构。插入函数法就是当用户在不能确定函数的拼写时，则可使用另一种插入函数的方法来应用函数。直接输入法的操作非常简单，只需先选择要输入函数公式的单元格，输入“”号，然后按照函数的语法直接输入函数名称及各参数即可。但其要求用户必须对所使用的函数较为熟悉，并且十分了解此函数包括多少个参数及参数的类型。然后就可以像输入公式一样来输入函数，使用起来也较为方便。但由于利用直接输入法来输入函数时，要求用户必须了解函数的语法、参数及使用方法，而且Excel 2010 提供了200 多种函数，因此用户不可能全部记住。这时就可以使用插入函数法，这种方法简单、快速，它不需要用户的输入，而直接插入即可使用。项目任务7-3 排序数据 在实际应用中，建立数据清单输入数据时，人们一般是按照数据到来的选后顺序输入的。但是，当用户要直接从数据清单中查找所需的信息时，很不直观。为了提高查找效率，需要重新整理数据，对此最有效的方法就是对数据进行排序。对数据清单中的数据进行排序是Excel最常见的应用之一。对数据记录进行排序时，主要利用“排序”工具按钮和“排序”对话框来进行排序。如果用户想快速地根据某一列的数据进行排序，则可使用“数据”选项卡下的“排序和筛选”组中的排序按钮：“升序”按钮：单击此按钮后，系统将按字母表顺序、数据由小到大、日期由前到后等默认的排列顺序进行排序。“降序”按钮：单击此按钮后，系统将反字母表顺序、数据由大到小、日期由后到前等顺序进行排序。在按升序排序时，Excel 2010 将使用如下顺序（在按降序排序时，除了空格总是在最后外，其他的排序顺序反之）：数字从最小的负数到最大的正数排序。文本以及包含数字的文本，按下列顺序排序：先是数字0 到9，然后是字符“-(空格)!#$%&()*,./:;?“”_|+”，最后是字母A 到Z。在逻辑值中，FALSE 排在 TRUE 之前。所有错误值的优先级等效。空格排在最后。项目任务7-4 数据筛选 筛选是查找和处理数据清单中数据子集的快捷方法，筛选清单仅显示满足条件的行，该条件由用户针对某列指定。筛选与排序不同，它并不重排数据清单，而只是将不必显示的行暂时隐藏。用户可以使用“自动筛选”或“自定义筛选”功能将那些符合条件的数据显示在工作表中。Excel 2010 在筛选行时，可以对清单子集进行编辑、设置格式、制作图表和打印，而不必重新排列或移动。动手做1 自动筛选 自动筛选是一种快速的筛选方法，用户可以通过它快速地访问大量数据，从中选出满足条件的记录并将其显示出来，隐藏那些不满足条件的数据，此种方法只适用于条件较简单的筛选。例如，利用“自动筛选”功能将销售业绩统计表中“部门”中“销售（3）部”的记录显示出来，具体步骤如下：（1）在销售业绩统计表工作簿中切换到“Sheet2”工作表。（2）选中单元格“A3：J46”区域，或将鼠标定位在数据区域的任意单元格中。（3）单击“数据”选项卡下“排序和筛选”组中的“筛选”按钮，则在选中区域的标题行中文本的右侧出现一个下三角箭头。（4）单击部门的下三角箭头打开一个列表，在列表的“数字筛选”下面的列表中取消“全选”的选中状态，然后选择“销售（3）部”。（5）单击“确定”按钮，项目任务7-4 数据筛选动手做2 自定义筛选 在使用“自动筛选”命令筛选数据时，还可以利用“自定义”的功能来限定一个或两个筛选条件，以便于将更接近条件的数据显示出来。例如，在销售业绩统计表中筛选出“一月”小于“60000”或大于“90000”的数据，具体步骤如下：（1）单击“数据”选项卡下“排序和筛选”组中的“清除”按钮，清除原来的筛选结果。（2）单击“一月”右侧的下三角箭头打开一个列表，然后指向“数字筛选”出现一个子菜单。（3）在列表中选择“自定义筛选”选项，打开“自定义自动筛选方式”对话框。（4）在左上部的“比较操作符”下拉列表中选择“大于”，在其右边的文本框中输入“90000”，选中“或”单选按钮，在左下部的“比较操作符”下列表中选择“小于”，在其右边的文本框中输入“60000”。（5）单击“确定”按钮。项目任务7-4 数据筛选动手做3 筛选前10 个 如果用户要筛选出最大或最小的几项，用户可以在筛选列表中使用“前10 个”命令来完成。例如，将销售业绩统计表中一月最大的前5 名显示出来，具体步骤如下：（1）单击“数据”选项卡下“排序和筛选”组中的“清除”按钮，清除原来的筛选结果。（2）单击“一月”右侧的下三角箭头打开一个列表，然后指向“数字筛选”出现一个子菜单，选择“10 个最大的项”选项，打开“自动筛选前10 个”对话框。（3）在对话框中的最左边的下拉列表中选择“最大”项，在中间的文本框中选择或输入“5”，在最后边的下拉列表中选择“项”。（4）单击“确定”按钮，按一月字段自动筛选出排在前5 名的效果如图所示。项目任务7-5 数据的分类汇总 分类汇总是对数据清单上的数据进行分析的一种常用方法，Excel 2010 可以使用函数实现分类和汇总值计算，汇总函数有求和、计算、求平均值等多种。使用汇总命令，可以按照用户选择的方式对数据进行汇总，自动建立分级显示，并在数据清单中插入汇总行和分类汇总行。动手做1 创建分类汇总 分类汇总是将数据清单中的某个关键字段进行分类，相同值的分为一类，然后对各类进行汇总。在进行自动分类汇总之前，应对数据清单进行排序将要分类字段相同的记录集中在一起，并且数据清单的第一行里必须有列标记。利用自动分类汇总功能可以对一项或多项指标进行汇总。例如，对销售业绩统计表中按“销售部门”对工作表中的各各月销售额进行求和汇总，具体步骤如下：（1）在销售业绩统计表工作簿中切换到“Sheet3”工作表。（2）首先将“部门”字段按升序进行排列使相同班级的记录集中在一起。（3）选中单元格“A3：J46”区域，或将鼠标定位在数据区域的任意单元格中。（4）单击“数据”选项卡下“分级显示”组中的“分类汇总”按钮，打开“分类汇总”对话框。（5）在“分类字段”下拉列表中选择“部门”；在“汇总方式”下拉列表中选择“求和”；在“选定汇总项”列表中选中一月份、二月份、三月份、四月份、五月份、六月份和总销售额。（6）选中“汇总结果显示在数据下方”复选框，则将分类汇总的结果放在本类数据的最后一行。（7）单击“确定”按钮。项目任务7-5 数据的分类汇总动手做2 分级显示数据 工作表中的数据进行分类汇总后，将会使原来的工作表显得有些庞大，如果用户要想单独查看汇总数据或查看数据清单中的明细数据，最简单的方法就是利用Excel 2010 提供的分级显示功能。在对工作表数据进行分类汇总后，汇总后的工作表在窗口处将出现“1”、“2”、“3”的数字，还有“-”、大括号等，这些符号在Excel 2010 中称为分级显示符号。符号减号 是“隐藏明细数据”按钮，加号 是“显示明细数据”按钮。单击减号可以隐藏该级及以下各级的明细数据。单击加号则可以展开该级明细数据。动手做3 消除分级显示数据 如果要取消部分分级显示，可先选定有关的行或列，然后单击“数据”选项卡下的“分级显示”组中的“取消组合”按钮打开一个下拉列表，在下拉列表中选择“清除分级显示”按钮即可。当创建了分类汇总后，如果不再需要了，用户还可以将其删除掉。首先在分类汇总数据清单区域单击任一单元格，单击“数据”选项卡下“分级显示”组中的“分类汇总”按钮，打开“分类汇总”对话框。在“分类汇总”对话框中单击“全部删除”按钮，最后单击“确定”按钮，关闭对话框。项目任务7-6 应用图表 Excel 2010 提供的图表功能，可以将系列数据以图表的方式表达出来，使数据更加清晰易懂，使数据表示的含义更形象更直观，并且用户可以通过图表直接了解到数据之间的关系和变化的趋势。动手做1 创建图表 对于一些结构复杂的表格，用户往往要花费相当长的时间才能对表格中要说明的问题理出个头绪来，既费时又费力。而如果使用Excel 2010 的“图表”功能，则可以将枯燥乏味的数字转化为图表，从而使数据之间的关系更一目了然。无论是以何种方式建立的图表，都与生成它们的工作表上的源数据建立了链接，这就意味着当更新工作表数据时，同时也会更新图表。例如，在销售统计表中插入销售（1）部各个销售人员16 月份总销售额图表，具体步骤如下：（1）在销售业绩统计表工作簿中切换到“Sheet4”工作表。（2）在工作表中选择要绘制图表的数据区域，这里选择“销售（1）部”的“姓名”字段的单元格与“总销售额”字段单元格。（3）单击“插入”选项卡下“图表”组中的“柱形图”按钮，弹出一个下拉列表。（4）在下拉列表中选择“二维柱形图”的“簇状柱形图”按钮即可插入图表。项目任务7-6 应用图表动手做2 调整图表的大小和位置 通过对图表的大小进行调整，可以使图表中的数据更清晰、图表更美观，在销售统计表中调整上面创建的图表大小的具体操作步骤如下：（1）将鼠标指向创建的图表，单击鼠标，选中图表。（2）将鼠标移至图表各边中间的控制手柄上，当鼠标变成双向箭头状时，拖动鼠标可以改变图表的宽度和高度，虚线框表示图表的大小，调整到合适位置后松开鼠标。（3）将鼠标移至四角的控制手柄上，当鼠标变成双向箭头状时拖动鼠标可以将图表等比放缩，虚线框表示图表的大小，调整到合适大小后松开鼠标。动手做3 图表对象的选取 在对图表及图表中的各个对象进行操作时，用户首先应将其选中，然后才能对其进行编辑操作。在选定整个图表时，只需将鼠标指向图表中的空白区域，当出现“图表区”的屏幕提示时单击鼠标即可将其选定。选定后整个图表四周出现八个句柄，此时就表示图表被选定。被选定之后用户就可以对整个图表进行移动、缩放等编辑操作了。在选定图表中的对象时，用户也可以利用鼠标来进行选定，用户只需用鼠标直接单击要选定的图表对象即可。例如，要选定图表的标题对象，用户可以将鼠标指向图表标题文本，当出现“图表标题”的屏幕提示时单击鼠标即可选定图表标题。项目任务7-6 应用图表动手做4 设置图表区的格式 可以通过为图表区添加边框、设置图表中的字体、填充图案等来修饰图表。例如，设置“总销售额”图表区的格式，具体操作步骤如下：（1）将鼠标指向图表的绘图区，当出现图表区的屏幕提示时单击鼠标左键即可选定图表选中图表。（2）切换到“格式”选项卡，在“形状样式”组中单击“形状填充”按钮，在“形状填充”列表的“纹理”列表中选择“水滴”。（3）在“形状样式”组中单击“形状轮廓”按钮，在“形状轮廓”列表的“主题颜色”区域选择“水绿色，强调颜色5，淡色40%”，在“粗细”列表中选择“3 磅”。（4）在“形状样式”组中单击“形状效果”按钮，在“形状效果”列表的“发光”列表中选择“蓝色，11pt 发光，强调文字颜色1”。项目任务7-6 应用图表动手做5 设置绘图区格式 在绘图区中，底纹在默认情况下为白色，可以根据需要对其进行更改。例如，在总销售额中设置“总销售额”图表中绘图区设置填充效果，具体操作步骤如下：（1）将鼠标指向图表的绘图区，当出现“绘图区”的屏幕提示时单击鼠标左键即可选定图表选中图表绘图区。（2）在“布局”选项卡下“当前所选内容”组中单击“设置所选内容格式”或在绘图区上单击鼠标右键，在快捷菜单中选择“设置绘图区格式”命令，均可打开“设置绘图区格式”对话框。（3）在对话框框左侧列表中选择“填充”，在右侧的“填充”区域选择“渐变填充”单选按钮，显示出渐变填充的一些设置按钮。（4）单击“预设颜色”按钮，弹出一个下拉列表，这里选择“熊熊火焰”。（5）在“类型”下拉列表中选择“线性”；在“方向”列表中选择“线性对角”；在“角度”列表中设置角度为“135”。（6）在“渐变光圈”的颜色列表中选择“橙色”，设置光圈1 的结束为止为“100%”。（7）单击“关闭”按钮，关闭“设置绘图区格式”对话框。项目任务7-6 应用图表动手做6 设置图表标题格式 在图表区中的字体默认为“宋体、10、黑色”，标题字体默认为“宋体、18、黑色”，用户可以根据需要对其字体格式以及标题文本进行更改。例如，这里为销售统计表中的“销售总额”图表标题设置字体格式，具体操作步骤如下：（1）将鼠标指向图表标题，当出现“图表标题”的屏幕提示时单击鼠标左键即可选中“图表标题”对象。（2）将鼠标定位在标题中，删除原来的标题“总销售额”，然后输入新的标题“雷烁光电科技公司销售（1）部2015 年上半年销售业绩统计表”。（3）按住鼠标左键拖动选中标题文本。（4）单击开始选项卡下的“字体”组中的“颜色”下拉列表中选择“深蓝”。动手做7 设置图例格式 如果设置图表对象的格式相对简单，用户可以在“布局”选项卡下快速进行设置。如要设置图例的格式用户可以在“布局”选项卡的“标签”组中单击“图例”按钮打开一个下拉列表。在列表中用户可以对图例的格式进行简单的设置，如这里选择“在底部显示图例”。选中图例，在“开始”选项卡下“字体”组中设置图例的字体为黑体，字号为14。知识拓展动手做1 保护单元格中的公式 如果单元格中的数据是公式计算出来的，那么当选定该单元格后，在编辑栏上将会显示出该数据的公式。如果用户工作表中的数据比较重要，可以将工作表中单元格中的公式隐藏，这样可以防止其他用户看出该数据是如何计算出的。对工作表中的公式进行保护，具体步骤如下：（1）选中要保护的单元格或单元格区域。（2）单击“开始”选项卡下“单元格”组中的“格式”按钮，在打开的下拉列表中选择“设置单元格格式”选项，打开“单元格格式”对话框，单击“保护”选项卡。（3）在对话框中如果选中了“锁定”复选框，则工作表受保护后，单元格中的数据不能被修改；如果选中了“隐藏”复选框，则工作表受保护后，单元格中的公式被隐藏。（4）单击“确定”按钮。（5）单击“审阅”选项卡下的“更改”组中的“保护工作表”按钮，打开“保护工作表”对话框。选中“保护工作表及锁定的单元格内”容复选框，单击“确定”按钮，对工作表设置保护。设置了隐藏功能后，在选中含有公式的单元格，则不显示公式。动手做2 移动图表的位置 在创建图表后用户还可以移动图表的位置，首先选中图表，然后在“设计”选项卡的“位置”组中单击“移动图表”按钮，则打开“移动图表”对话框。在对话框中用户可以选择是将图表移动到的位置，选择“新工作表”则创建一个图表工作表；选择“对象位于”则可以移动到工作簿的现有工作表中。知识拓展动手做3 设置图表数据系列格式 在图表数据系列上单击鼠标右键，在快捷菜单中选择“数据系列格式”命令，打开“数据系列格式”对话框。在对话框中用户可以对数据系列的格式进行设置。动手做4 设置图表坐标轴格式 在图表坐标轴上单击鼠标右键，在快捷菜单中选择“坐标轴格式”命令，打开“坐标轴格式”对话框。在对话框中用户可以对坐标轴的格式进行设置。