利用Ｅｘｃｅｌ巧妙處理學(xué)生成績

摘要：用Excel可高效處理學(xué)生成績，介紹了表格的設(shè)計、相關(guān)公式的使用和模板的制作。

關(guān)鍵詞：Excel 公式 模板

中圖分類號： G434 文獻標(biāo)識碼： B 文章編號：1002-2422(2008)01-0035-02

1 表格設(shè)計

工作簿中設(shè)計三個工作表，第一個工作表為參數(shù)設(shè)置工作表。不同的課程考勤成績、作業(yè)成績、考試成績占綜合成績的比例不同，缺席一次所扣分?jǐn)?shù)也會不同，考勤記錄的符號可能不同，為適應(yīng)不同的課程，設(shè)計一張工作表來設(shè)置這些參數(shù)，在計算學(xué)生成績時，相關(guān)參數(shù)就從該表中取得。在該表中設(shè)置記錄考勤的符號，如用“X”代表缺席，設(shè)置缺席一次所扣分?jǐn)?shù)為10分等。

第二張表為成績登記表，如圖1所示，在該表中記錄學(xué)生的考勤情況，根據(jù)考勤記錄計算考勤成績，錄入學(xué)生作業(yè)成績與考試成績，在錄入成績時設(shè)置了數(shù)據(jù)的有效性，將數(shù)據(jù)限定在0到100之間，如果不在此范圍內(nèi)則報出錯信息。設(shè)置的具體方法為，選擇“數(shù)據(jù)”菜單中的“有效性…”，彈出“數(shù)據(jù)有效性”對話框，設(shè)置數(shù)據(jù)的有效性范圍為0到100即可。計算學(xué)生的綜合成績或以等級計分的成績，在不改變學(xué)號排序的前提下計算學(xué)生名次。學(xué)生成績低于60分的用紅色字體顯示，可用“格式”菜單中的“條件格式”進行設(shè)置。

第三張表為學(xué)生成績分析表，在該表中設(shè)計了計算學(xué)生成績各分?jǐn)?shù)段的人數(shù)、百分比、計算及格率、平均分等。

2 成績計算

在參數(shù)設(shè)置表中將相關(guān)參數(shù)設(shè)置好后，學(xué)生成績的計算在學(xué)生成績登記表中進行，為了方便計算，需先對參數(shù)設(shè)置表中的相關(guān)單元格進行命名。

(1)單元格命名

對設(shè)置的參數(shù)缺席所扣分?jǐn)?shù)的D5單元格命名，先選中D5單元格，在名稱框中輸入要命名的名字”缺席扣分”，然后回車，該單元格即被命名為“缺席扣分”，在以后的公式中可以利用該名字代替D5單元格的地址。用相同的方法可對相關(guān)參數(shù)的單元格命名，各單元格名字如表1所示。

(2)考勤成績計算

圖1中M4單元格中的公式為“=100-IF(COUNTIF(E4：L4．缺席符號)>缺席次數(shù)，100，(COUNTIF(E4：L4，缺席符號)*缺席扣分+COUNTIF(E4：L4，遲到符號)*遲到扣分))”該公式由if()函數(shù)和counllf()函數(shù)組成，courltif()函數(shù)統(tǒng)計缺席、遲到的次數(shù)，if()函數(shù)的結(jié)果為所扣分?jǐn)?shù)，如果該生缺席次數(shù)大于設(shè)置的缺席次數(shù)，則所扣分?jǐn)?shù)為100，否則為統(tǒng)計出缺席、遲到扣分的總和。考勤成績默認為100分，減去if()函數(shù)的結(jié)果則可得到考勤分?jǐn)?shù)。如果在成績計算中需調(diào)整考勤缺席、遲到所扣分?jǐn)?shù)，只要調(diào)整參數(shù)即可，勿需更改公式。

(3)綜合成績

圖1中P4單元格中的公式為“=M4*考勤比例+N4*作業(yè)比例+04*考試比例”，若要修改各種成績所占綜合成績的比例，只需修改參數(shù)設(shè)置表中的相關(guān)參數(shù)即可。

(4)等級成績

圖1中Q4單元格中等級成績的生成是根據(jù)P4中的成績來確定的，具體公式為“=IF(P4>=90，“優(yōu)”，IF(P4>=80，“良”。IF(P4>=70，“中”，IF(P4>=60，“及格”，“不及格”))))”，該公式中使用了IF()函數(shù)，如果P4單元格中的成績大于等于90，則為“優(yōu)”，否則再判斷其是否大于等于80，依次類推嵌套If()函數(shù)，直到將所有的分?jǐn)?shù)段判斷完為止。

(5)排名次

要保持現(xiàn)有的順序不變，則可以利用公式完成，圖1中R4單元格的公式為“=RANK(P4，$P$4：$P$26)”，rank()函數(shù)中有兩個參數(shù)，第一個參數(shù)指P4中數(shù)字在區(qū)域中的排序，第二個參數(shù)$P$4：$P$26指明這個數(shù)據(jù)區(qū)域。

3 成績分析

計算出學(xué)生綜合成績后，一般需對學(xué)生的成績進行分析，如計算各分?jǐn)?shù)段的人數(shù)、百分比，及格率和平均分等。在第三張表中進行成績分析，因為分析的原始數(shù)據(jù)在第二張表中，所以先對成績登記表的數(shù)據(jù)區(qū)域命名。將圖1中從P4到P6單元格區(qū)域命名為“綜合成績”，在進行成績分析計算時將用到該區(qū)域。計算出學(xué)生總?cè)藬?shù)，B2中的公式為“=count(綜合成績)”，count()函數(shù)將返回名字為“綜合成績”的單元格區(qū)域的個數(shù)，即為總?cè)藬?shù)；接下來計算各分?jǐn)?shù)段的人數(shù)及百分比，先計算90-100分?jǐn)?shù)段的人數(shù)，B4中公式為“=countif(綜合成績，“>=90”)”，用countif()函數(shù)統(tǒng)計大于等于90的學(xué)生人數(shù)，因為成績登錄表中保證了分?jǐn)?shù)是O到100中之間的數(shù)，所以大于等于90的人數(shù)即為90-100之間的數(shù)。百分比相對簡單，用該分?jǐn)?shù)段的人數(shù)除以總?cè)藬?shù)即可，但須注意，總?cè)藬?shù)單元格地址的引用要用絕對引用。

4 制作模板

(1)按上述方法建立好相關(guān)的表格、公式等，去掉成績登錄表中的相關(guān)原始數(shù)據(jù)(公式不能去掉)；

(2)選擇“另存為”按鈕，系統(tǒng)彈出“另存為”對話框，輸入文件名“學(xué)生成績處理”，選擇保存類型為“模板”，這樣就把該表格存為了模板，以后就可基于該表格建立新的表格。

5 結(jié)束語

通過設(shè)置參數(shù)的方式，有效地解決了不同課程在成績處理上的不同要求，將設(shè)計好的工作表保存為模板，大大提高了處理成績的效率。