☰

Excel - 规定随机数函数的平均值

tag-icon • tag-icon microsoft-excel worksheet-function
Excel - 规定随机数函数的平均值

我想知道是否有人知道是否有一种方法可以使用 RANDBETWEEN 函数在 Excel 中生成一系列随机数，同时使这些随机数等于指定的平均值。我将使用它来填写定价表，例如，如果我使用 RANDBETWEEN 填写 100 行，并且我需要平均值为 50 美元，我知道我可以使用 RANDBETWEEN(25,75) 填写行并将其拖过 100 行，但它不一定会达到我需要的平均价格。有谁知道是否可以修改此内容，允许我规定该平均值？感谢您的帮助

答案1

假设有一场彩票，赞助商出售一堆彩票（假设 100 张），将它们放入一个容器（我们称之为帽子），然后从帽子中随机选择一张彩票来决定谁将赢得奖品。现在假设有多个奖项；赞助商抽出一等奖得主，然后是二等奖得主，然后是三等奖得主。当然，中奖彩票在抽出后不会放回帽子里，因为这样会允许它们再次被选中，导致一张彩票赢得多个奖项，这是违反规则的。因此，在第一个之后的所有选择都是受限制从某种意义上说，这个游戏是被操纵的，因此后续的获胜者只能来自非获胜者，这是一种达到不给任何一张票颁发多个奖项的目标的机制。

现在假设有 100 个奖品——每个人都会赢得一些奖品。当从帽子里抽出最后一张票时，那里只有一张票。选择被限制在预先确定的程度。你知道第 100 位获奖者是谁——就是站在人群中等待被叫名字的那个可怜的人；最后一个未获奖的人。

您的问题可以通过类似的策略解决：

  • 从“真正”随机数开始（我指的是根据你选择的概率分布统计分布的数字，例如，25-75 范围内的均匀分布），然后
  • 限制后面的数字（通过调整分布）作为达到规定平均值目标的机制。

约束概率分布的逻辑很简单：不要生成（并输入到定价表中）任何导致无法实现目标的数字。

