利用Excel實(shí)現(xiàn)成績的自動(dòng)統(tǒng)計(jì)

韓松喜

秦皇島市山海關(guān)區(qū)第三中學(xué) 河北秦皇島 066200

筆者擔(dān)任本校學(xué)生成績統(tǒng)計(jì)的工作已經(jīng)有好多年了，在成績的統(tǒng)計(jì)過程中，計(jì)算器使用過，成績統(tǒng)計(jì)軟件也使用過，但是好多的功能都和本學(xué)校要求的不太一致，在利用軟件進(jìn)行統(tǒng)計(jì)后，還需要再進(jìn)行大量繁瑣的工作。因此，筆者經(jīng)過一段時(shí)間的摸索，利用Excel這個(gè)軟件，制作出符合學(xué)校要求的成績統(tǒng)計(jì)模版，每次只要輸入學(xué)生的考試成績就可以了，其他的內(nèi)容全部是自動(dòng)生成的，剩下的工作就是直接去打印室打印出來就完成了。雖然各個(gè)學(xué)校的要求不太一致，但是大體上都相同，希望筆者的方法可以對(duì)大家有所幫助和啟發(fā)。

此Excel文件包含了所有初中學(xué)科、8個(gè)班，每班50人的成績統(tǒng)計(jì)功能。下面筆者就將制作過程介紹給大家，希望能給大家一些啟發(fā)，更好地完成學(xué)校交給的工作。

1 設(shè)計(jì)輸入成績的名單模塊

首先打開2003版Excel，將Sheet1重新命名為打印名單，然后根據(jù)自己的學(xué)校的需要將學(xué)生的名單復(fù)制進(jìn)來，并對(duì)表頭進(jìn)行輸入。這個(gè)表是將來輸入成績的時(shí)候由任課教師打印的成績填寫單（根據(jù)學(xué)校情況自定人數(shù)），任課教師將成績輸入完成后，將直接按照這個(gè)表單輸入成績。如果有學(xué)生的名單變動(dòng)，可直接更改這個(gè)表中的學(xué)生名字，其他的不需修改。

2 設(shè)計(jì)成績統(tǒng)計(jì)的設(shè)置模塊

將Sheet2改名為設(shè)置，然后根據(jù)需要將各個(gè)學(xué)科的成績的優(yōu)秀率、及格率、差生率的成績輸入，將每次考試的名稱輸入，將每個(gè)班的任課教師名單輸入，另外設(shè)置上哪個(gè)學(xué)年度的，這樣以后只需要修改設(shè)置里面的內(nèi)容，其他的就會(huì)自動(dòng)更改。設(shè)計(jì)這個(gè)模塊的最主要的作用就是為了某些學(xué)科為120分，這樣的優(yōu)秀率、及格率和差生率就和100分的不同了。為了以后不必頻繁地更改設(shè)置，故此設(shè)計(jì)了此模塊。

3 設(shè)計(jì)成績統(tǒng)計(jì)的錄入模塊

將Sheet3改名為成績輸入，下面要對(duì)學(xué)生的成績的輸入進(jìn)行設(shè)計(jì)。在這里，所有的學(xué)生成績輸入后，不需要更改任何內(nèi)容，其他的統(tǒng)計(jì)表就完全自動(dòng)生成了。另外，在介紹一個(gè)輸入成績的小竅門，就是在Excel2003以后新增加的一個(gè)功能“文本到語音”，利用這個(gè)功能，可以在輸入成績后按回車鍵將輸入的成績朗讀出來，這樣就可以方便地進(jìn)行輸入成績的校對(duì)。

具體做法：1）在輸入成績表中按照每科成績一列進(jìn)行輸入成績表的設(shè)置，每個(gè)班預(yù)留50人，8個(gè)班級(jí)；2）表頭為=設(shè)置!F1&“考試輸入成績表”；3）將打印名單表中的姓名按照每個(gè)班的順序進(jìn)行復(fù)制，然后到輸入成績中將學(xué)生名單選擇性粘貼過來（粘貼鏈接），這樣當(dāng)修改打印名單表中的學(xué)生姓名的時(shí)候，輸入成績表中的學(xué)生姓名也相應(yīng)修改；4）根據(jù)粘貼過來的學(xué)生名單，利用自動(dòng)填充功能，將班級(jí)填充上；5）以上工作全部完成后，就可以輸入本次學(xué)生的成績了，缺考的輸入0或?yàn)榭铡?/p>

