用Excel做线性回归分析

用Excel进行一元线性回归分析

Excel功能强大利用它的分析工具和函数可以进行各种试验数据的多元线性回归分析。

本文就从最简单的一元线性回归入手.

在数据分析中，对于成对成组数据的拟合是时常遇到的，涉及到的任务有线性描述，趋势

预测和残差分析等等。不少专业读者遇见此类问题时往往寻求专业软件，比如在化工中时常用

到的Origin和数学中常见的MATLAB等等。它们虽很专业，但其实使用Excel就彻底够用To

我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来

尝试使用较为专业的拟合工具来对此类数据进行处理。

文章使用的是2000版的软件，我在其中的一些步骤也添加了2022版的注解.

1利用Excel2000进行一元线性回归分析

首先录入数据.

以连续i。年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图

1）“

ABCD

1年份最大积雪深度欢溉面积y］千

2197115.228.6

3197210.419.3

4197321.240.5

5197413.635.t

6197526.448.0

7197623.445

8197713.529.2

9197816.734.1

1019792446.7

1119800137.4

图1

第二步，作散点图

如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在“插

入”菜单中打开“图表（H）（excel2022）”。图表向导的图标为座。选中数据后，数据变

为蓝色（图2）0

1

HicrosoftEKO,1-连续1□年最大积雪谏度和潜既面四的数据h

蟹I文件⑥舍甫辑⑥视图。插A（1）格式袈工具（1）教、商口地）

□Hdi•S匾茂金1U

Bls最大积雪深度招0

ABCD

1年份最大积雪沫度就米）^溉面积四平

2197115.228.6

3197210.419.3

4197321.240.5

5197413.63©6

6197526.448.9

7197623.445

8197713.S29.2

g197816.734.1

1019792446.7

11193019.137.11

图2

点击“图表向导”以后，弹出如下对话框（图3）：

图表问导-4步骤之1-图表类型

标推类型自定义类型

子图表类型CD

l

a

毋

理

胖

嘉

七

饼

一

也

n比较成对的数值

莅下不放可登看示例（X）

回取销I■＜上步代）「下一步》I完成一1

图3

在左边一栏中选中“XY散点图”，点击“完成”按钮，即将浮现散点图的原始形式（图

4）:

2

灌溉面积y（千亩）

第二步，回归

观察散点图，判断点列分布是否具有线性趋势。惟独当数据具有线性分布特征时，才能

采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。

回归的步骤如卜：

⑴首先，打开“工具”下拉菜单，可见数据分析选项（见图5）（2022为“数据”右端的

''数据分析〃）：

忖灌潮面积的数据b

图5

用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）:

3

08-1包括数据“标志”

图8-2不包括数据“标志”

⑶再后，确定，取得回归结果（图9）0

5

IIdfSSHSFSisnificanceF\*

12行，J分析1740.05”74s.8A2371.94&SE.42032E-08

二+8|l5.106762.OI33A5

Id总计y764.961

15__________________________________________________________________________________________________________________

16__________________Coefficien-标准误差ISialP-alueLower95H,死er9网F限沸.此限檄懒

17Iniercepi2.35&438L82元而""12891670,233363-1.85065424&闵153-1.85865&57I豆

13最大积雪大度H米)L0I29210。9d00219.285005,42E-081.5%最07%2.02人6911596151029691

19

20

21

22RESIDUALOUTPUT

23

增刚一租溉面膈

25129.91284-1.312B4

26221.21082I-1.9I0S2

2734().79036,29036

23436.07677|-0A7677

算550.31755-L3175F

Of；A.«'»m秣I

H・枷回可结果iShuctlY叩|谢果Nf填网可据/反阳毂｛ShB十3..

图9线性回归结果

⑷最后，读取回归结果如下：

截距：“€2.356；斜率：叱1.813；相关系数：/?=0.989；测定系数：

Rz€0.979；夕值：371.945o

⑸建立回归模型，并对结果进行检验

模型为：云€2.356+1.813X

至于检验，R、型和尸值可以直接从回归结果中读出。实际上，H£0.989416>0.632=R

,检验通过。有了4值，尸值和-值均可计算出来。，、值的

0.05,8

计算公式和结果为：

R?0.9894162

F€---------------€lu-------------€371.945>5.32=F

------(1—Ri)(1—0.9894162)0.05.8

n—k-llQ—1—1

显然与表中的结果一样。£值的计算公式和结果为：

