学校期末考试工作三步曲

不经意的回头

学校期末考试工作三步曲

作为一名教师，每个学期期中、期末考试的学生分考场、登分统计工作都要由我来完成。作为一名EXCEL菜鸟，这项工作曾经一再让我崩溃，直到我来到EH论坛。在这里我得到了很多老师的热心帮助，在老师们的指引下，我在EH培训中心学习了近一年的时间，使我对EXCEL的基础知识有了一定的了解，也学习掌握了一些操作技巧和函数基础知识。又逢期末考试，这次我较为轻松的完成了这项工作，我不会VBA，也不懂算法，只能基础操作和简单函数公式来完成，算是符合本校工作要求吧。下面我对我的做法做一介绍，欢迎大家拍砖，欢迎高手指点我继续改进完善。（本贴介绍的是基础应用，高手可以飘过）     下面我分三部分来介绍：安排考场、打印标签、统计成绩。

一、分考场 我在学区工作。学区下辖7所小学，每个小学的每个年级的班级数略有区别。安排考场要求每个考场40人，前后两个学生不能是同一个班。效果图如下： 我的做法是： 1.先录入基础数据，包括序号、姓名、性别、学校、班级、考场、考号等信息。这里的序号是全部学生的流水号。 2.根据每考场人数计算所需要的考场数量。     如图3，设置每考场40人，在K2单元格输入公式=ROUNDUP((COUNTA(A:A)-1)/J2,)。本例中学生数为156，所需要考场为4。 3.在辅助区域中的“考号序列”列输入公式：=MOD(ROW(A1)-1,$J$2)+1并向下填充，生成一个1-40的循环序列。将该序列以“选择性粘贴—数值”的方式粘贴到考场一列。 4.以“考场”列为关键字进行升序排序，形成如下图效果： 5.在辅助区域的“考场判断”列输入公式：=ROUNDDOWN((ROW(A1)-1)/$J$2,)+1 该公式会形成一个以每场人数40为循环的数字序列，将该列以“选择性粘贴—数值”的方式粘贴复制到“考场”列，并将单元格格式设置为：[DBNum1]"第"[$-804]G/通用格式"考场"，这样单元格显示的结果为“第X考场”（如图5）。此时学生已经被平均打散到各个考场中去。 如果考场不多，也可以用这个公式：=LOOKUP(ROUNDDOWN((ROW(A1)-1)/ $J$2,),{0,1,2,3,4,5},{"一","二","三","四","五"})，并将结果选择性粘贴到“考场列”，单元格格式改为：;;;"第"@"考场" 6.将“考号序列”中的考号值选择性粘贴到“考号”列，考场安排完成。

二、打印准考证     考场安排好后，为了方便小学生考试，还要打印准考证。制作准考证的方法有很多，我用的是WORD邮件合并功能。用EXCEL VBA应该更快捷、方便，可惜偶还没有学会，简单介绍一下邮件合并的方法吧。         1.新建准考证标签。 打印准考证我是通过邮件合并的标签形式完成的。关于标签的制作，WORD提供了非常的详细的示意图，相信大家一看就会明白的，这里就不再细述。下图中我是设定的8个标签。         2.选择开始邮件合并—选择标签项，选择刚才做好的准考证标签         3.键入标签标题等内容。          4.选择收件人—使用现有列表项，找到刚才做好的考场安排信息表，确定。     为方便以学校为单位分发准考证，我们可以选择编辑收件人列表，对学生信息按序号进行排序（当然以学校排序也是可以的）         5、将相应的合并域插入到对应位置。插入域后可以先点预览结果按钮，看下效果，并根据显示结果对标签内各项目的位置进行微调整。         6.将标签内容调整好后，点击“更新标签”，即可完成准考证的制作过程。将该标签文件保存，打印其他年级准考证时，只要更改一下收件人，再更新标签即可，非常方便。

三、成绩统计

