利用EXCEL轻松应对考务工作胡家君.docVIP

利用EXCEL轻松应对考务工作 乾县第一中学 胡家君 在学校组织的阶段考试考务工作中，为了便于评卷实行流水作业和避免学生作弊，需要按照年级把学生混合编排考场，并按年级统计成绩。这就给我们来了困难，比如考场考号如何确定各学生的名次及在自已班内的名次如何排定？这些工作，通过多次排序和复制粘贴数据，肯定可以完成。但时，面对几千学生，几十个班级的这些数据，显然又太麻烦了。现在我们就把这项繁杂的工作交给EXCEL去完成吧。 一、考号及试场的确定。 要求：由于阅卷机要求考号必须为10位数字，因此我们确定考号前2位用学生入学年份，如2013年入学的为“13”，3、4位为学生的试场号如“第一试场为‘01’”，5、6位为学生班级如“一班学生为‘01’”，后4位为流水号，一般按照上次考试成绩为顺序。这样一个2013年入学，班级为一班，上次考试成绩为年级11名的学生考号就为“1301010011”。 1）、考场号的确定。根据要求每30名学生安排一个考场，我们只需要将鼠标定位于D2单元格，输入公式 “=ROUNDUP((ROW()-1)/30,0),”00”)”，回车后，再选中D2单元格，向下拖动其填充句柄至最后一行D单元格，就可以得到每位学生的考场号了。30人一个考场，不会错的。ROUNDUP函数的作用是对数值向上取最接近的整数，由于第一个学生的数据位于工作表的第二行，所以，我们只需要将数据所在行数减1之后再除以30，把所得的结果用ROUNDUP函数向上取整数=13D2C2TEXT(ROW(A1),0000)”，就后生成考号“1301010001”，效果如图所示。 公式“ROW(A1)”的意思提取数字“1”，通过TEXT字符函数转化为“0001”，“”是连接符。利用A2单元格填充柄就可以得到全部学生的考号。 二、年级名次和班级名次。 1）、年级名次的排定。成绩通过阅卷机录入后，需要排定名次。比较简单的是年级名次。使用RANK函数可以轻松解决问题。在J2单元格中输入公式“=RANK（H2,$H$2:$H$2013）”，拖动其填充柄向下复制至最后一得J2013，松开鼠标就可以得到全年级名次。效果如图： 2）、班内名次的排定。在不对工作表进行排序的情况下，想得到学生的班内名次，RANK函数就力不从心了。我们必须同时考虑到学生所在的班级以及其分数所处的位置，SUMPRODUCT函数此时正可以大显身手。将鼠标定位于2单元格，输入如下公式“=SUMPRODUCT(($C$2:$C$2013=C2)*($H$2:$H$2013H2))+1”。完成后仍然拖动其填充句柄至单元格，看看，是不是已经得到所有学生的班内成绩了？如图所示。 公式=SUMPRODUCT(($C$2:$C$2013=C2)*($H$2:$H$2013H2))+1 ))”的意思是：计算在2:C2013单元格区域等于2且在2:H2013单元格区域大于2的单元格数量，是本班内的最高分的话，这个数量就为0”，所以把它加1班内的名次