分析学生成绩.doc

智用Excel高效分析学生成绩编码:0526刊登媒体:教育技术资讯刊登日期: 2006-05-08智用Excel高效分析学生成绩文/章杨琼学生成绩的统计分析是学校重要且枯燥烦琐的工作，市面上有许多相关的成绩管理系统，有学校不惜重金购买来提高处理成绩的效率。然而，此种成绩管理系统普遍存在以下两大缺陷：系统编程人员缺少教学工作方面的经验，而且各校对学生成绩的统计要求各不相同，设计出来的系统很难满足众家之需求；容易出现诸多问题，校方难以自我解决，只能联系专业人员前来救援，远水救近火，难免影响成绩统计的进度。俗话说得好：“自力更生，丰衣足食。”本人经过摸索，利用大众软件Excel设计了一个全校成绩自动统计的Excel模板，只需在“设置”工作表中初始化考试名称，在“成绩”工作表中导入各考生的班级、姓名、学号和各科成绩，即可在其他工作表中自动统计学校领导、班主任、任课老师所需要的学生成绩分析结果，非常方便。下面笔者将作具体介绍，以期抛砖引玉，与读者朋友一起学习和探讨。一、学生成绩录入成绩录入是统计分析的基础，但录入时常会遇到两个问题。1.有时多科成绩需同时录入，倘若各科分别录在不同的Excel文件中，之后的拼接工作将十分烦琐而且容易出错，若录在同一文件中则同时只能录入一科成绩，降低了效率。针对这个问题，本人通过共享Excel工作簿来解决。具体操作步骤如下：选择Excel软件“工具”菜单中的“共享工作簿”，在窗口中选择“允许多用户同时编辑”，这样就可以在多台电脑上同时在这个Excel文件中录入成绩了，而且互不影响。2.由于粗心，有时会输入一些让人哭笑不得的成绩，如8978分、七月九日等。为避免这类错误的出现，我们可以设置成绩录入区的数据有效性。只要在Excel菜单中选择“数据”“有效性”，设置有效性条件为0到100的整数即可，如果输入的成绩超出这个范围，系统就会报错。二、分析各科成绩成绩自动统计Excel模板包括六个工作表：设置、成绩、排名、平均分、总分分段、各科成绩分段。现对每个工作表的功能及设计制作过程介绍如下：1.“设置”工作表功能：（见图一）只要在此表的相应单元格中输入“学年学校高几期中（期末）考试”，那么各种统计分析表如各班各科平均分、分段统计等表的最上方都将统一出现此考试名称，再也不需要在其他工作表中逐个修改每次考试的名称。设置方法：根据提示在A2单元格中输入考试时间，在B2单元格中输入学校名，在C2单元格中输入统计的是高几的成绩，在D2单元格中输入考试类型（期中考、期末考），然后在A100单元格中输入“=A2&B2&C2&D2”，即可将A2:D2 单元格中的内容合并成一个字符串，在其他工作表中只需输入公式“=设置!A100”就可引用此考试名称。2.“成绩”工作表功能：（见图二）此工作表用来存放所有学生的班号、姓名、学号、各科成绩，是成绩统计分析基础。设置方法：参照图二导入学生成绩，但要注意如有学生缺考，务必将此生各科对应单元格的内容清空，以利于下面使用函数统计。3.“排名”工作表功能：此表可看出每位学生的总分以及在全年段的排名情况。设置方法：先用sum函数轻松算出各学生的各科总分，然后在名次列L的L3中=RANK(K3,K:K)（K列为总分列），即可算出第一位同学的总成绩在全年段中的排序，通过自动填充功能，可以计算出其他各学生的名次。此rank函数最大的好处是同分同学的名次也将相同，极好地避免了同分不同名的情况。4.“平均分”工作表功能：不管是班级名还是人数发生变化，工作表均可自动计算出各班实际考试人数、各班各科平均分、校总平均分和试卷难度。设置方法：为达到求各班各科平均分与班级名以及班级人数无关的目的，本人利用“某科平均分某科全班总分/某科全班实际考试人数”这个公式来完成。在求各班各科实际考试人数（需除去缺考）时，利用COUNTIF函数功能比较难满足设计要求。为解决这种多条件的计算功能，本人使用了数组公式进行多重条件计算。如要计算各班数学实际考试人数，可首先在B26中输入“=SUM(成绩!$A$1:成绩!$A$1500=A26)*(成绩!$D$1:成绩!$D$15000)”（注：“成绩”工作表的A列为学生所在班级名，D列为学生数学成绩，A26为班级名），再按下组合键SHIFTCTRL+ENTER（这是生成数组公式的关键环节），Excel会自动在公式两侧加上大括号，生成数组公式；然后用自动填充柄即可算出各科各班实际考试人数（此利用数组和函数结合方法进行多条件计算在此成绩统计的模板的其他工作表均普遍适用）；最后用“=SUM(B26:B38)”，求出全年级实考总人数。通过IF和SUMIF组合函数可算出各班各科平均值，在B3中输入IF(B26=0,0,SUMIF(成绩!$A:$A,= & $A3,成绩!D:D)/B26)，其中A3中为班级名，B26中为该班考数学的人数。其他依次自动填充。全年段平均分可用AVERAGE函数完成计算。各科试卷难度系统可用公式B19/100 计算，B19为数学全年段平均分，100为数学试卷总分。5.“总分分段统计”工作表功能：（见图三）不管班级名或人数的变化均可自动计算出各班中学生总分在各分数段的分布情况。设置方法：成绩的分段统计可谓是成绩分析中最复杂繁琐的一步，很多人士使用COUNTIF或FREQUECY函数来完成，但当班级人数改变或班级名改变时，将需要全部重新进行统计，这是很头疼的事情。本人使用数组公式结合的方法来统计分段情况，很好地解决了此问题。具体方法分三步：一是从A2开始输入班级名；二是从B2开始用数字代替班级，如高三2班用2代替，以便计算；三是利用数组和sum函数完成总分的分段统计，如要计算各班总分在600到650之间的可在相应单元格中输入“=SUM(成绩!$A$1:成绩!$A$1500=B4)*(成绩!$D$1:成绩!$D$1500599.9)*(成绩!$D$1:成绩!$D$1500=60) ”，然后同时按下SHIFTCTRL+ENTER键来完成计算。及格率可用公式“及格人数/实考人数”即=E4/D4计算出来。计算最高分最低分时如简单使用MAX或MIN函数又要涉及到班级名和班级人数变化的问题，其实照样可用数组公式法来解决。在G4单元格中输入=MAX(IF(成绩!$A$1:成绩!$A$1500=B4,成绩!$D$1:成绩!$D$1500) ，然后同时按下Shift+Alt+Enter键，就可算出B4单元格所指班的最高分，最低分可用=MIN(IF(成绩!$A$1:成绩!$A$1500=B4,成绩!$D$1:成绩!$D$1500)来完成计算各科成绩的分段统计，在O4单元格中输入“=SUM(成绩!$A$1:成绩!$A$1500=B4)*(成绩!$D$1:成绩!$D$150089.9)*(成绩!$D$1:成绩!$D$150099.9)”，即可统计出某班数学成绩在90到100分之间的学生人数。三、学生成绩统计模板的密码保护为了维护成绩的安全性，防止非法用户擅自有意或无意更改成绩数据，加强学生成绩统计系统的密码功能便成为了实际需要。本人利用VBA接口，加强了学生成绩统计系统的打开要求。通过“工具”菜单宏Visual Basic编辑器打开VBA编辑重窗口，在“模块1”的代码窗口中，输入下面语句：Sub Workbook_Open()Dim Passwordinput, PasswordsetPasswordset=12345Passwordinput = InputBox(请输入合法密码：)If Passwordinput Passwordset Then MsgBox 密码错误，你是非法用户, vbOKOnly,