|
編按：哈嘍，大家好！說到將excel中的數(shù)據(jù)進(jìn)行排名，大家首先想到就是rank函數(shù)，但如果說要按條件對數(shù)據(jù)進(jìn)行排名呢？小伙伴們是不是一下子就蒙圈了，似乎還沒有聽說過按條件進(jìn)行排名的函數(shù)。那么今天，老菜鳥就給大家分享一個(gè)在excel中按條件進(jìn)行排名的公式套路，一起來看看吧！ ＊＊＊＊＊＊＊＊＊ 在Excel的函數(shù)中，有按條件求和的SUMIF，有按條件求平均值的AVERAGEIF,也有按條件計(jì)數(shù)的COUNTIF，最新版本中甚至有了按條件求最大值的MAXIFS函數(shù)和按條件求最小值的MINIFS函數(shù)。可是唯獨(dú)沒有可以按條件排名次的函數(shù)。 但是按條件排名次這類問題平時(shí)又的確會(huì)遇到，例如下面這個(gè)問題就是其中的一類典型代表：
我們都知道使用RANK函數(shù)可以得到一個(gè)數(shù)字在一組數(shù)字中的排名，在這個(gè)例子中的總排名就是用了公式=RANK(C2,$C$2:$C$19)得到的。 但是如果要得到每個(gè)門店在區(qū)域內(nèi)的銷售排名該怎么辦，難道要在每一個(gè)區(qū)域中分別使用RANK函數(shù)進(jìn)行排名嗎？ 雖然這也是一個(gè)思路，但是效率之低可想而知，其實(shí)在Excel的函數(shù)中，是有一個(gè)可以實(shí)現(xiàn)按條件排名次的函數(shù)，它就是SUMPRODUCT。 在正式介紹按條件排名次的公式套路之前，讓我們先來理一理按條件排名的運(yùn)算原理。 以10004這個(gè)門店為例，區(qū)域內(nèi)排名是2，總排名是10，如圖所示：
它的區(qū)域排名之所以是2，很容易理解，因?yàn)樵谕粋€(gè)銷售區(qū)域（條件）中，只有六個(gè)數(shù)，在這六個(gè)數(shù)字中，大于56.55的只有1個(gè)數(shù)就是79.72，因此它在區(qū)域內(nèi)的排名就是2。 其他名次的計(jì)算原理也是一樣的，這樣想來，實(shí)現(xiàn)按條件排名其實(shí)包含了兩個(gè)過程：條件的判斷和大小的判斷。 把這兩個(gè)過程用公式寫出來就是：$A$2:$A$19=A2和$C$2:$C$19>C2，可以結(jié)合實(shí)例來理解這兩部分。 首先看第一個(gè)，$A$2:$A$19=A2會(huì)得到一組邏輯值： {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
從這個(gè)結(jié)果中可以看出，與要統(tǒng)計(jì)的門店在同一個(gè)區(qū)域的數(shù)據(jù)都是TRUE。 $C$2:$C$19>C2同樣也會(huì)得到一組邏輯值： {FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
這個(gè)結(jié)果表示銷售額大于要統(tǒng)計(jì)門店時(shí)也會(huì)得到TRUE。 現(xiàn)在的問題是如何將這兩個(gè)部分合并起來，因?yàn)檫@是對一個(gè)數(shù)據(jù)同時(shí)進(jìn)行的兩個(gè)判斷，所以將兩組邏輯值相乘，來看看得到了什么結(jié)果：
圖中的這一組由0和1構(gòu)成的數(shù)據(jù)，是($A$2:$A$19=A2)*($C$2:$C$19>C2)計(jì)算得到的結(jié)果，表示10001這個(gè)門店所在的區(qū)域中，銷售額高于14.46的有4個(gè)門店（4個(gè)1），只需要對這個(gè)結(jié)果求和，基本上就實(shí)現(xiàn)了排名的目的，因此公式套路也就有了： =SUMPRODUCT(($A$2:$A$19=A2)*($C$2:$C$19>C2))
不過這樣得到的結(jié)果有個(gè)問題，名次是從0開始的，要解決也很簡單，有兩個(gè)方法。 方法1：直接在公式后加1，結(jié)果如圖所示：
方法2：:將大于號(hào)改成大于等于，結(jié)果如圖所示：
這兩個(gè)方法，通常情況下并沒有什么區(qū)別，使用哪個(gè)公式都可以。 以上是針對一個(gè)條件進(jìn)行排名的公式，如果條件是兩個(gè)或者更多，將公式套路進(jìn)行擴(kuò)展就行： =SUMPRODUCT((條件區(qū)域1=條件1)* (條件區(qū)域2=條件2)* (數(shù)據(jù)區(qū)域>數(shù)據(jù))) 具體示例就不列舉了，相信大家理解了公式的原理以后，結(jié)合具體問題去自己套用是完全沒問題的。 |
|
|
