巧用EXCEL公式进行成绩统计与分析

用EXCEL公式对学生进行成绩统计与分析

成绩的统计与分析是学校教学中的一个重要环节。通过对学生每次考试的成绩的合理分析，可以及时了解学生在学习过程中出现的问题和老师在教学中存在的不足，进而及时改正。很多老师在教学管理中或多或少的使用EXCEL进行成绩的统计与分析，然而由于对EXCEL使用不够深入，不能正确合理的使用公式，往往也要花费很大的精力。其实只要熟练地掌握以下几个公式，就可以大大地提高工作的效率。下面就以我在实践中得出的一点经验为例，讲解一下学生成绩的统计与分析一般过程，以期与大家交流与分享：

一、成绩的录入

1、单科成绩录入。现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正当竞争，考前全年级统一进行考场和座号的安排，每个考场里分布几个甚至十几个班的学生，考完试卷按考场装订阅卷，所以成绩录入时应按考场和考号顺序录入。这样增加了透明度，公平合理。如表1：

2、成绩汇总。把各个备课组送交上的成绩表，通过按学号排序，全年级按班以及班里学号的顺序将成绩汇总成一张总表，如表2：

二、成绩分析

1、排名统计。很多学校都是把每次考试成绩来做为每个科任老师教学能力的重要指标，统计每个科任老师所教科目在全年级各名次段中占有的人数比例是必不可少的内容。用“=RANK(D28,$D$28:$D$32)”求出每个学生的各科成绩在全年级的排名，注意“$”符号在该函数中的作用，是不能省略的。然后统计每个科目在每个名次段的人数。假设在D列是一个班的语文成绩的排名我们可以采用EXCEL公式“=COUNTIF(成绩表!D28:D32,"<=200") “来完成计算该科任老师所教科目在全年级前200名的学生人数。如表3：

2、利用图表分析每个班的每个科目的情况。根据该班某科的分数段分布可清晰得出是否发展正常。

利用公式“=COUNTIF(成绩表!C28:C32,">130")-COUNTIF(成绩表!C28:C32, ">=140")”可以轻松获得成绩在130~140的人数，如表4。然后通过该表绘制相关图表，如表5。

三、查询技巧

许多学校为了鼓励一些家庭困难的学生，根据考试成绩对品学兼优的学生进行一些奖励和表扬措施。学校为这些学生建立了电子档案，这就要求我们查找相关学生的学习成绩，以往很多人都是通过EXCEL提供的查找功能，查找到该学生的学号或姓名，然后复制该同学的成绩，这就大大增加了我们的工作量，其实可以通过公式，顺利的完成这项工作。假设全年级成绩表中的区域大小为A1：DV1209（即包含所有学生的成绩），我们在奖励一表的C30单元格里输入公式“=IF(ISERROR(VLOOKUP(A30,成绩表!$A$1: $DV$1209,2,FALSE)),"",VLOO KUP(A30,成绩表!$A$1: $DV$1209,2,FALSE))”即可求得该学生的语文成绩。下面详细说明上述公式中各项的作用：公式的意思是以单元格A30作为查询内容，查找在成绩表中A1：DV1209区域中的值等于A30的行的第二列的值，如果查不到则显示为空，否则显示该单元格的值。当然我们也可以灵活改动""为“无此

人”等。这个函数较为复杂，是一个公式的牵套。下面我们对每个公式进行详细说明：

1、ISERROR函数的主要作用其实就是判断一个单元格是否是错误提示，语法是ISERROR( 单元格)，如果一个单元格出错，那么ISERROR的返回结果将是T RUE，否则就是FALSE。当然，单独使用这个函数还仍然无法达到我们隐藏错误提示的目的，下面就要给大家介绍完成这项任务的另一员大将——IF函数

2、IF函数的主要作用也很简单，它可以根据条件来返回不同的数值，语法是I F(条件, 当条件为真时的值, 当条件为假时的值)，比如我们将公式设为IF(A1> 0 , “通过”, “拦截”)，那么在工作时Excel会自动判断A1单元格的值，如果它大于0，那么当前单元格便会显示出“通过”两字，反之如果A1单元格的值小于或等于0，那么当前单元格便会显示出“拦截”两个字。这样，通过将I F和ISERROR两个函数配合使用，就可以先让ISERROR判断目标单元格是否显示错误，如果是，那么ISERROR就返回一个真值给IF，而IF函数再根据这个真值在当前单元格显示一个空白（比如“”）来盖住原来的出错提示，而如果目标单元格的值不是错误，那么直接显示原来的公式就可以了。

3、VLOOKUP(lookup_,table_array,col_index_num,range_lookup)

（1）Lookup_ 为需要在数据表第一列中查找的数值。Lookup_ 可以为数值、引用或文字串。（2）Col_index_num 为table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回table_array 第一列中的数值；col_index_nu m 为2，返回table_array 第二列中的数值，以此类推。如果col_index_num

小于1，函数VLOOKUP 返回错误值#!；如果col_index_num 大于table_arr ay 的列数，函数VLOOKUP 返回错误值#REF!。（3）Range_lookup 为一逻辑值，指明函数VLOOKUP 返回时是精确匹配还是近似匹配。如果为TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于l ookup_ 的最大数值；如果range_ 为FALSE，函数VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。

EXCEL已经成为办公自动化软件中最常用的软件之一，小到课程表，大到数据分析，受到越来越多人的青睐。熟练使用EXCEL可以解决教育教学管理很多数据的分析与统计。