利用EXCEL管理学生信息及批量打印的实现

摘要：MS Excel内置了VBA（Visual Basicfor Applications）系统开发工具，利用 VBA 可以方便地调用和定制主应用程序对象。该文利用Excel及其函数和VBA的简单编程设计，使学生信息管理和信息卡的批量打印自动完成，减少重复工作，实现高效办公。使非专业编程人员能够解决复杂计算及繁杂数据管理。

关键词：ExcelVBA；学生信息；批量打印

中图分类号：TP311.1 文献标识码：B 文章编号：1009-3044（2018）12-0164-03

1 概述

EXCEL是Microsoft office办公软件的重要功能模块之一，Excel 不仅具有强大的电子数据表、图表和数据库功能，还具有很强的数据分析性能、制作报表等功能，另外它还内置了VBA（Visual Basicfor Applications）的系统开发工具，利用 VBA 可以方便地调用和定制主应用程序对象。用来扩展Microsoft Office 应用程序功能。 使用者根据通过VBA 宏代码的编写 ，创建自己的解决方案，自动完成重复工作，使工作效率提高，实现高效办公。使非专业编程人员能够解决复杂计算及繁杂数据管理的理想工具软件。

在学校学生学籍管理中，需要对学生信息、学生课程成绩进行处理，对各种报表、档案、通知单等进行批量打印或套打。本文通过利用Microsoft Office 的电子表格软件 Excel 及其函数和 VBA 的简单编程功能，实现Excel对学生信息管理、成绩处理以及成绩报告单的打印的方法进行探讨，从而实现在同一Excel表格内实现信息、成绩管理与快速打印成绩报告单的方法，使信息管理更准确高效，工作效率进一步提高。

2 建立学生信息表

根据需要建立包括学生学号、姓名、出生日期、身份证号、课程成绩、成绩排名等信息，建立表如图1的“信息表”的工作表。

手工录入或从其他文件中复制学号、姓名、身份证号、电话、课程成绩等，出生日期、平均成绩和综合排名等可通過函数运算进行自动填充。

2.1 通过身份证号自动填充出生日期

Excel中MID函数是Visual Basic和Excel中的一个字符串函数，作用是从一个字符串中截取出指定数量的字符。DATE函数是关于日期的函数，它返回的是特定日期的序列号，通常当单元格的格式为“常规”时，那么返回的结果是一个日期格式。

每个18位身份证号7～10位为出生年，11～12位为出生月，13～14位为出生日，因而用MID和DATE函数结合就能提取出身份证号码的出生年月日，在出生日期所在单元格“F3”中输入函数公式“=DATE（MID（G3，7，4），MID（G3，11，2），MID（G3，13，2））”后按“回车”，有时回车后“F3”并没有变化，还是原公式，这时需要把F列选中，点击右键，在下拉菜单中选择“设置单元格格式”，在“数字”中选“日期”，再在右侧“类型”选项中选所需要的日期类型（图2），然后用“自动填充柄”下拉，就可自动填写下面的所有对应的出生日期（以后自动填充均用“自动填充柄”下拉填充）。

2.2 通过自动AVERAGE自动计算平均成绩

Excel中AVERAGE函数是EXCEL表格中的计算平均值函数，参数可以是数字，或者是涉及数字的名称、数组或引用，如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。

在平均成绩所在单元格“R3”中输入函数公式“=AVERAGE（J3：P3）”，然后回车，就得到本行学生的平均成绩。然后用“自动填充柄”下拉，就可自动填写下面的所有对应的成绩排名。

2.3 通过函数RANK自动计算成绩排名

Excel中RANK函数是排名函数。rank函数最常用的是求某一个数值在某一区域内的排名。

在成绩排名所在单元格“S3”中输入函数公式“=RANK（R3，$R$3：$R$20）”，然后回车，然后用“自动填充柄”下拉，就可自动填写下面的所有对应的成绩排名。

3 建立“学生信息卡”

3.1 建立信息卡

