Summary
LINEST関数を2次以上でもつかう
サンプルコード
Sub SampleLinEst2()
'Linest againt 'y = ax + b
DP LinEst2(Array(10, 20, 30), Array(1, 2, 3)), Array("a", "b"), Array("m", "stddev", "R2", "F/df", "SS"), , 5
' |m |stdde|R2 |F/df |SS |
'------------------------------------
'a | 10| 0| 1|Error| 200|
'b | 0| 0| 0| 1| 0|
'Linest againt 'y = ax^2 + bx +c
DP LinEst2(Array(10, 20, 30), PowerScan(Array(1, 2, 3), 2)), Array("a", "b", "c"), Array("m", "stddev", "R2", "F/df", "SS"), , 5
' |m |stdde|R2 |F/df |SS |
'------------------------------------
'a | 0| 0| 1|Error| 200|
'b | 10| 0| 0| 0| 0|
'c | 0| 0|Error|Error|Error|
'Linest againt 'y = ax^3 + bx^2 +cx +d
DP LinEst2(Array(1, 2, 3), PowerScan(Array(1, 2, 3), 3)), Array("a", "b", "c", "d"), Array("m", "stddev", "R2", "F/df", "SS"), , 5
' |m |stdde|R2 |F/df |SS |
'------------------------------------
'a |-0.09| 0| 1|Error| 2|
'b |0.545| 0| 0| 0| 0|
'c | 0| 0|Error|Error|Error|
'd |0.545| 0|Error|Error|Error|
End Sub
ちょっとした解説
ポイントと思っているところは、xのところに2次以上の場合はPowerScanで渡してるところです。
Sub SamplePowerScan()
Debug.Print Dump(PowerScan(2, 3))
'Array(Array(2#), Array(4#), Array(8#))
Debug.Print Dump(PowerScan(Array(1, 2, 3), 3))
'Array(Array(1#, 2#, 3#), Array(1#, 4#, 9#), Array(1#, 8#, 27#))
End Sub
サポート関数
Public Function LinEst2(ByVal y As Variant, ByVal x As Variant) As Variant
LinEst2 = Arr2DToJagArr(Application.WorksheetFunction.LinEst(y, x, True, True))
End Function
Public Function PowerScan(ByVal x As Variant, ByVal n As Long) As Variant
Dim wf As WorksheetFunction: Set wf = Application.WorksheetFunction
PowerScan = Arr2DToJagArr(wf.Transpose(Application.Power(wf.Transpose(x), ArrRange(1, n))))
End Function