利用Excel2007實現考試成績分段人數的自動統計

姚琦瑋

（湖南省株洲市第二中學G1414班 湖南株洲 412007）

利用Excel2007實現考試成績分段人數的自動統計

姚琦瑋

（湖南省株洲市第二中學G1414班 湖南株洲 412007）

針對考試成績分段人數統計，本文提出了基于Excel2007的數據自動處理方案，解決了傳統人工數據處理的種種弊端，提高了數據處理的效率和準確度，并為其他同類數據處理提供了有益的借鑒。

Excel 單元格 數據 函數

每一次考試結束后，老師都會對考試結果進行統計和分析，以確切了解每個學生排名、平均分、總分，并對全班考試成績分布進行分段統計。如果采用傳統人工方法完成這些重復性的事情，工作量大、枯燥不說，還容易出錯。怎樣解決這個問題呢？Microsoft2007中的Excel的數據處理功能提供了很好的解決方案，由于對學生成績排序、平均分及總分統計操作相對簡單，本文將不做介紹，只討論大家不熟悉的考試成績分段人數的自動統計方法。[1]

Excel 是微軟辦公套裝軟件的一個重要的組成部分，它可以進行各種數據的處理、統計分析和輔助決策操作，廣泛地應用于管理、統計財經、金融等眾多領域。Excel中大量的公式函數可以應用選擇，其中COUNTIF函數、DCOUNT函數、FREQUENCY函數和SUM函數可以應用到考試成績的分析和統計。本文僅討論COUNTIF函數的應用方法，其它函數的使用方法與COUNTIF類似。[2]

COUNTIF函數直譯為“條件計數”函數，即統計指定的單元格中符合條件的單元格個數，公式表達式為：“COUNTIF(數據區間,”邏輯運算符 值”)，邏輯運算符有：“＞”、“＜”、“=”、“＞=”、”＜=”等。舉例說，統計在Excel表格G22到G100單元格中成績小于60分（不及格）的單元格個數，COUNTIF函數表示為：COUNTIF(G22:G100,”＜60”)。[3]

高中考試結果評定有兩種形式，一種是百分制（或150分制），另一種為等級制（優、良、中、及格和不及格）。高中階段語文、數學、英語總分150分，物理、化學、生物總分100分，體育則采用等級制，各種考試結果評定的對應表見表1。[4]

表1 各種考試結果評定對應表

按照等級制統計各科成績對應等級的學生人數。按照如下步驟進行操作：

第一步：將學生各科成績輸入表2所示的學生成績表（版面原因，部分數據隱藏），建立原始的Excel文檔，保存為“學生考試成績表.xlsx”。

表2 學生考試成績表

第二步：在表2后面添加數據統計項目（分段）A51-A55，見表3。

表3 學生成績統計項目

第三步：用鼠標選擇B51單元格，在函數輸入框輸入=COUNTIF(B2:B49,”＜90”) ，系統會自動計算出選定單元格中值小于90的單元格數為0，也就是語文課不及格人數為0。見表4。

表4 統計“語文”成績小于90分（既不及格）人數

同樣，依次選擇B52、B53、B54、B55單元格，并在函數輸入框輸入條件計數函數COUNTIF的對應表達式：=COUNTIF(B2:B49,”＜105”)-COUNTIF(B2:B49,”＜90”) 、=COUNTIF(B2:B49,”＜120”)-COUNTIF(B2:B49,”＜105”)、=COUNTIF(B2:B49,”＜135”)-COUNTIF(B2:B49,”＜120”)、=COUNTIF(B2:B49,”＞=135”) 。每完成一個單元格的函數輸入，系統自動計算出在選定單元格中符合條件的人數。[5]

第四步：用鼠標選擇“B51-B55”共5個單元格，同時按鍵“Ctrl”+“c”，然后將復制結果粘貼到“C51-C55”單元格，即可完成英語成績分段的統計（粘貼的過程中系統會自動修改COUNTIF函數中的數據范圍）。同樣辦法完成理科數學成績統計。

第五步：參照第三步操作，只需將選擇范圍更改為“D2：D49”，分段分隔點改為百分制的分隔點，即可在“D51-D55”單元格中實現物理課程考試結果的統計。（單元格D51、D52、D53、D54、D55中依次輸入函數：=COUNTIF(D2:D49,”＜60”)、=COUNTIF(D2:D49,”＜70”)-COUNTIF(D2:D49,”＜60”)、=COUNTIF(D2:D49,”＜80”)-COUNTIF(D2:D49,”＜70”)、=COUNTIF(D2:D49,”＜90”)-COUNTIF(D2:D49,”＜80”)、=COUNTIF(D2:D49,”＞=90”) ）。

第六步：參照第四步，完成化學、生物等其它百分制課程成績統計。

第七步：體育成績為等級制，所以判別條件為字符串“不及格”、“及格”、“中等”、“良好”和“優秀”。操作方法為在單元格“H51-H55”中依次輸入如下函數：=COUNTIF(H2:H49,“=不及格”)、=COUNTIF(H3:H50,“=及格”)、=COUNTIF(H4:H51,“=中等”)、=COUNTIF(H5:H52,“=良好”)、=COUNTIF(H6:H53,“=優秀”) 。就可以統計出各等級學生人數了。

完成這些工作后，Excel就完成了各科成績分段學生人數的統計，并且統計結果會根據考試數據的變化而自動更新。通過該表格，考試結果一目了然。如表5。

表5 各課程考試成績分段人數統計結果表

當然，利用Excel其他功能，還可以插入各種統計圖形，也可以對學生每次考試成績的升降進行分析，繪制出成績變化圖等等，這些內容在本文中就不做討論了。

Excel具有強大的數據處理能力，靈活應用相關功能函數，在提高勞動效率、確保結果準確、分析結果清晰直觀的同時，還能使枯燥的數據處理變得充滿樂趣。

[1]學校成績統計 教你輕松搞定[J]. 王彥博. 電腦愛好者.2013(12)

[2]基于Excel VBA的S-P表試卷分析系統設計[J]. 郭孝存,王文忠,那木拉,陶紅平. 陰山學刊(自然科學). 2011(02)

[3]基于Excel的學生考試試卷成績分析[J]. 錢禮閏,李艷婷. 安慶師范學院學報(自然科學版). 2011(02)

[4]試卷分析與評價系統的主要指標及關鍵問題處理[J]. 崔國生,張楠,李卓玲. 沈陽工程學院學報(社會科學版). 2011(03)

[5]用EXCEL設計試卷分析模板[J]. 蔣勇. 黑龍江科技信息.2009(24)