李麗　孟江

摘要：在統(tǒng)計(jì)學(xué)生大學(xué)四年的各科成績的過程中，雖然教務(wù)系統(tǒng)可以導(dǎo)出Excel格式的原始成績數(shù)據(jù)，但無法滿足個性化的數(shù)據(jù)處理需求。為此，該文利用Excel VBA強(qiáng)大的數(shù)據(jù)處理功能，在對成績統(tǒng)計(jì)及評定需求梳理的基礎(chǔ)上，編程實(shí)現(xiàn)了學(xué)生成績統(tǒng)計(jì)及評定方法，涵蓋打開目標(biāo)Excel文件、數(shù)據(jù)篩選、數(shù)據(jù)統(tǒng)計(jì)和綜合評定等功能。結(jié)果表明，基于Excel VBA的數(shù)據(jù)處理能夠滿足個性化的需求，極大地提高了工作效率。

關(guān)鍵詞：Excel；VBA；宏；成績統(tǒng)計(jì)

中圖分類號：TP311.1 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2016）05-0129-03

Abstract： In order to analyze and gather statistical achievements of undergraduate courses， the excel format files of raw students achievements are exported from the educational administration system， and then a method is proposed to satisfy the data processing requirements. Based on the statistical and valuational requirements， this paper suggests a method which uses Excel VBA to deal with achievements in the excel format files， and includes opening excel file， screening data， statistical data and comprehensive evaluation. The results show that the method using Excel VBA satisfies the data processing requirements and improves work efficiency greatly.

Key words： Excel； VBA； Marco； Statistical Achievements

傳統(tǒng)的教學(xué)工作中，對于學(xué)生學(xué)習(xí)成績的統(tǒng)計(jì)及評定是教師的重要工作之一。隨著辦公自動化水平的提升，教務(wù)系統(tǒng)等辦公自動化軟件所管理的學(xué)生成績數(shù)據(jù)量越來越大、數(shù)據(jù)類型越來越復(fù)雜，同時綜合素質(zhì)涉及的加分項(xiàng)越來越多。雖然這些系統(tǒng)軟件可以承擔(dān)大部分的常規(guī)統(tǒng)計(jì)工作，但卻無法滿足不同教師對學(xué)生綜合成績千差萬別的處理需求。因此，滿足個性化需求的學(xué)生成績處理方法，尤其是能夠快速、準(zhǔn)確、全面的統(tǒng)計(jì)應(yīng)屆畢業(yè)生大學(xué)四年間的成績數(shù)據(jù)并評定出每一位同學(xué)綜合成績的方法，成為幫助教育工作者開展學(xué)生綜合成績評定、提升工作效率的必要工具。

Microsoft Excel作為一款必備的辦公軟件，其強(qiáng)大的數(shù)據(jù)處理和統(tǒng)計(jì)分析功能使之成為日常處理學(xué)生成績數(shù)據(jù)的重要工具，但是面對個性化需求的應(yīng)屆畢業(yè)生成績篩選、統(tǒng)計(jì)和評定，常規(guī)的數(shù)據(jù)處理函數(shù)已經(jīng)無法滿足要求[1-3]。因此，本文在梳理應(yīng)屆畢業(yè)生成績篩選、統(tǒng)計(jì)和評定需求的基礎(chǔ)上，利用Excel VBA（Visual Basic for Applications）強(qiáng)大的編程功能，設(shè)計(jì)并實(shí)現(xiàn)了一種學(xué)生成績統(tǒng)計(jì)及評定方法，涵蓋打開目標(biāo)Excel文件、數(shù)據(jù)讀取及篩選、數(shù)據(jù)統(tǒng)計(jì)及綜合評定等功能。

1 相關(guān)基礎(chǔ)技術(shù)

1.1 Excel對象模型

Microsoft Office體系采用層次（嵌套）對象模型，通過對象把Office的數(shù)據(jù)、屬性、操作封裝起來[4]。Office中的文檔、段落、工作表等各種實(shí)體都是對象，可以通過創(chuàng)建相應(yīng)的對象實(shí)現(xiàn)對其所屬對象、屬性、方法的訪問控制。

Excel對象模型的整體結(jié)構(gòu)如圖1所示，其基本描述了Excel的對象層次和操作對象，對圖1中各個對象的簡要介紹如下：

