在工作中，我們很常遇到需要對銷售業(yè)績或?qū)W生成績等進行排名。使用排序的方法可以很便捷的進行排名，但是運用函數(shù)公式可能更加方便和高大上。本文列舉了關(guān)于排名的幾種方法：
第一部分：美式排名公式
一、用RANK函數(shù)兩參數(shù)用法做基礎(chǔ)排名（默認降序排列）。
特點：如果有兩個數(shù)字排名相同，如下圖，有兩個第5名，下一名就是第7名，跳過了6。也就是說最大的次序和總數(shù)據(jù)量一致，其中的第2個第5占據(jù)了“第6”這個名次。
單列成績排名公式：=RANK(C2,C2:C11)
雙列成績排名公式:
=RANK(H2,($H$2:$H$6,$L$2:$L$6)) ---第一列
=RANK(M2,($H$2:$H$6,$L$2:$L$7)) ---第二列
參數(shù)重點：需要注意排名的成績區(qū)域。
函數(shù)格式說明： RANK(number,ref,[order]) ;Number 必需。需要找到排位的數(shù)字。Ref 必需。數(shù)據(jù)區(qū)域，多個區(qū)域用括號如(A1:A8，C1:C8)。忽略非數(shù)字格式。Order 可選。一數(shù)字，指明數(shù)字排位的方式。 如果 order 為 0（零）或省略，從大到小降序排列。如果 order 不為零，從大到小升序排列。
二、用COUNTIF函數(shù)
單列成績公式:=COUNTIF($C$2:$C$11,'>'&C2) 1
雙列成績公式:
=COUNTIF($H$2:$H$6,'>'&C2) COUNTIF($L$2:$L$6,'>'&C2) 1
=COUNTIF($H$2:$H$6,'>'&L2) COUNTIF($L$2:$L$6,'>'&L2) 1
和正序排名類似，用COUNTIF函數(shù)也可以替代RANK函數(shù)實現(xiàn)倒序排名，公式如下：=COUNTIF($C$2:$C$11,'<'&C2) 1
三、并列不重復(fù)排名
1、RANK和COUNTIF函數(shù)組合
公式：=RANK($C3,$C$3:$C$12) COUNTIF(C$3:$C3,$C3)-1
特點：用這個公式，根據(jù)Excel行的特點，相同分數(shù)時，行數(shù)越大排名越大。如下圖D6和D7單元格。
2、用COUNTIF函數(shù)也可以實現(xiàn)上一步的不重復(fù)排名。
公式：=COUNTIF(H$3:$H3,H3) COUNTIF($H$3:$H$12,'>'&H3)
第二部分：中國式排名公式匯總
什么是“中國式排名”？以下圖為例說明，C列是學(xué)生成績，D列為學(xué)生成績排位。比如學(xué)生江張六和張七的成績并列排名為5，李五和李六的并列排名是6，張五排名是7。這種排名方式在并列排名之后仍保持連續(xù)的名次，中間不會間斷。世界只有中國對學(xué)生成績采用的是排名的方法，故稱“中國式排名”。
先列舉一下我搜集整理的中國式排名公式：
1. 普通公式=SUMPRODUCT(($C$2:$C$11>C2)/COUNTIF($C$2:$C$11,$C$2:$C$11)) 1
2. 數(shù)組公式: =SUM((C$2:C$11>=C2)/COUNTIF(C$2:C$11,C$2:C$11))
3. 數(shù)組公式: =SUM(IF($C$2:$C$11<=C2,'',1/(COUNTIF($C$2:$C$11,$C$2:$C$11)))) 1
4. 公式=SUMPRODUCT(N(IF(FREQUENCY($C$2:$C$11,$C$2:$C$11),$C$2:$C$11,0)>=C2)
5. 普通公式=SUM(--(FREQUENCY($C$2:$C$11,IF($C$2:$C$116>=C2,$C$2:$C$11))>0))
6. 數(shù)組公式: =SUM(--IF(FREQUENCY($C$2:$C$11,$C$2:$C$11)>0,$C$2:$C$11>C2)) 1
7. 數(shù)組公式: =SUM(--IF($C$2:$C$11>=C2,MATCH($C$2:$C$11,$C$2:$C$11,)=ROW($C$2:$C$11)-1))
8. 雙列公式：
=COUNT(0/FREQUENCY(CHOOSE({1,2},$C$2:$C$6,$G$2:$G$6)-C2,ROW($1:$83)-1))
=COUNT(0/FREQUENCY(CHOOSE({1,2},$C$2:$C$6,$G$2:$G$6)-G2,ROW($1:$83)-1))
注意事項：
· 有的Excel運用不了這個公式，因為你的Excel中宏的級別設(shè)成是低?；蛘呤窃诎惭boffice沒有完全安裝。
第三部分：中國式排名公式具體分析
一、運用SUMPRODUCT函數(shù)
1. 公式=SUMPRODUCT(($C$2:$C$11>C2)/COUNTIF($C$2:$C$11,$C$2:$C$11)) 1
SUMPRODUCT是多條件求和函數(shù)。舉例說明，上圖兩個數(shù)組數(shù)組1和數(shù)組2，公式“SUMPRODUCT(A2:B4, C2:D4)”的作用是：兩個數(shù)組的所有元素對應(yīng)相乘，然后把乘積相加，即 3*2 4*7 8*6 6*7 1*5 9*3。(156)你的，明白？
再來說公式：公式的前半部分“($E$3:$E$16>$E3)”是返回一個數(shù)組：在E3:E16區(qū)域內(nèi)大于E3單元格數(shù)值的個數(shù)，這里為0；公式后半部分“/ COUNTIF($C$2:$C$11,$C$2:$C$11)”可表述為“*1/ COUNTIF($C$2:$C$11,$C$2:$C$11)”， COUNTIF($C$2:$C$11,$C$2:$C$11)的值永遠為一個固定值1，大家可以測試一下。所以這個公式可簡化為0*1 1=1。意思就是說在E3:E16區(qū)域內(nèi)沒有比E3大的數(shù)，排名為1。
再舉例說明單元格D3的公式為：“= SUMPRODUCT(($C$2:$C$11>C2)/COUNTIF
($C$2:$C$11,$C$2:$C$11)) 1”。在E3:E16區(qū)域內(nèi)比E10大的數(shù)字有6個，故其排名為7，所以函數(shù)公式后面要加1。
2. 公式簡化：
=SUMPRODUCT((C$2:C$11>=C2)/COUNTIF(C$2:C$11,C$2:C$11))
公式解釋：=SUMPRODUCT(($A$2:$A$11>=A2)/COUNTIF($A$2:$A$11,$A$2:$A$11))
$A$2:$A$11>=A2：運算結(jié)果得到{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}，
意思就是找到大于等于A2的分數(shù)，返回邏輯值。如果大于等于就是真，小于等于就是假（邏輯值參與運算的時候FALSE=0,TRUE=1)
COUNTIF($A$2:$A$11,$A$2:$A$11)：運算結(jié)果得到{1;1;1;2;1;1;1;1;1;2}。
意思是61在數(shù)據(jù)中有1個，81在數(shù)據(jù)中有1個，63在數(shù)據(jù)中有1個，83在數(shù)據(jù)中有2個，以此類推。就是求出A2:A11的每個數(shù)據(jù)在A2:A11的全部數(shù)據(jù)中有幾個相同的。($A$2:$A$11>=A2)/COUNTIF($A$2:$A$11,$A$2:$A$11)：運算結(jié)果為：{1;1;1;0.5;1;1;1;1;1;0.5}，這樣兩個0.5加起來就是1，這樣就做到相同分數(shù)只計算一次排名。
這也是大家所說的中國式排名，在中國人的習(xí)慣中，無論有幾個并列第2 名，之后的排名仍應(yīng)該是第3 名，即并列排名不占用名次。
3. 降序公式
=SUMPRODUCT((C$2:C$11<=C2)/COUNTIF(C$2:C$11,C$2:C$11))
4. 公式：=SUMPRODUCT(N(IF(FREQUENCY($C$2:$C$11,$C$2:$C$11),$C$2:$C$11,0)>=C2)
【思路分析】（1） 使用FREQUENCY將數(shù)組中不重復(fù)出現(xiàn)的元素標識出來，第一次出現(xiàn)的元素標識出現(xiàn)次數(shù)，重復(fù)出現(xiàn)的元素標識0，如FREQUENCY($C$2:$C$11,$C$2:$C$11)返回數(shù)組{2;1;2;0;1;1;0;1;1;0}
（2） 使用IF條件配合第1步的標識使原數(shù)組返回不重復(fù)元素與0組成的數(shù)組，即公式中的IF(FREQUENCY($C$2:$C$11,$C$2:$C$11), $C$2:$C$11,0)返回數(shù)組{8;9.5;9;0;8.5;6;0;7.5;7;0}
（3） 將第2步結(jié)果與數(shù)組中的每一個元素比較，判斷是否大于等于其數(shù)值，返回一串邏輯值構(gòu)成的數(shù)組即IF(FREQUENCY($C$2:$C$11,$C$2:$C$11), $C$2:$C$11,0)>=C2返回結(jié)果為{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
（4） 使用N函數(shù)將邏輯值TRUE和FALSE分別轉(zhuǎn)化為1和0，即{1;1;1;0;1;0;0;0;0;0}
（5） 最后使用SUMPRODUCT函數(shù)統(tǒng)計第4步中的求和結(jié)果，即中國式排名的名次。
這四個公式雖然也是數(shù)組函數(shù)，但因為是Sumproduct()所以不需要數(shù)組三鍵就能正確計算。
二、運用SUM IF函數(shù)組合
1. 數(shù)組公式:
=SUM(IF($C$2:$C$11<=C2,'',1/(COUNTIF($C$2:$C$11,$C$2:$C$11)))) 1
這是一個SUM(IF(Logical-test[value-if,[value-if-false），countif（rang，criteria））的數(shù)組公式，按ctrl shift enter三鍵結(jié)束。下拉復(fù)制公式即可得出其余的排名。
公式解釋： COUNTIF($C$2:$C$11,$C$2:$C$11)部分：這是一個數(shù)組運算用法，它的運算過程是：
COUNTIF($C$2:$C$11,C2)
COUNTIF($C$2:$C$11,C3)
COUNTIF($C$2:$C$11,C4)
……
分別統(tǒng)計B2、B3、B4單元格在B2：B6區(qū)域中出現(xiàn)的次數(shù)。得到結(jié)果為：1,1,1,2,2。其中“1”代表此單元格中的內(nèi)容在B2：B6區(qū)域中只出現(xiàn)一次，即沒有重復(fù)；“2”代表此單元格中的內(nèi)容在B2：B6區(qū)域里重復(fù)2次。這一步的操作，可以得到數(shù)據(jù)是否有重復(fù)和以及重復(fù)的次數(shù)。
1/ COUNTIF($C$2:$C$11,$C$2:$C$11)部分：在公式編輯欄選中這部分公式，按F9鍵查看運算結(jié)果為：{1;1;1;0.5;0.5}。
IF($C$2:$C$11>C2,……)部分：
IF第一參數(shù)：$C$2:$C$11>C2的結(jié)果是：{FALSE;TRUE;FALSE;TRUE;TRUE}，意思是B2單元格中的內(nèi)容分別和C2：C11區(qū)域內(nèi)的各個單元格內(nèi)容進行大小比較。
“IF($C$2:$C$11>B2, 1/ COUNTIF($C$2:$C$11,$C$2:$C$11))”，這里IF省略了第三參數(shù)，因此當(dāng)?shù)玫紽ALSE時，此時將返回結(jié)果“FALSE”，當(dāng)?shù)玫絋RUE時，此時將返回對應(yīng)的結(jié)果，得到的結(jié)果是{FALSE;1;FALSE;0.5;0.5}。
接著SUM函數(shù)對IF函數(shù)內(nèi)的結(jié)果進行加總，得到結(jié)果“3”。為什么還要再加上“1”呢？原因是IF函數(shù)內(nèi)的測試條件是“>”，對于“B2：B6區(qū)域”里的最大值“72”而言，得到的結(jié)果是{FALSE;FALSE;FALSE;FALSE;FALSE}，那么SUM函數(shù)計算得到的值就是“0”，顯然排名第0位，不符合常識，因此要額外加上“1”。
這個中國式排名公式的核心部分就是：“1/ COUNTIF($C$2:$C$11,$C$2:$C$11)”，目的是避免重復(fù)計算相同項。
2. 降序公式
{=SUM(IF($C$2:$C$11<=C2,1/(COUNTIF($C$2:$C$11,$C$2:$C$11)),''))}
三、運用SUM COUNTIF函數(shù)
1.數(shù)組公式=SUM((C2<=C$2:C$11)/COUNTIF(C$2:C$11,C$2:C$11)) }
函數(shù)關(guān)鍵部分是 1/COUNTIF(C$2:C$11,C$2:C$11)。countif（Rng,Cel）函數(shù)的作用是【統(tǒng)計】 Rng區(qū)域中符合Cel【相同條件】的【元素個數(shù)】，即每個元素的重復(fù)次數(shù)。
如果Cel參數(shù)是一個數(shù)組區(qū)域，那么將返回一個數(shù)組結(jié)果：即： COUNTIF(C$2:C$11,C$1:C$11)的含義，就是分別以第2參數(shù)C$2:C$11區(qū)域中的7個單元格為條件，每次查找第1參數(shù)C$2:C$11區(qū)域中=C$2 或C$3 或C$4…… 或C$11的元素數(shù)……返回一個數(shù)組結(jié)果如：{1,1,2,1,2,1,1}即，區(qū)域中每個元素的重復(fù)次數(shù)。
顯然1代表重復(fù)次數(shù)=1，即這個元素在整個區(qū)域中只出現(xiàn)了1次。
2代表重復(fù)次數(shù)=2，即這個元素在整個區(qū)域中一共出現(xiàn)了2次。
…………
到這里就可以發(fā)現(xiàn)一個重要規(guī)律： 【區(qū)域中，重復(fù)n次的元素個數(shù)=n個】。即，
重復(fù)次數(shù)=1的 元素數(shù)只有1個，
重復(fù)次數(shù)=2的 元素數(shù)會有2個，
重復(fù)次數(shù)=3的 元素數(shù)會有3個，
……
重復(fù)次數(shù)=5的 元素數(shù)會有5個
因此，接下來引入一個重要的計算結(jié)果：1/COUNTIF(C$2:C$11,C$2:C$11)
例如： COUNTIF(C$2:C$11,C$2:C$11)= {1,1,2,1,2,1,1}時，
1/ COUNTIF(C$2:C$11,C$2:C$11) 計算就返回 = {1,1,0.5,1,0.5,1,1}
那么把這個數(shù)組結(jié)果相加，你就會驚奇地發(fā)現(xiàn)：總和=區(qū)域中不重復(fù)元素的個數(shù)
其實原理很簡單，相當(dāng)于對于每一個不重復(fù)元素來說，如果這個元素在區(qū)域中一共重復(fù)了n次，那就一定會有n個元素。
那么，對于每一個元素COUNTIF（）結(jié)果=n，而其1/COUNTIF()結(jié)果=1/n，因為一共有n個元素，那么它們的總和=n*（1/n）=1
…………
即，=Sum（1/Countif(rng,cel)) 得到的計算結(jié)果，就會是rng區(qū)域中不重復(fù)元素的個數(shù)。進一步，如果我們需要統(tǒng)計比這個數(shù)大的個數(shù)，當(dāng)然使用(C$3:C$9>=C3)作為數(shù)組計算條件就可以了。
因此，最后的合成公式就是：SUM((C3<=C$2:C$11>=)/COUNTIF(C$2:C$11,C$2:C$11))
即，用sum（）統(tǒng)計以符合 (C$2:C$11>=C3)為條件，用/COUNTIF(C$2:C$11,C$2:C$11)方法統(tǒng)計區(qū)域中不重復(fù)元素的個數(shù)最后就得到了【中國式排名】的結(jié)果。
2. 降序公式：
{=SUM((C2>=$C$2:$C$11)/COUNTIF($C$2:$C$11,$C$2:$C$11))}
四、SUM FREQUENCY組合
1. 數(shù)組公式:
{=SUM(--IF(FREQUENCY($C$2:$C$11,$C$2:$C$11)>0,$C$2:$C$11>C2)) 1}
2. 普通公式:
=SUM(--(FREQUENCY($C$2:$C$11,IF($C$2:$C$116>=C2,$C$2:$C$11))>0))
五、SUM MATCH組合
{=SUM(--IF($C$2:$C$11>=C2,MATCH($C$2:$C$11,$C$2:$C$11,)=ROW($C$2:$C$11)-1))}
六、 統(tǒng)計雙列成績排名公式
=COUNT(0/FREQUENCY(CHOOSE({1,2},$H$2:$H$6,$L$2:$L$6)-H2,ROW($1:$100)-1)) =COUNT(0/FREQUENCY(CHOOSE({1,2},$H$2:$H$6,$L$2:$L$6)-L2,ROW($1:$100)-1))
七、Excel用SUMPRODUCT實現(xiàn)多條件排名
下圖是匯總后的成績表，三所學(xué)校各個專業(yè)的學(xué)生成績都放到了一個工作表中。為了做好成績分析，要求以總分為依據(jù)做好兩個排名：一是排出每位學(xué)生在全市相同專業(yè)的學(xué)生中的名次；二是排出每位學(xué)生在本校本專業(yè)中的名次。
可以將數(shù)據(jù)按專業(yè)、按學(xué)校分別篩選出來復(fù)制到不同的工作表中，然后在不同的工作表中用RANK函數(shù)進行排序。如果使用SUMPRODUCT函數(shù)來完成這個有條件的排名工作，就方便多了。具體實現(xiàn)過程如下：
1、準備工作：
選定總分所在的H2:H1032單元格區(qū)域，點擊功能區(qū)“公式”選項卡“定義的名稱”功能組中“定義名稱”按鈕，在彈出的“新建名稱”對話框“名稱”輸入框中輸入為此區(qū)域定義的名稱“zongfen”。此時，對話框下方的“引用位置”后的輸入框中已經(jīng)自動輸入我們選定的單元格區(qū)域“=對口!$H$2:$H$1032”，如圖2所示。
按同樣的方法，選定學(xué)校所在單元格區(qū)域I2:I1032、專業(yè)所在單元格區(qū)域J2:J1032，分別為它們指定名稱“xuexiao”和“zhuanye”。完成后，這準備工作就算是結(jié)束了。
2、排定名次：
在K1單元格輸入標題“按專業(yè)排名”。點擊K2單元格，輸入公式
“=SUMPRODUCT((zhuanye=$J2)*($H2<zongfen)) 1”，按下回車鍵，結(jié)果出來了吧？向下拖動其填充句柄至最后一行，OK，按專業(yè)排名就算完成了。
在K1單元格輸入標題“按專業(yè)排名”。點擊K2單元格，輸入公式“=SUMPRODUCT((zhuanye=$J2)*($H2<zongfen)) 1”，按下回車鍵，結(jié)果出來了吧？向下拖動其填充句柄至最后一行，OK，按專業(yè)排名就算完成了。
如果您也遇到類似的問題，比如平行班的成績匯總在一張工作表中，而我們又需要學(xué)生的班內(nèi)名次，那么不妨照此辦理一回，呵呵，那效果，真的是誰用誰知道啊。
說明：這里我們使用的是SUMPRODUCT函數(shù)的多條件計數(shù)功能。
語法：＝SUMPRODUCT((條件1)*(條件2)*(條件3)* …(條件n))
作用：統(tǒng)計同時滿足條件1、條件2到條件n的記錄的個數(shù)。
聯(lián)系客服
