엑셀 VBA의 Rnd()가 정말 이렇게 나쁜가요?

엑셀 VBA의 Rnd()가 정말 이렇게 나쁜가요?

저는 2D 몬테카를로 시뮬레이션을 위해 간단한 LCG로 얻을 수 있는 특징적인 초평면이 없는 의사 난수 생성기가 필요합니다.다음 코드를 사용하여 Excel 2013에서 난수 생성기 Rnd()를 테스트했습니다(실행하는 데 약 5초 소요).

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

위의 코드를 실행한 결과 x vs y의 간단한 그림입니다.

그것은 매우 무작위적으로 보이지 않을 뿐만 아니라 악명 높은 랜두 알고리즘이 2D에서 하는 것보다 더 명백한 초평면을 가지고 있습니다.기본적으로, 내가 이 기능을 잘못 사용하고 있는 것입니까, 아니면 VBA의 Rnd() 기능이 실제로 최소한의 비트를 사용할 수 없는 것입니까?

비교를 위해, 제가 C++의 Mersenne Twister MT19937에 대해 얻은 것은 다음과 같습니다.

더 나은 랜덤 생성기를 생성하고 더 빠른 성능을 위해 다음과 같이 코드를 수정했습니다.

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

나는 결과를 계획한 후에 이것을 얻습니다.

코드 출력보다 결과가 더 좋아 보입니다.위의 코드를 이와 같은 것으로 약간 수정하는 것.

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

이전 것보다 더 나은 결과를 산출합니다.

물론 C++의 Mersenne Twister MT19937이 여전히 더 낫지만, 마지막 결과는 몬테 카를로 시뮬레이션을 수행하는 데 상당히 좋습니다.FWIW, 당신은 이 논문을 읽는 것에 관심이 있을 것입니다.Microsoft Excel 2010의 통계 절차의 정확성에 대해 설명합니다.

이는 완료하는 데 평균 1000 * 100 * 100회 반복이 필요할 것으로 보이며 VBA는 일반적으로 기본 Excel 공식보다 약간 느립니다.이 예를 생각해 보십시오.

Sub ZoomRNG()
    t = Timer
    [a1:a1000] = "=ROW()"
    [b1:c1000] = "=RAND()/100+0.5"
    [a1:c1000] = [A1:C1000].Value
    Debug.Print CDbl(Timer - t) ' 0.0546875 seconds
End Sub

갱신하다

전혀 나쁘지 않아요!이것은 그것 없이도 작동할 것입니다.Randomize

Sub ZoomRNGs() ' VBA.Rnd returns Single
    t = Timer
    For i = 1 To 1000
        Cells(i, 1) = i
        Cells(i, 2) = Rnd / 100 + 0.5
        Cells(i, 3) = Rnd / 100 + 0.5
    Next i
    Debug.Print Timer - t ' 0.25 seconds
End Sub

Sub ZoomRNGd() ' the Excel Function RAND() returns Double
    t = Timer
    For i = 1 To 1000
        Cells(i, 1) = i
        Cells(i, 2) = [RAND()] / 100 + 0.5
        Cells(i, 3) = [RAND()] / 100 + 0.5
    Next i
    Debug.Print Timer - t ' 0.625 seconds
End Sub

그리고.Single의 정밀도의 약 절반을 가지고 있습니다.Double:

s = Rnd: d = [RAND()]
Debug.Print s; d; Len(Str(s)); Len(Str(d)) ' " 0.2895625  0.580839555868045  9  17 "

업데이트 2

저는 VBA Rnd만큼 빠른 C 대안을 찾았습니다.
C:\Windows\System32\msvcrt.dll는 Microsoft C 런타임 라이브러리입니다.

Declare Function rand Lib "msvcrt" () As Long ' this in a VBA module

그런 다음 이렇게 사용할 수 있습니다.x = rand / 32767코드:

Sub ZoomRNG()
    t = Timer
    Dim i%, x#, y#, Found As Boolean
    For i = 1 To 1000
        Found = False
        Do
            x = rand / 32767 ' RAND_MAX = 32,767
            y = rand / 32767
            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
    Debug.Print Timer - t ' 2.875 seconds
End Sub

이 질문을 읽고 궁금증이 생겼고 알렉세이 보치카레프의 "Monte Carlo Simulation을 위한 Excel VBA 적합성 평가"라는 논문을 발견했습니다.RAND와 RND 기능은 모두 권장되지 않지만 논문에서 지적했듯이 Mersenne Twister는 Jerry Wang에 의해 VBA에서 구현되었습니다.

빠른 검색을 통해 지난 2015/2/28에 업데이트된 이 멋진 코멘트 버전으로 이동했습니다. http://www.math.sci.hiroshima-u.ac.jp/ ~m-mat/MT/VERSION/BASIC/MTwister.xlsb

출처 : http://www.math.sci.hiroshima-u.ac.jp/ ~m-mat/MT/VERSION/BASIC/BASIC.html

모든 LCG는 초평면을 생성합니다.LCG의 품질은 이러한 초평면 사이의 거리가 감소함에 따라 증가합니다.랜두보다 초평면이 더 많은 것은 좋은 일입니다.

MT 그림은 LCG가 아니기 때문에 훨씬 더 잘 보입니다.실제로, LCG가 아닌 모든 pRNG는 무작위로 보이는 그림을 가지고 있지만 여전히 나쁠 수 있습니다.

2D 상관 관계 문제를 방지하려면 x와 y에 동일한 LCG를 사용하지만 x와 y에 대해 다른 시드를 사용할 수 있습니다.물론 두 개의 개별 스트림을 가질 수 없기 때문에 RND에서는 작동하지 않습니다.참조를 통해 시드를 인수로 사용하는 LCG pRNG가 필요합니다.

속도와 선량함 사이의 균형으로서, 저는 그것들을 다음과 같이 결합하는 것을 생각했습니다.

for...
  z = [rand()] ' good but slow.
  for .. ' just a few
     t = z + rnd()
     t = t - int(t)
     ...

좋은 엔트로피 + 나쁜 엔트로피 = 더 나은 엔트로피라는 것을 기억하세요.

즉, [rand()]당 0.05ms에 불과합니다.

언급URL : https://stackoverflow.com/questions/38891165/is-excel-vbas-rnd-really-this-bad