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

xiaoai007

1. =INDEX(A2:A25,RIGHT(SMALL(RIGHT(SMALL(RANDBETWEEN(ROW(1:24)^0,24)/1%+ROW(1:24)+(A2:A25="")/1%%,ROW(1:24)),3)+(ROW(1:24)>18)/1%%,COLUMN()*6+ROW()-25),2))

复制代码

kuangben8

重在参与一下

1. =INDEX(A:A,1+IFERROR(MOD(SMALL(RANDBETWEEN(1^ROW(1:18),9^9)/1%+ROW(1:18),ROW(1:6)+COLUMN(A:D)*6-6),100),RIGHT(SMALL(RANDBETWEEN(1^ROW(1:6),9^9)/1%+ROW(19:24),ROW(1:6)),2)))

复制代码

xlllw203

1. =LET(x,COUNTIF(A:A,">!")-1,WRAPCOLS(INDEX(A:A,SORTBY(SEQUENCE(x),RANDARRAY(x))+1),6,""))

复制代码

才看到，没时间了，先来个暴力365的答上

满坛皆为吾师

截止!已评已结,感谢小|树同学倾情奉献,有疑问请短消息我。

老版本109两条公式和365版本59字符公式相对可惜，此相应楼层无得分者额外+1技术分以示鼓励。

小|树

如有错漏，敬请指正！附件如下：

zpy2

create temp table aa as

select *,row_number() over (order by random()) 序 from 员工名单随机分组后进行排班 where rowid<19 order by random();
create temp table bb as
select '班次'||((序-1)/6+1)  班次,'星期'||((序-1)%6+1) 星期,员工名单 from aa;
//select rowid,* from bb where rowid<19;
create temp table cc as
select row_number() over () old_rowid,员工名单 `班次4` from 员工名单随机分组后进行排班 where rowid>=19;
//select * from cc;
cli_create_two_dim~bb~班次~员工名单;

select * from (select row_number() over (order by random()) old_rowid,* from bb_two_dim ) a left join cc on a.old_rowid=cc.old_rowid order by 星期;

E风卍行H

1. =T(OFFSET(A1,RIGHT(SMALL(RANDBETWEEN(ROW(2:25)^0,1000)*100+IF(A2:A25<>"",ROW(1:24),10^5+99),ROW(1:6)+COLUMN(A:D)*6-6),2),0))

复制代码

人间油物

奇怪了？这一期我竟然没看到，怎么没在公式板块置顶通知呢？