1）一個Excel應(yīng)用程序就是一個Application；

2）一個Application可以包含多個Workbook（Workbooks）。例如，可以同時打開很多個工作簿（Workbooks），但某一時刻只有一個工作簿（Workbook）處于編輯狀態(tài)，這個工作簿叫做活動工作簿（ActiveWorkbook）；

3）一個Workbook可以包含多個Worksheet（Worksheets）（與Workbook和Workbooks類似）；

4）一個Workbook可以包含多Shapes對象，圖表（Charts）是其中的一種；

5）一個Worksheet可以包含多個Range對象。例如，一個工作表里面有很多個單元格，單元格的范圍用Range表示，單元格都是嵌入到Sheet頁中的。

1.2 Excel VBA

VBA是一種標(biāo)準(zhǔn)宏語言[5]，是Microsoft基于VB語言開發(fā)出來的為使Word和Excel的處理自動化的通用語言，可以將其視為應(yīng)用程序開發(fā)語言VB的子集。因此，VBA繼承了VB的基本結(jié)構(gòu)，也是一種完整的程序設(shè)計(jì)語言；同時，與VB不同之處在于，VBA是一種寄生于父應(yīng)用程序（如Excel、Word）的語言，其程序運(yùn)行必須依賴其父應(yīng)用程序。

VBA在Microsoft Office系列軟件中均有集成，并提供了訪問Office文檔的接口，包含了多種專用的函數(shù)和對象，可以非常便捷的對Office文檔中各個對象、對象的屬性進(jìn)行訪問和操作[6]。其主要應(yīng)用場景包括兩類：一是在對用戶操作進(jìn)行梳理、規(guī)范的基礎(chǔ)上，通過錄制宏、編輯宏將用戶操作固化成VBA程序代碼，用戶可在父應(yīng)用程序中點(diǎn)擊執(zhí)行宏以實(shí)現(xiàn)用戶操作的自動化；二是將固化的VBA程序代碼模塊化，通過在其他程序開發(fā)過程中直接調(diào)用VBA模塊，實(shí)現(xiàn)代碼復(fù)用。

Excel作為一種常用的數(shù)據(jù)處理和統(tǒng)計(jì)分析工具，在日常辦公過程中不可避免的會面臨各式各樣的數(shù)據(jù)統(tǒng)計(jì)分析問題，而這類紛繁復(fù)雜的數(shù)據(jù)處理工作通常又具有一定的規(guī)律性，可以通過Excel VBA訪問上一節(jié)中的Excel對象，將對Excel中數(shù)據(jù)信息的處理規(guī)范化、自動化。

2 設(shè)計(jì)與實(shí)現(xiàn)

2.1 總體設(shè)計(jì)

由于在實(shí)際工作中面對不同的需求，對學(xué)生成績數(shù)據(jù)的處理過程有各種不同的需求，本文方法在綜合分析各種需求的基礎(chǔ)上，給出了“獲取”、“篩選”、“統(tǒng)計(jì)”和“評定”的四階段處理流程，每一階段根據(jù)不同的規(guī)則可以調(diào)用不同的處理函數(shù)，最終在完成上述四個階段的數(shù)據(jù)處理后完成學(xué)生成績的綜合評定。與之相對應(yīng)，本文方法可分為數(shù)據(jù)獲取、數(shù)據(jù)篩選、數(shù)據(jù)統(tǒng)計(jì)和綜合評定四個處理模塊，各個模塊具體功能如下：

1）數(shù)據(jù)獲?。焊鶕?jù)指定的學(xué)生成績數(shù)據(jù)文件，讀取其中的有效數(shù)據(jù)，包括學(xué)生學(xué)號、姓名、科目及成績等；

2）數(shù)據(jù)篩選：在進(jìn)行學(xué)生綜合評定時，某些選修科目是不計(jì)入評定的，因此需要根據(jù)不同的篩選規(guī)則剔除無效的成績數(shù)據(jù)；

3）數(shù)據(jù)統(tǒng)計(jì)：將不同學(xué)期的成績進(jìn)行關(guān)聯(lián)統(tǒng)計(jì)，并根據(jù)指定的統(tǒng)計(jì)規(guī)則進(jìn)行處理，得出基本的數(shù)據(jù)統(tǒng)計(jì)結(jié)果；

