設計自動統(tǒng)計excel模板

1、設計成績自動統(tǒng)計Excel模板在教學管理工作中，學期期末成績統(tǒng)計是學校重要且枯燥煩瑣的工作，設計成績自動統(tǒng)計Excel模板成為實際需要。本人經過摸索，利用Excel函數功能，能夠實現成績自動統(tǒng)計要求。接下來本人就將自動統(tǒng)計功能的實現過程進行簡單的陳述，以期望拋磚引玉，與眾多朋友一起學習和探討。一、學校教學成績統(tǒng)計的需求分析在進行成績自動統(tǒng)計模板設計前，正確進行學校教學成績統(tǒng)計的需求分析是必要的，就以我校情況簡述如下：每年級現有教學班10個，考慮到學校的發(fā)展，因此設模板每年級上限班級為20個?，F每個教學班具有50至60人，因此設模板每個教學班上限人數為75人，每級學生總人數上限為1500人。根據

2、學校教學成績評估的需求，成績統(tǒng)計表格如下：表1-1二、成績自動統(tǒng)計模板的總體設計新建空白Excel工作簿，將自動建立的Sheet1和Sheet2工作表重命名為Tstat和Tscore，并將Sheet3工作表刪除。如圖 ，其中Tscore工作表用于記錄成績數據，Tstat工作表用于記錄成績統(tǒng)計數據。1、定制Tscore工作表在Tscore工作表第一行中從A1單元格開始順序輸入記錄字段名：學號、姓名、班級、語文、數學、外語、物理、化學、生物、政治、歷史和地理。2、定制Tstat工作表對Tstat工作表的定制多而復雜，下面就以語文科統(tǒng)計表定制為例進行陳述，其它科的統(tǒng)計類比操作。（一）定制語文科統(tǒng)計表

