搜索
第4章办公中的数据分析与处理《办公自动化实用技术》电子教案水利水电出版社2【本章内容介绍】在办公实践中，经常需要对大量的数据进行分析和处理。如职工工资表中工资的计算、汇总、分析，公司销售表中销售额的统计、汇总、合并计算以及人事考勤表中职工请假、旷工、出勤等情况的分析统计等。本章采用实例形式，以Excel软件操作为例介绍办公实践中常见的数据分析与处理方法，包括数据的排序、筛选、分类汇总、公式与函数运用以及数据关联表格的操作等。《办公自动化实用技术》电子教案水利水电出版社3【本章主要知识点】Excel数据库表格的基本要求Excel表格中内容填充的常用技巧Excel数据库表格的特殊格式设置Excel表格中数据有效性的设置Excel数据库表格中的排序Excel数据库表格中的筛选Excel数据库表格的分类汇总Excel表格中公式和函数的使用Excel中的关联表格操作Excel公式使用中的常见错误信息《办公自动化实用技术》电子教案水利水电出版社44.1Excel数据分析与处理概述在办公业务中，除了文字和表格之外，还需要经常进行有关数据处理，有时候还需要将处理的数据结果用一种图表来进行表现。1．Excel中的数据库表格简介所谓数据库就是与特定主题和目标相联系的信息集合。在Excel中，可以通过首先创建数据库表格，然后再利用其提供的功能菜单进行数据的分析与处理。如图4-1示，Excel中数据库实际上就是工作表中的一个区域，是一个二维表。《办公自动化实用技术》电子教案水利水电出版社5图4-1Excel中的数据库表格样式说明：数据库表格最好单独占据一个工作表，并且不能跟其他内容（包括数据库标题）直接相连，至少需要一个空行或空列隔开，如图4-1中，第2行作为一个空行，将数据库表格与其标题进行了隔离。同时，也不要使用空白行将列标志和第一条记录分开。《办公自动化实用技术》电子教案水利水电出版社62．Excel中的数据分析与处理Excel中提供了很多种数据分析和数据处理功能，包括数据排序、数据筛选、分类汇总、数据透视、合并计算、关联表格处理等。本章将对上一章的实例进一步进行介绍，包括对报名汇总表进行数据分析，例如：按照某个主题排序、按照指定条件筛选、按照指定字段进行分类汇总、对整个表格进行数据透视分析等；同时，本章还将利用Excel中的公式和函数进行决赛阶段成绩的计算，并将几项不同的比赛成绩进行关联表格的综合处理等。3．Excel中的数据图表制作利用Excel，可以轻松、快捷地制作各种数据图表。这些数据图表可将枯燥的数字形象化，并且通过数据图表可以了解到数据之间的相互关系和变化趋势。数据图表的类型很多，在办公实践中，使用较多的主要有柱形图、条形图、折线图、饼图、散点图等。（1）柱形图用于显示某一段时间内数据的变化，或比较各数据项之间的差异。分类在水平方向组织，而数值在垂直方向组织，以强调相对于时间的变化。其中堆积柱形图显示了单个数据项与整体的关系，三维透视的柱形图可比较两个坐标轴上的数据点。《办公自动化实用技术》电子教案水利水电出版社7（2）条形图用于显示各数据之间的比较。分类在垂直方向，而数值在水平方向，以使观察者的注意力集中在数值的比较上，而不在时间上。堆积条形图显示单个数据与整体的关系。（3）折线图用于显示各数据之间的变化趋势。分类在水平方向组织，而数值在垂直方向组织，以强调相对于时间的变化。（4）饼图用于显示组成数据系列的各数据项与数据项总和的比例。当只有一个数据系列，并且用于强调整体中的某一重要元素时，饼状图十分有效。如果要使小扇区更容易查看，可将这些小扇区组织为饼状图中的一个数据项，然后将该数据项在主图表旁边的小饼状图或小条形图中拆分显示。（5）XY散点图既可用于显示若干数据系列中的数字值的关系，也可将两组数字分别当作单个系列的x坐标和y坐标进行绘制，常用于科技数据处理。制作数据图表时，图表类型的选取最好与源数据表内容以及制作目的相结合，对于不同的数据表，一定要选择最适合的图表类型，这样才能使表现的数据更生动、形象。《办公自动化实用技术》电子教案水利水电出版社84.2“报名信息汇总表”数据库表格的建立上一章我们介绍了Excel中空白报名汇总表的制作。本节主要介绍在Excel中如何制作该表格，效果如图4-2样式所示（版面所限，只是部分效果）。图4-2“报名信息汇总表”数据库表格样式《办公自动化实用技术》电子教案水利水电出版社94.2.1建立“报名汇总表”数据库框架将上一章制作好的汇总表框架打开，按照数据库表格的要求进行必要的内容编辑和格式设置，并调整好显示方式，如图4-3所示，等待进行各列数据的输入。图4-3空白的“报名信息汇总表”表格《办公自动化实用技术》电子教案水利水电出版社104.2.2“编号”列的自动序列填充对于“编号”列，因为输入的数据是有一定规律的，输入时可以考虑使用自动序列的填充方法。操作时，只要在图4-3所示表格的A3、A4分别输入1和2，然后选取这两个单元格，再拖动右下角的拖动柄到需要的位置即可。4.2.3定义单元格数据的有效性在创建Excel数据库的过程中，有些单元格中输入的数据没有限制，而有些单元格中输入的数据具有有效范围。例如：图4-3的报名汇总表中，“性别”列只能输入“男”、“女”之一；“年龄”列按照报名规定只能输入16~35之间的数字；而“唱法类型”列只能输入“通俗”、“民族”、“美声”中的一种。为了保证数据库中输入的数据都在其有效范围内，并且设置用户选择对应单元格时能够提供提示信息，错误时可以给出错误提示，可以考虑使用Excel提供的“有效性”来为单元格设置条件。下面仅以设置“唱法类型”一列的数据有效性为例，说明设置数据有效性的操作方法。操作步骤如下：《办公自动化实用技术》电子教案水利水电出版社11（1）选定需要设置数据有效性的单元格区域E3：E62（根据实际报名人数，后者中行号可以适当扩大，作为教学用例，此处假设报名人数只有60人，下面一样处理）。（2）选择“数据”|“有效性”命令，打开图4-4所示的“数据有效性”对话框。图4-4“数据有效性”对话框《办公自动化实用技术》电子教案水利水电出版社12（3）选择“设置”选项卡，在“允许”下拉列表中，选择“序列”项。（4）在随之出现“来源”文本框中输入“通俗，民族，美声”自定义序列。注意：序列中各项中间的逗号必须为英文状态符号。（5）根据需要，可以对其余选项卡进行适当设置。说明：数据有效性设置之前，必须首先选取所需单元格或区域；另外，数据有效性应该在输入之前设置，否则不会自动起作用。数据有效性设置之后，单击“唱法类型”列中单元格，则会出现如图4-5所示的列表框，从而可以实现快速的选取录入。图4-5“唱法类型”字段数据有效性的设置效果《办公自动化实用技术》电子教案水利水电出版社13办公实际使用的数据库中字段数往往很多，如果用滚屏的方法来处理数据很不方便。Excel为此专门提供了“记录单”功能，选择“数据”|“记录单”命令，系统即可弹出如图4-6所示对话框，它显示数据记录的所有字段，提供了增加、修改、删除等功能。当数据库很大时，记录单将会显示出很大的优势，利用它可以简捷、精确地输入记录。图4-6Excel中“记录单”样式《办公自动化实用技术》电子教案水利水电出版社144.3对“报名信息汇总表”进行数据分析本节介绍利用Excel中的有关功能，对“报名信息汇总表”进行数据分析的操作方法，主要包括数据的排序、筛选、分类汇总与数据透视等功能。4.3.1对“报名信息汇总表”内容进行排序在报名汇总表中，各个参赛选手的资料一般是按照其报名的先后顺序输入的。为了提高查找效率，有时需要对输入的数据重新进行整理，对此有效的方法就是排序。Excel可以根据数据库中的内容对数据记录进行排序。排序时，Excel将利用指定的顺序重新排列行、列或各单元格。可以根据一列或多列的内容按升序或降序对数据库排序。1．排序的依据在按升序排序时，Excel使用如下顺序：（1）数字从最小的负数到最大的正数排序；（2）文本以及包含数字的文本，按下列顺序排序：《办公自动化实用技术》电子教案水利水电出版社150123456789'-(空格)!#$%&()*,./:;?@[\]^_`{|}~+=ABCDEFGHIJKLMNOPQRSTUVWXYZ.（3）在逻辑值中，FALSE排在TRUE之前；（4）所有错误值的优先级等效；（5）空格总是排在最后；（6）汉字的排序可以按笔画，也可按字典顺序排序，这可以通过有关操作设置。2．按单个字段排序按单个字段排序就是只根据某一个字段的内容进行排序。操作方法为：将光标置于待排序列中；单击工具栏上“升序”或“降序”按钮即可。说明：按数据库某一列数据排序时，只需单击列中任一单元格而不用全选该列。《办公自动化实用技术》电子教案水利水电出版社163．按多级字段排序按多级字段排序就是根据两列或三列的内容对数据库排序，它主要适用于第一或第二排序字段出现相同内容时，为了排序合理再加上第二或第三排序字段。操作步骤如下：（1）将光标定位在待排序数据库的任一单元格中，之后选择“数据”|“排序”命令，打开如图4-7所示对话框。（2）在“主要关键字”、“次要关键字”和“第三关键字”下拉列表框中，分别选择需要排序的字段，并且设置“递增”或者“递减”即可，本例不太需要该设置。（3）选定所需的其他排序选项，然后单击“确定”按钮。说明：按数据库多列数据排序时，只需单击数据库中任一单元格而不用全选整个数据库表格，否则会引起数据的混乱。4．特殊排序方式除上述基本排序功能外，Excel还提供了一些特殊的排序功能。它们的操作方法为：（1）将光标放在所要排序的数据区域中的任一位置；（2）选择“数据”菜单中的“排序”项；在打开的对话框中选择“选项”按钮；《办公自动化实用技术》电子教案水利水电出版社17（3）在打开如图4-8所示的“选项”对话框中，分别设置：自定义排序次序、排序方向（按行或按列排序）、排序方法（按字母或按笔画排序）（4）按“确定”按钮完成操作。图4-7数据库按照多字段排序图4-8其他排序选项说明：在“排序选项”对话框中，若不选择“方向”和“方法”，系统默认的排序方向为“按列排序”，默认的排序方式为“字母排序”。如选择“笔画排序”方法，就可实现在开会代表名单、教材编写人员名单中经常看到的按姓氏笔画排序效果。《办公自动化实用技术》电子教案水利水电出版社184.3.2对“报名信息汇总表”内容进行筛选报名信息汇总表制作好之后，有时还需要根据指定条件从众多数据中筛选特定的记录，比如：筛选职业是“教师”的报名人员，筛选年龄在30以上的人员等等。Excel中提供了两种筛选方法：“自动筛选”和“高级筛选”，它们可以将那些符合条件的记录显示在工作表中，而将其他不满足条件的记录从视图中隐藏起来；或者将筛选出来的记录送到指定位置存放，而原数据不动。1．自动筛选利用自动筛选，可以很方便地从报名汇总表中筛选出职业是“教师”的报名人员信息。下面以此为例，说明自动筛选的操作。操作步骤如下：（1）将鼠标定位到需要筛选的数据库中任一单元格。（2）选择“数据”|“筛选”|“自动筛选”，使“自动筛选”项为选中状态（打上对号），这时在每个字段名旁出现筛选器箭头，如图4-9所示。（3）本例要筛选出来职业是“教师”的报名人员的情况，需要单击“职业”字段名旁的筛选器箭头，从弹出的菜单中选择“教师”，然后单击鼠标左键即可。《办公自动化实用技术》电子教案水利水电出版社19图4-9使用自动筛选器筛选记录说明：在该菜单中，选择“升序排列”和“降序排列”，则筛选结果将按照指定顺序排列；选择具体学历名称，将只显示对应学历的记录；单击“全部”显示所有记录；单击“前10个”可以显示最高或最低的一些（默认10个，可以自行设置）记录。（4）在图4-9中，如果下拉列表太长，不容易找到“教师”项时，还可以选择“自定义…”项，从出现图4-10所示的对话框中，按照图中样式设置本例的筛选条件。《办公自动化实用技术》电子教案水利水电出版社20图4-10