【字符争霸赛第15期】求整数中绝对值第二大的数

Magician、小Y

1. =INDEX(B:B,MOD(LARGE(IF(FREQUENCY(B4:B14^2,B4:B14^2),ABS(B4:B14&ROW(904:914))),2),20))

复制代码

1. =INDEX(B:B,3+MOD(LARGE(IFERROR(MATCH(ROW(1:100)-1,ABS(B4:B14),)+ROW(1:100)/1%,),2),20))

复制代码

可以用高版本函数 加多个。。。不过公式还比原来的长1个字符

砂海

=INDEX(B$4:B$14,MID(MAX(IF(MOD(B$4:B$14,1)=0,ABS(B$4:B$14)+10+ROW(B$1:B$11)/99,-9)*(ABS(B$4:B$14)<>MAX(IF(MOD(B$4:B$14,1)=0,ABS(B$4:B$14),0)))),4,2))

lufton

我做的，第一个不对，尽力了。

滴答滴

1. =VLOOKUP(LARGE(ISNUMBER(MATCH(ROW(1:99)^2,B4:B14^2,))*ROW(1:99)^2,2),B4:B14^{2,1},2,)

复制代码

948621651

1. =OFFSET(B3,RIGHT(LARGE(IFERROR(ROW(A:A)/1%+MATCH(ROW(A:A),ABS(B4:B14),),),2),2),)

复制代码

daniel32

=INDEX(B4:B14,MATCH(LARGE(ABS(B4:B14*(MOD(B4:B14,1)=0)),SUM((ABS(B4:B14)=MAX(ABS(B4:B14*(MOD(B4:B14,1)=0))))*1)+1),ABS(B4:B14),))

AmoKat

{=LOOKUP(,0/(MAX(--TEXT((INT(B4:B14)=B4:B14)*B4:B14^2,"[<"&MAX((INT(B4:B14)=B4:B14)*B4:B14^2)&"];!0;!0;!0"))=B4:B14^2),B4:B14)}

xia125602

=INDEX(B:B,RIGHT(MIN(-TEXT(ABS(B4:B14&ROW(104:114)),"[>"&MAX(ABS(B4:B14&100))&"]!0")),2))
=INDEX(B:B,RIGHT(LARGE(IF(FREQUENCY(B4:B14^2,B4:B14^2),ABS(B4:B14&ROW(104:114))),2),2))
=INDEX(B:B,MOD(MAX(TEXT((B4:B14&100)^2,"[="&MAX((B4:B14&100)^2)&"]!0")+ROW(4:14)),100))

路边的记忆

混点金币

1. =OFFSET(B3,MATCH(LARGE(IF(ISERROR(MATCH(ROW(1:99),ABS(B4:B14),)),0,ROW(1:99)),2),ABS(B4:B14),),)

复制代码

huahua2005

用了IFERROR还89，惭愧~

1. =INDEX(B4:B14,RIGHT(LARGE(IFERROR(ROW(1:101)/1%+MATCH(ROW(1:101)-1,ABS(B4:B14),),),2),2))

复制代码