excel实训课件一.doc

《excel实训课件一.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、EXCEL实训课件一　学生成绩表的制作应用背景学期结束时，班主任王老师遇到了一个难题：教务处将各位任课教师给出的成绩表（每张成绩表都是一个单独的Excel工作簿，见素材）全部交给了他，要求他据此得到“各科成绩表”（图1）及“成绩统计表”（图2），便于对全班的各科成绩进行分析统计。图1图2起初，他将所有的成绩表工作簿（见素材）全部打开，企图把各科成绩粘贴到图1中，结果他发现粘贴后的单元格中却显示了出错信息，他手忙脚乱地忙活了半天，图1的要求都没有达到，就更别提完成图2的要求了。他只好硬着头皮一个一个去输入了，但这既

2、容易出错，工作量又很大。不得已他向教Excel的老师请教，经过老师的指点，他快速准确地完成了图1和图2两个工作表的制作，并顺利地对全班成绩进行了其他的分析统计工作，下面是他的解决方案。解决方案首先建立一个新的Excel工作簿，并打开所有的成绩表工作簿（见素材），将相应的工作表复制到新建工作簿中。并按图1所示的要求，将内容复制到一个空白工作表中，特别注意，在复制分数时，必须使用“选择性粘贴”或引用其他工作表相应单元格的数据，否则就会显示出错信息。至于得到图2的结果嘛，就必须使用各种函数（如：COUNT、MAX、MI

3、N、COUNTIF以及IF等）来解决问题了！制作过程一、制作“计算机应用”成绩登记表1．新建一个工作簿。将工作簿命名为“学号后2位+姓名.xls”2．在工作表Sheet1中，输入图3所示的内容。提示：学号的输入方式为输入数字前加上西文的’将视为文本，或将单元格的格式设置为“文本”图33．将各列的宽度调整到最适合的位置（见图3）。4．将“第一次测试”、“第二次测试”栏目的栏目名进行自动换行。5．将标题的字体设置为“黑体”，“18”号，粗体，并跨列居中。6．表头单元格区域设置：行高（30），水平、垂直居中，淡绿色底纹

4、。7．表格数据区域单元格设置：字号（10）；垂直居中；学号内容左对齐，其他数据内容水平居中。8．表格边框设置（见图3）。9．计算：平时、第一次测试、第二次测试的平均成绩，并将结果放置在“平均成绩”单元格中。10．“计算机应用”成绩公式为：平时*0.2+第一次测试*0.45+第二次测试*0.3511．通过减少小数位或ROUND函数，分别将“平均成绩”和“计算机应用”的结果以整数位呈现。12．将工作表“Sheet1”更名为“计算机应用”。二、生成“各科成绩表”1．参见样例“成绩表.xls”，分别打开“程序设计”、“大

5、学英语”、“企业管理”三个工作簿，并将同名的工作表复制到“学号后2位+姓名.xls”的工作簿中。2．将四个工作表的排列顺序调整为“计算机应用”、“程序设计”、“大学英语”、“企业管理”。3．选择工作表“Sheet2”，并将工作表更名为“各科成绩表”。4．将“程序设计”工作表中“学号”、“姓名”、“性别”列的数据复制到“各科成绩表”中的相应位置。5．用选择性粘贴的方法分别将工作表“计算机应用”、“程序设计”中的同名列粘贴到“各科成绩表”中；用引用单元格的方法将“大学英语”、“企业管理”中的同名列引用到“各科成绩表”

6、中的对应位置。（思考：你觉得这两种方法各有何特点？）6．分别计算每人的总分和名次（用RANK函数完成，注意相对引用与绝对引用的区别），并计算各科成绩的班级平均分（参见样例）要求四舍五入到小数点后两位。三、生成“成绩统计表”1.在“学号后2位+姓名.xls”的工作簿中，选择工作表“Sheet3”，并将工作表更名为“统计表”。参见样例“成绩表.xls”中的“统计表”。见图4。图42．利用COUNT函数计算四门课程参加考试的人数。提示：必须引用“各科成绩表”相应区域的数据，如：“=COUNT(各科成绩表!D2:D38)

7、”，下同。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:

8、D38,">=80")-B7；70-79分：=COUNTIF(各科成绩表!D2:D38,">=70")-COUNTIF(各科成绩表!D2:D38,">=80")或=COUNTIF(各科成绩表!D2:D38,">=70")-B7-B8。以此类推。6.分别计算四门课程中的及格率。完成以上实训的同学，还可以尝试使用FREQUENCY函数的方法完成统计各门课程中各分数段的人数。提