4）綜合評定：對學(xué)生的綜合評定需要結(jié)合考試成績和多種素質(zhì)評分，可根據(jù)評定規(guī)則執(zhí)行。

綜上所述，篩選、統(tǒng)計(jì)和評定的要求較復(fù)雜，無法建立通用的處理方法，因此本文方法只是針對其中的某些需求進(jìn)行了定制開發(fā)，實(shí)現(xiàn)自動化處理以提高工作效率，同時這種模式也可以推廣到其他類似的數(shù)據(jù)處理需求中。具體而言，本文方法所涉及的需求可描述為“一項(xiàng)輸入、三類輸出”：

1）一項(xiàng)輸入：即輸入數(shù)據(jù)為教務(wù)系統(tǒng)按照學(xué)期導(dǎo)出的四年間的八份成績單；

2）三類輸出：篩選，是對教務(wù)系統(tǒng)所導(dǎo)出成績單數(shù)據(jù)的初步處理，具體包括將不需要計(jì)入統(tǒng)計(jì)的科目篩除；統(tǒng)計(jì)，是指將八個學(xué)期中計(jì)入統(tǒng)計(jì)的科目成績數(shù)據(jù)根據(jù)學(xué)生學(xué)號進(jìn)行有效關(guān)聯(lián)；評定，結(jié)合科目成績、公選課成績和創(chuàng)新學(xué)分，按照加權(quán)平均進(jìn)行綜合評定。

2.2 方法實(shí)現(xiàn)

2.2.1 數(shù)據(jù)獲取

本文方法以學(xué)校教務(wù)系統(tǒng)中存儲的學(xué)生成績作為唯一有效數(shù)據(jù)，利用Excel VBA對這些數(shù)據(jù)進(jìn)行綜合處理。

由于教務(wù)系統(tǒng)一次只能導(dǎo)出某一學(xué)期的成績單，因此首先需要在教務(wù)系統(tǒng)中指定班級，按照學(xué)期分別導(dǎo)出全部學(xué)生的各個科目成績，生成固定格式的Excel文件，格式如圖2所示。

其中，第一行為成績單抬頭，第二行為學(xué)期說明，第三行為年級、學(xué)院等信息，第四行為科目名稱及統(tǒng)計(jì)類別，第五行為科目的說明信息，第六行依次分別為編號、學(xué)號、姓名、各科目成績以及統(tǒng)計(jì)成績信息。同時本文規(guī)定該Excel文件的命名規(guī)則為“學(xué)年-學(xué)期”，如文件名“1-1”表示第一學(xué)年第一學(xué)期的成績。

2.2.2數(shù)據(jù)篩選

對表2中的成績，并不是所有科目都需計(jì)入考核，因此需要對每一學(xué)期的成績數(shù)據(jù)進(jìn)行篩選，分為以下兩部分。

1）配置文件

通過建立配置文件以篩選每一學(xué)期列入統(tǒng)計(jì)項(xiàng)的科目名稱，同樣以Excel格式存儲，只需輸入圖2中的課程名稱即可，例如選取圖2種的計(jì)算機(jī)應(yīng)用基礎(chǔ)、高級語言程序設(shè)計(jì)、高等數(shù)學(xué)1、線性代數(shù)、英語1等五門課程。圖3為配置文件示意圖；

2）Excel VBA編程篩選

利用Excel VBA編程，以學(xué)生學(xué)號為唯一標(biāo)識，將八個學(xué)期的列入配置文件的科目成績和參評的其他成績進(jìn)行自動篩選，處理流程如下：

步驟1：打開配置文件，獲取每一學(xué)期列入評定的科目名稱；

步驟2：選定第一學(xué)期的Excel成績單，將學(xué)生學(xué)號、學(xué)生名稱、配置單內(nèi)的科目，這些數(shù)據(jù)列的數(shù)據(jù)復(fù)制到名為“總成績”的Sheet中；

步驟3：依次打開第二學(xué)期后的Excel成績單，按照“總成績”Sheet中的學(xué)號選取每一個學(xué)生在對應(yīng)配置單內(nèi)科目的成績；

步驟4：名為“總成績”的Sheet即為篩選參評的數(shù)據(jù)。