R0Q7Q41R£1。2Rfi

・・1・R2：1—0.979416

\n-k/-1io-1-i

回归结果中给出了残差(图10),据此可以计算标准离差。首先求残差的平方

6

,,2€(y-§)2,然后求残差平方和5金..0,,2€1.724+...+0.174=16.107,于是标准

iii

离差为

2(1

7

iA7

=1.419

于是

1,419=0.0388<1075*=0.1〜0.15

—36.52

观测值董溉面积残差残芥平方

129.91284-1.31283811.723544标准窗差S

221.21082-1.910S173.6512221.41S923905

340.79036-0.29036450.084312

436.07677-0.47676970.227309畋的均值

550.21755-1.3175541.7359490.03S342702

644.778790.221209160.048933

726.E30872.36912775.612766

S32.632221.467780292.15437”

945.E56540.833456520.69465

1036.933230.416769730.173697

残差平方和16.106762.013345

图10y的侦测值及其相应的残差等

进而，可以计算DW值（参见图11）,计算公式及结果为

,(---------)2

DW_Z*_(—1.911+1.313)2+...+(0.417—0.833)2

DW=-A=2_-------------=------------------------------------------------------------=0.751

乙2一(—1.313”+(—1.911)2+…+0.4172

1

Q(显然V-10—1—1=8),查表得d=0.94,d=

飘购书仇@05，1可见有序列正相关，预测的懒冷人怀疑。

U

10残差之差的本方

9卷210残差之差

-1.312838-1.910817-0.5979788890.357578752

-1.910817-0.2903651.6204525012.625866307

-0.290365-0.4花”-0.1864052320.03474691

-0.47677-1.317554-0.8407843050.706918248

-1.3175540.22120921.5387631942.367792168

0.22120922.36912772.1479185414.613554059

2.36912771.4677803-0.9013474070.812427149

1.46778030.8334565-0.6343237730.402366649

0.83345650.4167697-0.4166367330.173627375DWffi

0.4167697景差之差的平方和12.094878120.750919

图11利用残差计算DN值

最后给出利用Excel快速估计模型的方法:

8

⑴用鼠标指向图4中的数据点列，单击右键，浮现如下选择菜单（图12）：

9

I翟溉面积试千

亩）

图12

⑵点击“添加趋势线（R）”,弹出如下选择框（图13）；

图13

⑶在“分析类型”中选择“线性（L）”，然后打开选项单（图14）：

10

图14

⑷在选择框中选中“显示公式（E）”和“显示R平方值（R）”（如图14）,确定，即将得

到回归结果如下（图15）：

图表标题

♦灌溉面积y（千亩）

-线性（灌溉面积

y（千亩））

图15

在图15中，给出了回归模型和相应的测定系数即拟合优度。物业管理执行

方案

一、物业管理人员要具备服务意识

1、物业管理人员形象。仪容、仪表要具有亲和力，着装整

齐，面带微笑。

2、服务观念的培养。物业管理各单位是营业现场创造和

11

维护良好购物环境的重要部门，所以各项配合工作都要以不影

响营业销售为前提。

3、物业管理人员虽然没有直接参预到营业现场的服务工

作中，但不少配合工作也要直面消费者，如卖场保洁人员、保

安员、设备维护人员都会与顾客接触。怎样从公司利益出发，

更好地为顾客服务是工作执行的要点。故服务意识就是如何为

现场管理单位做好配合、协助。更好地使消费者感觉便利和满

意，是我们工作的出发点。

二、开店、闭店物业管理配合事项

1、工程设备人员：

每天8：00开启通道口和办公区照明；8：10开启卖场

主通道照明；8：25开启卖场所有照明.空调、电梯等各项使

用设备开启时间另行规定，同时检查各项设备的使用状况。

2、保洁人员

每天按时进入卖场进行通道的清洁工作，开业后进行细部

保洁工作。

3、保安人员

每天7：30进入卖场，与夜班交接工作后负责门岗，8：

30协助营业单位开门进入卖场。

闭店流程：

送宾曲结束后，工程设备人员关闭主场照明及各项使用设

备，保留各通道灯带。保安员协助现场营业主管，对滞留顾客

委婉送宾，营业员统一退出卖场后，营业主管和保安人员一同

对卖场仓库电器设备进行检查，检查完毕后一同锁门，封场、

做好记录并签名。

三、营业中维护保养工作执行要点

1、营业时间