利用Excel數(shù)組公式設(shè)計考試成績自動分析系統(tǒng)

蘇巖峰

摘 要 考試成績的分析、比較是教務(wù)管理人員經(jīng)常進行的重復(fù)性勞動。多數(shù)教務(wù)管理人員只是應(yīng)用一些零散的統(tǒng)計功能，進行手動的統(tǒng)計工作，費時費力，負擔繁重。可利用Excel的數(shù)組公式實現(xiàn)數(shù)據(jù)的自動化統(tǒng)計分析，并可編輯成人機界面友好，功能強大的成績分析系統(tǒng)，只需將考試數(shù)據(jù)復(fù)制到該系統(tǒng)的相應(yīng)區(qū)域，即可實現(xiàn)全自動分析工作。主要探討該統(tǒng)計分析系統(tǒng)的實現(xiàn)方式。

關(guān)鍵詞 Excel；成績分析系統(tǒng)；數(shù)組公式

中圖分類號：TP391.13 文獻標識碼：B

文章編號：1671-489X（2018）09-0031-03

1 前言

考試成績的分析、管理、比較是中小學(xué)校評價教師教學(xué)水平和學(xué)生學(xué)習效果的重要依據(jù)，也是中小學(xué)教務(wù)管理人員經(jīng)常進行的工作之一。但對于目前很多學(xué)校的相關(guān)教師來說，多數(shù)只是應(yīng)用一些零散的統(tǒng)計功能，進行手動的統(tǒng)計工作，這就使得成績分析成為一項枯燥、煩瑣的工作，尤其在面對班級多、考生數(shù)量多的情況時，費時費力，負擔更重。如果利用Excel的數(shù)組公式，可以實現(xiàn)數(shù)據(jù)的自動化統(tǒng)計分析，并可借助Excel靈活的編輯方式設(shè)計成人機界面友好、功能強大的成績分析系統(tǒng)，每次只需要將考試數(shù)據(jù)復(fù)制到該系統(tǒng)的相應(yīng)區(qū)域，即可實現(xiàn)全自動的分析工作，將使用者從繁重、重復(fù)的勞動中解脫出來。

本文將對如上所述問題的實現(xiàn)展開討論，其具體可實現(xiàn)的功能有：各科優(yōu)秀分數(shù)線的自動劃定，按考號自動分班統(tǒng)計考試人數(shù)，并對總成績及各單科成績的最高分、平均分（不含缺考考生的0分）、排名、優(yōu)秀合格人數(shù)、后十名的平均分（不含0分）、各分數(shù)段人數(shù)進行統(tǒng)計等，如圖1所示。下面將實現(xiàn)過程進行詳細說明。

2 創(chuàng)建“成績單表”

本表用來存儲所有考生的考試數(shù)據(jù)，在A1至M1單元格中依次輸入班級、考號、姓名、總成績、語文、數(shù)學(xué)、外語、物理、化學(xué)、生物、歷史、地理、政治，因后續(xù)統(tǒng)計表需要用到本表數(shù)據(jù)，所以本表結(jié)構(gòu)固定，不可改動。班級如一班為“01”，二班為“02”，因后續(xù)各科成績統(tǒng)計表將利用本字段進行班級索引，故此列數(shù)據(jù)必須嚴格按照該規(guī)定格式存儲（如圖2所示）。

3 建立“參數(shù)表”

一般情況下，可以按照全部參加考試人數(shù)的百分比來確定優(yōu)秀、合格人數(shù)，“參數(shù)表”中就可根據(jù)預(yù)定的整個年級的優(yōu)秀、合格人數(shù)來確定相應(yīng)科目的優(yōu)秀及合格的分數(shù)線和達到人數(shù)（如圖3所示）。故可在B5和B6中分別輸入預(yù)定好的人數(shù)，在語文優(yōu)秀線的E2中輸入公式“=LARGE

（成績單！E：E，$B$5）”，在語文優(yōu)秀人數(shù)的E3中輸入公式“=COUNTIF（成績單！E：E，">="&E2;）”，在語文合格線的E5中輸入公式“=LARGE（成績單！E：E，$B$6）”，在語文合格人數(shù)的E6中輸入公式“=COUNTIF（成績單！E：E，">="&E5;）。其他科目的統(tǒng)計公式類推，此處不再贅述。

公式中用“l(fā)arge（）”函數(shù)在“成績單表”中的相應(yīng)數(shù)據(jù)中找出按預(yù)定人數(shù)設(shè)定的優(yōu)秀或合格分數(shù)，同時利用“countif（）”函數(shù)統(tǒng)計出成績大于或等于該分數(shù)的考生數(shù)，即達到人數(shù)。

4 建立相應(yīng)的總成績及各科目統(tǒng)計詳表

前面所建立的兩個表為統(tǒng)計的數(shù)據(jù)基礎(chǔ)，在此基礎(chǔ)上來建立相應(yīng)科目的統(tǒng)計詳表。下面以數(shù)學(xué)科目為例進行詳細說明。

建立表格框架 新建工作表，并重命名為“數(shù)學(xué)”，在B3至L3單元格中依次輸入班級、考生數(shù)、最高分、總平均、總平均名次、優(yōu)秀人數(shù)、優(yōu)秀名次、合格人數(shù)、合格名次、后10名平均分、后10名平均分名次；M3至V3中依次輸入預(yù)統(tǒng)計人數(shù)的分數(shù)段，如>140、（130，140]等；在B4至B27

