基于Excel VBA及函数的成绩上报单的制作

石晓玲，杨立功

（泰州职业技术学院，江苏泰州 225300）

基于Excel VBA及函数的成绩上报单的制作

石晓玲，杨立功

（泰州职业技术学院，江苏泰州 225300）

介绍了在Excel下利用内置函数及VBA程序制作班级学生成绩系统，实现数据的自动化处理，包括基本数据的自动导入、总评成绩的自动合成及统计数据的自动生成等。使用户能够快速完成特定任务，提高工作效率和准确性。

Excel VBA；函数；统计

VBA是Visual Basic for Application的简称，是集成在M icrosoft Office应用程序中的一种程序设计语言，能够实现办公自动化，从而极大地提高工作效率。VBA技术为办公自动化用户提供了面向对象的程序设计方法及相当完整的程序设计语言。其独特之处在于它由应用程序控制，反过来又可以增强应用程序的功能。

Microsoft Excel是微软公司的办公软件Microsoft Office的组件之一，用于进行数据的处理、统计分析和辅助决策。它功能强大，不但提供了打印，文件处理，格式化和文本编辑等基本功能，而且内置了大量函数，已被用户广泛接受和使用。

用户可以在Excel平台基础上，利用VBA进行二次开发，增强Excel的自动化能力，更高效地完成特定任务。

1 任务分析

高校每学期在课程结束后，要求教师向授课学生所在院系提供班级学生成绩上报单（见图1）。该上报单应给出每个学生的各项基本成绩信息，并且要提供班级成绩分析。其中总评成绩需根据各课程性质，按平时、实践及卷面比例计算得出；学生成绩统计表部分需按总评成绩统计出各分数段人数及平均分。上述工作较为繁琐，容易出错，为减轻教师重复劳动负担，提高工作效率，本文利用Excel内置函数及VBA技术提供了《学生成绩上报单》填报系统。将该系统挂到教务处网站供教师随时下载使用，使教师在几分钟内就可以便捷地完成班级成绩单的制作。

2 实现步骤

2.1 系统设计

在Excel工作簿sheet1表中绘制如图1所示《泰州职业技术学院学生成绩上报单》空表格格式。按照每列33人，总人数不超过66人设计表格。建立“jwxt”工作表，作为添加全校班级学生信息的数据源，该表由管理员导入数据，用户打开系统时被隐藏，A列为班级（bj），B列为学号（xh），C列为姓名（xm） 字段信息。

2.2 窗体及代码设计

用户打开文件，系统自动运行work book_open模块]，主要代码（各详细代码略）如下[1]：

打开sheet1表后需要进行工作项目的选择及基本信息的录入。首先设计对应窗体，如图2所示。

图2 用户开始界面

其中第一个按钮将原来表格中的数据清除以便输入新的班级数据，第二个按钮可以先将原表格中的数据保存为以班级名称为名的文件，然后再清空数据表[2]。保存文件代码如下：

设计第三个按钮为进入班级成绩录入方式。用户确认输入的班级正确后，将在sheet1表中立即加载该班级学生的学号和姓名信息（见图3）。添加班级学生信息代码如下：

图3 选择班级窗口

图4 填写成绩比例

在弹出的“填写成绩比例”窗口输入平时、实践及卷面成绩占总评成绩的比例（见图4），数据将被加载到sheet1表第5行。至此成绩上报表中学生基本信息添加完毕。

2.3 总评成绩和统计数据的生成

各任课教师在表格相应单元格内填写每个学生的平时、实践和卷面成绩，表格将立即自动生成总评成绩和统计数据。这里可以充分运用Excel强大的内置函数的功能去完成，方便而高效。例如在G 6单元格中插入公式：

实现功能：卷面成绩不为空时，按比例计算出总评分（当总评分>59时记为60）。其余单元格公式采用自动填充方式填写。

统计表中分段人数的统计，使用COUNTIF函数。C42单元格插入公式为：

3 保护工作表

为防止误操作，提高系统数据的安全性，并保证所有用户上报报表格式的一致，有必要对单元格格式和公式进行保护。对本系统而言，表格中需要用户填写的只有（D6:F38） 及（K6:38）区域，其余单元格的值由公式自动计算填充给出，这部分单元格的格式和其中的公式不能被修改或删除。因此，在系统设计结束前必须对上述单元格进行保护和公式隐藏。Excel系统平台中提供了这部分功能，通过菜单即可实现。

4 小结

（1）本填报系统采用了交互式的工作方式，简洁明确，具有友好精良的操作界面。通过各个工作界面提示，用户可以清楚填写要求，迅速完成表格的制作。

（2）本文中的源数据由管理员从教务系统复制过来。如果数据量大并且结构复杂，也可以通过Excel提供的MS Query技术从外部数据库和文件中检索数据。

（3） 总评成绩和统计数据也可以通过VBA程序实现，这里不再赘述。但笔者认为，采用内置函数更为直观，运行速度也更快，当用户输完原始数据的同时就完成了表格的制作。

（4）要运行上述所有功能，需将“宏”安全性设置为“中”，打开该文件时选择“启用宏”。

本文通过VBA语言进行二次开发，提供优良的用户界面，完成Excel工作表之间数据交互，快捷地加载各班级学生名单；利用Excel内置函数完成计算和统计。这样的工作方式让用户在熟悉的Excel平台下工作，提高了工作效率和应用水平。

[1]Excel Home.Excel VBA实战技巧精粹[M].北京:人民邮电出版社,2008.

[2]黄海.Excel VBA语法与应用辞典[M].北京:中国青年出版社,2009.

The Design of Transcript Based on Excel Function and Excel VBA

SHI Xiao-ling,YANG Li-gong
（Taizhou Polytechnic College,Taizhou Jiangsu 225300,China）

The paper describes the design of transcript based on Excel function and Excel VBA,and the implementation of data processing automaticity,including preparing data and inputing data,receiving the final course grades and the statistic data.It helps the user to accomplish the special tasks efficiency.

Excel VBA;function;statistics

中国分类号：TP311.1

B

1671-0142（2011）05-0040-03

石晓玲（1973-）,女,江苏泰州人,副教授,硕士.

（责任编辑李冠楠）