石曉玲，楊立功

（泰州職業(yè)技術(shù)學院，江蘇泰州 225300）

基于Excel VBA及函數(shù)的成績上報單的制作

石曉玲，楊立功

（泰州職業(yè)技術(shù)學院，江蘇泰州 225300）

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

Excel VBA；函數(shù)；統(tǒng)計

VBA是Visual Basic for Application的簡稱，是集成在M icrosoft Office應(yīng)用程序中的一種程序設(shè)計語言，能夠?qū)崿F(xiàn)辦公自動化，從而極大地提高工作效率。VBA技術(shù)為辦公自動化用戶提供了面向?qū)ο蟮某绦蛟O(shè)計方法及相當完整的程序設(shè)計語言。其獨特之處在于它由應(yīng)用程序控制，反過來又可以增強應(yīng)用程序的功能。

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

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

1 任務(wù)分析

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

2 實現(xiàn)步驟

2.1 系統(tǒng)設(shè)計

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

2.2 窗體及代碼設(shè)計

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

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

圖2 用戶開始界面

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

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

圖3 選擇班級窗口

圖4 填寫成績比例

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

2.3 總評成績和統(tǒng)計數(shù)據(jù)的生成

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

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

統(tǒng)計表中分段人數(shù)的統(tǒng)計，使用COUNTIF函數(shù)。C42單元格插入公式為：

3 保護工作表

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

4 小結(jié)

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

（2）本文中的源數(shù)據(jù)由管理員從教務(wù)系統(tǒng)復制過來。如果數(shù)據(jù)量大并且結(jié)構(gòu)復雜，也可以通過Excel提供的MS Query技術(shù)從外部數(shù)據(jù)庫和文件中檢索數(shù)據(jù)。

（3） 總評成績和統(tǒng)計數(shù)據(jù)也可以通過VBA程序?qū)崿F(xiàn)，這里不再贅述。但筆者認為，采用內(nèi)置函數(shù)更為直觀，運行速度也更快，當用戶輸完原始數(shù)據(jù)的同時就完成了表格的制作。

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

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

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

[2]黃海.Excel VBA語法與應(yīng)用辭典[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-）,女,江蘇泰州人,副教授,碩士.

（責任編輯李冠楠）