[第79期]按部门随机选取啦啦队队员(已总结)

wcymiss

答题截止时间：2011-8-31 24:00

请一人占一楼，谢谢。



[ 本帖最后由 wcymiss 于 2011-7-21 16:43 编辑 ]

chenhh803

不会是沙发还是我的,
169字符:纯2003版可以通过
=INDEX(B:B,LARGE(ISNA(MATCH(A$2:A$31,T(OFFSET(A$1,MATCH(D$1:D1,B$2:B$31,),)),))*ROW($2:$31),RANDBETWEEN(1,30-SUM(COUNTIF(A:A,T(OFFSET(A$1,MATCH(D$1:D1,B$2:B$31,),)))))))
164字符,字符是少了,但效率低了,不可取:
=INDEX(B:B,LARGE(ISNA(MATCH(A$2:A$31,T(OFFSET(A$1,MATCH(D$1:D1,B:B,)-1,)),))*ROW($2:$31),RANDBETWEEN(1,30-COUNT(MATCH(A:A,T(OFFSET(A$1,MATCH(D$1:D1,B:B,)-1,)),)))))
162字符
=INDEX(B:B,LARGE(ISNA(MATCH(A$2:A$31,T(INDIRECT("a"&MATCH(D$1:D1,B:B,))),))*ROW($2:$31),RANDBETWEEN(1,30-COUNT(MATCH(A:A,T(INDIRECT("a"&MATCH(D$1:D1,B:B,))),)))))
137字符,在2007兼容模式下可运行
=INDEX(B:B,RIGHT(MAX(ISNA(MATCH(A$2:A$31,T(OFFSET(A$1,MATCH(D$1:D1,B$2:B$31,),)),))*(RANDBETWEEN(1,ROW(2:31)^0/1%%)&ROW($2:$31)%)),3)/1%)

[ 本帖最后由 chenhh803 于 2011-8-8 13:17 编辑 ]

angellbxsc

先写一个答案吧，但离170字符还很遥远。D2输入公式下拉

1. =INDEX(B:B,LARGE((1-MMULT(N(TRANSPOSE(IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31))=A$2:A$31),ROW(1:30)^0))*ROW($2:$31),RANDBETWEEN(1,SUM(1-MMULT(--(TRANSPOSE(IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31))=A$2:A$31),ROW(1:30)^0)))))

复制代码

共218字符

进化下199字符

1. =INDEX(B:B,LARGE((1-MMULT(N(TRANSPOSE(IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31))=A$2:A$31),ROW(1:30)^0))*ROW($2:$31),RANDBETWEEN(1,30-SUM(1*(TRANSPOSE(IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31))=A$2:A$31)))))

复制代码

接近真相了184字符

1. =INDEX(B:B,LARGE((1-MMULT(N(TRANSPOSE(IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31))=A$2:A$31),ROW(1:30)^0))*ROW($2:$31),RANDBETWEEN(1,30-SUM(COUNTIF(D$1:D1,B$2:B$31)*COUNTIF(A:A,A$2:A$31)))))

复制代码

闯过170大关，共计161字符

1. =INDEX(B:B,LARGE(ISNA(MATCH(A$2:A$31,IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31),))*ROW($2:$31),RANDBETWEEN(1,30-SUM(COUNTIF(D$1:D1,B$2:B$31)*COUNTIF(A:A,A$2:A$31)))))

复制代码

[ 本帖最后由 angellbxsc 于 2011-7-25 15:08 编辑 ]

kakaco

LZ 是杨阳？。。

bluexuemei

先占个座，，，，，

jxrbz

=INDEX(B$2:B$31,LARGE(ROW($1:$30)*(A$2:A$31=INDEX(A$2:A$31,LARGE(((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30)))*ROW($1:$30),ROW(1:1)))),1+MOD(INT(RAND()*1000),COUNTIF(A$2:A$31,INDEX(A$2:A$31,LARGE(((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30)))*ROW($1:$30),ROW(1:1)))))))
=====================
260字符

羽希

总算凑合了一个 最近好忙思路糨了
=INDEX(B:B,LARGE(IF(MATCH(A$2:A$31,A:A,)=LARGE(IF(MATCH(A$2:A$31,A:A,)=ROW($2:$31),ROW($2:$31)),ROW(A1)),ROW($2:$31)),RANDBETWEEN(1,COUNTIF(A:A,INDEX(A:A,LARGE(IF(MATCH(A$2:A$31,A:A,)=ROW($2:$31),ROW($2:$31)),ROW(A1)))))))

[ 本帖最后由 羽希 于 2011-8-9 15:25 编辑 ]

oyzhjr

=OFFSET(B$1,LARGE(IF(ISERROR(MATCH(A$2:A$31,IF(ISERROR(MATCH(B$2:B$31,D$1:D1,)),"",A$2:A$31),)),ROW(B$1:B$30),0),INT((30-COUNT(MATCH(A$2:A$31,IF(ISERROR(MATCH(B$2:B$31,D$1:D1,)),"",A$2:A$31),)))*RAND()+1)),)
207字符。

chunlin1wang

222字符

1. =INDEX(B:B,SMALL(IF(MMULT(1*(A$2:A$31=TRANSPOSE(IF(MMULT(1*(B$2:B$31=TRANSPOSE(D$1:D1)),ROW($1:1)),A$2:A$31))),ROW($1:$30)),32,ROW($2:$31)),RANDBETWEEN(1,30-SUM(COUNTIF(A$2:A$31,IF(B$2:B$31=TRANSPOSE(D$1:D1),A$2:A$31))))))

复制代码

同样的思路精简一下197字符

1. =INDEX(B:B,SMALL(IF(MMULT(1*(A$2:A$31=TRANSPOSE(IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31))),ROW($1:$30)),32,ROW($2:$31)),RANDBETWEEN(1,30-SUM(COUNTIF(A$2:A$31,IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31))))))

复制代码

要字符少于170可能需要换思路了，再想想。
不喜欢用ISNA()，没办法只有用一次，166字符

1. =INDEX(B:B,LARGE(ISNA(MATCH(A$2:A$31,IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31),))*ROW($2:$31),RANDBETWEEN(1,30-SUM(COUNTIF(D$1:D1,B$2:B$31)*COUNTIF(A$2:A$31,A$2:A$31)))))

复制代码

再减几个$,161字符

1. =INDEX(B:B,LARGE(ISNA(MATCH(A$2:A$31,IF(COUNTIF(D$1:D1,B$2:B31),A$2:A31),))*ROW($2:$31),RANDBETWEEN(1,30-SUM(COUNTIF(D$1:D1,B$2:B31)*COUNTIF(A$2:A31,A$2:A31)))))

复制代码

[ 本帖最后由 chunlin1wang 于 2011-7-30 09:55 编辑 ]

一嘟噜钥匙

=INDEX(B:B,LARGE((A$2:A$31=INDEX(A:A,LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($2:$31),ROW(A1))))*ROW($2:$31),-INT(SUM(-(A$2:A$31=INDEX(A:A,LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($2:$31),ROW(A1)))))*RAND())))
227字符 。第一次参与，期待高手精彩思路