EXCEL進行學生成績管理

EXCEL進行學生成績管理

首先啟動Excel電子表格軟體,新建一個「空白工作簿」隨後在默認工作表中製作一個表格,其中包括:學號、姓名、成績、分數等級、名次等項並相應填好

1、 轉換學生成績分數等級

有時我們會遇到要將學生的考試成績按實際考試分數轉換成相應的分數等級的情況,例如將考試成績在90分以上的成績轉換成「A+」形式,85-89分的成績轉換成「A」形式……。一般情況,在EXCEL表格中大家會採用IF()函數來設計公式進行轉換,這樣所設計的公式會變得很複雜,如果進行轉換的成績等級類型超過IF()函數的最大嵌套7層時,IF()函數就無能為力了。這時我們可以用如下的方法來簡化操作。

在H3到J13單元格錄入考試成績分數段與考試成績分數等級對照表。

在E4單元格錄入公式「=INDEX(J$4:J$13,MATCH(1,(D4>=H$4:H$13)*(D4

將光標移動到E4單元格,向下拖動填充柄至E20單元格,將公式進行快速復制,這樣就可以迅速完成轉換學生成績分數等級的工作也可按照自己的喜好,將H3到J13單元格區域設置為「隱藏」,使工作表更加美觀。

2、 統計學生考試成績分布情況

在利用EXCEL管理學生考試成績時,經常要統計各分數段學生考試成績的分布情況,如果採用下面這種方法,就能使這項工作變得非常方便。

1、 打開管理學生成績表(本例仍採用上例的工作表,格式內容)。

2、 在J2到J5單元格錄入學生考試成績的統計分段點。在本例中採用的統計分段點為:59、69、79、89,即統計60分以下、60-69、70-79、80-89、90分以上五個學生考試成績區段的人數分布情況,當然你也可以根據自己的實際需要在此進行不同的設置。

3、 選中要進行公式設計的單元格區域I6到I10,按下F2鍵,錄入公式「=FREQUENCY(D4:D20,J2:J5)」,由於該公式為數組公式,在錄入完上述內容後,必須同時按下「Ctrl+Shift+Enter」鍵,為上述公式內容加上數組公式標誌即大括號「{}」。

4、 當上述操作完成後,在單元格區域I6到I10就迅速得到了正確的學生考試成績分布情況。

5、 要注意的是,在我們設計統計區段時,這個統計區段必須比統計分段點的數據個數多一個。這個多出來的統計區段表示超出最高間隔的數值個數。例如,在本例中,我們設計的統計分段點為59、69、79、89四個數值,這時要想取得正確的統計區段分布數據,就必須在單元格區域I6到I10五個單元格中輸入FREQUENCY()函數計算的結果,多出來的這一個單元格將返回學生成績表中大於90分的成績的人數。

3、 學生成績名次排名

1、 工作表按內容格式建好後,首先用滑鼠選中「名次」一列中的第一個學生所在的單元格。

2、 在此單元格中輸入公式「=RANK(D4,$D$4:$D$20,0)」,公式中的D4表示工作表中的第一位學生成績,$D$4:$D$20表示比較第一位學生到最後一位學生的成績。

3、 隨後用滑鼠選中F4單元格向下拖動填充柄至F20單元格,將公式進行快速復制,這樣就可以迅速完成學生成績名次排名的工作。

4、 當前顯示的學生名次,只是Excel對當前所輸入學生的成績進行比較之後的名次,該名次會隨著輸入成績的不同而發生變化,只有我們將全部學生成績輸入後所得到的名次才是最後的名次。

4、 用「條件格式」給學生分等級

通過以上操作我們已可以輕鬆地對全班學生進行成績管理,除了我們可以將學生的考試成績按實際考試分數轉換成相應的分數等級外,還可以利用Excel中的「條件格式」直觀的查看學生成績的等級情況。

例如,我們將全部考試成績等級定為:優、良、合格、不及格四個等級,即:90-100分之間是優;75-89分之間是良;60-74分之間是合格;60分以下是不及格。下面我們分別以不同的顏色來顯示各個等級,例如,優等級顯示為:紅色;良等級顯示為:藍色、字體傾斜;合格等級顯示為:黃色;不及格等級顯示為:默認的白色。這樣看起來就一目了然。

首先用滑鼠選中「成績」一列(D4:D20,具體區域應根據實際情況而定),執行「格式-條件格式」命令,打開「條件格式」對話框。

在「條件1」項中將第一個選項設置為「單元格數值」、在後面的項設置為「介於」選項,並對後面的兩個選項進行賦值,在此我們輸入100和90即可。然後單擊「格式」按鈕,隨後彈出一個「單元格格式」對話框,我們就按剛才的設定對字體的字形、顏色、圖案背景等進行設置。隨後單擊「確定」即可返回上一級界面。

這時我們看到第一個條件已經設置好,「條件1」設置好後單擊下面的「添加」按鈕,接著在下面彈出的「條件2」對話框按上面的方法進行設置……。(如圖5)60分以下為白色,我們不用進行設置。設置好後單擊「確定」,我們看到該工作表中的「成績」一列單元格區域已經變成彩色的了。(如圖6)你也可以再次選中「成績」一列,執行「數據-排序」命令進行降序排列,這樣就更清楚地了解各個等級情況。

5、 避免文本數據重復輸入

我們在管理學生成績表中輸入諸如學號、身份證號等文本數據時,為了防止輸入重復的號碼,我們採取了如下的預防措施。

1、 同時選中如圖1的「學號」一列(本例中是B4:B20),執行「數據-有效性」命令,打開「數據有效性」對話框窗口。

2、 在「設置」標籤按鈕中,單擊「允許」右側的下拉按鈕,在隨後彈出的下拉列表框中,選「自定義」選項,然後在下面的「公式」方框中輸入公式:「=COUNTIF(B:B,B4)=1」。

3、切換到「出錯警告」標籤按鈕下,設置好相關的提示信息,確定退出。

4、以後,如果輸入的學號出現了重復,系統會按設置的信息提示操作者進行檢查(如圖9),並要求重新輸入。

6、 統計全班學生的總分、平均分

我們為成績表列出總分以及平均分是以便於很好的掌握全班學生的學習情況,在Excel中可以輕鬆地計算出每次考試的總分和平均分。

在求總分時,首先用滑鼠選中放置總分結果的單元格G3,執行「插入-函數」命令,再彈出的「黏貼函數」對話框中選擇求和函數「SUM」,確定後在隨後彈出的對話框的輸入欄中輸入單元格區域(本例中為D4:D20),確定後就可看到在G3單元格出現全班學生成績總分。

在求平均分時,我們用滑鼠選中放置平均分後面的H3單元格,隨後在Excel的編輯欄的輸入框中輸入公式「=AVERAGE(D4:D20)」後回車,這樣我們就可以看到H3單元格會出現D4:D20中所有學生成績平均分。

通過以上的操作我們就可以輕鬆地來管理學生的每次考試成績情況了,其實利用Excel的這些功能再加以靈活運用還可以管理學生一學期或一學年的成績情況,如果你有興趣就動手試試吧。