利用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键即可实现一键计算。

在本文基础上还可进一步实现各统计数据按班级的对比分析图表，以折线图等形式呈现出来，但由于篇幅所限，暂不讨论。