How can you create an array of random numbers using the RAND or RANDBETWEEN functions?
如何使用RAND或RANDBETWEEN函数创建随机数组？
I'm trying to simulate the average outcome of 10 rolls of a six sided die (for example) in one formula?
我试图在一个公式中模拟10卷六面模（例如）的平均结果？
I have tried the following in the past, but it only creates one random number and repeats it.
我过去曾尝试过以下内容，但它只会创建一个随机数并重复它。
=SUMPRODUCT((ROW(A1:A10)^0)*(INT(RAND()*6)+1))/10
5 个解决方案
#1
7
The RANDBETWEEN function can deal with array inputs. So
RANDBETWEEN函数可以处理数组输入。所以
=RANDBETWEEN(ROW(1:10)^0,6)
effectively creates:
有效地创造：
=RANDBETWEEN({1;1;1;1;1;1;1;1;1;1},{6;6;6;6;6;6;6;6;6;6})
which returns an array of 10 different random numbers between 1 and 6. This could easily be changed to 100 (or however many) random numbers by changing A1:A10 to A1:A100.
它返回1到6之间的10个不同随机数的数组。通过将A1：A10更改为A1：A100，可以很容易地将其更改为100（或多个）随机数。
Therefore, a single formula for the average of 10 separate dice rolls could be:
因此，平均10个独立骰子卷的单一公式可以是：
=SUMPRODUCT(RANDBETWEEN(ROW(A1:A10)^0,6)/10
or the equivalent CSE formula
或等效的CSE公式
{=AVERAGE(RANDBETWEEN(ROW(A1:A10)^0,6))}
#2
2
Three possible solutions:
三种可能的解决方
1) You can do a decent job simulating the average of 10 rolls in a single cell by using a normal approximation. In the target cell enter:
1）通过使用正态近似，您可以在单个单元格中模拟10个卷的平均值。在目标单元格中输入：
=ROUND(NORM.INV(RAND(),3.5,SQRT(35/(12*10))),1)
I left the 10 in explicitly to show what you would need to modify if you want to vary the number of rolls. The fact that 35 is 3.5 * 10 is a coincidence, the number 35 would stay there even with 100 rolls. If you vary from 10, you might want to tweak the rounding. For 10 the standard deviation of is roughly 0.54, putting 1 and 6 at around 4.6 standard deviations away from the mean of 3.5. On average the formula will give you a value outside the range 1-6 1 out of every 270,000 times. A histogram of dice averages for 10 rolls forms a fairly nice bell-shaped curve, so this approach is certainly reasonable, and might work for your purposes.
如果你想改变卷数，我会明确地留下10来显示你需要修改的内容。事实上35是3.5 * 10是巧合，即使有100卷，35号也会保持不变。如果从10开始变化，则可能需要调整舍入。对于10，标准偏差约为0.54，将1和6放在约4.6的标准偏差处，平均值为3.5。平均而言，该公式将为您提供超过每270,000次1-6 1的范围内的值。 10个卷的平均骰子直方图形成一个相当不错的钟形曲线，所以这种方法当然是合理的，可能适用于您的目的。
2) A VBA sub which fills selected cells with exact spreadsheet formulas, relieving you of the tedious need to count. If there is a requirement that the final spreadsheet is macro-free, this can still be used in the development stage:
2）一个VBA子程序，用精确的电子表格公式填充选定的单元格，减轻了繁琐的计数需求。如果要求最终电子表格是无宏的，则仍可在开发阶段使用：
Sub DiceAverageFormula()
'places a formula in the selected cell (or cells)
'which simulates the average of rolling a die n 10 times
Dim i As Long, n As Long
Dim form As String
n = InputBox("How many times do you want to roll?", "Dice", 10)
If n < 1 Then Exit Sub
form = "=Average(Randbetween(1,6)"
For i = 2 To n
form = form & ",Randbetween(1,6)"
Next i
form = form & ")"
Selection.Formula = form
End Sub
3) For completeness sake, The naïve VBA UDF:
3）为了完整起见，天真的VBA UDF：
Function RollDice(n As Long, Optional k As Long = 6) As Double
'simulates the rolling of n k-sided die, returing the average
Application.Volatile 'can be removed, of course
Dim i As Long, sum As Long
With Application.WorksheetFunction
For i = 1 To n
sum = sum + .RandBetween(1, k)
Next i
End With
RollDice = sum / n
End Function
To test the various approaches, I filled 30 rows with each approach and made 5-number summaries of the results (which appear reasonable):
为了测试各种方法，我在每个方法中填充了30行，并对结果进行了5个数字的总结（看起来合理）：
Needless to say, the fact that in this screenshot the mean for the normal approximation is lower than the other two is not particularly relevant. You would need a fairly large number of runs before a statistical test could detect any difference between the first column and the other 2.
不用说，在这个屏幕截图中，正常近似的平均值低于其他两个的平均值并不特别相关。在统计测试可以检测到第一列和另一列之间的任何差异之前，您需要相当多的运行。
On Edit: Here is a graph of exact probability vs. normal approximation for the mean of 10 rolls of a fair die. It underscores how reasonable it is to use the central limit theorem here. Even though n = 10 is a somewhat small sample size, the exact probability is sufficiently symmetric and unimodal that it already looks fairly normal:
编辑：这是一个公平骰子的10个平均值的精确概率与正常近似的图表。它强调了在这里使用中心极限定理是多么合理。即使n = 10是一个有点小的样本大小，确切的概率是足够对称和单峰的，它已经看起来很正常：
#3
1
The only direct answer to the question I can think of is to define a named range called (say) randeval and use the hidden EVALUATE function in its definition:-
我能想到的唯一直接答案是定义一个名为（例如）randeval的命名区域，并在其定义中使用隐藏的EVALUATE函数： -
=EVALUATE(REPT("+RANDBETWEEN(1,6)",Sheet1!A1))
Enter 10 in A1, and the following in A2:-
在A1中输入10，在A2中输入以下内容： -
=randeval/a1
Each time you hit Ctrl-Alt-F9, it will produce a new estimate of the average of ten dice rolls.
每按一次Ctrl-Alt-F9，它将产生十个骰子平均值的新估计值。
#4
1
I was trying to do something similar in VBA, but Evaluate doesn't behave quite like Excel formulas:
我试图在VBA中做类似的事情，但是Evaluate的行为与Excel公式不同：
a = [randbetween(row(1:10)^0,6)] ' 3
b = [transpose(randbetween(row(1:10)^0,6))] ' {3,3,6,4,6,4,2,5,1,2}
c = [average(randbetween(row(1:10)^0,6))] ' 2.9
#5
0
Does this work for you? It has one number of the dice in each row.
You input the number of "throws" in A1 and B column will show you the outcome.
这对你有用吗？它每排有一个骰子。您输入A1中的“投掷”数量，B列将显示结果。
In B1:
=IF(A$1>=ROW(),RANDBETWEEN(1,6),"")
Then you just fill down to suit your need.
然后你只需填写以满足你的需要。
How about a UDF?
UDF怎么样？
Function Throws(c)
If Range(c).Value > 0 Then
For i = 1 To Range(c).Value
Str1 = Str1 & "," & WorksheetFunction.RandBetween(1, 6)
Next i
Throws = Mid(Str1, 2)
End If
End Function
You enter the number of throws in any cell and use =Throws("CELL") to activate it.
And it returns the throws in one cell
您输入任何单元格中的投掷数量，并使用=投掷（“CELL”）来激活它。并且它返回一个单元格中的抛出
EDIT2:
EDIT2：
Not sure you can use average even with it being an array.
不确定即使它是一个阵列也可以使用平均值。
Function Throws(c) As Variant()
Dim Str1 As Variant
If Range(c).Value > 0 Then
For i = 1 To Range(c).Value
If i = 1 Then Str1 = WorksheetFunction.RandBetween(1, 6)
Str1 = Str1 & "," & WorksheetFunction.RandBetween(1, 6)
Next i
Throws = Application.WorksheetFunction.Transpose(Array(Str1))
End If
End Function
#1
7
The RANDBETWEEN function can deal with array inputs. So
RANDBETWEEN函数可以处理数组输入。所以
=RANDBETWEEN(ROW(1:10)^0,6)
effectively creates:
有效地创造：
=RANDBETWEEN({1;1;1;1;1;1;1;1;1;1},{6;6;6;6;6;6;6;6;6;6})
which returns an array of 10 different random numbers between 1 and 6. This could easily be changed to 100 (or however many) random numbers by changing A1:A10 to A1:A100.
它返回1到6之间的10个不同随机数的数组。通过将A1：A10更改为A1：A100，可以很容易地将其更改为100（或多个）随机数。
Therefore, a single formula for the average of 10 separate dice rolls could be:
因此，平均10个独立骰子卷的单一公式可以是：
=SUMPRODUCT(RANDBETWEEN(ROW(A1:A10)^0,6)/10
or the equivalent CSE formula
或等效的CSE公式
{=AVERAGE(RANDBETWEEN(ROW(A1:A10)^0,6))}
#2
2
Three possible solutions:
三种可能的解决方
1) You can do a decent job simulating the average of 10 rolls in a single cell by using a normal approximation. In the target cell enter:
1）通过使用正态近似，您可以在单个单元格中模拟10个卷的平均值。在目标单元格中输入：
=ROUND(NORM.INV(RAND(),3.5,SQRT(35/(12*10))),1)
I left the 10 in explicitly to show what you would need to modify if you want to vary the number of rolls. The fact that 35 is 3.5 * 10 is a coincidence, the number 35 would stay there even with 100 rolls. If you vary from 10, you might want to tweak the rounding. For 10 the standard deviation of is roughly 0.54, putting 1 and 6 at around 4.6 standard deviations away from the mean of 3.5. On average the formula will give you a value outside the range 1-6 1 out of every 270,000 times. A histogram of dice averages for 10 rolls forms a fairly nice bell-shaped curve, so this approach is certainly reasonable, and might work for your purposes.
如果你想改变卷数，我会明确地留下10来显示你需要修改的内容。事实上35是3.5 * 10是巧合，即使有100卷，35号也会保持不变。如果从10开始变化，则可能需要调整舍入。对于10，标准偏差约为0.54，将1和6放在约4.6的标准偏差处，平均值为3.5。平均而言，该公式将为您提供超过每270,000次1-6 1的范围内的值。 10个卷的平均骰子直方图形成一个相当不错的钟形曲线，所以这种方法当然是合理的，可能适用于您的目的。
2) A VBA sub which fills selected cells with exact spreadsheet formulas, relieving you of the tedious need to count. If there is a requirement that the final spreadsheet is macro-free, this can still be used in the development stage:
2）一个VBA子程序，用精确的电子表格公式填充选定的单元格，减轻了繁琐的计数需求。如果要求最终电子表格是无宏的，则仍可在开发阶段使用：
Sub DiceAverageFormula()
'places a formula in the selected cell (or cells)
'which simulates the average of rolling a die n 10 times
Dim i As Long, n As Long
Dim form As String
n = InputBox("How many times do you want to roll?", "Dice", 10)
If n < 1 Then Exit Sub
form = "=Average(Randbetween(1,6)"
For i = 2 To n
form = form & ",Randbetween(1,6)"
Next i
form = form & ")"
Selection.Formula = form
End Sub
3) For completeness sake, The naïve VBA UDF:
3）为了完整起见，天真的VBA UDF：
Function RollDice(n As Long, Optional k As Long = 6) As Double
'simulates the rolling of n k-sided die, returing the average
Application.Volatile 'can be removed, of course
Dim i As Long, sum As Long
With Application.WorksheetFunction
For i = 1 To n
sum = sum + .RandBetween(1, k)
Next i
End With
RollDice = sum / n
End Function
To test the various approaches, I filled 30 rows with each approach and made 5-number summaries of the results (which appear reasonable):
为了测试各种方法，我在每个方法中填充了30行，并对结果进行了5个数字的总结（看起来合理）：
Needless to say, the fact that in this screenshot the mean for the normal approximation is lower than the other two is not particularly relevant. You would need a fairly large number of runs before a statistical test could detect any difference between the first column and the other 2.
不用说，在这个屏幕截图中，正常近似的平均值低于其他两个的平均值并不特别相关。在统计测试可以检测到第一列和另一列之间的任何差异之前，您需要相当多的运行。
On Edit: Here is a graph of exact probability vs. normal approximation for the mean of 10 rolls of a fair die. It underscores how reasonable it is to use the central limit theorem here. Even though n = 10 is a somewhat small sample size, the exact probability is sufficiently symmetric and unimodal that it already looks fairly normal:
编辑：这是一个公平骰子的10个平均值的精确概率与正常近似的图表。它强调了在这里使用中心极限定理是多么合理。即使n = 10是一个有点小的样本大小，确切的概率是足够对称和单峰的，它已经看起来很正常：
#3
1
The only direct answer to the question I can think of is to define a named range called (say) randeval and use the hidden EVALUATE function in its definition:-
我能想到的唯一直接答案是定义一个名为（例如）randeval的命名区域，并在其定义中使用隐藏的EVALUATE函数： -
=EVALUATE(REPT("+RANDBETWEEN(1,6)",Sheet1!A1))
Enter 10 in A1, and the following in A2:-
在A1中输入10，在A2中输入以下内容： -
=randeval/a1
Each time you hit Ctrl-Alt-F9, it will produce a new estimate of the average of ten dice rolls.
每按一次Ctrl-Alt-F9，它将产生十个骰子平均值的新估计值。
#4
1
I was trying to do something similar in VBA, but Evaluate doesn't behave quite like Excel formulas:
我试图在VBA中做类似的事情，但是Evaluate的行为与Excel公式不同：
a = [randbetween(row(1:10)^0,6)] ' 3
b = [transpose(randbetween(row(1:10)^0,6))] ' {3,3,6,4,6,4,2,5,1,2}
c = [average(randbetween(row(1:10)^0,6))] ' 2.9
#5
0
Does this work for you? It has one number of the dice in each row.
You input the number of "throws" in A1 and B column will show you the outcome.
这对你有用吗？它每排有一个骰子。您输入A1中的“投掷”数量，B列将显示结果。
In B1:
=IF(A$1>=ROW(),RANDBETWEEN(1,6),"")
Then you just fill down to suit your need.
然后你只需填写以满足你的需要。
How about a UDF?
UDF怎么样？
Function Throws(c)
If Range(c).Value > 0 Then
For i = 1 To Range(c).Value
Str1 = Str1 & "," & WorksheetFunction.RandBetween(1, 6)
Next i
Throws = Mid(Str1, 2)
End If
End Function
You enter the number of throws in any cell and use =Throws("CELL") to activate it.
And it returns the throws in one cell
您输入任何单元格中的投掷数量，并使用=投掷（“CELL”）来激活它。并且它返回一个单元格中的抛出
EDIT2:
EDIT2：
Not sure you can use average even with it being an array.
不确定即使它是一个阵列也可以使用平均值。
Function Throws(c) As Variant()
Dim Str1 As Variant
If Range(c).Value > 0 Then
For i = 1 To Range(c).Value
If i = 1 Then Str1 = WorksheetFunction.RandBetween(1, 6)
Str1 = Str1 & "," & WorksheetFunction.RandBetween(1, 6)
Next i
Throws = Application.WorksheetFunction.Transpose(Array(Str1))
End If
End Function