项目十三 管理Excel工作表中的数据 除了可以利用公式和函数对工作表数据 进行计算和处理外 还可以利用Excel提供 的数据排序 筛选 分类汇总 以及合并计 算等功能来管理和分析工作表中的数据 本 项目中我们便来学习这些知识 知识目标 u掌握公式的创建 移动 复制与修改方法 u了解审核公式的方法 以及公式中错误提示的含义 u掌握函数的使用方法 u掌握设置单元格引用的方法 能力目标 u能够对够工作表中的数据进行排序 u能够根据需要从工作表中找出满足一定条件的数据 其他数据隐藏 u能够根据需要对工作表中的数据分门别类地进行统计处理 如进行求和 求平均值 统计个数 求最大值 最小值 等计算 并且分级显示汇总 的结果 u能够根据需要将各单独工作表中的数据合并计算 如求和 求平均值等 到一个主工作表中 本项目任务列表 l任务一 数据排序 l任务二 数据筛选 l任务三 分类汇总 l任务四 合并计算 任务一 数据排序 l一 简单排序 l二 多关键字排序 排序是对工作表中的数据进行重新组织安排 的一种方式 在Excel中可以对一列或多列中的数 据按文本 数字以及日期和时间进行排序 还可 以按自定义序列 如大 中 小 进行排序 一 简单排序 简单排序是指对数据表中的单列数据按照Excel默认的升序 或降序的方式排列 单击要进行排序的列中的任一单元格 再 单击 数据 选项卡上 排序和筛选 组中 升序 按钮或 降序 按钮 所选列即按升序或降序方式进行排序 在Excel中 不同数据类型的默认排序方式如下 u升序排序 数字 按从最小的负数到最大的正数进行排序 日期 按从最早的日期到最晚的日期进行排序 文本 按照特殊字符 数字 0 9 小写英文字母 a z 大写英文字母 A Z 汉字 以拼音排序 排序 逻辑值 FALSE排在TRUE之前 错误值 所有错误值 如 NUM 和 REF 的优先级相同 空白单元格 总是放在最后 u降序排序 与升序排序的顺序相反 当工作表中的单元格引用了其他单元格内的数据时 有可能因排序 的关系 使公式的引用地址错误 从而使工作表中的数据不正确 二 多关键字排序 多关键字排序就是对工作表中的数据按两个或两个以上的关键 字进行排序 在此排序方式下 为了获得最佳结果 要排序的单元格 区域应包含列标题 对多个关键字进行排序时 在主要关键字完全相同的情况下 会 根据指定的次要关键字进行排序 在次要关键字完全相同的情况下 会根据指定的下一个次要关键字进行排序 依次类推 任务实施 排序2011年春季成绩表数据 总分 相同 的 按 关系 管理 科目降 序排序 任务二 数据筛选 l一 自动筛选 l二 按条件筛选 l三 高级筛选 l四 取消筛选 在对工作表数据进行处理时 有时需要从工作 表中找出满足一定条件的数据 这时可以用Excel的 数据筛选功能显示符合条件的数据 而将不符合条 件的数据隐藏起来 Excel提供了自动筛选 按条件 筛选和高级筛选三种筛选方式 无论使用哪种方式 要进行筛选操作 数据表中必须有列标签 一 自动筛选 自动筛选一般用于简单的筛选 筛选时将不需要显示的记录 暂时隐藏起来 只显示符合条件的记录 筛选出成绩表中 国际贸易 成 绩大于等于90分的记录 二 按条件筛选 在Excel中 还可按用户自定的筛选条件筛选出符合需要的 数据 先进行筛选操作 然后单击列标题右侧的筛选箭头 在 打开的筛选列表选择所需选项并在打开的对话框中进行设置 筛选出 平均分 大于 80小于90的记录 如果所筛选的列中记录为文本型或日期型数据 筛选列 表中的 数字筛选 项会变成 文本筛选 或 日期筛选 其操作 与 数字筛选 相似 此外 如果对工作表中的单元格填充了 颜色 还可以按颜色对工作表进行筛选 三 高级筛选 高级筛选用于条件较复杂的筛选操作 其筛选结果可显 示在原数据表格中 不符合条件的记录被隐藏起来 也可以 在新的位置显示筛选结果 不符合条件的记录同时保留在数 据表中 从而便于进行数据的对比 要通过多个条件来筛选单元格区域 应首先在选定工作 表中的指定区域创建筛选条件 然后单击 数据 选项卡上 排 序和筛选 组中的 高级 按钮 打开 高级筛选 对话框 接下来 分别选择要筛选的单元格区域 筛选条件区域和保存筛选结 果的目标区域 在高级筛选中 筛选条件又可分为多条件筛选和多选一 条件筛选两种 下面分别说明 1 多条件筛选 多条件筛选是指查找出同时满足多个条件的记录 条件的标题必须 与筛选区域的标 题保持一致 级别 为良 且 就业指 导 成绩大于80的记录 确认列表区域的单元格引用 是否正确 如果不正确 重新 在工作表中进行选择 2 多选一条件筛选 多选一条件筛选是指在查找时只要满足几个条件当中的 一个 记录就会显示出来 多选一条件筛选的操作与多条件筛 选类似 只是需要将条件输入在不同的行中 级别 为良 或者 就业 指导 成绩大于80的记录 若在 高级筛选 对话框选中 将筛选结果复制到其他位 置 单选钮 则 复制到 选项变为可用 此时可在该编辑框 中单击 然后在工作表单击要放置筛选结果单元格区域的 左上角单元格 四 取消筛选 对于不再需要的筛选可以将其取消 若要取消在数 据表中对某一列进行的筛选 可以单击该列列标签单元格 右侧的筛选按钮 在展开的列表中选择 全选 复选框 然 后单击 确定 按钮 此时筛选按钮上的筛选标记消失 该 列所有数据显示出来 若要取消在工作表中对所有列进行的筛选 可单击 数据 选项卡上 排序和筛选 组中的 清除 按钮 此时筛 选标记消失 所有列数据显示出来 若要删除工作表中的 三角筛选箭头 可单击 数据 选项卡上 排序和筛选 组中 的 筛选 按钮 任务实施 筛选商品促销折算表数据 下面将 商品促销折算表 中 类别 为A 销售量 大于190 折后总额 大于2000的记录筛选出来 打开素材文件 输入筛选条件 打开 高级筛选 对话框 选择筛选结果的放置方式 最后设置筛选项 确认列表区域的单元格引用 是否正确 如果不正确 重新 在工作表中进行选择 将筛选结 果复 制到其他位置 筛选出 类别 为A 销售量 大于190 折 后总额 大于 2000的记录 任务三 分类汇总 l一 简单分类汇总 l二 多重分类汇总 l三 嵌套分类汇总 l四 分级显示数据 l五 取消分类汇总 分类汇总是把数据表中的数据 分门别类地进行统计处理 无需建 立公式 Excel将会自动对各类别的 数据进行求和 求平均值 统计个 数 求最大值 最小值 和总体方 差等多种计算 并且分级显示汇总 的结果 从而增加了工作表的可读 性 使用户能更快捷地获得需要的 数据并做出判断 分类汇总分为简单分类汇总 多重分类汇总和嵌套分类汇 总三种方式 无论哪种方式 要进行分类汇总的数据表的第一 行必须有列标签 而且在分类汇总之前必须先对数据进行排序 以使得数据中拥有同一类关键字的记录集中在一起 然后再 对记录进行分类汇总操作 一 简单分类汇总 简单分类汇总指对数据表中的某一列以一种汇总方式进 行分类汇总 先对要进行分类汇总操作的列进行排序 然后打开 分类 汇总 对话框进行设置 设置分类汇总项 汇总出进货表中不同经手人所 进货物的数量和金额总计 在 分类字段 下拉列表进行选择时 该字段必须是已经排序的字段 如果选择没有排序的列标题作为分类字段 最后的分类结果是不正确的 此外 在 分类汇总 对话框中做设置时 注意在 选定汇总项 列表框中 选择的汇总项要与 汇总方式 下拉列表中选择的汇总方式相符合 例如 文 本是不能进行平均值计算的 二 多重分类汇总 对工作表中的某列数据选择两种或两种以上的分类汇总方式或 汇总项进行汇总 就叫多重分类汇总 也就是说 多重分类汇总每 次用的 分类字段 总是相同的 而汇总方式或汇总项不同 而且第2 次汇总运算是在第1次汇总运算的结果上进行的 第1次分类 汇总结果 设置第2次 分类汇总项 注意在 分类汇总 对话框中取消 替换当前分类汇总 复选框 否则新 创建的分类汇总将替换已存在的分类汇总 此外 选择 每组数据分页 复 选框 可使每个分类汇总自动分页 第2次分类汇总结果 汇总不同 经手人 所 进货物单价 数量和金额的最大值 三 嵌套分类汇总 嵌套分类汇总是指在一个已经建立了分类汇总的工作 表中再进行另外一种分类汇总 两次分类汇总的字段是不 相同的 其他项可以相同 也可以不同 在建立嵌套分类汇总前首先对工作表中需要进行分类 汇总的字段进行多关键字排序 排序的主要关键字应该是 第1级汇总关键字 排序的次要关键字应该是第2级汇总关 键字 其他的依次类推 有几套分类汇总就需要进行几次分类汇总操作 第2次 汇总是在第1次汇总的结果上进行操作的 第3次汇总操作 是在第2次汇总的结果上进行的 依次类推 在进货表中分别按 经手人 和 进货日期 对进货的数 量和金额进行汇总 对工作表进行多 关键字排序 设置嵌套分类 汇总项 按 经手人 和 进货日 期 对进货的数量和金 额进行汇总结果 四 分级显示数据 对工作表中的数据执行分类汇总后 Excel会自动按汇总 时的分类分级显示数据 u分级显示明细数据 在分级显示符号中单击所需级别的数字 较低级别的明细数据会隐藏起来 u隐藏与显示明细数据 单击工作表左侧的折叠按钮可以隐藏 原始数据 此时该按钮变为 单击该按钮将显示组中的原始数 据 u清除分级显示 不需要分级显示时 可以根据需要将其部分 或全部的分级删除 方法是选择要取消分级显示的行 然后单 击 数据 选项卡上 分级显示 组中的 取消组合 清除分级显示 项 可取消部分分级显示 要取消全部分级显示 可单击分类 汇总工作表中的任意单元格 然后单击 数据 选项卡上 分级显 示 组中的 取消组合 清除分级显示 项即可 五 取消分类汇总 要取消分类汇总 可打开 分类汇总 对话框 单击 全部删除 按钮 删除分类汇总的同时 Excel会删除与分 类汇总一起插入到列表中的分级显示 任务实施 汇总初二 1 班成绩表数据 下面对 初二 1 班成绩表 进行 姓名 和 类别 字段的嵌 套分类汇总 任务四 合并计算 l一 按位置合并计算 l二 按分类合并计算 合并计算是指用来汇总一个或多个源区域中数据的方法 合并计 算不仅可以进行求和汇总 还可以进行求平均值 计数统计和求标准 差等运算 利用它可以将各单独工作表中的数据合并计算到一个主工 作表中 其中 单独工作表可以与主工作表在同一个工作簿中 也可 位于其他工作簿中 要想合并计算数据 首先必须为合并数据定义一个目标区 用来 显示合并后的信息 此目标区域可位于与源数据相同的工作表中 也 可在另一个工作表中 其次 需要选择要合并计算的数据源 此数据 源可以来自单个工作表 多个工作表或多个工作簿 Excel提供了两种合并计算数据的方法 一是按位置合并计算 二 是按分类合并计算 一 按位置合并计算 按位置合并计算要求源区域中的数据使用相同的行标签和列 标签 并按相同的顺序排列在工作表中 且没有空行或空列 下面将同一工作簿的多张工作表中的成绩汇总到一张工作表中 放置合并计算 结果的工作表 在 网页设计 工作表中选择 要进行合并计算的单元格区 域 然后释放鼠标左键返回 合并计算 对话框 然后单击 添加 按钮 添加 市场信息学 工 作表数据区域 用同样的方法添加其 他工作表数据区域 各科成绩都 合并计算到 成绩汇总 工 作表中了 二 按分类合并计算 当源区域中的数据没有相同的组织结构 但有相同的行标 签或列标签时 可采用按分类合并计算方式进行汇总 放置合并计算 结果的工作表 一月和二月各书店销售的图书种类不 尽相同 但它们有相同的列标签 按分类合并计算数据时 必须 包含行或列标签 如果分类标签在 顶端时 应选择 首行 复选框 如 果分类标签在最左列 则应选择 最 左列 复选框 也可以同时选择两个 复选框 这样Excel将会自动按指 定的标签进行汇总 任务实施 计算生产和销售情况表数据 下面将生产和销售情况表各表格中的数据合并计算 放置合并计算结 果的表格 要进行合并计