excel最常用函数公式技巧(4)

W(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)/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$25-ROW($B$16:$B$25)/10000

顺次排名

由大到小

=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

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25

有并列排名

=RANK(B2,$B$2:$B$20)

=SUMPRODUCT(1*($B$3:$B$21>B3))+1 =COUNTIF($B$3:$B$21,\｛=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(\｝

｛=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT(\IF($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$

第 16 页

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 2 3 杨增海 郭爱玲 华志锋 135 138 134 136 137 138 146 141 141 袁文飞 4 134 143 135 能否用一个公式直接找出所用考生中语文成绩中第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、对英语进行排名，缺考计算在内。 英语 英语排名 42 9 62 3 72 1 48 5 48 5 72 1 54 4 42 9 缺考 缺考 45 8 46 7

第 17 页

缺考不计算在内

b2=IF(A2=\缺考\ 然后按照B列排序 缺考计算在内

=IF(A2=\缺考\=IF(A2=\缺考\

数据排名（隔几行排名）

=IF(A2=\

如果隔几行排名，如下表，第五行、第九行和第十二行不参与排名。 单位 数据 排名 A 1 8 A 5 7 A 6 6 小计 12 B 8 4 B 9 3 B 7 5 小计 24 C 18 1 C 11 2 小计 29

=IF(A2=\小计\ 下拉

根据分数进行倒排名

=RANK($E3,$E$3:$E$22,1) =RANK(K60,$K$60:$K$83,1)

=COUNTIF($K$60:$K$83,\

倒数排名函数是什么

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输入以下公式，往下拖。

第 18 页

=SUBTOTAL(3,$B$2:B2)*1

自动汇总，用以下公式： =SUBTOTAL(9,$B$2:B2)

说明：汇总时，不要在“全选”状态下进行，先“筛选”出某一单位，自动求和∑。然后再恢复到“全选”或者选择任何单位，就能自动汇总了（在“筛选”出某一单位进行求和时，一般表格会自动产生以上汇总公式）。

其它：如同时要在其它单元格显示人数，在“全选”状态下，选定单元格，点“fx”（用“sum”函数）再点击序列号最末尾数，即可。

如何筛选奇数行

公式=MOD(A1,2)=1

函数筛选姓名

如何把两列中只要包含A和A+的人员筛选出来

=IF(ISNUMBER(FIND(\

名次筛选

名次=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),\｝

自动为性别编号的问题

有一个编码,5位，第1位,1为男,2为女，后面4位,代表他的编号,从0001-9999，如何达到下表：

性别 编码 男 10001 男 10002 女 20001 男 10003 女 20002 男的也是从0001-9999 女的也是从0001-9999

如果你是已经输入了其它信息，仅仅为快速输入编码的话。用筛选可以实现吧。

先以“男”为关键字进行排序，然后在第一个男的编码输入10001，下拉复制到最后一单即可。同理再以“女”排序。完成目标。

用公式：

第 19 页

=IF(A2=\向下拖

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

第 20 页