|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub SimpleLinearRegression()
'获取历史数据
Dim histData As Variant
histData = Range("A2:A16")
'计算自变量和因变量的平均值
Dim xMean As Double, yMean As Double
xMean = Application.WorksheetFunction.Average(histData)
yMean = Application.WorksheetFunction.Average(Range("B2:B16"))
'计算自变量的方差和协方差
Dim xVar As Double, xCovar As Double
Dim i As Integer
For i = 1 To UBound(histData)
xVar = xVar + (histData(i, 1) - xMean) ^ 2
xCovar = xCovar + (histData(i, 1) - xMean) * (Range("B" & i + 1).Value - yMean)
Next i
'计算回归系数和截距参数
Dim beta1 As Double, beta0 As Double
beta1 = xCovar / xVar
beta0 = yMean - beta1 * xMean
'使用回归方程预测未来值
Dim predictedVal As Double
predictedVal = beta0 + beta1 * Range("A17").Value
Range("B17").Value = predictedVal
End Sub
数值在A2:A16中,范围1:16,随便填写
|
|