利用Ｅｘｃｅｌ設(shè)計(jì)通用成績(jī)統(tǒng)計(jì)分析系統(tǒng)

張偉林

摘 要：在學(xué)校教學(xué)管理中，成績(jī)管理與分析是考察教師和學(xué)生教與學(xué)情況的重要工作。目前，大多數(shù)學(xué)校大多只采用了Excel的表格功能，實(shí)際上，Excel中的公式及內(nèi)置函數(shù)，能為數(shù)據(jù)的分析與計(jì)算提供強(qiáng)有力的工具。本文主要探討的是在教學(xué)管理的成績(jī)的處理和統(tǒng)計(jì)方面，利用Excel，不寫程序?qū)崿F(xiàn)成績(jī)統(tǒng)計(jì)分析的系統(tǒng)。

關(guān)鍵詞：Excel；教學(xué)管理；成績(jī)統(tǒng)計(jì)分析系統(tǒng)

中圖分類號(hào)：TP315 文獻(xiàn)標(biāo)識(shí)碼：B 文章編號(hào)：1673-8454（2008）22-0065-03

在學(xué)校教學(xué)管理中，成績(jī)管理與分析是考察教師和學(xué)生教與學(xué)情況的重要工作。目前，大多數(shù)學(xué)?；旧隙际遣捎肊xcel進(jìn)行處理，但他們大多只是用了Excel的表格功能，計(jì)算功能用得相當(dāng)少。實(shí)際上，Excel中的公式及內(nèi)置函數(shù)，為數(shù)據(jù)的分析與計(jì)算提供了強(qiáng)有力的工具，在教學(xué)管理中，特別是在成績(jī)的處理和統(tǒng)計(jì)方面，利用Excel的強(qiáng)大功能完全可以實(shí)現(xiàn)復(fù)雜的成績(jī)統(tǒng)計(jì)分析。它還有高效、靈活的編輯手段、直觀的界面設(shè)計(jì)方法和強(qiáng)大的數(shù)據(jù)管理功能。

本文討論的是利用Excel，不寫程序?qū)崿F(xiàn)成績(jī)統(tǒng)計(jì)分析的系統(tǒng)。該系統(tǒng)具有：?jiǎn)未味嗫瓶荚嚦煽?jī)管理、整體成績(jī)統(tǒng)計(jì)分析、任意科目按班級(jí)進(jìn)行對(duì)比分析、任意科目分?jǐn)?shù)段對(duì)比分析圖等功能，該系統(tǒng)工作簿整體效果如圖 1所示?，F(xiàn)舉例說(shuō)明制作過(guò)程。

一、建立工作簿、工作表及各圖表框架

先創(chuàng)建一個(gè)成績(jī)管理系統(tǒng)工作簿，右擊工作表標(biāo)簽“Sheet 1”，改名為“成績(jī)管理系統(tǒng)”， 再按圖 1所示格式，在此工作表中建立考試成績(jī)冊(cè)、學(xué)生整體成績(jī)統(tǒng)計(jì)分析表、單科成績(jī)按班級(jí)對(duì)比分析統(tǒng)計(jì)表和圖。

二、 設(shè)置“考試成績(jī)冊(cè)”

1.設(shè)置表頭內(nèi)容

為了使此表能擴(kuò)展到較多的課程進(jìn)行成績(jī)管理，可以多預(yù)留一些成績(jī)列，在此圖中L和Q列間預(yù)留了4列，這4列的標(biāo)題可以暫時(shí)不輸入表頭，當(dāng)然，使用時(shí)還可以按實(shí)際的考試科目設(shè)置表頭和輸入具體的成績(jī)，不用的列可以先隱藏起來(lái)（不能刪除），這將不會(huì)影響下文所描述的公式的定義。

2.設(shè)置學(xué)生總分、平均分公式

由于預(yù)留了一些成績(jī)列，故可在Q5單元中輸入總分公式：=SUM(E5:P5)。