4 設(shè)計(jì)年級(jí)成績的統(tǒng)計(jì)模塊

在完成各班的成績輸入后，就要進(jìn)入到全年級(jí)的成績統(tǒng)計(jì)中。先插入一個(gè)新的工作表，改名為年級(jí)成績，將成績錄入表中的數(shù)據(jù)進(jìn)行選擇性粘貼（粘貼鏈接）。

1）姓名、班級(jí)、語文、數(shù)學(xué)、英語、物理、化學(xué)、政治、歷史、生物、地理字段為從成績錄入表中粘貼過來的。其他各學(xué)科的名次為粘貼完成績后插入新的列。除各學(xué)科的分?jǐn)?shù)、姓名、班級(jí)以外均為函數(shù)字段，需要手工設(shè)置。

2）各科名次、班級(jí)名次、年級(jí)名次應(yīng)用到的函數(shù)為RANK。比如語文名次在編輯欄輸入=RANK(C3,$C$3:$C$399,0)，C3是當(dāng)前單元格，$C$3:$C$399是指從C3開始到C399結(jié)束，然后自動(dòng)填充到C399。這樣語文的全年級(jí)的排名就出來了，其他排名類似，換成相應(yīng)的成績就可以了。

3）總分=C3+E3+G3+I3+K3+M3+O3+Q3+S3，這樣的話不容易混亂，然后向下自動(dòng)填充到U399；班級(jí)的名次只需要自動(dòng)填充到本班的最后一個(gè)人就可以了，其他依次類推。

4）表頭為=設(shè)置!F1&“班級(jí)成績總表”。

5）在單元格中輸入到函數(shù)后一般都需要按Enter，但是在某些時(shí)候還需要按Ctrl+Enter，這樣才能使函數(shù)生效。

5 設(shè)計(jì)各班成績統(tǒng)計(jì)模塊

完成年級(jí)的成績統(tǒng)計(jì)設(shè)計(jì)以后，只需要將相應(yīng)的班級(jí)的內(nèi)容選擇性地粘貼到一個(gè)新的表中就可以了，可以依次取名為一班、二班、三班、四班等，為將來的成績打印提供方便。

另外，還有各個(gè)班級(jí)的各科的總分和平均分的統(tǒng)計(jì)，每個(gè)班的各個(gè)學(xué)科的總分用到了SUM，人數(shù)用到了COUNTIF。例如：=COUNTIF(C3:C49,">0")，統(tǒng)計(jì)C3到C49分?jǐn)?shù)大于0的人數(shù)，平均分用總分除以人數(shù)。因?yàn)?分為缺考，這里就不做統(tǒng)計(jì)了。

打印的時(shí)候只需要將沒有考試的學(xué)科和相應(yīng)的學(xué)科名次直接進(jìn)行隱藏，另外利用自動(dòng)篩選功能將班級(jí)設(shè)置為相應(yīng)的班級(jí)就可以打印各個(gè)班的成績表了。

6 設(shè)計(jì)年級(jí)統(tǒng)計(jì)模塊

先插入一個(gè)新的工作表，改名為小表，整個(gè)年級(jí)的統(tǒng)計(jì)信息就在這個(gè)表中，其中學(xué)生的統(tǒng)計(jì)信息是以各個(gè)班為單位的。

1）表頭的優(yōu)秀率可以寫成=“優(yōu)秀率(“&設(shè)置!B2&”分以上）”，這樣只需要修改設(shè)置表中的分?jǐn)?shù)，這里就直接修改了過來，及格率、差生率同理。

2）班級(jí)人數(shù)為直接調(diào)用各個(gè)班級(jí)中的最下面統(tǒng)計(jì)出來的數(shù)據(jù)。

3）各個(gè)分?jǐn)?shù)段的人數(shù)利用到函數(shù)SUM，例如一班語文成績的110到120的人數(shù)為：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50<120)*(一班!$C$3:一班!$C$50>=110))，一班語文成績的100到109的人數(shù)為：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50<=109)*(一班!$C$3:一班!$C$50>=100))，其中A5為班級(jí)，一班為一班的成績表，$B為班級(jí)，$C為語文成績，其他的分?jǐn)?shù)段和其他的班級(jí)也同理可以得到。

