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

以下の問題の実証をしたく作成したプログラム

問題

変数x（x＞０）、yの間にy＝x²なる関係が成立している場合の、変数xとyの相関係数を求めよ

Sub 相関係数()
    
    
    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
    
        
    ws.Cells(1, "A") = "X"
    ws.Cells(1, "B") = "Y=X^2"
    ws.Cells(1, "C") = "相関係数"
    
    
    For i = StartPos To EndPos
        
        '計算
        X = i
        Y = X ^ 2 ' Xの2条
        
        ws.Cells(i + 1, "A") = X
        ws.Cells(i + 1, "B") = Y
        DoEvents
    Next i
    
    ws.Range("C2") = "=CORREL(A:A,B:B)"
End Sub

Sub 相関係数()

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

ws.Cells(1, "A") = "X"

ws.Cells(1, "B") = "Y=X^2"

ws.Cells(1, "C") = "相関係数"

For i = StartPos To EndPos

'計算

X = i

Y = X ^ 2 ' Xの2条

ws.Cells(i + 1, "A") = X

ws.Cells(i + 1, "B") = Y

DoEvents

Next i

ws.Range("C2") = "=CORREL(A:A,B:B)"

End Sub

実行すると、C2に相関係数が出力される。出力結果は 1以下　具体的にはデータ数30000　で　0.968266006

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

いいね:

コメント

共有:

いいね:

コメント