如何在EXCEL中实现考试成绩自动分析

王方云

摘 要 学校教学管理部门要求教师每学期对所任学科的期末考试成绩进行成绩统计分析，并做出成绩直方图。此项工作繁琐，手工操作还不准确且极易出错，如果采用EXCEL则完全可以精确实现对考试成绩自动分析并做出成绩直方图。文章对如何在EXCEL中实现考试成绩自动分析作了详细描述。

关键词 EXCEL 成绩 自动分析

中图分类号：TP39 文献标识码：A

How to Achieve Automatic Analysis of Test Scores in EXCEL

WANG Fangyun

(Sichuan Aerospace Polytechnic, Chengdu, Sichuan 610100)

Abstract School education management require teachers for statistical analysis of results each semester for the final examination of any subject, and make performance histogram. This work is tedious, manual operation is not accurate and error-prone, if you can use EXCEL to achieve accurate automatic analysis of test scores and make results histogram. This article detailed describes on how to achieve test results in EXCEL automatic analysis.

Key words EXCEL; score; automatic analysis

1 建立原始表格

在区域A1：M53建立如图1所示的成绩表格。

分别合并并居中下列单元格区域：A1:M1、A2:M2、G3:G53

“序号”列利用填充柄序列填充以实现快速对序号的录入。

由于学校规定总评成绩按平时成绩的30%加考试成绩的70%计算。

在F4中输入：= D4*0.3+E4*0.7。利用填充柄向下填充至F53单元格。

同理在M4中输入：= K4*0.3+L4*0.7。利用填充柄向下填充至M53单元格。

2 建立成绩分析表格

在区域N1：U36建立如图2、3、4所示的试卷分析表格。

为了和学校提供的表格一致，分别合并以下各组单元格：N1:U2、N5:O5、P5:U5、P6:Q6、S6:U6、P7:Q7、S7:U7、P8:Q8、S8:U8、O9:O10、N6:N23、O11:O23、P11:U23、N24:N26、O24:O26、P24:P26、Q24:Q26、R24:R26、S24:S26、T24:T26、U24:U26、N27:O33、P27:U33、P34:U35、N36:O36、T36:U36

在最高成绩P6单元格中输入：= MAX(E4:E53,L4:L53)

在最低成绩P7单元格中输入： = MIN(E4:E53,L4:L53)

在平均成绩P8单元格中输入： = AVERAGE (E4:E53,L4:L53)

在成绩分布P10单元格中输入：= COUNTIF($E$3:$E$53,"> = 90")+COUNTIF($L$4:$L$53,">=90")

在成绩分布Q10单元格中输入：= COUNTIF($E$3:$E$53,">=80")+COUNTIF($L$4:$L$53,">=80")-P10

在成绩分布R10单元格中输入：= COUNTIF($E$3:$E$53,">=70")+COUNTIF($L$4:$L$53,">=70")-Q10-P10

在成绩分布S10单元格中输入：= COUNTIF($E$3:$E$53,">=60")+COUNTIF($L$4:$L$53,">=60")-R10-Q10-P10

在成绩分布T10单元格中输入：= COUNTIF($E$3:$E$53,"<=59")+COUNTIF($L$4:$L$53,"<=59")

在实考人数U10单元格中输入：= COUNT(E4:E53)+COUNT(L4:L53)

在缺考人数S6单元格中输入：= S3-U10

在及格人数S7单元格中输入：= COUNTIF(E4:E53,">=60")+COUNT(L4:L53,">=60")

在及格率S8单元格中输入：= S7/(S3-S6)

通过以上函数和公式实现了成绩分析表各数据的自动统计和计算。

3 建立成绩分析直方图

利用EXCEL的图表向导作成绩分析直方图，如图三所示。

为了使直方图更适合人数的成绩分布，数值Y轴以最大人数分布为准。分类X轴为10-100等分。

在区域W12：W21单元格中分别输入：10、20、30、40、50、60、70、80、90、100

在X12单元格中输入：= COUNTIF($E$4:$E$53,"<=10")+COUNTIF($L$4:$L$53,"<=10")-SUM(X4)

在X13单元格中输入：= COUNTIF($E$4:$E$53,"<=20")+COUNTIF($L$4:$L$53,"<=20")-SUM(X4:X12)

在X14单元格中输入：= COUNTIF($E$4:$E$53,"<=30")+COUNTIF($L$4:$L$53,"<=30")-SUM(X4:X13)

在X15单元格中输入：= COUNTIF($E$4:$E$53,"<=40")+COUNTIF($L$4:$L$53,"<=40")-SUM(X4:X14)

在X16单元格中输入：= COUNTIF($E$4:$E$53,"<=50")+COUNTIF($L$4:$L$53,"<=50")-SUM(X4:X15)

在X17单元格中输入：= COUNTIF($E$4:$E$53,"<=59")+COUNTIF($L$4:$L$53,"<=59")-SUM(X4:X16)

在X18单元格中输入：= COUNTIF($E$4:$E$53,"<=69")+COUNTIF($L$4:$L$53,"<=69")-SUM(X4:X17)

在X19单元格中输入：= COUNTIF($E$4:$E$53,"<=79")+COUNTIF($L$4:$L$53,"<=79")-SUM(X4:X18)

在X20单元格中输入：= COUNTIF($E$4:$E$53,"<=89")+COUNTIF($L$4:$L$53,"<=89")-SUM(X4:X19)

在X21单元格中输入：= COUNTIF($E$4:$E$53,"<=100")+COUNTIF($L$4:$L$53,"<=100")-SUM(X4:X20)

4 工作表的保护

为了实现在原始数据的录入过程中不至于误删改不需要变化的单元格的内容。故应该将要输入原始数据的单元格设置为取消“锁定”。

选定要输入原始数据的单元格和单元格区域：

A2:M2、B4:E53、I4:L53、O3、Q3、S3、U3、P5:U5、Q24:Q26、S24:S26、U24:U26、P27:U35、T36:U36

使用“格式”菜单－“单元格”－“保护”－取消“锁定”

使用“工具”菜单－“保护” －“保护工作表”

5 对试卷的定性分析

根据以上数据定性分析试卷质量。如图4所示。