办公中的数据分析与处理-完整版PPT

1、办公中的数据分析与处理【本章内容介绍】在办公实践中，经常需要对大量的数据进行分析和处理。如职工工资表中工资的计算、汇总、分析，公司销售表中销售额的统计、汇总、合并计算以及人事考勤表中职工请假、旷工、出勤等情况的分析统计等。本章采用实例形式，以Excel软件操作为例介绍办公实践中常见的数据分析与处理方法，包括数据的排序、筛选、分类汇总、公式与函数运用以及数据关联表格的操作等。现代教育技术中心 岳经伟 TEL：89714158 E-mail：【本章主要知识点】Excel数据库表格的基本要求Excel表格中内容填充的常用技巧Excel数据库表格的特殊格式设置Excel表格中数据有效性的设置Excel

2、数据库表格中的排序Excel数据库表格中的筛选Excel数据库表格的分类汇总Excel表格中公式和函数的使用Excel中的关联表格操作Excel公式使用中的常见错误信息现代教育技术中心 岳经伟 TEL：89714158 E-mail：Excel数据分析与处理概述1Excel中的数据库表格简介所谓数据库就是与特定主题和目标相联系的信息集合。如图4-1示，Excel中数据库实际上就是工作表中的一个区域，是一个二维表。 现代教育技术中心 岳经伟 TEL：89714158 E-mail：Excel 2003 的窗口组成 Excel 的启动、退出、新建文件、保存文件、打开文件等基本操作与Word相似。启

3、动Excel 2003 后，系统自动打开一个空工作簿（book1.xls）现代教育技术中心 岳经伟 TEL：89714158 E-mail：几个重要的术语 1.工作簿（Book）：每个Excel文档都是一个工作簿，每个工作簿由若干张工作表（Sheet）所构成，最多可包含255个表。默认情况下，新工作簿名称为Book1.xls。 2.工作表（sheet） ：是工作簿里的一页，由单元格组成。每个工作表的网格由65536行（165536） 、 256列（AIV）构成。 3.单元格：工作表中行列交叉形成的格，是Excel中操作的基本单位。 4.单元格地址：单元格地址由列坐标行坐标（例：A3）构成。 5

4、.活动单元格：正在使用的单元格（黑色粗边框）称为活动单元格。要输入、处理数据的单元格必须是活动单元格。现代教育技术中心 岳经伟 TEL：89714158 E-mail：几个重要的术语文本：中文、西文的单词、句子和符号。数值：数字、小数点、数学符号和货币符号等。日期的时间：表示日期和时间的数字、符号和分隔符等。公式：以等号（=）开头由单元格名、运算符和数字组成的字符串。如果单元格中的数据是公式时，则选中该单元格时，其公式在编辑栏中出现，而在单元格中仍是数值，是公式的计算结果鼠标指针：在Excel工作表中，指针通常是“ 空心大十字 ”；双击某单元格而进入编辑状态时，指针呈“ I ”形；当指针移到选

5、中单元格的右下角时，变为“ 小十字 ”形。 现代教育技术中心 岳经伟 TEL：89714158 E-mail：工作表的操作工作表的含义插入、删除工作表右键单击某一工作表标签，选定该工作表，并弹出下面菜单；点“插入”|“工作表”|“确定”，则在该工作表前插下一新的工作表，名为Sheet4.若删除工作表，则点击 菜单中的“删除”现代教育技术中心 岳经伟 TEL：89714158 E-mail：工作表的操作重命名工作表右键单击某一工作表标签，选定该工作表并弹出一菜单；点“重命名”，则该工作表名称处于选择状态，改名即可.标签颜色选中工作表、右击、选“工作表标签颜色”、选择“颜色”点击“确定”隐藏工作表

6、选中工作表“格式”|“工作表”|“隐藏”取消隐藏：“格式”|“工作表”|“取消隐藏”，选择一工作表，点击“确定”现代教育技术中心 岳经伟 TEL：89714158 E-mail：工作表的操作复制和移动工作表打开接收工作表的工作簿选择要复制或移动的工作表，右击、选择“移动或复制工作表”，出现右图；在“工作簿”列表框中选择目标工作簿，在“下列选定工作表之前确定位置如果是移动而非复制，选中“建立副本”单击“确定”按钮，完成。现代教育技术中心 岳经伟 TEL：89714158 E-mail：单元格的操作单元格及单元格区域的表示方法单元格地址：列号行号； B2如右图 单元格区域：“左上角单元格：右下角单

