李欣樂

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

關(guān)鍵詞： excel宏；VBA；學(xué)分績(jī)計(jì)算

中圖分類號(hào)：TP3 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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軟件是一款功能強(qiáng)大的數(shù)據(jù)處理辦公軟件，它可以分析信息并管理電子表格或網(wǎng)頁中的數(shù)據(jù)信息列表與數(shù)據(jù)資料圖表制作，可以實(shí)現(xiàn)許多方便的功能，廣泛地應(yīng)用于高校信息化管理中。

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

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

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

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

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

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

1 專業(yè)推免學(xué)分績(jī)計(jì)算

[每門課學(xué)分績(jī)=考試成績(jī)20×學(xué)分?jǐn)?shù)]

[平均學(xué)分績(jī)=所有必修課+所有專業(yè)核心課或?qū)I(yè)重點(diǎn)課學(xué)分績(jī)所有必修課+所有專業(yè)核心課或?qū)I(yè)重點(diǎn)課學(xué)分?jǐn)?shù)]

[專業(yè)課學(xué)分績(jī)=所有專業(yè)核心課或?qū)I(yè)重點(diǎn)課學(xué)分績(jī)所有專業(yè)核心課或?qū)I(yè)重點(diǎn)課學(xué)分?jǐn)?shù)]

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

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

按此方式演算學(xué)分績(jī)，有三個(gè)特殊點(diǎn)以及難點(diǎn)

（1）準(zhǔn)確找到第一次的成績(jī)。

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

（3）所有必修課程不及格的學(xué)生標(biāo)紅。

2 界面設(shè)計(jì)

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

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

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

2）設(shè)計(jì)功能按鈕

按照上述思路，筆者設(shè)計(jì)了兩個(gè)功能按鈕。如圖2示。

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

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

3）結(jié)果顯示

程序運(yùn)行完畢，生成兩個(gè)表格頁result和sortlist

Result表— 按照學(xué)生學(xué)號(hào)排序顯示所有納入計(jì)算課程的第一次成績(jī)。所有必修課程不及格的學(xué)生標(biāo)紅。

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

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

3 錄制宏，編寫VBA

3.1 系統(tǒng)設(shè)計(jì)

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

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

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

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

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

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）對(duì)于[備考2]內(nèi)容中包含[補(bǔ)考]內(nèi)容時(shí)，讀取[備考2]中的原成績(jī)作為實(shí)際計(jì)算成績(jī)

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）對(duì)于忽視的數(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）將讀取的成績(jī)按照學(xué)號(hào)和課程編號(hào)保存在[PointList]sheet中，保存是如果已經(jīng)有成績(jī)數(shù)據(jù)存在，比較該成績(jī)獲得的學(xué)期。比較學(xué)期數(shù)據(jù)的大小，保存最早學(xué)期的成績(jī)，準(zhǔn)確找到第一次的成績(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中記錄的所有課程列表中標(biāo)注的必修課程、專業(yè)核心課程，從[PointList]sheet中獲取學(xué)生相應(yīng)課程的成績(jī)，保存在[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è)核心課成績(jī)，計(jì)算所有學(xué)生[每門課學(xué)分績(jī)=考試成績(jī)20×學(xué)分?jǐn)?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ù)所有學(xué)生的每門課的學(xué)分績(jī)，計(jì)算所有學(xué)生的專業(yè)學(xué)分、必修課學(xué)分、專業(yè)課學(xué)分績(jī)、基礎(chǔ)學(xué)分績(jī)。

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

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）按照所有學(xué)生的綜合學(xué)分績(jī)對(duì)學(xué)生進(jìn)行排序

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

參考文獻(xiàn)：

[1] 李震宇.EXCEL在教學(xué)管理中的應(yīng)用[J].教育科學(xué)，2008.

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