excel实训课件一.doc

EXCEL实训课件一　学生成绩表的制作 应用背景 学期结束时，班主任王老师遇到了一个难题：教务处将各位任课教师给出的成绩表（每张成绩表都是一个单独的Excel工作簿，见素材）全部交给了他，要求他据此得到“各科成绩表”（图1）及“成绩统计表”（图2），便于对全班的各科成绩进行分析统计。 图1 图2 起初，他将所有的成绩表工作簿（见素材）全部打开，企图把各科成绩粘贴到图1中，结果他发现粘贴后的单元格中却显示了出错信息，他手忙脚乱地忙活了半天，图1的要求都没有达到，就更别提完成图2的要求了。他只好硬着头皮一个一个去输入了，但这既容易出错，工作量又很大。不得已他向教Excel的老师请教，经过老师的指点，他快速准确地完成了图1和图2两个工作表的制作，并顺利地对全班成绩进行了其他的分析统计工作，下面是他的解决方案。 解决方案 首先建立一个新的Excel工作簿，并打开所有的成绩表工作簿（见素材），将相应的工作表复制到新建工作簿中。并按图1所示的要求，将内容复制到一个空白工作表中，特别注意，在复制分数时，必须使用“选择性粘贴”或引用其他工作表相应单元格的数据，否则就会显示出错信息。至于得到图2的结果嘛，就必须使用各种函数（如：COUNT、MAX、MIN、COUNTIF以及IF等）来解决问题了！ 制作过程 一、制作“计算机应用”成绩登记表 1．新建一个工作簿。将工作簿命名为“学号后2位+姓名.xls” 2．在工作表Sheet1中，输入图3所示的内容。 提示：学号的输入方式为输入数字前加上西文的 ’ 将视为文本，或将单元格的格式设置为“文本” 图3 3．将各列的宽度调整到最适合的位置（见图3）。 4．将“第一次测试”、“第二次测试”栏目的栏目名进行自动换行。 5．将标题的字体设置为“黑体”，“18”号，粗体，并跨列居中。 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”，并将工作表更名为“统计表”。参见样例“成绩表.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.分别计算四门课程中