4）優(yōu)秀率、及格率、差生率的人數(shù)求法和各個(gè)分?jǐn)?shù)段的求法基本相同，只不過是將上面的分?jǐn)?shù)段修改成設(shè)置表中的優(yōu)秀率、及格率、差生率相對(duì)應(yīng)的分?jǐn)?shù)。例如一班語文的優(yōu)秀率為：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50>(設(shè)置!$B$2)))；及格率為：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50>(設(shè)置!$C$2)))；差生率為：=SUM((一班!$B$3:一班!$B$50=A5)*(一班!$C$3:一班!$C$50<(設(shè)置!$D$2))*(一班!$C$3:一班!$C$50>0))。

5）各個(gè)率的百分比為人數(shù)除以班級(jí)參考人數(shù)，然后將該單元格設(shè)置為百分比格式。

6）總分和平均分可以調(diào)用各個(gè)班表中相對(duì)應(yīng)的數(shù)據(jù)，進(jìn)行選擇性粘貼。

7）每個(gè)班的單科最高分相對(duì)應(yīng)的學(xué)生姓名的求法利用到INDEX，例如一班的語文最高分的例子為：=INDEX(一班!$A$3:$A$50,MATCH(MAX(一 班!$C$3:$C$50),一班!$C$3:$C$50,0))。其他班的類似。

8）每個(gè)班的單科最高分和最低分應(yīng)用到MAX和MIN函數(shù)，例如一班語文最高分為：=MAX(一班!$C$3:一班!$C$50)，最低分為：=MIN(IF(一班!$C$3:一班!$C$50>0,一班!$C$3:一班!$C$50))。這里只求分?jǐn)?shù)大于0的的最低分。

9）將上面內(nèi)容設(shè)置好表格樣式，調(diào)整好大小和距離后直接復(fù)制，修改相對(duì)應(yīng)的學(xué)科，直到所有學(xué)科都修改完畢。

7 設(shè)置全年級(jí)各學(xué)科第一名模塊

將小表中的各個(gè)學(xué)科的最高分的名字和分?jǐn)?shù)以鏈接的方式復(fù)制到這個(gè)表中，根據(jù)需要設(shè)置好表格樣式和大小就可以了。

8 設(shè)置全年級(jí)總成績前50名模塊

將年級(jí)名次表進(jìn)行復(fù)制，創(chuàng)建一個(gè)新的表，表名為前50，然后利用Excel中的自動(dòng)篩選功能將年級(jí)名次為小于或等于50的數(shù)據(jù)篩選出來，進(jìn)行排序。

9 凍結(jié)窗口，保護(hù)文件

模版整體制作完成后，利用Excel的凍結(jié)窗口（窗口菜單下的凍結(jié)命令）將不需要改動(dòng)的內(nèi)容凍結(jié)起來，然后再將不需要改動(dòng)的文件保護(hù)起來，省得出現(xiàn)錯(cuò)誤。

10 設(shè)置打印

完成了以上的工作，就可以對(duì)Excel進(jìn)行表格設(shè)置、頁面設(shè)置、字體設(shè)置（宋體）、字號(hào)設(shè)置（10）、行高設(shè)置，完成后就可以直接到打印室進(jìn)行成績的打印了，到此圓滿完成學(xué)校交給的工作。

自從用Excel做了這個(gè)成績統(tǒng)計(jì)模板后，每次只需要在考完試后將學(xué)生的名單打印出來交給各個(gè)學(xué)科的教師，教師錄完成績后，筆者根據(jù)名單和成績將學(xué)生的成績進(jìn)行輸入，這樣基本上只需要輸入完成績就可以直接去打印室打印了。由于本統(tǒng)計(jì)介紹是初中的，如果小學(xué)想使用的話，可直接將班級(jí)改成年級(jí)，其他方法基本相同。

以上只是筆者的一點(diǎn)兒經(jīng)驗(yàn)，希望和其他的老師分享一下，也希望大家對(duì)此提出建議，以便更好地設(shè)計(jì)統(tǒng)計(jì)模板，更加方便快捷地完成成績統(tǒng)計(jì)工作，更好地的完成學(xué)校交給的工作。