一元线性回归软件(EXCEL)实现

81 8111.94244 Coefficients 标准误差 1.56969205 0.01207031 t Stat 31.90158406 -11.52197958
F 132.7560134
Significance F 1.13931E-18
P-value 2.94532E-47 1.13931E-18
70
60
50
坐标轴标题
R² = 0.624
每加仑汽油行驶里程（MPG） 线性 (每加仑汽油行驶里程（MPG）)
30
20
10
0 0 50 100 150 200 250 300 350
• 由图中可知，拟合的直线是y=-0.1391x+50.076， 由图中可知，拟合的直线是y=-0.1391x+50.076， y= R2的值为0.624。 的值为0.624 R2的值为0.624。 • 因为R2 值不是很大，所以这是一个线性特征 因为R2 值不是很大， 不是很明显的实验模型 即说明拟合直线只 实验模型， 不是很明显的实验模型，即说明拟合直线只能够 以大于62.4%地解释、涵盖了实测数。 62.4%地解释 以大于62.4%地解释、涵盖了实测数。 • 为了进一步使用更多的指标来描述这一个模 我们使用数据分析中的“回归” 型，我们使用数据分析中的“回归”工具来详细 分析这组数据。
一元线性回归软件（EXCEL）实现 一元线性回归软件（EXCEL）
七十七队： 王玮 指导教员：宋爱斌
一 二 三 四
概述 一元线性回归的基本概念 软件（EXCEL）实现过程 软件（EXCEL） 总结
一、概述
在数据分析中， 在数据分析中 ， 对于成对成组数据的 拟合是经常遇到的。 很多专业软件都可以 拟合是经常遇到的 。 很多专业软件 都可以 解决此类问题， 比如数学中常见的MATLAB 解决此类问题 ， 比如数学中常见的 MATLAB 它们虽很专业，但其实使用Excel Excel就完 等。它们虽很专业，但其实使用Excel就完 全够用了。我们已经知道在Excel Excel自带的数 全够用了。我们已经知道在Excel自带的数 据库中已有线性拟合工具， 据库中已有线性拟合工具 ， 但是它还稍显 单薄， 下面我们来尝试使用较为专业的拟 单薄 ， 下面 我们来尝试使用较为专业的拟 合工具来对此类数据进行处理。 合工具来对此类数据进行处理。
线性拟合图
70 60
50
行驶里程
40 Y 预测 Y
30
20
10
0 0 50 100 150 汽车马力 200 250 300 350
•
在线性拟合图中可以看到， 在线性拟合图中可以看到，不但有根 据要求生成的数据点， 据要求生成的数据点，而且还有经过拟和 处理的预测数据点， 处理的预测数据点，拟合直线的参数会在 数据表格中详细显示。 数据表格中详细显示。
-0.1150532 -0.163094531
“回归”工具为我们提供残差图、线性拟合图： 回归”工具为我们提供残差图、线性拟合图：
残差图
25
20
15
10 残差
5
0 0 -5 50 100 150 200 250 300 350
-10
-15
•
残差图是有关于实际值与预测值之间 残差图是有关于实际值与预测值之间 实际值 差距的图表，如果残差图中的散点在中轴 差距的图表，如果残差图中的散点在中轴 上下两侧零乱分布， 上下两侧零乱分布，那么拟合直线就是合 理的，否则就需要重新处理。 理的，否则就需要重新处理。
分析结果
SUMMARY OUTPUT 回归统计 Multiple R R Square Adjusted R Square 标准误差 观测值 0.789925583 0.623982426 0.619282206 6.174780275 82
方差分析
回归分析 残差 总计
Y=-0.1391+50.075和 df SS MS 前面散点图直线拟合 1 5061.70952 5061.709523 80 3050.23292 38.12791145 的结果一致
ˆ ˆ α = y −βx
三、软件（EXCEL）实现过程 软件（ 软件 EXCEL）
本功能需要使用Excel扩展功能， 本功能需要使用Excel扩展功能，如果 Excel扩展功能 您的Excel尚未安装数据分析， 加载“ Excel尚未安装数据分析 您的Excel尚未安装数据分析，需加载“分析数据 加载成功后，可以在“数据” 库”。加载成功后，可以在“数据”菜单中看到 数据分析” “数据分析”选项
• 在Excel中输入数据： Excel中输入数据 中输入数据：
• 分析： 这是一个很典型的线性拟合问题， 分析： 这是一个很典型的线性拟合问题， 手工计算就是采用最小二乘法求出拟合直 线的待定参数，同时可以得出R的值， 线的待定参数，同时可以得出R的值，也就 是相关系数的大小。 Excel中 是相关系数的大小。在Excel中，可以采用 先绘图再添加趋势线的方法完成前两步的 要求。 要求。
i =1 i =1 i =1
为使 Q (α , β ) 值到达极小，根据极值原理， 值到达极小，根据极值原理， 求偏导数， 只要将上式分别对 α , β 求偏导数，并令 其等于零，即可求得 α , β 之值，这就是 其等于零， 之值， 最小二乘法原理。 最小二乘法原理。
根据最小二乘法， 根据最小二乘法，可以得到一组正 最小二乘法 规方程组，对方程组求解， 规方程组，对方程组求解，即可得到回 ˆ 的计算式： n 归系数α ,βˆ 的计算式： n (∑xi )(∑ yi ) n n i =1 ( xi − x )( yi − y) ∑xi yi − i=1 ∑ Lxy n i =1 i =1 ˆ β= = = n n Lxx 2 2 (∑xi ) ∑(xi − x ) n 2 i =1 xi − i=1 ∑ n i =1
• 选择成对的数据列，将它们使用“X、Y散 选择成对的数据列，将它们使用“X、 “X 点图”制成散点图。 点图”制成散点图。
散点图
每加仑汽油行驶里程（ 每加仑汽油行驶里程（MPG） ）
70 60
50
40
30
20
10
0 0 50 100 150 200 250 300 350
直线拟合
每加仑汽油行驶里程（ 每加仑汽油行驶里程（MPG） ）
ˆ y i = αˆ + βˆ x i
α
ˆ y i 与yi之间的偏差称为残差，用ei表示， 之间的偏差称为残差， 表示， 即：
ˆ ei = yi − yi
当各残差平方和最小时， 当各残差平方和最小时，回归方程与试 验值的拟合程度最好。 验值的拟合程度最好。
令： n n n 2 2 2 ˆ Q (α , β ) = ∑ ei = ∑ ( yi − yi ) = ∑ [ yi − (α + β xi )]
Lower 95% 46.9518761 -0.163094531
Upper 95% 53.1994494
下限 95.0% 46.9518761
上限 95.0% 53.19944943 -0.115053172
Intercept X Variable 1
50.07566277 -0.139073852
谢谢！请批评指正！
• 案例：下表是82辆汽车的马力(HP)与 案例：下表是82辆汽车的马力(HP) 82辆汽车的马力(HP)与 每加仑汽油所行使的里程（MPG） 每加仑汽油所行使的里程（MPG）,试 拟合模型 MPG=f(HP)+e 建立标准曲线， 建立标准曲线，并且对此曲线进行评 给出残差等分析数据。 价，给出残差等分析数据。
二、一元线性回归基本概念
一元线性回归方程的建立
一元线性回归分析（ regression） 一元线性回归分析（linear regression） 又称直线拟合， 又称直线拟合 ， 是处理两个变量之间关系的 最简单模型。 最简单模型。
设有一组试验数据， 试验值为x i=1 设有一组试验数据 ， 试验值为 xi ， yi （ i=1 ， 其中ｘ为自变量， 为应变量， 2，…，n），其中ｘ为自变量，ｙ为应变量，若ｘ， 符合线性关系，或已知经验公式为直线形式， ｙ符合线性关系，或已知经验公式为直线形式，都 可拟合为直线方程， 可拟合为直线方程，即 上式为变量ｘ 的一元线性回归方程， 上式为变量 ｘ ， ｙ 的一元线性回归方程 ， 式 ， β 称 回 归 系 数 （ regression coefficient)； 是对应自变量ｘ coefficient) ； 是对应自变量 ｘ i 代入回归方程 的计算值，称为回归值 回归值。 的计算值，称为回归值。
汽车马力（ HP） 汽车马力 （ HP ） 49 55 55 70 53 70 55 62 62 80 73 92 92 73 66 73 78 92 78 90 92 74 95
每加仑汽油行驶里程（MPG） 每加仑汽油行驶里程 （ MPG ） 65.4 56 55.9 49 46.5 46.2 45.4 59.2 53.3 43.4 41.4 40.9 40.9 40.4 39.6 39.3 38.9 38.8 38.2 42.2 40.9 40.7 40