| 标题 | Excel宏在计算高校专业学分绩排名中的应用 |
| 范文 | 李欣乐 摘要： 综合教务管理系统基本已经在全国高校普及。虽然目前大部分教务管理系统都可以对学生的平均学分绩进行计算，但是针对专业学分绩，如只计算所有专业课程学分绩或者只计算某些指定课程学分绩，仍然需要从系统中导出成绩后另行计算，工作繁重。该文通过提供一种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. |
| 随便看 |
|
科学优质学术资源、百科知识分享平台，免费提供知识科普、生活经验分享、中外学术论文、各类范文、学术文献、教学资料、学术期刊、会议、报纸、杂志、工具书等各类资源检索、在线阅读和软件app下载服务。