Excel VBA在中小學成績統計分析中的應用探索

何灃燊

摘 要 首先通過調查法了解中小學教師對Excel軟件的使用情況及成績統計的工作情況，發現大部分中學教師在使用Excel進行成績統計時存在一定的操作困難，且由于數據量大，重復的手動移動數據的任務讓他們感到疲累。Excel VBA可以使Excel中的重復性任務自動化、個性化，能大大提高教師的成績統計分析工作效率。通過實驗法，向參與調查采訪的教師提供利用Excel VBA編寫的成績統計程序試用，并獲得他們的反饋，據此在文中提出一些Excel VBA在成績統計中應用的可行性建議。

關鍵詞 Excel；Excel VBA；成績統計

中圖分類號：TP317.3 文獻標識碼：B

文章編號：1671-489X（2016）04-0049-05

1 前言

學生的成績統計分析是教師在每次考試后必須進行的一項工作。參加考試的班別、科目、題型眾多，考試成績的數據量也十分龐大。教師使用Excel統計分析成績時，需進行以下工作：學生考試成績的填寫或移動；計算總分、平均分、標準差等數據；多次考試的成績匯總；成績數據的篩選呈現；繪制圖表分析成績。其中一些工作需要經過多個重復的步驟，還要調用一些Excel的函數功能。

如果能夠應用Excel VBA，將成績統計的功能自動化或部分自動化，將重復的工作步驟自動執行，可以大大提高教師的成績統計工作效率。如果能設計出較為通用的Excel VBA成績統計數據，更是可以讓一些原本不熟悉Excel統計功能和函數功能的教師輕松地在Excel這個平臺上統計分析考試成績。

2 Excel在中學成績統計中的現狀

Excel VBA使Excel的使用更自動化和個性化，在企業辦公、財務統計等領域中已經得到很廣泛的應用。但由于教師的工作重心更多放在課程教學中，使Excel VBA在教學領域的應用有一定的難度。

通過采訪調查獲悉，由于我國教育信息化工作的日漸推進，計算機已經成為大部分中學教師重要的工具。每次考試過后，幾乎所有有設備條件的中學教師（以廣州市鐵一中學、廣鐵一小、明新中學等參與調查的中小學教師為樣本，在其他學校中可能存在偏差）都會使用Excel來統計考試成績。大部分教師主要利用Excel表格的格式設計、求和（總分）、求平均值、排序、制作打印成績條等功能。而在一些重要考試后（如中高考模擬考、期中和期末考、調研水平測試等），一些教師還會利用Excel制作圖表、計算名次、篩選分數段。

在使用Excel的情況上，非信息類科目教師主要使用Excel中的格式設置、圖表、條件格式和一些簡單的函數功能；而一些復雜的任務如函數的嵌套、跨文件調用數據等，主要是求助于學校的信息教師來完成。信息教師都能熟練使用Excel，但大多數情況下幫助其他科目教師制作成績統計的工作表都是一次性的。特別是數據調用、嵌套函數等任務，在每次有新的考試成績后，都要對工作表中內容進行大量的更改。這樣加大了原本就承擔了學校中大量信息化工作任務的信息教師的工作壓力。

3 Excel VBA在中學成績統計中的應用情況

根據采訪調查，只有極少數中小學教師在成績統計中能用到簡單的“錄制宏”和相關的VBA代碼。這樣的應用能使某些Excel中不斷重復的任務自動快速地完成，但對于使用ActiveX控件和一些較為復雜的VBA代碼感到比較困難。而大部分非信息類教師在Excel的使用中不涉及VBA，不了解VBA強大的功能，在平時的工作中也很難抽出時間去進行系統的、細致的學習。

現在各中學的信息教師大多有很好的編程基礎，且VBA與中學信息課程中的編程內容所用語言VB一脈相承，信息教師能很快掌握并合理使用。若信息教師利用Excel VBA為所在學校的教師編寫出符合學校需求、能重復長期使用的成績統計分析程序，對整個學校的教學工作極為有利。同時也免去了每次考試后其他科目的教師求助于信息教師用Excel統計成績的工作壓力。

