生活工作中我們經(jīng)常需要進行排名。Excel中有一個專門用于排名的函數(shù)：Rank函數(shù)。但當(dāng)存在相同名次時，Rank函數(shù)的排名結(jié)果并不符合我們中國人的習(xí)慣。下圖為使用Rank函數(shù)排名的結(jié)果。有兩人并列第2名，第3名排位被占用，成績“70”排名第4。

在我們的排名習(xí)慣習(xí)慣中，并列排名不占用名次，因此成績“70”應(yīng)該排名第3。

本教程就為大家分享如何進行中國式排名。

一、案例

如下圖所示為一張成績表，現(xiàn)在要求根據(jù)B2：B10的成績進行排名。

方法一、sumproduct+countif函數(shù)

在C2單元格輸入公式

=SUMPRODUCT(($B$2:$B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1

下拉復(fù)制公式。

以B2單元格為例，計算成績“80”的排名，其實是計算大于80的不重復(fù)值的個數(shù)。sumproduct+countif組合就是依據(jù)這個思路構(gòu)建公式。

（1）公式 =SUMPRODUCT(1/COUNTIF($B$2:$B$10,$B$2:$B$10))是統(tǒng)計非重復(fù)值的個數(shù)的經(jīng)典用法。當(dāng)數(shù)據(jù)區(qū)域有n個重復(fù)值時，1/countif針對這n個重復(fù)值會生成n個1/n，相加后結(jié)果等于1，這n個重復(fù)值只統(tǒng)計一次。

（2）要獲得B2單元格成績的排名，就需要計算大于B2單元格數(shù)值的非重復(fù)值個數(shù)，因此在（1）中經(jīng)典公式用法的基礎(chǔ)上加入邏輯判斷公式“$B$2:$B$10>B2”。

（3）當(dāng)有3個數(shù)值大于B2單元格成績時，B2排名是第4，因此在統(tǒng)計大于B2非重復(fù)值個數(shù)的基礎(chǔ)上加1。

方法二、sum+frequency函數(shù)

在C2單元格輸入公式

=SUM(--(FREQUENCY($B$2:$B$10,IF($B$2:$B$10>=B2,$B$2:$B$10))>0))

按Ctrl+Shift+Enter結(jié)束公式輸入，向下拖動填充柄復(fù)制公式。

Frequency函數(shù)用于統(tǒng)計數(shù)據(jù)的頻率分布。以B2單元格成績“80”為例，大于80的成績有85、86、89。以80、85、86、89作為分段點，統(tǒng)計各分數(shù)區(qū)間人數(shù)，如下圖所示：

可以看到，每個分數(shù)區(qū)間至少有1人，當(dāng)分數(shù)相同時，分數(shù)區(qū)間超過1人。本例有2人成績?yōu)?5，因此80-85分數(shù)區(qū)間人數(shù)為2。

當(dāng)各分數(shù)區(qū)間人數(shù)（即E2：E5單元格）與0相比較時，結(jié)果為True(5>0)、True(2>0)、True(1>0)、True(1>0)，4個True進行相加運算時，結(jié)果為4，即成績“80”的排名第4名。

以上解釋了使用Frequency+sum函數(shù)得出排名的原理。

那么當(dāng)計算某個成績的排名時，如何得到大于等于該成績的分段點呢？IF函數(shù)的目的就是生成分段點。IF($B$2:$B$10>=B2,$B$2:$B$10)，當(dāng)大于等于B2單元格成績時，取單元格內(nèi)的值，否則為FALSE，其結(jié)果是由大于等于80的數(shù)值及FALSE構(gòu)成的一組數(shù)值。

方法三、數(shù)據(jù)透視表法

1、選中數(shù)據(jù)區(qū)域內(nèi)任意單元格，點擊【插入】-【數(shù)據(jù)透視表】，將“姓名”字段拖動到【行】區(qū)域，將“成績”字段拖動到【值】區(qū)域兩次，結(jié)果如下：

2、點擊“求和項：成績2”，選擇【值字段設(shè)置】，在打開的對話框中，選擇“值顯示方式”為“降序排列”。

點擊確定后，即可對成績排序，結(jié)果如下：

這個數(shù)據(jù)透視表的字段名稱不符合要求，比如F列是成績排名，但字段名稱是“求和項：成績2”，會造成誤解，因此需要對透視表字段名進行修改。

3、取消數(shù)據(jù)透視表匯總，修改字段名稱。

數(shù)據(jù)透視表按照姓名的拼音順序?qū)Τ煽冞M行排序，如果要求不能破壞源數(shù)據(jù)的排列順序進行排名，數(shù)據(jù)透視表法不適用。