設(shè)計(jì)成績(jī)自動(dòng)統(tǒng)計(jì)Excel模板.doc

《設(shè)計(jì)成績(jī)自動(dòng)統(tǒng)計(jì)Excel模板.doc》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫。

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

2、50至60人，因此設(shè)模板每個(gè)教學(xué)班上限人數(shù)為75人，每級(jí)學(xué)生總?cè)藬?shù)上限為1500人。根據(jù)學(xué)校教學(xué)成績(jī)?cè)u(píng)估的需求，成績(jī)統(tǒng)計(jì)表格如下：表1-1二、成績(jī)自動(dòng)統(tǒng)計(jì)模板的總體設(shè)計(jì)新建空白Excel工作簿，將自動(dòng)建立的Sheet1和Sheet2工作表重命名為Tstat和Tscore，并將Sheet3工作表刪除。如圖，其中Tscore工作表用于記錄成績(jī)數(shù)據(jù)，Tstat工作表用于記錄成績(jī)統(tǒng)計(jì)數(shù)據(jù)。1、定制Tscore工作表在Tscore工作表第一行中從A1單元格開始順序輸入記錄字段名：學(xué)號(hào)、姓名、班級(jí)、語文、數(shù)學(xué)、外語、物

3、理、化學(xué)、生物、政治、歷史和地理。2、定制Tstat工作表對(duì)Tstat工作表的定制多而復(fù)雜，下面就以語文科統(tǒng)計(jì)表定制為例進(jìn)行陳述，其它科的統(tǒng)計(jì)類比操作。（一）定制語文科統(tǒng)計(jì)表基本框架將單元格A1:T1合并。并在合并單元格中輸入“=IF(MONTH(TODAY())>6,"第"&(YEAR(TODAY())-1)&"-"&YEAR(TODAY())&"學(xué)年度第二學(xué)期期末","第"&(YEAR(TODAY())-2)&"-"&(YEAR(TODAY())-1)&"學(xué)年度第一學(xué)期期末")&TScore!D1&"科

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

5、用于計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目，通過COUNTIF函數(shù)求出1班的參考人數(shù)。然后選中B4單元格，拖動(dòng)B4單元格右下角的小“十”字圖標(biāo)至B23單元格（這種操作本文統(tǒng)稱為向下自動(dòng)填充操作），這樣，就能自動(dò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)”，其

6、中SUMIF函數(shù)功能是根據(jù)指定條件對(duì)若干單元格求和。為了避免分母為0的錯(cuò)誤產(chǎn)生，單元格中用到了IF函數(shù)。該單元格公式可理解為：若1班參考人數(shù)不為0，則把1班語文科總分除以參考人員得到平均分。然后對(duì)C4至C23單元格進(jìn)行向下自動(dòng)填充操作，就可以自動(dòng)得到C5至C23單元格的公式。最后在C25單元格中輸入“=IF(B24=0,0,SUM(TScore!$D$2:TScore!$D$1501)/B24)”，求出全級(jí)平均分。(四)求語文科各班合格人數(shù)在求各班合格人數(shù)時(shí)，利用COUNTIF函數(shù)功能比較難滿足設(shè)計(jì)要求，為

7、了解決這種多條件的計(jì)算功能，本人使用了數(shù)組公式進(jìn)行多重標(biāo)準(zhǔn)單元計(jì)算。首先在D4單元格中輸入“=SUM((TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$1501>=60))”，按下SHIFT＋CTRL鍵敲回車(這是生成數(shù)組公式的關(guān)鍵環(huán)節(jié))，Excel會(huì)自動(dòng)在公式兩側(cè)加上大括號(hào),生成數(shù)組公式。然后對(duì)D4至D23單元格進(jìn)行向下自動(dòng)填充操作。最后在D25中輸入“=SUM(D4:D23)”，求出全級(jí)及格人數(shù)。(五)求語文科各班合格率首先在E4單元格中輸入“

8、=IF(B4=0,0,D4/B4)*100”，為避免分母為O的錯(cuò)誤，用了IF函數(shù)。公式可以理解為：若1班參考人數(shù)不為0，則合格率為合格人數(shù)除以參考人數(shù)后乘以100。然后對(duì)E4至E24單元格進(jìn)行向下自動(dòng)填充操作。(六)求語文科各班差生人數(shù)我校對(duì)分?jǐn)?shù)低于30分的學(xué)生稱為差生。首先在F4單元格中輸入“=SUM((TScore!$C$2:TScore!$C$1501=A4)*(TScore!$D$2:TScore!$D$