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

1、Excel做线性回归分析基本原理及实例一、原理1、回归分析原理由一个或一组非随机变量来估计或预测某一个随机变量的观测值时，所建立的数学模型及所进行的统计分析，称为回归分析。按变量个数的多少，回归分析有一元回归分析与多元回归分析之分，多元回归分析的原理与一元回归分析的原理类似。按变量之间关系的形式，回归分析可以分为线性回归分析和非线性回归分析。2、回归分析的主要内容回归分析的内容包括如何确定因变量与自变量之间的回归模型；如何根据样本观测数据，估计并检验回归模型及未知参数；在众多的自变量中，判断哪些变量对因变量的影响是显著的，哪些变量的影响是不显著的；根据自变量的已知值或给定值来估计和预测因变量的

2、值。3、利用图表进行分析例23-1:某种合成纤维的强度与其拉伸倍数之间存在一定关系，图23-1所示（“线性回归分析”工作表）是实测12个纤维样品的强度y与相应的拉伸倍数x的数据记录。试求出它们之间的关系。ABrc11编号拉伸倍数强度2-1T.91.43匚221.34.32.11.85r42,52.5652.72.87匚62.72.58r?3.53983.52.710匚944111043.512r114.54.213_124.6最5(1)打开“线性回归分析”工作表。(2)利用“图表向导”绘制XY散点图”。(3)在XY散点图”中绘制趋势回归直线，如图23-2所示二、Excel中的回归分析工作表函数

3、(1)截距函数语法：INTERCEPT(known_ys,known_xs)其中：Known_ys为因变的观察值或数据集合，Known_xs为自变的观察值或数据集合。(2)斜率函数语法：SLOPE(known_ys,known_xs)其中：Known_ys为数字型因变量数据点数组或单元格区域；Known_xs为自变量数据点集合。(3)测定系数函数语法：RSQ(known_ys,known_xs)其中：Known_ys为数组或数据点区域，Known_xs为数组或数据点区域。(4)估计标准误差函数语法：STEYX(known_ys,known_xs)其中：Known_ys为因变量数据点数组或区域，

4、Known_xs为自变量数据点数组或区域。3利用工作表函数进行回归分析例23-2:在某大学一年级新生体检表中随机抽取10张，得到10名大学生的身高(x)和体重(y)的数据，如图23-3(“身高体重”工作表)所不。用Excel提供的工作表函数进行相关计算。(1)分别计算“截呵“斜率”“测定系数”“估计标准误差”如图23-3所示。(2)根据计算结果可以得出直线方程:ABC1蜀1wCera)悻重yCkm)216251317054416652S153476174S3716659815了559170GO1735Tl111685412截距T9.4201513斜率0.804182514测定系数0.68170

5、1S15估计标准误差NE1S0733三、Excel回归分析工具1、回归分析工具的主要内容回归分析工具是通过对一组观察值使用“最小平方法”进行直线拟合，以分析一个或多个自变量对单个因变量的影响方向与影响程度的方法。 它是Excel中数据分析工具的一个内容。在“回归”对话框中主要包括也许内容：Y值输入区域、X值输入区域、标志、置信度、常数为零、输出区域、新工作表组、新工作簿、残差、标准残差、残差图、线形拟合图、正态概率图。2回归分析工具的应用例23-3:以例23-2资料为例，利用回归分析工具进行回归分析。(1)打开“身高体重”工作表。(2)在“数据分析”中的“分析工具”列表中选择“H顺， 进行相关

6、设置， 输出结果，如图23-4所示。D二EFGHIJKL1SIMARYOUTPUT23回归统什4 Multiple0.8256525RSquareQ.681702Adjusted0.6419157标准误差2.S13074S观测值10910方差分析11dfSS13F;nificansF12回归分析1136.0677136,0677 17,13366 Q,00325713残差E63.532327.9415414总计.9199.6电16Coefficien,标准误差tStatP-value Lower95%Upper95虾限95.值上限瓯0%17 Intercept-714202.32,53478-

7、2.44108.0.040496,-154.445,-4,39481-154,445,-4,39481.18身岛工lew)。 .网乳邓QJ942即4.139283 0,003257 0.356171 L252194 0.3561711.2521943回归分析工具的输出解释Excel回归分析工具的输出结果包括3个部分:(1)回归统计表回归统计表包括以下几部分内容：MultipleR(复相关系数R):是R2的平方根， 又称为相关系数， 用来衡量变量x和y之间相关程度的大小。本例中R为0.825652,表示二者之间的关系是高度正相关。RSquare(复测定系数R2):用来说明自变量解释因变量变差的程

8、度，以测定因变量y的拟合效果。AdjustedRSquare（调整复测定系数R2）:仅用于多元回归才有意义，它用于衡量加入独立变量后模型的拟合程度。当有新的独立变量加入后，即使这一变量同因变量之间不相关，未经修正的R2也要增大，修正的R2仅用于比较含有同一个因变量的各种模型。标准误差：用来衡量拟合程度的大小，也用于计算与回归相关的其他统计量，此值越小，说明拟合程度越好。观测值：用于估计回归方程的数据的观测值个数。（2）方差分析表方差分析表的主要作用是通过F检验来判断回归模型的回归效果。表中“回归分析”行计算的是估计值同均值之差（-）的各项指标；“残差”行是用于计算每个样本观察值与估计值之差（-

9、）的各项指标；“总计”行用于计算每个值同均值之差（-）的各项指标。第二列df是自由度，第三列SS是离差的平方和，第四列MS是均方差，它是离差平方和除以自由度，第五列是F统计量，第六列SignificanceF是在显著性水平下的F%的临界值3.回归参数表回归参数表主要用于回归方程的描述和回归参数的推断。如图23-4所示的表中最下面三行中的第二行和第三行分别是（30（截距）（31（斜率）的各项指标。对于大多数回归分析来讲，关注B1要比（30重要。第二列是B0和B1的值，据此可以写出回归方程。第三列是各个回归系数的P值（双侧），最后是给出（30和（31）的95%的置信区间的上下限例23-4:有一个工

10、厂会计部门在估计每月管理费y时，用工人的劳动日数x1与机器的开工台数x2作自变量，现将当年10个月的数据搜集起来，如图23-5(“多元回归分析”工作表)所示，估计y对x1与x2的线性回归方程(=0.05)ACD1月份9日的机器开工台数七 管理费用y214516293242142443441527J54451325&5431326764614281874416309g4516231094415281110431527J(1)打开“回归”对话框。(2)在Y值输入区域”中输入D1:D11；在义值输入区域”中输入B1:C11选择“标志”置信度默认；在“输出选项”中选择“输出区域”在其右边输入“

11、A12；单击“确定”按钮输出结果，如图23-6所示。12ABCDaFGHSJJH1URYOUTPL-13H.回归经什15 MtipleKdEE377HREquue723323217AJustedRSr.F.FU7S7标淮误差LD70E3S819睨加值1020方均叶斤df5能F；ni土士二心二亡?23回归介祈221.5T51273310.T3S064-9.4114710.0L037LT&u第&改后1.14b26T匕如 息十g27Co&fficent行凌注羞t.Stat上限95.05(PilueLcsr晶Utper3居2E29I:trrrrpl13.8196313.3卷履93；03+530.