利用Ｅｘｃｅｌ设计通用成绩统计分析系统

张伟林

摘 要：在学校教学管理中，成绩管理与分析是考察教师和学生教与学情况的重要工作。目前，大多数学校大多只采用了Excel的表格功能，实际上，Excel中的公式及内置函数，能为数据的分析与计算提供强有力的工具。本文主要探讨的是在教学管理的成绩的处理和统计方面，利用Excel，不写程序实现成绩统计分析的系统。

关键词：Excel；教学管理；成绩统计分析系统

中图分类号：TP315 文献标识码：B 文章编号：1673-8454（2008）22-0065-03

在学校教学管理中，成绩管理与分析是考察教师和学生教与学情况的重要工作。目前，大多数学校基本上都是采用Excel进行处理，但他们大多只是用了Excel的表格功能，计算功能用得相当少。实际上，Excel中的公式及内置函数，为数据的分析与计算提供了强有力的工具，在教学管理中，特别是在成绩的处理和统计方面，利用Excel的强大功能完全可以实现复杂的成绩统计分析。它还有高效、灵活的编辑手段、直观的界面设计方法和强大的数据管理功能。

本文讨论的是利用Excel，不写程序实现成绩统计分析的系统。该系统具有：单次多科考试成绩管理、整体成绩统计分析、任意科目按班级进行对比分析、任意科目分数段对比分析图等功能，该系统工作簿整体效果如图 1所示。现举例说明制作过程。

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

先创建一个成绩管理系统工作簿，右击工作表标签“Sheet 1”，改名为“成绩管理系统”， 再按图 1所示格式，在此工作表中建立考试成绩册、学生整体成绩统计分析表、单科成绩按班级对比分析统计表和图。

二、 设置“考试成绩册”

1.设置表头内容

为了使此表能扩展到较多的课程进行成绩管理，可以多预留一些成绩列，在此图中L和Q列间预留了4列，这4列的标题可以暂时不输入表头，当然，使用时还可以按实际的考试科目设置表头和输入具体的成绩，不用的列可以先隐藏起来（不能删除），这将不会影响下文所描述的公式的定义。

2.设置学生总分、平均分公式

由于预留了一些成绩列，故可在Q5单元中输入总分公式：=SUM(E5:P5)。

同样，可以在R5单元中输入平均分公式：=AVERAGE(E5:P5)，但此公式是假定各科以100分制为前提的。为了使此表统计功能更通用，可以为不同的科目设置不同的计分制，为此可以在成绩统计分析区的Y5至AJ5分别设置各科目的分制，如果各科的分制不同，则在计算平均分时，要将各科折合成100分制再计算。因此，要使此表变得更通用，则R5的公式就定义为：=AVERAGE(IF(Y$5:AJ$5>0,E5:P5*100/Y$5:AJ$5))，输入后按Ctrl+Shift+Enter离开此单元，即完成了数组公式，公式会自动用一对大括号括起来（下文中用大括号括起来的公式表示数组公式，输入方法与此处相同），显示形如：{=AVERAGE(IF(Y$5:AJ$5>0,E5:P5*100/Y$5:AJ$5))}。公式的意义是：对分制>0的科目（即考试的科目）的成绩乘以100后除以分制（即转换成100分制）后再求平均值。

3.设置总分名次公式

名次是学生成绩管理中重要的指标，一般是按总分排名，但并不一定要按总分排序，只要统计总分这一列中比当前行的总分多的记录数就可确定当前学生的名次了，为此，只需在S5单元中输入名次计算公式：=COUNTIF(Q:Q,">"&Q5)+1 。

4.设置单科名次公式

此表可以计算任意科目的单科名次，为此，可将AE20作为指定统计科目的单元，使用时可以在此单元格中输入要统计的科目名称，AF20中将自动用公式=MATCH(AE20,E4:S4,0)计算出此科目的序号。为了按指定的科目计算学生的排名，在T5单元中输入名次计算公式：

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

在T4单元中输入公式：=AE20&“的名次” ，则单科名次的标题将自动根据AE20中输入的科目变化。如果AE20中输入“语文”，则AF20中值为：1，T4中将显示“语文的名次”，T5中的公式相当于：=COUNTIF阶段(E:E,">0"&E5)+1，表示统计语文这列中比当前行的值大的记录数，加1后即为该生语文的排名。

5.复制公式

将以上设置好公式的Q5:T5这四个单元内容复制到以下若干行相应位置（也可以通过使用填充柄复制）。此表可以处理一个班的成绩，也可以管理一个年级的成绩，只要将Q5:T5依人数复制相应行数即可，当然也可以多复制一些行作为预留，多余行将不会影响各种统计公式的计算结果。为了考虑通用性，本文复制至第2004行，这样最多能统计2000个学生成绩。