同樣，可以在R5單元中輸入平均分公式：=AVERAGE(E5:P5)，但此公式是假定各科以100分制為前提的。為了使此表統(tǒng)計(jì)功能更通用，可以為不同的科目設(shè)置不同的計(jì)分制，為此可以在成績(jī)統(tǒng)計(jì)分析區(qū)的Y5至AJ5分別設(shè)置各科目的分制，如果各科的分制不同，則在計(jì)算平均分時(shí)，要將各科折合成100分制再計(jì)算。因此，要使此表變得更通用，則R5的公式就定義為：=AVERAGE(IF(Y$5:AJ$5>0,E5:P5*100/Y$5:AJ$5))，輸入后按Ctrl+Shift+Enter離開此單元，即完成了數(shù)組公式，公式會(huì)自動(dòng)用一對(duì)大括號(hào)括起來(lái)（下文中用大括號(hào)括起來(lái)的公式表示數(shù)組公式，輸入方法與此處相同），顯示形如：{=AVERAGE(IF(Y$5:AJ$5>0,E5:P5*100/Y$5:AJ$5))}。公式的意義是：對(duì)分制>0的科目（即考試的科目）的成績(jī)乘以100后除以分制（即轉(zhuǎn)換成100分制）后再求平均值。

3.設(shè)置總分名次公式

名次是學(xué)生成績(jī)管理中重要的指標(biāo)，一般是按總分排名，但并不一定要按總分排序，只要統(tǒng)計(jì)總分這一列中比當(dāng)前行的總分多的記錄數(shù)就可確定當(dāng)前學(xué)生的名次了，為此，只需在S5單元中輸入名次計(jì)算公式：=COUNTIF(Q:Q,">"&Q5)+1 。

4.設(shè)置單科名次公式

此表可以計(jì)算任意科目的單科名次，為此，可將AE20作為指定統(tǒng)計(jì)科目的單元，使用時(shí)可以在此單元格中輸入要統(tǒng)計(jì)的科目名稱，AF20中將自動(dòng)用公式=MATCH(AE20,E4:S4,0)計(jì)算出此科目的序號(hào)。為了按指定的科目計(jì)算學(xué)生的排名，在T5單元中輸入名次計(jì)算公式：

=COUNTIF(INDEX($E$1:$S$2004,0,AF$20),">0"&INDEX($E$1:$S$2004,ROW(),AF$20))+1

在T4單元中輸入公式：=AE20&“的名次” ，則單科名次的標(biāo)題將自動(dòng)根據(jù)AE20中輸入的科目變化。如果AE20中輸入“語(yǔ)文”，則AF20中值為：1，T4中將顯示“語(yǔ)文的名次”，T5中的公式相當(dāng)于：=COUNTIF階段(E:E,">0"&E5)+1，表示統(tǒng)計(jì)語(yǔ)文這列中比當(dāng)前行的值大的記錄數(shù)，加1后即為該生語(yǔ)文的排名。

5.復(fù)制公式

將以上設(shè)置好公式的Q5:T5這四個(gè)單元內(nèi)容復(fù)制到以下若干行相應(yīng)位置（也可以通過(guò)使用填充柄復(fù)制）。此表可以處理一個(gè)班的成績(jī)，也可以管理一個(gè)年級(jí)的成績(jī)，只要將Q5:T5依人數(shù)復(fù)制相應(yīng)行數(shù)即可，當(dāng)然也可以多復(fù)制一些行作為預(yù)留，多余行將不會(huì)影響各種統(tǒng)計(jì)公式的計(jì)算結(jié)果。為了考慮通用性，本文復(fù)制至第2004行，這樣最多能統(tǒng)計(jì)2000個(gè)學(xué)生成績(jī)。

三、設(shè)置“學(xué)生整體成績(jī)統(tǒng)計(jì)分析”表

1.定義表頭科目公式

在Y4:AJ4中輸入公式引用成績(jī)冊(cè)中的科目，不必重新輸入科目名，為此可以在Y4中輸入公式：=E4，并將此公式復(fù)制到Z4:AJ4單元中。

2.設(shè)置考試科目分制

先在Y5:AJ5中輸入各科目的總分（即定義分制），沒(méi)有考試的科目的分制設(shè)置為0。并在AK5中設(shè)置各科總分和公式：=SUMIF(E5:P5,">0",Y5:AJ5)。然后在AL5中輸入平均分計(jì)算的分制：100。

3.設(shè)置單科各項(xiàng)統(tǒng)計(jì)公式

在Y6至Y16中輸入語(yǔ)文科目的各項(xiàng)統(tǒng)計(jì)公式：

=AVERAGE(E:E)

=MIN(E:E)

=MAX(E:E)

=COUNTIF(E:E,"<"&(Y5*0.6))

=COUNTIF(E:E,">="&(Y5*0.6))-Y11-Y12-Y13

=COUNTIF(E:E,">="&(Y5*0.7))-Y12-Y13

