Excel VBA在学时教务工作中的运用.docx

1、Excel VBA在学时教务工作中的运用Excel VBA在学校教务工作中的使用2010年6月6日1项目背景 12具体操作步骤 12.1考试用座位标签 12.1.1制作考试座位标签 12.1.2使用举例 32.2考试用门签 92.2.1 制作考试门签 92.2.2 使用举例 102.3考试用各班学生安排表 132.3.2使用举例 142.4考试用教师监考表 172.4.1 监考安排表 172.4.2使用举例 182.5学生成绩单 212.5.1 成绩单打印 212.5.2使用举例 222.6学校课表 252.6.1 课表生成 252.6.2使用举例 263展望 294.附录 304.1在Off

2、ice2007中畅通运行宏 304.2在Office 2003中畅通运行宏 321项目背景微软公司开发的Microsoft Office系列软件在全世界应用非常广泛，其中的Excel电子表格具有非常强大的数据处理功能，自Excel 2000版本起，附带了VBA （Visual Basic For Application）编程语言。该语言与Excel结合紧密，具有强大的数据（在计算机中，图片、声音、文字等信息均称为数据）处理功能。随着计算机的大量普及，使用Excel实行数据处理的人越来越多，宣汉中学在教务工作中常用Excel实行统考坐位标签的制作、学生成绩管理、教师排课等。可在很多时候它的强大功

3、能因为各方面的原因没能充分发挥出来。学校教务工作在学校日常工作中起着十分重要的作用。学校教务工作比较复杂，但现在计算机大量普及，数字技术飞速发展，如果将信息技术、网络技术、数字技术使用于学校日常教务工作，将减少大量错误，大幅度提升工作效率，让学校教务工作焕然一新。宣汉中学现有学生9100余人，在职教职工500余人。教务工作很多时候会涉及到大量的数据处理，例如：各年级每期至少实行两次统一考试，除此之外，全宣汉县还要统一实行一年一度的毕业会考、高考、中考、期末考试。在考试过程中要制作、打印、切割座位标签，工作量极大，并且稍不注意就要出错。如果借助Excel VBA实行数据处理，该工作将高效、简洁。

4、本文以Excel 2007为例介绍Excel VBA在教务工作中的使用。如果电脑上安装的是Excel 2003，操作方法略有不同，详情请参见附录4.2。2具体操作步骤2.1考试用座位标签2.1.1制作考试座位标签电脑上安装Excel2007软件后再拷贝教务系列文件夹到电脑。打开教务系列文件夹，打开座位标签文件夹，里面有两个Excel文件，一个是座位标签程序（A4纸模板）以前我们学校是用A4纸打印座位标签，另一个是座位标签程序（机读卡模板）我们学校每次统考学生都要填涂规格为133192的机读卡，这些机读卡用过之后就报废了。而机读卡纸张质量非常好，如果将其利用起来，一年可为学校节约大量的A4纸。打

5、开座位标签程序（机读卡模板）后如下图所示。（图2.1.11）如果打开过程中出现问题，解决方法请参考附录4.1。具体操作步骤：说明1. 请将黑色字全部删除后再将需要处理的数据拷贝到相对应位置；2. 鼠标左键单击视图-宏-查看宏-生成座位标签，即可生成14个考室的座位标签；3. 鼠标左键单击视图-宏-查看宏-打印座位标签，即可打印14个考室的座位标签；4. 鼠标左键单击视图-宏-查看宏-删除座位标签，即可删除14个考室的座位标签；5. 若要生成后面的座位标签，请在座位标签工作表中整行删除前14个考室的数据后再执行步骤2；6. 使用完毕后不用保存。如果电脑上安装的是Excel2003，运行方式见附录

6、4.2点击生成座位标签后计算机将立即生成14个考室的座位标签，其结果如下：（图2.1.12）如使用座位标签程序（A4纸模板）制作座位标签，其方法相仿。让计算机自动按顺序将座位标签打印（必要时可用生成的其中一张模板，制作一个封面）出来后，再用（厚层）钉书机将其钉好，最后用厚层切纸刀（如下图所示）将其切割。（图2.1.13）2.1.2使用举例宣汉中学高2010级要仿照高考规格实行统一考试，打开学生名单文件后，其界面如下：（图2.1.21）因为每个考室安排30学生，所以在D2输入1，D3输入=if(d230,d2+1,1)，回车后再见鼠标移到D3单元格的右下角，当其变为“”时，往下托，拖到最后一名学

