利用EXCEL VBA自动判分

冯迎春

摘 要：考核环节是高校各类管理系统教学过程中的一个难题，文章以用友的U8为例，利用EXCEL和VBA，通过匹配学生表和标准答案表来判断得分，最终得出考核结果，以期为该类课程教学结果的考核提供一种思路。

关键词：自动判分；管理信息系统；VBA

中图分类号：G434 文献标志码：B 文章编号：1673-8454（2014）02-0075-02

随着IT技术的不断普及，以关系数据库为基础的管理系统在日常工作中得以广泛应用，各类管理系统的应用教学也逐渐成为高校专业课程教学的重要内容，但如何对该类教学结果进行判分成为一个难以解决的问题。

目前较常见的解决办法有两种：一种是通过开发模拟考试软件进行判分，即虚拟某一管理系统的界面和功能，按指定步骤完成操作即判断得分；另一种是专门开发某一管理系统的判分软件，根据操作结果来判分。但这两种方法均存在开发不易、灵活性差的问题。本文拟以用友U8的凭证和科目总账为例，通过取自数据库中的数据，利用EXCEL和VBA匹配学生结果和标准答案来完成这一判分过程。

一、相关工作簿

为完成自动判分过程，设计了pf.xls、bz.xls和xs.xls三个工作簿，pf.xls为判分信息文件，bz.xls为标准答案，xs.xls为学生结果，三表结构如图1、图2、图3。

1.判分信息文件：pf.xls

如图1所示，该工作簿仅有main工作表，A列为学生姓名，C：H列为判分信息，其中D列为需判分的数据库表名，F列为该项满分值，G列为该表标准答案的总行数，H列为该表中需判分的列号。

2.标准答案文件：bz.xls

如图2所示，该工作簿中的工作表与pf.xls中的D列相对应，如本例中有gl_accsum和gl_accvouch两个工作表。各工作表中A列为姓名，B列为得分，C列为组合结果，从D列开始是取自标准答案的数据库该表的内容，B和C列为空。

3.学生结果文件：xs.xls

如图3所示，学生结果文件结构同标准答案文件，区别在于各表内容取自学生数据库该表的内容。

二、判分过程

1.取标准答案和学生结果

先根据pf.xls中D列中各表名，用SQL语句取出标准答案和学生结果形成bz.xls和xs.xls两个工作簿（因各系统数据平台各异，为节省篇幅，该部分程序略）。

2.处理标准答案

将bz.xls中各表的内容根据操作结果进行调整，把不构成判分的行删除，有多种答案的情况增加相应行。将标准答案各表中判分总行数填入pf.xls的G列，该表总得分填入pf.xls的F列。如第2行的分数和得分点表示gl_accsum的标准答案中有55行，占总分的40分。

3.设置分值、判分列和判分项

将各表的判分列填入pf.xls的H列，以半角逗号间隔。如“凭证”所对应H3的值“e，f，h，u，w，x”，表示根据gl_accvouch表中这6列数据判断凭证结果的正确性。

4.用程序判分

在pf.xls中按ALT+F11进入VBE窗口后输入判分程序代码，执行后在xs.xls中新增一张”判分”表，表中内容为各学生的分项得分和总分。结果见图4。

三、判分程序代码

Sub 判分（）

dp1 = ThisWorkbook.Path & "＼"

fmax = Sheets（"main"）.Range（"d65536"）.End（xlUp）.Row '需判分表数

xmax = Sheets（"main"）.Range（"a65536"）.End（xlUp）.Row '学生人数

Workbooks.Open Filename：=dp1 & "bz.xls"

Workbooks.Open Filename：=dp1 & "xs.xls"

With Workbooks（"xs.xls"）

On Error Resume Next

Application.DisplayAlerts = False

.Sheets（"判分"）.Delete

.Sheets.Add.Name = "判分"

'增加一张判分表，再次运行前需先把该表删除

.Sheets（"判分"）.Range（"a1：a" & xmax） = Sheets（"main"）.Range（"a1：a" & xmax）.Value

End With

For i = 2 To fmax '逐表判分

With Workbooks（"pf.xls"）.Sheets（"main"）

SN = Trim（.Cells（i， 4）.Value） '表名

sc = Trim（.Cells（i， 5）.Value） '表中文名

mf = .Cells（i， 6）.Value '单项满分数

fs = mf / .Cells（i， 7）.Value '每一得分点分数

gs = Trim（"=" & Replace（.Cells（i， 8）.Value， "，"， "2&" & """_""" & "&"） & 2） '替换后的公式

End With

With Workbooks（"bz.xls"）.Sheets（SN）

'生成各项标准答案

.Range（"c2：c" & .Range（"a65536"）.End（xlUp）.Row）.Formula = gsendprint

End With

With Workbooks（"xs.xls"）.Sheets（SN）

'用match（）学生各表判分

xsmax = .Range（"a65536"）.End（xlUp）.Row

.Range（"c2：c" & xsmax）.Formula = gs

.Range（"b2：b" & xsmax）.Formula = "=if（iserror（match（C2，[bz.xls]" & SN & "！$C：$C，0）），0，1）"

End With

With Workbooks（"xs.xls"）.Sheets（"判分"）

'用sumif（）分项统计学生得分

