我们需要一个 Excel 宏或公式,或者可以生成整数随机分布的东西,使得结果集具有固定大小和固定总和。更具体地说,我们想要模拟一个月的整数数据点(因此大小固定),其总数代表该月的需求(因此总和固定)。Excel 或第三方是否已经存在这样的功能?如果没有,有什么编写技巧吗?
我正在寻找类似这样的东西:
GetSetWithSum( count: 5, sum: 36)
此类公式的结果如下:
5
13
9
6
3
答案1
以下代码有效,但它有可能返回一个或多个值为 0,具体取决于它达到所需总数的速度(使用您的示例,如果第一个随机数为 36,则其他四个将为 0)。堆栈溢出可能比我更擅长编写防止这种情况的代码。
核心随机生成器代码
Sub RandomNumbers(ByVal ReqNum As Integer, ByVal ReqSum As Integer, Output As Collection)
Dim NewNum As Integer, SumNum As Integer
Dim I As Integer
SumNum = 0
Do Until (SumNum = ReqSum) Or (Output.Count = (ReqNum - 1))
Randomize Timer
NewNum = Int((ReqSum - SumNum) * Rnd + 1)
SumNum = SumNum + NewNum
Output.Add NewNum
Loop
If Output.Count < ReqNum Then
If SumNum < ReqSum Then
Output.Add (ReqSum - SumNum)
Else
NewNum = Output.Count
For I = (NewNum + 1) To ReqNum
Output.Add 0
Next I
End If
End If
End Sub
您可以使用类似这样的代码来调用此 Sub。此代码只是将随机值放入 A 列中。
Dim Numbers As New Collection, NumNum As Integer, SumofNum As Integer
Range("A:A").ClearContents
NumNum = 5
SumofNum = 36
Call RandomNumbers(NumNum, SumofNum, Numbers)
For SumofNum = 1 To Numbers.Count
NumNum = Numbers(SumofNum)
Cells(SumofNum, 1).Value = NumNum
Next SumofNum