對Excel數(shù)據(jù)進(jìn)行排名的四種方法

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

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

提示

在Excel中，COUNTIFS()函數(shù)將條件應(yīng)用于跨多個(gè)區(qū)域的單元格同時(shí)計(jì)算符合所有條件的次數(shù)。此時(shí)，其支持多條件計(jì)數(shù)。這里，使用COUNTIFS()函數(shù)統(tǒng)計(jì)出在班級中大于自身成績總分的總分個(gè)數(shù)，然后再加上1即可獲得該總分在班級中的排名。

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

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

提示

在公式中，"$B$2:$B$20=B2"用于判斷是否屬于當(dāng)前班級，"$G$2:$G$20>G2"用于判斷總分是否大于自身總分值。使用SUMPRODUCT()函數(shù)將數(shù)組對應(yīng)的值相乘并累加就可以得出大于自身總分的人數(shù)，然后將其加上1即可獲得班級排名值。

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

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

提示

在公式中，"($B$2:$B$20=B2)*$G$2:$G$20"用于判斷是否屬于當(dāng)前班級，如果是，則返回對應(yīng)的總分值，否則返回0，這樣可以得到一個(gè)數(shù)組。以當(dāng)前G2單元格的數(shù)值作為分段點(diǎn)，使用FREQUENCY()獲得小于或等于G2單元格和大于G2單元格值的數(shù)據(jù)分布頻率，這時(shí)將獲得包含這兩個(gè)頻率值的數(shù)組。最后使用INDEX()函數(shù)從這個(gè)數(shù)組中提取第2個(gè)數(shù)組，即大于G2單元格值的總分個(gè)數(shù)，再加上1即可獲得G2單元格數(shù)值在所屬班級中的排名。

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

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

提示

與前面公式相似，這里的公式中首先以班級為條件來查找同班總分值，屬于其他班的總分值被置為0，使用LARGE()函數(shù)將獲得的總分值進(jìn)行排名獲得一個(gè)數(shù)組。使用MATCH()函數(shù)獲得當(dāng)前總分在數(shù)組中的位置，該位置即為班級排名值。