【Excel】【VBA】相関係数の算出サンプル　その２

以下の問題を計算した際に作成したもの

問題

変数x 、yの間にy＝x²なる関係が成立している場合に、z＝2x²+3として求めた変数yとzの相関係数　　

Sub 相関係数2()
    
    
    Const StartPos As Long = 1
    Const Count As Long = 3000
    Const EndPos As Long = StartPos + Count
    
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("sheet1")
    ws.Cells.ClearContents
    
    
    
    Dim i As Long, j As Long
    '公式
    Dim X As Long, Y As Long, Z As Long
    
        
    ws.Cells(1, "A") = "X"
    ws.Cells(1, "B") = "Y=X^2"
    ws.Cells(1, "C") = "z＝2x^2+3"
    ws.Cells(1, "D") = "相関係数"
    
    
    For i = StartPos To EndPos
        
        '計算
        X = i
        Y = X ^ 2 ' Xの2条
        Z = 2 * Y + 3
        
        ws.Cells(i + 1, "A") = X
        ws.Cells(i + 1, "B") = Y
        ws.Cells(i + 1, "C") = Z
        
        DoEvents
    Next i
    
    ws.Range("D2") = "=CORREL(B:B,C:C)"
End Sub

実行結果は１