Excel详细教程之回归分析

1、-PAGE . z.E*cel详细教程之回归分析除了数据存储和管理功能，E*cel为基于工作表的数据分析提供了各类不同的工具和方法，用于各类通用的数据分析工作。从应用和表现形式看，E*cel的数据分析工具和方法可以分为以下几个类别：1基于工作表函数和公式的分析能力使用E*cel置的公式计算和统计分析函数，例如通过本期的技巧文章E*cel矩阵函数和公式的使用中介绍的矩阵函数，可以完成回归分析。使用E*cel的公式和函数功能，需了解相关的语法和参数，同时可能还需熟悉所使用的分析方法的数学推导过程。2基于用户界面的数据分析工具E*cel提供用于统计和计量分析的集成界面工具包，使用该工具包可进展描述统

2、计、方差分析、假设检验、回归抽样等统计分析。在分析工具库已正确加载的前提下，点击E*cel工具菜单中的数据分析选项，可调出数据分析功能选择界面，选择一项具体分析功能后即可进入详细的输入输出和设置界面：在上步中选择的不同功能项，会弹出不同的分析界面，一般情况下该分析界面包括参数的输入和分析结果的输出选择以及与该功能相关的具体参数选项。数据分析工具提供交互界面的分析功能，其优点是容易理解和使用，但输出结果是静态的，如需变更输入数据或参数，都需重新启动分析工具以获得修正结果。为了输出动态、可随时更改输入选项的结果，需要使用E*cel的函数和公式功能。3其他快捷数据分析方法E*cel中的*些对象操作含

3、了简单的可视化数据分析能力，例如区域的选择、图表数据的选择等。这些快捷工具可以简化使用函数或界面工具的输入输出过程。4来自用户自定义或第三方的增强数据分析工具E*cel提供了用户开发平台，高级用户可在此根底上开发专用的数据分析函数或工具。同时，由于E*cel的通用性，有许多基于E*cel的商业统计和数据分析插件可供选择。这些工具和软件在不同程度和不同领域增强和扩大了E*cel的数据分析能力。例如，DataDirect M*就是一个可以扩大E*cel金融数据分析能力的第三方软件。OLS回归分析一元回归分析是估计一个因变量和一个自变量之间平均关系的统计方法。定义根本关系式为：Y=a+b*，回归通过

4、最小二乘法找出一条能最正确拟合所有观测数据的直线，也即使残差平方和最小化。方程参数a和b的计算公式为：多元回归分析是估算一个因变量和两个或两个以上的自变量之间的平均关系的方法。如价格水平、广告支出、消费者收入、喜好和竞争状况对销售额的影响；公司收益增长、收益波动、股票贝塔值、通货膨胀率等对股票价格的影响等。多元回归的模型形式为：其中，Y为自变量或被解释变量，*1到*k为自变量或解释变量，b1到bk为回归系数，e为回归误差项。在E*cel中进展多元回归可利用数据分析中的回归工具，也使用函数LINEST获得构造。E*cel最多允许16个回归变量，假设方程超出此限制，需使用其它计量分析软件。回归模型

5、的工作表展示本例通过工作表的数据组织，直观地说明回归分析的最终目的：残差平方和SSR的最小化。给定自变量*和应变量Y的数据，同时假定方程参数a和b也为给定，可以得到根据一元回归模型计算的应变量预测值Y、预测值和实际值的差以及残差的平方和数据，进一步通过调整初始的参数a和b，可以直观观察各数据点的残差及其平方和的变动。其中C-E各列数据均包含公式，即为根据给定的*和Y原始数据和给定的模型参数a和b计算得出：回归模型的目的是实现残差平方和SSR即单元格E5的最小化，可以使用E*cel Solver工具进展求解。从工具菜单中启动规划求解，将目标单元格设定为E5的最小值，可变单元格为参数B4：B5，界

6、面如下：点击求解即返回最优结果：除了使用规划求解方法，通过E*cel进展回归分析有四种途径可供选择：快捷方式：图表趋势线 界面工具：数据分析 函数方法：LINEST及其他 公式函数方法：矩阵代数方法一：图表趋势线用E*cel的图表向导建立一个*Y散点图，而后选中数据系列，在右键菜单中选择添加趋势线，可对图表数据生成一个快捷的拟合序列。在添加趋势线功能中，可供选择的趋势线模型包括：对数：y = c + b*ln(*) ，即线性-对数模型。指数： y = a*e*p(b*)，即对数-线性模型，两边取对数后变成ln(y) = ln(a) + b* 乘幂：y = a*(*b)，即双对数模型，因取对数后