中依次輸入各班級代號，如01、02等（此處的班號為從“成績單表”中按班級提取數(shù)據(jù)的依據(jù)，故應(yīng)設(shè)置為與“成績單表”中的班級數(shù)據(jù)格式一致，以文本格式的兩位數(shù)字存在，前面已作詳細說明）。

輸入統(tǒng)計公式 工作表“數(shù)學(xué)”中第四行中的數(shù)據(jù)是求得的01班關(guān)于數(shù)學(xué)考試的有關(guān)成績分析數(shù)據(jù)。在C4單元格中輸入公式“=COUNTIF（成績單！$A：$A，"="&B4;）”，即

可在全部考試數(shù)據(jù)中自動統(tǒng)計出01班本次參加考試的考生數(shù)。在D4單元格中輸入公式“=MAX（（INDIRECT（"成績單！

$a$2：$a$"&COUNTA;（成績單！$A：$A））=$B4）*（INDIRECT（"成績單！$f$2：$f$"&COUNTA;（成績單！$A：$A））））”，之后敲Ctrl+Shift+Enter組合鍵，會自動用一對大括號將公式括起來，這樣就完成了數(shù)組公式的輸入（下文中用大括號括起來的公式表示數(shù)組公式，輸入方法與此處相同）。本公式的意義是：求得“成績單表”A列值為“01”的所有考生中，F(xiàn)列數(shù)據(jù)的最大值（即數(shù)學(xué)科的最高分），這樣就能在全部考生數(shù)據(jù)中將01班的數(shù)學(xué)最高分找出來。

在E4至L4單元中依次輸入其他的各項統(tǒng)計公式如下：

E4： {=AVERAGE（IF（INDIRECT（"成績單！$a$2：$a$"&

COUNTA（成績單！$A：$A））=$B4，IF（INDIRECT（"成績單！$F

$2：$F$"&COUNTA;（成績單！$A：$A））>0，INDIRECT（"成績單

！$F$2：$F$"&COUNTA;（成績單！$A：$A）））））}

F4： =RANK（E4，$E$4：$E$27）

G4： {=COUNT（IF（INDIRECT（"成績單！$A$2：$A$"&

COUNTA（成績單！$A：$A））=$B4，IF（INDIRECT（"成績單！$F

$2：$F$"&COUNTA;（成績單！$A：$A））>=參數(shù)表！$F$2，INDI

RECT（"成績單！$F$2：$F$"&COUNTA;（成績單！$A：$A）））））}

H4： =RANK（G4，$G$4：$G$27）

I4： {=COUNT（IF（INDIRECT（"成績單！$A$2：$A$"&

COUNTA（成績單！$A：$A））=$B4，IF（INDIRECT（"成績單！$F

$2：$F$"&COUNTA;（成績單！$A：$A））>=參數(shù)表！$F$5，INDI

RECT（"成績單！$F$2：$F$"&COUNTA;（成績單！$A：$A）））））}

J4： =RANK（I4，$I$4：$I$27）

K4： {=AVERAGE（SMALL（IF（INDIRECT（"成績單！$a

$2：$a$"&COUNTA;（成績單！$A：$A））=$B4，IF（INDIRECT（"成

績單！$f$2：$f$"&COUNTA;（成績單！$A：$A））>0，INDIRECT

（"成績單！$f$2：$f$"&COUNTA;（成績單！$A：$A）））），{1，2，3，4，5，

6，7，8，9，10}））}

L4： =RANK（K4，$K$4：$K$27）

M4至V4單元格是求得的各分數(shù)段的考生數(shù)，下面以M4和N4舉例說明，其余類推。

M4：求出分數(shù)大于140分的考生數(shù)，其公式為“{=

SUMPRODUCT（（INDIRECT（"成績單！$a$2：$a$"&COUNTA;（成績單！$A：$A））=$B4）*（INDIRECT（"成績單！$F$2：$F$"&COUNTA;

（成績單！$A：$A））>140））}”。

N4：求出分數(shù)在區(qū)間（130，140]的考生數(shù)，其公式為“{

=SUMPRODUCT（（INDIRECT（"成績單！$a$2：$a$"&COUNTA;（成績單！$A：$A））=$B4）*（INDIRECT（"成績單！$F$2：$F$"&

COUNTA（成績單！$A：$A））>130）*（INDIRECT（"成績單！$F$2：

$F$"&COUNTA;（成績單！$A：$A））<=140））}”。

以上為舉例說明的01班數(shù)學(xué)科相關(guān)統(tǒng)計分析項目的實現(xiàn)公式，將這些公式對應(yīng)復(fù)制到5～27行對應(yīng)位置，即可實現(xiàn)對其余23個班的數(shù)據(jù)統(tǒng)計。

5 結(jié)束語

至此，成績統(tǒng)計分析表的各項統(tǒng)計公式全部設(shè)置完成，使用時只需要將考生的原始成績數(shù)據(jù)按“成績單表”的格式整理好，并將其復(fù)制到“成績單表”的對應(yīng)位置，其他工作表將自動進行統(tǒng)計分析。

如果數(shù)據(jù)量過大，為避免在復(fù)制原始成績后再修改個別數(shù)據(jù)造成Excel頻繁地重新計算，可將“工具”—“選項”—“重新計算”設(shè)置為“手動計算”，等一切準備好后，只需要敲F9鍵即可實現(xiàn)一鍵計算。

在本文基礎(chǔ)上還可進一步實現(xiàn)各統(tǒng)計數(shù)據(jù)按班級的對比分析圖表，以折線圖等形式呈現(xiàn)出來，但由于篇幅所限，暫不討論。