說到將excel中的資料進行排名,大家首先想到就是rank函數,但如果說要依照條件對資料進行排名呢?小夥伴們是不是一下子就蒙圈了,似乎還沒聽過依照條件進行排名的函數。那麼今天就來跟大家分享一個在excel中按條件進行排名的公式套路,一起來看看吧!
在Excel的函數中,有按條件求和的SUMIF,有按條件求平均值的AVERAGEIF,也有按條件計數的COUNTIF,最新版本甚至有了依條件求最大值的MAXIFS函數和依條件求最小值的MINIFS函數。可是唯獨沒有可以依條件排名次的函數。
但是按條件排名次這類問題平時又的確會遇到,例如下面這個問題就是其中的一類典型代表:
我們都知道使用RANK函數可以得到一個數字在一組數字中的排名,在這個例子中的總排名就是用了公式=RANK(C2,$C$2:$C$19)得到的。
但如果要得到每家店在區域內的銷售排名該怎麼辦,難道要在每個區域中分別使用RANK函數進行排名嗎?
雖然這也是一個思路,但是效率之低可想而知,其實在Excel的函數中,是有一個可以實現按條件排名次的函數,它就是SUMPRODUCT。
在正式介紹按條件排名次的公式套路之前,讓我們先來理一理按條件排名的運算原理。
以10004這個門市為例,區域內排名是2,總排名是10,如圖所示:
它的區域排名之所以是2,很容易理解,因為在同一個銷售區域(條件)中,只有六個數,在這六個數字中,大於56.55的只有1個數就是79.72,因此它在區域內的排名就是2。
其他名次的計算原理也是一樣的,這樣想來,實現按條件排名其實包含了兩個過程:條件的判斷和大小的判斷。
把這兩個過程用公式寫出來就是:$A$2:$A$19=A2和$C$2:$C$19>C2
,可以結合實例來理解這兩部分。
先看第一個,$A$2:$A$19=A2
會得到一組邏輯值:
{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
從這個結果中可以看出,與要統計的門市在同一個區域的數據都是TRUE。
$C$2:$C$19>C2
同樣也會得到一組邏輯值:
{FALSE;TRUE;TRUE;TRUE;TRUE ;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
這個結果表示銷售額大於要統計門市時也會得到TRUE。
現在的問題是如何將這兩個部分合併起來,因為這是對一個數據同時進行的兩個判斷,所以將兩組邏輯值相乘,來看看得到了什麼結果:
圖中的這一組由0和1構成的數據,是($A$2:$A$19=A2)*($C$2:$C$19> ;C2)計算得到的結果,表示10001這個門市所在的區域中,銷售額高於14.46的有4個門市(4個1),只需要對這個結果求和,基本上就實現了排名的目的,因此公式套路也就有了:
=SUMPRODUCT(($A$2:$A$19=A2)*($C$2:$C$19>C2))
不過這樣得到的結果有個問題,名次是從0開始的,要解決也很簡單,有兩個方法。
方法1:直接在公式後面加上1,結果如圖所示。
方法2::將大於號改成大於等於,結果如圖所示。
這兩個方法,通常情況下並沒有什麼差別,使用哪個公式都可以。
以上是針對一個條件進行排名的公式,如果條件是兩個或更多,將公式套路進行擴展就行:
=SUMPRODUCT((條件區域1=條件1)* (條件區域2=條件2)* (資料區域>資料))
具體範例就不列舉了,相信大家理解了公式的原理以後,結合具體問題去自己套用是完全沒問題的。
相關學習推薦:excel教學
以上是實用Excel技巧分享:按條件進行排名的公式套路的詳細內容。更多資訊請關注PHP中文網其他相關文章!