excel最常用函数公式技巧搜集大全更新(编辑修改稿)

excel最常用函数公式技巧搜集大全更新(编辑修改稿)内容摘要：

技术员 6 赵五 A1 单元格输入公式 =row(),往下拉 ,然后再插入。 =SUBTOTAL(3,$B$2:$B2) 在 A1 中输入公式： “ =if(b1=,counta($b$1:b1)” 后下拉复制至 A列各行即可（ “”不必输入） 根据规律的重复的姓名列产生自动序号 姓名 序号 张三 1 张三 1 李四 2 李四 2 赵五 3 赵五 3 赵五 3 王六 4 王六 4 =(A1A2)+N(B1) =IF(A3=A2,B2,B2+1) 姓名已排序： B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)) 姓名未排序： B2=IF(COUNTIF(A$2:A2,A2)1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2))) ～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～ 排名的函数 用排名函数来对成绩进行排名 ， 用起来非常地方便。 =IF(ISERR(RANK(M3,M:M)),RANK(M3,M:M)) A 列是成绩 ,B 列是排名 =SUMPRODUCT((A$1:A$9A1)/COUNTIF(A$1:A$9,A$1:A$9))+1 自动排名公式 =RANK(C3,$C$3:$C$12) 11 =RANK(A2,$A$2:$A$11,0) =RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)1 百分比排名的公式写法为： =PERCENTRANK($C$3:$C$12,C3) 平均分 及总分 排名 =AVERAGE(B2:E2) =RANK(F2,$F$2:$F$65536) 求名次排名 统计成绩时遇到一个分别求班级和年级总分名次排名的问题，不晓得应该运用什么公式来实现。 班级名次： =SUMPRODUCT((BJ=A2)*(ZFE2))+1 年级名次： =RANK(E2,ZF) 公式下拖。 排名 次 根据总分值大小，只将姓名排序后 , 降序结果 =INDEX(A$2:A$6,RANK(D2,D$2:D$6)) 根据总分值大小，只将姓名排序后 , 升序 =INDEX(A$2:A$6,RANK(D2,D$2:D$6,1)) 根据分数进行普通排名 =RANK(A2,$A$2:$A$12) =RANK(A2,A$2:A$12)+COUNTIF(A$2:A2,A2)1 =SUMPRODUCT(1*($E$3:$E$12=E3)) =RANK(K3,$K$3:$K$26) =RANK(A2,A$2:A$12) =SUM((A$2:A$12=A2)/COUNTIF(A$2:A$12,A$2:A$12)) =COUNTIF($K$3:$K$26,amp。 K3)+1 =INDEX($A$2:$A$7,MATCH(LARGE($C$2:$C$7,ROW(A1)),$C$2:$C$7,0),1) =SUMPRODUCT(($A$2:$A$12A2)/COUNTIF($A$2:$A$12,$A$2:$A$12amp。 ))+1 =RANK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)1,3,COUNTIF($A:$A,$A2),1)) 对于普通排名分数相同时，按顺序进行不重复排名 =RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)1 =COUNTIF($K$32:K32,K32)1+COUNTIF($K$3:$K$26,amp。 K32)+1 =SUMPRODUCT(1*(($E$3:$E$12+ROW($E$3:$E$12)/100=($E3+ROW(E3)/100)))) =RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)1 =SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100)=(E3+B3/100))) 依分数比高低名次成绩排名 12 =RANK($E3,$E$3:$E$22) 內建方式排名 =SUMPRODUCT(1*($E$3:$E$12=E3)) 一般方式排名 ｛ =RANK(E3,$E$3:$E$22)+SUM(IF($E$3:$E$22E3,1/COUNTIF($E$3:$E$22,$E$3:$E$22),0))COUNTIF($E$3:$E$22,amp。 E3)｝ 一般方式排名 =RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)1 不重复排名 =SUMPRODUCT(1*(($E$3:$E$12+ROW($E$3:$E$12)/100=($E3+ROW(E3)/100)))) =SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100)=(E3+B3/100))) 不重复排名 =SUMPRODUCT(1*(($E$3:$E$12+$B$3:$B$12/100+$C$3:$C$12/10000)=(E3+B3/100+C3/10000))) 不重复排名 =RANK($E3,$E$3:$E$22,1) 倒排序 美国式排名 =RANK(K247,$K$247:$K$270) =RANK(B1,$B1:$H1) 中国式排名 =RANK(B2,$B$2:$B$21,0) =RANK(B1,$B1:$H1)+COUNTIF($B$1:B1,B1)1 =SUM(IF($A$1:$E$1=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),)) =SUMPRODUCT(($B$2:$B$21=B2)/COUNTIF($B$2:B$21,B$2:B$21)) =SUMPRODUCT((B$3:B$21B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)))+1 （升序） =SUMPRODUCT((B$3:B$21B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)))+1 （降序） ｛ =SUM((IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21B2)))+1｝ ｛ =SUM(IF($B$3:$B$21=B3,1/(COUNTIF($B$3:B$21,B$3:B$21))))+1｝ （升序） ｛ =SUM(IF($B$3:$B$21=B3,1/(COUNTIF($B$3:B$21,B$3:B$21)),))｝ （降序） ｛ =SUM(IF($B$2:$B$21B2,1/COUNTIF($B$2:B$21,B$2:B$21)))+1｝ ｛ =SUM(IF($A$1:$E$1=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),))｝ ｛ =SUM(($B$2:$B$21B2)*(MATCH($B$2:B$21,B$2:B$21,)=ROW($1:$20)))+1｝ ｛ =SUM(IF($B$1:$H$1=B1,1/(COUNTIF($B$1:$H$1,$B$1:$H$1))))+1｝ 求最精简的自动排名公式 =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(Aamp。 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)),第 13 [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$25B16))+1 由小到大排名 =RANK(B3,$B$3:$B$12,1) =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25B16))+1 不等次排名 (行小排先） 由大到小 =RANK(B3,$B$3:$B$12)+COUNTIF($B$3:B3,B3)1 =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25ROW($B$16:$B$25)/10000B16ROW(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)/10000B16+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)/10000B16+ROW(B16)/10000))+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$25ROW($B$16:$B$25)/10000B16ROW(B16)/10000))+1 顺次排名 14 由大到小 =SUMPRODUCT((B$3:B$12B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1 =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1 由小到大 =SUMPRODUCT((B$3:B$12B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1 =SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1 有 并列排名 =RANK(B2,$B$2:$B$20) =SUMPRODUCT(1*($B$3:$B$21B3))+1 =COUNTIF($B$3:$B$21,amp。 B3)+1 ｛ =SUM(IF($B$3:$B$21B3,1,0))+1｝ =19FREQUENCY($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((B3ROW()/1000$B$3:$B$21ROW($B$3:$B$21)/1000)*1)+1 =19FREQUENCY($B$3:$B$21ROW($B$3:$B$21)/1000,B3ROW()/1000)+1 ｛ =SUM(IF($B$3:$B$21ROW($B$3:。