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

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

2、竞争，考前全年级统一进行考场和座号的安排，每个考场里分布几个甚至十几个班的学生, 考完试卷按考场装订阅卷，所以成绩录入时应按考场和考号顺序录入。这样增加了透明度,26学号姓名语文数学英语270101学生12S0102学生2290103学生3300104.学生43101.05学生E32H 4耐 成绩表/Sheet 2/Sheet 3 /公平合理。如表1:2、成绩汇总。把各个备课组送交上的成绩表，通过按学号排序，全年级按班以及班里学号 的顺序将成绩汇总成一张总表，如表2：ABDE 11学号林座位号姓名科目2110111学生13130512学生24201113学生35174314学生46121215

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

4、0名前500名231班老师a521242班老师B42325珈老师C62526老师D116327子班老师E921川 成绩表/表32、利用图表分析每个班的每个科目的情况。根据该班某科的分数段分布可清晰得出是否发 展正常。利用公式 “ =COUNTIF（成绩表!C28:C32,">130"）-COUNTIF（成绩表！C28:C32,">=140"） ”可以轻 松获得成绩在130140的人数，如表4。（注：该表省略了几列）然后通过该表绘制相关图表，如表5。班叛150140-15(1130-140120130和以下1班013123渤0241Q13班03

5、313501593乐班U02112三、查询技巧许多学校为了鼓励一些家庭困难的学生，根据考试成绩对品学兼优的学生进行一些奖励和表扬措施。学校为这些学生建立了电子档案，这就要求我们查找相关学生的学习成绩，以往很多人都是通过EXCEL提供的查找功能，查找到该学生的学号或姓名，然后复制该同学的成 绩，这就大大增加了我们的工作量，其实可以通过公式，顺利的完成这项工作。假设全年级成绩表中的区域大小为 A1 : DV1209 （即包含所有学生的成绩），我们在奖励一表的C30单元格里输入公式 “=IF（ISERROR（VLOOKUP（A30,成绩表！$A$1:$DV$1209,2,FALSE）,"&

6、quot;,VLOOKUP（A30,成绩表!$A$1: $DV$1209,2,FALSE）即可求得该学 生的语文成绩。下面详细说明上述公式中各项的作用：公式的意思是以单元格A30作为查询内容，查找在成绩表中A1 : DV1209区域中的值等于 A30的行的第二列的值，如果查不到则显示为空，否则显示该单元格的值。当然我们也可以灵活改动""为无此人”等。这个函数较为复杂，是一个公式的牵套。下面我们对每个公式进行详细说明：1、ISERROR函数的主要作用其实就是判断一个单元格是否是错误提示，语法是ISERROR（单元格），如果一个单元格出错，那么ISERROR的返回结果将是 TR

7、UE，否则就是FALSE。当然，单独使用这个函数还仍然无法达到我们隐藏错误提示的目的，下面就 要给大家介绍完成这项任务的另一员大将一一IF函数2、IF函数的主要作用也很简单，它可以根据条件来返回不同的数值，语法是IF（条件，当条件为真时的值，当条件为假时的值），比如我们将公式设为IF（A1>0 ,通过”，拦截”）那 么在工作时Excel会自动判断A1单元格的值，如果它大于0，那么当前单元格便会显示出通过”两字，反之如果A1单元格的值小于或等于 0，那么当前单元格便会显示出拦截”两个字。这样，通过将IF和ISERROR两个函数配合使用，就可以先让ISERROR判断目标单元格是否显示错误，如

8、果是，那么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第一列中的数值；colndex_num 为2,返回table_array第二列中的数值， 以此类推。如果 col_index_num 小于1，函数 VLOOKUP 返回错误值 #!;如果col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。（ 3） Ran ge_lookup为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果 为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_的最大