4 Excel VBA的使用方法

Excel VBA與Excel函數功能結合 利用Excel VBA可以對工作表中指定的區域進行賦值操作。賦值可以是數值、文本等。Excel中的函數功能主要是通過在單元格中輸入函數語句實現的。單元格中存放的函數語句是文本形式的。通過VBA代碼可以自動將函數語句輸入指定單元格中。示例如下。

在工作表中添加一個圖1所示命令按鈕（CommandButton）

控件。在設計模式下雙擊該按鈕，進入VBA代碼編寫界面。輸入代碼：

保存代碼后回到工作表，點擊按鈕，因按鈕的caption屬性仍為”CommandButton1”，則執行代碼中的Else語句，向P列單元格中填入求平均值的函數式。按鈕Caption屬性更改為“總分”，即提示再次點擊按鈕將呈現總分。結果如圖2所示。

若再次點擊按鈕，則P列填充總分，按鈕Caption屬性更改為“平均分”。通過一個按鈕和代碼，就可以實現一列單元格中函數在求和求平均值之間切換。此例僅說明VBA代碼和函數功能結合的使用方式，實際可編寫邏輯結構更復雜的代碼，填入的函數式在功能、嵌套結構上也可以更復雜，從而使實現的功能更靈活強大。

錄制宏與Excel功能結合 作為一個功能豐富完善的辦公統計軟件，Excel中集成了大量數據統計分析、工作表格式設置的功能。通過錄制宏的方式可以將在Excel中的操作步驟錄制成代碼，通過對這些代碼的修改可以實現個性化的功能。示例如下。

在Excel“開發工具”功能區中，點擊“錄制宏”開始錄制代碼。選擇F列語文成績，設置條件格式（圖3）。完成后選擇開發工具中的“停止錄制”。

停止錄制宏后查看代碼，在VBA工程的“模塊”目錄下，查看模塊1中保存了錄制的代碼：

這段代碼可以作為一個模板，修改其中的參數，則可以使條件格式的顯示形式更個性化。將這段代碼與控件結合使用，通過控件就可以設置條件格式，在多次設置時大大減少了操作步驟。

Excel VBA代碼與控件的使用 通過編寫代碼，直接對工作表中數據進行分析和呈現。

向工作表中添加一個名稱為“CommandButton2”的命令按鈕控件。在設計模式下雙擊按鈕，進入代碼的編寫。輸入代碼：

該段代碼的功能是統計F列上的語文成績，通過MsgBox對話框顯示語文成績中110分上下的學生分別有多少人。編寫完成后點擊按鈕，效果如圖4所示。

通過編寫代碼，還可以對數據進行選定、移動、分析等多種操作，但需要較好的編程基礎和算法思維。

5 制作并試用Excel VBA成績統計分析程序

由于大部分教師不能自行設計編寫一個系統的成績統計分析程序，實驗通過設計制作一個Excel VBA成績統計分析程序，交由教師試用，來獲得教師的反饋。

根據采訪調查的結果，分析教師成績分析統計的需求，確定程序的功能。基礎的功能包括：1）花名冊的輸入和修改；2）成績表中的考試科目、綜合科目的設置；3）成績表按每次考試的新建、修改、刪除；4）成績錄入成績表；5）成績自動匯總排名；6）成績分析的功能；7）多次考試成績的匯總；8）篩選呈現特定的學科、班級的成績；9）選擇學生個人的多次成績呈現；10）制作成績打印條；11）年級成績統計圖表；12）個人成績圖表。

在這些功能中，除了成績和花名冊的錄入由教師按照Excel原來的數據輸入方式輸入相應的表中，其他的功能全部通過按鈕、下拉列表控件或者程序自動運行代碼完成。

圖5是程序運行后顯示的界面，是程序的啟動界面，基礎的功能主要通過右邊兩個按鈕啟動。成績分析功能設計在成績匯總表中，由最左邊的按鈕打開進入。

