编程技术网

关注微信公众号,定时推送前沿、专业、深度的编程技术资料。

 找回密码
 立即注册

QQ登录

只需一步,快速开始

极客时间

LinEst用于多项式函数vba:LinEst for polynomial function vba

alekzg 集合 2022-5-11 10:42 20人围观

腾讯云服务器
LinEst用于多项式函数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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程技术网(www.editcode.net)!

腾讯云服务器 阿里云服务器
关注微信
^