Excel宏在计算高校专业学分绩排名中的应用
2018-11-01李欣乐
李欣乐
摘要： 综合教务管理系统基本已经在全国高校普及。虽然目前大部分教务管理系统都可以对学生的平均学分绩进行计算，但是针对专业学分绩，如只计算所有专业课程学分绩或者只计算某些指定课程学分绩，仍然需要从系统中导出成绩后另行计算，工作繁重。该文通过提供一种EXCEL中VBA（Visual Basic for Application）编写宏代码的方法，设计人机界面，简化此类特殊学分绩的计算，分类结果，清晰排序，提高教学管理的质量和效率。
关键词： excel宏；VBA；学分绩计算
中图分类号：TP3 文献标识码：A 文章编号：1009-3044（2018）18-0232-05
The Application of Excel Macro in the Calculation of Major GPA Ranking in Colleges and Universities
LI Xin-yue
（Nanjing University， Nanjing 210093， China）
Abstract： The comprehensive education administration system has been popularized in colleges and universities nationwide. Although most of the current education management systems are able to calculate the Grade Point Average （GPA） of students， there is still a lot of work to be done for major GPA， such as for all Compulsory courses or for certain specified courses，the records still need to be exported from the system and calculated separately. This article provides a method of writing macro code for VBA （Visual Basic for Application） in EXCEL， designing a human-computer interface， simplifying the calculation of such special GPA， classifying the results， sorting clearly， and improving the quality and efficiency of teaching management.
Key words： Excel Macro； VBA； GPA calculation
EXCEL軟件是一款功能强大的数据处理办公软件，它可以分析信息并管理电子表格或网页中的数据信息列表与数据资料图表制作，可以实现许多方便的功能，广泛地应用于高校信息化管理中。
Visual Basic for Applications（VBA）是Visual Basic的一种宏语言，主要能用来扩展Windows的应用程式功能，特别是Microsoft Office软件，其中包括Excel、PPT、Word、Outlook等。VBA应用于EXCEL可以实现：
（1）规范用户的操作，控制用户的操作行为；
（2）操作界面人性化，方便用户的操作；
（3）多个步骤的手工操作通过执行VBA代码可以迅速的实现；
（4）利用VBA可以Excel内轻松开发出功能强大的自动化程序。
笔者将以较为特殊的推免学分绩计算为例，通过录制宏，在EXCEL界面上设计按钮BUTTON功能既迅速实现对原始成绩数据的处理并完成学分绩计算以及排序。该程序已经用于计算近三年推免学分绩，经过核对，结果完全准确，但人工时间却是缩减10倍以上， 大大提高了工作效率和质量。
1 专业推免学分绩计算
[每门课学分绩=考试成绩20×学分数]
[平均学分绩=所有必修课+所有专业核心课或专业重点课学分绩所有必修课+所有专业核心课或专业重点课学分数]
[专业课学分绩=所有专业核心课或专业重点课学分绩所有专业核心课或专业重点课学分数]
[推免学分绩=平均学分绩+专业课学分绩2]
要求学分绩统一以学生第一次通过的课程考试成绩计算（如第一次考试不及格者，按不及格原始成绩计算学分绩）。返校未满一年的交换生以现有课程成绩计算。仍然有必修课成绩不及格的学生标红
按此方式演算学分绩，有三个特殊点以及难点
（1）准确找到第一次的成绩。
（2）判定交换生课程，此类课程0分或空缺均不计入学分绩并在结果中显示出来。
（3）所有必修课程不及格的学生标红。
2 界面设计
为了实现上述功能，我们要利用EXCEL宏设计一个方便操作的人机界面，完成对原始数据库的筛选，清晰明确显示所有学生推免课程的成绩及最终推免学分绩的结果。
1）成绩数据库
即未经处理的学生成绩数据库，包含所有学生的所有课程成绩。我们就是要将需要的信息从这个数据库中提取出来进行计算。如图1 示。
2）设计功能按钮
按照上述思路，笔者设计了两个功能按钮。如图2示。
读取考试成绩按钮—单击按钮，选择需要处理的学生成绩数据库，页面会显示数据库中所有课程列表，然后由操作人选择是否是必修课程和是否是专业核心课程，完成第一步的数据筛选。
计算学分绩按钮—单击按钮，后台按照上述学分绩计算规则计算所有学生的推免学分绩。
3）结果显示
程序运行完毕，生成两个表格页result和sortlist
Result表— 按照学生学号排序显示所有纳入计算课程的第一次成绩。所有必修课程不及格的学生标红。
Sortlist表—按照推免学分绩从高到低排序，并分别显示必修课平均学分绩和专业核心课平均学分绩。
Result表格和Sortlist表格数据是相关联的，改动Result表格中的任一成绩，将会直接影响Sortlist表格中的学分绩数据。这样设定也是为了方便纠错检查，校对基点，灵活改动。
3 录制宏，编写VBA
3.1 系统设计
3.2 系统环境设置
1）从成绩数据库中读取成绩数据库的格式，确定各列保存的数据内容。
包含学号、学生姓名、所属院系、课程编号、课程名称、学分、课程类别、学期、成绩类别、总评、备考、备考2。
ForreadCol = 1 ToscoreSht.Range（"A1"）.SpecialCells（xlCellTypeLastCell）.Column
tmpStr = Trim（scoreSht.Cells（1， readCol））
IfLen（tmpStr） = 0 Then
GoToNextTitleCol
EndIf
IftmpStr = SCORE_COL_STUNO Then
readStuNoCol = readCol
ElseIftmpStr = SCORE_COL_STUNM Then
readStuNmCol = readCol
ElseIftmpStr = SCORE_COL_COUNO Then
readCouNoCol = readCol
ElseIftmpStr = SCORE_COL_COUNM Then
readCouNmCol = readCol
ElseIftmpStr = SCORE_COL_COUPOINT Then
readCouPointCol = readCol
ElseIftmpStr = SCORE_COL_COUTERM Then
readCouTermCol = readCol
ElseIftmpStr = SCORE_COL_COUTYPE Then
readCouTypeCol = readCol
ElseIftmpStr = SCORE_COL_SCOTYPE Then
readScoTypeCol = readCol
ElseIftmpStr = SCORE_COL_SCORE Then
readScoreCol = readCol
ElseIftmpStr = SCORE_COL_COMMENT Then
readCommentCol = readCol
ElseIftmpStr = SCORE_COL_COMMENT2 Then
readComment2Col = readCol
EndIf
NextTitleCol：
Next
2）從成绩数据库中读取学生各学习各门课的成绩，对于初次考试不及格的课程，从【备考2】列中读取原始考试成绩。
（1）读取数据时，对于没有学号、没有学期、没有课程编号，或者学期数据不是数字的脏数据进行忽视。
IfLen（strStuNo） = 0 Or Len（strCouNo） = 0 Or Len（strCouTerm） = 0 _
OrstrCouType = EXAM_COUTYPE_MINOR Then
hasSkipFlg = False
GoToNextScoreRow
EndIf
IfNotIsNumeric（strCouTerm） Then
hasSkipFlg = False
GoToNextScoreRow
EndIf
IfstrScore = EmptyThen
strScore = "0"
EndIf
strOriginalScore = GetOriginalScore（strScore， strComment， strComment2）
（2）对于成绩数据库中的[备考]内容为[出国]、[交换]、[交流]字样的课程，将该学生该门课程的成绩进行标记。
IfInStr（1， strComment， EXAM_ABROAD1， vbTextCompare） > 0 _
OrInStr（1， strComment， EXAM_ABROAD2， vbTextCompare） > 0 _
OrInStr（1， strComment， EXAM_ABROAD3， vbTextCompare） > 0 Then
tmpScore = ABROAD_SCORE
GoToHasOriginalScore
EndIf
（3）对于[备考2]内容中包含[补考]内容时，读取[备考2]中的原成绩作为实际计算成绩
IfmakeupPos> 0 Then
IforiginalPos> 0 Then
subStartPos = originalPos + Len（EXAM_ORIGINAL_SCORE）
ElseIforiginalSubPos> 0 Then
subStartPos = originalSubPos + Len（EXAM_ORIGINAL_SCORE_SUB）
EndIf
ElseIforiginalPos> 0 Then
subStartPos = originalPos + Len（EXAM_ORIGINAL_SCORE）
Else
GoToHasOriginalScore
EndIf
tmpScore = Empty
For w = subStartPosToLen（strComment2）
tmpStr = Mid（strComment2， w， 1）
IfIsNumeric（tmpStr） Then
hasNumberFlg = True
tmpScore = tmpScore&tmpStr;
Else
IfhasNumberFlgThen
IftmpStr = NUMBER_POINT Then
tmpScore = tmpScore&tmpStr;
Else
GoToHasOriginalScore
EndIf
EndIf
EndIf
Next
（4）对于忽视的数据保存在[ErrorRecord]sheet中，以背检查。
IfNothasSkipFlgThen
Sheet3.Range（"A" &writeSkipRow;）.Value = readRow
Sheet3.Range（"B" &writeSkipRow;）.Value = "'" &strStuNo;
Sheet3.Range（"C" &writeSkipRow;）.Value = strStuNm
Sheet3.Range（"D" &writeSkipRow;）.Value = "'" &strCouNo;
Sheet3.Range（"E" &writeSkipRow;）.Value = strCouNm
Sheet3.Range（"F" &writeSkipRow;）.Value = strCouPoint
Sheet3.Range（"G" &writeSkipRow;）.Value = "'" &strCouTerm;
Sheet3.Range（"H" &writeSkipRow;）.Value = strScoType
Sheet3.Range（"I" &writeSkipRow;）.Value = strScore
Sheet3.Range（"J" &writeSkipRow;）.Value = strComment
Sheet3.Range（"K" &writeSkipRow;）.Value = strComment2
writeSkipRow = writeSkipRow + 1
EndIf
3）将读取的成绩按照学号和课程编号保存在[PointList]sheet中，保存是如果已經有成绩数据存在，比较该成绩获得的学期。比较学期数据的大小，保存最早学期的成绩，准确找到第一次的成绩。
tmpStr = Sheet2.Cells（targetRow， targetCol）
IfLen（tmpStr） > 0 Then
tmpSepPos = InStr（1， tmpStr， NUMBER_SEPERATOR， vbTextCompare）
tmpTerm = Mid（tmpStr， 1， tmpSepPos - 1）
tmpScore = Mid（tmpStr， tmpSepPos + 1， Len（tmpStr） - tmpSepPos）
IfCInt（tmpTerm） SetScore2Cell = False IfCInt（strOriginalScore）>= STANDARD_SCORE _ OrCInt（strScore）>= STANDARD_SCORE Then Sheet2.Cells（targetRow， targetCol）.Interior.ColorIndex = 2 EndIf ExitFunction ElseIfCInt（tmpTerm）>= CInt（strCouTerm）AndCInt（strOriginalScore） = ABROAD_SCORE Then SetScore2Cell = False ExitFunction EndIf EndIf Sheet2.Cells（targetRow， targetCol） = strCouTerm& NUMBER_SEPERATOR &strOriginalScore;
IfCInt（strOriginalScore）>= STANDARD_SCORE _
OrCInt（strScore）>= STANDARD_SCORE Then
Sheet2.Cells（targetRow， targetCol）.Interior.ColorIndex = 2
Else
IfLen（tmpStr） = 0 Then
Sheet2.Cells（targetRow， targetCol）.Interior.ColorIndex = 3
EndIf
EndIf
4）根据[Compute]sheet中记录的所有课程列表中标注的必修课程、专业核心课程，从[PointList]sheet中获取学生相应课程的成绩，保存在[Result]Sheet中。
FunctionCopyCourseScore（ByValtargetReqColArrAsVariant， ByValtargetProfColArrAsVariant） AsLong
Dim w， r， c AsLong
DimwriteColAsLong
writeCol = WRITE_COURSE_START_COL
For w = 0 ToUBound（targetReqColArr）
IfNotMergeEngCourse（targetReqColArr（w）， writeCol - 1） Then
CopyCourseColtargetReqColArr（w）， writeCol， COURSE_TYPE_REQUIRED
writeCol = writeCol + 1
EndIf
Next
For w = 0 ToUBound（targetProfColArr）
IfNotMergeEngCourse（targetProfColArr（w）， writeCol - 1） Then
CopyCourseColtargetProfColArr（w）， writeCol， COURSE_TYPE_PROFESSION
writeCol = writeCol + 1
EndIf
Next
For r = WRITE_SCORE_START_ROW ToSheet2.Range（"A1"）.SpecialCells（xlCellTypeLastCell）.Row
For c = WRITE_COURSE_START_COL TowriteCol - 1
If Sheet4.Cells（r， c）.Interior.ColorIndex = 3 Then
Sheet4.Cells（r， 1）.Interior.ColorIndex = 3
Sheet4.Cells（r， 2）.Interior.ColorIndex = 3
GoToNextStudent
EndIf
Next
NextStudent：
Next
CopyCourseScore = writeCol - 1
EndFunction
5）根據[Result]Sheet保存的必修课和专业核心课成绩，计算所有学生[每门课学分绩=考试成绩20×学分数]
ForlCol = WRITE_COURSE_START_COL TomaxCol
coursePointAddr = Sheet4.Cells（3， lCol）.Address
ForlRow = writeStartRowTowriteStartRow + scoreNum - 1
scoreRow = WRITE_COURSE_START_ROW + lRow - writeStartRow
scoreAddr = Sheet4.Cells（scoreRow， lCol）.Address
Sheet4.Cells（lRow， lCol）.Formula = "=IF（ISBLANK（" &scoreAddr;& "）， """"， " &scoreAddr;& "/" & CREDIT_DIVISOR & "*" &coursePointAddr;& "）"
Next
Next
6）根据所有学生的每门课的学分绩，计算所有学生的专业学分、必修课学分、专业课学分绩、基础学分绩。
ForlRow = writeStartRowTowriteStartRow + scoreNum - 1
scoreStartAddr = Sheet4.Cells（lRow， WRITE_COURSE_START_COL）.Address
scoreEndAddr = Sheet4.Cells（lRow， maxCol）.Address
Sheet4.Cells（lRow， maxCol + 1）.Formula = "=SUMIF（" &scoreStartAddr;& "：" &scoreEndAddr;& "， "">=0""， " &courseStartAddr;& "：" &courseEndAddr;& "）"
Sheet4.Cells（lRow， maxCol + 2）.Formula = "=SUMIFS（" &courseStartAddr;& "：" &courseEndAddr;& "，" &courseProfStartAddr;& "：" &courseProfEndAddr;& "， " & COURSE_TYPE_PROFESSION & "， " &scoreStartAddr;& "：" &scoreEndAddr;& "， "">=0""）"
Sheet4.Cells（lRow， maxCol + 3）.Formula = "=SUM（" &scoreStartAddr;& "：" &scoreEndAddr;& "）/" & Sheet4.Cells（lRow， maxCol + 1）.Address
Sheet4.Cells（lRow， maxCol + 4）.Formula = "=SUMIF（" &courseProfStartAddr;& "：" &courseProfEndAddr;& "， " & COURSE_TYPE_PROFESSION & "， "&scoreStartAddr;& "：" &scoreEndAddr;& "）/" & Sheet4.Cells（lRow， maxCol + 2）.Address
Sheet4.Cells（lRow， maxCol + 5）.Formula = "=（" & Sheet4.Cells（lRow， maxCol + 3）.Address & "+" & Sheet4.Cells（lRow， maxCol + 4）.Address & "）/2"
Next
7）計算每个学生必修课平均学分绩、专业核心课平均学分绩、基础学分绩以及综合学分绩
ForlRow = WRITE_CREDIT_START_ROW TomaxRow - WRITE_SCORE_START_ROW + 1
creditAreaAddr = Sheet4.Cells（writeStartRow + lRow - WRITE_CREDIT_START_ROW， maxCol + 3）.Address
Sheet5.Range（"A" &lRow;）.Formula = "=ROW（）-1"
Sheet5.Range（"D" &lRow;）.Formula = "=Round（" & Sheet4.Name & "！" &Sheet4.Cells;（writeStartRow + lRow - WRITE_CREDIT_START_ROW， maxCol + 3）.Address & "， 4）"
Sheet5.Range（"E" &lRow;）.Formula = "=Round（" & Sheet4.Name & "！" &Sheet4.Cells;（writeStartRow + lRow - WRITE_CREDIT_START_ROW， maxCol + 4）.Address & "， 4）"
Sheet5.Range（"F" &lRow;）.Formula = "=Round（" & Sheet4.Name & "！" &Sheet4.Cells;（writeStartRow + lRow - WRITE_CREDIT_START_ROW， maxCol + 5）.Address & "， 4）"
Sheet5.Range（"H" &lRow;）.Formula = "=" & Sheet5.Range（"F" &lRow;）.Address（False， False） & "+" & Sheet5.Range（"G" &lRow;）.Address（False， False）
Next
8）按照所有学生的综合学分绩对学生进行排序
Sheet5.Range（"A1：I" &maxRow; - WRITE_SCORE_START_ROW + 1）.Sort _
Key1：=Sheet5.Range（"H1"）， _
Order1：=xlDescending， _
Header：=xlYes， _
Orientation：=xlTopToBottom
Sheet5.UsedRange.AutoFilter
4 结束语
总之， EXCEL是一款功能强大的数据处理软件，在高校的教学管理中已经成为必不可少的辅助工具。笔者以计算推免学分绩为切入口，利用EXCEL宏的编程功能，设计了一个操作简单的人机界面，不失为对编程软件在教学管理中的应用做了一次探索。通过略微修改宏代码，该界面和程序还可以变化应用到其他种类学分绩的计算上，具有普遍应用的现实意义。学习好，利用好EXCEL的各项功能，不仅能促进教学管理的信息化，数据化，还能提高教学管理的效率，提高教学管理水平。笔者在今后的工作中将继续思索如何有效地将办公辅助软件充分适用到教学管理中。
参考文献：
[1] 李震宇.EXCEL在教学管理中的应用[J].教育科学，2008.
[2] 伍远高.EXCEL VBA实战宝典[M].清华大学出版社，2014.
