Excel数据管理

1 Excel 的数据分析功能的数据分析功能 课时 课时 6 课时 课程目标 课程目标 理解数据分析的必要 掌握通过排序 筛选和分类汇总进行数据分析的操作方 法 课程内容 课程内容 1 排序 a 简单排序 b 多个关键字排序 c 自定义排序 2 筛选 a 记录单查找 修改 添加 删除 b 自动筛选 i 前 10 ii 自定义 c 高级筛选 3 分类汇总 课程重点 课程重点 简单排序和复杂排序 前 10 筛选 自定义筛选 分类汇总 课程难点 课程难点 自定义排序高级筛选 分类汇总 课前准备 课前准备 PPT 随堂作业 示例工作薄 教学过程 排序 排序 2 课时 课时 引入 上体育课时 老师要求同学们按身高进行排队 同学们会怎么做 相互比较身高 然后确定自己的位置 同学们先比较身高再排队 结果我们得到的队伍就是一支从高到低或者从低到高的整 齐队伍 同样 在我们的 Excel 中 如果我们有一系列的数据 我们希望这些数据很整齐 很 规律 就需要我们按一定的要求进行重新安排序列 这个重新排列在我们的 Excel 中就叫 做 排序排序 演示演示 学生成绩表 学生成绩表的表头如下 学号姓名班级数学语文英语政治总分 学生成绩表中包含了各班级学生四门学科的考试成绩和总分 如果我们想知道谁的总 分最高 谁又是第二名 以及谁又是第三名 我们应该通过什么方法最快捷地找到答案 一 排序 排序排序是指按照一定的顺序重新排列工作表中的数据 通过排序 可以根据某特定列的 内容来重新排列工作表中的行 1 简单排序 a 操作方法 确定要进行排序的列 用鼠标单击这列中的任一单元格单击这列中的任一单元格 再单击工具栏上的 升升 序排序序排序 或 降序排序降序排序 按钮 Excel 自动按要求重排工作表数据顺序 b Excel 的排序原则 2 升序升序 数字越来越大 字母越来越后 降序降序 数字越来越小 字母越来越前 i 数字 从最小的负数到最大的正数 ii 字母 A Z iii 文本 按拼音或笔画进行排序 iv 空格排最后 小测试 下列题目要求中 哪些项是需要使用升序排序的 按学号从 1 号到 30 号排列 按体重从由重到轻排列 按班级人数递增排列 按销售量从高到低排列 在 Excel 中 如果我们要通过常用工具栏上的 升序排序 和 降序排序 进行简单排序 第一步我们应该怎么做 选中要排序的列中的全部内容 在工作表中随便点击鼠标 在工作表中有数据的单元格中随便点击鼠标 在要进行排序的列里面随便点击鼠标 演示演示 1 在 学生成绩表 中按总分进行降序排序 2 在 学生成绩表 中按学号进行升序排序 2 多个关键字排序 在简单排序的时候 我们不难发现 如果在排序字段里出现相同的内容 它们会被保 持着它们的原始次序 观察和思考观察和思考 对 班级班级 列进行排序 观察相同班级下的学生排列是否有顺序 如果我们希望工作表不但按班级进行排序 对相同班级下的总分还可以由高到低进行 排序 就是每个班级里再按总分进行排序 要如何操作 方法方法 在工作表中有数据的任一位置点击鼠标 打开 数据数据 排序排序 命令 弹出 排序排序 对话框如右图 在这个对话框中我们可以设置 主要关键主要关键 字字 次要关键字次要关键字 第三关键字第三关键字 等 主要关键字决定工作表中的最主要数据的 排序 在主要关键字相同的情况下 次要 关键字发挥作用 第三关键字以此类推 有标题行有标题行 工作表中有标题行且 的第一行为标题行 排序的时候自动 忽略第一行 对余下行进行排序 无标题行无标题行 工作表中无标题行 排序默认范围包含工作表的第一行 小测试 有标题行 与 无标题行 的工作表对比 演示演示 对 学生成绩表 进行复杂排序 先按班级进行升序排序 相同班级下 按学生的 成绩总分从高至低进行排序 3 自定义排序 如果我们需要对一堆数据按特殊的序列进行排序 这个顺序不是数字的从小到大 也不是字母的从 A 到 Z 而是根据实际情况涉及到的特殊顺序 如 上午 下午 3 晚上 要怎么进行排序呢 自定义排序的步骤步骤如下 a 自定义序列 选择 工具工具 菜单 点击 选项选项 打开 自定义序列自定义序列 选项卡 在自定义序列里 定义新的序列 如下图 通过输入确定添加新的序列 一个序列和另一个序列之间需要换行 b 按自定义序列排序 在工作表中有数据的任一位置点击鼠标 选择 数数 据据 排序排序 命令 在弹出的 排序排序 对话框中 定义主要关键字主要关键字 点选 有标题行有标题行 再点击左下 角的 选项选项 这时弹出 排序选项排序选项 对话框 如 右图 在 自定义排序次序自定义排序次序 的下拉框中选择我们 之前新添加的序列 如图右 点击确定 就能按自定义的序列对数据进行排序了 演示演示 按自定义排序的步骤对 功课表 中的功课按 上午 下午 晚上 的顺序进行排序 小测试 判断下列情况中 我们使用哪种排序方法更恰当 在 农夫山泉出厂日期表 中 按农夫山泉矿泉水的出厂日期进行排序 顺序由最近 出厂一直排到最早出厂 在 歌目表 中 按歌曲的专辑名进行排列 在相同的专辑名下 按歌曲的名称进行 排列 在 2009 中国电影票房排行榜 中 将电影按票房从高到低进行排序 在 学生信息表 中 将学生先按班级进行排序 班级从 高一 1 班 高一 2 班 一直排到 高三 2 班 在一个班级下的学生再按学生的学号由小到大进行排 序 在 图书目录 中 将图书先按图书大类进行排序 同一个大类下的图书再按图书名 称进行排序 筛选 筛选 2 课时 课时 4 二 筛选 在一张庞大的学生成绩表中 我们经常要查找某一个同学的所有成绩 或者语文成绩 的前 10 名同学姓名 甚至是查询数学成绩介于 70 60 分之间的学生的姓名 我们通过什么 功能实现这种效果 筛选筛选 在工作表中只显示满足给定条件的数据 而不满足的数据将被暂时隐藏起来不显示 1 记录单筛选 在学生成绩表中 查询某一个同学的成绩可以使用记录单进行筛选 a 操作方法 点击 数据数据 记录单记录单 命令 弹出 记录单记录单 对话框 如下面图 1 点击右 边的 条件条件 此时记录单成了一个可以输入条件进行查找的小工具 在左边的文 本框中输入条件 此时界面如下面图 2 再按回车键 我们就可以通过记录单查 看到要查找的内容 如下面图 3 图 1 图 2 图 3 演示演示 在学生成绩表中使用记录单查找学生成绩的操作步骤 b 记录单的其他功能 通过记录单我们还可以新建工作表数据条 修改数据以及删除数据 这些操作与 在工作表单元格中的操作效果是一样的 2 自动筛选 a 前 10 如果要查找到单科成绩的前 10 名学生 使用 自动筛选自动筛选 将使工作量大大降低 操作方法 点击 数据数据 菜单里 筛选筛选 下的 自动筛选自动筛选 这时 工作表中数据 的第一行自动被识别为行标题 在行标题的单元格右边均出现了下拉三角形 如 图 点击要进行筛选的列的列标题旁边的下拉框 从中选择 前 10 如图 5 点击 前前 10 个个 打开 自动筛选前 10 个 对话框 在这个对话框中进行设置 即可让 Excel 自动为我们生成这门功课的前 10 名学生姓名列表 如下图所示 演示演示 在 学生成绩表 中查找语文成绩前 10 名学生姓名 在 学生成绩表 中查找数字成绩后 20 学生姓名 b 自定义筛选 要找不及格的学生姓名 就用自定义筛选自定义筛选 操作方法 选择 数据数据 筛选筛选 自定义筛选自定义筛选 点击工作表中行标题单元 格右边的下拉框 选 择 自定义自定义 弹 出如图对话框 在这个对话框中填写 要筛选的自定义条件 最后点击 确定 工 作表就显示出所有被 筛选到的学生姓名 其他不符合条件的数 据将先被隐藏起来不 显示 演示演示 在 学生成绩表 中筛选出语文成绩不及格的学生 在 学生成绩表 中筛选出语文成绩和数学成绩都及格的学生 筛选条件总结 选项功能 全部不对当前字段作筛选 前 10 个 将打开 自动筛选前 10 个 对话框 用户可以选择项或百分比 显示数据清单 中上限或下限范围内的指定数量的数据行 自定义将打开 自定义自动筛选方式 对话框 用户可以使用当前字段中的两个条件 值 确切值Excel 将该字段的所有数据都列在选项中 用户选择某一值后 将显示出包含该 值的所有记录 6 空白当该字段中有空白单元格时 才有此选项 其作用是只显示该列中含有空白单元 格的数据行 非空白当该字段中有空白单元格时 才有此选项 其作用是只显示该列中含数据的数据 行 3 高级筛选 自定义筛选只支持设置两项条件 如果我们要筛选出没有不及格成绩的学生姓名 我们要怎么操作呢 高级筛选高级筛选是可以自己通过填写筛选条件 并支持筛选结果复制到新区域的筛选方法 在自动筛选不能完成任务的情况下 我们可以通过高级筛选进行复杂的筛选 高级筛选的操作步骤操作步骤如下 a 在工作表单元格中填写筛选条件 i 填写条件需要两行单元格 第一行填写需要定义筛选条件的字段名 第 二行填写各字段的条件 表达式 字段名和条件 表达式要列列相对应 ii 选择 数据数据 菜单中 筛选筛选 下级的 高级筛选高级筛选 打开 高级筛选高级筛选 如图 高级筛选中 首先我们可以设置筛选结果在 原有区域显示还是将筛选结果复制到其他位 置 然后我们可以定义 列表区域列表区域 即从 哪个区域里进行筛选 条件区域条件区域 即填写 条件的单元格区域所在位置 以及 复制到复制到 设置筛选结果复制到新的位置 设置好这 些内容 高级筛选的效果就在确定之后显示 出来了 演示演示 在 学生成绩表 中筛选出没有不及格科目的学生姓名 小测试 下列各种情况下 我们应该使用哪种筛选方式更为恰当 在 服装销售表 中筛选出总销售量排前 10 的销售店面 在 服装销售表 中查找其中一家销售店 希望可以查看这家店的销售量 在 服装销售表 中筛选出月销售量小于 300 的销售店面 分类汇总 分类汇总 2 课时 课时 三 分类汇总 如果我们想快速知道每个班级学生各科总成绩的平均值 如何操作 可以先根据班级进行排序 然后再逐个计算班级的平均分 现在我们有一个新的方法 不但一次性解决我们上面需要两步才能解决的问题 还有 很好的显示效果 这个方法叫分类汇总分类汇总 分类汇总分类汇总是按某一字段的内容进行分类 对每一类统计出相应的结果数据 在成绩表中 我们首先通过班级进行分类 分别分成 高二高二 1 班班 高二高二 2 班班 高二高二 3 班班 高二高二 4 班班 然后将每个班的里同学的 总分总分 计算平均值 操作方法 1 对要分类的字段进行排序 这使得学生能按班级分别集中排列 7 2 单击 数据数据 菜单下的 分类汇总分类汇总 弹出如下图 对话框 分类汇总对话框中的各项设置内容意义如下 分类字段 设置需要分类的字段名称 即我们需要按什 么字段进行分类 班级 或是性别 汇总方式 设置每一个分类下的汇总项通过什么方式进 行汇总 包括求和 求平均值 计数 最大数 最小数 和乘积 汇总项 设置每一个分类按哪一项值进行汇总 小测试 分类汇总第一步 我们应该做什么 点击工作表中有数据的任一单元格 选中需要进行分类汇总的单元格区域 对要进行分类的字段进行排序 不用做什么 直接进行分类汇总 语文成绩表 中的字段如下 姓名 性别 语文成绩 如果要分析一个班级里的所有男生的语文成绩和所有女生的语文成绩的平均分的高低所有男生的语文成绩和所有女生的语文成绩的平均分的高低 分类字段是什么 汇总方式是什么 汇总项是什么 运动会得奖表 中的字段如下 比赛项目 参赛者编号 参赛者姓名 参赛者所在班级 所 获奖牌 如果要分析一个学校运动会后高一段高一段 5 个班级的所有比赛项目奖牌总数个班级的所有比赛项目奖牌总数的高低 分类 字段是什么 汇总方式是什么 汇总项是什么 所以我们想知道每个班级学生各科成绩的总分平均值 分类字段是 班级班级 汇总