啟動程序后點擊“打開模文件”，如圖6所示，可以進入花名冊的輸入和成績表的設置。花名表中教師可以在特定的位置輸入班級名稱、學號、學生姓名等信息。關閉文件后會彈窗提示保存數據。這些花名冊數據會被成績表輸入表和匯總表等功能自動調用。

成績輸入表就是每次輸入成績的表格模板。在成績輸入表模板中，教師可以通過設置好的控件對考試的科目范圍（即有哪些科目）、綜合科（文綜或理綜）進行設置。

成績輸入表模板設置好后，關閉這個工作表，程序會自動保存設置，回到開始的啟動界面。點擊“打開成績輸入表”后，彈出如圖3所示的窗口，通過這個窗口的幾個按鈕，教師可以新建成績表、打開某次考試的成績表輸入成績、刪除某次考試的成績表或重命名某個成績表。

在完成花名冊輸入、成績表的設置、成績的輸入后，關閉工作表回到啟動界面。

通過這些功能，建立的成績表格式、功能都與成績輸入表模板一致。相較于不利用Excel VBA統計匯總成績，每次都要手動新建成績表并設置成相同的格式；利用Excel VBA制作的程序，大大減少了教師制作成績表的工作步驟，且由于數據的存放方式一致，程序可以快速地調用數據，完成多班級多次多科目考試的成績數據調用分析工作。

建立成績表并輸入學生多次考試成績數據后，從啟動界面的“打開成績匯總表”按鈕進入匯總表。通過窗體和控件，可以對匯總表中的成績數據進行篩選查看。部分效果如圖7～圖9所示。

還可查看學生個人分數、學生分數圖表，具備制作成績條打印等多種功能。

交由廣州市鐵一中學教師使用后獲得反饋意見如下：

“我使用得還不夠熟練，就覺得提示界面還不夠充分，查找、成績條打印、分數、排位這些都比較好。還可以增加一些雙上線的內容，比如說50%的學生總分達到一本，各學科是否達標，能夠顯示出來。”（根據廣州市鐵一中學高三黃老師原話記錄。）

整個程序包含了成績統計用到的一般功能，其中的班級統計、學生個人統計、年級成績分析等功能十分實用。但是成績功能眾多，也導致教師使用時存在一定的困難，需要有更充分明確的界面提示，幫助教師更好地使用該程序進行成績統計分析工作。（根據廣州市鐵一中學及其他省市中學試用程序的教師填寫的問卷整理得。）

6 Excel VBA在中小學成績統計分析中應用的建議

根據Excel VBA編寫程序的特點，以及教師試用Excel VBA編制的成績統計分析程序反饋的意見，提出兩點建議。

1）非信息類教師大多數有使用Excel進行統計分析。錄制宏、ActiveX控件等功能較易掌握，可以向非信息類教師推廣錄制宏、控件等功能的簡單使用。學校在培訓教師信息化能力時，應適當加入Excel VBA的內容，可以大大提高教師在成績統計分析上的工作效率。

2）信息類教師大多具有良好的編程基礎及Excel軟件使用基礎，在學校的信息化工作中，信息類教師應重視Excel VBA的應用。無論是教務管理、成績統計分析、學校財務工作信息化等方面，均應讓Excel VBA大展身手。協助非信息類教師制作成績統計分析工作表時，可參考文中的成績統計分析程序，設計編寫適合所在學校使用的小程序，提高非信息類教師的工作效率。這樣也減少每次考試后都要制作成績表的工作壓力。

參考文獻

[1]郗金甲，范進勇，李練，等.Excel VBA實戰技巧精粹（修訂版）[M].北京：人民郵電出版社，2013.

[2]馬文宇.Excel中小學標準化測試中教育信息處理的應用[J].中國現代教育裝備，2009（12）.

[3]沈浩.Excel高級應用與數據分析[M].北京：電子工業出版社，2008.

[4]唐德海.在教育統計分析中運用Excel VBA自動生成個性化圖表[J].中國教育信息化，2012（9）.

[5]Excel VBA在學校教育工作中的應用研究[EB/OL].http：//djsylm.edugd.cn/.