关于Excel多元回归估算待定系数的操作与分析

@Silence.life同学：你在来信中提出：

已知模型函数表达式为：

y = b₀ + b₁x₁ + b₂x₂ + b₃x₃ + b₄x₁x₂ +b₅x₁x₃ + b₆x₂x₃ + b₇x₁² + b₈x₂² + b₉x₃²

并取得模拟数据如下表：

如何根据上述模拟数据，确定b_0~ b₉？

以上数据仅为自变量的一次项的值，还需要使用这几个值求得混合项及完全平方的二次项的值，然后再使用Excel的统计函数LINEST进行多元回归分析估计求得上述公式中的10个待定系数b_0~b₉_。

在上表基础上计算混合项及完全平方的二次项，增补后的表格公式如下：

在单元格区域K1_~T1中，分别键入b₉、b₈、b₇、b₆、b₅、b₄、b₃、b₂、b₁、b₀如下图：

预先设定参数值的名称是为了在运算完毕后，各项参数顺序不会搞错，因为最后自动计算后的结果的顺序正好与假设的函数式中的参数编号顺序相反。

很重要的是：选定单元格区域K2：T6，准备存放计算结果。

注意：一定要在选定区域后再做下一步。

插入函数，在函数分类列表框中选择“统计”选项，在函数名列表框中选择“LINEST”函数，确定后弹出LINEST对话框如下：

在Known_y’s文本框，点击右侧折叠按钮，选取函数Y数据所在单元格区域J2：J21，选完后再单击折叠按钮返回，这过程也可以直接在Known_y’s文本框中输入J2：J21；

在Known_x’s文本框，点击右侧折叠按钮，选取自变量数据所在单元格区域A2：I21，选完后再单击折叠按钮返回，这过程也可以直接在Known_x’s文本框中输入A2：I21；

在Const和Stats两个文本框中都输入TRUE即可。

此时不要回车确定，而是使用组合键“Shift+Ctrl+Enter”，就可以得到以下结果：

这是一个统计结果，第一行的数据就是各项系数和常数项。其余数据可见下面说明（左侧将数据分行排列并给以标记，右侧按单元格颜色对应给予注释）：

b9～b1：各项系数值；b0：常数项；

Se_i （i=9～1）：系数b9～b1的标准误差；Se₀：常数项的标准误差；

r²：判定系数 r²=0.834326589001093；

Se_y：y估计值的误差 Se_y=293.728298

F：统计量 F=5.59552397542333

d_f：自由度 d_f=10

SS_reg：回归平方和

SS_resid：残差平方和

建立所估计的回归方程是：

判定系数r²为0.83 ，说明自变量与Y值有较大的相关性，说明回归方程有较大的相关性。可以通过进一步分析F统计量来确定具有如此高的r²的结果偶然发生的可能性。

假设事实上变量之间不存在相关性，但20组数据作为样本进行统计分析却导致了很强的相关性。显著性水平α表示得出这样的相关性结论错误的概率。如果F观测统计值大于临界值，表明变量间存在相关性。

如果α值为0.01，k是回归分析中的自变量个数，n是数据点的个数，在此处：

K=9；n=20。于是有：

f₁=k=9，f₂=n-(k+1)=20-(9+1)=10，

而临界值F_0.01（f₁, f₂）=F_0.01（9,10）=4.94，统计量F=5.5955，大于临界值，因此该方程可以用于对函数进行估算。

也可以使用Excel中的数据分析工具中的回归工具对给出数据进行多元回归的分析，不仅可以得出与以上相同的回归方程，还可以对其中的回归系数作进一步的数据分析，这将另外行文加以说明。