I want to produce 100 random numbers with normal distribution (with µ=10, σ=7) and then draw a quantity diagram for these numbers.
我想生成100个正态分布的随机数(μ= 10,σ= 7),然后绘制这些数字的数量图。
How can I produce random numbers with a specific distribution in Excel 2010?
如何在Excel 2010中生成具有特定分布的随机数?
One more question:
还有一个问题:
When I produce, for example, 20 random numbers with RANDBETWEEN(Bottom,Top), the numbers change every time the sheet recalculates. How can I keep this from happening?
例如,当我使用RANDBETWEEN(Bottom,Top)生成20个随机数时,每次重新计算工作表时数字都会改变。我该如何防止这种情况发生?
7 个解决方案
#1
83
Use the NORMINV function together with RAND():
将NORMINV功能与RAND()一起使用:
=NORMINV(RAND(),10,7)
To keep your set of random values from changing, select all the values, copy them, and then paste (special) the values back into the same range.
要保持随机值集不变,请选择所有值,复制它们,然后将值(特殊)粘贴回相同的范围。
Sample output (column A), 500 numbers generated with this formula:
样本输出(A列),使用此公式生成的500个数字:
#2
5
IF you have excel 2007, you can use
如果你有excel 2007,你可以使用
=NORMSINV(RAND())*SD+MEAN
Because there was a big change in 2010 about excel's function
因为2010年有关于excel功能的重大变化
#3
3
As @osknows said in a comment above (rather than an answer which is why I am adding this), the Analysis Pack has Random Number Generation to generate a set of numbers. A good summary link is at http://www.bettersolutions.com/excel/EUN147/YI231420881.htm.
正如@osknows在上面的评论中所说的那样(而不是我为此添加此答案的答案),分析包具有随机数生成以生成一组数字。一个很好的摘要链接在http://www.bettersolutions.com/excel/EUN147/YI231420881.htm。
#4
2
Rand() does generate a uniform distribution of random numbers between 0 and 1, but the norminv (or norm.inv) function is taking the uniform distributed Rand() as an input to generate the normally distributed sample set.
Rand()确实生成0到1之间随机数的均匀分布,但是norminv(或norm.inv)函数将统一分布式Rand()作为输入来生成正态分布的样本集。
#5
1
Take a loot at the Wikipedia article on random numbers as it talks about using sampling techniques. You can find the equation for your normal distribution by plugging into this one
在关于使用抽样技术的随机数字的*文章中获取战利品。您可以通过插入这个方程式找到正态分布的等式
(equation via Wikipedia)
(通过*的等式)
As for the second issue, go into Options under the circle Office icon, go to formulas, and change calculations to "Manual". That will maintain your sheet and not recalculate the formulas each time.
至于第二个问题,请进入圆圈Office图标下的选项,转到公式,然后将计算更改为“手动”。这将维护您的工作表,而不是每次都重新计算公式。
#6
1
About the recalculation:
关于重新计算:
You can keep your set of random values from changing every time you make an adjustment, by adjusting the automatic recalculation, to: manual recalculate. (Re)calculations are then only done when you press F9. Or shift F9.
每次进行调整时,通过调整自动重新计算,您可以保持随机值集的更改为:手动重新计算。 (重新)计算仅在按F9时完成。或者换班F9。
See this link (though for older excel version than the current 2013) for some info about it: https://support.office.com/en-us/article/Change-formula-recalculation-iteration-or-precision-73fc7dac-91cf-4d36-86e8-67124f6bcce4.
有关它的一些信息,请参阅此链接(虽然对于比当前2013年更旧的excel版本):https://support.office.com/en-us/article/Change-formula-recalculation-iteration-or-precision-73fc7dac- 91cf-4d36-86e8-67124f6bcce4。
#7
-3
The numbers generated by
生成的数字
=NORMINV(RAND(),10,7)
are uniformally distributed. If you want the numbers to be normally distributed, you will have to write a function I guess.
是统一分布的。如果你想要正常分配数字,你必须编写一个函数我猜。
#1
83
Use the NORMINV function together with RAND():
将NORMINV功能与RAND()一起使用:
=NORMINV(RAND(),10,7)
To keep your set of random values from changing, select all the values, copy them, and then paste (special) the values back into the same range.
要保持随机值集不变,请选择所有值,复制它们,然后将值(特殊)粘贴回相同的范围。
Sample output (column A), 500 numbers generated with this formula:
样本输出(A列),使用此公式生成的500个数字:
#2
5
IF you have excel 2007, you can use
如果你有excel 2007,你可以使用
=NORMSINV(RAND())*SD+MEAN
Because there was a big change in 2010 about excel's function
因为2010年有关于excel功能的重大变化
#3
3
As @osknows said in a comment above (rather than an answer which is why I am adding this), the Analysis Pack has Random Number Generation to generate a set of numbers. A good summary link is at http://www.bettersolutions.com/excel/EUN147/YI231420881.htm.
正如@osknows在上面的评论中所说的那样(而不是我为此添加此答案的答案),分析包具有随机数生成以生成一组数字。一个很好的摘要链接在http://www.bettersolutions.com/excel/EUN147/YI231420881.htm。
#4
2
Rand() does generate a uniform distribution of random numbers between 0 and 1, but the norminv (or norm.inv) function is taking the uniform distributed Rand() as an input to generate the normally distributed sample set.
Rand()确实生成0到1之间随机数的均匀分布,但是norminv(或norm.inv)函数将统一分布式Rand()作为输入来生成正态分布的样本集。
#5
1
Take a loot at the Wikipedia article on random numbers as it talks about using sampling techniques. You can find the equation for your normal distribution by plugging into this one
在关于使用抽样技术的随机数字的*文章中获取战利品。您可以通过插入这个方程式找到正态分布的等式
(equation via Wikipedia)
(通过*的等式)
As for the second issue, go into Options under the circle Office icon, go to formulas, and change calculations to "Manual". That will maintain your sheet and not recalculate the formulas each time.
至于第二个问题,请进入圆圈Office图标下的选项,转到公式,然后将计算更改为“手动”。这将维护您的工作表,而不是每次都重新计算公式。
#6
1
About the recalculation:
关于重新计算:
You can keep your set of random values from changing every time you make an adjustment, by adjusting the automatic recalculation, to: manual recalculate. (Re)calculations are then only done when you press F9. Or shift F9.
每次进行调整时,通过调整自动重新计算,您可以保持随机值集的更改为:手动重新计算。 (重新)计算仅在按F9时完成。或者换班F9。
See this link (though for older excel version than the current 2013) for some info about it: https://support.office.com/en-us/article/Change-formula-recalculation-iteration-or-precision-73fc7dac-91cf-4d36-86e8-67124f6bcce4.
有关它的一些信息,请参阅此链接(虽然对于比当前2013年更旧的excel版本):https://support.office.com/en-us/article/Change-formula-recalculation-iteration-or-precision-73fc7dac- 91cf-4d36-86e8-67124f6bcce4。
#7
-3
The numbers generated by
生成的数字
=NORMINV(RAND(),10,7)
are uniformally distributed. If you want the numbers to be normally distributed, you will have to write a function I guess.
是统一分布的。如果你想要正常分配数字,你必须编写一个函数我猜。