7、模型变成ln(y) = ln(a) + b*ln(*)多项式：y = a + b* + c*2 + d*3 + 移动平均，移动平均仅适用于时间序列数据。在*Y散点图中，选中图表的序列对象，点击右键弹出快捷菜单：选择添加趋势线后出现对话框，从中可以选择预测模型和显示选项，如选择线性模型并要求显示公式：完成后的效果如下：方法二：数据分析工具中的回归分析趋势线快捷方法只适用于一元回归，并且仅返回简要的统计信息。对于详尽的回归分析，可以通过E*cel数据分析界面工具实现。使用数据分析工具之前，要确认分析工具库插件处于加载状态：从E*cel工具菜单中选择数据分析，在列表功能中选择回归：确定后出现回归分析

8、界面对话框，在此对话框中进展输入输出设定，包括自变量和应变量的所在区域、回归选项及输出结果，E*cel将返回预定格式的数据和图表结果：回归分析三E*cel提供的回归分析函数主要包括：使用函数INTERCEPT、SLOPE、RSQ、STEY*和FORECAST拟合回归线使用函数LINEST拟合回归线使用函数TREND获得回归预测结果这些函数的使用方法如下：其中，LINEST函数返回的结果排列格式为：以上函数结果可同数据分析工具的回归结果进一步比照：但界面工具返回的是静态结果，而函数方法可以返回动态的统计分析结果，随原始数据的改变而改变。方法四：矩阵代数使用矩阵代数方法需要了解E*cel矩阵函数的

9、使用和回归模型参数的矩阵代数表达式。通过在工作表中的步步推导和计算，可以得出回归的参数结果：在熟悉矩阵操作的情况下，仅需一步操作就可到达目的：回归分析方法的选择趋势线是最简捷的方法，但仅适用于一元回归； 界面工具方法的输入输出更友好，但只能返回静态结果； 函数方法可以返回动态数据，但需了解函数语法和输出格式； 矩阵代数方法灵活性最高，但用户友好程度最低。回归模型的计算公式说明回归分析可以灵活应用于描述不同函数形式的变量关系。一元回归模型中的参数的计算公式为，其中，n为样本观测点数，上划线的*和Y分别代表均值。根据上述公式，可以在原始数据根底上逐步计算回归的参数估计值。可以看出：1回归线通过*和

10、Y的均值点；2最小二乘斜率是样本Y值的加权平均值；3权重之和为零；回归模型的函数形式回归分析可以灵活应用于描述不同函数形式的变量关系。线性模型可分为参数线性和变量线性模型，线性回归仅指参数线性的回归模型，而解释变量无需是线性的。比拟：。主要的参数线性变量非线性模型形式：线性-对数：y = a + b*ln(*) + u 对数-线性： ln(y) = a + b* + u 双对数：ln(y) = a + b*ln(*) + u 多项式：y = a + b* + c*2 + d*3 + + u 双曲：y = a + b*(1/*) + uE*cel中处理非线性模型，可通过两种方法实现：数据变换或趋

11、势线方法。前者是将非现性的数据转换为线性数据后进展回归分析，方法和普通回归分析一样，后者则是利用E*cel的添加趋势线功能，选择适宜的模型形式。本例采用三种不同的模型形式进展拟合，效果及比拟结果如下：对本例不同模型拟合的比照结果说明双曲模型的残差平方和最低。OLS回归的缺陷：蒙特卡洛模拟演示OLS回归在处理异常值时的表现较差。本节通过一个实例和蒙特卡洛模拟分析方法说明OLS回归在稳健性方面的缺陷。考虑两个数据样本，其中一个为干净数据，另一个样本包含一个异常值J19单元格：从图表和样本的描述统计可以看出，异常值的引入导致OLS回归效果发生很大变动，也即单个数据观察值的变动可以完全破坏OLS回归结果，因此OLS回归的稳健性存在较大缺陷。假定方程误差项服从均值为0，标准差为10的正态分布，进一步通过蒙特卡洛模拟可以观察OLS回归的参数结果。工作表中给定B5单元格的随机项标准差，而后通过函数=NORMIN