excel做回归分析的原理和实例

excel做回归分析

Excel做线性回归分析基本原理及实例

一、原理

1、回归分析原理

由一个或一组非随机变量来估计或预测某一个随机变量的观测值时，所建立的数学模型及所进行的统计分析，称为回归分析。按变量个数的多少，回归分析有一元回归分析与多元回归分析之分，多元回归分析的原理与一元回归分析的原理类似。按变量之间关系的形式，回归分析可以分为线性回归分析和非线性回归分析。

2 、回归分析的主要内容

回归分析的内容包括如何确定因变量与自变量之间的回归模型；如何根据样本观测数据，估计并检验回归模型及未知参数；在众多的自变量中，判断哪些变量对因变量的影响是显著的，哪些变量的影响是不显著的；根据自变量的已知值或给定值来估计和预测因变量的值。

3、利用图表进行分析

例23-1：某种合成纤维的强度与其拉伸倍数之间存在一定关系，图23-1所示（“线性回归分析”工作表）是实测12个纤维样品的强度y与相应的拉伸倍数x的数据记录。试求出它们之间的关系。

excel做回归分析

（1）打开“线性回归分析”工作表。

（2）利用“图表向导”绘制 “XY散点图”。

（3）在“XY散点图”中绘制趋势回归直线，如图23-2所示。

excel做回归分析

二、 Excel中的回归分析工作表函数

（1）截距函数

语法：INTERCEPT(known_y's,known_x's)

其中：Known_y's为因变的观察值或数据集合，Known_x's为自变的观察值或数据集合。

（2）斜率函数

语法：SLOPE(known_y's,known_x's)

其中：Known_y's为数字型因变量数据点数组或单元格区域；Known_x's为自变量数据点集合。

（3）测定系数函数

语法：RSQ(known_y's,known_x's)

其中：Known_y's为数组或数据点区域，Known_x's为数组或数据点区域。

（4）估计标准误差函数

语法：STEYX(known_y's,known_x's)

其中：Known_y's为因变量数据点数组或区域，Known_x's为自变量数据点数组或区域。

3 利用工作表函数进行回归分析

例23-2:在某大学一年级新生体检表中随机抽取10张，得到10名大学生的身高（x）和体重（y）的数据，如图23-3（“身高体重”工作表）所示。

用Excel提供的工作表函数进行相关计算。

excel做回归分析

（1）分别计算“截距”、“斜率”、“测定系数”、“估计标准误差”，如图23-3所示。

（2）根据计算结果可以得出直线方程：

三、Excel回归分析工具

1 、回归分析工具的主要内容

回归分析工具是通过对一组观察值使用“最小平方法”进行直线拟合，以分析一个或多个自变量对单个因变量的影响方向与影响程度的方法。它是Excel中数据分析工具的一个内容。在“回归”对话框中主要包括也许内容：

Y值输入区域、X值输入区域、标志、置信度、常数为零、输出区域、新工作表组、新工作簿、残差、标准残差、残差图、线形拟合图、正态概率图。

2 回归分析工具的应用

excel做回归分析

例23-3：以例23-2资料为例，利用回归分析工具进行回归分析。

（1）打开“身高体重”工作表。

（2）在“数据分析”中的“分析工具”列表中选择“回归”，项，进行相关设置，输出结果，如图23-4所示。

3 回归分析工具的输出解释

Excel回归分析工具的输出结果包括3个部分：

(1)回归统计表

回归统计表包括以下几部分内容：

①Multiple R（复相关系数R）：是R2的平方根，又称为相关系数，用来衡量变量x和y之间相关程度的大小。本例中R为0.825652，表示二者之间的关系是高度正相关。

②R Square（复测定系数R2）

：用来说明自变量解释因变量

excel做回归分析

变差的程度，以测定因变量y的拟合效果。

③Adjusted R Square（调整复测定系数R2）：仅用于多元回归才有意义，它用于衡量加入独立变量后模型的拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的R2也要增大，修正的R2仅用于比较含有同一个因变量的各种模型。

④标准误差：用来衡量拟合程度的大小，也用于计算与回归相关的其他统计量，此值越小，说明拟合程度越好。

⑤观测值：用于估计回归方程的数据的观测值个数。

（2）方差分析表

方差分析表的主要作用是通过F检验来判断回归模型的回归效果。表中“回归分析”行计算的是估计值同均值之差（-）的各项指标；“残差”行是用于计算每个样本观察值与估计值之差（-）的各项指标；“总计”行用于计算每个值同均值之差（-）的各项指标。第二列df是自由度，第三列SS是离差的平方和，第四列MS是均方差，它是离差平方和除以自由度，第五列是F统计量，第六列Significance F是在显著性水平下的Fα的临界值

3．回归参数表

回归参数表主要用于回归方程的描述和回归参数的推断。

如图23-4所示的表中最下面三行中的第二行和第三行分别是β0（截距）β1（斜率）的各项指标。对于大多数回归分析来讲，关注β1要比β0重要。第二列是β0和β1的值，据此可以写出回归方程。第三列是各个回归系数的P值（双侧），最后是给出（β0和β1）的95%

excel做回归分析

的置信区间的上下限

例23-4：有一个工厂会计部门在估计每月管理费y时，用工人的劳动日数x1与机器的开工台数x2作自变量，现将当年10个月的数据搜集起来，如图23-5（“多元回归分析”工作表）所示，估计y对x1与x2的线性回归方程(α=0.05)。

（1）打开“回归”对话框。

（2）在“Y值输入区域”中输入“D1:D11”，在“X值输入区域”中输入“B1:C11”；选择“标志”，置信度默认；在“输出选项”中选择“输出区域”，在其右边输入“A12”，单击“确定”按钮输出结果，如图23-6所示。

excel做回归分析

excel做回归分析

本文来源：https://www.bwwdw.com/article/ity4.html