利用Excel實現成績的自動統計
每當學期結束，試卷評改完畢，我校都要對各個年級各科目成績進行統計和分析，以便期末進行評先評優、成績考核、獎懲等事宜，其中包括各科目的平均分、及格率、優生率等專案。以前通過手工計算，但效率低，工作量大，後來用一些成績統計軟體，工作輕鬆了，但不能很好的滿足需要。
經過一段時間摸索，利用Excel函式功能，很好的解決了我校成績統計的各種問題。下面我將就如何利用Excel實現成績的自動統計進行簡單的描述。
一、需求分析
在各科成績統計和分析的專案包括參考人數、平均分、及格人數、及格率、優生率、優生人數、各分數段以及每科的最高分，為了更好的作為資料儲存，還要加上每班的班主任和任課教師。最終成績統計表格(語文科，其它類似)如下圖：
二、總體設計
新建空白Excel工作簿，將自動建立的Sheet1、Sheet2和 Sheet3工作表重新命名為成績分析表、TSCJ和Teacher， 其中TSCJ工作表用於輸入各科成績，Teacher工作表用於輸入教師資訊。
（一）、定製TSCJ工作表
在TSCJ工作表第一行中輸入統計名稱，如“中心校2004-2005學年上期各科成績統計”，在第二行從A1單元格開始順序輸入記錄欄位名：學號、姓名、考號、班級、語文、數學、外語、政治、歷史、地理、生物和總分。
（二）、定製成績統計和分析工作表
對成績統計和分析工作表的定製多而複雜，下面就以語文科統計表定製為例進行描述，其它科的統計類比操作。
1、 定製語文科統計表基本框架
將單位格A1：N1合併，並在單位格中輸入=”TSCJ!A1”得到統計名稱。再合併單位格A2：G2，在單位格輸入“=TSCJ!D1”得到統計科目，即“語文”。參考上圖，定製其它單位格。
2、求語文科各班參考人數
首先在B4單元格中輸入“=COUNTIF(TSCJ!$C$2: TSCJ!$C$1501,"=" & A4)”，其中COUNTIF函式用於計算給定區域內滿足特定條件的單元格的數目，通過COUNTIF函式求出1班的參考人數。然後選中B4單元格，拖動B4單元格右下角的小“十”字圖示至B7單元格（這種操作本文統稱為向下自動填充操作），這樣，就能自動得到B5至B7單元格的公式。最後在B8單元格中輸入“=SUM(B4:B8)”，用於統計全年級參考總人數。
3、 求語文科各班平均分
首先在C4單元格中輸入“=IF(B4=0,0,SUMIF(TSCJ!$C$2: TSCJ!$C$1501,"=" & A4, TSCJ!$E$2: TSCJ!$E$1501)/B4) ”，其中SUMIF函式功能是根據指定條件對若干單元格求和。為了避免分母為0的錯誤產生，單元格中用到了IF函式。該單元格公式可理解為：若1班參考人數不為0，則把1班語文科總分除以參考人員得到平均分。然後對C4至C7單元格進行向下自動填充操作，就可以自動得到C5至C7單元格的公式。最後在C8單元格中輸入“=IF(B7=0,0,SUM(TSCJ!$D$2: TSCJ!$D$1501)/B7)”，求出全年級平均分。
4、求語文科各班及格人數
在求各班及格人數時，利用COUNTIF函式功能比較難滿足設計要求，為了解決這種多條件的計算功能，使用了陣列公式進行多重標準單元計算。首先在D4單元格中輸入“=SUM((TSCJ!$C$2: TSCJ!$C$1501=A4)*( TSCJ!$E$2: TSCJ!$E$1501>
=60))”，按下SHIFT＋CTRL鍵敲回車(這是生成陣列公式的關鍵環節)，Excel會自動在公式兩側加上大括號,生成陣列公式。然後對D4至D7單元格進行向下自動填充操作。最後在D8中輸入“=SUM(D4:D7)”，求出全級及格人數。
5、求語文科各班合格率
首先在E4單元格中輸入“=IF(B4=0,0,D4/B4)*100”，為避免分母為O的錯誤，用了IF函式。公式可以理解為：若1班參考人數不為0，則合格率為合格人數除以參考人數後乘以100。然後對E4至E7單元格進行向下自動填充操作。
6、求語文科各班優生人數
我校對分數高於或等於80分的.學生稱為優生。首先在H4單元格中輸入“=SUM((TSCJ!$C$2: TSCJ!$C$1501=A4)*( TSCJ!$E$2: TSCJ!$E$1501>=80))”，按下SHIFT＋CTRL鍵敲回車(這是生成陣列公式的關鍵環節)，Excel會自動在公式兩側加上大括號,生成陣列公式。然後對H4至H7單元格進行向下自動填充操作。最後在H24單元格中輸入“=SUM(H4:H7)”，以求得全年級優生人數。
7、求語文科各班優生率
首先在I4單元格中輸入“=IF(B4=0,0,H4/B4)*100”，為避免分母為O的錯誤，用了IF函式。公式可以理解為：若1班參考人數不為0，則優生率為優生人數除以參考人數後乘以100。然後對I4至I7單元格進行向下自動填充操作。
8、求語文科各班小於分數段人數
在J4輸入“=SUM((TSCJ!$C$2: TSCJ!$C$1501=A4)*( TSCJ!$E$2: TSCJ!$E$1501<60))”，按下SHIFT＋CTRL鍵敲回車，然後對J4至J23單元格進行向下自動填充操作。，然後在J24單元格中輸入“=SUM(J4:J7)”，以求得全年級小於分數段人數。
9、求語文科各班60-70分數段人數
求語文科各班60-70分數段人數，將用到三個條件進行標準單元計算。首先在K4單 =SUM((TSCJ!$C$2:TSCJ!$C$1501=A4)*(TSCJ!$E$2:TSCJ!$E$1501<70)*(tscj!$e$2:tscj!$e$1501>=60))”，按下SHIFT＋CTRL鍵敲回車，生成陣列公式。然後對K4至K7單元格進行向下自動填充操作。最後在K8單元格中輸入“=SUM(K4:K7)”，求出全年級60-70分數段人數。
同樣的操作，可以分別統計各班語文科共它各分數段人數和總分各分數段人類。參考8、9進行。
10、求語文科各班最高分
在T4輸入“=MAX(IF(TSCJ!$C$2: TSCJ!$C$1501=A4, TSCJ!$D$2: TSCJ!$D$1501))”
按下SHIFT＋CTRL鍵敲回車，生成陣列公式。然後對T4至T23單元格進行向下自動填充操作。最後在T8單元格中輸入“=MAX(T4:T7)”，求出全級最高分。
（三）定製Teacher工作表
在Teacher第一行依次輸入：班級、語文、數學、英語、政治、歷史、地理、生物和班主任。
1、 求各科任課教師
在成績統計工作表的N4單位格輸入”=Teacher!B2”, 然後選中N4單元格，拖動N4單元格右下角的小“十”字圖示至N7單元格.
2、 求各班班主任
在成績統計工作表的N52單位格輸入”=Teacher!B2”, 然後選中N52單元格，拖動N4單元格右下角的小“十”字圖示至N55單元格.
三 保護成績統計工作表
為了維護資料的安全性，防止非法使用者善自有意或無意更改成績資料，我們對成績統計工作表進行加密保護。單擊選單“檔案→工具→保護→保護工作表”，輸入兩次密碼。以後如果想改動統計方法，必須輸入密碼才行。最後儲存工作表，命名為“成績自動統計Excel表”。
通過上述操作，當我們要進行學校成績統計時，只要開啟“成績自動統計Excel表”，然後在TSCJ工作表和Teacher工作表中分別輸入各科成績和教師資訊，而成績對應的各種統計則默默地自動執行，不需要人的參與。
