收稿日期:2006-08-20
作者简介:赵丽娟(1974-),女,河北曲周人,邯郸职业技术学院经济系讲师。
Excel 在一元线性回归预测分析中的应用
赵丽娟1 冯韶华2
(1邯郸职业技术学院经济系,河北邯郸 056001;2河北工程大学,河北邯郸 056038)
摘要:预测是企业决策的前提与基础,Excel 是一个具有强大功能的数据管理与分析软件。我们可以运用Excel 进行回归分析预测,以提高工作效率。通过案例分析,运用Excel 函数与数据分析工具两种方法建立一元线性回归预测分析模型,并进行回归分析的预测。
关键词:Excel ;一元线性回归;预测中图分类号:TP391.13 文献标识码:A 文章编号:1009-5462(2006)04-0066-06 现代企业经营管理离不开决策,决策的正确与否关系到企业的生存与发展。而正确的决策要依据正确的预测,预测分析是决策的前提与基础。预测分析的方法种类繁多,随分析对象和预测期限不同而有差异,但基本方法可分为定量分析法和定性分析法。
回归分析法是根据事物的因果关系对变量的预测方法,它是定量预测方法的一种。因果关系普遍存在,比如,收入对消费支出的影响预测、产量对生产成本的影响预测、销量的预测、资金需要量的预测等,都可以运用回归分析法建立数学模型,进行预测分析。Excel 是一个功能强大的数据管理与分析软件,我们可以运用Excel 函数与数据分析进行回归分析预测。
一、一元线性回归预测法的基本原理
该方法是指影响市场变化的因素虽然是多方面的,但存在一个因素是最基本的、起决定作用的,而且自变量与因变量之间的数据分布成线性(直线)趋势,那么就可以运用一元线性回归方程y =a +bx 进行预测。这里,y 是因变量,x 是自变量,a ,b 均为参数,其中b 为回归系数,表示当x 每增加一个单位时,y 的平均增加数量。
例如要对城镇居民消费支出的发展趋势进行预测,首先要找到影响城镇居民消费支出的影响因素。通过分析我们发现城镇居民消费支出的多少与城镇居民人均可支配收入密切相关,收入高,其消费支出就大,反之消费支出就小。但城镇居民人均可支配收入并不能完全决定消费支出,城镇居民消费支出的多少还受到市场环境、居民储蓄额、消费习惯、消费心理等诸多因素的影响,因此,城镇居民消费支出与城镇居民人均可支配收入是一种非确定性关系(即相关关系)。
当因变量、自变量间变化呈现线性关系时,我们就可以用一条直线来表示它们的线性关系,以便于预测。但对于这种线性关系,我们可以用很多条的直线来描述,每条线对变量之间关系的拟合程度是不相同的,我们要从所有直线中找到一条最具有“代表性”
的直线。常用的确定直线方程的方法是最小二乘法。
一元线性回归预测法就是要依据一定数量的观察值(x i ,y i ),i =1,2,...,n ,找出回归方程y =a +bx ,确定方程参数a ,b ,即:
b =n ∑x i y i -∑x i ∑y i n ∑x 2i -(∑x i )2
a =
∑y i -b ∑x i n
这样,把求出的a,b代入y=a+bx中,就得到回归直线,只要给定x i值,就可以用y i作因变量y i的预测值。
二、一元线性回归预测实例
以上我们介绍了一元线性回归预测的基本原理,下面我们将通过实例说明一元线性回归预测的方法应用,以及如何利用办公软件Excel2000来分析处理数据。这样可以简化我们手工计算,借助计算机来完成数据的计算。
案例:
表1为我国1991-2001年城镇居民收入与消费支出情况,根据资料分析城镇居民人均可支配收入与消费支出之间的关系,并根据2002年、2003年的人均收入预测消费支出。
表1 城镇居民收入与消费支出情况
年份消费支出城镇居民人均可支配收入
19901278.891510.2
19911453.811700.6
19921671.732026.6
19932110.812577.4
19942851.343496.2
19953537.574283
19963919.54838.9
19974185.65160.3
19984331.65425.1
19994615.95854
200049986280
20015309.016859.6
预测步骤:
第一种方法:运用数据分析工具
1.将1990-2001年城镇居民人均可支配收入与消费支出的原始数据按照如图1的格式输入到Excel 中,并绘制散点图如图2。
2.通过目测发现其散点图呈直线趋势。通过相关分析判断变量间相关的程度。
选择工具菜单中的数据分析命令,弹出数据分析对话框。(若没有,需要通过安装Excel2000的加载宏之后,对它们进行调用,然后才可以在编辑窗口的菜单中使用相关的命令)
在分析工具列表框中,选择相关系数工具,出现相关系数对话框,如图3所示。输入各项参数后得到相关分析结果,如图4所示。从计算出的相关系数可以看出,城镇居民人均可支配收入与消费支出之间存在较强的相关性。因此,需要利用回归分析工具进一步建立回归方程。
3.建立回归模型
选择工具菜单中的数据分析命令,弹出数据分析对话框。
在分析工具列表框中选择回归工具,弹出回归对话框。
指定输入参数。在输入Y区域、输入X区域指定相应数据所在的单元格区域,本例分别指定为B2: B14和C2:C14,并选定标志复选框,在置信水平框内键入95%。对于一些特殊的回归模型,可以根据需
要指定常数为0。
指定输出选项。这里选择输出到新工作表组,并指定工作表的名称为“回归模型”。选定残差(即随机误差项)、线性拟合图和正态分布中的所有输出选项,以观察相应的结果。
单击确定按钮。如图5所示。
最后得到回归分析的结果。图6是有关回归分析的统计量、方差分析表和回归系数及t 检验、预测区间等数据。
分析图6的计算结果,可得下述检验结论(本例样本个数n =12,解释变量个数m =1)。
拟合程度检验:在回归统计区域A3:B8中给出的R 2为0.998504,调整后的R 2为0.998354,均很接近1,说明x 与y 的关系很密切。
F 检验:在方差分析区域A10:F14中给出的F 检验值为6672.716,
F 0.05(1,12-2)=4.96,F 检验值远远大于F 0.05(1,12-2),说明x 与y 的回归方程显著。t 检验:在回归模型区域A16:I18中给出了回归系数a 、b 的估计值极其标准误差、t 检验值和回归系数估计区间的上下限等。a =151.60、b =0.77
回归系数b 的t 检验值为81.6867,t 0.052(12-2)=2.228,t 检验值大于t 0.052(12-2),故拒绝原假设,即可以断言,城镇居民人均可支配收入对消费支出有显著影响。
综合上述计算结果和检验结果,确定回归模型如下:y =151.60+0.77x R 2=0.9985 n =12 F =6672.716 S =58.2346
这是一个较为优良的回归模型。现在利用该回归模型,就可以根据预测期的城镇居民人均可支配收入预测消费支出。
假定2002年城镇居民人均可支配收入为7300元,则2002年消费支出的预测值为:y =151.60+0.77x =151.60+0.77×7300=5772.6(元)因标准误差S =58.2346、样本个数n =12、解释变量个数m =1,则在显著水平α=0.05下,2001年消费支出的预测区间为:
y μt α2(n -m )?S =5772.6μ2.228×58.2346=5772.6μ129.7467即:当2001年的城镇居民人均可支配收入为7300元时,在显著水平α=0.05下,城镇居民消费支出预测区间在5642.8533～5902.3467元之间。
第二种方法:运用函数法
1.将1990-2001年城镇居民人均可支配收入与消费支出的原始数据按照如图1的格式输入到Excel 中,并绘制散点图。
2.通过目测发现其散点图呈直线趋势。通过相关分析判断变量间相关的程度。运用函数CORREL 求出相关系数,CORREL 函数的语法格式如下:CORREL (array1,array2)
Array1第一组数值单元格区域。Array2第二组数值单元格区域。
输入参数后得到计算结果,如图7所示。
3.利用L IN EST函数建立回归模型,L IN EST函数的语法格式如下:
L IN EST(known-y’s,known-x’s,const,stats)
其中:
Known-y’s是关系表达式y=a+bx中已知的y值集合。
Known-x’s是关系表达式y=a+bx中已知的可选x值集合。
Const为一逻辑值,指明是否强制使常数a为0。
如果const为TRU E或省略,a将被正常计算。
如果const为FAL SE,a将被设为0,并同时调整b值使y=bx。
Stats为一逻辑值,指明是否返回附加回归统计值。
如果stats为FAL SE或省略,函数L IN EST只返回系数b和常数项a。
如果stats为TRU E,函数L IN EST返回各个回归系数及附加回归统计值,函数返回的数据格式如表2所示。
表2 函数返回的数据格式
计算结果 数据含义
β
βm-1 Λ β1 β0回归系数
m
SE m SE m-1 Λ SE1 SE0回归系数的标准误差
R2 S复可决系数 R2、因变量标准误差
F df F统计量、自由度 df
S回 S残回归平方和 S回、残差平方和 S残
具体步骤如下:
为输出数据指定足够的存储区域;
在单元格E4中输入公式,并输入函数参数,如图8所示;
按下组合键Ctrl+Shift键后,再按回车键,系统输出如图9所示。
对照上表可知,回归系数分别为a=151.60、b=0.77,复可决系数R2=0.9985,因变量的标准误差S =58.2347,F统计量=6672.7159,残差平方和S残=33912.77、回归平方和S回=22629031.11将这些计算结果与由回归工具所得的结果(图6)比较可以看出,使用L IN EST函数与利用回归工具进行回归分析可得到一致的结论。
参考文献
[1](美)拉格斯代尔著,杜学孔,崔鑫生译.电子表格建模与决策分析[M].北京:电子工业出版社,2006.2
[2](美)罗伯特?S?平狄克,丹尼尔?L?鲁宾费尔德著,钱小军译.计量经济模型与经济预测[M].北京:机械工业出版社,
1999.11
[3]刘利主编.中文版Excel2003教程[M].北京:中国宇航出版社,2004.6
[责任编校:张勇]
(上接第65页)
3结论
1)实验结果显示冷凝温度的变化主要受循环水流量和水箱水温的影响,而与蒸发温度的变化关系不大。且随着流量的增大,流量对冷凝温度的影响越小。
2)从实验结果可以看出冷凝回收利用家用空调的冷凝热是可行的。
参考文献
[1]张华俊,董晓俊,项卫中.制冷系统余热回收的研究[J].低温工程,1997,99(5):37-43.
[2]季杰,裴刚等.空调-热水器一体机制冷兼制热水模式的性能模拟和实验分析[J].暖通空调2003,33(2):19-23.
[3]季杰,裴刚等.空调-热水器一体机单独制热水模式的性能模拟和实验分析[J].暖通空调,2004,34(12):96-98.
[4]江辉民,王洋,马最良等.带热水供应的家用空调器的探讨[J].建筑热能通风空调.2004,23(1):48-51.
[5]江辉民.带热水供应的节能型空调器的实验研究[D].哈尔滨:哈尔滨工业大学,2003.
[责任编校:张勇]
实验四（1）用Excel作一元线性回归分析 实验名称：回归分析 实验目的：学会应用软件实验一元线性回归，多元线性回归和非线性回归模型的求解及应用模型解决相应地理问题。 1 利用Excel进行一元线性回归分析 第一步，录入数据 以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。 图1 第二步，作散点图 如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在 “插入”菜单中打开“图表（H）”。图表向导的图标为。选中数据后，数据变为蓝色（图2）（office2003）。插入-图表（office2007)
图2 点击“图表向导”以后，弹出如下对话框（图3）： 图3 在左边一栏中选中“XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图4）：
图4 第三步，回归 观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。回归的步骤如下： ⑴ 首先，打开“工具”下拉菜单，可见数 据分析选项（见图5） （office2003）。数据-数据分析（office2007) ： 图5 用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）：
图6 ⑵然后，选择“回归”，确定，弹出如下选项表（图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）。
野外实习资料的数理统计分析 一元线性回归分析 一元回归处理的是两个变量之间的关系，即两个变量X和Y之间如果存在一定的关系，则通过观测所得数据，找出两者之间的关系式。如果两个变量的关系大致是线性的，那就是一元线性回归问题。 对两个现象X和Y进行观察或实验，得到两组数值：X1，X2,…，Xn和Y1，Y2，…，Yn,假如要找出一个函数Y=f(X),使它在 X=X1,X2, …,Xn时的数值f(X1),f(X2), …,f(Xn)与观察值Y1，Y2，…，Yn趋于接近。 在一个平面直角坐标XOY中找出（X1，Y1），（X2，Y2），…，（Xn，Yn）各点，将其各点分布状况进行察看，即可以清楚地看出其各点分布状况接近一条直线。对于这种线性关系，可以用数学公式表示： Y = a + bX 这条直线所表示的关系，叫做变量Y对X的回归直线，也叫Y对X 的回归方程。其中a为常数，b为Y对于X的回归系数。 对于任何具有线性关系的两组变量Y与X，只要求解出a与b的值，即可以写出回归方程。计算a与b值的公式为：
式中：为变量X的均值，Xi为第i个自变量的样本值，为因变量的均值，Yi为第i个因变量Y的样本值。n为样本数。 当前一般计算机的Microsoft Excel中都有现成的回归程序，只要将所获得的数据录入就可自动得到回归方程。 得到的回归方程是否有意义，其相关的程度有多大，可以根据相关系数的大小来决定。通常用r来表示两个变量X和Y之间的直线相关程度，r为X和Y的相关系数。r值的绝对值越大，两个变量之间的相关程度就越高。当r为正值时，叫做正相关，r为负值时叫做负相关。r 的计算公式如下： 式中各符号的意义同上。 在求得了回归方程与两个变量之间的相关系数后，可以利用F检验法、t检验法或r检验法来检验两个变量是否显著相关。具体的检验方法在后面介绍。
excel2016官方版怎么做一元线性回归分析图表 在excel2016中也是能够制作一元线性回归分析图表的，而且方法很简单，想要检测两组数据是否具有线性关系的话就可以使用excel2016官方版来做一元线性回归分析图表分析数据，根据结果来测试两组数据的关系，下面为大家介绍使用官方版excel2016来做一元线性回归分析图表的方法。 excel2016官方版怎么做一元线性回归分析图表分析数据： 1、首先要准备好两组数据做为x和y，这组数据在可以简单感觉一下是否具有线性关系。将准备好的数据放入excel2016表格里面 2、EXCEL需要我们自己启用数据分析，点击文件，选择选项，点击左侧的加载项，加载分析工具
3、加载工具完成以后，点击数据中的“工具分析”，选择“回归”，点击确定 4、点击Y值输入区域后面的单元格选择工具，选择Y值单元格，比如小编这里的A2:A20，X值同理操作，这里选择B2:B20，勾选下方的线性拟合图，我们可以看一下拟合的效果
5、excel会在新的工作表里面输出回归分析的相关结果，比如相关系数 R^2，标准误差，在X-variable和Intercept两项的值可以写出一元回归方程 6、在右侧就是我们的线性拟合图，观察拟合效果还不错，我们可以对图做一些修改，方便放到word文档里面，选中该图 7、在图表工具里面的图表布局中选择“布局3”，图标样式选择第一个黑白色
8、在新的图标样式里面多了很多网格线，实际我们并不是太需要，选中右击删除。是整个图标简洁一些 总结：以上就是全部的“excel2016官方版怎么做一元线性回归分析图表”内容。
用EXCEL进行生产函数的多元线性回归分析 一、相关函数 EXCEL电子制表系统中函数的语法分为函数名和参数两部分，参数用圆括号括起来，之间以逗号隔开。参数可以为单元格区域、数组、函数、常数（逻辑型、数值型等）。 进行回归分析时，主要采用线性回归函数LINEST，辅以使用索引取值INDEX与四舍五入ROUND函数。 1、线性回归函数LINEST。 使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组。因为此函数返回数值数组，所以必须以数组公式的形式输入。 该函数的功能为：运算结果返回一线性回归方程的参数，即当已知一组混合成本为Y因变量序列值、N组Xi有关自变量因素的数量序列值时，函数返回回归方程的系数bi（i=1，2…n单位变动成本）和常数a（固定成本或费用）。 多元回归方程模型则为：y=b1x1＋b2X2……＋bnXn＋a 语法 LINEST(known_y's,known_x's,const,stats) Known_y's 是关系表达式 y = mx + b 中已知的 y 值集合。 ?如果数组 known_y's 在单独一列中，则 known_x's 的每一列被视为一个独立的变量。 ?如果数组 known-y's 在单独一行中，则 known-x's 的每一行被视为一个独立的变量。 Known_x's 是关系表达式 y = mx + b 中已知的可选 x 值集合。 ?数组 known_x's 可以包含一组或多组变量。如果只用到一个变量，只要 known_y's 和 known_x's 维数相同，它们可以是任何形状的区域。如果用到多个变量，则 known_y's 必须为向量（即必须为一行或一列）。 ?如果省略 known_x's，则假设该数组为 {1,2,3,...}，其大小与 known_y's 相同。Const 为一逻辑值，用于指定是否将常量 b 强制设为 0。 ?如果 const 为 TRUE 或省略，b 将按正常计算。 ?如果 const 为 FALSE，b 将被设为 0，并同时调整 m 值使 y = mx。 Stats 为一逻辑值，指定是否返回附加回归统计值。 ?如果 stats 为 TRUE，则 LINEST 函数返回附加回归统计值，这时返回的数组为{mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。
案例分析报告 （2014——2015学年第一学期） 课程名称：预测与决策 专业班级：电子商务1202 学号： 2204120202 学生姓名：陈维维 2014 年 11月 案例分析（一元线性回归模型） 我国城镇居民家庭人均消费支出预测 一、研究目的与要求 居民消费在社会经济的持续发展中有着重要的作用，居民合理的消费模式和居民适度的消费规模有利于经济持续健康的增长，而且这也是人民生活水平的具体体现。从理论角度讲，消费需求的具体内容主要体现在消费结构上，要增加居民消费，就要从研究居民消费结构入手，只有了解居民消费结构变化的趋势和规律，掌握消费需求的热点和发展方向，才能为消费者提供良好的政策环境，引导消费者合理扩大消费，才能促进产业结构调整与消费结构优化升级相协调，才能推动国民经济平稳、健康发展。例如，2008年全国城镇居民家庭平均每人每年消费支出为11242.85元，?最低的青海省仅为人均8192.56元，最高的上海市达人均19397.89元，上海是黑龙江的2.37倍。为了研究全国居民消费水平及其变动的原因，需要作具体的分析。影响各地区居民消费支出有明显差异的因素可能很多，例如，零售物价指数、利率、居民财产、购物环境等等都可能对居民消费有影响。为了分析什么是影响各地区居民消费支出有明显差异的最主要因素，并分析影响因素与消费水平的数量关系，可以建立相应的计量经济模型去研究。 二、模型设定?
我研究的对象是各地区居民消费的差异。居民消费可分为城镇居民消费和农村居民消费，由于各地区的城镇与农村人口比例及经济结构有较大差异，最具有直接对比可比性的是城市居民消费。而且，由于各地区人口和经济总量不同，只能用“城镇居民每人每年的平均消费支出”来比较，而这正是可从统计年鉴中获得数据的变量。 所以模型的被解释变量Y选定为“城镇居民每人每年的平均消费支出”。 因为研究的目的是各地区城镇居民消费的差异，并不是城镇居民消费在不同时间的变动，所以应选择同一时期各地区城镇居民的消费支出来建立模型。因此建立的是2008年截面数据模型。影响各地区城镇居民人均消费支出有明显差异的因素有多种，但从理论和经验分析，最主要的影响因素应是居民收入，其他因素虽然对居民消费也有影响，但有的不易取得数据，如“居民财产”和“购物环境”；有的与居民收入可能高度相关，如“就业状况”、“居民财产”；还有的因素在运用截面数据时在地区间的差异并不大，如“零售物价指数”、“利率”。因此这些其他因素可以不列入模型，即便它们对居民消费有某些影响也可归入随即扰动项中。 为了与“城镇居民人均消费支出”相对应，选择在统计年鉴中可以获得的“城市居民每人每年可支配收入”作为解释变量X。 以下是2008年各地区城镇居民人均年消费支出和可支配收入表
实验结果： 实验一：一元线性回归在Excel中的实现 一、实验过程描述 1.录入数据 打开EXCLE，录入实验数据，B列存放居民货币收入，C列存放居民消费品购买力，如下图所示： 2.绘制散点图 点击插入——图表——散点图——下一步，选择数据区域如下图：
定义表名为消费能力表、X轴为收入、Y轴为购买力，形成生散点图： 根据散点图可知，题中两个条件之间存在着线性关系，根据散点图可建立一次回归模型。 3.所需数据的计算 一元线性回归系数的计算中，需要用到∑x、∑y、∑2x、∑2y及∑xy 的值，因此按下列步骤求出这些值。
在D2单元格中输入“=B2*B2”，下拉求出所有的值。同上，在E2单元格中输入”=C2*C2”,在F2单元格中输入“=B2*C2”，依次下拉，得到所有值。结果如下表所示： 在B11单元格中输入“=SUM(B2:B10)”，依次右拉，求出各列的和∑x 、∑y 、∑2x 、∑2y 及∑xy ，依次存在B11，C11，D11，E11，F11.如下图所示： 4. 一元线性回归系数的计算： 根据系数公式 x b y a x x n y x xy n b 22-=--=∑∑∑∑∑)(，在EXCLE 表格中进行计算如下： 在I2单元格中输入一元线性回归系数b 的公式“=(9*F11-B11*C11)/(9*D11-B11*B11)”，在I3单元格中输入系数a 的公式 “ =C11/9-I2*(B11/9)”结果如下图所示：
由此得出回归方程： Y=-0.99464X+0.847206 二、实验结果分析 在进行线性回归分析之前，首先必须依据一定的经济理论、专业知识，对变量间是否存在一定的相关性进行分析。本题中，应根据实际经验，确定居民货币收入为自变量，居民消费品购买力为因变量。再次要绘制散点图，观察数据信息是否符合线性要求，在完成上述准备工作后，才能进行线性回归方程的计算。
用Excel进行一元线性回归分析 Excel功能强大，利用它的分析工具和函数，可以进行各种试验数据的多元线性回归分析。本文就从最简单的一元线性回归入手. 在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用到的Origin和数学中常见的MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。 文章使用的是2000版的软件,我在其中的一些步骤也添加了2007版的注解. 1 利用Excel2000进行一元线性回归分析 首先录入数据. 以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。 图1 第二步，作散点图 如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在 “插入”菜单中打开“图表（H）(excel2007)”。图表向导的图标为。选中数据后，数据变为蓝色（图2）。
图2 点击“图表向导”以后，弹出如下对话框（图3）： 图3 在左边一栏中选中“XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图4）：
图4 第三步，回归 观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。回归的步骤如下： ⑴ 首先，打开“工具”下拉菜单，可见数据分析选项（见图5）(2007为”数据”右端的”数据分析”)： 图5 用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）：
Excel求解一元线性回归方程步骤(图解详细) 1．开始－程序－Microsoft Excel，启动Excel程序。 2．Excel程序启动后，屏幕显示一个空白工作簿。 3．选定单元格，在单元格输入计算数据。
4．选中输入数据，点击“图表向导”按钮。 5．弹出图表向导对话窗，点击XY散点图，选择平滑线散点图，点击下一步。 6．选择系列产生在：列，点击下一步。
7．在图表标题中输入“硝基苯标准曲线”，数值（X）轴输入“硝基苯浓度”，数值（Y）轴输入“HPLC峰面积”。此外还可以点击“坐标轴”，“网格线”，“图例”，“数据标志”下拉菜单，对其中选项进行选择。 8．点击完成后，即可得到硝基苯的标准曲线图。 9．将鼠标移至图表工作曲线上，单击鼠标右键，选择“添加趋势线”。
10．在“类型”选项中选择“线性”，“选项”中选择“显示公式”，“显示R平方值”，单击确定。 11．单击确定后即可得到附有回归方程的一元线性回归曲线。 12．至此，利用“图表向导”制作回归方程的操作步骤完毕。 利用Excel中“图表向导”制作标准曲线，使用者仅需按照向导说明填入相关信息即可完成图表的制作。方法简单，适合对Excel了解不多的人员，如果你对Excel函数有一定的了解，那么你可以利Excel函数编制程序完成回归方程的计算。 4.4.2.2通过编制Excel程序计算一元线性回归方程 1．打开一个新工作簿，以“一元线性回归方程”为文件名存盘。 2．单击插入，选择名称－定义。
3．在弹出的“定义名称”对话窗中“名称”栏输入“a”，“引用位置”栏输入“＝$E$4”，然后按“添加”按钮；再在“名称”栏输入“b”，“引用位置”栏输入“＝$E$3”，按“添加”按钮，依次输入下列容，最后单击确定。 “名称”栏输入容“引用位置”栏输入容 a =$E$4 b =$E$3 f =$G$4 n =$G$3 rf =$G$6 rxy =$E$5 x =$A$3:$A$888 y =$B$3:$B$888 aa=$G$2 yi1 =$E$12 yi2 =$E$13 4．完成命名后，在相关单元格输入下列程序容。 单元格输入容 E3 =ROUND(SLOPE(y,x),4) G3 =COUNT(x) E4 =ROUND(INTERCEPT(y,x),4) G4 =n-2 E5 =PEARSON(x,y) E6 =DEVSQ(x) G6 =SQRT(FINV(a,1,f)/(f+FINV(a,1,f ))) E7 =DEVSQ(x)*(1-rxy^2) E8 =STEYX(y,x) E9 =IF(rxy>rf,“rxy>临界值回归方程有意义”， “rxy>临界值回归方程有意义”) G10 =1-G2 E11 =CONCATENATE(“=”,a,”+”,”(“,b,”)X”) G12 =(yi1-a)/b G13 =(yi2-a)/b
Excel求解一元线性回归方程步骤(图解详细) 1．开始－程序－Microsoft Excel，启动Excel程序。 2．Excel程序启动后，屏幕显示一个空白工作簿。 3．选定单元格，在单元格内输入计算数据。
4．选中输入数据，点击“图表向导”按钮。 5．弹出图表向导对话窗，点击XY散点图，选择平滑线散点图，点击下一步。 6．选择系列产生在：列，点击下一步。
7．在图表标题中输入“硝基苯标准曲线”，数值（X）轴输入“硝基苯浓度”，数值（Y）轴输入“HPLC峰面积”。此外还可以点击“坐标轴”，“网格线”，“图例”，“数据标志”下拉菜单，对其中选项进行选择。 8．点击完成后，即可得到硝基苯的标准曲线图。 9．将鼠标移至图表工作曲线上，单击鼠标右键，选择“添加趋势线”。
10．在“类型”选项中选择“线性”，“选项”中选择“显示公式”，“显示R平方值”，单击确定。 11．单击确定后即可得到附有回归方程的一元线性回归曲线。 12．至此，利用“图表向导”制作回归方程的操作步骤完毕。 利用Excel中“图表向导”制作标准曲线，使用者仅需按照向导说明填入相关信息即可完成图表的制作。方法简单，适合对Excel了解不多的人员，如果你对Excel函数有一定的了解，那么你可以利Excel函数编制程序完成回归方程的计算。 4.4.2.2通过编制Excel程序计算一元线性回归方程 1．打开一个新工作簿，以“一元线性回归方程”为文件名存盘。 2．单击插入，选择名称－定义。
3．在弹出的“定义名称”对话窗中“名称”栏输入“a”，“引用位置”栏输入“＝$E$4”，然后按“添加”按钮；再在“名称”栏输入“b”，“引用位置”栏输入“＝$E$3”，按“添加”按钮，依次输入下列内容，最后单击确定。 “名称”栏输入内容“引用位置”栏输入内容 a =$E$4 b =$E$3 f =$G$4 n =$G$3 rf =$G$6 rxy =$E$5 x =$A$3:$A$888 y =$B$3:$B$888 aa=$G$2 yi1 =$E$12 yi2 =$E$13 4．完成命名后，在相关单元格内输入下列程序内容。 单元格输入内容 E3 =ROUND(SLOPE(y,x),4) G3 =COUNT(x) E4 =ROUND(INTERCEPT(y,x),4) G4 =n-2 E5 =PEARSON(x,y) E6 =DEVSQ(x) G6 =SQRT(FINV(a,1,f)/(f+FINV(a,1,f ))) E7 =DEVSQ(x)*(1-rxy^2) E8 =STEYX(y,x) E9 =IF(rxy>rf,“rxy>临界值回归方程有意义”， “rxy>临界值回归方程有意义”) G10 =1-G2 E11 =CONCA TENATE(“=”,a,”+”,”(“,b,”)X”) G12 =(yi1-a)/b G13 =(yi2-a)/b
1 利用Excel2000进行一元线性回归分析 第一步，录入数据 以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。 图1 第二步，作散点图 如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在 “插入”菜单中打开“图表（H）”。图表向导的图标为。选中数据后，数据变为蓝色（图2）。 图2 点击“图表向导”以后，弹出如下对话框（图3）：
图3 在左边一栏中选中“XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图 第三步，回归 观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。回归的步骤如下： ⑴首先，打开“工具”下拉菜单，可见数据分析选项（见图5）：
图5 用鼠标双击“数据分析”选项，弹出“数据分析”对话框（图6）： 图6 ⑵然后，选择“回归”，确定，弹出如下选项表：
图7 进行如下选择：X、Y值的输入区域（B1:B11，C1:C11），标志，置信度（95%），新工作表组，残差，线性拟合图。 或者：X、Y值的输入区域（B2:B11，C2:C11），置信度（95%），新工作表组，残差，线性拟合图。 注意：选中数据“标志”和不选“标志”，X、Y值的输入区域是不一样的：前者包括 数据标志： 最大积雪深度x(米)灌溉面积y(千亩) 后者不包括。这一点务请注意。
图8-1 包括数据“标志” 图8-2 不包括数据“标志” ⑶再后，确定，取得回归结果（图9）。
图9 线性回归结果 ⑷ 最后，读取回归结果如下： 截距：356.2=a ；斜率：813.1=b ；相关系数：989.0=R ；测定系数：979.02=R ；F 值：945.371=F 。 ⑸ 建立回归模型，并对结果进行检验 模型为：x y 813.1356.2?+= 至于检验，R 、R 2 和F 值可以直接从回归结果中读出。实际上，8,05.0632.0989416.0R R =>=，检验通过。有了R 值，F 值和t 值均可计算出来。F 值的计算公式和结果为： 8,05.022 22 32.5945.371) 989416.01(1 1101 989416 .0)1(11F R k n R F =>=---=---= 显然与表中的结果一样。t 值的计算公式和结果为： 8,05.02 306.2286.191110979416.01979416 .01 1t k n R R t =>=---=---= 回归结果中给出了残差（图10），据此可以计算标准离差。首先求残差的平方 2 2)?(i i i y y -=ε，然后求残差平方和107.16174.0724.1101 2 =++==∑== n i i S ε ，于是标准 离差为
文档内容 1. 利用Excel进行一元线性回归分析 2. 利用Excel进行多元线性回归分析 1. 利用Excel进行一元线性回归分析 第一步，录入数据 以连续10年最大积雪深度和灌溉面积关系数据为例予以说明。录入结果见下图（图1）。 图1 第二步，作散点图 如图2所示，选中数据（包括自变量和因变量），点击“图表向导”图标；或者在 “插入”菜单中打开“图表（H）”。图表向导的图标为。选中数据后，数据变为蓝色（图2）。
图2 点击“图表向导”以后，弹出如下对话框（图3）： 图3 在左边一栏中选中“XY散点图”，点击“完成”按钮，立即出现散点图的原始形式（图4）：
图4 第三步，回归 观察散点图，判断点列分布是否具有线性趋势。只有当数据具有线性分布特征时，才能采用线性回归分析方法。从图中可以看出，本例数据具有线性分布趋势，可以进行线性回归。 回归的步骤如下： 1.首先，打开“工具”下拉菜单，可见数据分析选项（见图5）： 图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）。