办公自动化 第五章.ppt

第五章 办公中数据库分析与处理制作人 江友 办公中数据分析与处理 本章要点EXCEL表格中内容填充的常用技巧EXCEL数据库表格的特殊格式设置EXCEL表格中数据有效性的设置EXCEL数据库表格中的排序与筛选EXCEL数据库表格中的分类汇总操作EXCEL数据库表格中的数据透视分析EXCEL中的数据图表制作EXCEL表格中公式和函数的使用EXCEL中的关联表格操作 5 1利用EXCLE进行数据分析与处理概述 EXCEL中的数据库表格简介所谓数据库就是与特定主题和目标相关联系的数据集合 在EXCEL中 可以通过首先创建数据库表格 然后再利用其提供的菜单命令进行数据的分析与处理 EXCEL中有很多可以进行数据分析和数据处理的功能 包括数据排序 数据筛选 分类汇总 数据透视 合并计算 关联表格处理等 5 2 报名信息汇总表 数据库表格的建立 编号 列的自动序列填充 自动填充柄 定义单元格数据的有效性选定需要设置数据有效性的单元格区域 数据 有效性 数据有效性对话框 设置选项卡 序列 来源 注意 序列中的各项之间的逗号必须为英文状态符号 使用数据清单添加记录数据 记录单 5 3对报名信息汇总表进行数据分析 1 排序的依据 数字从最小的负数到最大的正数排序 在逻辑值中 FALSE排在TRUE之前 所有错误值的优先级等效 空格总是排在最后 汉字的排序可以按笔画 也可按字典顺序序 这可以通过有关操作设置 2 按单个字段排序 操作方法 姜光标置于待排序列中 单击工具栏上的 升序 或 降序 按钮即可 3 按多个字段排序将光标定位于待排序数据可的任意单元格中 选择 数据 排序 命令 打开排序对话框 再进行相关排序设置 4 特殊排序方式 数据 排序 排序选项 对话框 说明 按数据库排序时 只需单击数据库中的任一单元格 无需全选 筛选 自动筛选 高级筛选 1 自动筛选 数据 筛选 自动筛选 2 高级筛选 数据 筛选 高级筛选 说明 不管是自动筛选还是高级筛选 筛出来的数据都没有被删除 只是被隐藏而已 可以恢复 恢复方式有多种 如何通过 窗口 菜单冻结窗口 高级筛选的注意事项有哪些 高级筛选必须指定一个条件区域 它可以与数据可表格在一张表格上 但是必须与数据库之间有空白行隔开 条件区域也可以数据库表格不在一张工作表上 条件区域中字段名必须与数据库中的完全一样 最好通过复制得到 如果 条件区域 与数据库是再一张工作表上 在筛选之前 最好把光标置于数据库中某一单元格 这样数据库区域就回自动填上数据库所在位置 省去再次鼠标选择或者重新输入的麻烦 对报名信息汇总表中的数据分类汇总对报名信息汇总表进行数据透视分析 1 分类汇总 对需要分类汇总的字段进行排序 从而使相同的记录集中显示 选定数据库中任意一个单元格 选择 数据 分类汇总 命令 出现 分类汇总 对话框 2 数据透视分析 数据透视表以及数据透视表的创建和功能 数据透视图以及数据透视图的创建和功能 数据透视表的更新 数据透视表的数据来源于数据源 不能在数据透视表中直接修改 即使数据库中的数据被修改 透视表中的数据也不会自动更新 必须执行更新数据操作才能刷新 需要更新数据透视表时 有三种方法可用 数据 更新数据 在数据区域的快捷菜单中用 更新数据 命令 在 数据透视表 工具栏上单击 更新数据 按钮 5 4笔试成绩的数据处理 1 成绩中原始数据的输入如要输入的数据显示如 001 的效果 需要在输入数据前先以单引号 英文状态 开头 否则输入的 001 将只显示 1 2 数据库表格的格式设置 如下设置可通过 格式 菜单栏进行设置 1 数据库标题格式的设置 格式 合并并居中 2 字段标题的格式设置 字号 颜色 粗体效果等 3 条件格式设置 格式 条件格式 最多三个条件 4 表格边框设置 格式 边框 5 间隔底纹效果的设置 先设置样例再格式刷 用公式计算笔试成绩 进行公式输入时应该遵循以下规则 公式输入时必须以 开头 公式中间不能包括空格 公式中的单元格引用可以在编辑区直接输入 也可以通过单击该单元格或拖动单元格区域以实现自动填充 用算术运算符号 乘方 字符运算符号 链接字符串 以及关系运算符号 等来说明公式的操作类型 在公式中可以使用圆括号 也只能使用圆括号 并且允许圆括号嵌套 但是应该注意嵌套时前后括号的匹配关系 其计算顺序也是先内层再外层 单元格中的公式内容显示在编辑栏中 计算结果显示在单元格内 如果希望直接在单元格中显示为公式 可以使用 CTRL 在Tab键的上面 再次键入则取消公式显示 重新显示结果 说明 使用公式的好处在于公式中单元格名所表示的内容数据发生变化时 公式会自动计算相应的结果 利用RANK函数确定笔试名次 RANK函数的语法格式为 RANK number ref order 其中 number为需要找到排位的数字 ref为包含一组数字的数组或引用 order为一数字 指明排位的方式 为0或省略时按降序排列进行排位 不为0时按升序排列进行排位 说明 F4键是数组 绝对引用 和 相对引用 的热键 利用IF函数确定考试是否通过笔试考试 IF函数的格式 1 IF 条件 结果1 满足条件时显示的结果 结果2 不满足条件时显示的结果 2 例如 IF H4 12 可以 该式子表达的意思是 当H4小于或等于12时 显示的结果为 可以 当不满足此条件时 显示为空格 什么内容都没有 利用VLOOKUP函数确定选手姓名 VLOOKUP函数的作用是在表格或首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 类似于自动填充函数 跨工作簿 工作表的单元格地址引用的方法分别如下 1 在当前工作表中引用本工作表中的单元格 只需输入单元格的地址即可 2 在当前工作表中引用本工作薄其他工作表的单元格时 需首先输入被引用的工作表名和一个感叹号 英文状态 然后再输入那个工作表中的单元格地址 3 在当前工作表中引用另外工作簿中工作表的单元格时 需要首先输入由中括号 英文状态 包围的引用的工作簿名称 然后输入被引用的工作表名称和一个感叹号 最后输入那个工