Excel宏在計算高校專業(yè)學分績排名中的應用

李欣樂

摘要： 綜合教務管理系統(tǒng)基本已經(jīng)在全國高校普及。雖然目前大部分教務管理系統(tǒng)都可以對學生的平均學分績進行計算，但是針對專業(yè)學分績，如只計算所有專業(yè)課程學分績或者只計算某些指定課程學分績，仍然需要從系統(tǒng)中導出成績后另行計算，工作繁重。該文通過提供一種EXCEL中VBA（Visual Basic for Application）編寫宏代碼的方法，設計人機界面，簡化此類特殊學分績的計算，分類結(jié)果，清晰排序，提高教學管理的質(zhì)量和效率。

關(guān)鍵詞： 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軟件是一款功能強大的數(shù)據(jù)處理辦公軟件，它可以分析信息并管理電子表格或網(wǎng)頁中的數(shù)據(jù)信息列表與數(shù)據(jù)資料圖表制作，可以實現(xiàn)許多方便的功能，廣泛地應用于高校信息化管理中。

Visual Basic for Applications（VBA）是Visual Basic的一種宏語言，主要能用來擴展Windows的應用程式功能，特別是Microsoft Office軟件，其中包括Excel、PPT、Word、Outlook等。VBA應用于EXCEL可以實現(xiàn)：

（1）規(guī)范用戶的操作，控制用戶的操作行為；

（2）操作界面人性化，方便用戶的操作；

（3）多個步驟的手工操作通過執(zhí)行VBA代碼可以迅速的實現(xiàn)；

（4）利用VBA可以Excel內(nèi)輕松開發(fā)出功能強大的自動化程序。

筆者將以較為特殊的推免學分績計算為例，通過錄制宏，在EXCEL界面上設計按鈕BUTTON功能既迅速實現(xiàn)對原始成績數(shù)據(jù)的處理并完成學分績計算以及排序。該程序已經(jīng)用于計算近三年推免學分績，經(jīng)過核對，結(jié)果完全準確，但人工時間卻是縮減10倍以上， 大大提高了工作效率和質(zhì)量。

1 專業(yè)推免學分績計算

[每門課學分績=考試成績20×學分數(shù)]

[平均學分績=所有必修課+所有專業(yè)核心課或?qū)I(yè)重點課學分績所有必修課+所有專業(yè)核心課或?qū)I(yè)重點課學分數(shù)]

[專業(yè)課學分績=所有專業(yè)核心課或?qū)I(yè)重點課學分績所有專業(yè)核心課或?qū)I(yè)重點課學分數(shù)]

[推免學分績=平均學分績+專業(yè)課學分績2]

要求學分績統(tǒng)一以學生第一次通過的課程考試成績計算（如第一次考試不及格者，按不及格原始成績計算學分績）。返校未滿一年的交換生以現(xiàn)有課程成績計算。仍然有必修課成績不及格的學生標紅

按此方式演算學分績，有三個特殊點以及難點

（1）準確找到第一次的成績。

（2）判定交換生課程，此類課程0分或空缺均不計入學分績并在結(jié)果中顯示出來。

（3）所有必修課程不及格的學生標紅。

2 界面設計

為了實現(xiàn)上述功能，我們要利用EXCEL宏設計一個方便操作的人機界面，完成對原始數(shù)據(jù)庫的篩選，清晰明確顯示所有學生推免課程的成績及最終推免學分績的結(jié)果。

1）成績數(shù)據(jù)庫

即未經(jīng)處理的學生成績數(shù)據(jù)庫，包含所有學生的所有課程成績。我們就是要將需要的信息從這個數(shù)據(jù)庫中提取出來進行計算。如圖1 示。

2）設計功能按鈕

按照上述思路，筆者設計了兩個功能按鈕。如圖2示。

