为excel随机数设置种子 [英] setting seed for excel random number
问题描述
在 excel 中,下面的公式将从均值为 10 和方差为 1 的正态分布中生成随机数.有没有办法设置固定种子,以便我始终获得一组固定的随机数?我使用的是 Excel 2010
=NORMINV(RAND(),10,1)
您可以使用电子表格函数实现您自己的随机数生成器.例如,C++11 有一个
从直方图中可以看出,分布似乎大致正常.
In excel below formula will generate random number from a normal distribution with mean 10 and variance 1. Is there a way to set a fix seed so that i get a fix set of random numbers all the time? I am using Excel 2010
=NORMINV(RAND(),10,1)
You can implement your own random number generator using spreadsheet functions. For example, C++11 has a Lehmer random number generator called minstd_rand
which is obtained by the recurrence
X = X*g (mod m)
where g = 48271
and m = 2^31-1
In A1
you can place your seed value. In A2
enter the formula:
=MOD(48271*A1,2^31-1)
and copy it down however far you need.
In B2
enter =A2/(2^31-1)
and in C2
enter =NORM.INV(B2,10,1)
, copying as needed. Note that you can always replace the seed value in A1
by
=RANDBETWEEN(1,2^31-2)
if you want to turn volatile randomness back on.
The following screenshot shows 25 random normal variables generated in this fashion:
As you can tell from the histogram the distribution seems roughly normal.
这篇关于为excel随机数设置种子的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!