ExcelVba在成绩录入中提高效率的应用

ExcelExcel VbaVba 在成绩录入中提高效率的应用在成绩录入中提高效率的应用 李介甫小学 陈 靓 学生成绩的统计和分析是常规教学中不可或缺的手段之一 对 于量化教学质量 分析学生学习效果和修正下一步教学方向起到了 一定的指导作用 在计算机应用已经广泛普及的今天 传统的手工 统计和分析的方法其弊端是显而易见的 Excel 电子表格在学生成 绩的统计和分析中的运用大大提高了工作的效率 但在实际运用中 也发现了一些瓶颈问题 如何更进一步的提高 Excel 辅助统计工作 的效率就显得更加重要了 问题的成因 用 Excel 对学生成绩统计和分析的前提是要先将全体学生的成 绩录入到 Excel 表格中去 然后利用其统计函数根据具体的设定自 动计算出需要的数据 再对数据做相应的统计和分析 其中统计和 分析只需要事先一次性的设定好就可以了 如果遇到统计分析项目 有变化也只是对相应的统计公式做一些调整就可以了 可谓是 一 劳永逸 对于统计和分析部分的设计和实现在此就不累述了 但学生成绩的录入就不是了 每次学生考试的成绩都必须用手工的 方法逐个录入到电子表格中 每个年级三个考试科目就有三个成绩 全校 1000 多人就有 3000 多个数据需要录入 其工作量是可想而知 的 在大量成绩录入的过程中最主要的瓶颈是学生成绩录入单元格 的查找 学生的学号 各科成绩 总分的表格事先已经做好 就等 待具体成绩的录入 因为考试的时候为公平起见 各班学生的座 1 位是交叉打乱的 而且收卷的时候也没有什么顺序可言 所以成绩 录入时的试卷是混乱无序的 想提高成绩录入的效率就必须先对试 卷按照班级和学号进行排号的整理 而一个年级有 200 多学生 每 个科目先要按班级分开 再按各班内学号的大小来排序 如果手工 完成分班和学号排序 这一部分的工作量是不小的 根据往年的经 验 整个成绩的录入过程有 65 以上的时间都是消耗在了分班和学 号排序上了 所以如果可以解决分班以及学号排序的问题 成绩录 入的效率就会有大幅的提高 解决问题的思路 如何分班以及学号排序的问题呢 如果从试卷方面着手 只能 是用人工的方法 但人工分卷的办法效能太低 此方法行不通 那 就只有从 Excel 方面来解决问题了 Excel 的高级筛选功能是好用 用高级筛选功能很快就能定位到 要输入成绩的学号相应的单元格 但是其高级筛选功能需要每次筛 选之前用鼠标来设定筛选的条件 确定了单元格再输入成绩 这个 筛选 录入的动作需要操作员的手部不断的在鼠标和键盘之间切 换 这一点在大量数据录入的时候是一个很致命的缺陷 因为这种 切换是一个非常消耗录入效能的动作 每减少一个切换动作或是减 少一个按键动作都会相应的提高录入的效能 由此得出 想要提高 数据录入的效能就是设计出一个方案使得手部动作尽量的限制在最 小的活动范围内 并尽量的减少按键的次数 如何将手部的动作尽量的限制在最小的活动范围内 并尽量的 2 减少按键的次数呢 首先就是解决操作界面的问题 Excel 是以表 格的形式来工作的 在其操作的范围里有很多的单元格 在这些单 元格间实现数据的录入就必须要用鼠标或是不断的使用方向键来定 位 前面已经说过了这种鼠标或是方向键的定位方式效能是很低的 必须要将操作界面限定在一个很小的范围之内才能提高效能 因此 考虑使用 Excel 中 VBA 提供的 窗体 来统一界面的问题 界面统 一了 其他的问题就会迎刃而解了 具体解决方法 学生的成绩录入表的基本格式是固定的 见下图 那么只需要设计一个统一的录入界面来实现各科成绩的录入就 行了 先在工作簿上设定三个按钮用来 分别调取相应学科成绩录入的窗体 按钮中相应的 VBA 代码为 代码中 CommandButton1 对应 语文 按钮 CommandButton2 对应 数学 按钮 CommandButton3 对应 英语 按钮 Private Sub CommandButton1 Click 3 yuwen Show End Sub Private Sub CommandButton2 Click shuxue Show End Sub Private Sub CommandButton3 Click yingyu Show End Sub 然后通过菜单栏的 工具 宏 Visual Basic 编 辑器 进行具体窗体的设计 三个科目的窗体的设计基本是一样的 只是其中运行的代码有小小不同 这里就只用 语文成绩录入 的 窗体为例 新建一个窗体 然后在窗体中加入相应的控件 最终效果如下 图 4 接下来就是编写窗体中不同控件的代码 来实现具体的成绩录 入工作 窗体代码如下 以 开头的为注释语句 定义全局变量用以取得 学号 所在行号 Public k As String 此为 下一个 按钮 Private Sub CommandButton1 Click 清除所有文本框中内容 为下一个输入做准备 For i 1 To 4 Me Controls textbox i Next i 自动跳转到 查询 框 等待输入 学号 SendKeys tab SendKeys tab End Sub Private Sub CommandButton2 Click 退出 关闭窗体 关闭内存中的窗体 Unload Me 此处可改为相应的 shuxue yingyu Unload yuwen End Sub 5 此处为第一个文本框用以接收键盘输入的 学号 并做相应 查询 Private Sub TextBox1 Exit ByVal Cancel As MSForms ReturnBoolean 判断 学号 是否正确 Dim rng As Range Set rng Range A A Find TextBox1 Text If rng Is Nothing Then TextBox1 MsgBox 请输入正确的学号 Cancel True Else 取得输入查询的 学号 所在行的行号 k Range A A Find TextBox1 Text Row End If End Sub 此处为第二个文本框 提取查询到的 学号 与输入的 学 号 比对 防止录错分数 Private Sub TextBox2 Enter TextBox2 Range A k Value 6 自动跳转到下一文本框 因为这里只是显示查询的结果用以 比对 不需要手工录入任何信息 所以设定自动跳转 SendKeys tab End Sub Private Sub TextBox3 Enter 提取学生姓名比对 TextBox3 Range B k Value 自动跳转到下一文本框 SendKeys tab End Sub 此处为第四个文本框 用以录入成绩 Private Sub TextBox4 Exit ByVal Cancel As MSForms ReturnBoolean Dim n n TextBox4 Text 判断成绩有效性 是否大于 100 或小于 0 If n 100 Or n 0 Then TextBox4 MsgBox 请输入正确的分数 Cancel True 7 Else 输入成绩 并存入相应单元格内 k 为行号 3 为 C 列即 语 文 成绩 数学成绩 此处为 4 英语成绩 此处为 5 Cells k 3 Value TextBox4 Value End If End Sub 到此整个 VBA 的设计已基本完成 最终的使用流程是 进入工作簿选择要科目 例如 弹出相应科目的录入界面 输入学号 光标自动跳转到 语文成绩 处等待输入成绩 8 注 因为设定的 查询 语句支持 模糊查询 所以如果只在 一个年级内查询学号就可以只输入后三位 如果是全校范围内则需 要输入 6 位的学号 建议先按年级分类分别做数据表 以减少输入 学号的按键次数 学号的设定规则是 050102 前两位 05 为入学时 间即年级 中间两位 01 为班级 最后两位 02 为该生在该班的编号 学号必须唯一 输入该生的成绩 回车 焦点自动跳转到 下一个 按钮 直接 回车 自动清空所有文本框中的内容 光标回到 查询 文本框等待输入下一个学号 注意事项 因为用到了 VBA 代码 所以在 Excel 中要将 宏 的安全性降低为 中 级 默认是 高 如若不然无法正常使用 结束语 在 Excel 中使用 VB