利用Excel數組公式設計考試成績自動分析系統

蘇巖峰

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

關鍵詞 Excel；成績分析系統；數組公式

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

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

1 前言

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

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

2 創建“成績單表”

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

3 建立“參數表”

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

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

公式中用“large（）”函數在“成績單表”中的相應數據中找出按預定人數設定的優秀或合格分數，同時利用“countif（）”函數統計出成績大于或等于該分數的考生數，即達到人數。

4 建立相應的總成績及各科目統計詳表

前面所建立的兩個表為統計的數據基礎，在此基礎上來建立相應科目的統計詳表。下面以數學科目為例進行詳細說明。

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

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

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

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

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

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

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））>=參數表！$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））>=參數表！$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單元格是求得的各分數段的考生數，下面以M4和N4舉例說明，其余類推。

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

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

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

N4：求出分數在區間（130，140]的考生數，其公式為“{

=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班數學科相關統計分析項目的實現公式，將這些公式對應復制到5～27行對應位置，即可實現對其余23個班的數據統計。

5 結束語

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

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

在本文基礎上還可進一步實現各統計數據按班級的對比分析圖表，以折線圖等形式呈現出來，但由于篇幅所限，暫不討論。