7、元格” B2：D3如右图现代教育技术中心 岳经伟 TEL：89714158 E-mail：选定操作 选定连续单元格区域。首先将鼠标箭头指向要选定区域的第一个单元格，按住鼠标左键，拖动至要选定区域的最后一个单元格，选定的区域会变成蓝色，表明这一块区域被选中的状态。释放鼠标左键，一个连续的区域就选定了。 选定不连续单元格区域。按住【Ctrl】键的同时，逐个单击所要选取的单元格。 现代教育技术中心 岳经伟 TEL：89714158 E-mail：选定操作 选定整行（列）选定单行（列）：在工作表上单击该行（列）的行（列）号。 选定连续行（列）区域：在工作表上单击该区域的第一行（列）行号（列号），然后按

8、住【Shift】键，再单击最末一行（列）的行号（列号）；或者拖动鼠标。 选定整个工作表。单击工作表左上角行号与列号相交处的全选按钮，即可选定整个工作表。现代教育技术中心 岳经伟 TEL：89714158 E-mail：复制或移动单元格复制和移动操作与Word类似，可以使用菜单命令、工具栏按钮、快捷键、快捷菜单中的命令、鼠标拖动。选定要移动或复制的单元格将鼠标指向选定区域边框，鼠标指针成“十字箭头”移动则拖动鼠标到目的区域复制则按Ctrl同时拖动鼠标到目的区域相邻单元格的复制，可以直接拖动“填充柄”现代教育技术中心 岳经伟 TEL：89714158 E-mail：插入或删除单元格 清除单元格内容

9、选择单元格、行、列，按Delete键 清除单元格格式选择点击菜单“编辑”|“清除”|“全部”、“格式”、“内容” 删除单元格选择点击菜单“编辑”|“删除”确定删除选项，点“确定”现代教育技术中心 岳经伟 TEL：89714158 E-mail：设置单元格格式“格式”|“单元格”现代教育技术中心 岳经伟 TEL：89714158 E-mail：文本数据的输入除了数值和日期型数据外，Excel将其余输入的数据均作为文本数据，文本指当作字符串处理的数据，由字母、数字或其他字符组成。如果要输入的文本数据全部由数字组成（如序号、邮政编码、身份证号码、电话号码等），输入时应在数字前加一个西文单引号，如序号

10、“00123”输入时键入“00123”，以区别数值格式的数据。如果希望在输入这类数据时不键入，又不会将该数据作为数值格式的数据对待，可以在输入数据之前，定义单元格数据的格式为文本格式。现代教育技术中心 岳经伟 TEL：89714158 E-mail：数字、日期、时间的输入 负数-10：-10或（10）分数3/5：0 空格 3/5 日期2006年11月12日：2006-11-12，2006/11/12插入当前日期：Ctrl+ ;时间5时32分：5：32插入当前日期：Ctrl+ ：现代教育技术中心 岳经伟 TEL：89714158 E-mail：“报名信息汇总表”建立 现代教育技术中心 岳经伟 T

11、EL：89714158 E-mail：“报名汇总表”数据库框架现代教育技术中心 岳经伟 TEL：89714158 E-mail：“编号”列的自动序列填充对于“编号”列，因为输入的数据是有一定规律的，输入时可以考虑使用自动序列的填充方法。操作时，只要在图4-3所示表格的A3、A4分别输入1和2，然后选取这两个单元格，再拖动右下角的拖动柄到需要的位置即可。现代教育技术中心 岳经伟 TEL：89714158 E-mail：定义单元格数据的有效性在Excel中有些单元格中输入的数据具有有效范围。例如：图4-3的报名汇总表中，“性别”列只能输入“男”、“女”之一；“年龄”列按照报名规定只能输入1635之

12、间的数字；而“唱法类型”列只能输入“通俗”、“民族”、“美声”中的一种。可以使用Excel提供的“有效性”来为单元格设置条件。 下面仅以设置“唱法类型”一列的数据有效性为例，说明设置数据有效性的操作方法。操作步骤如下：现代教育技术中心 岳经伟 TEL：89714158 E-mail：定义单元格数据的有效性现代教育技术中心 岳经伟 TEL：89714158 E-mail：（1）选定需要设置数据有效性的单元格区域E3：E62（假设报名人数只有60人）。（2）选择“数据”|“有效性”命令，打开图4-4所示的“数据有效性”对话框。定义单元格数据的有效性现代教育技术中心 岳经伟 TEL：89714158

