[开_158]随机排班（已总结）

℡Soul

1. =LET(fx,LAMBDA(x,y,SORTBY(x,RANDARRAY(y))),HSTACK(WRAPROWS(fx(A2:A19,18),3),fx(A20:A25,6)))

复制代码

楼兰城主TT

=LET(a,SORTBY(A2:A25,RANDARRAY(24)),SORT(WRAPCOLS(FILTER(a,a>""),6,"")))

补充内容 (2023-4-7 21:18):
最早的公式不完全随机，优化365公式如下
=SORTBY(WRAPCOLS(SORTBY(A2:A25,RANDARRAY(A2:A25>"")),6),RANDARRAY(6))

补充内容 (2023-4-7 21:19):
区域数组
=INDEX(A2:A25,MOD(SMALL(SMALL((A2:A25>"")*RANDBETWEEN(ROW(1:24)%,99)/1%+ROW(1:24),ROW(1:24))+RANDBETWEEN(ROW(1:24)%,9)*(ROW(1:24)<7)/1%%,ROW(1:6)+{0,1,2,3}*6),50))

静听溪流

首先来个109字符，有点小瑕疵的公式

1. =INDEX(A2:A25,MOD(SMALL(RANDBETWEEN(9^(A2:A25=""),9)/1%+ROW(1:24),MOD(ROW()-RIGHT(NOW()),6)+{1,7,13,19}),25))

复制代码

169字符：

1. =INDEX(A2:A25,MOD(SMALL((MOD(SMALL(RIGHT(NOW())+RANDBETWEEN(9^(A2:A25=""),9)*6+ROW(1:24),ROW(1:24)),6)&RANDBETWEEN(9^(A2:A25=""),9))/1%+ROW(1:24),ROW()*4-{7,6,5,4}),25))

复制代码

143字符

1. =INDEX(A2:A25,MOD(SMALL((ROW(1:24)>18)/1%%+RIGHT(LARGE(-(N(A2:A25="")&RIGHT(ROW(4:27)/NOW())&ROW(26:49)),ROW(1:24)),3),ROW()+{-1,5,11,17}),25))

复制代码

132字符：

1. =INDEX(A2:A25,MOD(SMALL(RANDBETWEEN(9^(A2:A25=""),9)+ROW(1:24)%,MOD(SMALL(RIGHT(NOW()/ROW())*6+ROW(),ROW()-1),6)+{1,7,13,19}),1)/1%)

复制代码

132字符：

1. =INDEX(A2:A25,MOD(SMALL(RANDBETWEEN(9^(A2:A25=""),9)*25+ROW(1:24),MOD(SMALL(RIGHT(NOW()/ROW())*6+ROW(),ROW()-1),6)+{1,7,13,19}),25))

复制代码

绊成ρìи

=INDEX(A:A,MOD(SMALL(SMALL(IFNA(RANDBETWEEN(X2:X25+1,9)/1%+ROW(INDIRECT("2:"&SUM(N(A:A<>"")))),999),ROW(1:6)+{0,6,12,18})+RANDBETWEEN(W2:W7+1,9)/1%+{1,2,3,4}/1%%,ROW(1:6)+{0,6,12,18}),100))

小書生

区域数组，选中 答案区域 按三键，注意手法！！

1. =INDEX(A2:A25,MOD(SMALL(RANDBETWEEN(9^(A2:A25=""),9)+ROW(1:24)%,RIGHT(SMALL(RIGHT(ROW()*NOW())+ROW()%,ROW()-1))+{-1,5,11,17}),1)/1%)

复制代码

区域数组，选中 答案区域 按三键，注意手法！！

1. =INDEX(A2:A25,MOD(SMALL(RANDBETWEEN(9^(A2:A25=""),9)+ROW(1:24)%,MOD(ROW()-RIGHT(NOW()),6)+{1,7,13,19}),1)/1%)

复制代码

365函数写一条

1. =SORTBY(WRAPCOLS(SORTBY(A2:A25,RANDARRAY(24)/(A2:A25>"")),6),RANDARRAY(6))

