第8章 分析和管理数据.pdf

109 第 8 章 分析和管理数据 通过本章 你应当学会 1 排序 2 筛选 3 分类汇总 4 合并计算 在 Excel 中制作好表格后 可以方便地对数据进行分析和管理 如按设置的条件对数据进 行排序 筛选 按位置或类别合并计算数据 汇总数据等 8 1 数据的排序 排序是统计工作中经常涉及的一项工作 在 Excel 中可以将数据按单个条件进行排序 还 可以按多个条件或自定义条件进行排序 8 1 1 按单个条件进行排序 如果需要将数据按某一字段进行排序 此时可以使用按单个条件进行排序的方法 按单个条件进行排序 操作步骤如下 1 打开 计算机应用2班成绩表 工作簿 选择A2 I16单元格区域 选择 数据 选项卡 单击 排序和筛选 组中的 排序 按钮 如图 8 1 1 所示 图 8 1 1 Excel 2007 中文版实用教程 110 2 打开 排序 对话框 在 列 栏 下的 主要关键字 下拉列表框中选择需要排 序的列 这里选择 总分 在 排序依据 下拉列表框中选择 数值 选项 在 次序 下拉列表框中选择 降序 选项 表示以总分 从高到低进行排序 单击 确定 按钮 如图 8 1 2 所示 图 8 1 2 3 此时表格中按总分从高到低进行排序 如图 8 1 3 所示 图 8 1 3 如果创建的表格中没有表头 则在 排序 对话框的 主要关键字 下拉列表框中将显示 列 A 列 B 和 列 C 等选项 此时只需选择其中某个选项即可进行该列的排序 在需要进行排序的列中 选择要排序的单元格后 单击 数据 选项卡 排序与筛选 组中的 升 序 按钮或 降序 按钮 可以对该列中的数据从低到高或从高到低进行排序 8 1 2 按多个条件排序 当按单个条件进行排序时 有两名学生的总分都是487分 此时可以再按其他条件进行排序 即按多个条件进行排序 按多个条件排序 操作步骤如下 1 打开 计算机应用 2 班成绩表 工作簿 选择 A2 I16 单元格 选择 数据 选项卡 单击 排序和筛选 组中的 排序 按钮 如图 8 1 4 所示 第 8 章 分析和管理数据 111 图 8 1 4 2 打开 排序 对话框 单击 添加条件 按钮 在 列 栏下的 主要关 键字 下拉列表框中选择 总分 在 排序依据 下拉列表框中选择 数值 选项 在 次序 下拉列表框中选择 降序 选项 表示首先以总分从高到低进行排序 3 在 次要关键字 下拉列表框中选 择 专业英语 在 排序依据 下拉列表框 中选择 数值 选项 在 次序 下拉列表框 中选择 降序 选项 表示当总分成绩相同时 以专业英语成绩从高到低进行排序 单击 确 定 按钮 如图 8 1 5 所示 图 8 1 5 4 此时表格中显示排序后的结果如图 8 1 6 所示 图 8 1 6 在对数据进行排序时 当 主要关键字 和 次要关键字 两项都相同时 可以再以 第 三关键字 进行排序 Excel 2007 中文版实用教程 112 在进行升序排列时 当需要排序的对象是数字时就从最小的负数到最大的正数进行排序 若是字母则按 A Z 的顺序进行排序 若为逻辑值则 FLASE 排在 TRUE 之前 若是空格则排在最 后 降序排序的结果与升序排序的结果相反 8 1 3 自定义排序 在Excel中除了上述的基本排序功能外 还可以按自定义的条件进行排序 如果需要按大专 本科 硕士 博士进行学历排序时 需要先将这些数据定义为序列 然后进行自定义排序 自定义条件排序 操作步骤如下 1 打开 员工信息表 工作簿 选择 数 据 选项卡 单击 排序和筛选 组中的 排 序 按钮 如图 8 1 7 所示 2 打开 排序 对话框 单击其中的 选 项 按钮 打开 排序选项 对话框 在 方向 栏中选中 按列排序 单选按钮 在 方法 栏中选中 笔划排序 单选按钮 单击 确定 按钮 如图 8 1 8 所示 图 8 1 7 图 8 1 8 3 返回到 排序 对话框 在 列 栏下的 主要关键字 下拉列表框中选择 学历 在 排序依据 下拉列表框中选择 数值 选项 在 次序 下拉列表框中选择 自定义序列 选项 如图 8 1 9 所示 图 8 1 9 第 8 章 分析和管理数据 113 4 打开 自定义序列 对话框 在 输入序列 文本框中按顺序输入需要定义的数据 这里输入 大专 本科 硕士 博士 单击 添加 按钮 将输入的序列添加到 自定义序列 列表框中 如图 8 1 10 所示 然后单击 确定 按钮返回 排序 对话框 图 8 1 10 5 在 排序 对话框中单击 确定 按钮 此时表格中将显示所需的排序结果 如 图 8 1 11 所示 8 2 数据的筛选 Excel 提供了数据筛选功能 通过该功能可以选择性地在大型数据库中只显示满足某一个或 某几个条件的记录 筛选有 3 种方式 自动筛选 自定义筛选和高级筛选 下面分别介绍 8 2 1 自动筛选 如果想在工作表中只显示满足给定条件的数值 可以使用自动筛选功能 自动筛选 操作步骤如下 1 打开 计算机应用 2 班成绩表 工作簿 选择表头所在的 A1 I1 单元格区域 选择 数 据 选项卡 单击 排序和筛选 组中的 筛选 按钮 如图 8 2 1 所示 图 8 1 11 Excel 2007 中文版实用教程 114 4 此时筛选出 C 语言 成绩高于平均值的 8 个学生记录 如图 8 2 4 所示 图 8 2 4 图 8 2 3 图 8 2 2 图 8 2 1 2 在工作表表头各字段右侧均出现下拉按钮 如图 8 2 2 所示 3 单击 C 语言 右侧的下拉按钮 在弹出的下拉列表中选择 数字筛选 高于平均 值 命令 如图 8 2 3 所示 第 8 章 分析和管理数据 115 若要退出工作表中的筛选状态 只需再次单击 筛选 按钮即可 8 2 2 自定义筛选 当需要设置更多条件进行筛选时 可以通过 自定义自动筛选方式 对话框进行设置 从 而得到更为准确的筛选结果 自定义筛选 操作步骤如下 1 打开 计算机应用 2 班成绩表 工作簿 选择表头所在的 A1 I1 单元格区域 选择 数 据 选项卡 单击 排序和筛选 组中的 筛选 按钮 如图 8 2 5 所示 2 单击 总分 右侧的下拉按钮 在弹出的下拉列表中选择 数字筛选 大于或等于 命令 如图 8 2 6 所示 图 8 2 5 图 8 2 6 Excel 2007 中文版实用教程 116 图 8 2 8 在 自定义自动筛选方式 对话框中输入筛选条件时 可以使用 代表单个的任意字符 用 代表任意多个字符 8 2 3 高级筛选 高级筛选是通过已经设置好的条件来对工作表中的数据进行筛选 高级筛选需要在工作表 中无数据的地方指定一个区域用于存放筛选条件 这个区域就是条件区域 高级筛选 操作步骤如下 1 打开 计算机应用 2 班成绩表 工作簿 在空白单元格中建立条件区域 并输入筛选 条件 如图 8 2 9 所示 图 8 2 9 图 8 2 7 3 打开 自定义自动筛选方式 对话框 在 总分 栏下的第一个下拉列表框中选择 大 于 选项 在其右侧的文本框中输入 470 选择 与 单选按钮 在 与 单选按钮下的 第一个下拉列表框中选择 小于 选项 在其 右侧的文本框中输入 500 单击 确定 按钮 如图 8 2 7 所示 4 此时筛选出总分在 470 到 500 分之间的记录 如图 8 2 8 所示 第 8 章 分析和管理数据 117 图 8 2 10 3 打开 高级筛选 对话框 在 方 式 栏下选中 在原有区域显示筛选结果 单 选按钮 在 列表区域 文本框中默认选择 A1 I16 单元格区域为筛选区域 单击 条件 区域 文本框中的按钮 如图8 2 11所示 缩小 高级筛选 对话框 图 8 2 11 4 选择前面建立的条件区域 J6 K7 单击按钮 如图 8 2 12 所示 还原 高级筛选 对话框 图 8 2 12 5 在 高级筛选 对话框中单击 确定 按钮 如图 8 2 13 所示 图 8 2 13 6 此时工作表中显示出数据库成绩大于 85 且专业英语成绩大于 85 的所有学生记录 如 图 8 2 14 所示 图 8 2 14 2 选择 数据 选项卡 单击 排序和筛选 组中的 高级 按钮 如图 8 2 10 所示 Excel 2007 中文版实用教程 118 2 打开 排序 对话框 在 列 栏下的 主要关键字 下拉列表框中选择 所属部门 在 排序依据 下拉列表框中选择 数值 选项 在 次序 下拉列表框中选择 升序 选项 单击 确定 按钮 如图 8 3 2 所示 图 8 3 1 在 高级筛选 对话框中 选择 将筛选结果复制到其他位置 单选按钮后 下面的 复制到 文本框被激活 单击该文本框后的按钮 选择将筛选结果复制到的位置 然后单击按钮 再单击 确定 按钮可将选择出的记录复制到表格中的其他位置 在 高级筛选 对话框中 若勾选 选择不重复的记录 复选框 当有多行满足条件时 只显示或复制惟一的行 而排除重复的行 8 3 数据的分类汇总 分类汇总对数据库中指定的字段进行分类 然后统计同一类记录的有关信息 统计的内容 可以由用户指定 也可以统计同一类记录的记录条数 还可以对某些数值段求和 求平均值 求极值等 8 3 1 创建分类汇总 在创建分类汇总之前 应先对要分类汇总的数据进行排序 即将同类的数据排列在一起 创建分类汇总 操作步骤如下 1 打开 1 月员工销售业绩表 工作薄 选择所属部门所在的列 选择 数据 选项卡 在 排序和筛选 组中单击 排序 按钮 如图 8 3 1 所示 第 8 章 分析和管理数据 119 3 此时工作表按 所属部门 排序 结果如图 8 3 3 所示 4 选择 数据 选项卡 单击 分级显示 组中的 分类汇总 按钮 打开 分类汇总 对话框 在 分类字段 下拉列表框中选择 所 属部门 选项 在 汇总方式 下拉列表框中 选择 求和 选项 在 选定汇总项 列表框 中勾选 销售额 复选框 单击 确定 按钮 如图 8 3 4 所示 图 8 3 4 5 此时得到各所属部门的销售额汇总数据 如图 8 3 5 所示 图 8 3 5 图 8 3 3 图 8 3 2 Excel 2007 中文版实用教程 120 2 此时隐藏 销售 2 部 的详细数据 只显示其汇总结果 如图 8 3 7 所示 图 8 3 6 图 8 3 7 若不先对数据进行排序操作 则在执行分类汇总操作后 Excel 2007 只会对连续相同的数据 进行汇总 在 分类汇总 对话框中 针对不同的汇总类型 可以在 汇总方式 下拉列表框中选择 平 均值 和 标准偏差 等汇总方式 在 分类汇总 对话框中 勾选 每组数据分页 复选框 则将得到分页显示的分类汇总结果 8 3 2 显示或隐藏分类汇总 为了方便查看数据 可将分类汇总后暂时不需要使用的数据隐藏起来 减少界面的占用空间 当需要查看隐藏的数据时 可再将其显示 隐藏及显示分类汇总 操作步骤如下 1 打开 1 月员工销售业绩表 工作薄 单击 销售 2 部 汇总 左侧的 折叠 按钮 如图 8 3 6 所示 第 8 章 分析和管理数据 121 3 用同样的方法可以隐藏销售 1 部和销售 3 部的详细数据 完成后如图 8 3 8 所示 4 单击各所属部门左侧的 展开 按钮 即可显示所隐藏的详细数据 如图8 3 9所示 图 8 3 8 8 3 3 删除分类汇总 查看完分类汇总的数据后 有时需要删除分类汇总 使表格还原至以前的状态 删除分类汇总 操作步骤如下 1 打开 1月员工销售业绩表 工作薄 选择 数据 选项卡 在 分级显示 组中单击 分 类汇总 按钮 如图 8 3 10 所示 图 8 3 9 图 8 3 10 2 在打开的 分类汇总 对话框中单 击 全部删除 按钮 如图 8 3 11 所示 图 8 3 11 Excel 2007 中文版实用教程 122 8 4 合 并 计 算 通过合并计算 可以对来自一张或多张工作表中的数据进行汇总 并建立合并计算表 存 放合并计算结果的工作表称为 目标工作表 接收合并数据并参与合并计算的区域称为 源 区域 合并计算的方法有两种 即按位置合并计算和按分类合并计算 8 4 1 按位置合并计算 按位置合并计算数据时 要求在所有源区域中的数据被同样排列 也就是每一个工作表中 的记录名称和字段名称均在相同的位置上 按位置合并计算 操作步骤如下 1 打开 1 季度员工销售业绩表 工作簿 在 1 月 2 月 和 3 月 工作表中已 输入相应月份的数据 如图 8 4 1 所示 图 8 4 1 2 选择 1 季度 工作表 选择 E3 E17 单元格区域为目标区域 如图 8 4 2 所示 图 8 3 12 3 此时工作表还原到以前的状态 如图 8 3 12 所示 第 8 章 分析和管理数据 123 图 8 4 2 3 选择 数据 选项卡 单击 数据工具 组中的 合并计算 按钮 如图 8 4 3 所示 图 8 4 3 4 打开 合并计算 对话框 在 函数 下拉列表框中选择 求和 函数 单击 引用 位置 文本框后的按钮 如图 8 4 4 所示 图 8 4 4 5 选择 1 月 工作表 选择 E3 E17 单元格区域 单击按钮还原对话框 如图 8 4 5 所示 图 8 4 5 Excel 2007 中文版实用教程 124 6 在 合并计算 对话框中单击 添加 按钮 此时选择的区域被添加到了 所有引用 位置 列表框中 如图 8 4 6 所示 图 8 4 6 7 用同样的方法分别将 2月 和 3月 工作表的 E3 E17 单元格区域添加到 所有引 用位置 列表框中 单击 确定 按钮 如图 8 4 7 所示 图 8 4 7 8 此时在 1 季度 工作表的 E3 E17 单元格区域中将显示合并计算的结果 如图 8 4 8 所示 图 8 4 8 在 合并计算 对话框的 函数 下拉列表框中可以选择 求和 最大值 最小值 和 乘积 等函数作为合并计算的函数 在 合并计算 对话框的 所有引用位置 列表框中选择某项后 单击 删除 按钮可以 删除该选项 第 8 章 分析和管理数据 125 8 4 2 按分类合并计算 如果各月份公司所属员工姓名不尽相同 所放位置也不一定相同时 同样可以使用合并计 算功能来完成汇总工作 但此时不能使用前面介绍的按位置合并计算 而应使用按分类合并计 算数据 按分类合并计算 操作步骤如下 1 打开 1 季度销售表 工作簿 选择 1 季度 工作表 选择 A2 单元格 将其作为 目标区域的起始单元格 如图 8 4 9 所示 图 8 4 9 2 选择 数据 选项卡 单击 数据工具 组中的 合并计算 按钮 如图 8 4 10 所示 图 8 4 10 3 打开 合并计算 对话框 在 函数 下拉列表框中选择 求和 函数 单击 引用 位置 文本框后的按钮 如图8 4 11所示 图 8 4 11 4 选择 1 月 工作表 选择 A2 E17 单元格区域 单击按钮还原对话框 如图 8 4 12 所示 Excel 2007 中文版实用教程 126 图 8 4 12 图 8 4 13图 8 4 14 图 8 4 15 5 在 合并计算 对话框中单击 添加 按钮 此时选择的单元格区域被添加到 所有 引用位置 列表框中 如图 8 4 13 所示 6 用同样的方法分别将 2 月 和 3 月 工作表的 A2 E17 单元格区域添加到 所有 引用位置 列表框中 勾选 首行 和 最左列 复选框 单击 确定 按钮 如图 8 4 14 所示 7 此时在 1 季度 工作表的单元格区域将显示合并计算后的结果 如图 8 4 15 所示 第 8 章 分析和管理数据 127 图 8 4 16 8 在 B C