7、生所在行。结果如图所示：（图2.1.22）在C2单元格中输入1，C3单元格中输入=if(d230,c2,c2+1)，回车后再将鼠标移到C3单元格的右下角，当其变为“”时，双击。结果如图所示：（图2.1.23）座号和考室有的是一位，有的是两位，为了对齐位数，在E2单元格输入：=if(len(c2)=1,”0”&c2,c2)，回车后再将鼠标移到E2单元格的右下角，当鼠标变为“”时，双击。在F2单元格内实行类似的操作，结果如下：（图2.1.24）考号要求有九位，前三位为101，第四、五位代表班，第六、七位代表考室，第八、九位代表座号。在G2单元格中输入：=”101”&b2&e2&f2，再将鼠标移到G

8、2单元格的右下角，当鼠标变为“”时双击。最后结果如下图：（图2.1.25）将得到的数据以数字格式选择性粘贴到座位标签制作（机读卡模板）相对应位置。如下图：（图2.1.26）按说明操作，生成座位标签后的界面如下：（图2.1.27）按说明打印座位标签，打印过程中可制作座位标签的封面：将名为30的工作表选中，在A1单元格中制作相对应的考试封面，注意设置内边框，便于切割。其结果如下：（图2.1.28）打印出来后装订、切割，如下图所示：（图2.1.29）（图2.1.210）（图2.1.211）最终切割后的结果如下图所示：（图2.1.212）2.2考试用门签2.2.1 制作考试门签打开教务系列文件夹，打开

9、考试门签文件夹，里面有两个Excel文件，一个是考试门签（A4纸模板）以前我们学校是用A4纸打印座门签，另一个是考试门签（B4纸模板），我们学校因为文印室有很多试卷是用B4纸印的，印刷过程中有一部分报废，但另一面还能够使用。将其收集起来可作B4纸门签的打印纸。打开考试门签（B4 纸模板），（如果打开过程中出现问题，请参照附录4.1）。打开后界面如下图所示：（图2.2.11）具体操作步骤如下：使用说明1.将考生数据中的黑色字全部删除后再将相对应数据拷贝到相对应位置；2.工作表门签模板上的斜体字删除后再写上相对应的内容；3.单击单击视图-宏-查看宏生成门签即可；4.如果对生成的门签不满意，可单击单

10、击视图-宏-查看宏删除门签；5.单击单击视图-宏-查看宏打印门签即可打印。备注：使用完后不保存2.2.2 使用举例打开2.2.1中的学生名单文件，得到如下界面：（图2.2.21）打开教务系列文件夹中的座位标签文件夹下的考试门签（A4纸模板）再将学生名单中的相关字段以数值方式拷贝到相对应字段，得到如下效果：（图2.2.22）再点击左下角的门签模板工作表，得：（图2.2.23）第一行标题改为：宣汉中学高2010级上期末考试（理科），因为每考室只有30名学生，选中第8行和第9行，将其整行删除，最终结果如下：（图2.2.24）点击视图宏查看宏生成门签，得到如下结果：（图2.2.25）点击视图宏查看宏打

11、印门签，即可打印。2.3考试用各班学生安排表2.3.1 制作各班考试安排表打开教务系列文件夹，打开各班考试安排文件夹，里面有两个Excel文件，一个是各班学生考试安排（A4纸）以前我们学校是用A4纸打印各班学生考试安排表，另一个是各班学生考试安排（B4纸），我们学校因为文印室有很多试卷是用B4纸印的，印刷过程中有一部分报废，但另一面还能够使用。将其收集起来可发挥其作用。打开考试门签（B4 纸模板），（如果打开过程中出现问题，请参照附录4.1的方法处理）。打开后界面如下图所示：（图2.3.11）具体操作步骤如下：说明1. 请将黑色字全部删除后再将需要处理的数据拷贝到相对应位置；2. 鼠标左键单击

12、视图-宏-查看宏-生成各班考试安排，即可生成各班考试安排表；3. 鼠标左键单击视图-宏-查看宏-打印各班考试安排，即可打印各班考试安排表；4. 鼠标左键单击视图-宏-查看宏-删除各班考试安排，即可删除各班考试安排表；6. 使用完毕后不用保存。2.3.2使用举例打开学生名单，得到如下界面：（图2.3.21）在考试地点一列中的H2单元格中输入：=Vlookup(c2,$j$2：$K&46,2),回车后再将鼠标移到单元格H2的右下角，当鼠标变为“”时双击，最得到如下结果：（图2.3.22）打开教务系列文件夹中的各班考试安排文件夹下的各班学生考试安排A4纸，将学生名单中的相对应内容以数值方式拷贝到该文

