如何运用电子表格模板快速统计学生成绩.doc

如何运用电子表格模板快速统计学生成绩摘 要：本文阐述了用Excel制作一个能够自动统计成绩的电子表格模板的具体方法和步骤，只要在“空白”模板中填上学生成绩，各种统计数据（均分、及格率、优分率、分数段等）就立刻显现出来。关键词：学校 电子表格 成绩统计每次考试结束后，面对诸多的统计项目，许多教师开始在计算机中使用Excel软件进行统计。可是里面涉及的函数公式让很多教师头疼，而且稍不注意就容易出错。笔者结合多年的工作实践，用Excel制作了一个能够自动统计成绩的电子表格模板提供给教师。只要在“空白”模板中填上学生成绩，各统计数据就立刻显现出来，能够让教师方便快捷、准确无误进行统计，轻松完成繁重的工作，班级的教学质量数据一目了然，彻底告别那些眼花缭乱的繁杂统计工作，提高了工作效率。一、登分图一如图一，我们把第一个工作表命名为“成绩册”，在A1M1单元格中分别输入姓名，班级，考号，考室，语文，数学，总分，名次。阅卷结束后，完成登分工作。1、计算学生的总成绩在L2单元格输入“=SUM(E2:K2)”后按回车键或单击编辑栏中的输入按钮“”确认。如果要计算每个学生的总成绩，可用鼠标对准L2单元格右下角的填充柄，向下拖动至L320单元格，L2单元格的公式即被复制到了L3 ：L320单元格区域。2、按总分排名在单元格M2输入“=RANK(L2,$L$1:$L$320)” 后按回车键或单击编辑栏中的输入按钮“”确认。用鼠标对准M2单元格右下角的填充柄，向下拖动至M320单元格，M2单元格的公式即被复制到了M3 ：M320单元格区域。二、使用函数 FREQUENCY统计各分数段的人数图二如图二，使用函数 FREQUENCY 统计各分数段的人数。由于函数 FREQUENCY 返回一个数组，所以它必须以数组公式的形式输入。所有数组公式编辑完公式后，必须按 Ctrl+Shift+Enter 以锁定公式。1、设置区间及分割点（如图二所示Q2:R21区域）2、在S2单元格输入“=FREQUENCY(L$1:L$320,$Q$2:$Q$17)” 用鼠标对准S2单元格右下角的填充柄，向下拖动至S17单元格，S2单元格的公式即被复制到了S3 ：S17单元格区域。按 Ctrl+Shift+Enter结束。3、统计总分最高分在S18单元格输入“=MAX($L:$L)” 后按回车键或单击编辑栏中的输入按钮“”确认。4、统计总分最低分在S19单元格输入“=MIN(IF(L2:L3200,L2:L320)” 后按回车键或单击编辑栏中的输入按钮“”确认。5、统计应考人数在S20单元格输入“=COUNT($L:$L)” 后按回车键或单击编辑栏中的输入按钮“”确认。6、统计实考人数在S21单元格输入“=SUMPRODUCT($L$2:$L$3200)*($L$2:$L$320=1)” 后按回车键或单击编辑栏中的输入按钮“”确认。三、偏科分析图三如图三，我们把第二个工作表命名为“偏科分析”， 在A1单元格输入“=成绩册!A1” 用鼠标对准A1单元格右下角的填充柄，向下拖动至M320单元格，工作表“成绩册”中的学生成绩即被复制到了A1 ：M320单元格区域。删除考号，考室这两列的内容，分别在语文、数学、英语、政治、历史、物理、化学后插入一列，在单元格D1、F1、H1、J1、L1、N1、P1中输入“名次”，在单元格D2输入“=RANK(C2,$C$2:$C$320)” 。用鼠标对准D2单元格右下角的填充柄，向下拖动至D320单元格，D2单元格的公式即被复制到了D3 ：D320单元格区域。通过语文这一科的名次和总分的排名对比，可以看出哪些同学的语文是否偏科。如901班姚越同学的语文排名全校103名，而他的总分在全校排名并列12名，说明他的语文偏科，希望能引起该班的语文教师和姚越同学的重视。其余各科的偏科分析和语文学科的操作类似。四、填写几个报表1、表（一）制作好如图四所示的表头后，在B6单元格输入“=成绩册!S3”，在B7单元格输入“=B6”，在C6单元格输入“=成绩册!S4”，在C7单元格输入“=B7+C6”，其余类推。表四图五2、表（二）统计全校各科均分、及格、优分、最高分、最低分，以语文学科为例：制作好如图五所示的表头后，统计均分，在A6单元格输入“=AVERAGE(成绩册!$E2:$E320)”，统计及格人数，在B6单元格输入“=COUNTIF(成绩册!$E:$E,=72)”，统计及格率，在C6单元格输入“=COUNTIF(成绩册!$E2:$E320,=72)/COUNTA(成绩册!$E2:$E320)*100”，统计优分人数，在D6单元格输入“=COUNTIF(成绩册!$E:$E,=96)”， 统计优分率，在E6单元格输入“=COUNTIF(成绩册!$E2:$E320,=96)/COUNTA(成绩册!$E2:$E320)*100”，统计最高分，在F6单元格输入“=MAX(成绩册!$E:$E)”，统计最低分，在G6单元格输入“=MIN(成绩册!$E2:$E320)”，其余各科类推。图六2、表（三）统计各班各科均分、及格、优分、最高分、最低分，以901班语文学科为例：制作好如图六所示的表头后， 统计应考人数，在B4单元格输入“=COUNTIF(成绩册!$B:$B,=901)”， 统计实考人数，在C4单元格输入“=SUMPRODUCT(成绩册!$B$2:成绩册!$B$320=901)*(成绩册!$E$2:$E$320=1)”， 统计均分，在E4单元格输入“=SUMIF(成绩册!$B$2:成绩册!$B$320,=901,成绩册!$E$2:成绩册!$E$320)/表（三）!C4”， 统计及格人数，在F4单元格输入“=SUMPRODUCT(成绩册!$B$2:成绩册!$B$320=901)*(成绩册!$E$2:成绩册!$E$320=72)”， 统计及格率，在G4单元格输入“=F4/C4*100”， 统计优分人数，在H4单元格输入“=SUMPRODUCT(成绩册!$B$2:成绩册!$B$320=901)*(成绩册!$E$2:成绩册!$E$320=96)”， 统计优分率，在I4单元格输入“=H4/C4*100”， 统计最高分，在J4单元格输入“=MAX(IF(成绩册!$B$2:成绩册!$B$320=901,成绩册!$E$2:成绩册!$E$320)”， 统计最低分，在K4单元格输入“=MIN(IF(成绩册!$B$2:成绩册!$B$320=901,成绩册!$E$2:成绩册!$E$320)”，其余各科类推。3、表（四）各班前100名分布情况制作好如图七所示的表头后，在B4单元格输入“=SUMPRODUCT(成绩册!$B$2:成绩册!$B$11=901)*(成绩册!$L$2:$L$11=1)”，在C4单元格输入“=SUMPRODUCT(成绩册!$B$12:成绩册!$B$21=901)*(成绩册!$L$12:$L$21=1)”，其余类推。图七五、制作模板完成上述过程之后，在“文件”菜单中选择“另存为”命令，出现“另存为”对话框。在“保存类型”列表框中选择“模板”，在“保存位置”出现Templates文件夹，单击“保存”按钮。（如图八）图八六、运用电子表格模板统计学生成绩 图九启动Excel，选择“文件”菜单中的“新建”命令，在工作簿窗口的右侧弹出“新建工作簿”任务窗格。在任务窗格中，单击“模板”选项区中的“本机上的模板”，打开“模板”对话框。在该对话框中，单击“电子方案表格”选项卡，在下面的“文件”列表框中列出了已有的工作簿模板。（如图九）单击“2010年秋季九年级第一次月考”模板，单击“确定”按钮，系统将打开选中的模板文件。在成绩册中输入学生的各科成绩，单击“保存”即可。这时由于学生人数发生变化，表（三）可能出现图十的现象：图十图十一选中C4：BG9区域，选择“编辑”菜单下的 “查找和替换”功能，将“成绩册!$B$2:成绩册!#REF!”替换为“成绩册!$B$2:成绩册! $B$315”，单击“全部替换”；再将“成绩册!$E$2:成绩册!#REF!”替换为“成绩册!$E$2:成绩册