=COUNTIF(E:E,">="&(Y5*0.8))-Y13

=COUNTIF(E:E,">="&(Y5*0.9))

=COUNTIF(E:E,">="&(Y5*0.85))

=1-Y9/SUM(Y9:Y13)

=Y14/SUM(Y9:Y13)

并將這些公式復(fù)制到AL6:AL16中，以使這些統(tǒng)計(jì)項(xiàng)擴(kuò)展到其它科目。

4.調(diào)整AK6:AL9的公式

由于成績(jī)冊(cè)中預(yù)留了多余的行，而這些行中的總分及平均分均為0，在統(tǒng)計(jì)平均分、最低分、不及格人數(shù)時(shí)均會(huì)將0統(tǒng)計(jì)進(jìn)去，故在進(jìn)行這幾項(xiàng)數(shù)據(jù)（AK6:AL9）統(tǒng)計(jì)時(shí)，公式應(yīng)該重新調(diào)整，調(diào)整的方法是：將這幾項(xiàng)統(tǒng)計(jì)中Q:Q改為Q5:INDEX(Q5:Q2004,$Y23,1)，R:R改為R5:INDEX(R5:R2004,$Y23,1)，其中INDEX(R5:R2004,$Y23,1)表示最后一項(xiàng)有效平均分的位置，當(dāng)然也可以直接修改為形如Rx具體的單元地址（x表示最后一條記錄的行號(hào)）。

四、設(shè)置“單科成績(jī)按班級(jí)對(duì)比分析統(tǒng)計(jì)”表

1.設(shè)置統(tǒng)計(jì)科目及成績(jī)冊(cè)的有關(guān)參數(shù)公式

為了使本表更通用，能按任意科目進(jìn)行整體統(tǒng)計(jì)分析，可將本表中AE20設(shè)置為指定科目的單元，用戶在使用時(shí)，只要在此單元中輸入E4:S4中任意一門科目，此表將可按指定科目進(jìn)行統(tǒng)計(jì)，形成按班級(jí)對(duì)比分析表。為了便于其它公式的編寫，要計(jì)算出指定科目在E4:S4科目表中的序號(hào)，故AF20中公式為：=MATCH(AE20,E4:S4,0) ，找出指定科目的分制，以便給單科統(tǒng)計(jì)表中其它公式引用，故在AH20中輸入公式：=INDEX(Y5:AL5,1,AF20)，計(jì)算出成績(jī)冊(cè)有效行數(shù)，故AL20公式為：=COUNTIF(C:C,"<>")+3，由于有效行數(shù)是按姓名進(jìn)行統(tǒng)計(jì)的，故有效的學(xué)生記錄必須保證姓名不為空，無(wú)效的學(xué)生記錄保持學(xué)號(hào)、姓名及各科成績(jī)?yōu)榭铡?/p>

還可為AE20設(shè)置下拉框提供科目名稱選擇，方法是：選擇AE20單元→“數(shù)據(jù)”菜單→有效性…→允許：序列→來(lái)源：=E4:S4 ，使用時(shí)就會(huì)有一個(gè)下拉框供選擇。

2.設(shè)置分班統(tǒng)計(jì)的參數(shù)區(qū)

為了進(jìn)行分班對(duì)比分析，需要設(shè)置班級(jí)條件區(qū)，此表假定最多統(tǒng)計(jì)20個(gè)班的成績(jī)，故在AM24:AM43中分別輸入公式：=(D5=$X$24), =(D5=$X$25),…, =(D5=$X$43)，使用時(shí)只要在X24到X43中分別輸入成績(jī)冊(cè)中各班的名字，參數(shù)區(qū)的20個(gè)單元自動(dòng)從輸入的班級(jí)名中選取要統(tǒng)計(jì)的班名生成條件，設(shè)置完成后，可以將AM列隱藏。

3.統(tǒng)計(jì)單科的整體情況

在Y23:AL23中分別輸入以下公式：

=COUNTIF(C5:C2004,"＜＞")

=AVERAGE(OFFSET(E5:R2004,0,AF20-1,Y23,1)) =MIN(OFFSET(E5:R2004,0,AF20-1,Y23,1)) =MAX(OFFSET(E5:R2004,0,AF20-1,Y23,1)) =COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＜"&AH20*0.6) =COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＜"&AH20*0.7)-AC23 =COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＜"&AH20*0.8)-AD23-AC23 =COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＜"&AH20*0.9)-AE23-AD23-AC23 =COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＞="&AH20*0.9) =COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＞="&AH20*AH21/100) =1-AC23/Y23

