自己动手用EXCEL做一个考试成绩分析应用软件

1、自己动手，用EXCEL做一个考试成绩分析应用小软件河南省沁阳市第二学 王东庆 柴红伟长期以来，在每次考完试后，不仅要去处理学生的成绩，还要计算和分析教师的成绩。这是一件非常烦琐和无可奈何的事情。如果只是对学生的总分、名次、各科的平均分进行统计的话，用EXCEL去完成这项工作是一件非常容易的事情，然而许多学校还要对任课教师进行成绩统计，依据就是该任课教师所教该学科的“三率和”。“三率和”是指将某一学科的平均分+及格率（及格人数/参加考试的人数*100）+优秀率（优秀人数/参考人数*100）。实际上我们用EXCEL是可以轻松做到的。只需将学生的考试成绩按照一定的格式录入，那么EXCEL就可以自动去

2、完成计算三率和的工作。下面我们通过一个实例来看一下。比如录入初一年级某次考试成绩：录入结束后，利用EXCEL自带的一些函数，就能方便地把结果统计出来。如图：在开始学习之前，大家有必要了解一些EXCLE 函数方面的知识。公式和函数是Excel最基本、最重要的应用工具，是Excel的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。1数组公式的输入数组公式的输入步骤如下：（1）选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。（2）输入数组公式。（3）同时按“Crtl+Shift+Ente

3、r”组合键，则Excel 自动在公式的两边加上大括号 。特别要注意的是，第（3）步相当重要，只有输入公式后同时按“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组公式。否则，如果只按Enter键，则输入的只是一个简单的公式，也只在选中的单元格区域的第1个单元格显示出一个计算结果。2条件求和SUMIF函数SUMIF函数的功能是根据指定条件对若干单元格求和，公式为:=SUMIF(range,criteria,sum_range)式中 range用于条件判断的单元格区域, 只有当range中的相应单元格满足条件时，才对 sum_range 中的单元格求和。如果省略 sum_ran

4、ge,则直接对 range 中的单元格求和。3. AVERAGE函数AVERAGE函数的功能是计算给定参数的算术平均值。公式为= AVERAGE（参数1，参数2，参数N）4.COUNT函数和COUNTIF函数COUNT函数的功能是计算给定区域内数值型参数的数目。公式为= COUNT（参数1，参数2，参数N）COUNTIF函数的功能是计算给定区域内满足特定条件的单元格的数目。公式为= COUNTIF（range，criteria）式中range需要计算其中满足条件的单元格数目的单元格区域；criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。COUNT函数和COUNT

5、IF函数在数据汇总统计分析中是非常有用的函数。有了这些基础知识以后，下面，笔者就将这一过程详细展现给大家。首先，新建一个EXCEL文档，将文档的第一个工作表sheet1重命名为“成绩”，将第二个工作表sheet2重命名为“统计”。回到成绩工作表中，从第一个单元格开始，依次输入年级、班级、姓名、考号、语文、数学、英语、政治、历史、地理、生物、总分等。然后单击菜单栏中的“插入”“名称”“定义名称”，在定义栏中输入“年级”，然后单击添加。如图所示：再将引用位置中的“=成绩!$A$2”，改写为“=成绩!$A$2:$A$1000”,它的意思是说定义“年级”这个名称是由A列的第2行开始到第1000行结束，

6、在这个区域有效（也可以根据自己的实际情况进行定义,但一定要保证所定义的名称范围超出数据输入的范围，其它的定义和它是类似的）。“年级”定义结束后，我们采用同样的方法去定义“班级”这个名称。单击B2单元格，单击“插入”“名称”“定义名称”。这时由于“班级”这一列是在B列上，因此需将它的引用位置“=成绩!$B$2”改写成“=成绩!$B$2:$B$1000”。采用类似的方法依次定义 “姓名”、“考号”、“语文”、“数学”、“英语”、“政治”、“历史”、“地理”、“生物”、“总分”，注意要将它们的引用位置写正确！这样，在“成绩”工作表中我们所要做的准备工作就基本完成了。现在就可以在此“成绩”工作表中进行

