|
- Sub tt()
- Randomize
- arr = Range("h1:i" & [h65536].End(3).Row)
- ReDim brr(1 To UBound(arr)) '记录每组需填的空格位置
- s = 2: x = arr(s, 1) - arr(s, 2) '起始行
- n = IIf(arr(s, 2) > 0, 0, 1) '每组的空格数
- If n = 1 Then brr(1) = s
- For i = 3 To UBound(arr) '往下
- If Len(arr(i, 1)) = 0 Then '表示在本组内
- If arr(i, 2) = 0 Then '右列无值,记录位置
- n = n + 1
- brr(n) = i
- Else '右列有值,总额减之
- x = x - arr(i, 2)
- End If
- Else '表示本组结束,进入下一组
- xx = x '当前余额(在随机数或最后一数出现极小数时需重新调用)
- If n = 1 Then '本组只有一个空,直接填之
- arr(brr(n), 2) = x
- Else
- 100: x = xx '恢复当前余额
- For k = 1 To n - 1 '超过一个空,前n-1个空随机数
- y = Round(x * Rnd, 2)
- If y < 0.001 Then k = 1: GoTo 100
- x = x - y
- arr(brr(k), 2) = y
- Next
- If x < 0.001 Then k = 1: GoTo 100
- arr(brr(k), 2) = x
- End If
- s = i: x = arr(s, 1) - arr(s, 2) '下一组起始行,并初始化brr及x
- n = IIf(arr(s, 2) > 0, 0, 1)
- If n = 1 Then brr(1) = s
- End If
- Next
- [i1].Resize(UBound(arr)) = Application.Index(arr, , 2)
- End Sub
复制代码 |
|