1. 首頁 > 生活百科 > >

Excel統計學生成績時的四個難題及解決2020攻略資訊

excel電腦知識

一、前言
對于教師而言，經常需要用Excel進行學生成績統計，會被一些常見問題難住 。 碰到的難題主要有：如何統計不同分數段人數、如何在保持學號不變前提下進行排名、如何將百分制轉換成不同分數段與如何用紅色顯示不及格的分數等，本文著重對這些問題的解決方法與技巧加以分析和討論 。
本文假設讀者已對Excel的基本操作已經有一定基礎，已經掌握如何進行求和、求平均和如何使用自動填充柄進行復制公式等操作，本文對這些一般性操作不做詳細介紹，僅對一些難度較大的操作技巧進行討論 。
二、Excel統計學生成績時的四個難題
假設在統計學生成績時，我們需要統計出如圖1所示的相關結果 。

Excel統計學生成績時的四個難題及解決2020攻略資訊

文章插圖

圖1　學生成績統計所需要的結果圖
這里，假設學號、姓名、成績等列及行15都已經事先輸好，需要讓Excel統計其他的相關數據結果 。 這時，成績統計中主要難解決的問題及它們在圖中的位置如下：
問題1：如何統計不同分數段的學生人數?(圖中A16~E16)
問題2：如何在保持學號順序不變的前提下進行學生成績名次排定?(圖中F2~F13)
問題3：如何將百分制轉換成不同的等級分?(圖中“等級1”與“等級2”列)
問題4：如何使不及格的分數以紅色顯示?(圖中紅色顯示部分，即第12行)

【Excel統計學生成績時的四個難題及解決2020攻略資訊】 三、解決統計學生成績時的四個難題的方法
下面，針對上面提出的四個難題分別討論解決的方法與技巧 。
1、統計不同分數段的學生人數
統計不同分數段的學生人數是非常常見的需求，其所需結果如圖1中A16~E16所示 。 這里，假設需要統計90~100、80~89、70~79、60~69及低于60分五個不同分數段的人數 。
通常，統計不同分數段最好的方法是利用COUNTIF(X，Y)函數 。 其中有兩個參數，第一個參數X為統計的范圍，一般最好用絕對引用;第二個參數Y為統計條件，要加引號 。
對于小于60分的人數只要用一個COUNTIF( )函數，如在E16單元格中輸入公式：=COUNTIF($C$2:$C$13,"<60") 。
對于其他在兩個分數之間的分數段的人數統計，需要用兩個COUNTIF( )函數相減 。 如在A16單元格中輸入公式：=COUNTIF($C$2:$C$13,"<=100")-COUNTIF($C$2:$C$13,"<90")，即用小于等于100的人數減去小于90的人數 。
如果要統計80~89、70~79與60~69分數段的人數，只要利用自動填充柄將該公式復制到右邊三個單元格，再把"<=100"與"<90"作相應的修改，就可以得到正確的結果 。
2.保持學號順序不變的前提下進行成績排名
學生成績排定在學生成績統計中經常用到 。 特別要強調的是，這里所談的方法不是一般的排序，因為那樣會使學生的學號順序發生變化 。 這里所需要的是在保持學號順序不變的情況下進行學生成績名次排定的功能，其所需結果如圖1中F2~F13所示 。
要進行保持學號順序不變的情況下進行學生成績名次的排定，最好使用RANK(X，Y，Z)函數 。 其中有三個參數，第一個參數X為某個學生的成績所在單元格;第二個參數Y為整個班級成績所在的區域;第三個參數Z是可選的，表示統計方式，若省寫或寫0，則成績高的名次靠前，一般都使用這種方式，如果寫1，則成績高的名次靠后，這種情況一般較少用 。
為了在保持學號順序不變的前提下進行學生成績名次排定，可以在F2單元格中輸入公式：=RANK(C2,$C$2:$C$13,0)，然后，利用自動填充柄將其復制到下方的幾個單元格 。 注意，這里$C$2:$C$13用的是絕對地址，是為了保證公式在復制時此處不變，因為作為第二個參數，這里都是指整個班級成績所在的區域，這個區域是相同的 。
3、將百分制轉換成不同的等級分
將百分制轉換成不同的等級分有多種不同的劃分方法，其所需結果如圖1中“等級1”與“等級2”列所示 。 這里，“等級1”列是將百分制的分數轉換成A(90~100)、B(80~89)、C(70~79)、D(60~69)與E(低于60)五個等級;“等級2”列是將百分制的分數轉換成優(90~100)、良(75~89)、中(60~74)與不及格(低于60)四個等級 。 具體使用哪種等級劃分方法可根據實際情況自己確定 。
在百分制轉換成不同的等級分時，一般使用IF(X，Y，Z)函數 。 其中有三個參數，第一個參數X為條件，不能加引號;第二個參數為條件成立時的結果，如果是顯示某個值，則要加引號;第三個參數為條件不成立時的結果，如果是顯示某個值，同樣要加引號 。 該函數可以嵌套，即在第二個或第三個參數處可以再寫一個IF函數 。

推薦閱讀