13、件的相对应位置中，得：（图2.3.23）点击左下角的A4纸模板工作表，在A1中输入：高2010级上期期末考试，点击视图宏查看宏生成各表学生安排，得到如下结果：（图2.3.24）2.4考试用教师监考表2.4.1 监考安排表打开教务系列文件夹，打开监考安排文件夹，里面有Excel格式的文件：排监考表，打开后界面如下：（图2.4.11）具体操作步骤如下：使用说明* 将监考表和监考堂数表除红色字外全部删除后再将监考表上的科目数与考室数按要求更改；* 如果监考表内某单元格不安排监考，则在该单元格输入任意一个字符占位，例如“*”；* 将姓名与监考堂数拷贝进监考堂数表中的相对应字段，单击视图-宏-查看宏-生

14、成监考表-执行即可。备注* 如果要将某位（些）教师安排在指定位置，请将监考堂数表中教师的姓名拷贝到监考表中，然后在监考堂数表中对这个（些）教师整行删除；* 如果对监考表排列不满意，则单击宏程序中监考堂数升序排列或监考堂数降序排列或监考堂数随机排列，再运行相对应的宏。2.4.2使用举例打开高2012级期末考试教师监考安排文件，点击左下角的高2012级教师期末考试教师监考表标签，可得考场56个，考试科目为8科，其中2456考场的地理和历史不安排教师监考，用”*”占位。界面如下：（图2.4.12）点击监考堂数标签，得如下界面：（图2.4.13）打开教务系列下的监考安排文件夹下的排监考表，其界面如下：

15、（图2.4.14）将其按要求实行修改为：（图2.4.15）点击左下角的监考堂数标签，并将高2012级期末考试教师安排表中监考堂数表中的相对应数据拷贝到排监考表中监考堂数表的相对应位置，得：（图2.4.16）其中龚路等19名教师必须安排监考第一堂和最后一堂，所以，在运行程序之前先作如下安排，并将监考堂数表中将这些教师所在的行整行删除。点击视图宏查看宏生成监考表，得：（图2.4.18）将生成的数据拷贝到高二上期第三次月考文件中的Sheet1工作表，得：（图2.4.19）2.5学生成绩单2.5.1 成绩单打印打开教务系列文件夹，打开成绩单生成文件夹，里面有两个Excel格式的文件：成绩单生成（A4纸

16、）和成绩单生成（机读卡）。因为很多学校都有大量学生用过的规格为133192的机读卡，由此用可使用学校用过的机读卡来打印学生成绩单。打开成绩单生成（机读卡）文件，打开后界面如下：（图2.5.11）操作步骤如下：说明1. 将黑色字全部删除后再将要处理的数据拷贝到相对应位置并按班号升序排列；2. 鼠标左键单击视图-宏-查看宏-生成成绩单，即可生成10个班的成绩单；3. 鼠标左键单击视图-宏-查看宏-打印成绩单，即可打印10个班的成绩单；4. 鼠标左键单击视图-宏-查看宏-删除成绩单，即可删除已生成的成绩单；5. 如果要生成另外的成绩单，可将学生数据中前10个班的成绩单整行删除后再执行步骤26. 使用

17、完毕后不用保存。成绩单打印出来后，可用（厚层）钉书机将其装订，根据需要也可做一个封面。然后再用厚层切纸刀实行切割。2.5.2使用举例打开高二上期第三次月考成绩，按班级字段升序排，得：（图2.5.21）打开教务系列文件夹中的成绩单生成（A4纸），得如下界面：（图2.5.22）将20个需要的字段拷贝到成绩单生成（A4纸）的相对应位置，得到如下界面：（图2.5.23）点击视图宏查看宏生成成绩单，得：（图2.5.24）仿照2.1.2的方法装订后再用切纸刀切割，最终得到如下的各班学生成绩单：（图2.5.25）2.6学校课表2.6.1 课表生成打开教务系列文件夹，打开课表生成文件夹，里面有两个Excel格

18、式的文件：生成班课表与生成教师课表。因为很多学校都有大量学生用过的规格为133192的机读卡，由此用可使用学校用过的机读卡来打印各班课程表和教师课程表。打开生成班课表后再点击左下角的总课表标签，出现如下界面：（图2.6.11）具体操作步骤如下：说明1.删除总课表和任课教师中的黑色字后再将相对应数据拷贝到相对应位置;2.可对模板中的上课时间,表格边框及字体和打印纸张实行设置;3.单击视图-宏-查看宏-生成课表可生成课表;4.单击试图-宏-查看宏-打印课表可打印课表;5.单击视图-宏-查看宏-删除课表可删除课表;6.使用完毕不保存.打开生成教师课表文件，再点击左下角的总课表标签，出现的界面如（图2

