施得天

摘要：Excel作為現(xiàn)在最流行也是最強大的電子表格軟件，擁有強大的統(tǒng)計功能?？梢猿錾赝瓿沙煽兘y(tǒng)計的工作。輔之以VBA程序可以快速地完成常規(guī)的成績操作。

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

中圖分類號：TP311.1 文獻標(biāo)識碼：A 文章編號：1009-3044（2018）19-0224-02

Abstract： Excel is the most popular and powerful spreadsheet software which has powerful statistical functions. The work of statistics can be accomplished well. With the VBA programming， the routine practices can be done quickly.

Key words： Excel； statistics of score； VBA； Macro

通過調(diào)查發(fā)現(xiàn)大部分非計算機專業(yè)的教師在使用Excel統(tǒng)計成績、進行成績分析的時候仍然采用手寫成績表的方式，后續(xù)的分析極為不方便。部分使用Excel處理的表格的教師在面對大量的數(shù)據(jù)時也會難免出現(xiàn)成績手動錄入錯誤。而通過Excel的一些進階操作可以有效地規(guī)避錯誤并顯著地提高成績統(tǒng)計分析效率。本文就如何實現(xiàn)Excel 在成績統(tǒng)計中的高效綜合應(yīng)用提出了一些見解。

1 模板

1.1 模板簡介

Excel的模板文件（.xtml）可以將各種字體、內(nèi)容、單元格、操作權(quán)限、計算函數(shù)的格式預(yù)置在工作簿中。它只有格式、主題，而沒有具體的數(shù)據(jù)。相比每次新建工作簿的做法，使用模板可以減少相同工作的工作量，保證了每份成績表的格式一致，方便后續(xù)的匯總，也能在一定程度上減少數(shù)據(jù)輸入或格式錯誤。

1.2 創(chuàng)建Excel模板

1.2.1 成績表字段數(shù)據(jù)格式

為了保證各成績表格式一致，模板中應(yīng)預(yù)置成績錄入的要求。下文中將每列的數(shù)據(jù)成為一個字段，每行的信息稱為一條記錄。格式最基本的要求就是字段的順序即每列應(yīng)該輸入的信息。以高等數(shù)學(xué)的成績表為例：大學(xué)開設(shè)不同課程的班級大多不是自然班，所以學(xué)生可能來自不同院系，不同專業(yè)。為了方便區(qū)分，學(xué)生個人信息要求填寫學(xué)院、專業(yè)、姓名、學(xué)號加以區(qū)分并方便匯總。個人信息后的字段即為高等數(shù)學(xué)歷次考試的成績。

1.2.2 建立特定字段的聯(lián)動菜單

對于一些特定的字段例如學(xué)院、專業(yè)，字段下的值只可能為固定的幾個。為了減少相同的工作量并且防止手動輸入時的人為錯誤?？梢越⑾吕藛喂┛焖龠x擇。首先在工作表2中輸入學(xué)院、專業(yè)可供選擇的值。然后返回工作表1，在菜單中點擊公式-建立名稱管理器，新建名稱：學(xué)院，引用位置為工作表2中的三個學(xué)院的單元格。再選中學(xué)院字段下的單元格，設(shè)置數(shù)據(jù)有效性，允許范圍為序列，來源輸入學(xué)院。確定后選擇學(xué)院字段下的單元格就會自動出現(xiàn)選擇菜單，大大減少了工作時間和出錯的可能性。

對專業(yè)字段的下拉菜單的建立要比學(xué)院復(fù)雜一些。因為不同的學(xué)院下有不同的專業(yè)，所以專業(yè)的下拉菜單需要和學(xué)院字段聯(lián)動。再次新建名稱管理器，為每個學(xué)院各自新建名稱，引用位置就是工作表中各學(xué)院下的專業(yè)所在單元格，新建如下：

新建完成后，選中工作表中的專業(yè)下的單元格，設(shè)置數(shù)據(jù)有效性，允許范圍仍為序列。由于序列的值與學(xué)院的值有關(guān)，所以使用Indirect間接引用函數(shù)，使用方式為Indirect（$A3）（以上述成績表模板為例），$A3即混合引用，指在專業(yè)字段的單元格位置改變的時候，引用的地址列數(shù)不變，行數(shù)隨之變化。這樣，專業(yè)單元格就會根據(jù)對應(yīng)的學(xué)院的值和名稱管理器的文件生成對應(yīng)的下拉菜單。

1.2.3 成績的數(shù)據(jù)有效性及條件樣式

在各個考試成績的字段下也可以使用數(shù)據(jù)有效性來規(guī)避一些錯誤。選中用于填寫成績的單元格，設(shè)置數(shù)據(jù)有效性，允許范圍為整數(shù)并且大于0小于100。這樣輸入成績時如果多輸了一位，Excel就會彈出輸入值非法的對話框。

成績表中不及格的成績一般都以紅色標(biāo)記，這也可以做到模板中。選中填寫成績的單元格，設(shè)置條件格式-突出選擇單元格規(guī)則-小于，設(shè)置條件為小于60，格式為自定義-字體顏色設(shè)置為紅色。這樣輸入成績的同時，Excel就會自動標(biāo)紅低于60的成績。

1.2.4 模板保護

