使用多種方法對EXCEL中的數據進行排名
日常工作中有時會遇到對Excel中的數據排名的需要,一般情況下可以分為美式排名和中國式排名。
美式排名通常使用RANK函數,直接指定範圍,公式就可以自動按照順序進行排名,自動處理相同數據的排名。
中國式排名有點複雜,中國式排名的習慣是並列排名不佔用後面的名次,比如無論有幾個並列第2名,之後的排名仍然是第3名。
中國式排名,其實就是對一個數在一組數據中排名第幾的統計,重複數排名相同。通常可以使用COUNTIF,FREQUENCY和SUMPRODUCT等函數結合實現,或者可以使用數據透視表也可以實現。
1. 使用SUMPRODUCT進行中國式排名
如下圖所示,對班級成員進行排名
= SUMPRODUCT((F$2:F$9>F2)*(1/COUNTIF(F$2:F$9,F$2:F$9)))+1
以單元格G2為例,簡要解釋如下:
因為SUMPRODUCT函數返回數組或區域中相應乘積的和,在這個函數中的其他函數都是以數組的形式返回值,比如F$2:F$9>F2和COUNTIF(F$2:F$9,F$2:F$9)的返回值分別如下圖所示。
F$2:F$9>F2的返回值中只有一個值為TRUE,表明只有1個數據的值比F2大。
COUNTIF(F$2:F$9,F$2:F$9)的返回值表明了對應的數值在數列中的個數,"1"表示只有1個相同值,"3"表示有3個相同值。1/ COUNTIF(F$2:F$9,F$2:F$9)是為了平均設置相同的數據在數列中的比例,避免重複計算相同項。
EXCEL中的FALSE相當於0,TRUE相當於1,SUMPRODUCT函數返回兩列數組中相應乘積的和。由於數組{F$2:F$9>F2}中只有一個TRUE,表示只有一個值比F2大,需要在SUMPRODUCT公式最後再加"1",表示處於第2位。
公式中的COUNTIF也可以換成MATCH:
= SUMPRODUCT(($F$2:$F$9>F2)*(MATCH($F$2:$F$9,$F$2:$F$9,0)=ROW($1:$8)))+1
2. 使用FREQUENCY進行中國式排名
這是一個數組公式:
= SUM(--(FREQUENCY(F$2:F$9,IF(F$2:F$9>=$F2,F$2:F$9,))>0))
輸入完成按CTRL + SHIFT + ENTER完成數組輸入。
FREQUENCY是以一列垂直數組返回相應數組的頻率分佈。IF(F$2:F$9>=$F2,F$2:F$9,)得到的結果為數組,其中大於F2的為原值,其餘的為FALSE。FREQUENCY統計出IF的結果在F$2:F$9中的分佈頻率,如果頻率大於0,結果為TRUE,否則為FALSE。"- -"的作用是將文本型、邏輯型的數值轉換為數字型數值。
3. 使用SUMPRODUCT進行分組排名
4. 使用SUM、COUNTIF進行中國式排名
這是一個數組公式:
= SUM(IF($F$2:$F$9>F2,1/COUNTIF($F$2:$F$9,$F$2:$F$9)))+1,按CTRL + SHIFT + ENTER完成數組公式輸入。
5. 使用RANK進行美式排名
=RANK(C2,$C$2:$C$9)
6. 使用數據透視表進行中國式排名
使用數據透視表的關鍵步驟是對成績字段進行兩次取值,把第2次的值作為名次進行顯示:
歡迎多多留言交流。
閱讀更多 Office技巧共享 的文章