2.2.3數(shù)據(jù)統(tǒng)計(jì)

對“總成績”Sheet中的成績數(shù)據(jù)，可以根據(jù)不同的需求進(jìn)行統(tǒng)計(jì)。本文進(jìn)行了以下幾類統(tǒng)計(jì)：

1）將“優(yōu)、良、中、差”映射為具體分?jǐn)?shù)以便于統(tǒng)計(jì)，分別對應(yīng)90、80、70、60；

2）統(tǒng)計(jì)某科目成績大于85分的學(xué)生總數(shù)；

3）統(tǒng)計(jì)每個學(xué)生所有科目中大于85分的科目數(shù)量；

4）統(tǒng)計(jì)成績不合格學(xué)生的數(shù)量，學(xué)生名稱、科目和對應(yīng)的成績。

2.2.4綜合評定

綜合評定的標(biāo)準(zhǔn)和規(guī)則沒有明確規(guī)定，在進(jìn)行充分的討論之后，按照各科總成績、創(chuàng)新學(xué)分、公共選修課學(xué)分進(jìn)行加權(quán)平均，處理流程如下：

步驟1：統(tǒng)計(jì)每一位學(xué)生的各科總成績、創(chuàng)新學(xué)分總數(shù)和公共選修課總學(xué)分；

步驟2：分別對上述三項(xiàng)成績進(jìn)行歸一化處理；

步驟3：按照既定權(quán)重對歸一化后的三項(xiàng)成績加權(quán)平均，得到綜合評定成績；

步驟4：按照綜合評定成績排序，完成綜合評定。

3 應(yīng)用實(shí)例

利用本文的統(tǒng)計(jì)分析方法，對本校2010級的學(xué)生成績進(jìn)行處理，結(jié)果如圖4、圖5所示。

4 總結(jié)

辦公自動化系統(tǒng)的應(yīng)用極大地提高了教師日常的工作效率，但總是存在部分工作由于各種個性化需求使其無法通過系統(tǒng)完成，需要教師手工處理，學(xué)生成績的統(tǒng)計(jì)和評定就是其中重要的一項(xiàng)。

本文通過對Excel VBA的文件打開、數(shù)據(jù)選取和數(shù)據(jù)分析等基本功能的深入學(xué)習(xí)，實(shí)現(xiàn)了一種學(xué)生成績統(tǒng)計(jì)及評定方法。通過“獲取”、“篩選”、“統(tǒng)計(jì)”和“評定”四階段處理流程，快速、準(zhǔn)確、全面的統(tǒng)計(jì)應(yīng)屆畢業(yè)生大學(xué)四年間的成績數(shù)據(jù)并評定出每一位同學(xué)綜合成績。本文方法簡單實(shí)用，能夠在此基礎(chǔ)上通過簡單移植和修改，解決日常工作中的多種統(tǒng)計(jì)分析工作，將部分個性化的需求轉(zhuǎn)化為成熟的軟件工具，提高工作效率。

感謝西藏民族大學(xué)科研創(chuàng)新團(tuán)隊(duì)建設(shè)專項(xiàng)經(jīng)費(fèi)資助。

參考文獻(xiàn)：

[1] 李小遐. Excel VBA 在辦公自動化中的應(yīng)用[J]. 科技論壇， 2014（22）：105-107.

[2] 王東燕. 用Excel 的宏和VBA 實(shí)現(xiàn)方便快捷的調(diào)查統(tǒng)計(jì)[J].辦公自動化， 2010（11）：44-47.

[3] 王亞利， 李井竹. 基于VBA 的Office 操作題自動閱卷技術(shù)的研究與實(shí)現(xiàn)[J]. 廊坊師范學(xué)院學(xué)報(bào)， 2008， 8（4）：5-7.

[4] 協(xié)同教育微軟ATC 教材編譯室1OFFICE 專家案例教程EXCEL 2000 [M].北京： 電子工業(yè)出版社， 1999： 56-58

[5] 陳軍民. Excel VBA 在Office 中的應(yīng)用案例[J]. 電腦知識與技術(shù)， 2013（4）：2819-2821.

[6] 楊峻， 睢丹. VBA在Excel中的應(yīng)用[J]. 安陽大學(xué)學(xué)報(bào)， 2003， 2（2）：44-45.