英文:
I have a Google Sheet with 1000 rows, each is a different company. I would like to assign them randomly into 5 equal groups. How can I do it?
问题
我有一个包含1000行的Google表格，每一行都是不同的公司。我想将它们随机分成5个相等的组。我应该如何做？
我尝试在一个列中随机生成从1到5的数字，但问题是每个组包含不同数量的行。
英文:
I have a Google Sheet with 1000 rows, each is a different company. I would like to assign them randomly into 5 equal groups. How can I do it?
I have tried to randomize a number from 1 to 5 in one column, but the problem is that wach group contains a different amount of rows.
答案1
得分: 1
=let(
names,tocol(A1:A,1),
groups,5,
stableRandomSortOrder,lambda(x,x)(randarray(counta(names))),
wraprows(sort(names,stableRandomSortOrder,1),groups,))
英文:
Also possible to achieve the same thing for any number of company names in A1:A and any number of required groups without the MAP or SEQUENCE :
=let(
names,tocol(A1:A,1),
groups,5,
stableRandomSortOrder,lambda(x,x)(randarray(counta(names))),
wraprows(sort(names,stableRandomSortOrder,1),groups,))
</details>
# 答案2
**得分**: 0
***更新后的公式:***
```excel
=let(grp,3,len,counta(A:A),Δ,(grp*ceiling(len/grp)),sort(query(tocol(map(sequence(grp),lambda(Σ,wraprows(Σ,Δ/grp,Σ)))),"offset "&Δ-len),lambda(Σ,Σ)(randarray(len,1)),1))
你可以尝试:
=sort(tocol(map(sequence(5),lambda(Σ,wraprows(Σ,200,Σ)))),lambda(Σ,Σ)(randarray(1000,1)),1)
英文:
Updated formula:
=let(grp,3,len,counta(A:A),Δ,(grp*ceiling(len/grp)),sort(query(tocol(map(sequence(grp),lambda(Σ,wraprows(Σ,Δ/grp,Σ)))),"offset "&Δ-len),lambda(Σ,Σ)(randarray(len,1)),1))
Can you try:
=sort(tocol(map(sequence(5),lambda(Σ,wraprows(Σ,200,Σ)))),lambda(Σ,Σ)(randarray(1000,1)),1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论