电脑办公自动化使用教程第04章.ppt

本书由vince上传于世界工厂-学堂中心精品下载栏目 第 4 章 办公中的数据分析与处理办公中的数据分析与处理 办公自动化实用技术电子 教案 1中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 【本章内容介绍】 n在办公业务实践中，经常需要对大量的业务数 据进行分析和处理。如职工工资表中工资的计 算、汇总、分析，公司产品销售表中销售额的 统计、汇总、合并计算以及人事考勤表中职工 请假、旷工、出勤等情况的分析统计等。 n本章采用实例形式，以Excel软件操作为例介绍 办公业务实践中常见的数据分析与处理方法， 主要包括数据的排序、筛选、分类汇总、数据 透视、图表制作、公式与函数运用以及数据关 联表格的操作。 办公自动化实用技术电子教案2中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 【本章主要知识点】 nExcel表格中内容填充的常用技巧 nExcel数据库表格的特殊格式设置 nExcel表格中数据有效性的设置 nExcel数据库表格中的排序与筛选 nExcel数据库表格中的分类汇总操作 nExcel数据库表格中的数据透视分析 nExcel中的数据图表制作 nExcel表格中公式和函数的使用 nExcel中的关联表格操作 办公自动化实用技术电子教案3中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.1 EXCEL数据分析与处理概述 n在办公业务中，除了文字和表格之外，还需 要经常进行有关数据处理，有时候还需要将 处理的数据结果用一种图表来进行表现。 办公自动化实用技术电子教案4中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 1Excel中的数据库表格简介 n数据库就是与特定主题相联系的信息集合。在Excel中，数 据库实际上就是工作表中的一个区域，是一个二维表。 办公自动化实用技术电子教案5中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 2Excel中的数据分析与处理 nExcel中有很多可以进行的数据分析和数据处 理功能，包括数据排序、数据筛选、分类汇 总、数据透视、合并计算、关联表格处理等 。 n本章将对上一章的实例进一步进行介绍，包 括对报名汇总表进行数据分析，例如：按照 某个主题排序、按照指定条件筛选、按照指 定字段进行分类汇总、对整个表格进行数据 透视分析等；同时，本章还将利用Excel中的 公式和函数进行笔试成绩的计算、筛选、查 询，与面试成绩进行关联表格的综合处理等 。 办公自动化实用技术电子教案6中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 3Excel中的数据图表制作 n利用Excel，可以轻松、快捷地制作各种数据 图表。 n这些数据图表可将枯燥的数字形象化，并且 通过数据图表可以了解到数据之间的相互关 系和变化趋势。 n数据图表的类型很多，在办公实践中，使用 较多的主要有柱形图、条形图、折线图、饼 图、散点图等。 办公自动化实用技术电子教案7中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.2 “报名信息汇总表”数据库的建立 4.2.1 建立“报名汇总表”数据库框架 4.2.2 “编号”列的自动序列填充 4.2.3 定义单元格数据的有效性 4.2.4 使用数据清单添加记录 办公自动化实用技术电子教案8中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 制作效果 办公自动化实用技术电子教案9中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.3 对“报名汇总表”进行数据分析 n本节介绍利用Excel中的有关功能 ，对“报名信息汇总表”进行数据分 析的操作方法，主要包括数据的排 序、筛选、分类汇总与数据透视等 功能。 办公自动化实用技术电子教案10中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.3.1 对“报名信息汇总表”内容进行 排序 1排序的依据 2按单个字段排序 3按多级字段排序 4特殊排序方式 办公自动化实用技术电子教案11中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.3.2 对“报名信息汇总表”内容进行 筛选 n1自动筛选 n2高级筛选 n3高级筛选时的注意事项 办公自动化实用技术电子教案12中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.3.3 对“报名信息汇总表”中数据分 类汇总 n使用分类汇总，能够在数据库适当位置加上 统计结果，使数据库变得清晰易懂。 n对于本例，根据需要，可以对“性别”数据进行 分类汇总，以便了解不同性别报名情况；也 可以按照“学历”进行分类汇总，以便了解不同 学历的报名人数；可以对“报考职位”进行汇总 ，以便了解各个不同职位的报名情况。 n在执行分类汇总命令之前，首先应该对数据 库按照待汇总字段进行排序。 办公自动化实用技术电子教案13中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.3.4 对“报名信息汇总表”进行数据 透视分析 n1数据透视表（图）的概念 n2数据透视表的创建 n3创建数据透视图 n4数据透视表的更新 n5删除数据透视表 办公自动化实用技术电子教案14中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.4 利用数据图表表现数据之间关系 nExcel数据图表能将工作表中的数据变为非常 直观的图形格式，并且从图表上很容易看出 结果，以及数据之间的关系和数据的发展趋 势。 办公自动化实用技术电子教案15中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.4.1 利用图表向导创建条形图 n利用图表向导 方法可以创建 嵌入到原工作 表中的柱形图 表，该图表可 以用来反应各 地报考各种不 同职位的人数 情况，其样式 效果如图4-27 所示。 办公自动化实用技术电子教案16中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.4.2 数据图表的格式设置与内容更新 1图表的组成 2图表的格式设置 3数据图表的内容更新 办公自动化实用技术电子教案17中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.4.3 利用快捷键建立单独的图表并 进行格式设置 n在上面的向导方法制作图表外的最后一个步 骤中选择“作为新工作表插入”，即可制作一个 单独的图表工作表。除此之外，Excel中还可 以利用F11或Alt+ F1功能键快速制作单独的 柱状图表，然后再进行三维效果以及其他项 目的格式设置和修饰， 办公自动化实用技术电子教案18中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.4.4 选取不连续区域制作表现各职 位报考人数比例的饼图 n对不连续区域 的数据，也可 以制作图表， 这在饼状图制 作中非常普遍 。图4-36的饼 图就是如此制 作的，它用来 反应各个岗位 报名人数在总 人数中的比例 。 图4-36 选取不连续 数据区域制作的饼图 办公自动化实用技术电子教案19中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5 笔试成绩的数据处理 n上面的报名情况经过审核、分析和汇总之后 ，下一个环节就是要对合格考生进行统一设 置准考生编号，并举行统一考试，包括笔试 和面试。在本节重点介绍考试成绩的处理方 法，主要用到了Excel中的数据库表格设置、 内容录入技巧、公式和函数使用以及关联表 格操作等。 办公自动化实用技术电子教案20中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.1 笔试成绩处理表的框架建立 n招录过程的第一步是笔试，为此先建立笔试 成绩数据库表格框架。 n建立好的数据库格式框架如下图所示，还需 要进行数据输入和格式设置工作。 办公自动化实用技术电子教案21中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.2 成绩处理表中原始数据的输入 表格框架建立后，下一步就是要输入原始数据，输入时 候需要注意以下几点： （1）“编号”字段可利用自动序列输入，为了使A4 单元 格中显示出“001”效果，需要在输入时现以单引号（ ）开头，否则输入的“001”将只是显示为“1”。 （2）“性别”字段可以设置有效性，或者利用列表选择输 入。 （3）三门考试课程对应的三个字段，需要先设置数据 有效性（整数，范围0100），然后再进行数据的 输入，这样才能较好地避免数据输入时的错误。 办公自动化实用技术电子教案22中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.3 数据库表格的格式设置 n1数据库标题格式设置 n2字段标题格式设置 n3条件格式化设置 n4表格边框线设置 n5间隔底纹效果的设置 办公自动化实用技术电子教案23中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.4 用公式计算笔试成绩 n笔试成绩的最后结果需要使用公式来进行运 算。 n公式的有关知识： n公式是对工作表中的数据进行计算和分析的一 种等式，它可以对工作表数值进行算术运算、 关系运算和逻辑运算，同样也可以对字符数据 以及其他数据进行运算。公式可以引用同一工 作表中的其他单元格、同一工作簿不同工作表 中的单元格，或者其他工作簿的工作表中的单 元格。 办公自动化实用技术电子教案24中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 公式输入应该遵循的基本规则 （1）公式输入时必须以“=”开头，公式中间不能包括空格。 （2）公式中的单元格引用，可以在编辑区直接输入，也可 以通过单击该单元格或拖动单元格区域从而实现自动填入 。 （3）用算术运算符号：+（加）、（减）、*（乘）、/（ 除）、（乘方），字符运算符号&(连接字符串)以及关系 运算符号（大于）、（小于）、=（等于）等来说明公 式的操作类型。 （4）在公式中可以使用圆括号，也只能使用圆括号，并且 允许圆括号嵌套，但是应该注意嵌套时前后括号的匹配关 系，计算顺序是先内层再外层。 （5）单元格中的公式内容显示在编辑栏中，计算结果则显 示在单元格内，如果希望直接在单元格中显示为公式，可 以使用Ctrl+键（在Tab键的上面），再次键入，则取消 公式显示，重新显示结果。 办公自动化实用技术电子教案25中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.5 利用RANK函数确定笔试名次 n在Excel 中，名次的确认最好使用RANK函数，该函 数是专门进行排名次的函数，其结果是返回一个数值 在一组数值中的排位。 nRANK函数的语法格式为： RANK(number,ref,order) 。其中：Number 为需要找到排位的数字；Ref 为包 含一组数字的数组或引用；Order 为一数字，指明 排位的方式，为 0 或省略，按降序排列排位，不为零 ，按升序排列进行排位。 n对本例中笔试成绩的名次排列，使用RANK函数并进 行函数拖动复制即可。同样先选择H4单元格，在编 辑栏中输入公式“=RANK(G4, $G$4:$G$33)”，然后 按回车键，即可得到第一个考生的名次“4”，采用与 上面同样的方法拖动复制得到每个考生的名次。 办公自动化实用技术电子教案26中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.6 利用IF函数确定考生是否通过 笔试考试 在Excel 中，利用IF函数可以非常方便地进行条件判断 。本实例中，名次确认完成后，根据考试规则要求， 假设只能选取前40%（即前12名考生进入面试阶段。 则“是否通过”一列可以按照如下步骤进行操作。 （1）将鼠标定位到I4 单元格。 （2）在I4 单元格中输入：=IF(H4=12,“可以“,“)。 （3）单击编辑栏上的确定按钮，则I4 单元格中显示出 计算结果“可以”。 （4）选取I4 单元格，拖动单元格右下角的拖动柄一直 到I33。 （5）松开鼠标，则I5到I33 中部分单元格将会显示出“ 可以”样式，如图4-46所示，说明这些人都在前12名 之内，顺利通过了笔试考试阶段。 办公自动化实用技术电子教案27中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.7 利用自动筛选获取通过笔试人 员的情况 n对于通过笔试考试的考生，还需要进行面试 ，为此需要将他们的记录从数据库中筛选出 来。将笔试成绩合格者筛选出来，可以利用 自动筛选功能实现。 办公自动化实用技术电子教案28中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.8 综合成绩表的创建 n将Sheet3工作表 更名为“综合成绩 表”，并制作如图 4-49样式表格。 该表格中 “编号” 、“姓名”、“笔试 成绩”三列下面对 应的内容从“笔试 通过”工作表复制 过来即可，“面试 成绩”一列根据实 际情况输入。 图4-49 综合成绩表的基本样式 办公自动化实用技术电子教案29中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.9 综合成绩计算 可以采用与前面计算笔试成绩一样的方法计算笔试和面 试的综合成绩。步骤如下： （1）将鼠标定位到“综合成绩表”工作表的F2单元格中 。 （2）在F2 单元格中输入公式“=D2*0.7+E2*0.3”（此处 假设在综合成绩计算中，笔试部分占70%，面试部分 占30%）。 （3）单击编辑栏上的确定按钮，则F2 单元格中显示出 计算结果“84.97”。 （4）选取F2 单元格，拖动单元格右下角的拖动柄一直 到F13。 （5）松开鼠标，则F3到F13 的所有单元格均计算出了 对应的综合成绩。 办公自动化实用技术电子教案30中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 4.5.10 综合名次以及最后结果的确定 n按照与4.5.5和4.5.6中类似的方法，分别利用RANK函数和IF 函数 ，确定每个考生的“综合名次”并给出“是否进入体检阶段”的判断（ 假设综合名次前50%的可以进入）。 n综合成绩表的最后处理结果如图4-50所示。 n图4-50 综合成绩表的最终效果 办公自动化实用技术电子教案31中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 小 结 n在办公业务实践中，经常需要对大量的业务数据进行分析 和处理。这在Excel中通常通过建立数据库表格来实现。利 用有关功能可以对Excel数据库表格进行数据分析，包括数 据排序、数据筛选、分类汇总以及数据透视分析等。利用 数据清单可以制作精美的数据图表，利用公式和函数能够 进行各种复杂、繁琐的数据计算和数据处理。 n对于数据的排序操作，需要掌握排序依据，单个字段排序 和多个关键字段排序的操作方法。数据筛选有两种操作方 法：自动筛选与高级筛选，要注意二者的适用情况，对于 高级筛选，还必须掌握如何正确设置筛选条件区域。 办公自动化实用技术电子教案32中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 n数据分类汇总可以将数据库中的数值，按照某一项内容进行 分类核算，使得汇总结果清晰明了，操作时注意必须首先按 照汇总字段排序。数据透视表是一种对大量数据快速汇总和 建立交叉列表的动态工作表，而数据透视图是形象生动的图 表，还可以根据数据透视表制作不同格式的数据透视报告。 n数据计算主要通过使用Excel的公式和函数进行，操作时需要 注意以下问题：函数多层嵌套时的括号匹配；公式中的符号 必须使用英文状态下的符号；各个函数的正确使用；单元格 引用方式的正确选择；关联表格之间数据引用的正确表达方 法。 n通过本章学习，要求读者掌握Excel操作数据库表格的快速创 建方法，能进行特殊格式设置，熟练掌握数据排序、筛选、 分析、汇总以及公式、函数计算，数据图表制作。 小 结（续） 办公自动化实用技术电子教案33中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 习 题 一、填空 1Excel对数据表中数据进行分类汇总前首先应该 _。 2对数据库表格中的数据进行条件格式化时，最多可以设 置_个不同条件。 3使用Rank函数时，如最后一个参数为“1”，则排列名次 为按照_排列。 4高级筛选时，条件区域中同一个条件行的不同单元格中 的条件之间互为_的逻辑关系；不同条件行 的单元格条件之间互为_的逻辑关系。 办公自动化实用技术电子教案34中国水利水电出版社 本书由vince上传于世界工厂-学堂中心精品下载栏目 二、判断题 1Excel中对数据库按照某列进行排序时必须选 定该列。 （ ） 2Excel中对数据库进行按几个关键字排序时必 须选定整个表格区域。 （ ） 3Excel中自动筛选可以实现多个字段之间的或 运算。 （ ） 4Excel中排序时，无论是递增还是递减排序