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

問題

変数x 、yの相関係数が１の場合、z＝-xとして求めた変数yとzの相関係数

今回、変数x 、yの相関係数が１の場合　については、相関係数のサンプル２の結果を再利用　し　改造

Sub 相関係数3()
    
    Const StartPos As Long = 1
    Const EndPos As Long = 30000
    
    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, ZZ As Long
    
        
    ws.Cells(1, "A") = "X：使わない"
    ws.Cells(1, "B") = "Y=X^2 : Xとする"
    ws.Cells(1, "C") = "Z＝2X^2+3 : Yとする"
    ws.Cells(1, "D") = "Z＝-X"
    ws.Cells(1, "E") = "相関係数"
    
    
    For i = StartPos To EndPos
        
        '計算
        X = i
        Y = X ^ 2 ' Xの2条
        Z = 2 * Y + 3
        ZZ = -Y
        
        ws.Cells(i + 1, "A") = X
        ws.Cells(i + 1, "B") = Y
        ws.Cells(i + 1, "C") = Z
        ws.Cells(i + 1, "D") = ZZ
        
        DoEvents
    Next i
    
    ws.Range("E2") = "=CORREL(C:C,D:D)"
End Sub

Sub 相関係数3()

Const StartPos As Long = 1

Const EndPos As Long = 30000

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, ZZ As Long

ws.Cells(1, "A") = "X：使わない"

ws.Cells(1, "B") = "Y=X^2 : Xとする"

ws.Cells(1, "C") = "Z＝2X^2+3 : Yとする"

ws.Cells(1, "D") = "Z＝-X"

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

For i = StartPos To EndPos

'計算

X = i

Y = X ^ 2 ' Xの2条

Z = 2 * Y + 3

ZZ = -Y

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

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

ws.Cells(i + 1, "C") = Z

ws.Cells(i + 1, "D") = ZZ

DoEvents

Next i

ws.Range("E2") = "=CORREL(C:C,D:D)"

End Sub

結果は-1

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

いいね:

コメント

共有:

いいね:

コメント