718那天，SUT學(xué)習(xí)交流群里一位北京的小伙伴咨詢了關(guān)于數(shù)據(jù)比較大小的問(wèn)題，問(wèn)題截圖如下：

        從上圖可以看出，剛開(kāi)始管理員小S并沒(méi)有理解小豬的意思，直接給出Rank函數(shù)，其實(shí)Rank只適用于美式排名，不適用于中國(guó)式排名。那究竟什么是中國(guó)式排名呢？按照中國(guó)人的習(xí)慣，無(wú)論有幾個(gè)第2名，都是并列第2名，即并列排名不占用名次，這就是中國(guó)式排名。

        通過(guò)上圖可以看出，C列直接使用Rank函數(shù)進(jìn)行排名，得到也就是美式排名，并列排名并占用名次。D列采用連續(xù)排名，同樣的分?jǐn)?shù)，先找到名次就靠前，可能不太公平。E列到I列是五種方法得到的中國(guó)式排名，可以看到分?jǐn)?shù)88都是第2名，78分是第3名。接下來(lái)分別為大家介紹這三種排名：

        美式排名是這三種排名中最簡(jiǎn)單的，可以直接使用Rank函數(shù)得到。Rank函數(shù)的語(yǔ)法說(shuō)明如下（截圖來(lái)自微軟官方，Excel中F1鍵）：

        我仿造的數(shù)據(jù)C2單元格中的公式為：=RANK(B2,$B$2:$B$100,0)，采用降序排列，最后一個(gè)參數(shù)0可以省略。

        問(wèn)：這里為什么寫100？

        答：隨便寫的一個(gè)，只要大于數(shù)據(jù)8即可，因?yàn)閿?shù)據(jù)（包含標(biāo)題）有8行。

        前面已經(jīng)說(shuō)明了連續(xù)排名會(huì)導(dǎo)致不公平的狀況出現(xiàn)，但這不影響我們學(xué)習(xí)這項(xiàng)技能，在美式排名的技術(shù)上加上Countif函數(shù)，即可實(shí)現(xiàn)連續(xù)排名。如果忘記Countif函數(shù)如何使用的小伙伴可以移步【Excel技巧】- 辦公常用的十大函數(shù)@COUNTIFS函數(shù)。

        連續(xù)排名D2單元格公式為：=RANK(B2,$B$2:$B$100,0) COUNTIF($B$2:B2,B2)-1

        這個(gè)公式不解釋，相比大家也能理解，如果還不理解的可以以此單擊【公式】→【公式求值】單步查看哦。

        中國(guó)式排名的方法有很多，主要介紹第一個(gè)方法，其他的方法大家可以下載素材文件自行學(xué)習(xí)哦。

        E2單元格的公式為：{=SUM(IF($B$2:$B$100>B2,1/COUNTIF($B$2:$B$100,$B$2:$B$100))) 1}，公式兩邊的大括號(hào)不是手動(dòng)輸入的哦，是通過(guò)同時(shí)按下Ctrl Shift Enter三個(gè)鍵自動(dòng)生成的。對(duì)數(shù)組公式了解的小伙伴一眼就能看穿。

        公式這么長(zhǎng)，到底是什么意思呢？解釋如下（便于解釋修改100為8）：

　　COUNTIF($B$2:$B$8,$B$2:$B$8)部分：這是一個(gè)數(shù)組運(yùn)算用法，它的運(yùn)算過(guò)程是：

　　COUNTIF($B$2:$B$8,B2)

　　COUNTIF($B$2:$B$8,B3)

　　COUNTIF($B$2:$B$8,B4)

　　……

　　分別統(tǒng)計(jì)B2、B3、B4單元格在B2：B8區(qū)域中出現(xiàn)的次數(shù)。得到結(jié)果為：1,3,1,3,1,3,1。其中“1”代表此單元格中的內(nèi)容在B2：B8區(qū)域中只出現(xiàn)一次，即沒(méi)有重復(fù)；“3”代表此單元格中的內(nèi)容在B2：B8區(qū)域里重復(fù)3次。這一步的操作，可以得到數(shù)據(jù)是否有重復(fù)和以及重復(fù)的次數(shù)。

　　1/COUNTIF($B$2:$B$8,$B$2:$B$8)部分：

　　在公式編輯欄選中這部分公式，按F9鍵查看運(yùn)算結(jié)果為：{1;0.333333333333333;1;0.333333333333333;1;0.333333333333333;1}。

　　IF($B$2:$B$8>B2,……)部分：

　　IF第一參數(shù)：$B$2:$B$8>B2的結(jié)果是：{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}，意思是B2單元格中的內(nèi)容分別和B2：B8區(qū)域內(nèi)的各個(gè)單元格內(nèi)容進(jìn)行大小比較。

　　“IF($B$2:$B$8>B3,1/COUNTIF($B$2:$B$8,$B$2:$B$8))”，這里IF省略了第三參數(shù)，因此當(dāng)?shù)玫紽ALSE時(shí)，此時(shí)將返回結(jié)果“FALSE”，當(dāng)?shù)玫絋RUE時(shí)，此時(shí)將返回對(duì)應(yīng)的結(jié)果，得到的結(jié)果是{1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。

　　接著SUM函數(shù)對(duì)IF函數(shù)內(nèi)的結(jié)果進(jìn)行加總，得到結(jié)果“1”。為什么還要再加上“1”呢？原因是IF函數(shù)內(nèi)的測(cè)試條件是“>”，對(duì)于“B2：B8區(qū)域”里的最大值“99”而言，得到的結(jié)果是{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}，那么SUM函數(shù)計(jì)算得到的值就是“0”，顯然排名第0位，不符合常識(shí)，因此要額外加上“1”。

　　這個(gè)中國(guó)式排名公式的核心部分就是：“1/COUNTIF($B$2:$B$8,$B$2:$B$8)”，目的是避免重復(fù)計(jì)算相同項(xiàng)。