應(yīng)用場(chǎng)景

下圖工作表中顯示了2組人員的分?jǐn)?shù)，為方便大家閱讀，第2組用金黃色底紋進(jìn)行了突出顯示。接下來(lái)我們要分不同的組別對(duì)每個(gè)小組中的人員分?jǐn)?shù)進(jìn)行排名。分?jǐn)?shù)越高，名次越靠前，每個(gè)小組中分?jǐn)?shù)最高的為小組第１名。

操作步驟

1.選中D2單元格，輸入公式：=SUMPRODUCT(($A$2:$A$9=A2)*($C$2:$C$9>C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))+1，輸入完畢按回車(chē)鍵確認(rèn)公式，即可返回“賈探春”在“1組”中的排名“1”。

公式說(shuō)明：

$A$2:$A$9=A2為邏輯值，即判斷$A$2:$A$9單元格中的值是否等于A2；

$C$2:$C$9>C2也是邏輯值，即判斷$C$2:$C$9單元格中的值是否大于C2單元格中的值；

COUNTIF($C$2:$C$9,$C$2:$C$9)用于統(tǒng)計(jì)$C$2:$C$9單元格區(qū)域中每一行的值所出現(xiàn)的次數(shù)。

SUMPRODUCT(($A$2:$A$9=A2)*($C$2:$C$9>C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))的含義是：在滿(mǎn)足$A$2:$A$9單元格中的值為“1組”的前提下，統(tǒng)計(jì)$C$2:$C$9對(duì)應(yīng)單元格中分?jǐn)?shù)大于C2單元格（98）的分?jǐn)?shù)個(gè)數(shù)。

因?yàn)榇笥贑2單元格的分?jǐn)?shù)個(gè)數(shù)要加上1，才能得到C2單元格自身的排序名次，所以公式的最后還要加上“+１”。

2.將公式向下填充，即可得到所有人員在所屬小組中的排名。

注意事項(xiàng)

如果希望得到的排名是中國(guó)式排名（重復(fù)排名不占用名次），上述公式中的“/COUNTIF($C$2:$C$9,$C$2:$C$9)”不能省略。如果省略，得到的排名將是美國(guó)式排名，中間會(huì)有名次被跳過(guò)，參見(jiàn)下圖E列，2組有兩個(gè)第“1”名，第“2”名被跳過(guò)，接下去直接排到了第“3”名。

拓展知識(shí)

如果想對(duì)所有人員的分?jǐn)?shù)整體進(jìn)行中國(guó)式排名（不區(qū)分組別），可將前面案例公式中的第一個(gè)邏輯值($A$2:$A$9=A2)刪除，將公式修改為“=SUMPRODUCT(($C$2:$C$9>C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))+1”。