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

fjrlgj

194字符:
=INDEX(T(OFFSET(B$1,LARGE(ISNA(MATCH(A$2:A$31,IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31),))*ROW($1:$30),ROW($1:$30)),)),RANDBETWEEN(1,30-SUM(COUNTIF(A$2:A$31,IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31)))))
165字符:
=INDEX(B:B,SMALL(ISNA(MATCH(A$2:A$31,IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31),))*ROW($2:$31),RANDBETWEEN(SUM(COUNTIF(A:A,IF(COUNTIF(D$1:D1,B$2:B$31),A$2:A$31)))+1,30)))
159字符(删几个$符):
=INDEX(B:B,SMALL(ISNA(MATCH(A$2:A31,IF(COUNTIF(D$1:D1,B$2:B31),A$2:A31),))*ROW($2:31),RANDBETWEEN(SUM(COUNTIF(A:A,IF(COUNTIF(D$1:D1,B$2:B31),A$2:A31)))+1,30)))

[ 本帖最后由 fjrlgj 于 2011-7-29 13:36 编辑 ]

悟空师弟

157字符：
=INDEX(B:B,LARGE(ISNA(MATCH(A$2:A$32,IF(COUNTIF(D$1:D1,B$2:B32),A$2:A32),))*ROW($2:$32),RAND()*(30-SUM(COUNTIF(A:A,IF(COUNTIF(D$1:D1,B$2:B31),A$2:A31))))+1))

jianbing

先搞个 246个字符含等号
D2

1. =INDEX(B:B,LARGE((MMULT(TRANSPOSE(N(T(INDIRECT("A"&COUNTIF(D$1:D1,B$2:B$31)*ROW($1:$30)+1))=TRANSPOSE(A$2:A$31))),ROW($1:$30))=0)*ROW($2:$31),INT(1+RAND()*(30-SUM(N(T(INDIRECT("A"&COUNTIF(D$1:D1,B$2:B$31)*ROW($1:$30)+1))=TRANSPOSE(A$2:A$31)))))))

复制代码

第二关228字符，好险

1. =INDEX(B:B,LARGE((MMULT(TRANSPOSE(N(T(OFFSET(A$1,COUNTIF(D$1:D1,B$2:B$31)*ROW($1:$30),))=TRANSPOSE(A$2:A$31))),ROW($1:$30))=0)*ROW($2:$31),INT(1+RAND()*(30-COUNT(0/(T(OFFSET(A$1,MATCH(D$1:D1,B$2:B31,),))=TRANSPOSE(A$2:A31)))))))

复制代码

换一种思路246字符长度没什么进展

1. =INDEX(B:B,LARGE(IF(MATCH(A$1:$A$31,A$1:A$31,)=LARGE((MATCH(A$1:A$31,A$1:A$31,)=ROW($1:$31))*ROW($1:$31),ROW(A1)),ROW($1:$31)),INT(1+RAND()*COUNT(0/(MATCH(A$1:A$31,A$1:A$31,)=LARGE((MATCH(A$1:A$31,A$1:A$31,)=ROW($1:$31))*ROW($1:$31),ROW(A1)))))))

复制代码

[ 本帖最后由 jianbing 于 2011-7-27 02:14 编辑 ]

hopeson2010

=INDEX(B:B,MOD(SMALL(COLUMN(A:F)*10^6+IF(A2:A31=T(OFFSET(A1,LARGE((MATCH(A2:A31,A2:A31,)=ROW(1:30))*ROW(1:30),COLUMN(A:F)),)),MID(SUBSTITUTE(RAND()&RAND()&RAND(),"0.",),ROW(1:30),1)*10^4+ROW(2:31),99999),(ROW(1:6)-1)*30+1),10^4))

[ 本帖最后由 hopeson2010 于 2011-7-26 10:19 编辑 ]

wangyuhu

