（Excel）常⽤函数公式及操作技巧之三：排名及排序筛选（⼆）
(Excel)常⽤函数公式及操作技巧之三：
排名及排序筛选（⼆）
求最精简的⾃动排名公式
=RANK(E2,$E$2:$E$21)
=RANK(A2,$A$2:$A$9,0)
=RANK(A2,$A$2:$A$10)+COUNTIF($A$2:$A2,$A2)-1（如果数据列中数值有相同）
=RANK(F10,$F10:$Q10)+COUNTIF($F10:F10,F10)-1
=INDEX(A:A,1/MOD(LARGE(E$2:E$21+1/ROW($2:$21),ROW(1:1)),1))
=LOOKUP(1,0/(($F$2:$F$21=A27)*
(COUNTIF(D$26:D26,$A$2:$A$21)=0)),$A$2:$A$21)=INDIRECT("A"&RIGHT(LARGE(($E$2:$E$21*100+ROW($A$2:$A$21)),ROW(A1)),2)) =RANK(C2,OFFSET($C$1,MATCH(E2,$E$2:$E$768,),,COUNTIF($E$2:$E$768,E2)))
数组公式
｛=INDEX(A:A,MOD(LARGE(E$2:E$21*100+ROW($2:$21),ROW(1:1)),100))｝
｛=OFFSET($A$1,RIGHT(LARGE($E$2:$E$21*1000+ROW($E$2:$E$21),ROW()-25),3)-1,,)｝
=OFFSET($A$1,RIGHT(LARGE(($E$2:$E$21*100+ROW($A$1:$A$20)),ROW(A3)),2),)
=TEXT(SUMPRODUCT(($E$2:$E$21>=E2)/COUNTIF($E$2:$E$21,$E$2:$E$21)),"第[DBNUM1]G/通⽤格式名")
排序后排名
｛=SUM(IF($B$2:$B$15>=B2,1/COUNTIF($B$2:$B$15,$B$2:$B$15)))｝
=SUMPRODUCT((B$2:B$15>=B2)/COUNTIF(B$2:B$15,B$2:B$15))
位次排名
｛=IF($B2:$O2>=0,RANK($B2:$O2,$B2:$O2,0),)｝
根据双列成绩进⾏共同排名
=RANK(C345,($C$345:$C$356,$H$345:$H$356))
在双列间排名
=RANK(B2,($B$2:$B$26,$E$2:$E$16))
等次排名
由⼤到⼩排名
=RANK(B3,$B$3:$B$12)
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16))+1
由⼩到⼤排名
=RANK(B3,$B$3:$B$12,1)
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16))+1
不等次排名(⾏⼩排先）
由⼤到⼩
=RANK(B3,$B$3:$B$12)+COUNTIF($B$3:B3,B3)-1
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000>B16-ROW(B16)/10000))+1由⼩到⼤
=RANK(B3,$B$3:$B$12,1)+COUNTIF($B$3:B3,B3)-1
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000<B16+ROW(B16)/10000))+1不等次排名(⾏⼤排先）
由⼤到⼩
=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12,1)-COUNTIF($B$3:B3,B3)+2
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000>B16+ROW(B16)/1
0000))+1由⼩到⼤
=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12)-COUNTIF($B$3:B3,B3)+2
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000<B16-ROW(B16)/10000))+1顺次排名
由⼤到⼩
=SUMPRODUCT((B$3:B$12>B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1由⼩到⼤
=SUMPRODUCT((B$3:B$12<B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1
=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1
有并列排名
=RANK(B2,$B$2:$B$20)
=SUMPRODUCT(1*($B$3:$B$21>B3))+1
=COUNTIF($B$3:$B$21,">"&B3)+1
｛=SUM(IF($B$3:$B$21>B3,1,0))+1｝
=19-FREQUENCY($B$3:$B$21,B3)+1
=SUMPRODUCT(($B$2:$B$20>=B2)/COUNTIF($B$2:$B$20,$B$2:$B$20))
⽆并列排名
=RANK(B3,$B$3:$B$21)+COUNTIF($B$3:$B3,B3)-1
=SUMPRODUCT((B3-ROW()/1000<$B$3:$B$21-ROW($B$3:$B$21)/1000)*1)+1
=19-FREQUENCY($B$3:$B$21-ROW($B$3:$B$21)/1000,B3-ROW()/1000)+1
｛=SUM(IF($B$3:$B$21-ROW($B$3:$B$21)/1000>B3-ROW()/1000,1,0))+1｝
有并列分段排名
=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3))+1
=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21),C3)+1
｛=MATCH(C3,LARGE(OFFSET($C$2,IF($A$3:$A$21=A3,ROW($A$3:$A$21)-
2),),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)｝
｛=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)｝｛=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3)/COUNTIF($N$3:$N$21,$N$3:$N$21))+1｝（需辅助列）⽆并列分段排名
｛=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/10000>C3-ROW(C3)/10000))+1｝
=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/1000),C3-ROW()/1000)+1
成绩排名
序号姓名语⽂数学英语
1杨增海135136146
2郭爱玲138137141
3华志锋134138141
4袁⽂飞134143135
能否⽤⼀个公式直接出所⽤考⽣中语⽂成绩中第100名的成绩是多少?
=LARGE(C2:C417,100)
=PERCENTILE(C2:C417,(416-100)/416)
=PERCENTILE($C$2:$C$417,(COUNTA($C$2:$C$417)-100)/COUNTA($C$2:$C$417))
能否⽤⼀个公式直接出所⽤考⽣中语⽂成绩中按与考⼈数的35%切线中位于第35%的成绩是多少?
升冪
=SMALL(C2:C417,416*0.35)
=PERCENTILE($C$2:$C$417,0.35)
降冪
=LARGE(C2:C417,416*0.35)
=PERCENTILE($C$2:$C$417,1-0.35)
如何排名
1、对英语进⾏排名，缺考不计算在内。
2、对英语进⾏排名，缺考计算在内。
英语英语排名
429
623
721
485
485
721
544
429
缺考　
缺考　
458
467
缺考不计算在内
b2=IF(A2="缺考","",RANK(A2,$A$2:$A$13))然后按照B列排序
缺考计算在内
=IF(A2="缺考",COUNTIF($A$2:$A$13,">=0")+1,RANK(A2,$A$2:$A$13))
=IF(A2="缺考",COUNT($A$2:$A$13)+1,RANK(A2,$A$2:$A$13,0))
数据排名（隔⼏⾏排名）
=IF(A2="","",RANK(A2,$A$2:$A$11,0))
如果隔⼏⾏排名，如下表，第五⾏、第九⾏和第⼗⼆⾏不参与排名。
单位数据排名
A18
A57
A66
⼩计12
B84
B93
B75
⼩计24
C181
C112
excel利用rank排名次公式⼩计29
=IF(A2="⼩计","",RANK(B2,(B$2:B$4,B$6:B$8,B$10:B$11))) 下拉
根据分数进⾏倒排名
=RANK($E3,$E$3:$E$22,1)
=RANK(K60,$K$60:$K$83,1)
=COUNTIF($K$60:$K$83,"<"&K60)+1
倒数排名函数是什么
1为正排序，0为逆排序。
倒数排名=RANK(A2,$A$2:$A$5,0)
正数排名=RANK(A2,$A$2:$A$5,1)
如何实现每⽇各车间产量的排名
=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$1000,0)-1,,,))
=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$33,0)-1,,,))
分数相同时按照⼀科的分数进⾏排名
｛=MATCH(K308*100+D308,LARGE($K$308:$K$331*100+$D$308:$D$331,ROW($K$308:$K$331)-307),)｝
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
筛选后⾃动产⽣序列号并汇总
⾃动产⽣序列号：在A1输⼊以下公式，往下拖。
=SUBTOTAL(3,$B$2:B2)*1
⾃动汇总，⽤以下公式：
=SUBTOTAL(9,$B$2:B2)
说明：汇总时，不要在“全选”状态下进⾏，先“筛选”出某⼀单位，⾃动求和∑。然后再恢复到“全选”或者选择任何单位，就能⾃动汇总了（在“筛选”出某⼀单位进⾏求和时，⼀般表格会⾃动产⽣以上汇总公式）。
其它：如同时要在其它单元格显⽰⼈数，在“全选”状态下，选定单元格，点“fx”（⽤“sum”函数）再点击序列号最末尾数，即可。
如何筛选奇数⾏
公式=MOD(A1,2)=1
函数筛选姓名
如何把两列中只要包含A和A+的⼈员筛选出来
=IF(ISNUMBER(FIND("A",C2))+ISNUMBER(FIND("A",B2))>0,"OK","")
名次筛选
名次=RANK(K5,K$2:K$435)
班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,)-2,,COUNTIF(A$1:A$500,A6)))
如何实现快速定位（筛选出不重复值）
=IF(COUNTIF($A$2:A2,A2)=1,A2,"")
=IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,"")
=INDEX(A:A,SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW(A$1:A$20),65536),ROW()))&""（数组公式）如何请在N列中列出A1:L9中每列都存在的数值
｛=IF(ROW()>SUM(--x),"",INDEX(A:A,SMALL(IF(x,ROW($A$1:$A$9)),ROW())))｝
⾃动为性别编号的问题
有⼀个编码,5位，第1位,1为男,2为⼥，后⾯4位，代表他的编号，从0001-9999，如何达到下表：
性别编码
男    10001
男    10002
⼥    20001
男    10003
⼥    20002
男的也是从0001-9999
⼥的也是从0001-9999
如果你是已经输⼊了其它信息，仅仅为快速输⼊编码的话。⽤筛选可以实现吧。
先以“男”为关键字进⾏排序，然后在第⼀个男的编码输⼊10001，下拉复制到最后⼀单即可。同理再以“⼥”排序。完成⽬标。
⽤公式：=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。