EXCEL經典公式解析-中式排名！

今天我們來說點談一下排名的問題，把經典的中式排名公式剖析一下。

我們先來看一下中式排名和美式排名的區別：

簡單來說，就是美式相同的排名會佔位，比如這裡的美式沒有第三名，直接第四名，也就是說，他沒有真正的並列的意思

中式排名，並列第二，後面應該是第三名，並列不佔位。

美式排名1：=RANK(B2,$B$2:$B$7)

美式排名2：=SUMPRODUCT(N($B$2:$B$7>B2))+1

中式排名： =SUMPRODUCT(($B$2:$B$7>=B2)/(COUNTIF($B$2:$B$7,$B$2:$B$7)))

美式2

你可能會發現，美式排名2的公式跟中式比較，

中國排名多出了一個COUNTIF其他基本一樣。

具體我們還是來庖丁解牛：

解析1：$B$2:$B$7>$B2 到底是什麼意思？B2=3，B3=4 我們使用B3>B2,大家肯定可以明白，

就是他們兩個比較一下大小。

那麼這裡只是把B3 替換成了換成了多個單元格，結果也由一個結果變成了多個結果

想要知道自己的排名，必須和參與排名的全部數據比較一次，看看有多少比自己大的，

也就是多少是TRUE的，來確定自己的排名。

拿75舉例，其他同理按下F9，我們可以看到結果是多個值這裡，

75是最大的，所以只有他自己一個返回TRUE，其他都是FALSE

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

普通公式看一下，就可以看出，

我們是把每個數值都和75比較了一次這個是針對75的，

但是我們其實每個數值都要和$B$2:$B$7中的全部數據比較一下大小，才能知道自己的位置有多少個TRUE，

就說明自己排名第幾在EXCEL中，參與四則運算都會讓TRUE和FALSE

進行轉換

那麼我們只需要SUM一下，看看結果就知道排名了到這裡你可能就已經明白美式排名了

解析2：COUNTIF($B$2:$B$7,$B$2:$B$7) 何解？

COUNTIF($B$2:$B$7,$B$2:$B$7)難點在於是數組公式，很多新手搞不懂是怎麼計算，其實比較簡單，

可以拆解成

COUNTIF($B$2:$B$7,$B$2)=1

COUNTIF($B$2:$B$7,$B$3)=2

COUNTIF($B$2:$B$7,$B$4)=2

……

COUNTIF($B$2:$B$7,$B$7)=1

也就是我們把一般的一次性把每個值都計算了一遍，自己在數據中一共出現了幾次，結果也是對應的6個結果，對應數組結果如下

也即是71出現了兩次，其他都是1次和下方的普通公式無異，只是我們每個單元格都需要這樣去結算所以使用數組

結合解析1和解析2，我們來看看。

75的第一名到底是怎麼計算出來的
TRUE是1，FALSE是0，所以結果合計1，也就是第一名

66的第三名是怎麼計算出來的？

計算都是一樣的，我們就不講了，看一下比較大小，

有3個大於等於66的同時可以看到有兩個71在其中,也就變成了0.5+0.5 =1，

沒有佔據第三名，合計也就是3，排名第三

解析3：SUMPRODUCT的用處？我們通過小案例來看，

1-6分別乘以10，再求和SUMPRODUCT的作用就是先讓他們分別對應相乘，

然後再相加A1:A6*B1:B6 結算結果：{10;20;30;40;50;60}

=SUMPRODUCT({10;20;30;40;50;60}) = 210

最後我們要計算每個數據的排名，就需要把它裝到一起($B$2:$B$7>=B2)/(COUNTIF($B$2:$B$7,$B$2:$B$7))

如果相同，我們就把她變成多少分之一

不多佔位最後我們使用SUMPRODUCT計算出來的多組數據進行求和，得到的結果就是排名

小結

數組函數，對於新手來說，比較難以理解，但確實函數水平的分水嶺，如果想成為一名函數高手，那麼數組這關必須過，排名這個公式本身並不複雜，我們只要學會去分解，一切問題就迎刃而解

Excel辦公實戰，高效辦公，每天進步一點點！

--THE END--

閱讀更多 Excel辦公實戰 的文章

關鍵字: Excel 同理 公式