=COUNTIF($T:$T,"＜="&AJ22)

=COUNTIF($T:$T,"＜="&AK22)

=COUNTIF($T:$T,"＜="&AL22)

以上公式中“OFFSET(E5:R2004,0,AF20-1,Y23,1)”表示指定科目的有效數(shù)據(jù)區(qū)域。AH21中存放要統(tǒng)計(jì)的分?jǐn)?shù)點(diǎn)，可由用戶自行輸入。AJ22:AL22中分別存放要統(tǒng)計(jì)的名次段，以便使用時(shí)設(shè)置名次段。

4.設(shè)置班級(jí)各項(xiàng)統(tǒng)計(jì)公式

在Y24:AL24中分別輸入以下公式：

=DCOUNT($B$4:$S$2004,$AE$20,AM23:AM24) =DAVERAGE($B$4:$S$2004,$AE$20,AM23:AM24)

=DMIN($B$4:$S$2004,$AE$20,AM23:AM24)

=DMAX($B$4:$S$2004,$AE$20,AM23:AM24) =SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＜0.6*$AH$20)) =SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＜0.7*$AH$20))-AC24 =SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＜0.8*$AH$20))-AD24-AC24

=SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＜0.9*$AH$20))-AE24-AD24-AC24 =SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＞=0.9*$AH$20)) =SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＞=AH$21*$AH$20/100)) =1-AC24/Y24

=SUMPRODUCT(($D$5:$D$2004=$X24)*($S$5:$S$2004＜=AJ$22)) =SUMPRODUCT(($D$5:$D$2004=$X24)*($S$5:$S$2004＜=AK$22)) =SUMPRODUCT(($D$5:$D$2004=$X24)*($S$5:$S$2004＜=AL$22))

將Y24:AL24中的公式按行復(fù)制到Y(jié)25:AL43中。

五、插入“單科成績(jī)分?jǐn)?shù)段按班對(duì)比分析圖”

先按圖1所示的位置，插入“圖表”→“折線圖”，數(shù)據(jù)區(qū)設(shè)置為：

= $X$21:$X$43, $AC$21:$AG$43，設(shè)置系列產(chǎn)生在“行”。

通過(guò)繪圖工具，在Z46處插入文本框，并設(shè)置文本內(nèi)容為：“單科成績(jī)分?jǐn)?shù)段按班對(duì)比分析圖”。

設(shè)置AE48公式為：=AE20。

六、對(duì)成績(jī)分析表進(jìn)行加密保護(hù)

為了數(shù)據(jù)的安全性，防止用戶有意或無(wú)意更改成績(jī)公式，可對(duì)成績(jī)統(tǒng)計(jì)分析表中部分單元進(jìn)行加密保護(hù)。具體步驟如下：

選擇X4:AM67→單元格格式→保護(hù)→選擇“鎖定”、“隱藏”。

用同樣的方法取消Y5:AJ5、AE20、X24:X43、AH21及AJ22:AL22等單元的“鎖定”、“隱藏”。

設(shè)置Q4:T2004單元“鎖定”、“隱藏”。

取消B2:P2004單元的“鎖定”、“隱藏”。

單擊菜單“工具—保護(hù)—保護(hù)工作表”，輸入密碼。以后如果想改動(dòng)統(tǒng)計(jì)公式，必須輸入密碼才行。最后保存工作表，命名為“成績(jī)管理系統(tǒng)”。

七、結(jié)束語(yǔ)

至此成績(jī)統(tǒng)計(jì)分析表的各項(xiàng)統(tǒng)計(jì)公式全部設(shè)置完成。使用時(shí)只要修改成績(jī)冊(cè)中的學(xué)生姓名及科目名稱，輸入各項(xiàng)成績(jī)，并清除不考試的科目及成績(jī)，清除B5:P2004中無(wú)用的學(xué)生成績(jī)記錄，處理完后，其它圖表將自動(dòng)進(jìn)行統(tǒng)計(jì)與繪圖。經(jīng)多所學(xué)校的使用，效果良好。

在本文基礎(chǔ)上還可進(jìn)一步實(shí)現(xiàn)多次成績(jī)匯總統(tǒng)計(jì)分析、生成學(xué)生成績(jī)單等功能，但由于篇幅有限，暫不討論。