利用Excel进行线性回归分析汇总.docx
《利用Excel进行线性回归分析汇总.docx》由会员分享，可在线阅读，更多相关《利用Excel进行线性回归分析汇总.docx（33页珍藏版）》请在冰豆网上搜索。
利用Excel进行线性回归分析汇总
利用Excel进行线性回归分析汇
文档内容
1.利用Excel进行一元线性回归分析2.利用Excel进行多元线性回归分析
1.利用Excel进行一元线性回归分析
第一步，录入数据
以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。
录入结果见下图（图1）
1A
■B
C
1D
1
年份
最大积雪深度双氷）
灌溉面积y（千亩）
2
1971
15.2
28.6
3
1972
10.4
19.3
4
1973
21.2
40.5
5
1974
18.6
35.6
6
1975
26.4
48.9
7
1976
23.4
45
8
1977
13.5
29.2
9
1978
16.7
34.1
10
1979
24
46.7
11
1980
19.1
37,4
图1
第二步，作散点图
如图2所示，选中数据（包括自变量和因变量），点击图表向导”图标；或者在插入菜单中打开图表（H）”图表向导的图标为越。
选中数据后，数据变为蓝色（图2）。
迢M1crosoftExcel
-连续山年摄犬积雪深度和灌陽面积的数齬b
］轡文件®編辐囲视图世）插入Q）榕式©工具①数据窗口⑩
DL
&19自
B1
二］二「最大积雪深度莖冰）
IA1
B1C
D
1
年份
最大积雪深度忑（米）灌溉面积y（千亩）
2
1971
15,228,6
3
1972
10,419.3
4
1973
21.240,5
5
1974
18.6兗＞6
6
1975
26.448.9
7
1976
23*445
8
1977
13.529*2
9
1978
16.734.1
10
1979
244&7
11
1980
19.137.4
1J
点击图表向导”以后，弹出如下对话框（图3）:
图3
在左边一栏中选中“XY散点图”点击完成”按钮，立即出现散点图的原始形式
（图4）:
图2
灌溉面积y（千亩）
第三步，回归
观察散点图，判断点列分布是否具有线性趋势。
只有当数据具有线性分布特征时，才能采用线性回归分析方法。
从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。
回归的步骤如下：
1.首先，打开工具”下拉菜单，可见数据分析选项（见图5）：
肝雇槪面积的数菇b
式©）1
工具①数据窗口观帮助电）
尊拼写宦）…FT
2自动画W…
共享工作薄⑩….
保护②►
联机抽作型）►
E
规划求解®…
自定义©…
选项W
向导⑩►
1
数据分折Q）-
更新加我宏卷接ry..
¥
图5
用鼠标双击数据分析”选项，弹出数据分析”对话框（图6）：
图6
2.然后，选择回归”确定，弹出如下选项表（图7）:
图7
进行如下选择：
X、Y值的输入区域（B1:
B11,C1:
C11），标志，置信度（95%）,新工作表组，残差，线性拟合图（图8-1）。
或者：
X、Y值的输入区域（B2:
B11，C2:
C11），置信度（95%），新工作表组，残差，线性拟合图（图8-2）。
注意：
选中数据标志"和不选标志”X、Y值的输入区域是不一样的：
前者包括数据标志：
最大积雪深度x（米）灌溉面积y（千亩）
后者不包括。
这一点务请注意（图8）。
图8-1包括数据标志
图8-2不包括数据标志
3.再后，确定，取得回归结果（图9）。
F
jn
A
g
c
D
E
1
SUHVAfi?
ocrtpur
2
回归经计
3
KultLple
R
0.-989416
4
RSquire
0.-978944
5
Adjusted
RSquare
0.976312
$柘淮误整
L4LB924
7
ID
8
万差分折
9
df
SS
ns
F
.SisnificanceF
卡JOOg|m5D0
MR〔
♦积讥千亩）
■强到千苗）
J102434
氓丈积謝剧血（料
最大玮邑瀑度毗米1LineFitPlot
o1
F值的计算公式和结果为:
R2
1
0.9894162
=371.945a5.32=F0.05,8
（1-R2）n—k—1
显然与表中的结果一样。
T值的计算公式和结果为：
R
t-|
M-R2
2
贾J。
989416）
n—k-1
I=19.286a2.306=t0058
1-0.979416
10-1-1
13
14
Cosfficien'
标■淮i吴理tStatP-valueL伽曲2界
申p业9阴下限95J3
上限座.OH
15
Intercept
2.35643B
LS2T8759C1,239167（J,233363-1.3566^（24
6,57153
-L858654
&5715301
16
最大积雪课廃M试）
1.£12921
的19.2858S5.42E-0S1.596150796
2.029691
1.5961BOS
2.0296^13
17
FESIDUALOUTPUT
IS
观测值
酣湎此L
19
1
寒.912^4
-1.312ESS1
20
2
2L210E2
-1.^102L?
21
3
i0.79036
-0.2釦冊驶
22
电
3&.07677
-0.475T6W
23
5
E0.21755
-1.317554
刖
6
卸.77S79
th2212的IE
25
7
26.83087
Z369127T
26
8
3Z63222
1.46778029
27
9
45.86654
CL83345652
28
10
36.98323
0.41676973
29
毬差平方和
H4
卜h|\回归结果/Sheet1\匪结疊/原蜡删』丘硒數廳hertS/
HI_1
df
17^.8&-1^743,054237L!
HF35P-42Q32E-O8
316.106760^2,QI3345
9T64.961
绘图叩•亀空|自谨田电巴n_o[s]_E4iillo-i^-A.=z7：
gi^\
图9线性回归结果
4.最后，读取回归结果如下：
截距：
a=2.356;斜率：
b=1.813;相关系数：
R=0.989;测定系数：
R^0.979；F值：
F=371.945；t值：
t=19.286;标准离差（标准误差）：
s=1.419;回归平方和：
SSr=748.854；剩余平方和：
SSe=16.107；y的误差平方和即总平方和：
SS仁764.961。
5.建立回归模型，并对结果进行检验
模型为：
?
2.3561.813x
至于检验，R、R2、F值、t值等均可以直接从回归结果中读出。
实际上，R=0.989416>0.632=Ro.O5,8，检验通过。
有了R值，F值和t值均可计算出来。
回归结果中给出了残差（图10）,据此可以计算标准离差。
首先求残差的平方斬2=（yi—?
）2，然后求残差平方和S=5材=1.724+…十0.174=16.107，于是标准离差为
n
g—（yi
-？
）2
=1.419
于是
s1419
0.0388：
：
：
10~15%=0.1~0.15
DW
i=2
）2
22
（-1.9111.313）汕…川（0.417-0.833）
（-1.313）2（-1.911）2：
：
…：
：
0.4172
=0.751
y36.53
观测值
翟溉面积y
残差
残差平方
129.91284
-1.3128381
1.723544
标准离差£
221.21082
-1・9）08圻
3.651222
1.418923905
340.79Q36
T.2903645
0.084312
436.07677
-0,4767697
0.227309
旳的均值
550.21755
-1.317554
1.735949
0.038842702
644.77879
0.22120916
0.048933
726.83087
2.3691277
5.612766
832.63222
1.46778029
2,154379
945.86654
0.83345652
0.69465
1036.98323
0.41676973
0.173697
残差平方和
16.10676
2.013345
图10y的预测值及其相应的残差等
进而，可以计算DW值（参见图11），计算公式及结果为
取0.05，k=1，n=10
i=1
残差1-9
残差2-10
残差之差_]
残差之差的平方
-1.312838
-1,910817
-0.597978889
0.357578752
T.91鬲了
-0.290365
1.620452501
2.625866307
-0.290365
-0.47677
-0.186405232
0.03474691
（显然v=10-1-1=8），查表得d^0.94，du=1.29
显然，DW=0.751-0.47677
-1.317554
-0.840784305
0.706918248
-1.317554
0.2212092
1.538763194
2.367792168
0.2212092
2.3691277
2.147918541
4.613554059
2.3691277
1.4677803
-0.901347407
0.812427149
1.4677803
0.8334565
-0.634323773
0.402366649
0.8334565
0.4167697
-0.416686783
0.173627875
DW®
0.4167697
残差之差的平方和
12,09487812
0.750919
图11利用残差计算DW值
利用Excel快速估计模型的方法:
2.用鼠标指向图4中的数据点列，单击右键，出现如下选择菜单（图12）:
灌溉面积y（千亩）
图12
2.点击添加趋势线？
”，弹出如下选择框（图13）：
图13
3.在分析类型”中选择线性（L）”，然后打开选项单（图14）：
4.
图14
在选择框中选中显示公式（E）”和显示R平方值？
”（如图14）,确定，立即得到回归结果如下（图15）:
图表标题
*灌溉面积y（千亩）
—线性（灌溉面积
—y（千亩））-
图15
在图15中，给出了回归模型和相应的测定系数即拟合优度。
顺便说明残差分析：
如果在图8中选中残差图（D）”则可以自动生成残差图（图12）
XVariable1ResidualPlot
1
差0
残0
-1
-2
-3
11
♦
♦
*1♦1
»♦
（一
5
10
15202530
•«
♦
XVariable1
图16
回归分析原则上要求残差分布是无趋势的，如果在图中添加趋势线，则趋势线应该是与轴平行的，且测定系数很小。
事实上，添加趋势线的结果如下（图17）：
XVariable1ResidualPlot
1差0残0
-2
-3
-1
XVariable1
图17可见残差分布图基本满足回归分析的要求。
预测分析
虽然DW检验似乎不能通过，但这里采用的变量相关分析，与纯粹的时间序列分析不同（时间序列分析应该以时间为自变量）。
从残差图看来，模型的序列似乎并非具有较强的自相关性，因为残差分布相当随机。
因此，仍有可能进行预测分析。
现在假定：
有人在1981年测得最大积雪深度为27.5米，他怎样预测当年的灌溉面积？
下面给出Excel2000的操作步骤：
2.在图9所示的回归结果中，复制回归参数（包括截距和斜率），然后粘帖到图1所示的原始数据附近；并将1981年观测的最大积雪深度27.5写在1980年之后（图18）。
A
B
C
D
|E
■F
1［年份
最大积雪深度蚊米）灌漑面积vT千亩?
|计算値
Coefficierits
2
1971
1乩2
28*6[
llntercept
2.356437929
3
1972
10.4
19.3
懐大积雪深度米）
1.812921065
4
1973
21.2
465
5
1974
1比6
35.6
6
1975
26.4
逗9
7
1976
23.4
45
8
1977
13.5
29.2
9
1978
16.7
34.1
10
1979
24
迢7
11
1980
19.1
37,电
12
1981
27,5
图18
2.将光标至于图18所示的D2单元格中，按等于号丄”，点击F2单元格（对应于截距a=2.356…），按F4键，按加号‘牛”，点击F3单元格（对应于斜率b=1.812…），按F4键，按乘号“*”点击B2单元格（对应于自变量xi），于是得到表达式“=$F$2+$F$3*B2
（图19），相当于表达式？
i二a•b*xi，回车，立即得到？
i=29.9128，即佃71年灌溉面积的计算值。
1A
B
c
D
■E
F
1
年份
最大积雪深度X杓滙漑面积吭千亩）计算値
Coefficients
2
1971
!
_15.2：
28.6|-SF$MF$3*B2|
2.356437929
3
1972
10.4
19.3
最大积雪深庫珂米）
1.812921065
4
1973
21.2
40.5
5
1974
1B.6
35.6
6
1975
26.4
48.9
7
1976
23.4
电5
8
1977
13.5
29.2
9
1978
16,7
34.1
10
pL听9
24
46.7
11
1980
19,1
37.4
12
1981
27.5
图佃
3.将十字光标标至于D2单元格的右下角，当粗十字变成细十字以后，按住鼠标左键,往下一拉，各年份的灌溉面积的计算值立即出现，其中1981年对应的D12单元格的
52.212
即我们所需要的预测数据，即有和=52.212千亩（图20）。
La
C
D
1E
F
1
年份
最大积雪探度W米）灌溉面积y（千甸计算值
Coefficients
2
1971
15.2
28.6
29.913
Intercept
2.35643792勺
3
1972
10.也
19.3
2L211
最大积雪深度盟（米）
1.812921065
4
1.973
2L2
40.5
40.79
5
1974
1S.6
35.6
36.077
6
1975
26.4
48”9
50.218
7
1976
23.也
45
44.779
8
1977
13.5
29.2
26.831
9
1978
16.7
汕1
32,632
10
1979
迢7
4工867
11
1980
19.1
37.4
36.983
12
1981
27,5
52.212
图20
4.进一步地，如果可以测得1982年及其以后各年份的数据，输入单元格B13及其下面的单元格中，在D13及其以下的单元格中，立即出现预测数值。
例如，假定1982年的最
大积雪深度为x12=23.7米，可以算得？
12=45.323千亩；1983年的最大积雪深度为
X13=15.7，容易得到?
13=31.819千亩（图21）
LA
B
C
D
E
F1
1
年份
最丈积誓深度反米）灌漑面积yC千助计算值
Coefficients
2
1971
15（2
2S,5
29.913
Irttexcept
2.356437929
3
19T2
10.4
19.3
21.211
最大积雷深度u侏）
1.812921065
4
1973
21.2
40.5
40,79
5
1974=
18.6
35.6
33.077
6
1975
26.4
48.9
50.218
7
1976
23.4
45
44.779
S
1977
13.E
29.2
26.831
9
19T8
15.7
34+1
32.532
10
1979
24
46.7
45.967
11
1980
19.1
37.4
36.983
12
1981
27.5
52.212
13
1932
23.7
45.323
14
1983
15.7
30.819
图21预测结果（1981—1983）
最后大家思考一下为什么DW检验对本例中的问题未必有效?
2.利用Excel进行多元线性回归分析
【例】某省工业产值、农业产值、固定资产投资对运输业产值的影响分析。
Excel2000的操作方法与一元线性回归分析大同小异：
第一步，录入数据（图1）。
A
B
0
D
E
■F」
1
序号
年份
工业产值X1
农业产值吃
固定资产投资泊
运输业产值y
2
1
1970
57.82
27.05
14.54
3.09
3
2
1971
58.05[
28.89
16.83
3.4
4
3
1972
59.15
33.02
12.26
3.88
5
4：
1973
63,83
35,23
12.87
3,9
6
5
1974
皈361
2L94
11.65
3.22
7
6
1975
67.261
32.95
12.87
3.76
8
一丫
1976
66.92
30,35
10.8
3.59
9
8
1977
67.79
38.7
10.93
£03]
10
1973
75.65
47.99
71
4.34
U
10
1979
80.571
54.18
17,56
4.65
12
11
19S0
79.02
58.73
20.32
生.78
13
12
1981
30,52
59.85
18.67
5.04
14
13
1932
86.881
弘57
25.34
5.591
15
14
1983
95.48
70.97
25.06
6.01
16
15
1984
109.71
S1.54
29.69
7.03
17
16
1985
126.51
9L01
43.86
10.031
18
17
1986
138.89
103.23
4乩9
10.83
图1录入的原始数据
第二步，数据分析
1.沿着主菜单的工具（T）”宀数据分析
（D）…”路径打开数据分析”对话框，选择回归”然后确定”弹出回归”分析对话框，对话框的各选项与一元线性回归基本相同（图2）。
下面只说明x值的设置方法：
首先，将光标置于“X值输入区域（X）”中（图2）；
然后，从图1所示的C1单元格起，至E19止,选中用作自变量全部数据连同标志，这时“X值输
入区域（）”的空白栏中立即出现
“$C$1:
$E$19”一当然，也可以通过直接在“X值输入区域（X）”勺空白栏中输入“$C$1:
$E$19的办法实现这一步骤。
注意：
与一元线性回归的设置一样，这里数据范围包括数据标志：
工农固定运
业产值业产值资产投资输业产
x1x2x3值y
故对话框中一定选中标志项（图3）。
如果不设标志”项，则“X值输入区域（X）”勺空白栏中应为“$C$2:
$E$19；“Y值输入区域（Y）”勺空白
栏中则是“$F$2:
$F$19”否则，计算结果不会准确。
图2x值以外的各项设置
图3设置完毕后的对话框（包括数据标志）
2.完成上述设置以后，确定，立即给出回归结果。
由于这里的输出选项”选中了新工作表组
（P）”（图3），输出结果在出现在新建的工作表上（图4）。
从图4的输出摘要（S
升级会员 