說說Excel中的函數排名

說說Excel中的函數排名

1.什么是排名?

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

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

2.如何進行排名?

普通排名

1.1 通過排序進行排名

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

這種排名屬于比較低級的方法，有兩個問題它無法跨越：

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

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

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

1.2 通過Rank函數排名

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

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

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

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

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

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

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

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

中國式排名

如下圖的樣子這就是中國式排名：并列第五，然后會出現第六名。

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

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

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

函數作用分析：

=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)為條件，統計區域中不重復元素的個數最后就得到了【中國式排名】的結果。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。