[已解决]数组小练习--中式排名（已经开帖）

顺⑦.zì繎。

要求已经在附件中。。。答对者 10BB...
字符最短为最佳...  开帖时间待定。。。
有兴趣的童鞋可以挑战内存数组。。。额外10BB

最佳答案

月排行榜 / 总排行榜

as0810114

2013-5-15 21:40

总算是弄出来了，加经验可以不，顺委。
=SUM(--IF(B$2:B$14&C$2:C$14>=B2&C2,MATCH(B$2:B$14&C$2:C$14,B$2:B$14&C$2:C$14,)=ROW($2:$14)-1))     93字符
数组公式。
60字符  =SUM(--IF(B:B&C:C>B2&C2,MATCH(B:B&C:C,B:B&C:C,)=ROW($1:$14)))
很卡的喔。

跳转到最佳答案楼层

笨笨四

SUM(((B$2:B$14+C$2:C$14%)>(B2+C2%))*(MATCH(B$2:B$14&C$2:C$14%,B$2:B$14&C$2:C$14%,)=ROW($1:$13)))+1

先交一个数组的。。

SUM((B$2:B14&C$2:C14>=B2&C2)*(MATCH(B$2:B14&C$2:C14,B$2:B14&C$2:C14,)=ROW($1:13)))
省了几个字符。
另一个方法：
SUM(N(IF(FREQUENCY(B$2:B19+C$2:C19%,B$2:B19+C$2:C19%),B$2:B19+C$2:C19%>B10+C10%)))+1


MMULT(N((B2:B14+C2:C14%)<TRANSPOSE(B2:B14+C2:C14%)),N(MATCH(B2:B14&C2:C14%,B2:B14&C2:C14%,)=ROW(1:13)))+1

内存数组。

cg372101

拼凑个数组公式：

1. =SUM(--(FREQUENCY(B$2:B$14*100+C$2:C$14,IF(B$2:B$14*100+C$2:C$14>=B2*100+C2,B$2:B$14*100+C$2:C$14))>0))

复制代码

yunqingzi

新增一列辅助列：D2{=MMULT($B$2:$C$14,{100;1})}
然后在E列输入公式E2=SUMPRODUCT((D$2:D$14>=D2)*(1/COUNTIF(D$2:D$14,D$2:D$14)))

opqazxc

含等号70

1. =SUM((B$2:B$14+C$2:C$14%>=B2+C2%)/COUNTIFS(B:B,B$2:B$14,C:C,C$2:C$14))

复制代码

内存数组

1. =MMULT(1/TRANSPOSE(COUNTIFS(B:B,B2:B14,C:C,C2:C14)),N(B2:B14+C2:C14%>=TRANSPOSE(B2:B14+C2:C14%)))

复制代码

1. =MMULT(H1:T1+1,(B2:B14+C2:C14%>=TRANSPOSE(B2:B14+C2:C14%))/COUNTIFS(B:B,B2:B14,C:C,C2:C14))

复制代码

``

402031036

SUM(IF(B$2:B$14>B3,1,(B$2:B$14=B3)*(C$2:C$14>=C3))/COUNTIFS(B$2:B$14,B$2:B$14,C$2:C$14,C$2:C$14))

1032446692

1. SUM(((B$2:B$14>B2)+(B$2:B$14=B2)*(C$2:C$14>=C2))/COUNTIFS(B$2:B$14,B$2:B$14,C$2:C$14,C$2:C$14))

复制代码

上面那个95   引用整列不至于卡的85

1. SUM(((B$2:B$14>B2)+(B$2:B$14=B2)*(C$2:C$14>=C2))/COUNTIFS(B:B,B$2:B$14,C:C,C$2:C$14))

复制代码

比较取巧的一个  换些数据不一定就适合了(75)

1. SUM(((B$2:B$14&C$2:C$14)*1>=(B2&C2)*1)/COUNTIFS(B:B,B$2:B$14,C:C,C$2:C$14))

复制代码

lyf7276

=COUNT(1/(((B$2:B$14+C$2:C$14*0.1)>=(B2+C2*0.1))*(MATCH((B$2:B$14+C$2:C$14*0.1),(B$2:B$14+C$2:C$14*0.1),)=ROW($1:$14))))三键结束。

lucking551

数组公式：

1. =SUM(--IF($B$2:$B$14+$C$2:$C$14*0.1>=B2+C2*0.1,MATCH($B$2:$B$14+$C$2:$C$14*0.1,$B$2:$B$14+$C$2:$C$14*0.1,)=ROW($2:$14)-1))

复制代码

zx_wl

公式下拉：

1. {=COUNT(0/(MATCH((B$2:B$14*10+C$2:C$14>=B2*10+C2)*(B$2:B$14*10+C$2:C$14),B$2:B$14*10+C$2:C$14,)=ROW($1:$13)))}
   

复制代码

多单元格数组公式：

1. ｛=MATCH(B$2:B$14*10+C$2:C$14,LARGE((B$2:B$14*10+C$2:C$14)*(MATCH(B$2:B$14*10+C$2:C$14,B$2:B$14*10+C$2:C$14,)=ROW($1:$13)),ROW($1:$13)),)｝

复制代码