我发现更容易将目标视为实现特定目标全部的 而不是特定的平均的当然，这些在逻辑上是等价的。例如，假设您只想要 4 个数字而不是 100 个。您希望平均值为 50，因此总数需要为 4×50=200。假设您生成前两个数字， 第1节=68 和第2部分=70。这些加起来是138，所以第3节+第4 节必须是 62。如果第3节是 50，那么第4 节必须是 12，超出了 25-75 的有效范围。解决 68+70+第3节+25=200 收益第3节=200−(68+70+25)=37，因此我们限制第3节介于 25 和 37 之间。一旦第3节被选中，第4 节确定；它必须是 62−第3节；即 200−(第1节+第2部分+第3节）这类似于第 100 张彩票的情况；它必须是第一张彩票之后剩下的钱。n已做出 −1 个选择。

这听起来像是 VBA 的工作，但我找到了一个工作表公式解决方案。在下文中，我选择设置所需概率分布 (25 和 75) 参数的界限；即，将它们放在单独的单元格 (A1和B1) 中，而不是将它们硬编码到公式中。我使用了一些辅助列。

  • 设置C1为所需的数字总数。您可以直接输入常数值，也可以计算为 平均数 × 数字数量， IE，=AVERAGE(A1, B1) * 100。
  • 设置。C2​=C$1-SUM(G$1:G1)
  • 设置。D1​=100-ROW()
  • 设置。E1​=MAX(C1-D1*B$1, A$1)
  • 设置。F1​=MIN(C1-D1*A$1, B$1)
  • 设置。G1​=RANDBETWEEN(E1, F1)
  • C2将、D1、E1、F1和向下拖动G1至第 100 行。

讨论：

让n为 100，即随机数的数量，因此也是行数。以下是我之前给出的四行示例的一般实现。

对于每一行我(1 ≤我≤n),

  • Gi是我​， 这我第个随机数。
  • Ei和 分别是所选范围的下限和上限 （从 的公式中可以明显看出）。FiGiGi
  • Di从倒计时n第 1 行中的 −1 至第 0 行n；即，它给出当前行下方的行数。如果您从第 1 行以外的行开始，请相应调整此公式。
  • C1是第1节+第2部分+…+vn −1+規模​
    Ci= C1− (第1节+第2部分+…+维−2+v i−1），等于（目标值）我​+Ⅵ i+1+…+vn −1+規模​

  • D1*A$1→ ×是DiA1Ⅵ i+1+Ⅵ +2+…+vn −1+規模​
    因此C1-D1*A$1( − × ) 是CiDi A1我​以便能够达到目标总数。

    让我们重新回顾一下四行示例。如果（假设）第1节是126，就不可能实现第1节+第2部分+第3节+第4 节=200，因为有约束第2部分，第3节， 和第4 节≥ 25。但如果第1节为 125，则有可能实现目标第2部分=第3节=第4 节= 25。因此 125 是第1节。 但是当然，第1节也要求≤75；125 的限制是没有意义的。五值必须满足两组约束（25-75 范围和达到总计所需的约束）n
    × 50)，因此 它们必须满足两边（上方和下方）中较严格的一个界限。Fi我​。

  • 并且是Ei我​。

以下是我得到的快照n = 10：

                    工作表快照

当然，按下F9会导致 Excel 重新计算工作表并生成一组新的随机数。但G1:G10总和总是 500；即平均值为 50。

如果規模​取决于第1节，第2部分，…，n −2， 和vn −1，它还是真的“随机”吗？

这是个有趣的问题。

  • 如果n为 1，第1节被强制为 50。这显然不是随机的。但当然，这只是一个极端情况。

  • 如果n为 2，第1节是随机选择的，那么第2部分设置为 100−第1节。 清楚地第2部分不独立于第1节。 但

    • P(第2部分=25）= P（第1节=75）= 1/51。
    • P(第2部分=26）= P（第1节=74）= 1/51。
    •       ︙
    • P(第2部分=75）= P（第1节=25）= 1/51。

    所以第2部分在 25-75 范围内均匀分布，与第1节。 

  • 回到彩票的例子——第 100 个奖的得主由前 99 个奖的得主决定。但是，在抽出任何彩票之前，每个参赛者都有同等的机会赢得第 100 个奖。因此，第 100 个奖的得主是随机的。

  • 我无法计算这个问题的答案n> 2. 因此我做了一个实证实验。我生成了 100 个介于 25 和 75 之间的随机数，并重新计算了 250 次，并累积了结果。然后我绘制了几个频率分布图五值：

     1    10    50

    （这些图像是其自身全尺寸版本的链接。）

    向上通过第91 节，分布看起来相当均匀：

    90   91

    但是，从第92 节，一些有趣的事情开始发生：

    92   93

    请注意，我必须改变比例第94 节超越：

     94    95    96
    97   98
    99   100

    看起来最近几个五值具有非均匀分布。我猜，因为n变得更大，更有可能的是第一个n−10（左右）个值将偏离所需的平均值，迫使最后几个值达到范围两端的极值，以使平均值回到正轨。但至少，所有值都应该具有对称分布。

    • 它们没有理由不对称。
    • 我的图表看起来是对称的。

如果你有兴趣继续探讨这个问题，你可以数学堆栈交换– 他们把这样的问题当早餐吃。

答案2

理论上不可能生成具有固定平均值的随机（甚至是伪随机）列表，因为这要求后续值取决于先前值，甚至可能在某些情况下迫使您返回并更改一些先前值。

答案3

正如 Catherine 所解释的那样，没有这样的内置函数。创建随机数据后，您必须返回并稍微更改数据。因此，可以使用 VBA 或其他编程语言完成此任务。

这是一个 VBA 解决方案。它的作用是：

  1. 用户指定随机函数的最小值、最大值以及将创建多少个数字
  2. 程序生成随机数据
  3. 程序计算期望状态和当前状态之间的差异
  4. 如果数字太高，则它每次访问一行并减去 -1 直到可以为止。如果数字太低，则它每次访问一行并加 +1 直到可以为止。

选项 1：下载可行的解决方案

警告：在不了解 VBA 的情况下运行从互联网上下载的宏可能会损害您的 PC。这就是为什么有第二种选择。

下载工作解决方案这里。是在 Excel 2013 中创建的，因此它应该可以在 Excel 2010 及更高版本中正常运行。打开文件后，Microsoft 应该会弹出两个安全窗口：弹出1，弹出2。你必须接受它们。跑步很简单：只需单击按钮...

选项 2：安装宏

打开 Excel -> Alt+F11 -> 插入 -> 模块 -> 粘贴以下代码 -> Ctrl+S -> 从下拉列表中选择“Excel 启用宏的工作簿 (*.xlsm)”

Sub RandomGenerator()
    Dim min, max, cnt As Variant

    Do While True
        min = InputBox("Set the minimum", "Generating random with average", 25)
        If min = "" Then Exit Sub
        If Not IsNumeric(min) Then
            MsgBox "Minimum has to be an integer. Try again.", vbExclamation, "Wrong input"
        ElseIf CInt(min) <= 0 Then
            MsgBox "Minimum has to be a positive integer. Try again.", vbExclamation, "Wrong input"
        Else
            Exit Do
        End If
    Loop

    Do While True
        max = InputBox("Set the maximum", "Generating random with average", 75)
        If max = "" Then Exit Sub
        If Not IsNumeric(max) Then
            MsgBox "Maximum has to be an integer. Try again.", vbExclamation, "Wrong input"
        ElseIf max <= min Then
            MsgBox "Maximum has to be greater than minimum. Try again.", vbExclamation, "Wrong input"
        ElseIf ((CInt(max) + CInt(min)) Mod 2) = 1 Then
            MsgBox "Average of (min + max) has to be even. Try again.", vbExclamation, "Wrong input"
        Else
            Exit Do
        End If
    Loop

    Do While True
        cnt = InputBox("Set the count of numbers to generate", "Generating random with average", 100)
        If cnt = "" Then Exit Sub
        If Not IsNumeric(cnt) Then
            MsgBox "Count has to be an integer. Try again.", vbExclamation, "Wrong input"
        ElseIf CInt(cnt) <= 0 Then
            MsgBox "Count has to be a positive integer. Try again.", vbExclamation, "Wrong input"
        Else
            Exit Do
        End If
    Loop

    Call generateRandomWithAverage(CInt(min), CInt(max), CInt(cnt))
End Sub

Sub generateRandomWithAverage(min As Integer, max As Integer, cnt As Integer)
    Dim random As Double
    Dim i, avg, sum, desiredAvg, diff As Integer

    sum = 0
    desiredAvg = (min + max) / 2

    For i = 1 To cnt
        Cells(i, 1) = Excel.Application.WorksheetFunction.RandBetween(min, max)
        sum = sum + Cells(i, 1)
    Next

    diff = sum - desiredAvg * cnt

    i = 1
    Do While diff <> 0
        If diff > 0 Then
            If Cells(i, 1) = min Then GoTo continue
            Cells(i, 1) = Cells(i, 1) - 1
            diff = diff - 1
        Else
            If Cells(i, 1) = max Then GoTo continue
            Cells(i, 1) = Cells(i, 1) + 1
            diff = diff + 1
        End If

continue:
        i = i + 1
        If i > cnt Then
            i = 1
        End If
    Loop

End Sub

运行宏：

  1. 确保您位于所需的工作表中！然后单击工作表中的任意位置！（因为此宏在已激活的工作表中运行）
  2. Alt+F11 -> 单击代码中的第二行 -> 按 F5

您还可以在工作表中创建一个按钮并为其分配宏 - 它更加用户友好，您不必检查是否在正确的工作表中。

相关内容