EXCEL实训1--制作成绩表

EXCEL 实训课件一实训课件一 制作成绩表制作成绩表 请采用 文档结构图 的方式浏览课件 视图 文档结构 图 课前准备课前准备课前准备 1 在 D 盘新建个人文件夹学号最后 2 位 姓名 如 10 张三 2 进入计算机基础教学网 作业提交 资料下载作业提交 资料下载 登录登录 将操作素材 第 6 章素材 下载到 D 盘的个人文件夹中 并解压缩 实训目标实训目标实训目标 按照样文 成绩表 样例 xls 制作一份 计算机应用 课程学生成绩登记表 图 1 并据此和三个素材文件的数据生成 各科成绩表 图 2 并利用 自动筛选 查找 出满足条件的记录 图 3 图 1 图 2 图 3 实现过程实现过程实现过程 任务一 制作 计算机应用 成绩登记表 实现目标 实现目标 在任务一中 我们要制作如图 4 所示的工作表 主要完成以下任务 1 掌握各种快速高效地在 Excel 中输入数据的方法 2 进行公式计算 3 进行单元格格式设置 4 工作表的重命名 图 4 通通 过过 公公 式式 计计 算算 得得 到到 1 准备工作 1 启动 Excel 2 单击 常用 工具栏中的 保存 按钮 在 另存为 对话框中将文件名由 Book1 xls 另存为 成绩表 学号后 2 位 姓名 xls 并保存在个人文件夹中 相关概念 a 工作簿 工作表 工作表标签 b 行 列 单元格 当前单元格格式 2 在工作表 Sheet1 中输入标题及表头数据 请参见 成绩表 样例 xls 中的 计算机应用 工作表 输入以下内容 1 建立表格标题 在工作表 Sheet1 中 选中单元格 A1 输入标题 计算机应用 课程学生成绩 登记表 2 输入表格的表头内容 分别在单元格 A2 B2 C2 D2 E2 F2 G2 中 输入 学号姓名性别 平时成绩作业设计期末考试总成绩 3 输入 学号 列数据 请根据以下提示 参见 P125 126 完成所有学号 04302101 04302137 的 输入 在单元格 A3 中输入 学号 数据 04302101 数据前面的数字 0 出现了吗 为什么呢 怎么让它出现呢 4 利用自定义填充序列 输入 姓名 列数据 选做 若无时间可用复制粘贴的方法实现 请参见 P126 127 完成姓名列的输入 5 输入 性别 列数据 选做 若无时间可用复制粘贴的方法实现 请参见 成绩表 样例 xls 中的 计算机应用 工作表 并参见 P127 完成 性 别 列数据的输入 1 利用 填充柄 将 性别 列的内容全部填充为 女 2 选择第一个应该修改为 男 的单元格 在按住 Ctrl 键的同时 分别单击其他单 元格 3 在被选中的最后一个单元格中输入 男 4 同时按住 Ctrl Enter 键 如图 6 所示 图 6 相关概念 a 填充柄 位于选定区域右下角的小黑方块 将用鼠标指向填充柄 时 鼠标的指针更改为黑十字 b 复制 粘贴 c 自定义填充序列 6 输入 平时成绩 作业设计 期末考试 列的数据 切换到 Word 文档 计算机应用 素材 doc 文件 选择 3 列分数 将分数复制到相 应单元格处 7 单元格计算 请参见 P129 请参见 P129 完成 总成绩 列数据的计算 要求如下 计算所有学生的 总成绩 其中 总成绩 平时成绩 20 作业设计 30 期末考试 50 注意注意 1 Excel 中的 公式 是指在单元格中执行计算功能的等式 公式可以在单元格中直接输 入 也可以在编辑栏中输入 Excel 中的所有公式都必须以 号开头 若无等号 Excel 将 其理解为正文 所以 是公式中最不可缺少的一个运算符 后面是参与计算的运算数 和运算符 每一个运算数可以是常量 单元格或区域引用 单元格名称或函数等 2 算术运算符中的乘号和除号与数学中使用的符号不同 乘号要用 除号要用 乘方用 这是初学者经常容易忽视的问题 3 在 Excel 的公式中 最好包含单元格引用 如 D3 0 2 E3 0 3 F3 0 5 而不要直 接使用数值 如 90 0 2 88 0 3 95 0 5 相关概念 公式的复制 可以通过使用填充柄 将公式复制到相邻的单元格中 选择包含 公式的单元格 再拖动填充柄 使之覆盖要填充的区域 8 单元格格式设置 请参见 P130 P134 完成 单元格格式设置 要求如下 1 将标题字体设置为 幼圆 加粗 18 号 并使标题在 A1 G1 列合并及居中 2 将数据区域 不包括列标题 所有单元格的字号设置为 10 垂直对齐方式和水平对 齐方式都设置为 居中 3 将表格的外边框设置为双细线 内边框设置为单细线 4 为表格列标题区域添加浅绿色底纹 并设置垂直对齐方式和水平对齐方式为 居中 5 将表格列标题行的行高设置为 30 00 40 像素 将 平时成绩 列的列宽适当调窄 6 将列标题 平时成绩 作业设计 期末考试 在单元格内分两行显示 7 将平时成绩 作业设计 期末考试三列的列宽调整为最适合的列宽 8 通过减少小数位数 将 总成绩 的结果以整数位呈现 减少小数位数按钮和增 加小数位数按钮 相关概念 单元格格式的设置 9 工作表的更名 请参见 P134 完成对当前工作表的更名 要求如下 将当前工作表的名称 Sheet1 更名为 计算机应用 提示提示 方法一 右键单击工作表的标签 sheet1 方法二 双击工作表标签 sheet1 再输入 计算机应用 注意注意 请单击 常用 工具栏中的 保存 按钮 保存当前工作簿文件 要点回顾 1 如何在单元格中输入你自己的学号 2 如何快速填充全班的学号 如何快速填充数字序列 1 2 3 10 3 向单元格输入公式应注意什么 4 如何将标题在一行的多个单元格之间 合并及居中 它与 跨列居中 有何不同 5 调整列宽和行高有哪些方法 哪种方法最简便 6 如何使单元格的宽度根据内容自动调整 7 如何精确调整单元格的行高或列宽 任务二 由多张工作表数据生成 各科成绩表 实现目标 实现目标 在任务二中 我们要制作如图 7 所示的工作 表 主要完成以下任务 1 工作表的复制 移动 删除及插入 2 单元格数据的复制与粘贴 3 单元格的引用方法 图 7 1 工作表的复制及移动 请参见 P134 P135 完成对工作表的复制及移动操作 要求如下 1 分别打开 大学英语 素材 xls 工作簿 应用文写作 素材 xls 工作簿 高等数学 素材 xls 工作簿 2 将 大学英语 素材 xls 工作簿中的 Sheet1 工作表 复制到你自己的工作簿 如 18 张三 成绩表 xls 中的 计算机应用 工作表之前 并将复制后的目的工作表 Sheet1 更 名为 大学英语 2 分别将 应用文写作 素材 xls 工作簿中的 应用文写作 工作表 高等数学 素材 xls 工作簿中的 高等数学 工作表复制到你自己工作簿中的 Sheet2 工作表之前 3 在你自己的工作簿中 将前四个工作表的排列顺序调整为 大学英语 计算机应用 高等数学 应用文写作 说说明 明 1 在同一工作簿中复制或移动工作表 使用鼠标拖曳的方法比较方便 而在不同工 作簿中复制或移动工作表 则使用 移动或复制工作表 命令更可靠 2 在不同工作簿中移动或复制工作表时 应该首先打开相关的所有工作簿 2 工作表的删除与插入 请参见 P136 完成对工作表的删除与插入操作 要求如下 通通 过过 公公 式式 计计 算算 得得 到到 1 删除你自己工作簿中的工作表 Sheet2 Sheet3 2 在你自己工作簿中的工作表 大学英语 之前插入一张新的工作表 并将新工作表更 名为 各科成绩表 3 单元格数据的复制与粘贴 请参见 成绩表 样例 xls 中的 各科成绩表 工作表 并参考教材 P136 完 成对单元格数据的复制与粘贴操作 要求如下 1 将你自己工作簿中 计算机应用 工作表的 学号 姓名 性别 的数据复制到 各 科成绩表 工作表中的 A1 C38 单元格中 试一试 在 计算机应用 工作表中 复制 总成绩 列数据到 各科成绩表 工作表中时 会出现 什么结果 2 利用选择性粘贴 将 计算机应用 工作表中的 总成绩 列的数据复制到 各科成绩表 的 D1 单元格中 并将 总成绩 更改为 计算机应用 再分别将 应用文写作 列数据 大学 英语 列数据 高等数学 列数据 复制到 各科成绩表 中的 E1 F1 以及 G1 的位置 结果如 图 8 所示 图 8 4 单元格数据的移动 请参见图 8 并参考教材 P139 完成对单元格数据的移动操作 要求如下 在 各科成绩表 工作表中 将各列成绩的排列顺序调整为 大学英语 计算机应用 高等数学 应用文写作 调整结果如图 7 所示 5 函数及单元格引用 请参见 成绩表 样例 xls 中的 各科成绩表 工作表 并参考教材 P139 P142 完成各种函数计算操作 要求如下 1 在 各科成绩表 工作表中 增加 总分 列 计算每位学生的总分 用 SUM 函数完成 各参数之间用逗号分隔 圆括号用于括起参数 用括起来 SUM number1 number2 函数名称 说说明 明 所有函数都包含三部分 函数名 参数和圆括号 以求和函数 SUM 来说明 SUM 是函数名称 从名称大略可知该函数的功能及用途是求和 圆括号用来括起参数 在函数中圆括号是不可以省略的 参数是函数在计算时所必须使用的数据 函数的参数不仅是数字类型 它还可以是字符型 逻辑值或是单元格引用 如 SUM B1 C3 SUM D2 G2 等 2 在 各科成绩表 工作表中 增加 名次 列 计算每位学生的总分排名 用 RANK 函 数完成 注意相对引用与绝对引用的区别 注意 注意 双击填充柄后 计算机出的名次对吗 有没有问题 为什么呢 3 在 各科成绩表 工作表中 修改每位学生的总分排名 思考 思考 你知道什么是 相对引用 绝对引用 和 混合引用 吗 说说明 明 相对引用 用 H2 这样的方式来引用单元格是相对引用 相对引用是指当公式在复制或移动时 公式中引用单元格的地址会随着移动的位置自动改变 绝对引用 在行号和列号前均加上 如 H 2 这样的方式来引用单元格是绝对引用 当公式 在复制或移动时 公式中引用单元格的地址不会随着公式的位置而改变 混合引用 混合引用是指单元格地址中既有相对引用 也有绝对引用 H2 表示具有绝对列和相对行 当公式在复制或移动时 保持列不变 而行变化 H 2 表示具有相对列和绝对行 当公式在复制或移动时 保持行不变 而列变化 4 在 各科成绩表 工作表中 计算出各门课程的 班级平均分 提示 提示 使用 AVERAGE 函数 5 在 各科成绩表 工作表中 计算出各门课程的 班级最高分 及 班级最低分 提示 提示 使用 MAX MIN 函数 6 在 各科成绩表 工作表中 将各门课程的 班级平均分 的结果四舍五入 保留 2 位 小数 提示 提示 使用 ROUND 和 AVERAGE 函数 相关概念 a 函数的使用 最大值 MAX 函数 最小值 MIN 函数 四舍五入 ROUND 函数 求和 Sum 函数 和平均值 Average 函数 b 相对引用 绝对引用混合引用 要点回顾 1 在不同工作薄中进行工作表的复制或移动操作要注意什么问题 2 使用鼠标移动及复制工作表有何区别 3 什么是选择性粘贴 它有什么用途 4 函数包括哪三部分 函数的参数之间用什么符号分隔 5 相对引用和绝对引用分别应用在什么场合 它们的表达方式有何区别 6 如何在相对引用 绝对引用和混合引用之间切换 问题辨析 在下图所示的单元格中 有以下数据 请分析以下四个函数表达式中各返回什么值 为什么 其中哪两个函数表达式返回的值一定相同 哪个函数表达式返回的的值没有意 义 请说明原因 1 MAX A1 A2 B1 B2 2 MAX A1 A2 B1 B2 3 MAX A1 B2 4 MAX A1 B2 任务三 成绩表的排序和筛选 实现目标 实现目标 在任务三中 我们要制作如图 9 所示的工作表 主要完成以 下任务 1 数据的排序 2 用 自动筛选 的方法筛选数据 3 用 高级筛选 的方法筛选数据 图 9 1 排序 请参见 P148 P149 完成对 大学英语 高等数学 工作表的操作 要求如 下 1 在 大学英语 工作表中 将 大学英语 列的成绩按升序排列 2 在 高等数学 工作表中 以 性别 为主要关键字降序排列 以 高等数学 为第二关键 字降序排列 以 姓名 为第三关键字升序排列 要点提示 要点提示 排序并不是针对某一列进行的 而是以某一列的大小为顺序 对所有的记 录进行排序 也就是说 无论怎么排序 每一条记录的内容都不容改变 改变的 只是它在数据清单中显示的位置 因此 以数据清单中的某一列为关键字排序时 只需单击该列中任一单元 格 而不用全选该列 否则 排序将只发生在选定列 其他列的数据将保持不变 这样做的结果可能会破坏原始工作表的数据结构 造成数据的错行 2 自动筛选 请参见 P149 P150 完成对 自动筛选 工作表的操作 要求如下 先将 各科成绩表 工作表复制一份 并将复制后的工作表改名为 自动筛选 在 自动筛 选 工作表中 筛选出同时满足以下四个条件 性别 为 女 姓 黄 或姓名中最后一个字为 静 计算机应用 的成绩在 80 分 90 分之间 名次 在前 8 名的数据记录 要点提示 要点提示 在数据清单中进行多次筛选 下一次筛选的对象是上一次筛选的结果 最 后的筛选结果受所有筛选条件的影响 它们之间的逻辑关系是 与 的关系 如果要取消对所有列的筛选 只要执行 数据 筛选 全部显示 命令 如果要撤消数据清单中的自动筛选箭头 并取消所有的自动筛选设置 只要