對Excel資料進行排名的四種方法

在對Excel資料進行分析時，經常需要獲得資料的排名。在對資料進行排名時，資料被分為不同的組，需要獲得其在所屬的組中的排名值。例如，在對學生的成績進行分析時，存在一個年級成績統計表中，該表包含各個班級學生的成績，需要獲得成績在班級中的排名值作為成績分析的參考。本文介紹了對Excel資料進行排名的四種方法。

1、啟動Excel並開啟工作表，在工作表中選擇單元格區域，在編輯列中輸入公式"=COUNTIFS($B$2:$B$20,B2,$G$2:$G$20,">"&G2)+1"，按Ctrl+Enter鍵結束公式輸入，此時選擇單元格區域中顯示總分在班級中的排名值，如圖1所示。

圖1　使用COUNTIFS()函數獲得排名值

提示

在Excel中，COUNTIFS()函數將條件應用於跨多個區域的單元格同時計算符合所有條件的次數。此時，其支援多條件計數。這裡，使用COUNTIFS()函數統計出在班級中大於自身成績總分的總分個數，然後再加上1即可獲得該總分在班級中的排名。

2、在工作表中選擇放置班級排名值的單元格區域，在編輯列中輸入公式"=SUMP RODUCT(($B$2:$B$20=B2)*($G$2:$G$20>G2))+1"，按Ctrl+Enter鍵結束公式輸入，此時選擇單元格區域中顯示排名值，如圖2所示。

圖2　使用SUMPRODUCT()函數獲得排名值

提示

在公式中，"$B$2:$B$20=B2"用於判斷是否屬於當前班級，"$G$2:$G$20>G2"用於判斷總分是否大於自身總分值。使用SUMPRODUCT()函數將陣列對應的值相乘並累加就可以得出大於自身總分的人數，然後將其加上1即可獲得班級排名值。

3、在工作表中選擇放置班級排名值的單元格區域，在編輯列中輸入公式"=INDEX(FREQUENCY(($B$2:$B$20=B2)*$G$2:$G$20,G2),2)+1"，按Ctrl+Enter鍵結束公式的輸入，此時選擇單元格區域中顯示排名值，如圖3所示。

圖3　使用INDEX()函數和FREQUENCY()函數獲得排名值

提示

在公式中，"($B$2:$B$20=B2)*$G$2:$G$20"用於判斷是否屬於當前班級，如果是，則返回對應的總分值，否則返回0，這樣可以得到一個陣列。以當前G2單元格的數值作為分段點，使用FREQUENCY()獲得小於或等於G2單元格和大於G2單元格值的資料分布頻率，這時將獲得包含這兩個頻率值的陣列。最後使用INDEX()函數從這個陣列中提取第2個陣列，即大於G2單元格值的總分個數，再加上1即可獲得G2單元格數值在所屬班級中的排名。

4、在工作表中選擇放置排名值的單元格區域的第一個單元格，在編輯列中輸入公式"=MATCH(G2,LARGE(($B$2:$B$20=B2)*$G$2:$G$20,ROW($2:$20)-1),)"，按Ctrl+Shift+Enter鍵建立陣列公式。將公式向下填充到其他單元格中，此時單元格中顯示排名值，如圖4所示。

圖4　使用MATCH()函數和LARGE()函數獲得排名值

提示

與前面公式相似，這裡的公式中首先以班級為條件來查詢同班總分值，屬於其他班的總分值被置為0，使用LARGE()函數將獲得的總分值進行排名獲得一個陣列。使用MATCH()函數獲得當前總分在陣列中的位置，該位置即為班級排名值。