如何在EXCEL中實現(xiàn)考試成績自動分析

王方云

摘 要 學校教學管理部門要求教師每學期對所任學科的期末考試成績進行成績統(tǒng)計分析，并做出成績直方圖。此項工作繁瑣，手工操作還不準確且極易出錯，如果采用EXCEL則完全可以精確實現(xiàn)對考試成績自動分析并做出成績直方圖。文章對如何在EXCEL中實現(xiàn)考試成績自動分析作了詳細描述。

關鍵詞 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 建立原始表格

在區(qū)域A1：M53建立如圖1所示的成績表格。

分別合并并居中下列單元格區(qū)域：A1:M1、A2:M2、G3:G53

“序號”列利用填充柄序列填充以實現(xiàn)快速對序號的錄入。

由于學校規(guī)定總評成績按平時成績的30%加考試成績的70%計算。

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

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

2 建立成績分析表格

在區(qū)域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")

在實考人數(shù)U10單元格中輸入：= COUNT(E4:E53)+COUNT(L4:L53)

在缺考人數(shù)S6單元格中輸入：= S3-U10

在及格人數(shù)S7單元格中輸入：= COUNTIF(E4:E53,">=60")+COUNT(L4:L53,">=60")

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

通過以上函數(shù)和公式實現(xiàn)了成績分析表各數(shù)據的自動統(tǒng)計和計算。

3 建立成績分析直方圖

利用EXCEL的圖表向導作成績分析直方圖，如圖三所示。

為了使直方圖更適合人數(shù)的成績分布，數(shù)值Y軸以最大人數(shù)分布為準。分類X軸為10-100等分。

在區(qū)域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 工作表的保護

為了實現(xiàn)在原始數(shù)據的錄入過程中不至于誤刪改不需要變化的單元格的內容。故應該將要輸入原始數(shù)據的單元格設置為取消“鎖定”。

選定要輸入原始數(shù)據的單元格和單元格區(qū)域：

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

使用“格式”菜單－“單元格”－“保護”－取消“鎖定”

使用“工具”菜單－“保護” －“保護工作表”

5 對試卷的定性分析

根據以上數(shù)據定性分析試卷質量。如圖4所示。