3、基本框架將單元格A1:T1合并。并在合并單元格中輸入“=IF(MONTH(TODAY()>6,"第" & (YEAR(TODAY()-1) & "-" & YEAR(TODAY() &"學年度第二學期期末","第" & (YEAR(TODAY()-2) & "-" & (YEAR(TODAY()-1)&"學年度第一學期期末") &TScore!D1 &"科統(tǒng)計表一覽" ”

4、，其中MONTH（TODAY（）得到統(tǒng)計時的月份數，YEAR(TODAY()得到統(tǒng)計時的年份數，TScore!D1得到統(tǒng)計科目，即“語文”。通過函數IF對數值和公式進行條件檢測，使得單元格能夠自動獲得學年度信息和學期信息。參照前面表1-1，定制單元格區(qū)域A2：S3，并從A4單元格至A23單元格中順序輸入1至20，A24輸入“級”。 (二)求語文科各班參考人數首先在B4單元格中輸入“=COUNTIF(TScore!$C$2:TScore!$C$1501,"=" & A4)”，其中COUNTIF函數用于計算給定區(qū)域內滿足特定條件的單元格的數目，通過COUNTIF函數求出

5、1班的參考人數。然后選中B4單元格，拖動B4單元格右下角的小“十”字圖標至B23單元格（這種操作本文統(tǒng)稱為向下自動填充操作），這樣，就能自動得到B5至B23單元格的公式。最后在B24單元格中輸入“=SUM(B4:B23)”(三) 求語文科各班平均分首先在C4單元格中輸入“=IF(B4=0,0,SUMIF(TScore!$C$2:TScore!$C$1501,"=" & A4,TScore!$D$2:TScore!$D$1501)/B4) ”，其中SUMIF函數功能是根據指定條件對若干單元格求和。為了避免分母為0的錯誤產生，單元格中用到了IF函數。該單元格公式可理解為

6、：若1班參考人數不為0，則把1班語文科總分除以參考人員得到平均分。然后對C4至C23單元格進行向下自動填充操作，就可以自動得到C5至C23單元格的公式。最后在C25單元格中輸入“=IF(B24=0,0,SUM(TScore!$D$2:TScore!$D$1501)/B24)”，求出全級平均分。(四)求語文科各班合格人數在求各班合格人數時，利用COUNTIF函數功能比較難滿足設計要求，為了解決這種多條件的計算功能，本人使用了數組公式進行多重標準單元計算。首先在D4單元格中輸入“=SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1

7、501>=60)”，按下SHIFTCTRL鍵敲回車(這是生成數組公式的關鍵環(huán)節(jié))，Excel會自動在公式兩側加上大括號,生成數組公式。然后對D4至D23單元格進行向下自動填充操作。最后在D25中輸入“=SUM(D4:D23)”，求出全級及格人數。(五)求語文科各班合格率首先在E4單元格中輸入“=IF(B4=0,0,D4/B4)*100”，為避免分母為O的錯誤，用了IF函數。公式可以理解為：若1班參考人數不為0，則合格率為合格人數除以參考人數后乘以100。然后對E4至E24單元格進行向下自動填充操作。(六) 求語文科各班差生人數我校對分數低于30分的學生稱為差生。首先在F4單元格中輸入“=

8、SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1501<30)”，按下SHIFTCTRL鍵敲回車(這是生成數組公式的關鍵環(huán)節(jié))，Excel會自動在公式兩側加上大括號,生成數組公式。然后對F4至F23單元格進行向下自動填充操作。最后在F24單元格中輸入“=SUM(F4:F23)”，以求得全級差生人數。(七) 求語文科各班差生率首先在G4單元格中輸入“=IF(B4=0,0,F4/B4)*100”，為避免分母為O的錯誤，用了IF函數。該單元格公式可以理解為：若1班參考人數不為0，則差生率為差生人數除以參考人數后乘以100。然

9、后對G4至G24單元格進行向下自動填充操作。(八）求語文科各班優(yōu)生人數我校對分數高于或等于80分的學生稱為優(yōu)生。首先在H4單元格中輸入“=SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1501>=80)”，按下SHIFTCTRL鍵敲回車(這是生成數組公式的關鍵環(huán)節(jié))，Excel會自動在公式兩側加上大括號,生成數組公式。然后對H4至H23單元格進行向下自動填充操作。最后在H24單元格中輸入“=SUM(H4:H23)”，以求得全級優(yōu)生人數。(九) 求語文科各班優(yōu)生率首先在I4單元格中輸入“=IF(B4=0,0,H4/B4)

10、*100”，為避免分母為O的錯誤，用了IF函數。公式可以理解為：若1班參考人數不為0，則優(yōu)生率為優(yōu)生人數除以參考人數后乘以100。然后對I4至I24單元格進行向下自動填充操作。(十) 求語文科各班0-10分數段人數0-10分數段即可理解為小于10分的分數段。首先在J4單元格中輸入“=SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1501<10)”，按下SHIFTCTRL鍵敲回車(這是生成數組公式的關鍵環(huán)節(jié))，Excel會自動在公式兩側加上大括號,生成數組公式。然后對J4至J23單元格進行向下自動填充操作。，然后在J24

11、單元格中輸入“=SUM(J4:J23)”，以求得全級0-10分數段人數。(十一）求語文科各班10-20分數段人數求語文科各班10-20分數段人數，將用到三個條件進行標準單元計算。首先在K4單元格中輸入“=SUM(TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1501<20)*(TScore!$D$2:TScore!$D$1501>=10)”，按下SHIFTCTRL鍵敲回車，生成數組公式。然后對K4至K23單元格進行向下自動填充操作。最后在K25單元格中輸入“=SUM(K4:K23)”，求出全級10-20分數段人數。同樣的

12、道理，可以分別統(tǒng)計各班語文科共它各分數段人數，在此不再多述。(十二）求語文科各班最高分在T4單元格中輸入“=MAX(IF(TScore!$C$2:TScore!$C$1501=A4,TScore!$D$2:TScore!$D$1501)” 按下SHIFTCTRL鍵敲回車，生成數組公式。然后對T4至T23單元格進行向下自動填充操作。最后在T25單元格中輸入“=MAX(T4:T23)”，求出全級最高分。三、成績自動統(tǒng)計Excel模板的密碼加強為了維護數據的安全性，防止非法用戶善自有意或無意更改成績數據，加強成績自動統(tǒng)計Excel模板的密碼功能成為實際需要。雖然Excel應用軟件提供了加密功能，但針

13、對Excel的加密而開發(fā)的解密軟件已經出現，因此，本人利用VBA接口，加強了學校成績統(tǒng)計Excel模板打開要求。同時按住Alt鍵和F11鍵，激活VBE（即VB編輯器），在“工程”窗口中雙擊“ThisWorkbook”后，在代碼窗口中輸入下列語句：Private Sub Workbook_Open()Dim PasswordPassword = InputBox("請輸入合法密碼：", "提示")If Password <> "111" Then MsgBox "密碼錯誤，你是非法用戶", vbOKOnly, "提示" Me.Close (1)End IfEnd Sub上述代碼可理解為：將輸入密碼內容賦值給Password，若Password內容不為驗證密碼“111”，則提示用戶為非法用戶