Excel函数在学生成绩统计中的应用

西棘针小学  梁友军

Excel俗称电子表格，是集表格、计算和数据库为一身的优秀软件，其数据处理的核心是函数和数据库。
    长期以来，在每次考完试后，不仅要去处理学生的成绩，还要计算和分析教师的成绩。这是一件非常烦琐和无可奈何的事情。如果只是对学生的总分、名次、各科的平均分进行统计的话，用EXCEL去完成这项工作是一件非常容易的事情，然而许多学校还要对任课教师进行成绩统计，依据就是该任课教师所教该学科的“三率和”。“三率和”是指将某一学科的平均分+及格率（及格人数/参加考试的人数*100）+优秀率（优秀人数/参考人数*100）。实际上我们用Excel是可以轻松做到的。只需将学生的考试成绩按照一定的格式录入，那么Excel就可以自动去完成计算三率和的工作。下面就具体介绍一下。

一．考试成绩的统计
（一）对班学生成绩的总体评估统计
1. 假设E4：E53单元格存放某班学生的语文考试成绩，若要计算该科的总分、人平分、及格人数、及格率、优秀人数、优秀率、最高（低）分、分段统计等。
（1）参考人数统计：
若要将参考人数放在E54单元格中，只需选定该单元格并插入“计数函数”：“COUNT”并选定要计数的单元格区间E4:E53或（输入）公式：“=COUNT(E4:E53)” 回车即可；
（2）学科总分的统计：
若要将该科的总分存放在E55单元格中，只需选定该单元格并插入“求和函数”：“SUM”并选定要计数的单元格区间E4:E53或（输入）公式：“= SUM (E4:E53)”回车后即可计算出该科总分(注：在E55单元格只显示计算结果。如要修改该公式，只需选定该单元格，在编辑栏内即可修改)。
（3）学科平均分的统计：
若要将该科人平分存放在E56单元格中，选定该单元格插入平均值函数“AVERAGE”并选定求值区间“E4：E53”或输入公式：“＝AVERAGE(E4：E53)”，回车后即可计算出人平分(注：数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的“0”则参与运算)。
（3）学科及格人数统计：
若要将该科的及格人数存放在E57单元格中，选定该单元格插入计算某个区域内满足指定条件的单元格数目函数的条件函数：“COUNTIF”，插入函数的方法是单击“插入”菜单中的“插入函数（F）…”命令，弹出一个“插入函数”对话框，在其中的“选择函数”下的列表框中选定选定“COUNTIF”函数，单击“确定”，弹出一个“函数参数”指定对话框，在其中的“Range”右边矫形框中输入统计的区间E4：E53或（单击矫形框右端的压缩图标，再在工作表中选择统计区间E4：E53，再单击“函数参数”压缩框中右端的扩展图标，返回扩展的“函数参数”对话框中），再单击对话框中的“Criteria”右边的矩形框，并键入及格条件：“>=60”，最后单击“确定”即可；也可以手动编辑输入公式及条件：“＝COUNTIF(E4：E53，″＞＝60″)”回车，即可计算出该科的及格人数(空白单元格、文字、逻辑值、错误值均被忽略)。
（4）学科及格率统计：
若要将该科的及格率存放在E58中，选定该单元格并输入公式“＝COUNTIF(E4：E53，″＞＝60″)/ COUNT (E4：E53)”回车(COUNT为计数函数，用法是“计数函数”加上“计数区间”)，即可计算出该科的及格率(其中“COUNT (E4：E53)”是计算E4：E53区间内单元格中的实际参考人数，空白单元格将被忽略)。
（5）学科优生人数统计：
若要统计学科优生人数与学科及格人数统计类似。如：将该科的优秀人数存放在E59单元 格中，选定该单元格插入条件函数：“COUNTIF”，方法与统计及格人数一样，只是函数参数对对话框中“Criteria”右边的矩形框中的函数参数要修改为：“>=80”(优分标准)；
（6）学科优秀人数及优秀率统计：
学科优秀率的统计与学科及格率的统计相同。如：将该科的优秀率存放在E60单元格中，选定该单元格并输入公式：“＝COUNTIF(E4：E53，″＞＝80″)/COUNT (E4：E53)”。这与及格率公式的编辑只是参数“＞＝60” 变成了“＞＝80”而已。
求优秀率：例如：B2:B50存放的是初一一班的语文期末考试成绩，B2:B500存放的是初一全年级语文考试的成绩，如果规定全年级20%的学生为优秀，那么初一一班语文的优秀率应该这样计算：“COUNTIF(B2:B50,">="&&LARGE(B2:B500,INT(0.2COUNT(B2:B500))))/COUNT(B2:B50)”，其中LARGE(B2:B500,INT(0.2COUNT(B2:B500)))所求的是全年级语文分数前20%中最低的一个同学的分数，COUNTIF(B2:B50,">="&&LARGE(B2:B500,INT(0.2*COUNT(B2:B500))))则是求出了初一一班语文高于或等于这个同学分数的人数，最后再除以初一一班的总人数COUNT(B2:B50)，所得就是初一一班的语文优秀率，如果想求其他班其他科目的优秀率，道理都是一样。
（7）学科最高分的查询统计：
若要查询统计学科最高分，则可利用“MAX”(最大值)函数。如将E4：E53区域中存放着的全班50名学生语文科的考试成绩，将其学科最高分、最低分别存放在E61和E62单元格中，则可选中E61单元格，插入“最大值”函数：“MAX”并指定统计数据的区域：“E4：E53”或在编辑栏输入公式“＝MAX(E4：E53)”回车后即可计算出其中的最高分。
（8）学科最低分的查询统计：
如将E4：E53区域中存放着的全班50名学生语文科的考试成绩，将其学科最低分别存放在E62单元格中，则可选中E62单元格，插入“最小值”函数：“MIN”并指定统计数据的区域：“E4：E53”或在编辑栏输入公式“＝MIN(E4：E53)”回车后即可计算出其中的最低分。
（9）若在“F4:F53”、“G4:G53”……“K4:K53”区域中分别存放数学、英语、计算机、音、体、美等学科成绩，则只需选定E54：E62区域，移动空心十字光标至单元格右下角。待光标变成小实心十字光标时，按住鼠标左键沿行拖动控制手柄至K列即可，放开鼠标左键后，则各科的科总分、人平分、及格人数、及格率、优秀人数、优秀率、最高分、最低分等立即计算出来。
2. 班学生成绩的分段统计：
若要将学生成绩按分数段进行统计，则可利用FREQUENCY函数(以一列垂直数组及对该数组设定进行频率颁布计算的分段点计算出一组数据的频率颁布)。假设E4：E53共有50个学生的语文科测试成绩，那么可在B66：B70单元格分别输入59、69、79、89、100，则表示统计59(包括59)分以下、60—69、70—79、80—89、90—100分数段的成绩个数。
若要将计算结果存入C66：C70，则选中C66：C70单元格区域，在编辑栏内输入公式“＝FREQUENCY (E4：E53，$B$66：$B$70)”,（注：公式中区域：“E4：E53”是用来计算频率的数组区域——班学生学科成绩的单元区域，公式中区域：“$B$70：$B$70”是对所计算频率数组——班学生学科成绩进行频率计算的分段界点的数组区域的绝对引用），输完后让光标停在公式末尾，按下SHIFT＋CTRL键敲回车(这是生成数组公式的关键环节)，Excel会自动在公式两侧加上大括号，公式成为“{＝FREQUENCY(E4：E53，$B$66：$B$70)}”,则区间C66:C70各单元格中立刻计算出对应各分数段中成绩的个数。
（二）对班中学生个体成绩的评估统计
1. 学生个体总成绩的统计：
若在工作表中E4：K53区域存放某班50个学生各科的成绩，而E4:K4、E5：K5、……E53：K53中分别各个学生语文、数学、……、美术等各科成绩记录。则需在L列中的L4：L53区间单元格中分别存放各学生的各门学科成绩总分，则先选定L4单元格插入求和函数“ＳＵＭ”并选定求和区域回车或手动输入公式“=ＳＵＭ(E4:K4)”回车即可计算出该生各科的总分；然后选定L4单元格，并将鼠标指针指向选择框右下角的控制柄并沿列拖动鼠标至L53即可算出全班其余学生各科的总分。若某生未考试，但不希望在其总分单元格填入“0”，只需将公式修改为“＝IF(ISNUMBER(E4：L4)，SUM(E4：L4)，″″)”回车，单元格中会填入空格而不会出现“0”。
2. 学生个体班级名次的统计：
若要将该班学生的班级排名计算出来，但不希望打乱学生原来按学号的顺序，则可利用RANK函数轻松完成，该函数的最大优点是，可以处理重名次，可使数值相同的单元格计算出的名次相同。如上例在L4：L53区域中存放某班学生的总分，要将学生在班级排名的结果放在N4：N53区域中，只需在N4中输入公式“＝RANK(L4，$L$4：$L$53)”回车，即可计算出该生在班级中所排名次，然后沿列拖动复制，即将全班学生的排名计算出来。当有并列重复名次时，并列名次后缺少名次数等于名次并列重复数。
如果某些学生未参加考试，又不希望在其存放名次的单元格内显示出错信息(如：#VALUE！)，则可将公式修改为“＝IF(ISNUMBER(L4)，RANK(L4，$L$4:$L$53), ″″)”。
在计算的过程中我们需要注意两点：首先当RANK函数中的Number不是一个数时，其返回值为“#VALUE!”，影响美观。另外，Excel有时将空白单元格当成是数值“0”处理，造成所有成绩空缺者都是最后一名，看上去也很不舒服。此时，可将上面的公式“=RANK(E2,$E$2:$E$50)”改为“=IF(ISNUMBER(E2),RANK(E2,$e$2:$E$50),"")”。其含义是先判断E2单元格里面有没有数值，如果有则计算名次，没有则空白。其次当使用RANK函数计算名次时，相同分数算出的名次也相同，这会造成后续名次的空缺，但这并不影响我们的工作。同样的道理，我们也可以算出一个学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注意参与计算的数字单元格区域不一样。
3. 学生个体等级评定成绩的统计：
如学校采用等级评定考试成绩，若标准为：考试各科平均分数高于或等于85分为A等；考试分数低于85分且高于或等于70分为B等；考试分数低于70分且高于或等于60分为C等；考试分数低于60分为D等；没有参加考试的不划等级。假设平均分数存放在M列的M4:M53区域，计算结果存入O列O4:O53区域，则用Excel计算等第的公式如下：“＝IF(M4＞＝85，″A″，IF(M4＞＝70，″B″，IF(M4＞＝60，″C″，IF(ISNUMBER(M4)，″D″，IF(ISBLANK(M4)，″″)))))”，(公式中的等级代码为大写时显示大写,小写时显示小写,其余字母不区分大小写)这是一个IF函数嵌套公式，式中第二个IF语句是第一个IF语句的参数，第三个IF语句则是第二个IF语句的参数，以此类推。如果第一个逻辑判断表达式M4＞＝85为TRUE(真)，则O4单元格式被填入“A”；如果为FALSE(假)，则计算第二个IF语句“IF(M4＞＝70”；以此类推直至计算结束。其中ISNUMBER函数在M4为空时返回FALSE(假)，接着执行最后一个IF语句，否则在O4单元格中填入“D”。ISBLANK函数在M4为空时返回TRUE(真)，则在O4单元格中填入一个鉴定会格。使用ISNUMBER函数和ISBLANK函数，可防止某个学生没有参加考试(即考试成绩为空)，但仍然给他评定为D等的情况发生。计算其他学生的成绩等级时，选中O4,鼠标指向选定框右下角的控制手柄并拖动到O53单元格将公式复制即可。
如果成绩等级划分标准发生了变化，只须改变逻辑判断式中的值(85，70，60)即可，也可将等级代码“A、B、C、D”分别换成“优秀、良好、及格、不及格”等。

方法便于掌握，但最基础的成绩录入不能出错，Excel可以使用语音功能输入和校对成绩：
     Excel XP新增了一项“文本到语音”功能，利用这项功能我们可以让Excel工作表开口说话，并可以在输入数据时实现语音校对，大大方便了我们的输入校对工作。

　　单击“工具”菜单下的“语音”，然后选择“文本到语音”命令，注意默认情况下，这个功能并没有被安装，你可以放入Office XP的光盘，按照屏幕上的提示安装。打开“文本到语音”之后，会出现“文本到语音”的任务面板，
     共有五个控制按钮，从左到右依次是“朗读单元格”、“停止朗读”、“按行”、“按列”和“安回车键开始朗读”。

既然我们的输入有时难免出错，为什么不在输入时仔细校对呢？
    选择“文本到语音”任务面板的“按下回车键开始朗读”按钮，然后开始输入，
   当你在按回车键结束对一个单元格的输入时，Excel会接着读出的你刚才所输入的内容

怎么样试试吧，看看效率提高了没有。

西棘针小学  梁友军