模板的作用之一就是保證每份成績表的格式的一致性。所以以模板創(chuàng)建的工作簿必須保證初始的格式不被破壞。Excel提供了保護工作表的功能以為了防止誤操作改變模板預(yù)置的內(nèi)容。先將預(yù)設(shè)置好的單元格格式改為保護，再在審閱-保護工作表中勾選允許編輯對象，輸入保護密碼。這樣使用者就不會誤操作更改被保護的單元格。

1.3 模板的使用

電腦上安裝的Excel應(yīng)用會在自己的根目錄下建立模板文件夾template。所有在建立新的工作簿時調(diào)用的模板都保存在template這個文件夾里。將模板保存在template文件夾中，在打開Excel后選擇新建-我的模板即可以保存的模板建立工作簿。

2 使用宏完成成績的分析

2.1 Excel宏簡介

宏（macro）是一種批量處理的稱謂，它可以用一條或幾條指令批量完成相同任務(wù)。教師在不同成績進行分析時完成的工作本質(zhì)上時相同的。這就可以使用宏代為完成。Excel中的宏可以保存在工作表中，在需要的時候進行調(diào)用就可以執(zhí)行代碼中的過程，輔助完成分析工作。

2.2 單一工作表的成績分析

教師對于成績最基本的分析是建立在一個完整的班級上的。比如在全體的基礎(chǔ)上計算平均分，最高分，最低分，統(tǒng)計各分?jǐn)?shù)段人數(shù)，及格率等數(shù)據(jù)。因為在相同的模板下，存放數(shù)據(jù)的單元格的位置是固定的。所以機器可以準(zhǔn)確地找到所需的數(shù)據(jù)。在代碼中編輯對特定單元格的操作，可以讓宏代碼按照要求在固定的單元格內(nèi)計算出所需的數(shù)據(jù)。

ActiveCell.FormulaR1C1 = "=MAX（R[-19]C：R[-2]C）"

Range（"E22"）.Select

Selection.AutoFill Destination：=Range（"E22：K22"）， Type：=xlFillDefault

Range（"E22：K22"）.Select

Range（"E23"）.Select

…

由于各班的人數(shù)不同，不能每次在相同的地方生成統(tǒng)計數(shù)據(jù)，所以使用以下語句完成動態(tài)的統(tǒng)計。

ar = range（"e3：e" & Cells（Rows.Count， 2）.End（3）.Row）

這樣，ar既存儲了成績的數(shù)據(jù)個數(shù)，又能表示成績數(shù)據(jù)的單元格范圍。在使用ar作為成績數(shù)量時使用 Ubound（ar） 返回ar中最大下標(biāo)，即為成績的數(shù)量。

代碼完成后保存。當(dāng)再次使用時只需在開發(fā)工具-宏中找到所需的宏雙擊執(zhí)行即可完成代碼中包含的操作。

下圖為使用宏后產(chǎn)生的各科成績分析表。

2.3 對各條記錄的分析

教師在分析本班的整體情況的同時也需要掌握每個學(xué)生的情況，比如個人的單科考試的成績浮動，但是在一張成績總表的大量數(shù)據(jù)中很難看出一個人成績的變化趨勢或是相比其他學(xué)生成績的情況。最好的方法是給每個學(xué)生建立一張單獨的工作表以方便查看。但是由于學(xué)生數(shù)量一般較大，手動為每個人建立表格是相當(dāng)麻煩的。所以可以使用宏給每位學(xué)生建立以名字命名的工作表。同樣的使用上述的ar變量存儲學(xué)生個數(shù)。

Set d = CreateObject（"scripting.dictionary"）

ar = range（"c3：c" & Cells（Rows.Count， 2）.End（3）.Row）

For i = 1 To Sheets.Count

Debug.Print Sheets（i）.Name

d（Sheets（i）.Name & "|"） = ""

Next

k = d.keys

For i = 1 To UBound（ar）

If Not d.exists（ar（i， 1） & "|"） Then Sheets.Add： ActiveSheet.Name = ar（i， 1）

Next

…

上述代碼使用循環(huán)依次以學(xué)生姓名建立新工作表，在單元格為空時自動停止循環(huán)防止無限制地建立無效的工作表。這種循環(huán)結(jié)構(gòu)結(jié)合前面所述的模板一起使用也能進一步為每個學(xué)生制作電子版的成績單。建立完工作表后就需要在對應(yīng)的工作表中填充每個學(xué)生的成績。這個代碼仍然需要使用循環(huán)結(jié)構(gòu)，將每個學(xué)生的成績對應(yīng)地復(fù)制到他的個人工作表中，并循環(huán)操作至最后一名學(xué)生。

下圖為使用VBA后產(chǎn)生的單個學(xué)生成績表。

3 結(jié)語

本文只講解了基本的模板建立和成績分析操作，一些問題仍然無法避免：模板并不能保證規(guī)避所有的錄入錯誤。也存在一些不方便的地方：比如對于格式的要求太嚴(yán)格，宏不能靈活地隨著不同的模板而改變。這些可以進一步使用高級編程語言結(jié)合電子閱卷和更多樣的模板來解決，為教師實現(xiàn)真正的減負(fù)，使教師從煩瑣的成績工作中解放出來，更多地投入到教學(xué)中。