Excel 能否生成固定大小和固定总和的随机分布?

Excel 能否生成固定大小和固定总和的随机分布?

我们需要一个 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

相关内容