230字符，思路一：
=INDEX(B:B,SMALL(IF(A$2:A$31=INDEX(A:A,SMALL(IF(MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30),ROW($2:$31)),ROW(A1))),ROW($2:$31)),INT(RAND()*COUNTIF(A:A,INDEX(A:A,SMALL(IF(MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30),ROW($2:$31)),ROW(A1)))))+1))
简化为220字符
=INDEX(B:B,SMALL(IF(A$2:A$31=INDEX(A:A,SMALL(IF(MATCH(A$2:A$31,A:A,)=ROW($2:$31),ROW($2:$31)),ROW(A1))),ROW($2:$31)),INT(RAND()*COUNTIF(A:A,INDEX(A:A,SMALL(IF(MATCH(A$2:A$31,A:A,)=ROW($2:$31),ROW($2:$31)),ROW(A1)))))+1))
+++++++++
另外的方法出现的几率不相等，
195字符：（个人感觉 出错几率很小可以忽略不计）
=INDEX(B:B,RIGHT(MAX(((1+MID(SUBSTITUTE(RAND()&RAND()&RAND(),"0.",),ROW($1:$30),1))*(A$2:A$31=INDEX(A:A,SMALL(IF(MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30),ROW($2:$31)),ROW(A1)))))/1%+ROW($2:$31)),2))

[ 本帖最后由 wangyuhu 于 2011-8-10 11:59 编辑 ]

ZDH

1. =INDEX(B:B,SMALL(IF(A$2:A$31=INDEX(A:A,SMALL(IF(MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30),ROW($2:$31),32),ROW(A1))),ROW($2:$31),32),RANDBETWEEN(1,COUNTIF(A$2:A$31,INDEX(A:A,SMALL(IF(MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30),ROW($2:$31),32),ROW(A1)))))))

复制代码

wddn

公式一（230）：
D2=INDEX(B:B,LARGE((MATCH(A$2:A$31,A$2:A$31,)=LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($1:$30),ROW(A1)))*ROW($2:$31),INT(1+RAND()*COUNTIF(A:A,INDEX(A:A,LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($2:$31),ROW(A1)))))))
公式二（224）：
D2=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(1+RAND()*COUNTIF(A:A,INDEX(A:A,LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($2:$31),ROW(A1)))))))
公式三（197）：
D2=INDEX(B:B,RIGHT(LARGE(MATCH(A$2:A$31,A$2:A$31,)/1%+ROW($2:$31),1-TRUNC(SUM(-TEXT(MATCH(A$2:A$31,A$2:A$31,)-LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($1:$30),ROW(A1)),"1;!0;"&RAND())))),2))
公式四（172），公式的正确性有待审核：
D2=INDEX(B:B,RIGHT(LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($2:$31)*10^9+(A$2:A$31=TRANSPOSE(A$2:A$31))*(MID(COLUMN(B:AE)*RAND(),4,6)&COLUMN(CX:EA)),ROW()*30-59),2))
公式五（159），公式的正确性有待审核：
D2=INDEX(B:B,RIGHT(MAX(IF(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))),MID(ROW($2:$31)*RAND(),4,9)/1%+ROW($2:$31))),2))

[ 本帖最后由 wddn 于 2011-8-3 11:50 编辑 ]

草民1982111

字符数：228

1. =INDEX(B:B,LARGE((INDEX(A:A,LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($2:$31),ROW(A1)))=A$2:A$31)*ROW($2:$31),INT(RAND()*SUM(N(INDEX(A:A,LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($2:$31),ROW(A1)))=A$2:A$31))+1)))

复制代码

[ 本帖最后由 草民1982111 于 2011-7-30 11:31 编辑 ]

piny

D2

1. =INDEX(B:B,LARGE(IF(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)),RANDBETWEEN(1,COUNTIF(A:A,INDEX(A:A,LARGE((MATCH(A$2:A$31,A$2:A$31,)=ROW($1:$30))*ROW($2:$31),ROW(A1)))))))

复制代码

數組 下拉

fugb-2010

=INDEX(B:B,RIGHT(LARGE(COUNTIF(A$2:A$31,A$2:A$31)/1%%+MATCH(A$2:A$31,A:A,)/1%+ROW($2:$31),INT(RAND()*LARGE(IF(MATCH(A$2:A$31,A:A,)=ROW($2:$31),COUNTIF(A$2:A$31,A$2:A$31),0),ROW(1:1))+1)+SUM(LARGE(IF(MATCH(A$2:A$31,A:A,)=ROW($2:$31),COUNTIF(A$2:A$31,A$2:A$31),0),MOD(5+ROW($1:1),7)+1))),2))
数组公式