在同一工作簿中，根据需要建立新的“信息卡”的（图3）工作表。

3.2 建立信息卡引用控件

在“信息卡”的右侧，根据制作“信息表”与“信息卡”的引用控件（图4）。在“M4”单元格中输入“=MATCH（M2，信息表！A3：A2249，0）+2”函数。

3.3 “信息卡”对“信息表”数据的引用

在“信息卡”中需要填写姓名的B4单元格中输入“=INDEX（信息表！C：C，信息卡！$M$4，1）”函数式，然后回车就可自动填入本行学生的姓名，同样在D4单元格中输入“=INDEX（信息表！D：D，信息卡！$M$4，1）”函数式，同样自动填写本学生的性别。根据本方法填写其他需要填写内容，在填写中可以复制本公式然后只要对公式中“信息表！C：C”，根据信息表所在的列对应进行修改。在引用的出生日期中可能发现其中填写的并不是我们需要的“XXXX年X月X日”而是一些数字，这时只要右键点击本单元格，选“设置单元格格式”选项，设置数字格式为所需要的日期类型（图2）。另外在打印日期G3单元格中输入“=TODAY（）”就会得到打印的及时日期，不用每次打印时重新输入。

3.4 数值调节钮控件的制作

点击“开发工具”标签，点“插入”，在“表单控件”中点击“数值调节钮”（如图5），按住右键在L2、L3单元格区域画出调节钮（如图5图4），点击画好的调节按钮右键，选择“设置控件格式”，在“设置控件格式”中点控制选项卡设置最大值、最小值，在“单元格链接”中输入“$M$2”，即本控件为上下调节$M$2单元格数值。这样就可用控件的上下箭头改变$M$2单元格的数值，随之也改变信息卡中的可变量。

4 打印信息卡的设置

4.1 打印信息卡的设置

在J7：M9单元格区域建立如图表格并设置最大值、最小值、起始序号、截至序号等（图6），其中在最大值的M9单元格中输入函数“=MAX（信息表！$A$3：$A$1994）”即自动找到“信息表”中的最大有效数值。

4.2 打印信息卡的设置

点击“开发工具”，“插入”，“表单控件”中的“按钮”图标（如图7），然后在J6单元格画一按钮，并出现“指定宏”对话框（如图7）。点“新建”按钮，即进入VBA程序编写窗口，在此框内进行“打印当前页”和“打印全部”的编程，程序编码（如图8）。这样就可通过图8控制打印页码和页数。

4.3 打印页面设置

由于需要打印的页面和控制页面在同一工作表，这样在打印时也把控制页也打印出来，我们只要把需要打印的“学生信息卡”设置为打印区域，并根据实际需要对页面进行设置。

5 总结

在实际工作中，利用word的“邮件合并”功能，对Excel数据源引用到word模板中，实现证卡等的批量生成和批量打印也是常用的方法，但利用本文Excel管理和批量打印学生信息与“邮件合并”相比，它具有如下优点：

“邮件合并”在使用上必須在Word和Excel两个功能软件交替进行，使用起来较麻烦，而本文的所有操作只在Excel系统中完成，减少了系统之间的切换。

利用本系统学生信息数据更新，信息卡的打印页面也随时更新，随时选择打印，而Word 的邮件合并只有在所有信息数据修改完后，再进行合并、再批量打印，并且合并出来的新文档打印页面不能选择，不能单页打印，只能把所有页面进行打印，非常浪费。

另外与其他采用高级编程语言实现的系统相比，利用Excel 和 Vba 技术，实现起来非常方便，即使是没有编程经验者也可轻松操作。

参考文献：

[1] 李蓉鑫.基于ExcelVBA的带相片证书批量打印系统的设计与实现[J].电脑编程与维护，2007（6）.

[2] 张继瑄.利用ExcelVBA功能实现带照片证书的及时打印[J].办公自动化，2014（12）.

[3] 张继瑄.利用EXCEL函数功能从身份证号提取学生基础学籍信息[J].电脑知识与技术，2014（33）.