一個Excel排名案例,輕鬆掌握三個重要函數

  已獲授權轉載

講中國式排名之前,我們先學習一下排名。

1.什麼是排名?

排名不等同於排序(雖然通過排序可以得出排名),排名指的是按照一定的方式確定一組數據的名次,比如將成績進行排名,得出的結果是第1名、第二名……

排名和排序的概念,很多人分不清楚,容易導致不在一個頻道上,所以希望大家首先搞明白兩者的區別。

2.如何進行排名?

普通式排名

1.1 通過排序進行排名

這個很好理解,想要獲得一組數據的排名,可以首先將數據進行排序,然後標上序號即可。

一個Excel排名案例,輕鬆掌握三個重要函數

這種排名屬於比較低級的方法,有兩個問題它無法跨越:

①如果數據有重復,無法識別重復

②如果數據有更新,無法動態排名

因此有一個高級的玩法——使用Rank函數進行排名。

1.2 通過Rank函數排名

rank函數是排名函數,最常用的是求某一個數值在某一區域內的排名。

rank函數語法形式:rank(number,ref,[order])

number :需要求排名的那個數值;

ref :排名的參照數值區域;

order:為0或1,0的情況默認不用輸入,得到的就是從大到小的排名,對應的輸入1是逆序排名。

同樣是上面的案例,我們來使用Rank函數來進行排名。

一個Excel排名案例,輕鬆掌握三個重要函數

讓我們定格最終的排名效果,發現有兩個第五名,但是沒有第六名。

一個Excel排名案例,輕鬆掌握三個重要函數

這是因為排名的數據中,有兩個數據重復了,在Rank的世界裡,重復的排名也會占用一個名次,因此沒有第六名了,直接就調到了第七名,這就是普通是排名。

中國式排名

如下圖的樣子這就是中國式排名:並列第五,然後會出現第六名。

一個Excel排名案例,輕鬆掌握三個重要函數

這種情況,使用用rank函數就無法做到,需要用到COUNTIF和SUMPRODUCT函數嵌套。

在C2單元格輸入函數,=SUMPRODUCT((B2<=B$2:B$9)/COUNTIF(B$2:B$9,B$2:B$9)),然後用力將行數向下復制。

一大坨函數,肯定看不懂是什麼鬼東西吧!

函數作用分析:

下文涉及到比較多的函數知識,如果看不懂,建議先補一補函數基礎知識,點這裡可以查看函數課程的復牌:

從0到1:輕鬆掌握職場中最常用的十二個函數

=SUMPRODUCT((B2<=B$2:B$9)/COUNTIF(B$2:B$9,B$2:B$9))

這個公式是兩個常用函數的嵌套。

1. 函數關鍵部分是 COUNTIF(B$2:B$9,B$2:B$9)

COUNTIF函數的語法規則如下:

countif(range,criteria)

參數:range 要計算其中非空單元格數目的區域

參數:criteria 以數字、表達式或文本形式定義的條件

說白了就是條件計數,在區域rang中求滿足Criteria條件的單元格的個數。

大家常用的情況,比較簡單,一般是這樣的:

上述例子,在D2單元格寫入公式=COUNTIF(B2:B9,B2)

意思就是說,求在B2:B9這個區域中,等於B2單元格數值的單元格個數是幾?

顯然,在這個區域中,等於88的單元格只有B2自身,因此結果為1.

而如果Criteria參數是一個數組區域,那麼將返回一個數組結果。

即: COUNTIF(B$2:B$9,B$2:B$9)的含義,(下面是重點)

就是分別以第2參數B$2:B$9區域中的8個單元格為條件,每次查找第1參數B$2:B$9區域中=B$2 或B$3 或B$4…… 或B$9的元素數……

返回一個數組結果是:{1,1,1,1,2,1,1,2}

即,區域中每個元素的重復次數

↑請把這句話讀三遍↑

2.解讀1/COUNTIF(B$2:B$9,B$2:B$9)

將函數求得的數組結果,作為分母,被1除,會出現什麼結果呢?

例如: COUNTIF(B$2:B$9,B$2:B$9)= {1,1,1,1,2,1,1,2}時,

1/COUNTIF(C$3:C$9,C$3:C$9) 計算就是 = {1,1,1,1,0.5,1,1,0.5}

這一步是小學數學水平,應該不難。

3.SUMPRODUCT函數

這個函數被譽為計算全能王,有限的篇幅裡,我只能講最核心的知識。

直接上結論,大家記住結論即可,以後有機會詳細講解。

SUMPRODUCT函數的萬能公式為:

=SUMPRODUCT((條件1)*(條件2)*……*求和區域)

可以做到單一條件求和、多條件求和。

因此,在這個案例中,SUMPRODUCT函數括號內的這一坨,最終做到的功能就是按照某一個條件求和。

①先來說求和

將1/COUNTIF(C$3:C$9,C$3:C$9) 得出的結果 {1,1,1,1,0.5,1,1,0.5}進行求和,你就會驚奇地發現:

總和=【區域中不重復元素的個數!】

其實原理很簡單:比如案例中89重復了兩次,那麼得出的數組中,兩個89分別對應的位置都是0.5,兩個0.5相加等於1,相當於只被計算了一次。

以此推廣,如果某個數據重復了N次,那麼它對應的COUNTIF()結果=n,而其1/COUNTIF()結果=1/n,因為一共有n個元素(因為重復了N次),因此它們的個數總和=n*(1/n)=1

…………

所以,=SUMPRODUCT((1/COUNTIF(B$2:B$9,B$2:B$9))囉嗦了這麼久,其實就是計算B$2:B$9區域中不重復元素的個數。

這一步相當於中學數學知識,對大家來說也應該沒有問題。

②附加條件的求和

因為要進行從大到小的順序排名,因此我們需要統計大於等於這個數的個數。

想一想,為什麼?

(比如,對於排名第一的數,大於等於它的只有它自己,排名第二的數,大於等於它的只有第一和它自己……所以,想要求一個數在一組數中的排名,計算出這組數中大於等於這個數的個數即可)

所以要加上一個附加條件:(B2<=B$2:B$9),而根據SUMPRODUCT函數的萬能公式,這個條件需要與求和區域進行相乘。

因此,最後的合成公式就是:

=SUMPRODUCT((B2<=B$2:B$9)/COUNTIF(B$2:B$9,B$2:B$9))

最終公式的含義是:以 符合(B2<=B$2:B$9)為條件,統計區域中不重復元素的個數最後就得到了【中國式排名】的結果。

從此,幾乎任何排名你都能搞定。

極度燒腦!!

●本文編號518,以後想閱讀這篇文章直接輸入518即可

●輸入m可以獲取到全部文章目錄

●輸入c可以獲取到全部動畫下載地址