基于Excel VBA及函數的成績上報單的制作

石曉玲，楊立功

（泰州職業技術學院，江蘇泰州 225300）

基于Excel VBA及函數的成績上報單的制作

石曉玲，楊立功

（泰州職業技術學院，江蘇泰州 225300）

介紹了在Excel下利用內置函數及VBA程序制作班級學生成績系統，實現數據的自動化處理，包括基本數據的自動導入、總評成績的自動合成及統計數據的自動生成等。使用戶能夠快速完成特定任務，提高工作效率和準確性。

Excel VBA；函數；統計

VBA是Visual Basic for Application的簡稱，是集成在M icrosoft Office應用程序中的一種程序設計語言，能夠實現辦公自動化，從而極大地提高工作效率。VBA技術為辦公自動化用戶提供了面向對象的程序設計方法及相當完整的程序設計語言。其獨特之處在于它由應用程序控制，反過來又可以增強應用程序的功能。

Microsoft Excel是微軟公司的辦公軟件Microsoft Office的組件之一，用于進行數據的處理、統計分析和輔助決策。它功能強大，不但提供了打印，文件處理，格式化和文本編輯等基本功能，而且內置了大量函數，已被用戶廣泛接受和使用。

用戶可以在Excel平臺基礎上，利用VBA進行二次開發，增強Excel的自動化能力，更高效地完成特定任務。

1 任務分析

高校每學期在課程結束后，要求教師向授課學生所在院系提供班級學生成績上報單（見圖1）。該上報單應給出每個學生的各項基本成績信息，并且要提供班級成績分析。其中總評成績需根據各課程性質，按平時、實踐及卷面比例計算得出；學生成績統計表部分需按總評成績統計出各分數段人數及平均分。上述工作較為繁瑣，容易出錯，為減輕教師重復勞動負擔，提高工作效率，本文利用Excel內置函數及VBA技術提供了《學生成績上報單》填報系統。將該系統掛到教務處網站供教師隨時下載使用，使教師在幾分鐘內就可以便捷地完成班級成績單的制作。

2 實現步驟

2.1 系統設計

在Excel工作簿sheet1表中繪制如圖1所示《泰州職業技術學院學生成績上報單》空表格格式。按照每列33人，總人數不超過66人設計表格。建立“jwxt”工作表，作為添加全校班級學生信息的數據源，該表由管理員導入數據，用戶打開系統時被隱藏，A列為班級（bj），B列為學號（xh），C列為姓名（xm） 字段信息。

2.2 窗體及代碼設計

用戶打開文件，系統自動運行work book_open模塊]，主要代碼（各詳細代碼略）如下[1]：

打開sheet1表后需要進行工作項目的選擇及基本信息的錄入。首先設計對應窗體，如圖2所示。

圖2 用戶開始界面

其中第一個按鈕將原來表格中的數據清除以便輸入新的班級數據，第二個按鈕可以先將原表格中的數據保存為以班級名稱為名的文件，然后再清空數據表[2]。保存文件代碼如下：

設計第三個按鈕為進入班級成績錄入方式。用戶確認輸入的班級正確后，將在sheet1表中立即加載該班級學生的學號和姓名信息（見圖3）。添加班級學生信息代碼如下：

圖3 選擇班級窗口

圖4 填寫成績比例

在彈出的“填寫成績比例”窗口輸入平時、實踐及卷面成績占總評成績的比例（見圖4），數據將被加載到sheet1表第5行。至此成績上報表中學生基本信息添加完畢。

2.3 總評成績和統計數據的生成

各任課教師在表格相應單元格內填寫每個學生的平時、實踐和卷面成績，表格將立即自動生成總評成績和統計數據。這里可以充分運用Excel強大的內置函數的功能去完成，方便而高效。例如在G 6單元格中插入公式：

實現功能：卷面成績不為空時，按比例計算出總評分（當總評分>59時記為60）。其余單元格公式采用自動填充方式填寫。

統計表中分段人數的統計，使用COUNTIF函數。C42單元格插入公式為：

3 保護工作表

為防止誤操作，提高系統數據的安全性，并保證所有用戶上報報表格式的一致，有必要對單元格格式和公式進行保護。對本系統而言，表格中需要用戶填寫的只有（D6:F38） 及（K6:38）區域，其余單元格的值由公式自動計算填充給出，這部分單元格的格式和其中的公式不能被修改或刪除。因此，在系統設計結束前必須對上述單元格進行保護和公式隱藏。Excel系統平臺中提供了這部分功能，通過菜單即可實現。

4 小結

（1）本填報系統采用了交互式的工作方式，簡潔明確，具有友好精良的操作界面。通過各個工作界面提示，用戶可以清楚填寫要求，迅速完成表格的制作。

（2）本文中的源數據由管理員從教務系統復制過來。如果數據量大并且結構復雜，也可以通過Excel提供的MS Query技術從外部數據庫和文件中檢索數據。

（3） 總評成績和統計數據也可以通過VBA程序實現，這里不再贅述。但筆者認為，采用內置函數更為直觀，運行速度也更快，當用戶輸完原始數據的同時就完成了表格的制作。

（4）要運行上述所有功能，需將“宏”安全性設置為“中”，打開該文件時選擇“啟用宏”。

本文通過VBA語言進行二次開發，提供優良的用戶界面，完成Excel工作表之間數據交互，快捷地加載各班級學生名單；利用Excel內置函數完成計算和統計。這樣的工作方式讓用戶在熟悉的Excel平臺下工作，提高了工作效率和應用水平。

[1]Excel Home.Excel VBA實戰技巧精粹[M].北京:人民郵電出版社,2008.

[2]黃海.Excel VBA語法與應用辭典[M].北京:中國青年出版社,2009.

The Design of Transcript Based on Excel Function and Excel VBA

SHI Xiao-ling,YANG Li-gong
（Taizhou Polytechnic College,Taizhou Jiangsu 225300,China）

The paper describes the design of transcript based on Excel function and Excel VBA,and the implementation of data processing automaticity,including preparing data and inputing data,receiving the final course grades and the statistic data.It helps the user to accomplish the special tasks efficiency.

Excel VBA;function;statistics

中國分類號：TP311.1

B

1671-0142（2011）05-0040-03

石曉玲（1973-）,女,江蘇泰州人,副教授,碩士.

（責任編輯李冠楠）