如何用VBA的WorksheetFunction.LinEst正确计算R²？拟合差异解惑

问题背景 #

我编写了VBA代码，对基于公式 Y = 2*X^3 - 3*X^2 + 4*X - 5 的数据进行线性、二阶、三阶多项式拟合，X取值为1-10，对应Y值为-2,7,34,91,190,343,562,859,1246,1735。拟合系数与Excel图表趋势线一致，但R²结果差异极大：

• 线性拟合：趋势线R²=0.8483，LinEst返回值=27.17940397
• 二阶多项式拟合：趋势线R²=0.9962，LinEst返回值=1.828348201
• 三阶多项式拟合：趋势线R²=1，LinEst返回值=1.43117E-15

疑问：为何出现这种差异？R²为何大于1？如何通过WorksheetFunction.LinEst正确计算R²？

原代码 #

Sub Data_regression()

Dim X() As Double   ' X values
Dim Y() As Double   ' Y values
Dim Z() As Variant  ' Regression statistics

Dim number_datapoints As Integer
Dim i, r As Integer

' Insert data in columns A and B
' Column A contains X values
' Column B contains Y values
' First value should start in row 1

With ActiveSheet

' Get number of datapoints
r = 1
While .Cells(r + 1, 1).Text <> ""
    r = r + 1
Wend
number_datapoints = r

' ********* LINEAR REGRESSION *********

' Define the ranges for X and Y data
ReDim X(number_datapoints - 1, 0)
ReDim Y(number_datapoints - 1, 0)
r = 1
For r = 1 To number_datapoints
    i = r - 1
    Y(i, 0) = .Cells(r, 2)
    X(i, 0) = .Cells(r, 1)
Next r

' Perform regression using LINEST function
Z = WorksheetFunction.LinEst(Y, X, True, True)

' Write the results back to the worksheet
r = 1
.Cells(r, 4).Value = "y = m.x + c"
.Cells(r + 1, 4).Value = "slope (m):"
.Cells(r + 1, 5).Value = Z(1, 1)
.Cells(r + 2, 4).Value = "Intercept (c):"
.Cells(r + 2, 5).Value = Z(1, 2)
.Cells(r + 3, 4).Value = "R-squared:"
.Cells(r + 3, 5).Value = Z(2, 1)  ' 错误：此处取错了R²的位置

' ********* QUADRATIC REGRESSION *********

ReDim Preserve X(number_datapoints - 1, 1)

For i = 0 To number_datapoints - 1
    X(i, 1) = X(i, 0) ^ 2
Next i

' Perform regression using LINEST function
Z = WorksheetFunction.LinEst(Y, X, True, True)

' Write the results back to the worksheet
r = 6
.Cells(r, 4).Value = "y = a.x^2 + b.x + c"
.Cells(r + 1, 4).Value = "a:"
.Cells(r + 1, 5).Value = Z(1, 1)
.Cells(r + 2, 4).Value = "b:"
.Cells(r + 2, 5).Value = Z(1, 2)
.Cells(r + 3, 4).Value = "c:"
.Cells(r + 3, 5).Value = Z(1, 3)
.Cells(r + 4, 4).Value = "R-squared:"
.Cells(r + 4, 5).Value = Z(2, 1)  ' 错误：此处取错了R²的位置

' ********* THIRD DEGREE POLINOMIAL REGRESSION *********

ReDim Preserve X(number_datapoints - 1, 2)

For i = 0 To number_datapoints - 1
    X(i, 2) = X(i, 0) ^ 3
Next i

' Perform regression using LINEST function
Z = WorksheetFunction.LinEst(Y, X, True, True)

' Write the results back to the worksheet
r = 12
.Cells(r, 4).Value = "y = a.x^3 + b.x^2 + c.x + d"
.Cells(r + 1, 4).Value = "a:"
.Cells(r + 1, 5).Value = Z(1, 1)
.Cells(r + 2, 4).Value = "b:"
.Cells(r + 2, 5).Value = Z(1, 2)
.Cells(r + 3, 4).Value = "c:"
.Cells(r + 3, 5).Value = Z(1, 3)
.Cells(r + 4, 4).Value = "d:"
.Cells(r + 4, 5).Value = Z(1, 4)
.Cells(r + 5, 4).Value = "R-squared:"
.Cells(r + 5, 5).Value = Z(2, 1)  ' 错误：此处取错了R²的位置

End With

End Sub

问题原因 #

你错误引用了LinEst返回数组中R²的位置：
当LinEst的第四个参数stats=True时，返回的是一个5行×(自变量列数+1)列的多维数组，其中R²（决定系数）位于第三行第一列（即Z(3,1)）。而你之前取值的Z(2,1)是对应系数的标准误差，Z(5,1)是回归平方和（SSR），这些都不是R²，因此出现了大于1或极小值的异常结果。

修正方法 #

将所有获取R²的代码行，从Z(2,1)改为Z(3,1)即可。修正后的关键代码片段如下：

线性拟合部分 #

.Cells(r + 3, 4).Value = "R-squared:"
.Cells(r + 3, 5).Value = Z(3, 1)  ' 正确的R²位置

二阶多项式拟合部分 #

.Cells(r + 4, 4).Value = "R-squared:"
.Cells(r + 4, 5).Value = Z(3, 1)  ' 正确的R²位置

三阶多项式拟合部分 #

.Cells(r + 5, 4).Value = "R-squared:"
.Cells(r + 5, 5).Value = Z(3, 1)  ' 正确的R²位置

LinEst返回数组的统计量位置说明 #

为避免后续出错，明确stats=True时返回数组的核心统计量位置：

• 第1行：回归系数（从最高次项到常数项，比如三阶多项式依次是X³、X²、X、常数项的系数）
• 第2行：对应系数的标准误差
• 第3行：第1列=R²，第2列=Y的标准误差
• 第4行：第1列=F统计量，第2列=自由度
• 第5行：第1列=回归平方和（SSR），第2列=残差平方和（SSE）

修正后，LinEst返回的R²将与Excel趋势线的结果完全一致。

内容的提问来源于stack exchange，提问作者Daniel Louw