7、学生成绩的录入工作，学生的成绩也可以是从其它的EXCEL文档中复制粘贴过来，但是顺序必须要和“成绩”表中的顺序保持一致。在打开“统计”这个工作表。单击“插入名称定义”，你会发现，在“成绩”工作表中定义的名称，在“统计”这个工作表中也可以显示出来。我们在“统计”工作表中建立如图所示的一张表：注意，第15行的06表示这个年级是06级。单击B5这个单元格，在公式编辑栏中输入“=COUNTIF(班级,A5)”，它的意思是要统计我们定义的名称“班级”当中的A5这个班（即06p01班）的参加考试人数。接着同时按下ctrl+shift+enter这三个键，生成一个计算班级参加考试人数的数组公式。接着再单击C

8、5这个单元格，在公式编辑栏中输入“=AVERAGE(IF(班级=A5),语文)”，它的意思是说要计算A5这个班的语文平均分，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击D5这个单元格，在公式编辑栏中输入“=SUM(班级=A5)*(语文>=60)/COUNTIF(班级,A5)*100”，意思是说要统计A5这个班级当中语文成绩大于60分（及格分数是自己定义），也就是说统计该班的语文科的及格人数，然后除以这个班实际参加考试的人数再乘以100，就得出了A5这个班的及格率。注意“SUM(班级=A5)*(语文>=60)”中的这个*，表示的是逻辑运算中的“与”。而CO

9、UNTIF(班级,A5)*100”中的*表示乘的意思，这两个*的含义是不一样的。同理，单击E5这个单元格，在公式编辑栏中输入“=SUM(班级=A5)*(语文>=80)/COUNTIF(班级,A5)*100”，便可以得出这个班的语文优秀率（实际上优秀的分数也是自己根据实际情况进行定义的）。同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击F5这个单元格，在公式栏中输入“C5+D5+E5”,就得出了三率和。然后再用鼠标拖拽选中B5到F5这些连续的单击格，向下填充到第14行。如图：接下来要做的工作是，单击B15这个单击格，在公式栏中输入“=COUNTIF(年级,A15)”

10、，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击C15单元格，在公式栏中输入“=AVERAGE(IF(年级=A15),语文)”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击D15单元格，在公式栏中输入“=SUM(年级=A15)*(语文>=60)/B15*100”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击E15单元格，在公式栏中输入“=SUM(年级=A15)*(语文>=80)/B15*100”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击F15单元格，在公式栏中输入“D1

11、5+E15+C15”。接着我们要做的是各分数段人数的统计工作。单击H5单元格，在公式栏中输入“=MAX(IF(班级=A5),语文)”，它的意思是统计出该班的最高分。单击I5这个单元格，在公式栏中输入“=MIN(IF(班级=A5),语文)”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击J5这个单击格，在公式栏中输入“=SUM(班级=A5)*(语文<=59)”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击K5单元格后，在公式栏中输入“=SUM(班级=A5)*(语文>=60)*(语文<=79)”，同时按下ctrl+shift+

12、enter这三个键，生成一个数组公式。单击L5单元格，在公式栏中输入“=SUM(班级=A5)*(语文>=80)*(语文<=100)”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。然后用鼠标拖拽选中H5到L5，向下进行填充。如图：然后单击H15这个单击格，在公式栏中输入“=MAX(IF(年级=A15),语文)”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击I15这个单元格，在公式栏中输入“=MIN(IF(年级=A15),语文)”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击J15单元格，在公式栏中输入“=SUM(语文<=59)*(年级=A15)”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击K15单元格，在公式栏中输入“=SUM(年级=A15)*(语文>=60)*(语文<=79)”，同时按下ctrl+shift+enter这三个键，生成一个数组公式。单击L15单元格，在公式栏中输入“=SUM(年级=A15)*(语文>=80)*(语文<=100)”，同时按下ctrl+shift+enter这三个键，生成