19、.6.11）所示具体操作步骤如下：说明1.删除总课表和任课教师表的黑色字后再将数据拷贝到相对应位置;2.可对模板中的上课时间和边框以及打印纸张大小实行设置;3.点击视图-宏-查看宏-生成课表可生成教师课表;4.点击视图-宏-查看宏-打印课表可打印教师课表;5.点击视图-宏-查看宏-删除课表可删除教师课表;6.使用完毕不用保存.2.6.2使用举例打开高二下期课程表文件并点击左下角的总课表标签，得：（图2.6.21）打开教务系列课表生成生成班课表并点击左下角的总课表标签，得（图2.6.11）.将相关数据转置粘贴到相对应位置，得如图（图2.6.22）所示的结果（图2.6.22）点击高二下期课程表文件

20、左下角的任课教师标签，得：（图2.6.23）同理，将相对应数据复制到生成班课表的任课教师工作表中的相对应位置，得：（图2.6.24）点击班课表左下角的模板标签，对其实行相对应调整，得：（图2.6.25）最后点击单击视图-宏-查看宏-生成课表即可。（图2.6.26）如果要生成各任课教师课表，操作方法与之类似。3展望该项目虽然解决了教务工作中存有的部分问题，可还有很多问题没有解决。随着科学技术的发展，计算机功能越来越强，操作方式越来越简单，就常用的Office系列软件来说，除了微软公司的Microsoft Office以外，还有国产Office如永中Office2009（宏以Java语言为基础）和

21、金山Office（有的版本带有VBA编程语言）等系列国产软件都能满足日常教务系列需求。当然，程序语言应用范围很广，它能够解决学校或其它单位或其它部门的很多问题。利用现代科技成果，新生事物持续涌现。数字技术的发展造就了数字化军队、数字化油田等。不久的将来，宣汉中学的教务必将实现数字化，宣汉中学将成为一个高效运作的数字化学校。4.附录4.1在Office2007中畅通运行宏在Office2007中如果打开带宏的文件出现问题，可将左上角office标志点开，（见图2.1.11）然后再单击出来的页面右下角Excel选项（图4.11）再点“信任中心”“信任中心设置”“宏设置”“启用所有宏”“确定”（图4

22、.1）（图4.13）确定（图4.14）4.2在Office 2003中畅通运行宏 如果电脑上安装的是Excel 2003，打开教务系列文件后出现下图（图4.2-1）所示情况，（图4.2-1）则可通过下列步骤让宏运行。 步骤一，选择“工具宏安全性”，设置宏的安全级为“低”，再关闭文件，然后重新打开，如果问题没有解决，则打开Excel后，依次选择“工具宏Visual Basic 编辑器”，在“Visual Basic 编辑器”中依次选择“插入模块”，在窗口中输入三行代码：sub macrodm()、MsBox “my macro”、End Sub（代码后没有标点）。然后依次选择“文件关闭并返回到

23、Microsoft Excel”，保存并关闭当前的Excel文件。这时再打开刚才文件，就出现安全警告，提示宏被禁用，这时我们需依次选择“工具宏安全性”，在弹出的对话框中，将“宏”安全级设置为“低”，并点击“确定”保存。 步骤二，安装 VBA 项目的数字证书 打开“控制面板添加或删除程序更改或删除程序”，在右侧找到“Microsoft Office Professional Edition 2003”，单击“更改”，然后会出现“Microsoft Office 2003安装”，依次选择“添加或删除功能下一步”，选中“选择应用程序的高级自定义”复选框，单击“下一步”，在“Office 共享功能”中

24、，单击“VBA 项目的数字证书”左侧的箭头，然后在列表中依次单击“从本机运行”，最后点击“更新”，插入office的安装盘开始安装。 步骤三，创建自己的数字证书 安装成功后。在Microsoft Office的启动程序菜单中依次选择“Microsoft Office 工具VBA 项目的数字证书”，在弹出的“创建数字证书”对话框的“您的证书名称”框中，键入数字证书的名称，点击“确定”就创建好了自己的数字证书。 步骤四，对宏添加签名 打开步骤二中创建的文件，依次选择“工具宏Visual Basic 编辑器”，在编辑器中再依次选择“工具数字签名”，在“数字签名”对话框中，单击“选择”按钮，选择步骤四中创建的数字证书，并“确定”返回。然后依次选择“文件关闭并返回到 Microsoft Excel”，保存并关闭该文件。 步骤五，将自己添加为可靠发行商 打开步骤五中的包含已签名的宏的文件。出现警告，选中“总是相信来自此发布者的宏”复选框，单击“启用宏”。再次打开该Excel文件，就不再显示任何安全警告了。你还能够通过“工具”菜单中的“宏”，来执行一下你所建立的宏。