13、 E-mail：（3）选择“设置”选项卡，在“允许”下拉列表中，选择“序列”项。（4）在随之出现“来源”文本框中输入“通俗，民族，美声”自定义序列。注意：序列中各项中间的逗号必须为英文状态符号。（5）数据有效性设置之后，单击“唱法类型”列中单元格，则会出现如图4-5所示的列表框，从而可以实现快速的选取录入。对“报名信息汇总表”进行数据分析0 1 2 3 4 5 6 7 8 9 - (空格) ! # $ % & ( ) * , . / : ; ? _ | + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z .现代教育技术中心 岳经伟 TEL

14、：89714158 E-mail： 在报名汇总表中，各个参赛选手的资料一般是按照其报名的先后顺序输入的。为了提高查找效率，有时需要对输入的数据进行排序。 1排序的依据在按升序排序时， Excel 使用如下顺序：（1）数字从最小的负数到最大的正数排序；（2）文本以及包含数字的文本，按下列顺序排序：对“汇总表”进行数据排序现代教育技术中心 岳经伟 TEL：89714158 E-mail：（3）空格总是排在最后；（4）汉字的排序可以按笔画，也可按字典顺序排序，这可以通过有关操作设置。对“汇总表”进行数据排序现代教育技术中心 岳经伟 TEL：89714158 E-mail： 2按单个字段排序 按单个字

15、段排序就是只根据某一个字段的内容进行排序。 操作方法为：将光标置于待排序列中；单击工具栏上“升序”或“降序”按钮即可。 说明：按数据库某一列数据排序时，只需单击列中任一单元格而不用全选该列。3按多级字段排序按多级字段排序就是根据两列或三列的内容对数据库排序，它主要适用于第一或第二排序字段出现相同内容时，为了排序合理再加上第二或第三排序字段。（1）将光标定位在待排序数据库的任一单元格中，之后选择“数据”|“排序”命令，打开如图4-7所示对话框。现代教育技术中心 岳经伟 TEL：89714158 E-mail：3按多级字段排序（2）在“主要关键字”、“次要关键字”和“第三关键字”下拉列表框中，分别

16、选择需要排序的字段，并且设置“递增”或者“递减”即可。（3）选定所需的其他排序选项，然后单击“确定”按钮。说明：按数据库多列数据排序时，只需单击数据库中任一单元格而不用全选整个数据库表格，否则会引起数据的混乱。现代教育技术中心 岳经伟 TEL：89714158 E-mail：4特殊排序方式现代教育技术中心 岳经伟 TEL：89714158 E-mail： 除上述基本排序功能外，Excel还提供了一些特殊的排序功能。它们的操作方法为： （1）将光标放在所要排序的数据区域中的任一位置； （2）选择“数据”菜单中的“排序”项；在打开的对话框中选择“选项”按钮；4特殊排序方式（3）在打开如图4-8所示

17、的“选项”对话框中，分别设置：自定义排序次序、排序方向（按行或按列排序）、排序方法（按字母或按笔画排序）（4）按“确定”按钮完成操作。说明：在“排序选项”对话框中，若不选择“方向”和“方法”，系统默认的排序方向为“按列排序”，默认的排序方式为“字母排序”。如选择“笔画排序”方法，就可实现在开会代表名单、教材编写人员名单中经常看到的按姓氏笔画排序效果。现代教育技术中心 岳经伟 TEL：89714158 E-mail：对“报名信息汇总表”内容进行筛选现代教育技术中心 岳经伟 TEL：89714158 E-mail：报名信息汇总表制作好之后，有时还需要根据指定条件从众多数据中筛选特定的记录，比如：筛

18、选职业是“教师”的报名人员，筛选年龄在30以上的人员等等。Excel中提供了两种筛选方法：“自动筛选”和“高级筛选”，它们可以将那些符合条件的记录显示在工作表中，而将其他不满足条件的记录从视图中隐藏起来；或者将筛选出来的记录送到指定位置存放，而原数据不动。1自动筛选利用自动筛选，可以很方便地从报名汇总表中筛选出职业是“教师”的报名人员信息。下面以此为例，说明自动筛选的操作。操作步骤如下：（1）将鼠标定位到需要筛选的数据库中任一单元格。（2）选择“数据”|“筛选”|“自动筛选”，使“自动筛选”项为选中状态（打上对号），这时在每个字段名旁出现筛选器箭头，如图4-9所示。（3）本例要筛选出来职业是“

19、教师”的报名人员的情况，需要单击“职业”字段名旁的筛选器箭头，从弹出的菜单中选择“教师”，然后单击鼠标左键即可。现代教育技术中心 岳经伟 TEL：89714158 E-mail：自动筛选现代教育技术中心 岳经伟 TEL：89714158 E-mail：说明：在该菜单中，选择“升序排列”和“降序排列”，则筛选结果将按照指定顺序排列；选择具体学历名称，将只显示对应学历的记录；单击“全部”显示所有记录；单击“前10个”可以显示最高或最低的一些（默认10个，可以自行设置）记录。自动筛选现代教育技术中心 岳经伟 TEL：89714158 E-mail：（4）在图4-9中，如果下拉列表太长，不容易找到“教

