《案例实践五Excel》PPT课件.ppt

电子报表处理软件Excel 案例描述 二 Excel数据计算 参考效果图 任务1 在各 成绩表 中计算 平时成绩 计算各位同学的总评 其计算规则为 平时成绩15 期中成绩15 期末成绩70 并按总评对其进行单科排名 总分和名次放入所对应的单元格 任务2 在每个课程成绩工作表中 计算课程各分数段分布人数及比例 平均分 标准方差 计算平均分和标准方差时 小数点后保留2位小数 任务3 参考效果图 从各课程成绩表中引用总评成绩 并填入 成绩汇总表 中各相应单元格 计算单科平均分 各科总分 任务4 参考效果图 在 成绩汇总表 中 按总分进行排名 并利用函数判断各科是否超过单科平均分 预备知识 Excel中的计算公式公式的使用运算符单元格引用 绝对引用 相对引用 混合引用 出错信息函数函数基本知识常用函数 SUM AVERAGE MAX MIN COUNT 选择性粘贴数据 任务完成 任务1步骤1 选择 成绩表 用AVERAGE公式求得平时成绩填入H列相应单元格 其余同学的平时成绩可利用填充柄来完成计算 选择H3单元格 将鼠标指针移动至该单元格右下角位置 出现黑色十字形时拖动至相应需要填充的最后一个单元格 步骤2 选中单元格K3 在编辑栏内输入 ROUND H3 15 I3 15 J3 70 2 按 回车 键确认 求得总评成绩 四舍五入 保留整数 用填充柄向下填充 完成所有学生的总评成绩输入 步骤3 选中单元格L3 输入 RANK K3 K 3 K 32 0 回车确认 求得的排名依次填入相应单元格 任务完成 任务2步骤1 在每个课程成绩工作表中 在学生成绩列表下方建立各成绩段分布人数及比例平均分和标准方差的相应单元格 步骤2 利用SUMPRODUCT函数计算各个分数段分布人数 比如 SUMPRODUCT K3 K32 90 K3 K32 100 可以计算90 100分段的人数 同时计算该分数段的人数分布比例 步骤3 选定K3 K32 用AVERAGE公式求得该课程的平均分 步骤3 选定K3 K32 用STDEV公式求得该课程的标准方差 任务完成 任务3步骤1 选择 成绩汇总表 用VLOOKUP公式从各课程成绩表中引用总评成绩并填入相应单元格 比如 在编辑栏中输入 VLOOKUP A3 高等数学成绩 A 3 K 32 11 0 回车确认 再利用填充柄完成其余同学的总评成绩 步骤2 重复步骤1 完成其他四门课程总评成绩的输入 步骤3 选中单元格C33 单击编辑栏中的 插入函数 按钮 在弹出的 插入函数 对话框中选择 AVERAGE 函数 在打开的AVERAGE 函数参数 对话框中的第一个参数 Number1 文本框中输入C3 C32 也可以通过选择区域完成输入 单击 确定 按钮完成第一位同学高等数学平均分的计算 步骤4 其余同学可利用填充柄来完成计算 步骤5 总分的计算可参考步骤3和步骤4 将AVERAGE函数换成SUM函数 并修改相应的数据区域C3 G3即可 任务完成 任务4步骤1 选中单元格K3 在编辑栏内输入 RANK J3 J 3 J 32 0 按 回车 键确认 求得按总分的排名 用填充柄向下填充 完成所有学生的排名 步骤2 选中单元格L3 在编辑栏内输入 IF AND E3 E 33 F3 F 33 G3 G 33 H3 H 33 I3 I 33 是 否 IF函数是判断条件 第一个参数 条件为真返回 是 为假返回 否 以此求得单科是否超过平均分 用填充柄向下填充 完成对所有同学的判断 案例描述 三 工作表格式化任务1 设置 学生信息 表标题格式为隶书 22号字 并要求合并及居中 任务2 将 成绩汇总表 中的 单科平均分 保留两位小数 并将各列以 最合适列宽 显示 任务3 将 成绩汇总表 中各科成绩低于60分的学生成绩用红色的字体 黄色的底纹显示 任务4 为 学生信息 表中数据所在区域加田字框 粗线外框 细线内框 预备知识 工作表的格式化数字的格式化字体的设置设置列宽与行高数据对齐方式设置设置工作表的边框与底纹自动套用格式设置条件格式的设置 任务完成 任务1步骤1 选定 学生信息 表 步骤2 选定A1 G1单元格区域 利用 格式 菜单下的 单元格 命令 或直接右击选中的单元格区域 在弹出的快捷菜单中选择 设置单元格格式 弹出 单元格格式 对话框 步骤3 在 单元格格式 对话框中 单击 字体 选项卡 设置相应的字体为隶书 字号为22 步骤4 单击 对齐 选项卡 水平对齐方式选择 居中 并选中其中的 合并单元格 复选框 最后单击 确定 按钮 任务完成 任务2步骤1 选定 成绩汇总表 步骤2 选定C33 G33单元格区域 单击 格式 菜单下的 单元格 命令 或直接右击选中的单元格区域 在弹出的快捷菜单中选择 设置单元格格式 弹出 单元格格式 对话框 步骤3 在 单元格格式 对话框中 单击 数字 选项卡 在 分类 列表框中选中 数值 设置小数位数为2 单击 确定 按钮 步骤4 选定整张 成绩汇总表 中的数据区域 单击 格式 菜单下的 列 最合适的列宽 命令 即可完成最合适的列宽的设置 任务完成 任务3步骤1 选定 成绩汇总表 步骤2 选定C3 G32单元格区域 单击 格式 菜单下的 条件格式 命令 弹出 条件格式对话框 步骤3 在 条件格式 对话框中的 条件1 栏中的3个组合框中分别选择或输入 单元格数值 小于 60 步骤4 单击 条件格式 对话框中的 格式 按钮 在弹出的 单元格格式 对话框的 字体 选项卡中选择红色字体 在 图案 选项卡中选择黄色底纹 最后单击 确定 按钮 任务完成 任务4步骤1 选定 学生信息 表 步骤2 选定数据区域A1 G32 单击 格式 菜单下的 单元格 命令 或直接右击 设置单元格格式 命令 弹出 单元格格式 对话框 步骤3 在 单元格格式 对话框中选择 边框 选项卡 在该选项卡中先选择粗线的线条样式 再单击 预置 栏内的 外边框 按钮 同样再选择细线的线条样式 单击 预置 栏内的 内部 按钮 最后单击 确定 按钮 案例描述 四 数据库管理与分析任务1 将 成绩汇总表 中的 各门课程成绩 和 总分 列数据复制至 成绩表备份 中对应区域 在 姓名 列后插入一列 专业 并从 学生信息 表中复制相关信息 填入相应单元格 任务2 将 成绩表备份 表中的数据按总分降序排列 若总分相同的 则按 大学英语 降序排列 任务3 查看 成绩备份表 中专业是 软件工程 且总分大于400分同学的相关信息 任务4 查看 成绩备份表 中不及格的学生的相关信息 并将其复制到该表下方的相关区域 案例描述 四 数据库管理与分析 续 任务5 在 成绩备份表 中按专业对 计算机导论 课程进行平均分的分类汇总 任务6 在 成绩备份表 中创建一个图表 显示每个课程的各个专业平均分比较 显示在J3 R20区域 要求以 各专业课程平均分比较 为标题 以 专业平均值 作为图例项 图例位于图表右方 任务7 在 成绩汇总表 中 建立一张数据透视表 要求按专业比较 高等数学 课程男 女生的平均成绩 预备知识 数据库的概念数据库 字段 记录 数据清单创建数据清单的规则记录的排序记录的筛选记录的分类汇总 图表基本概念建立图表基本步骤数据点和数据系列坐标轴标志分类图例 任务完成 任务1步骤1 选中 成绩汇总表 选定数据区域C2 H32 并右击 在快捷菜单中选择 复制 命令 选择 成绩备份表 中的C2单元格 右击从弹出的快捷菜单中选择 选择性粘贴 命令 在弹出的 选择性粘贴 对话框中选定粘贴 数值 并单击 确定 按钮 步骤2 选择 成绩备份表 单击 姓名 列后一列的列标 本任务为C 选中整列 右键弹出快捷菜单 选择 插入 命令 在 姓名 列后就生成了一空列 在单元格C2输入字段名称 专业 再从 学生信息 表中将 专业 相关信息复制到产生的空列中 也可利用VLOOKUP函数 任务完成 任务2步骤1 选择 成绩表备份 数据表数据清单中的任意一个单元格 单击 数据 菜单下的 排序 命令 弹出 排序 对话框 步骤2 在弹出的 排序 对话框中选择主要关键字为 总分 选中 降序 次要关键字为 大学英语 并选中 降序 最后单击 确定 按钮 完成排序操作 任务完成 任务3步骤1 选择 成绩表备份 工作表数据清单所在区域中的任一单元格 单击 数据 菜单下的 筛选 自动筛选 命令 此时在每个列标题右侧出现一个向下的箭头 步骤2 在 专业 字段名右侧下拉列表框中选择 软件工程 此时系统将筛选出软件工程专业的所有记录 步骤3 在 总分 字段名右侧下拉列表框中选择 自定义 在弹出的 自定义自动筛选方式 对话框中输入相应条件 总分大于400 单击 确定 按钮后 数据清单中显示的信息即是任务中所需的信息 任务完成 任务4步骤1 在 成绩表备份 数据清单所在区域以外空白位置设置一个条件区域 本任务在D34 H39区域设置 步骤2 选中 成绩表备份 数据清单所在区域中的任一单元格 单击 数据 菜单下的 筛选 高级筛选 命令 弹出 高级筛选 对话框 步骤3 设置相关参数 列表区域 的选择默认 将数据源所在的区域全选 条件区域 在本任务中选择E34 I39区域 方式 设置为 将筛选结果复制到其他位置 并在 复制到 栏内输入用来显示符合条件的信息的区域 可只输入该区域的首个单元格 本任务中选择A40 最后单击 确定 按钮 任务完成 任务5步骤1 选中 成绩表备份 工作表数据清单 单击 专业 字段所在列数据区域任一单元格 利用常用工具栏中的 升序排列 按钮或 降序排列 按钮将其进行排序 步骤2 将活动单元格定位于 成绩表备份 数据区域任一位置 单击 数据 菜单下的 分类汇总 命令 弹出 分类汇总 对话框 步骤3 在弹出的 分类汇总 对话框中设置 分类字段 为 专业 汇总方式 为平均值 在 选定汇总项 中选中 计算机导论 其他选项设置为默认 最后单击 确定 按钮 任务完成 任务6步骤1 选定 成绩备份表 步骤2 参照任务5 按专业对五门课程进行平均分的分类汇总 步骤3 选中 成绩汇总表 数据区域中任何一个单元格 单击 插入 菜单下的 图表 命令 或者直接单击常用工具栏上的 图表向导 按钮 弹出 图表向导 4步骤之1 图表类型 对话框 此处Excel提供了多种不同的图表类型 我们选择图表类型为 柱形图 子图表类型为 簇状柱形图 并单击 下一步 按钮 步骤4 在弹出的 图表向导 4步骤之2 图表源数据 对话框中 用户可单击此处的 数据区域 右侧的 压缩对话框 按钮来选择数据区域 本任务选择C2 H2 C6 H6 C11 H11 C16 H16 C23 H23 C32 H32 C38 H38 按实际情况填写 区域 用户还可以选择系列产生在行或是列 此处选择 行 单击 下一步 按钮 任务完成 任务6 续 步骤5 在弹出的图表向导 4步骤之3 图表选项 对话框中 选择标题选项卡 设置图表标题为 各专业课程平均分比较 再选择 图例 选项卡 选择图例位于右方 单击 下一步 步骤6 在弹出的图表向导 4步骤之4 图表位置 对话框中 可以确定图表的位置 用户可把图表放在一张新的工作表中插入到工作簿文件中 称为图表工作表 也可让图表与原始数据放在一起 称为嵌入式图表 在本任务中选择默认 嵌入式图表 单击 完成 按钮 步骤7 改变步骤5中插入图表的大小和位置 将其放置到J3 R20区域 任务完成 任务7步骤1 选定 成绩汇总表 插入 专业 和 性别 列 步骤2 选中 成绩汇总表 数据清单中任一单元格 单击 数据 菜单 选择 数据透视表和数据透视图 命令 打开 数据透视表和数据透视图向导 3步骤之1 对话框 在该对话框中选中数据源类型为 MicrosoftOfficeExcel数据列表或数据库 在所创建的报表类型中选中 数据透视表 单击 下一步 按钮 步骤3 在弹出的 数据透视表和数据透视图向导 3步骤之2 对话框中 选中要建立数据透视表的数据源区域 本任务中选择A2 J32区域 然后单击 下一步 按钮 任务完成 任务7 续 步骤4 在弹出的 数据透视表和数据透视图向导 3步骤之3 对话框中 确定透视表的显示位置 本任务中选择在现有工作表中显示数据透视表 并可设置布局 步骤5 若步骤3中单击了 布局 按钮 弹出 数据透视表和数据透视图向导 布局 对话框 在该对话框中 用户可将右边的字段名拖到左边的图表结构图上 构造数据透视表 本任务是将 专业 字段拖到行上 性别 字段拖到列上 高等数学 字段拖到数据区域中 并双击数据区域字段选择汇总方式为平均值 单击 确定 按钮回到 数据透视表和数据透视图向导 3步骤之3 对话框 最后单击 完成 按钮 案例描述 五 工作表的打印任务1 为 学生信息 表添加页脚 中间位置内容为总页数和页码 靠右位置内容为当前的制作日期 任务2 设置 学生信息 表上 下页边距各3厘米 并水平居中 任务3 对以上设置进行打印预览 满意后打印输出 预备知识 页面设置页面页边距页眉 页脚工作表打印区域打印输出 任务完成 任务1步骤1 选定 学生信息 表 单击 文件 菜单下的 页面设置 对话框 单击 页眉 页脚 选项卡 本任务中单击 自定义页脚 按钮来自定义页脚 步骤2 在弹出的 页脚 对话框的中部位置输入文本 总页数 并单击相应图标按钮 再输入文本 当前页 并单击相应图标按钮 在靠右位置输入文本 制作日期 并单击相应图标按钮 最后单击 确定 按钮回到 页面设置 对话框并单击 确定 按钮 任务完成 任务2步骤1 选定 学生信息 表 单击