excle中回归分析(实例)

由一个或一组非随机变量来估计或预测某一个随机变量的观测值时，所建立的数学模型及所进行的统计分析，称为回归分析。因此，回归分析是研究随机变量与非随机变量之间的数量关系的一种数学方法。如果所建立的模型是线性的就称为线性回归分析。线性回归分析不仅告诉我们怎样建立变量间的数学表达式，即经验公式，而且还利用概率统计知识进行分析讨论，判断出所建立的经验公式的有效性，从而可以进行预测或估计。

回归分析的内容包括如何确定因变量与自变量之间的回归模型；如何根据样本观测数据，估计并检验回归模型及未知参数；在众多的自变量中，判断哪些变量对因变量的影响是显著的，哪些变量的影响是不显著的；根据自变量的已知值或给定值来估计和预测因变量的值。

一、利用图表进行分析As、Sb的相关程度。

（1）打开“罗山”工作表。

（2）在工具栏上选择“插入—图表”，单击打开图表向导对话框，如图1-1所示，在“图表类型”列表框中选择“XY散点图”，单击“下一步”按钮进入图表向导步骤2。

（3）在图表向导步骤2对话框的“数据区域”中输入“E2：F25”，选择“系列产生在”为“列”，如图1-2所示，单击“下一步”按钮进入步骤3。

（4）在图表向导步骤3的对话框中，打开“图例”页面，取消“显示图例”，省略标题，如图1-3所示。

（5）单击“完成”按钮，得到XY散点图如图1-4所示。

（6）在散点图中，把鼠标放在任一数据点上，右击，在快捷菜单中选择“添加趋势线”，打开趋势线对话框。

（7）在“添加趋势线”对话框中打开“类型”页面，选择“线性”选项，在“选项”页面中选择“显示公式”和“显示R平方”选项，单击“确定”按钮，得 到趋势回归图，如图1-5所示。

图1-1

图1-2

图1-3

图1-4

图1-5

二、利用工作表函数进行回归分析

Excel提供了回归分析工作表函数，主要有以下几个： （1）截距函数。 （2）斜率函数。 （3）测定系数函数。

（4）总体标准偏差函数。

 截距函数功能是利用现有的x值与y值计算直线与y轴的截距。截距为穿过已知的

known_x's 和known_y's数据点的线性回归线与y轴的交点。当自变量为0时，使用INTERCEPT函数可以决定因变量的值。语法为：INTERCEPT(known_y's,known_x's)

斜率函数返回根据known_y's和known_x's中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的变化率。语法为：SLOPE (known_y's,known_x's)

测定系数函数返回根据known_y's和known_x's中数据点计算得出的乘积矩相关系数的平方。R平方值可以解释为y方差与x方差的比例。语法为：RSQ(known_y's,known_x's)

④总体标准偏差函数返回通过线性回归法计算每个x的y预测值时所产生的标准误差。标准误差用来度量根据单个x变量计算出的y预测值的误差量。语法为：STEYX(known_y's,known_x's)

用Excel提供的工作表函数进行相关计算。 （1）在单元格A26~A29中分别输入“截距”、“斜率”、“测 定系数”、“估计标准误差”。

（2）在单元格B26中输入公式“=INTERCEPT(B2:B25,A2：A25)”， 回车后显示0.337425。

（3）在单元格B13中输入公式“=SLOPE(B2:B25,A2：A25)”，回 车后显示0.075423。

（4）在单元格B14中输入公式“=RSQ(B2:B25,A2：A25)”，回车 后显示0.738455。

（5）在单元格B15中输入公式“=STDEVPA(B2:B25,A2：A25)”，回 车后显示2.8180738。计算结果如图2-1所示。

图2-1

三、Excel回归分析工具

回归分析工具是通过对一组观察值使用“最小平方法”进行直线拟合，以分析一个或多个自变量对单个因变量的影响方向与影响程度的方法。它是Excel中数据分析工具的一个内容。

在“工具”菜单中选择“数据分析”选项，会出现“数据分析”对话框，在分析工具中选择“回归”，单击“确定”按钮就会进入“回归”对话框(只有完整版的Excel才有，花了好长时间才找到)，如图2-2所示。

图2-2

选取Sb为Y值，As为X值，进行回归分析。如图2-3。

图2-3

结果为：