第05章办公中的数据分析与处理ppt课件.ppt

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