20、师”项时，还可以选择“自定义”项，从出现图4-10所示的对话框中，按照图中样式设置本例的筛选条件。自动筛选现代教育技术中心 岳经伟 TEL：89714158 E-mail：（5）按“确定”按钮，完成操作，筛选结果如图4-11所示。自动筛选说明：取消数据筛选的操作方法：如要取消对某一列的筛选，单击该列筛选器箭头，然后再单击“全部”。如要取消对所有列的筛选，可选择“数据”|“筛选”|“全部显示”命令。如要撤消数据清单中的筛选箭头，可选择“数据”|“筛选”|“自动筛选”命令。现代教育技术中心 岳经伟 TEL：89714158 E-mail：高级筛选现代教育技术中心 岳经伟 TEL：89714158

21、E-mail：从上面的讲解可以看到，自动筛选可以实现同一字段之间的“与”运算和“或”运算，通过多次进行自动筛选也可以进行不同字段之间的“与”运算，但是它无法实现多个字段之间的“或”运算。这时就需要使用高级筛选。需要说明的是：对于数据清单，如果要进行高级筛选，必须首先设置筛选条件区域。为此，必须在该数据清单上方留出若干空行，以便作为条件区域。另外，为了使读者可以更好地理解高级筛选，首先列出一个表格，如表4-1所示，该表用来对高级筛选条件区域的逻辑关系进行定义。现代教育技术中心 岳经伟 TEL：89714158 E-mail：高级筛选下面以筛选出来职业为“教师”、唱法类型为“美声”或者“民族”的报

22、名选手情况操作为例，说明高级筛选的使用方法。操作步骤如下：现代教育技术中心 岳经伟 TEL：89714158 E-mail：（1）在“报名信息汇总表”表格的上方插入几个空行，建立高级筛选的条件区域，如图4-12所示A1:B3区域。 （2）单击“报名信息汇总表”数据库表格中的任一单元格。高级筛选高级筛选（3）选择“数据”|“筛选”|“高级筛选”命令，出现“高级筛选”对话框。现代教育技术中心 岳经伟 TEL：89714158 E-mail：现代教育技术中心 岳经伟 TEL：89714158 E-mail：（4）如上图所示，在 “方式”选项区中，单击选中“将筛选结果复制到其他位置”按钮；在“列表区域

23、”框中，软件自动输入了要筛选的数据区域“$A$6:$H$66”，也就是整个数据清单区域 ；在“条件区域”框中，输入设置好的包含筛选条件的区域“$A$1:$B$3”（可直接在该文本框中输入区域引用，也可用鼠标在工作表中选定条件区域）；在“复制到”文本框中输入时，首先将光标在其中定位，然后用鼠标在数据清单下方指定一个单元格（本例为“$A$71”），该单元格将作为放置筛选结果区域左上角的位置，筛选结果将在它的下方和右方进行排列。高级筛选现代教育技术中心 岳经伟 TEL：89714158 E-mail：（5）单击“确定”按钮，高级筛选结果如图4-14所示（原来数据我们选择的“隐藏”操作，以便大家能够看

24、到筛选出来的效果）。高级筛选现代教育技术中心 岳经伟 TEL：89714158 E-mail：使用高级筛选时，需要注意以下几个问题：（1）高级筛选必须指定一个条件区域，它可以与数据库表格在一张工作表上， 但是必须与数据库之间有空白行隔开；条件区域也可以与数据库表格不在一张工作表上。（2）条件区域中的字段名必须与数据库中的完全一样，最好通过复制得到。（3）如果“条件区域”与数据库表格在一张工作表上，在筛选之前，最好把光标放置到数据库中某一单元格上，这样数据区域就会自动填上数据库所在位置，省去再次鼠标选择或者重新输入的麻烦。（4）执行“将筛选结果复制到其他位置”时，在“复制到”文本框中输入或选取将来要放置位置的左上角单元格即可，不要指定某区域（因为事先无法确定筛选结果）。（5）根据需要，条件区域可以定义多个条件，以便用来筛选符合多个条件的记录。 这些条件可以输入到条件区域的同一行上，也可以输入到不同行上。但是必须记住：两个字段名下面的同一行中的各个条件之间为“与”的关系，也就是条件必须同时成立才算符合条件；两个字段名下面的不同行中的各个条件之间为“或”的关系，也就