如何在最小值和最大值（包括）之間生成一列數字，分布相等的次數？
例如，在 excel 的 600 個單元格中生成 1 到 5 之間的整數。該列將由 120 個 1、120 個 2....和 ??120 個 5 組成，以隨機順序排列。
任何指導表示贊賞！
uj5u.com熱心網友回復：
您可以構建所有數字的存盤桶（例如 1 到 5 每個 120 次），然后以隨機順序洗掉：
Sub test()
Dim c As Collection
Set c = GenerateBucket(1, 5, 120)
Dim row As Long
Dim index As Long
row = 1
' now draw with deletion in random order
Randomize
While c.Count > 0
' generate a number [1..c.count]
index = Int(Rnd() * c.Count 1)
Cells(row, 1).Value = c(index)
c.Remove index
row = row 1
Wend
End Sub
Private Function GenerateBucket(xmin As Long, xmax As Long, times As Long) As Collection
Dim i As Long, n As Long
Dim rv As New Collection
For i = xmin To xmax
For n = 1 To times
rv.Add i
Next
Next
Set GenerateBucket = rv
End Function
uj5u.com熱心網友回復：
如果您可以訪問 Excel 365 中更新的功能，您也可以這樣做：
=LET(min,1,
max,5,
times,120,
range,max-min 1,
total,range*times,
colArr,SEQUENCE(range,1,min),
rowArr,SEQUENCE(1,times,1,0),
rectArr,colArr*rowArr,
finalArr,TOCOL(rectArr),
SORTBY(finalArr,RANDARRAY(total)))
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/486427.html