中統(tǒng)計成績常用的函數(shù)

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

2、統(tǒng)計所需要的結果圖這里，假設學號、姓名、成績等列及行15都已經(jīng)事先輸好，需要讓Excel統(tǒng)計其他的相關數(shù)據(jù)結果。這時，成績統(tǒng)計中主要難解決的問題及它們在圖中的位置如下：問題1：如何統(tǒng)計不同分數(shù)段的學生人數(shù)?(圖中A16E16)問題2：如何在保持學號順序不變的前提下進行學生成績名次排定?(圖中F2F13)問題3：如何將百分制轉(zhuǎn)換成不同的等級分?(圖中“等級1”與“等級2”列)問題4：如何使不及格的分數(shù)以紅色顯示?(圖中紅色顯示部分，即第12行)三、解決統(tǒng)計學生成績時的四個難題的方法下面，針對上面提出的四個難題分別討論解決的方法與技巧。1、統(tǒng)計不同分數(shù)段的學生人數(shù)統(tǒng)計不同分數(shù)段的學生人數(shù)是非常常見

3、的需求，其所需結果如圖1中A16E16所示。這里，假設需要統(tǒng)計90100、8089、7079、6069及低于60分五個不同分數(shù)段的人數(shù)。通常，統(tǒng)計不同分數(shù)段最好的方法是利用COUNTIF(X，Y)函數(shù)。其中有兩個參數(shù)，第一個參數(shù)X為統(tǒng)計的范圍，一般最好用絕對引用;第二個參數(shù)Y為統(tǒng)計條件，要加引號。對于小于60分的人數(shù)只要用一個COUNTIF( )函數(shù)，如在E16單元格中輸入公式：=COUNTIF($C$2:$C$13,"<60")。對于其他在兩個分數(shù)之間的分數(shù)段的人數(shù)統(tǒng)計，需要用兩個COUNTIF( )函數(shù)相減。如在A16單元格中輸入公式：=COUNTIF($C$2:

4、$C$13,"<=100")-COUNTIF($C$2:$C$13, "<90")，即用小于等于100的人數(shù)減去小于90的人數(shù)。如果要統(tǒng)計8089、7079與6069分數(shù)段的人數(shù)，只要利用自動填充柄將該公式復制到右邊三個單元格，再把"<=100"與"<90"作相應的修改，就可以得到正確的結果。2.保持學號順序不變的前提下進行成績排名學生成績排定在學生成績統(tǒng)計中經(jīng)常用到。特別要強調(diào)的是，這里所談的方法不是一般的排序，因為那樣會使學生的學號順序發(fā)生變化。這里所需要的是在保持學號順序不變的情況下進

5、行學生成績名次排定的功能，其所需結果如圖1中F2F13所示。要進行保持學號順序不變的情況下進行學生成績名次的排定，最好使用RANK(X，Y，Z)函數(shù)。其中有三個參數(shù)，第一個參數(shù)X為某個學生的成績所在單元格;第二個參數(shù)Y為整個班級成績所在的區(qū)域;第三個參數(shù)Z是可選的，表示統(tǒng)計方式，若省寫或?qū)?，則成績高的名次靠前，一般都使用這種方式，如果寫1，則成績高的名次靠后，這種情況一般較少用。為了在保持學號順序不變的前提下進行學生成績名次排定，可以在 F2單元格中輸入公式：=RANK(C2,$C$2:$C$13,0)，然后，利用自動填充柄將其復制到下方的幾個單元格。注意，這里$C$2:$C $13用的是絕

6、對地址，是為了保證公式在復制時此處不變，因為作為第二個參數(shù)，這里都是指整個班級成績所在的區(qū)域，這個區(qū)域是相同的。3、將百分制轉(zhuǎn)換成不同的等級分將百分制轉(zhuǎn)換成不同的等級分有多種不同的劃分方法，其所需結果如圖1中“等級1”與“等級2”列所示。這里，“等級1”列是將百分制的分數(shù)轉(zhuǎn)換成A (90100)、B(8089)、C(7079)、D(6069)與E(低于60)五個等級;“等級2”列是將百分制的分數(shù)轉(zhuǎn)換成優(yōu) (90100)、良(7589)、中(6074)與不及格(低于60)四個等級。具體使用哪種等級劃分方法可根據(jù)實際情況自己確定。在百分制轉(zhuǎn)換成不同的等級分時，一般使用IF(X，Y，Z)函數(shù)。其中有

7、三個參數(shù)，第一個參數(shù)X為條件，不能加引號;第二個參數(shù)為條件成立時的結果，如果是顯示某個值，則要加引號;第三個參數(shù)為條件不成立時的結果，如果是顯示某個值，同樣要加引號。該函數(shù)可以嵌套，即在第二個或第三個參數(shù)處可以再寫一個 IF函數(shù)。為了得到“等級1”列所要的等級結果，可以在D2單元格中輸入公式：=IF(C2>= 90,"A",IF(C2>=80,"B",IF(C2>=70,"C",IF(C2>=60,"D","E")，然后，利用自動填充柄將其復制到下方的幾個單元格。為了得到

8、“等級2”列所要的等級結果，可以在E2單元格中輸入公式：=IF(C2>=90,"優(yōu)",IF(C2>=75,"良",IF(C2>=60,"中","不及格")，然后，利用自動填充柄將其復制到下方的幾個單元格。4、使不及格的分數(shù)以紅色顯示統(tǒng)計學生成績時經(jīng)常需要將不及格的分數(shù)用紅色顯示，其結果如圖1中紅色顯示部分(如第12行)。使不及格的分數(shù)以紅色顯示需要使用“格式”菜單中的“條件格式”命令。該命令會彈出一個對話框，其中要求確認條件與相應的格式。對于“成績”列，可先選中C2：C13，然后使用“格式”菜單中的“條件格式”命令，在彈出的對話框中，左邊使用默認的“單元格數(shù)值”，中間選“小于”，右邊填寫60，然后單擊右邊的“格式”按鈕，從中選擇紅色，最后單擊兩次“確定”按鈕。對于“等級1”列，可先選中D2：D13，然后使用“格式”菜單中的“條件格式”命令，在彈出的對話框