2022年excel实训课件一.docx

名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -EXCEL 实训课件一 应用背景同学成果表的制作学期终止时, 班主任王老师遇到了一个难题：教务处将各位任课老师给出的成果表（每张成果表都是一个单独的 Excel 工作簿,见素材）全部交给了他,要求他据此得到“ 各科成果表” （图 1）及“ 成果统计表”（图 2）,便于对全班的各科成果进行分析统计；图 1 图 2 起初, 他将全部的成果表工作簿（见素材） 全部打开, 妄想把各科成果粘贴到图 1 中,结果他发觉粘贴后的单元格中却显示了出错信息,他手忙脚乱地忙活了半天,图 1 的要求都没有达到,就更别提完成图2 的要求了；他只好硬着头皮一个一个去输入了,但这既容易出错,工作量又很大；不得已他向教 Excel 的老师请教,经过老师的指点,他快速精确地完成了图 1 和图 2 两个工作表的制作, 并顺当地对全班成果进行了其他的分析统计工作,下面是他的解决方案；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 1 页,共 9 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -解决方案第一建立一个新的Excel 工作簿,并打开全部的成果表工作簿（见素材）,将相应的工作表复制到新建工作簿中；并按图1 所示的要求,将内容复制到一个空白工作表中,特别留意,在复制分数时,必需使用“ 挑选性粘贴” 或引用其他工作表相应单元格的数据,否就就会显示出错信息；至于得到图2 的结果嘛,就必需使用各种函数（如：COUNT 、MAX 、MIN 、COUNTIF 以及 IF 等）来解决问题了！制作过程一、制作“ 运算机应用” 成果登记表1新建一个工作簿；将工作簿命名为“ 学号后 2 位+姓名 .xls”2在工作表 Sheet1 中,输入图 3 所示的内容；提示：学号的输入方式为输入数字前加上西文的将视为文本,或将单元格的格式设置为“ 文本”图 3 3将各列的宽度调整到最适合的位置（见图 3）；4将“ 第一次测试”、“ 其次次测试” 栏目的栏目名进行自动换行；5将标题的字体设置为“ 黑体”,“ 18” 号,粗体,并跨列居中；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 2 页,共 9 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -6表头单元格区域设置：行高（30）,水平、垂直居中,淡绿色底纹；7表格数据区域单元格设置：字号（10）；垂直居中；学号内容左对齐,其他数据内容水平居中；8表格边框设置（见图 3）；9运算：平常、第一次测试、其次次测试的平均成果,并将结果放置在“ 平均成果”单元格中；10“ 运算机应用” 成果公式为：平常*0.2+ 第一次测试 *0.45+ 其次次测试 *0.35 11 通过削减小数位或 ROUND 函数,分别将“ 平均成果” 和“ 运算机应用” 的结果以整数位出现；12将工作表“Sheet1” 更名为“ 运算机应用”；二、生成“ 各科成果表”1参见样例“ 成果表.xls ” ,分别打开“ 程序设计”、“ 高校英语”、“ 企业治理” 三个工作簿,并将同名的工作表复制到“ 学号后2 位+姓名 .xls” 的工作簿中；2将四个工作表的排列次序调整为“ 运算机应用”、“ 程序设计”、“ 高校英语”、“ 企业治理” ；3挑选工作表“Sheet2” ,并将工作表更名为“ 各科成果表”；4将“ 程序设计” 工作表中“ 学号”表” 中的相应位置；、“ 姓名” 、“ 性别” 列的数据复制到“ 各科成果5用挑选性粘贴的方法分别将工作表“ 运算机应用”、“ 程序设计” 中的同名列粘贴到“ 各科成果表” 中；用引用单元格的方法将“ 高校英语”、“ 企业治理” 中的同名列引用到“ 各科成果表” 中的对应位置；（摸索：你觉得这两种方法各有何特点？）6分别运算每人的总分和名次（用RANK 函数完成,留意相对引用与肯定引用的区别）,并运算各科成果的班级平均分（参见样例）要求四舍五入到小数点后两位；三、生成“ 成果统计表”1. 在“ 学号后2 位+姓名 .xls” 的工作簿中,挑选工作表“Sheet3” ,并将工作表更名 第 3 页,共 9 页 - - - - - - - - - 为“ 统计表”；参见样例“ 成果表.xls” 中的“ 统计表”；见图 4；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - -名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -图 4 2利用 COUNT 函数运算四门课程参与考试的人数；提示：必需引用“ 各科成果表”下同；相应区域的数据, 如：“ =COUNT 各科成果表 .D2:D38” ,3.分别利用 MAX 函数与 MIN 函数运算四门课程中的最高分数与最低分；4.将“ 各科成果表” 工作表中四门课程的班级平均分引用到平均分行中；提示：运算“ 运算机应用” 课程的平均成果：=各科成果表 .D39；5.用 COUNTIF 函数分别统计出各门课程中各分数段的人数；提示：90-100 分数段： =COUNTIF 各科成果表 .D2:D38,">=90" ；80-89 分 ： =COUNTIF 各 科 成 绩 表 .D2:D38,">=80"-COUNTIF 各 科 成 绩表.D2:D38,">=90" 或 =COUNTIF 各科成果表 .D2:D38,">=80"-B7 ；70-79 分 ： =COUNTIF 各 科 成 绩 表 .D2:D38,">=70"-COUNTIF 各 科 成 绩表.D2:D38,">=80" 或=COUNTIF 各科成果表 .D2:D38,">=70"-B7-B8 ；以此类推；6.分别运算四门课程中的及格率；完成以上实训的同学,仍可以尝试使用FREQUENCY函数的方法完成统计各门课程中各分数段的人数；提示：将“ 统计表” 复制一份,并清除 FREQUENCY 函数的用法：B2:E11 单元格的全部内容；先在一个空白区域（如：G7:G11）中分别输入如下分数间隔点“100、89、 79、69、 第 4 页,共 9 页 59” ,再选定目标单元格（如：B7:B11）,然后挑选FREQUENCY 函数；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -在 data_array区域中,选定“ 各科成果表” 工作表中的 的成果）；D2:D38 区域（“ 运算机应用”在 bins_array区域中,选定间隔点区域 G7:G11,单击“ 确定” 按钮；按 F2,再按 Ctrl+Shift+Enter ；表示公式是以数组公式的形式输入的；假如要运算其他课程各分数段的人数,应当如何使用四、制作“ 成果统计图”利用图表向导绘制成果统计图,见图 5,其中：图 5 1图表类型用：簇状柱形图；2数据系列产生在列；FREQUENCY函数？3图表标题：成果统计图；分类（X ）轴：等级；数值（Y）轴：人数；4显示数据表；5将图表作为新工作表插入,新工作表名称为：成果统计图；细心整理归纳 精选学习资料 6图表区域的填充成效用“ 麦浪滚滚”； 第 5 页,共 9 页 7绘图区格式的填充成效用纹理“ 白色大理石”8图例格式的填充成效用“ 蓝色面巾纸”； - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -五、依据“ 各科成果表” 制作“ 各科等级表”1.插入一个新的工作表,并将工作表命名为“ 各科等级表”；2.将“ 各科成果表” 中“ 学号”的相应位置；、“ 姓名” 、“ 性别” 列的数据复制到“ 各科等级表” 中3.利用 If 函数对“ 各科成果表” 工作表中“ 运算机应用” 成果在 60 分以上的设置为“ 及格” ,否就设置为“ 不及格”；4.利用 If 嵌套函数对 “ 各科成果表”工作表中其他三门课程进行如下设置：成果在 90分以上的设置为“A ” ；成果在 80-89 分之间的设置为“B” ；成果在 70-79 分之间的设置为“ C” ；成果在 60-69 分之间的设置为“D” ；成果小于 60 分的设置为“E” ；5.利用条件格式将“ 不及格” 或等级为“体” ；E” 的单元格设置成“ 黄色底纹红色加粗字将除标题行以外的偶数行数据区域设置为“ 灰色底纹紫罗兰色字体”；提示：利用公式 =MODROW,2=0 设置条件；六、排序1.将“ 运算机应用” 工作表中“ 运算机应用” 列的成果降序排列；2.将“ 企业治理” 工作表中的“ 企业治理” 列的成果升序排列；3.在“ 高校英语” 工作表中,以“ 性别” 为主要关键字（降序）；“ 高校英语” 为其次关键字（降序） ；“ 姓名” 为第三关键字（升序）排列、七、挑选1. 自动挑选第一把“ 各科成果表” 工作表复制一份,并将工作表改名为“ 自动挑选”；在“ 自动挑选” 工作表中,将同时满意以下三个条件：1）“ 性别” 为“ 女”2）姓“ 李” 或名字中最终一个字为“ 娜”；3）“ 高校英语” 的成果为90 分以上或不及格的记录挑选出来；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 6 页,共 9 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -2. 高级挑选把“ 各科成果表” 工作表再复制一份,并将工作表改名为“ 高级挑选”；在“ 高级挑选” 工作表中,将名次在前 5 名的女同学或名次在后 5 名的男同学的记录挑选出来；六、依据“ 成果统计表” 制作可挑选的“ 成果统计图”1.插入一个新的工作表,并将工作表命名为“ 挑选统计图”；2.在“ 统计表” 工作表中,复制A2:E12 区域的内容,切换到新建的“ 挑选统计图”工作表中,并选中 A2 单元格,执行“ 挑选性粘贴” 命令,在对话框中,选中“ 转置” 复选框及“ 值和数字格式” 单项按钮；3.执行“ 视图” “ 工具栏” “ 窗体” 命令,单击“ 窗体” 工具栏中的“ 组合框”按钮,在工作区的空白位置画出一个组合框,选中组合框,单击右键,执行“ 设置控件格式” 命令,在“ 数据源区域” 中挑选四名课程名称“ 运算机应用” “ 企业治理” 所在的单元格,在“ 单元格链接” 区域中,挑选 D9 单元格,在“ 下拉显示项数” 中输入“4” ；4.执行“ 插入” “ 名称” “ 定义” 命令,在引用位置处输入：=OFFSET 挑选统计图 .$E$2,挑选统计图 .$D$9,1,1,5 ,并为其命名为“Point” ；5.单击“ 常用”工具栏中的 “ 图表向导”按钮, 挑选“ 饼图”中的“ 分别型三维饼图”,单击“ 下一步” 按钮,切换到“ 系列” 选项卡,单击“ 添加” 按钮,在“ 值” 处,输入引用区域名称“=成果表 .xls.Point ” ,在“ 分类标志” 处,挑选分数段标题,如：“ =挑选统计图.$F$2:$J$2 ” ； 6.适当对图表进行各种美化处理；课后作业1新建一个工作簿,并将工作簿命名为“ 学号后2 位+姓名 +课后作业 .xls” ；2 打开“ 学号后2 位+姓名 .xls” 的工作簿,将工作表“ 各科成果表” 复制到“ 学号后 2 位+姓名 +课后作业 .xls” 中；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 7 页,共 9 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -3在“ 学号后 2 位+姓名 +课后作业 .xls” 中,插入一个新的工作表,并将工作表命名为“ 各科等级表”4依据“ 各科成果表” 制作“ 各科等级表”,其中全部四门课程的等级规定如下：成果在 90 分以上的设置为“ 优秀”；成果在 80-89 分之间的设置为“ 良好”；成果在70-79 分之间的设置为“ 中等”；成果在 60-69 分之间的设置为“ 及格”；成果小于 60 分的设置为“ 不及格”；结果如图 6 所示；图 6 5在“ 学号后 2 位+姓名 +课后作业 .xls” 中,插入一个新的工作表,并将工作表命名为“ 统计表”,在“ 统计表” 相应的单元格中输入图 7 所示的内容；图 7 6依据“ 各科等级表” 中的内容,用COUNTIF函数分别统计出各门课程中各等级的人数；7依据统计出的各等级人数,利用图表向导绘制“ 程序设计” 课程各等级人数统计图；其中：图表类型为“ 分别型三维饼图”；图表标题为： “ 程序设计” 各分数段人数统计； 第 8 页,共 9 页 图；图例位置在“ 底部”；数据标签包括“ 类别名称” 及“ 百分比”；将图表作为新工作表插入,新工作表名称为： “ 程序设计” 各等级人数统计图；图表区域的填充成效为“ 水滴”细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -摸索：能否用 FREQUENCY 函数完成各 等级 人数的统计；为什么？细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 9 页,共 9 页 - - - - - - - - -