ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 挑战你的Excel知识,一起测验下 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 3968|回复: 18

[原创] 学校期末考试工作三步曲

[复制链接]

TA的精华主题

TA的得分主题

发表于 2014-1-24 19:46 | 显示全部楼层 |阅读模式
本帖最后由 不经意的回头 于 2014-1-24 19:48 编辑


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


我的做法是:
1.先录入基础数据,包括序号、姓名、性别、学校、班级、考场、考号等信息。这里的序号是全部学生的流水号。
2.png
2.根据每考场人数计算所需要的考场数量。
    如图3,设置每考场40人,在K2单元格输入公式=ROUNDUP((COUNTA(A:A)-1)/J2,)。本例中学生数为156,所需要考场为4。
3.png
3.在辅助区域中的“考号序列”列输入公式:=MOD(ROW(A1)-1,$J$2)+1并向下填充,生成一个1-40的循环序列。将该序列以“选择性粘贴—数值”的方式粘贴到考场一列。
4.以“考场”列为关键字进行升序排序,形成如下图效果:
4.png
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},{"","","","",""}),并将结果选择性粘贴到“考场列”,单元格格式改为:;;;""@"考场"
5.png
6.将“考号序列”中的考号值选择性粘贴到“考号”列,考场安排完成。
6.png

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


三、成绩统计
      到目前为止,我总共用过三个方法对考试成绩进行汇总。下面我分别介绍一下:
    1、分类汇总法
    第1步,先对数据以学校、班级为关键字进行排序;
    第2步,以学校为分类字段,汇总方式为平均值,对各学科进行分类汇总,记得取消“替换当前分类汇总”前的勾选。(如图)
3-1.png
    第3步,以班级为分类字段,汇总方式为平均值,对各学科进行分类汇总,记得取消“替换当前分类汇总”前的勾选。(如图)
3-2.png
    第4步,删除班级汇总、总计等行,并隐藏班级、性别、考场、考号等列(如下图3-33-4
3-3.png

3-4.png

    选中数据区域C1:M173,定位到可见单元格并复制到其它区域(3-5),
3-5.png
此时学校列和班级列数据因为错位有数据重复。我们选中A列数据区域,定位到空值,并执行删除整命令,最后形成成绩表如下(3-6):
3-6.png
小  结
优点:不用函数也可以完成统计。
缺点:操作步骤较为繁琐。


2、函数公式法
   第1步,构造成绩表结构。将学校和班级列复制到目标区域,执行删除重复项,得到学校和班级唯一项,将考试科目标题一并复制到相应区域,得到如下图
3-7.png

   在表格中输入公式=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-8.png
小 结
优点:用函数公式来进行统计,非常方便灵活,
缺点:需要使用者有一定的函数基础。
3、数据透视表
    这是我个人极力推荐的方法!一旦你认识了她,相信你会爱上她的——数据透视表!方便、快捷。。。。。。好的没法说,嘻嘻。
    第1步:将光标定位到数据区域的任意位置,依次执行“插入—数据透视表”命令
    第2步:在弹出的“创建数据透视表”对话框中,指定透视表存放的位置,可以是新建一个工作表,也可以在当前工作表中指定一个位置,如下图(3-11
3-11.png
    第3步,在“数据透视表字段列表”对话框中,将学校、班级名称字段拖入“行标签”框中,将语文、数学等学科字段放入数值框中。此时数值框中会显示为“计数项:数学”的字样,表示统计方式为计数统计。我们可以点右侧的下三角,选择“值字段设置”,将“值字段汇总方式”更改为“平均值”。
3-9.png

3-12.png
    第4步,几项必要的美化操作。
      1.数据透视表工具—设计—报表布局—以表格形式显示;
      2.在“学校”项上点右键,取消“分类汇总 学校”项前面的勾选;
      3. 依次点击“数据透视表工具—选项—+/-按钮”,取消对加减符号的显示;
      4.将学科前面的“平均值:”等字样替换成空格;
      5.对透视表中的数值区域进行单元格设置。
   最后结果如下图:
3-10.png

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

学校考试三步曲.rar

43.2 KB, 下载次数: 189

评分

参与人数 2财富 +50 鲜花 +2 收起 理由
数据先生 + 2 优秀作品
祝洪忠- + 50 很细致的总结

查看全部评分

TA的精华主题

TA的得分主题

发表于 2014-1-24 20:14 | 显示全部楼层
已在深入学习中!谢谢楼主

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-1-24 20:18 | 显示全部楼层
DZ潇潇 发表于 2014-1-24 20:14
已在深入学习中!谢谢楼主

欢迎大家交流讨论。

TA的精华主题

TA的得分主题

发表于 2014-1-24 22:02 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-1-24 22:50 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-1-26 11:33 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-1-26 17:11 | 显示全部楼层
可以用vloolup  ,  不断引用  效果一样

TA的精华主题

TA的得分主题

发表于 2014-1-26 17:14 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-1-26 17:55 | 显示全部楼层
shutongyu2002 发表于 2014-1-26 17:14
打错了 是  vlookup  有兴趣 可以联系我  讨论

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

TA的精华主题

TA的得分主题

发表于 2014-1-30 09:13 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-9-21 21:03 , Processed in 0.099024 second(s), 18 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表