复制代码

绊成ρìи

绊成ρìи 发表于 2023-3-11 14:44
=INDEX(A:A,MOD(SMALL(SMALL(IFNA(RANDBETWEEN(X2:X25+1,9)/1%+ROW(INDIRECT("2:"&SUM(N(A:A"")))),999),RO ...

=INDEX(A:A,MOD(SMALL(IFNA(RANDBETWEEN(X2:X25+1,9)/1%+ROW(INDIRECT("2:"&SUM(N(A:A<>"")))),999),MOD(SMALL(RANDBETWEEN(W2:W7+1,9)*99+ROW(1:6),ROW(1:6)),99)+{0,6,12,18}),100))

sayhi95

重在参与

1. =SORTBY(WRAPCOLS(SORTBY(A2:A25,RANDARRAY(24)+(A2:A25="")),6),RANDARRAY(6))

复制代码

2023-03-18 18:32再交个低版本

1. =INDEX(A2:A25,MOD(SMALL((RANDBETWEEN(ROW(1:24)/9,9)+9^(A2:A25=""))/1%+ROW(1:24),{-1,5,11,17}+RIGHT(SMALL(RANDBETWEEN(ROW(1:6),9)/1%+ROW(),ROW(1:6)),2)),100))

复制代码

再砍几个，太艰难了

1. =INDEX(A2:A25,RIGHT(LARGE((LEN(A2:A25)&RANDBETWEEN(N(+A2:A25),9))/1%+ROW(1:24),{-1,5,11,17}+RIGHT(LARGE(-(RANDBETWEEN(ROW()%,9)&ROW()),ROW()-1))),2))

复制代码

这个在行位置随机上不知道会不会差点，看起来好像还行

1. =INDEX(A2:A25,RIGHT(LARGE((LEN(A2:A25)&RANDBETWEEN(N(+A2:A25),9))/1%+ROW(1:24),{-1,5,11,17}+RIGHT(LARGE(-(RANDBETWEEN(ROW(),9)&ROW()),ROW()-1))),2))

复制代码

江山社稷

1. =INDEX(A2:A25,MOD(SMALL(RANDBETWEEN(ROW(1:24)^0,99)^(ROW(2:25)>19)/1%+SMALL(RANDBETWEEN(99^(A2:A25=""),99)+ROW(1:24)%,ROW(1:24)),COLUMN()*6-ROW()-16),1)/1%)

复制代码

2

1. =INDEX(A2:A25,MOD(SMALL(RANDBETWEEN(1,9^(ROW(1:24)>18))/1%+SMALL(RANDBETWEEN(25^(A2:A25=""),25)+ROW(1:24)%,ROW(1:24)),COLUMN()*6-ROW()-16),1)/1%)

复制代码

太阳之子

1. =INDEX($A:$A,MOD(LARGE(IF((COUNTIF($C2:C9,$A2:$A25)=0)+(COLUMN()=7)*($A2:$A25=""),RANDBETWEEN(ROW(1:24),32))/1%+ROW(2:25),ROW()-1),50))

复制代码

1. =INDEX($A:$A,DAY(LARGE(--IF((COUNTIF($C:C,$A2:$A25)=0)+(COLUMN()=7)*($A2:$A25=""),RANDARRAY(24,,1,9,1)&-ROW(2:25)),ROW()-1)))

复制代码

列数组，横拉.  第二个是365版。

良良

D2

1. =IFERROR(INDEX($A:$A,SMALL(IF(COUNTIF($C$2:C$7,OFFSET($A$2,,,25-$B$1))+COUNTIF(D$1:D1,OFFSET($A$2,,,25-$B$1))=0,ROW(INDIRECT("1:"&25-$B$1))),RANDBETWEEN(1,32-$B$1+(COLUMN(A1)=4)*($B$1-1)-6*COLUMN(A1)-ROW(A1)))+1),"")

复制代码

，数组三键，下拉、右拉。