Excel详细教程之回归分析

Excel详细教程之回归分析除了数据存储和管理功能，Excel为基于工作表的数据分析提供了各类不同的工具和方法，用于各类通用的数据分析工作。从应用和表现形式看，Excel的数据分析工具和方法可以分为以下几个类别：1）基于工作表函数和公式的分析能力使用Excel内置的公式计算和统计分析函数，例如通过本期的技巧文章“Excel矩阵函数和公式的使用”中介绍的矩阵函数，可以完成回归分析。使用Excel的公式和函数功能，需了解相关的语法和参数，同时可能还需熟悉所使用的分析方法的数学推导过程。2）基于用户界面的数据分析工具Excel提供用于统计和计量分析的集成界面工具包，使用该工具包可进行描述统计、方差分析、假设检验、回归抽样等统计分析。在“分析工具库”已正确加载的前提下，点击Excel工具菜单中的“数据分析”选项，可调出数据分析功能选择界面，选择一项具体分析功能后即可进入详细的输入输出和设置界面：在上步中选择的不同功能项，会弹出不同的分析界面，一般情况下该分析界面包括参数的输入和分析结果的输出选择以及与该功能相关的具体参数选项。数据分析工具提供交互界面的分析功能，其优点是容易理解和使用，但输出结果是静态的，如需变更输入数据或参数，都需重新启动分析工具以获得修正结果。为了输出动态、可随时更改输入选项的结果，需要使用Excel的函数和公式功能。3）其他快捷数据分析方法Excel中的某些对象操作内含了简单的可视化数据分析能力，例如区域的选择、图表数据的选择等。这些快捷工具可以简化使用函数或界面工具的输入输出过程。4）来自用户自定义或第三方的增强数据分析工具Excel提供了用户开发平台，高级用户可在此基础上开发专用的数据分析函数或工具。同时，由于Excel的通用性，有许多基于Excel的商业统计和数据分析插件可供选择。这些工具和软件在不同程度和不同领域增强和扩充了Excel的数据分析能力。例如，DataDirectMX就是一个可以扩充Excel金融数据分析能力的第三方软件。OLS回归分析一元回归分析是估计一个因变量和一个自变量之间平均关系的统计方法。定义基本关系式为：Y=a+bx，回归通过最小二乘法找出一条能最佳拟合所有观测数据的直线，也即使残差平方和最小化。`方程参数a和b的计算公式为：多元回归分析是估算一个因变量和两个或两个以上的自变量之间的平均关系的方法。如价格水平、广告支出、消费者收入、喜好和竞争状况对销售额的影响；公司收益增长、收益波动、股票贝塔值、通货膨胀率等对股票价格的影响等。多元回归的模型形式为：其中，Y为自变量或被解释变量，x1到xk为自变量或解释变量，b1到bk为回归系数，e为回归误差项。在Excel中进行多元回归可利用数据分析中的回归工具，也使用函数LINEST获得结构。Excel最多允许16个回归变量，若方程超出此限制，需使用其它计量分析软件。回归模型的工作表展示本例通过工作表的数据组织，直观地说明回归分析的最终目的：残差平方和SSR的最小化。给定自变量X和应变量Y的数据，同时假定方程参数a和b也为给定，可以得到根据一元回归模型计算的应变量预测值Y’、预测值和实际值的差以及残差的平方和数据，进一步通过调整初始的参数a和b，可以直观观察各数据点的残差及其平方和的变动。其中C-E各列数据均包含公式，即为根据给定的X和Y原始数据和给定的模型参数a和b计算得出：回归模型的目的是实现残差平方和SSR即单元格E5的最小化，可以使用ExcelSolver工具进行求解。从工具菜单中启动“规划求解”，将目标单元格设定为E5的最小值，可变单元格为参数B4：B5，界面如下：点击“求解”即返回最优结果：除了使用规划求解方法，通过Excel进行回归分析有四种途径可供选择：o快捷方式：图表趋势线o界面工具：数据分析o函数方法：LINEST及其他o公式函数方法：矩阵代数方法一：图表趋势线用Excel的图表向导建立一个XY散点图，而后选中数据系列，在右键菜单中选择“添加趋势线”，可对图表数据生成一个快捷的拟合序列。在添加趋势线功能中，可供选择的趋势线模型包括：?对数：y=c+b*ln(x)，即线性-对数模型。?指数：y=a*exp(bx)，即对数-线性模型，两边取对数后变成ln(y)=ln(a)+b*x?乘幂：y=a*(x^b)，即双对数模型，因取对数后模型变成ln(y)=ln(a)+b*ln(x)?多项式：y=a+b*x+c*x^2+d*x^3+....?移动平均，移动平均仅适用于时间序列数据。在XY散点图中，选中图表的序列对象，点击右键弹出快捷菜单：选择“添加趋势线”后出现对话框，从中可以选择预测模型和显示选项，如选择线性模型并要求显示公式：完成后的效果如下：方法二：数据分析工具中的回归分析趋势线快捷方法只适用于一元回归，并且仅返回简要的统计信息。对于详尽的回归分析，可以通过Excel数据分析界面工具实现。使用数据分析工具之前，要确认“分析工具库”插件处于加载状态：从Excel工具菜单中选择“数据分析”，在列表功能中选择“回归”：确定后出现回归分析界面对话框，在此对话框中进行输入输出设定，包括自变量和应变量的所在区域、回归选项及输出结果，Excel将返回预定格式的数据和图表结果：回归分析（三）Excel提供的回归分析函数主要包括：?使用函数INTERCEPT、SLOPE、RSQ、STEYX和FORECAST拟合回归线?使用函数LINEST拟合回归线?使用函数TREND获得回归预测结果这些函数的使用方法如下：其中，LINEST函数返回的结果排列格式为：以上函数结果可同数据分析工具的回归结果进一步对比：但界面工具返回的是静态结果，而函数方法可以返回动态的统计分析结果，随原始数据的改变而改变。方法四：矩阵代数使用矩阵代数方法需要了解Excel矩阵函数的使用和回归模型参数的矩阵代数表达式。通过在工作表中的步步推导和计算，可以得出回归的参数结果：在熟悉矩阵操作的情况下，仅需一步操作就可达到目的：回归分析方法的选择o趋势线是最简捷的办法，但仅适用于一元回归；o界面工具方法的输入输出更友好，但只能返回静态结果；o函数方法可以返回动态数据，但需了解函数语法和输出格式；o矩阵代数方法灵活性最高，但用户友好程度最低。回归模型的计算公式说明回归分析可以灵活应用于描述不同函数形式的变量关系。一元回归模型中的参数的计算公式为，其中，n为样本观测点数，上划线的X和Y分别代表均值。根据上述公式，可以在原始数据基础上逐步计算回归的参数估计值。可以看出：1）回归线通过X和Y的均值点；2）最小二乘斜率是样本Y值的加权平均值；3）权重之和为零；回归模型的函数形式回归分析可以灵活应用于描述不同函数形式的变量关系。线性模型可分为参数线性和变量线性模型，线性回归仅指参数线性的回归模型，而解释变量无需是线性的。比较：。主要的参数线性变量非线性模型形式：?线性-对数：y=a+b*ln(x)+u?对数-线性：ln(y)=a+b*x+u?双对数：：ln(y)=a+b*ln(x)+u?多项式：y=a+b*x+c*x^2+d*x^3+....+u?双曲：y=a+b*(1/x)+uExcel中处理非线性模型，可通过两种方法实现：数据变换或趋势线方法。前者是将非现性的数据转换为线性数据后进行回归分析，方法和普通回归分析相同，后者则是利用Excel的添加趋势线功能，选择合适的模型形式。本例采用三种不同的模型形式进行拟合，效果及比较结果如下：对本例不同模型拟合的对比结果表明双曲模型的残差平方和最低。OLS回归的缺陷：蒙特卡洛模拟演示OLS回归在处理异常值时的表现较差。本节通过一个实例和蒙特卡洛模拟分析方法说明OLS回归在稳健性方面的缺陷。考虑两个数据样本，其中一个为干净数据，另一个样本包含一个异常值（J19单元格）：从图表和样本的描述统计可以看出，异常值的引入导致OLS回归效果发生很大变动，也即单个数据观察值的变动可以完全破坏OLS回归结果，因此OLS回归的稳健性存在较大缺陷。假定方程误差项服从均值为0，标准差为10的正态分布，进一步通过蒙特卡洛模拟可以观察OLS回归的参数结果。工作表中给定B5单元格的随机项标准差，而后通过函数“=NORMINV(RAND(),0,s)”生成该分布假设下的随机值，其中s为标准差。模拟结果的统计和直方图如下：本步中蒙特卡洛模拟的步骤是：1）获得初始的模型参数值和随机项的标准差2）生成随机数并根据模型形式生成匹配的数据样本3）运行回归分析，记录估算的参数值4）重复上述步骤1000次5）计算1000次模拟结果的均值、标准差等描述统计值6）计算1000次模拟结果的区间分布并生成直方图