Excel在成績統(tǒng)計(jì)的運(yùn)用

施得天

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

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

中圖分類號(hào)：TP311.1 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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)大部分非計(jì)算機(jī)專業(yè)的教師在使用Excel統(tǒng)計(jì)成績、進(jìn)行成績分析的時(shí)候仍然采用手寫成績表的方式，后續(xù)的分析極為不方便。部分使用Excel處理的表格的教師在面對(duì)大量的數(shù)據(jù)時(shí)也會(huì)難免出現(xiàn)成績手動(dòng)錄入錯(cuò)誤。而通過Excel的一些進(jìn)階操作可以有效地規(guī)避錯(cuò)誤并顯著地提高成績統(tǒng)計(jì)分析效率。本文就如何實(shí)現(xiàn)Excel 在成績統(tǒng)計(jì)中的高效綜合應(yīng)用提出了一些見解。

1 模板

1.1 模板簡(jiǎn)介

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

1.2 創(chuàng)建Excel模板

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

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

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

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

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

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

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

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

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

1.2.4 模板保護(hù)

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

1.3 模板的使用

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

2 使用宏完成成績的分析

2.1 Excel宏簡(jiǎn)介

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

2.2 單一工作表的成績分析

教師對(duì)于成績最基本的分析是建立在一個(gè)完整的班級(jí)上的。比如在全體的基礎(chǔ)上計(jì)算平均分，最高分，最低分，統(tǒng)計(jì)各分?jǐn)?shù)段人數(shù)，及格率等數(shù)據(jù)。因?yàn)樵谙嗤哪０逑?，存放?shù)據(jù)的單元格的位置是固定的。所以機(jī)器可以準(zhǔn)確地找到所需的數(shù)據(jù)。在代碼中編輯對(duì)特定單元格的操作，可以讓宏代碼按照要求在固定的單元格內(nèi)計(jì)算出所需的數(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)計(jì)數(shù)據(jù)，所以使用以下語句完成動(dòng)態(tài)的統(tǒng)計(jì)。

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

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

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

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

2.3 對(duì)各條記錄的分析

教師在分析本班的整體情況的同時(shí)也需要掌握每個(gè)學(xué)生的情況，比如個(gè)人的單科考試的成績浮動(dòng)，但是在一張成績總表的大量數(shù)據(jù)中很難看出一個(gè)人成績的變化趨勢(shì)或是相比其他學(xué)生成績的情況。最好的方法是給每個(gè)學(xué)生建立一張單獨(dú)的工作表以方便查看。但是由于學(xué)生數(shù)量一般較大，手動(dòng)為每個(gè)人建立表格是相當(dāng)麻煩的。所以可以使用宏給每位學(xué)生建立以名字命名的工作表。同樣的使用上述的ar變量存儲(chǔ)學(xué)生個(gè)數(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é)生姓名建立新工作表，在單元格為空時(shí)自動(dòng)停止循環(huán)防止無限制地建立無效的工作表。這種循環(huán)結(jié)構(gòu)結(jié)合前面所述的模板一起使用也能進(jìn)一步為每個(gè)學(xué)生制作電子版的成績單。建立完工作表后就需要在對(duì)應(yīng)的工作表中填充每個(gè)學(xué)生的成績。這個(gè)代碼仍然需要使用循環(huán)結(jié)構(gòu)，將每個(gè)學(xué)生的成績對(duì)應(yīng)地復(fù)制到他的個(gè)人工作表中，并循環(huán)操作至最后一名學(xué)生。

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

3 結(jié)語

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