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