三、设置“学生整体成绩统计分析”表

1.定义表头科目公式

在Y4:AJ4中输入公式引用成绩册中的科目，不必重新输入科目名，为此可以在Y4中输入公式：=E4，并将此公式复制到Z4:AJ4单元中。

2.设置考试科目分制

先在Y5:AJ5中输入各科目的总分（即定义分制），没有考试的科目的分制设置为0。并在AK5中设置各科总分和公式：=SUMIF(E5:P5,">0",Y5:AJ5)。然后在AL5中输入平均分计算的分制：100。

3.设置单科各项统计公式

在Y6至Y16中输入语文科目的各项统计公式：

=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)

并将这些公式复制到AL6:AL16中，以使这些统计项扩展到其它科目。

4.调整AK6:AL9的公式

由于成绩册中预留了多余的行，而这些行中的总分及平均分均为0，在统计平均分、最低分、不及格人数时均会将0统计进去，故在进行这几项数据（AK6:AL9）统计时，公式应该重新调整，调整的方法是：将这几项统计中Q:Q改为Q5:INDEX(Q5:Q2004,$Y23,1)，R:R改为R5:INDEX(R5:R2004,$Y23,1)，其中INDEX(R5:R2004,$Y23,1)表示最后一项有效平均分的位置，当然也可以直接修改为形如Rx具体的单元地址（x表示最后一条记录的行号）。

四、设置“单科成绩按班级对比分析统计”表

1.设置统计科目及成绩册的有关参数公式

为了使本表更通用，能按任意科目进行整体统计分析，可将本表中AE20设置为指定科目的单元，用户在使用时，只要在此单元中输入E4:S4中任意一门科目，此表将可按指定科目进行统计，形成按班级对比分析表。为了便于其它公式的编写，要计算出指定科目在E4:S4科目表中的序号，故AF20中公式为：=MATCH(AE20,E4:S4,0) ，找出指定科目的分制，以便给单科统计表中其它公式引用，故在AH20中输入公式：=INDEX(Y5:AL5,1,AF20)，计算出成绩册有效行数，故AL20公式为：=COUNTIF(C:C,"<>")+3，由于有效行数是按姓名进行统计的，故有效的学生记录必须保证姓名不为空，无效的学生记录保持学号、姓名及各科成绩为空。

还可为AE20设置下拉框提供科目名称选择，方法是：选择AE20单元→“数据”菜单→有效性…→允许：序列→来源：=E4:S4 ，使用时就会有一个下拉框供选择。

2.设置分班统计的参数区

为了进行分班对比分析，需要设置班级条件区，此表假定最多统计20个班的成绩，故在AM24:AM43中分别输入公式：=(D5=$X$24), =(D5=$X$25),…, =(D5=$X$43)，使用时只要在X24到X43中分别输入成绩册中各班的名字，参数区的20个单元自动从输入的班级名中选取要统计的班名生成条件，设置完成后，可以将AM列隐藏。

3.统计单科的整体情况

在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)”表示指定科目的有效数据区域。AH21中存放要统计的分数点，可由用户自行输入。AJ22:AL22中分别存放要统计的名次段，以便使用时设置名次段。

4.设置班级各项统计公式

在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中的公式按行复制到Y25:AL43中。

五、插入“单科成绩分数段按班对比分析图”

先按图1所示的位置，插入“图表”→“折线图”，数据区设置为：

= $X$21:$X$43, $AC$21:$AG$43，设置系列产生在“行”。

通过绘图工具，在Z46处插入文本框，并设置文本内容为：“单科成绩分数段按班对比分析图”。

设置AE48公式为：=AE20。

六、对成绩分析表进行加密保护

为了数据的安全性，防止用户有意或无意更改成绩公式，可对成绩统计分析表中部分单元进行加密保护。具体步骤如下：

选择X4:AM67→单元格格式→保护→选择“锁定”、“隐藏”。

用同样的方法取消Y5:AJ5、AE20、X24:X43、AH21及AJ22:AL22等单元的“锁定”、“隐藏”。

设置Q4:T2004单元“锁定”、“隐藏”。

取消B2:P2004单元的“锁定”、“隐藏”。

单击菜单“工具—保护—保护工作表”，输入密码。以后如果想改动统计公式，必须输入密码才行。最后保存工作表，命名为“成绩管理系统”。

七、结束语

至此成绩统计分析表的各项统计公式全部设置完成。使用时只要修改成绩册中的学生姓名及科目名称，输入各项成绩，并清除不考试的科目及成绩，清除B5:P2004中无用的学生成绩记录，处理完后，其它图表将自动进行统计与绘图。经多所学校的使用，效果良好。

在本文基础上还可进一步实现多次成绩汇总统计分析、生成学生成绩单等功能，但由于篇幅有限，暂不讨论。