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

wcymiss

论坛升级，竞赛题出了点意外，提前开贴了，很遗憾。
预设公式：

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

复制代码

和大家的差不多。

wcymiss

整理了大家的回帖，并作了拟评分结果。有错误或意见请及时告知。

wcymiss

       基本思路都是先构造一个符合条件的人名的行号数组作为large的第一参数，在限定的范围内随机得出large的第二参数，最后用index取出数据。固定部门的思路较简单，但公式较长；而不固定部门的数据构造相对精巧些，公式也可简化得比较短。所以对不固定部门的公式进行了相应的加分。
       12楼的公式虽然比预设公式多了1个字符，但经简化后可达151字符。

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

复制代码

公式亮点为去掉了随机部分的int。large可以自动将第二参数的小数部分向上取整，但第二参数如果是小于1的数字large却会返回错误值。所以12楼的公式在去掉int的同时，将rand()结果+1，并将large第一参数的数组增加一个元素，match(a$2:a$32……*ROW($2:$32)。这个构造可谓别具匠心，所以评12楼公式为最佳。
       11楼的small的第二参数构造也比较巧妙。一般公式的均为large + [1，30-n]的形式，11楼则为small + [n+1,30]，这样的好处是small的第一参数可以再省两个美元。
      17楼的公式是固定部门思路的公式里的最短公式。text用得非常棒。
      部分公式在某些2003版本可能会测试错误。因为2003处理rand的能力实在是有限，当rand搭配过多的数组公式后，可能会因03的资源限制导致公式结果不正确。只要公式的原理能在2003版通过的，就视同公式本身在2003版本通过。
      2楼的最后一个公式其实也是非常精彩的。对randbetween的第二参数进行数组构造，产生30个10000以内的随机数，然后与行号合成后，用isna过滤掉不符合条件的合成数据，最后max+right取出符合条件的随机行号。思路很好啊。可惜2003的加载函数不能使用数组。{:soso_e151:}相信如果不限制版本的话，会有更多精彩公式呈现的。

wcymiss

本题原来是想要求随机从每部门抽取2人的公式。后因公式太长（我做了260-）而且思路不好，最终选择了求每部门抽取1人的公式。大家如果有兴趣，可以试试每部门抽取2人的。呵呵

CTFuture

31号结呢，怎么就结了，哎……{:soso_e127:}

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

复制代码

hzhb14796

公式真长啊，一会好好研究每个公式，学习学习

绿玉人

2003版本可以使用randbetween函数吗？

wcymiss

randbetween是2003的加载函数。可以用。见要求5。但是2003版本下，randbetween的两个参数不能是数组。

CTFuture

wcymiss 发表于 2011-8-16 13:24
randbetween是2003的加载函数。可以用。见要求5。但是2003版本下，randbetween的两个参数不能是数组。

我还以为不能选取整列呢，保存时总是提示2003或以下版本选取整列可能会出现错误，结帖早那么多，哎……
使用整列引用：

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

复制代码

fugb-2010

D2单元为公式下拉至D3单元，D3单元公式中引用D2单元数据，是否算使用辅助列？