成績分組排名輕松做

摘 要:在學校處理學生的考試成績的時候，經常要反復調用函數，為了更快地處理成績。本文闡述了RANK復合函數法、RANK+大部分數組法和SUMPRODUCT函數法三種更有效的方法和大家一起探討。

關鍵詞:成績排名RANK復合函數法RANK+大部分數組法SUMPRODUCT函數法

中圖分類號:G64文獻標識碼:Ａ文章編號:1674-098X(2011)05(c)-0205-01

關于利用EXCEL進行學生成績排名的問題，報刊、雜志和網上都做了非常深入而全面的探討，很多作者給出的方法具有相當高的實用價值。有點遺憾的是，在眾多解決方案中，卻忽視了一個實際問題，即:如何在年級成績表中快速排出每位考生的班名次。

一般來講，在正規的考試中，同年級多個班的學生成績要統一錄入到一個EXCEL工作表中進行綜合處理，其中有一項內容就是給考生按成績排名次，包括每位考生的年級排名和班內排名。大家都知道，完成這項工作并不困難，RANK函數足以勝任。給考生進行班內排名，也只須先按班級給表格排序，再對每個班分別使用RANK函數即可。問題是，對于教學班很少的年級來說，這種方法的確很實用，然而一些規模較大的學校，一個年級動輒就達到十幾個甚至二、三十個教學班，顯然一味反復使用RANK函數就成了既費時又枯燥的工作了。我們能不能采用一些技巧來大幅提高操作效率呢？筆者經過多年實踐，總結出了三種有效的方法供您選擇。

案例:下圖是某次考試的全年級成績表，需要在E列填入每位考生的班名次。為便于說明問題，筆者對成績表進行了簡化，刪除了絕大部分數據(圖1)。

方法一:RANK復合函數法

所謂RANK復合函數法，是指除了RANK函數外，還用到了三個函數:OFFSET，MATCH和COUNTIF。MATCH函數用來找到某班第一個學生所在的單元格位置，COUNTIF函數統計該班學生人數，即占用單元格的數量。通過這兩個函數，就可以確定該班所覆蓋的單元格區域。OFFSET函數返回對這一區域的引用，然后RANK函數對這一區域內的數據進行排序。關于上面提到的幾個函數的詳細使用方法，可以到EXCEL幫助中去查找。

操作過程:(1)把整個成績表按班級排序;(2)在E2單元格內輸入公式“=RANK(D2，OFFSET(D$1，MATCH(A2，A:A，0)-1，0，COUNTIF(A:A，A2)，1))”;(3)拖動填充柄，向下填充單元格，所需要的結果便躍然眼前，并且自動實現了名次按班統計。如圖2所示。

方法二:RANK+大數分組法

這種方法需要用到一個附加的列，先把數據做一次轉換。具體思路是:①把每個考生的成績按“班號×M＋成績”的形式進行變換后，填充到一個附加的列中，其中M是您自己任意選取的一個較大的數值，其值大于成績列中最大的數即可;②對變換后的成績進行排名，這樣既做到了按班分組的目的，又保證了每位考生的成績在本班內的排名不發生變化;③把排序后的名次轉換成班名次。下面操作過程中的COUNTIF(A:A，\">\"A2)起到了名次轉換的作用，其中“”用來構造文本型的條件表達式。

操作過程:(1)在單元格F1中輸入字符串“中間結果”;(2)在單元格F2中輸入公式:“=A2*1000+D2”，或者輸入公式 “=A2*(MAX(D:D)+1)+D2”，然后向下填充單元格;(3)在E2單元格中輸入公式:“=RANK(F2，F:F)-COUNTIF(A:A，\">\"A2)”，向下填充，排名結果如圖3所示;

１．隱藏F列。

方法三:SUMPRODUCT函數法

1.點擊E2單元格，輸入公式: “=SUMPRODUCT(($A$2:$A$13=$A2)*($D2<$D$2:$D$13))+1”，然后按下回車鍵，在E2單元格中便可以看到統計結果了;

2.向下填充單元格，結果如下圖4所示。補充說明:上面公式的意思是，統計班級與A2單元格字符串相同且成績小于D2中的數值的單元格的個數。“*”符號前后的括號內就是我們設置的條件。如果要設置的條件更多，就再添加“*”，并在其后添加括號，在括號內添加條件即可。

有了這三種排名方法，相信您在以后的成績統計中更加游刃有余了。當然，最終采用哪一種方法，就要看實際情況和自己的喜好了。