Excel VBA的Rnd()真的很糟吗? [英] Is Excel VBA's Rnd() really this bad?
问题描述
Sub ZoomRNG()
随机化
对于i = 1到1000
Found = False
Do
x = Rnd()'0和0.0之间的2个随机数
y = Rnd()
如果((x> 0.5)And(x <0.51))然后
如果((y> 0.5)And(y <0.51)
'如果x& y在窄范围
细胞(i,1)= i
细胞(i,2)= x
细胞(i,3)= y
Found = True
结束如果
结束If
循环while(未找到)
下一步i
End Sub
以下是运行上述代码的x vs y的简单图表
它不仅不是随机的,它比2D中臭名昭着的RANDU算法有更明显的超平面。基本上,我使用的函数不正确或是VBA中的Rnd()函数实际上是不是最少可用?
为了比较,这里是我在C ++中为Mersenne Twister MT19937所得到的。
结果看起来比代码的输出更好。修改上面的代码有点像这样的东西
Const N = 1000
Sub ZoomRNG()
Dim RandXY(1到N,1到3)As Single,i As Single,x As Single,y As Single
For i = 1 To N
随机化
Do
x = Rnd
如果x> 0.5和x < 0.51然后
y = Rnd
如果y> 0.5和y < 0.51然后
RandXY(i,1)= i
RandXY(i,2)= x
RandXY(i,3)= y
退出Do
结束If
如果
循环
下一个
单元格(1,9).Resize(N,3)= RandXY
End Sub
产生比上一个更好的结果
确定C ++中的Mersenne Twister MT19937仍然更好,但最后一个结果对于进行蒙特卡罗模拟是相当不错的。 FWIW,您可能有兴趣阅读本文:关于统计程序的准确性在Microsoft Excel 2010中。
I need a pseudo random number generator for 2D Monte Carlo simulation that doesn't have the characteristic hyperplanes that you get with simple LCGs. I tested the random number generator Rnd() in Excel 2013 using the following code (takes about 5 secs to run):
Sub ZoomRNG()
Randomize
For i = 1 To 1000
Found = False
Do
x = Rnd() ' 2 random numbers between 0.0 and 1.0
y = Rnd()
If ((x > 0.5) And (x < 0.51)) Then
If ((y > 0.5) And (y < 0.51)) Then
' Write if both x & y in a narrow range
Cells(i, 1) = i
Cells(i, 2) = x
Cells(i, 3) = y
Found = True
End If
End If
Loop While (Not Found)
Next i
End Sub
Here is a simple plot of x vs y from running the above code
Not only is it not very random-looking, it has more obvious hyperplanes than the infamous RANDU algorithm does in 2D. Basically, am I using the function incorrectly or is the Rnd() function in VBA actually not the least bit usable?
For comparison, here's what I get for the Mersenne Twister MT19937 in C++.
To yield a better random generator and to make its performance faster, I modified your code like this:
Const N = 1000 'Put this on top of your code module
Sub ZoomRNG()
Dim RandXY(1 To N, 1 To 3) As Single, i As Single, x As Single, y As Single
For i = 1 To N
Randomize 'Put this in the loop to generate a better random numbers
Do
x = Rnd
y = Rnd
If x > 0.5 And x < 0.51 Then
If y > 0.5 And y < 0.51 Then
RandXY(i, 1) = i
RandXY(i, 2) = x
RandXY(i, 3) = y
Exit Do
End If
End If
Loop
Next
Cells(1, 9).Resize(N, 3) = RandXY
End Sub
I obtain this after plotting the result
The result looks better than your code's output. Modifying the above code a little bit to something like this
Const N = 1000
Sub ZoomRNG()
Dim RandXY(1 To N, 1 To 3) As Single, i As Single, x As Single, y As Single
For i = 1 To N
Randomize
Do
x = Rnd
If x > 0.5 And x < 0.51 Then
y = Rnd
If y > 0.5 And y < 0.51 Then
RandXY(i, 1) = i
RandXY(i, 2) = x
RandXY(i, 3) = y
Exit Do
End If
End If
Loop
Next
Cells(1, 9).Resize(N, 3) = RandXY
End Sub
yields a better result than the previous one
Sure the Mersenne Twister MT19937 in C++ is still better, but the last result is quite good for conducting Monte-Carlo simulations. FWIW, you might be interested in reading this paper: On the accuracy of statistical procedures in Microsoft Excel 2010.
这篇关于Excel VBA的Rnd()真的很糟吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!