.Cells（1， i + 1）.Value = sc & mf

.Range（Cells（2， i + 1）， Cells（xmax， i + 1））.Formula = "=round（SUMIF（" & SN & "！A：A，判分！A2，" & SN & "！B：B）*" & fs & "，2）"

End With

Next i

With Workbooks（"xs.xls"）.Sheets（"判分"）

'用sum（）汇总学生得分至判分表第2列

.Cells（1， 2）.Value = "总分"

.Range（Cells（2， 2）， Cells（xmax， 2））.Formula = "=SUM（c2：z2）"

End With

Workbooks（"xs.xls"）.Close Savechanges：=True

Workbooks（"bz.xls"）.Close Savechanges：=True

End Sub

四、本办法的优点

1.简单易用

本办法主要利用EXCEL查找函数的功能进行判分，只要用VBA通过SQL语句将数据取到工作簿中，完成判分表中相关设置，利用上述代码即可用本办法完成判分功能。

2.灵活性强

上述判分代码不受管理系统本身各表结构影响，具有通用性；同时需要进行判分的表、列和行均自行设计，可以根据需要随时调整。

3.结果可逆

在xs.xls的判分表中有学生的各项得分，各分项表中有得分明细，从中可以了解和分析学生的错误题目及原因，从而有效保证了教学效果。

以上方法在EXCEL2003、EXCEL2010和用友U8环境下测试通过。 （编辑：鲁利瑞）endprint

End With

With Workbooks（"xs.xls"）.Sheets（SN）

'用match（）学生各表判分

xsmax = .Range（"a65536"）.End（xlUp）.Row

.Range（"c2：c" & xsmax）.Formula = gs

.Range（"b2：b" & xsmax）.Formula = "=if（iserror（match（C2，[bz.xls]" & SN & "！$C：$C，0）），0，1）"

End With

With Workbooks（"xs.xls"）.Sheets（"判分"）

'用sumif（）分项统计学生得分

.Cells（1， i + 1）.Value = sc & mf

.Range（Cells（2， i + 1）， Cells（xmax， i + 1））.Formula = "=round（SUMIF（" & SN & "！A：A，判分！A2，" & SN & "！B：B）*" & fs & "，2）"

End With

Next i

With Workbooks（"xs.xls"）.Sheets（"判分"）

'用sum（）汇总学生得分至判分表第2列

.Cells（1， 2）.Value = "总分"

.Range（Cells（2， 2）， Cells（xmax， 2））.Formula = "=SUM（c2：z2）"

End With

Workbooks（"xs.xls"）.Close Savechanges：=True

Workbooks（"bz.xls"）.Close Savechanges：=True

End Sub

四、本办法的优点

1.简单易用

本办法主要利用EXCEL查找函数的功能进行判分，只要用VBA通过SQL语句将数据取到工作簿中，完成判分表中相关设置，利用上述代码即可用本办法完成判分功能。

2.灵活性强

上述判分代码不受管理系统本身各表结构影响，具有通用性；同时需要进行判分的表、列和行均自行设计，可以根据需要随时调整。

3.结果可逆

在xs.xls的判分表中有学生的各项得分，各分项表中有得分明细，从中可以了解和分析学生的错误题目及原因，从而有效保证了教学效果。

以上方法在EXCEL2003、EXCEL2010和用友U8环境下测试通过。 （编辑：鲁利瑞）endprint

End With

With Workbooks（"xs.xls"）.Sheets（SN）

'用match（）学生各表判分

xsmax = .Range（"a65536"）.End（xlUp）.Row

.Range（"c2：c" & xsmax）.Formula = gs

.Range（"b2：b" & xsmax）.Formula = "=if（iserror（match（C2，[bz.xls]" & SN & "！$C：$C，0）），0，1）"

End With

With Workbooks（"xs.xls"）.Sheets（"判分"）

'用sumif（）分项统计学生得分

.Cells（1， i + 1）.Value = sc & mf

.Range（Cells（2， i + 1）， Cells（xmax， i + 1））.Formula = "=round（SUMIF（" & SN & "！A：A，判分！A2，" & SN & "！B：B）*" & fs & "，2）"

End With

Next i

With Workbooks（"xs.xls"）.Sheets（"判分"）

'用sum（）汇总学生得分至判分表第2列

.Cells（1， 2）.Value = "总分"

.Range（Cells（2， 2）， Cells（xmax， 2））.Formula = "=SUM（c2：z2）"

End With

Workbooks（"xs.xls"）.Close Savechanges：=True

Workbooks（"bz.xls"）.Close Savechanges：=True

End Sub

四、本办法的优点

1.简单易用

本办法主要利用EXCEL查找函数的功能进行判分，只要用VBA通过SQL语句将数据取到工作簿中，完成判分表中相关设置，利用上述代码即可用本办法完成判分功能。

2.灵活性强

上述判分代码不受管理系统本身各表结构影响，具有通用性；同时需要进行判分的表、列和行均自行设计，可以根据需要随时调整。

3.结果可逆

在xs.xls的判分表中有学生的各项得分，各分项表中有得分明细，从中可以了解和分析学生的错误题目及原因，从而有效保证了教学效果。

以上方法在EXCEL2003、EXCEL2010和用友U8环境下测试通过。 （编辑：鲁利瑞）endprint