到目前为止，我总共用过三个方法对考试成绩进行汇总。下面我分别介绍一下：     1、分类汇总法     第1步，先对数据以学校、班级为关键字进行排序；     第2步，以学校为分类字段，汇总方式为平均值，对各学科进行分类汇总，记得取消“替换当前分类汇总”前的勾选。（如图）     第3步，以班级为分类字段，汇总方式为平均值，对各学科进行分类汇总，记得取消“替换当前分类汇总”前的勾选。（如图）     第4步，删除班级汇总、总计等行，并隐藏班级、性别、考场、考号等列（如下图3-3，3-4）     选中数据区域C1:M173,定位到可见单元格并复制到其它区域（3-5）， 此时学校列和班级列数据因为错位有数据重复。我们选中A列数据区域，定位到空值，并执行删除整命令，最后形成成绩表如下（3-6）： 小  结 优点：不用函数也可以完成统计。 缺点：操作步骤较为繁琐。

2、函数公式法    第1步，构造成绩表结构。将学校和班级列复制到目标区域，执行删除重复项，得到学校和班级唯一项，将考试科目标题一并复制到相应区域，得到如下图    在表格中输入公式=SUM(($C$2:$C$161=$O2)*($D$2:$D$161=$P2)*I$2:I$161)/SUM(($C$2:$C$161=$O2)*($D$2:$D$161=$P2))，并三键结束并填充公式即可。    第一个SUM公式段通过两个逻辑判断对学生成绩区域（I$2:I$161）进行条件筛选并求和；后一个SUM公式段是对筛选条件进行逻辑判断后的逻辑结果的求和，等同于条件计数。当然，用函数还有很多方法，并不局限于此公式。 小 结 优点：用函数公式来进行统计，非常方便灵活， 缺点：需要使用者有一定的函数基础。

3、数据透视表     这是我个人极力推荐的方法！一旦你认识了她，相信你会爱上她的——数据透视表！方便、快捷。。。。。。好的没法说，嘻嘻。     第1步：将光标定位到数据区域的任意位置，依次执行“插入—数据透视表”命令     第2步：在弹出的“创建数据透视表”对话框中，指定透视表存放的位置，可以是新建一个工作表，也可以在当前工作表中指定一个位置，如下图（3-11）     第3步，在“数据透视表字段列表”对话框中，将学校、班级名称字段拖入“行标签”框中，将语文、数学等学科字段放入数值框中。此时数值框中会显示为“计数项：数学”的字样，表示统计方式为计数统计。我们可以点右侧的下三角，选择“值字段设置”，将“值字段汇总方式”更改为“平均值”。     第4步，几项必要的美化操作。       1.数据透视表工具—设计—报表布局—以表格形式显示；       2.在“学校”项上点右键，取消“分类汇总 学校”项前面的勾选；       3. 依次点击“数据透视表工具—选项—+/-按钮”，取消对加减符号的显示；       4.将学科前面的“平均值：”等字样替换成空格；       5.对透视表中的数值区域进行单元格设置。    最后结果如下图：

关于学校工作的这点事儿，各位大侠如果还有更好的做法，请多指教偶，在此多谢了！

DZ潇潇

已在深入学习中！谢谢楼主

不经意的回头

DZ潇潇 发表于 2014-1-24 20:14
已在深入学习中！谢谢楼主

欢迎大家交流讨论。

hellobaby589

如此劳动力，难免不进步呀，

细雨言寺

不错，学习了。

孤影任我行

三部曲内容丰富，学习借鉴

shutongyu2002

可以用vloolup  ，  不断引用  效果一样

shutongyu2002

打错了 是  vlookup  有兴趣 可以联系我  讨论

不经意的回头

shutongyu2002 发表于 2014-1-26 17:14
打错了 是  vlookup  有兴趣 可以联系我  讨论

你是用VLOOKUP实现哪一部分功能呢？

zhouguangcai

好，真的很好，学习