Excel函数在成绩统计中的一个应用实例.docVIP

Excel函数在成绩统计中的一个应用实例 在教学过程中，我们经常会遇上成绩统计的事情，在计算机广泛普及的今天，教师们都会运用电子表格来做成绩统计。我们每次对五个班300多同学进行全国计算机等级考试模拟考之后，产生大量的数据，对这些数据的登记、统计、分析成了一件颇为费时费力的事情，经过反复的探索，终于找到一种比较有效的方法，高效地解决了这个问题。现把它整理出来，希望能抛砖引玉，对大家的成绩管理有所启发。 问题的实例 假设有五个班的同学参加计算机等级考试，每两个星期进行一次模拟考，考完之后我们马上把成绩数据从考试服务器导出来，这是一个文本文件，然后我们以最快的速度对这些数据登记到成绩总表中，统计好之后分发到各个班级，向学生公布，成绩总表里的数据又可以给上课老师分析教学效果，调整教学方法。 解决的步骤 首先提前拿五个班的学生花名册，制作好一份《计算机一级5个班成绩登记表.xls》，用于登记每次的测验成绩，为后面的统计提供基础数据。如图1所示 图1 单元测验成绩登记表以及成绩总表 第一次单元测验后，从考试服务器中把成绩导出，成为一个文本文件。如图2所示： 图2《考生成绩一览表.txt》 如果有某位学生因故没参加考试，那么就是缺考，导出的这份成绩表里就没有他的名字。在后面的数据处理中就要把他登记为“缺考”。 操作第一步：把文本格式的考生成绩导入到Word 文档中，转变成一个表格。 考号 考生姓名 考试科目 考试总分 150199990101 欧惠霞 上机 61 150199990102 麦丽欣 上机 0 150199990103 邓锦琪 上机 71 150199990104 黄芊芊 上机 83 150199990105 谭斯尹 上机 65 150199990106 黄秀湄 上机 0 150199990107 朱丽敏 上机 0 150199990108 黎德扬 上机 69 150199990109 朱倩影 上机 77 150199990110 何嘉欣 上机 58 150199990111 苏敏仪 上机 61 150199990112 梁顺成 上机 67 150199990113 何丽君 上机 56 150199990114 林惠玲 上机 66 150199990115 朱倩婷 上机 55 150199990116 麦惠施 上机 58 150199990117 罗晓桐 上机 44 150199990118 苏燕茵 上机 61 150199990119 胡健铃 上机 54 150199990120 胡翠雄 上机 75 150199990121 何嘉盛 上机 73 150199990122 林翠结 上机 44 150199990124 蔡铨恒 上机 39 150199990125 简家惠 上机 0 第二步，把Word 中的表格复制到Excel ，放在sheet1中。 （后面各次测验成绩分别放在sheet2,sheet3,…如此类推）。如图3所示： 图3：第一次测验成绩，储存在sheet1中 第三步，通过下面所示的组合函数，把sheet1中的成绩“填写”到成绩总表里。注意，因为在300名考生里，难免会存在某些人缺考，缺考学生的名字是不会出现在考试服务器导出的数据中，因此不能简单地把数据从sheet1 复制粘贴到成绩总表，否则会出现成绩张冠李戴的现象。如图4所示： 图4：第一次测验成绩填写到成绩总表了 至此，我们可以把第一次测验成绩向学生公布了。 说明： 这里我们用到的函数组合是 =IF(ISNA(MATCH($B2,Sheet1!$A$2:$A$351,0)),缺考,VLOOKUP($B2,Sheet1!$A$2:$C$351,2,0)) 公式中的函数说明： IF函数： 判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。 语法：IF(logical_test,value_if_true,value_if_false) 在这里的用法是，判断某个学生是不缺考了。如果是，则返回“缺考”，如果不是，则返回他的成绩。 ISNA 函数：检测一个值是否为 #N/A 返回 TRUE 或 FALSE 语法：ISNA(value) 在这里的用法是，配合IF函数和MARCH函数做逻辑测试，如果MARCH得出的是#N/A，则返回 TRUE，从而判断为“此人缺考”，让IF函数返回“缺考” MARCH函数：返回符合特定值特定顺序的项在数组中的相对位置 语法：MATCH(lookup_value,lookup_array,match_type) 在这里的用法是，寻找成绩总表中某个学生名字在单元测验成绩表sheet1里的位置。如果找不到，则返回“#N/A”,意味着这个学生缺考了。 VLOOKUP 函数：搜索表