巧用EXCEL函数做成绩统计.doc

巧用EXCEL函数做成绩统计广东北江中学 邓才云作为一名中学教师，对于教学的东西，可谓是轻车熟路，可等到考试后，统计学生的成绩却是一件麻烦事，让学生的成绩表怎样表现出来，才简洁明了。时下有关成绩统计的软件也不少，要真正适合自己的不是很多。EXCEL中的函数较为多，基本能满足我们日常数据处理的需要，本人充分运用EXCEL中的函数做了一个成绩统计的小软件，自己用起来感觉还不错。下面将我做的过程向大家介绍一下，希望能对各位有所帮助。在EXCEL的工作表中，先做好如下的各列的标题栏：编号班别姓名政治语文数学外语物理总分班名次年级名次图（1）学生的编号、班别、姓名，可以从其它文件中复制过来，在用小键盘输入成绩的时候，按回车就转到下一个单元格，可有时按回车时，却转到相邻的单元格，要使它转到下一个单元格，只需选择菜单中工具选项，在弹出选项中单击编辑项，选中“按Enter键移动活动单元格标识框方向”，在下拉选项中选择“向下”如图(1)所示。当成绩输入完成后，下一步就是进行成绩统计工作，在统计成绩时，除了想知道年级排名和班级排名外，还要了解整个年级及各班、各学科和总分的最高分、最低分、平均分、标准差，及每个分数段的学生人数的分布情况等。图（3）图（2）第一步，计算每个学生总分、年级名次和班级名次。计算各学科总分用sum( )函数就可以了，这里就不用说了。计算年级排名用Rank( )函数，在K2单元格输入公式“=IF($I20,Rank($I2,$I$2:$I$417),0)”（注意公式中符号“$”），用if( )函数主要是考虑如果有学生缺考的话，则其年级名次为0。将鼠标移到单元格的右下角，使其变成“十”字，向下拖动复制，年级名次就做好了。如图(2)所示。班级名次的统计就复杂些了，我是运用函数调用数组进行统计的，其做法如下：在J2单元格中输入公式：“=IF($I20,SUM($I2$I$2:$I$417)*($B2=$B$2:$B$417)+1,0)”，意思是统计该学生在其所在的班中有多少人的总分比他高，其结果再加上1就是该学生在班里的班名次，如果该学生缺考，总分则为0，班名次也为0，公式中的“”不是直接输入的，而是输入公式后按Ctrl+Shift+Enter键可以了。将公式向下拖动复制，班名次就做好了。效果如图（3）所示。图（4）第二步，统计各学科最高分、最低分、平均分、标准差。对整个年级来说，还是较为容易的，分别函数Max( )、Min( )、Average( ) 、Stdve( ) 直接引用单元格就可以了。如统计年级各学科及总分的最高分，先在单元格D418中，输入公式“=Max(D$2:D$417)”即可得到全级政治科的最高分，如图（4）。再将单元格向右拖动复制。这样，各科和总分的最高分统计出来了。其它几项照此方法用相应的函数就OK了。要分别按班统计上述各数据项，得花点心思一步一步来吧。先统计每个单科和总分的最高分，在学生成绩后面的空白中，如图（5）所示，在B列中输入班别，C列中输入“最高分”，在单元格D421中输入公式“=Max(D$2:D$417)*($B$2:$B$417=$A421)”，求得1班政治科的最高分，将单元格向下扡动复制，各班的政治科最高分就出来了，简单吧。其它还没出来呀，容易了，选取D421：D427，向右拖动复制到I列，每个班的单科成绩和总分的最高分就统计好了。图（6）图（5）要统计每个班各学科和总分的最低分，各位可能要说，不就是按统计各班最高分的方法把函数Max( )改成Min( )不就行了。我开始就是这样做的，但是却发现得到的结果都是0，究其原因是如果某位学生缺考，则在引用的单元格中出现了空行，为此在公式中增加一个判定条件，将公式改为“=MIN(IF($B$2:$B$417=$B428)*(D$2:D$4170),D$2:D$417,aa)”，问题就解决了。如图（6）所示。图（7）分别统计各个班单科和总分的平均分时，我用了一个最笨的办法，即用各个班某科成绩的总和除以其参考人数，如图（7）所示在单元D428中输入如下公式： =SUM(D$2:D$417)*($B$2:$B$417=$B428)/SUM(D$2:D$4170)*($B$2:$B$417=$B428)再通过扡拉复制，各个班各学科和总分的平均分统计完成。第三步，分别按班统计各科和总分在各分数段的人数，先统计各学科在各分数段的人数分布情况。先做好如图（8）所示的表格，在L452：L457中依次输入0.2、60、70、80、90、101，0.2表示缺考和0分不参与统计，各分数段分别是060、6070、7080、80-90、90分以上，在单元格D452中输入：=SUM(D$2:D$417=$L452)*(D$2:D$417=$M452)*(I$2:I$417$M453)*($B$2:$B$417=$B452)将其再向下复制即可。如图（9）所示。通过上述几个步骤，1班各学科和总分的成绩分布情况就做完了。将1班成绩分布情况复制几份到后面，将班别改成其它班。选中1至417行对成绩按主关键字“总分”递减排序。至此，统计的工作就完成了第四步、为了使我们做的表格板面美观，还需做点小小的美工。首先，将在统计过程中所输入的班别、统计分数段人数的分段点，把这些单元格中的字体颜色设置为白色。其次根据自己的要求设置好各部分的表格边框的样式。最后在页面设置中设置好表格的标题，在页脚中设置好页码，点击菜单中的“数据筛选自动筛选”，在班别一栏的下拉列表中任选一个班，预览一下，看看效果如何。图（10）是我做的表头效果，图（11）是统计部分的效果，还可以吧。最后别忘记，将文件另存为模板，随时都可以用哦。图（11）图（10） EXCEL中的函数可谓是丰富多彩，只要我们多留