Excel VBA的Rnd（）真的很糟吗？ [英] Is Excel VBA's Rnd() really this bad?

问题描述

我需要一个用于2D蒙特卡罗模拟的伪随机数生成器，其不具有使用简单LCG获得的特征超平面。我使用以下代码测试了Excel 2013中的随机数生成器Rnd（）（运行大约需要5秒）：

  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屋！