LinEst用于多项式函数vba [英] LinEst for polynomial function vba

问题描述

我正在尝试使用vba计算多项式回归.首先，我尝试了y = x ^ 2 + b:

I am trying to calculate a polynomial regression using vba. First, I tried y=x^2+b:

OUTPUT = WorksheetFunction.Application.LinEst (A,Application.Power(C,2),True,True)

其中A和C是数组并且 OUTPUT 很好.我可以使用 Application.Index(OUTPUT，3)

where A and C are arrays and the OUTPUT is good. I can read r2 from OUTPUT using Application.Index(OUTPUT,3)

但是，当我想通过将Array添加到Array的参数来尝试 y = x + x ^ 2 + b 时:

However, when I want to try y=x+x^2+b by adding Array to the argument of Array:

OUTPUT = WorksheetFunction.Application.LinEst (A,Application.Power(C,Array(1,2)),True,True)

我无法使用 Application.Index(OUTPUT，3)

有解决方案吗?我在做什么错了?

Any solution? What am I doing wrong?

解决方案:R_SQUARE = Application.Index(WorksheetFunction.LinEst(yVal，Application.Power(xVal，Application.Transponse(Array(1，2)))，True，True)，3,1)

Solution: R_SQUARE = Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Application.Transponse(Array(1, 2))), True, True), 3,1)

推荐答案

尝试关注..

Sub LinEst()
Dim yVal As Range, xVal As Range
Set yVal = Range("C5:C14")
Set xVal = Range("B5:B14")

'You tried following formula which gives incorrect results for polynomial order 2
Range("B17") = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, 2), True, True), 3)

'For linear
Range("B18") = Application.Index(WorksheetFunction.LinEst(yVal, _
                xVal, True, True), 3)
'For polynomial order 2
Range("B19") = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, Array(1, 2)), True, True), 3)
'For polynomial order 3
Range("B20") = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, Array(1, 2, 3)), True, True), 3)

End Sub

编辑

我尝试了 = INDEX(LINEST({3,2,5,7,4,2,1，-2，-5，-1}，{0,1,2,3,4，5,6,7,8,9}，TRUE，TRUE)，3)在工作表中.但是在VBA中，我无法为数组赋值两倍.但是，当在下面的注释中尝试使用@Domenic的建议而没有数据类型时，它就起作用了.

I tried with =INDEX(LINEST({3,2,5,7,4,2,1,-2,-5,-1},{0,1,2,3,4,5,6,7,8,9},TRUE,TRUE),3) in worksheet. But in VBA i couldn't assign values to an array as double. But when tried @Domenic 's suggestion in comments below without data type it worked.

以下作品.

Sub LinEst()
'Dim xVal(1 To 10) As Double, yVal(1 To 10) As Double 'This fails

xVal = Application.Transpose(Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9))
yVal = Application.Transpose(Array(3, 2, 5, 7, 4, 2, 1, -2, -5, -1))

'For polynomial order 3
Range("B20") = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, Array(1, 2, 3)), True, True), 3)

End Sub

这篇关于LinEst用于多项式函数vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！