Может ли Excel произвести случайное распределение фиксированного размера и фиксированной суммы?

Question

Нам нужен макрос или формула Excel или что-то, что может привести к случайному распределению целых чисел, так что результирующий набор будет иметь фиксированный размер и фиксированную сумму. Более конкретно, мы хотим смоделировать сумму целочисленных точек данных за месяц (отсюда и фиксированный размер), общая сумма которых представляет спрос на этот месяц (отсюда и фиксированная сумма). Такая функция уже существует в Excel или от стороннего производителя? Если нет, какие-либо советы по написанию одного?

Я ищу что-то похожее на это:

GetSetWithSum( count: 5, sum: 36)

Результатом такой формулы будет что-то вроде этого:

5
13
9
6
3

Answer 1

Следующий код работает, но у него есть возможность вернуть 0 для одного или нескольких значений, в зависимости от того, как быстро он достигает требуемой суммы (в вашем примере, если первое случайное число равно 36, тогда остальные четыре будут 0). хорошие люди в Stack OverFlow могут лучше писать код, который предотвращает это, чем я.

Основной генератор случайных кодов

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, используя код с чем-то вроде этого. Этот код просто помещает случайные значения в столбец А.

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