讀取考試成績按鈕—單擊按鈕，選擇需要處理的學生成績數(shù)據(jù)庫，頁面會顯示數(shù)據(jù)庫中所有課程列表，然后由操作人選擇是否是必修課程和是否是專業(yè)核心課程，完成第一步的數(shù)據(jù)篩選。

計算學分績按鈕—單擊按鈕，后臺按照上述學分績計算規(guī)則計算所有學生的推免學分績。

3）結(jié)果顯示

程序運行完畢，生成兩個表格頁result和sortlist

Result表— 按照學生學號排序顯示所有納入計算課程的第一次成績。所有必修課程不及格的學生標紅。

Sortlist表—按照推免學分績從高到低排序，并分別顯示必修課平均學分績和專業(yè)核心課平均學分績。

Result表格和Sortlist表格數(shù)據(jù)是相關(guān)聯(lián)的，改動Result表格中的任一成績，將會直接影響Sortlist表格中的學分績數(shù)據(jù)。這樣設定也是為了方便糾錯檢查，校對基點，靈活改動。

3 錄制宏，編寫VBA

3.1 系統(tǒng)設計

3.2 系統(tǒng)環(huán)境設置

1）從成績數(shù)據(jù)庫中讀取成績數(shù)據(jù)庫的格式，確定各列保存的數(shù)據(jù)內(nèi)容。

包含學號、學生姓名、所屬院系、課程編號、課程名稱、學分、課程類別、學期、成績類別、總評、備考、備考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）從成績數(shù)據(jù)庫中讀取學生各學習各門課的成績，對于初次考試不及格的課程，從【備考2】列中讀取原始考試成績。

（1）讀取數(shù)據(jù)時，對于沒有學號、沒有學期、沒有課程編號，或者學期數(shù)據(jù)不是數(shù)字的臟數(shù)據(jù)進行忽視。

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）對于成績數(shù)據(jù)庫中的[備考]內(nèi)容為[出國]、[交換]、[交流]字樣的課程，將該學生該門課程的成績進行標記。

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]內(nèi)容中包含[補考]內(nèi)容時，讀取[備考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）對于忽視的數(shù)據(jù)保存在[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中，保存是如果已經(jīng)有成績數(shù)據(jù)存在，比較該成績獲得的學期。比較學期數(shù)據(jù)的大小，保存最早學期的成績，準確找到第一次的成績。

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）根據(jù)[Compute]sheet中記錄的所有課程列表中標注的必修課程、專業(yè)核心課程，從[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）根據(jù)[Result]Sheet保存的必修課和專業(yè)核心課成績，計算所有學生[每門課學分績=考試成績20×學分數(shù)]

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）根據(jù)所有學生的每門課的學分績，計算所有學生的專業(yè)學分、必修課學分、專業(yè)課學分績、基礎學分績。

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）計算每個學生必修課平均學分績、專業(yè)核心課平均學分績、基礎學分績以及綜合學分績

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 結(jié)束語

總之， EXCEL是一款功能強大的數(shù)據(jù)處理軟件，在高校的教學管理中已經(jīng)成為必不可少的輔助工具。筆者以計算推免學分績?yōu)榍腥肟?，利用EXCEL宏的編程功能，設計了一個操作簡單的人機界面，不失為對編程軟件在教學管理中的應用做了一次探索。通過略微修改宏代碼，該界面和程序還可以變化應用到其他種類學分績的計算上，具有普遍應用的現(xiàn)實意義。學習好，利用好EXCEL的各項功能，不僅能促進教學管理的信息化，數(shù)據(jù)化，還能提高教學管理的效率，提高教學管理水平。筆者在今后的工作中將繼續(xù)思索如何有效地將辦公輔助軟件充分適用到教學管理中。

參考文獻：

[1] 李震宇.EXCEL在教學管理中的應用[J].教育科學，2008.

[2] 伍遠高.EXCEL VBA實戰(zhàn)寶典[M].清華大學出版社，2014.