浅谈公式在Excel中的应用.docVIP

如何利用EXCEL软件进行成绩数据的自动化处理 （图1） 确定学生的总分，用公式SUM，=SUM(C2:H2)（图2） （图2） 这是总分，如果要算几科在一起的成绩，比如综合，公式：=SUM(F2+G2+H2)。（图3） （图3） 总分算好后，可能要算到平均分，一个班的话，直接用公式AVERAGE。（图4） （图4） 但如果是一个年级各个班的平均分，就不仅仅要用公式AVERAGE，首先确定班级。班级可以从学号中提取，用公式MID,如图5 （图5） 公式=MID(A2,5,3)，“=”符号不能少，“A2”表示提取数据的位置，“5”代表提取数据的起始位置，“3”代表提取的长度。“001”表示这个学生的班级是1班，以此类推。 确定好班级之后，在Excel工作簿中建立工作表，工作表的个数根据班级数定，工作表的名称就是班级的名称，我们以4个班级举例（图6）。 （图6） 确定班级数之后，从工作表“成绩”中将各班级的成绩复制到班级工作表中（图7）。 （图7） 班级数据复制好之后，再建立一个工作表，名称为“平均分”（图8） （图8） 首先确定各班人数，选定“B4”,输入公式=COUNTIF(成绩!K:K,A4), 公式“COUNTIF”用来计算满足条件的单元格个数，“成绩!K:K”表示范围在工作表名称为“成绩”的K列，“A4”表示以当前工作表A4单元格的内容为条件，结果如图9。 （图9） 图9结果显示1班的人数为3人。 人数确定后可以计算这个班级的各学科及总分的平均分了，用公式AVERAGE,选定“C4”,输入公式 =AVERAGE(1!C2:C4), 公式“AVERAGE”用来计算平均分，“1!”表示数据在 “1”的工作表中，“C2:C4”表中“C”表示语文成绩所在列，“2”表示从C列第二行开始提取（第一行为题头），“4”是根据各班人数确定的，1个人数为3人，范围到“C4”，如果是4人，则范围到“C4”结束，以此类推（图10）。 （图10） 平均分确定以后，就可以用公式TANK进行排名了，选定“D4”,输入公式=RANK(C4,C$4:C$7), 公式“RANK”用来排序，“C4”表示确定当前工作表C4单元格的内容在确定范围内的位置，“C$4:C$7”用来确定排序的范围。特别指出的是“$”符号是用来对排序的范围进行固定。（图11） （图11） 语文成绩和名次确定后，其他学科依次类推。（图12） (图12) 平均分确定后，我们再来算一下各班各学科分数段、最高分和最低分。首先还是要建立一个工作表，名称为“某某” 学科（一般有几个学科就见几个工作表，这里只以语文学科为例），然后在表中输入图13内容 （图13） 选定“B2”,输入公式=COUNTIF(1!C2:C99,=140)-COUNTIF(1!C2:C99,=150), 这个公式的用途是算出1班学生语文成绩中大于140，小于150的学生人数，其中“1”表示在工作表名为“1”， “C2:C99”是范围，“C2”是起始位置，“C99”是结束位置（99是个概数，只要大于班级人数就行，一般情况一个班不会超过99人）。“=140”和“=150”为分数的范围，可以根据实际情况调整。如图14 （图14） “B2”确定后，选中“B3”输入公式 =COUNTIF(1!C2:C99,=135)-COUNTIF(1!C2:C99,=140)，这个公式与“B2”中公式是一样的，区别在于分数的范围有变化。以此类推，确定其他的范围，合计用公式SUM（=SUM(B2:B25)），最高分用公式MAX(=MAX(1!C2:C99)) ，最低分用公式MIN(=MIN(1!C2:C99)) ，如图15。 (图15) 语文学科确定后，用同样的方法确定其他学科的分数段和最高、最低分。最终如图16 （图16） 上述工作完成后，一次考试学生的所有成绩信息基本上都能分析出来，在具体操作过程中，可能会感觉到很难，不过使用公式最大的好处终于“一次使用，终身收益”，第一次套用公式，下一次只要把数据放入，相关成绩信息就能自动分析出来。 事实上，Excel功能是很强大的，我们只能掌握皮毛而已。但是把这些掌握的知识运用到实际工作中，就能够提高我们的工作效率。让我们继续努力吧！