排名的十九种思路
排名的十九种思路排名的十九种思路
在一个工作表中，先设置A3～A12为班级（A1、A2为名称）B3～B12为成绩。（如图1）
http://img.pconline.com.cn/images/bbs4/20068/24/1156434405281.jpg
年级排名
第一种思路：等次排名
由大到小排名：C3＝RANK(B3,$B$3:$B$12)
由小到大排名：D3＝RANK(B3,$B$3:$B$12,1)
第二种思路：不等次排名（行小排先）
由大到小：E3＝RANK(B3,$B$3:$B$12)+COUNTIF($B$3:B3,B3)-1
由小到大：F3＝RANK(B3,$B$3:$B$12,1)+COUNTIF($B$3:B3,B3)-1
第三种思路：不等次排名（行大排先）
由大到小：G3＝COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12,1)-COUNTIF($B$3:B3,B3)+2
由小到大：H3＝COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12)-COUNTIF($B$3:B3,B3)+2
第四种思路：顺次排名
由大到小：I3＝SUMPRODUCT((B$3:B$12>B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1
由小到大：J3＝SUMPRODUCT((B$3:B$12<B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1
第五路思路：数组公式
由大到小：K3＝SUM(IF(B$3:B$12>B3,1/COUNTIF(B$3:B$12,B$3:B$12)))+1
由小到大：L3＝SUM(IF(B$3:B$12<B3,1/COUNTIF(B$3:B$12,B$3:B$12)))+1
附件：
排名的十九种方法.zip论坛[*]魔方知识库，魔方知道上线啦！ 将图中数据复制到A16～B25，
http://img.pconline.com.cn/images/bbs4/20068/24/1156434475519.jpg
以下是班级（班内）排名的公式
第六种排名：等次排名
由大到小排名：C16＝SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16))+1
由小到大排名：D16＝SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16))+1
第七种排名：不等次排名（行小排先）
由大到小：E16＝SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000>B16-ROW(B16)/10000))+1
由小到大：F16＝SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000<B16+ROW(B16)/10000))+1
第八种排名：不等次排名（行大排先）
由大到小：G16＝SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000>B16+ROW(B16)/10000))+1
由小到大：H16＝SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000<B16-ROW(B16)/10000))+1
第九种排名：顺次排名
使用辅助列：K16～K25，K16＝A16&B16
由大到小：I16＝SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1
由小到大：J16＝SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1 将数据复制到第A29－B38。
下面介绍顺次排名的数组思路：
第十种排名：
由大到小：C29＝SUMPRODUCT(($A$29:$A$39=A29)*($B$29:$B$39>B29)*(FREQUENCY(MATCH($A$29:$A$38&$B$29:$B$38,$A$29:$A$38&$B$29:$B$38,),MATCH($A$29:$A$38&$B$29:$B$38,$A$29:$A$38&$B$29:$B$38,))>0))+1
由小到大：D29＝SUMPRODUCT(($A$29:$A$39=A29)*($B$29:$B$39<B29)*(FREQUENCY(MATCH($A$29:$A$38&$B$29:$B$38,$A$29:$A$38&$B$29:$B$38,),MATCH($A$29:$A$38&$B$29:$B$38,$A$29:$A$38&$B$29:$B$38,))>0))+1
第十一种思路：
由大到小：E29＝COUNT(1/(MATCH(IF(($A$29:$A$38=A29)*($B$29:$B$38>B29),$A$29:$A$38&$B$29:$B$38),$A$29:$A$38&$B$29:$B$38,)=ROW($A$29:$A$38)-ROW($A$29)+1))+1
由小到大：F29＝COUNT(1/(MATCH(IF(($A$29:$A$38=A29)*($B$29:$B$38<B29),$A$29:$A$38&$B$29:$B$38),$A$29:$A$38&$B$29:$B$38,)=ROW($A$29:$A$38)-ROW($A$29)+1))+1
第十二种思路：
由大到小：G29＝SUM(ISNUMBER(MATCH($B$29:$B$38,($A$29:$A$38=A29)*($B$29:$B$38>=B29)*$B$29:$B$38,))/COUNTIF($B$29:$B$38,$B$29:$B$38))
由小到大：H29＝SUM(ISNUMBER(MATCH($B$29:$B$38,($A$29:$A$38=A29)*($B$29:$B$38<=B29)*$B$29:$B$38,))/COUNTIF($B$29:$B$38,$B$29:$B$38))
第十三种思路：
由大到小：I29＝SUM((FREQUENCY(IF(($A$29:$A$38=A29)*($B$29:$B$38>B29),$B$29:$B$38),IF(($A$29:$A$38=A29)*($B$29:$B$38>B29),$B$29:$B$38))>0)*1)+1
由小到大：J29＝SUM((FREQUENCY(IF(($A$29:$A$38=A29)*($B$29:$B$38<B29),$B$29:$B$38),IF(($A$29:$A$38=A29)*($B$29:$B$38<B29),$B$29:$B$38))>0)*1)+1
第十四种思路：
由大到小：K29＝SUM((MATCH(B$29:B$38&"$"&(A$29:A$38=A29)*(B$29:B$38>B29),B$29:B$38&"$"&(A$29:A$38=A29)*(B$29:B$38>B29),)=ROW(A$29:A$38)-28)*(A$29:A$38=A29)*(B$29:B$38>B29))+1
由小到大：L29＝SUM((MATCH(B$29:B$38&"$"&(A$29:A$38=A29)*(B$29:B$38>B29),B$29:B$38&"$"&(A$29:A$38=A29)*(B$29:B$38>B29),)=ROW(A$29:A$38)-28)*(A$29:A$38=A29)*(B$29:B$38<B29))+1
将数据复制到第A42～B51
等次排名（断开式）
第十五种思路：C42＝COUNTIF($A$42:$A$51,A42)-FREQUENCY(IF($A$42:$A$51=A42,$B$42:$B$51),B42)+1
第十六种思路：D42＝SUMPRODUCT((A$42:A$51=A42)*(B$42:B$51>B42))+1
顺次排名（连续式）
第十七种思路：E42＝SUM((FREQUENCY(IF(($A$42:$A$51=A42)*($B$42:$B$51>B42),$B$42:$B$51),IF(($A$42:$A$51=A5)*($B$42:$B$51>B42),$B$42:$B$51))>0)*1)+1
第十八种思路：F42＝COUNT(1/(MATCH(IF(($A$42:$A$51=A42)*($B$42:$B$51>B42),$A$42:$A$51&$B$42:$B$51),$A$42:$A$51&$B$42:$B$51,)=ROW($A$42:$A$51)-ROW($A$42)+1))+1
第十九种思路：G42＝SUM(ISNUMBER(MATCH($B$42:$B$51,($A$42:$A$51=A42)*($B$42:$B$51>=B42)*$B$42:$B$51,))/COUNTIF($B$42:$B$51,$B$42:$B$51))
特别提示：数组的确定方式是用组合键：Ctrl＋Shift＋Enter。
页:
[1]