楼主
liuguansky |
Q：如何生成限定上限与总和的随机数？
A：如下两个代码思路都可以。
- Sub test()
- Dim Arr, ArrD, Result, N&, I&, Dic As Object
- Arr = Range("b2:b" & Cells(Rows.Count, 1).End(3).Row - 1).Value
- ReDim Result(LBound(Arr) To UBound(Arr), 1 To 1)
- Set Dic = CreateObject("scripting.dictionary")
- For N = LBound(Arr) To UBound(Arr)
- Dic.Add CStr(N), Arr(N, 1)
- Next N
- Randomize
- For N = 1 To Cells(Rows.Count, 1).End(3).Offset(, 2).Value * 10 '每次随机分配1
- ArrD = Dic.keys
- I = Int(Rnd * Dic.Count)
- Result(Val(ArrD(I)), 1) = Result(Val(ArrD(I)), 1) + 0.1 '对随机下标数组值进行累加
- If Val(Result(Val(ArrD(I)), 1)) = Val(Dic(ArrD(I))) Then Dic.Remove ArrD(I) '如果达到上限，则清出字典项目
- Next N
- [c2].Resize(UBound(Result)) = Result
- Set Dic = Nothing
- End Sub
- Sub test2()
- Dim Arr, ArrD, Result, N&, I&, T#, Dic As Object
- Arr = Range("b2:b" & Cells(Rows.Count, 1).End(3).Row - 1).Value
- T = WorksheetFunction.Min(Arr)
- ReDim Result(LBound(Arr) To UBound(Arr), 1 To 1)
- For N = LBound(Result) To UBound(Result) '先赋值最小部分
- Result(N, 1) = Arr(N, 1) - T
- Next N
- Set Dic = CreateObject("scripting.dictionary")
- For N = LBound(Arr) To UBound(Arr)
- Dic.Add CStr(N), Arr(N, 1)
- Next N
- Randomize
- For N = 1 To (Cells(Rows.Count, 1).End(3).Offset(, 2).Value - T * UBound(Result)) * 10 '调整差异
- ArrD = Dic.keys
- I = Int(Rnd * Dic.Count)
- Result(Val(ArrD(I)), 1) = Result(Val(ArrD(I)), 1) + 0.1
- If Val(Result(Val(ArrD(I)), 1)) = Val(Dic(ArrD(I))) Then Dic.Remove ArrD(I)
- Next N
- [c2].Resize(UBound(Result)) = Result
- Set Dic = Nothing
- End Sub
 该帖已经同步到 |