[Excel详细教程之回归分析.docVIP

Excel详细教程之回归分析除了数据存储和管理功能，Excel为基于工作表的数据分析提供了各类不同的工具和方法，用于各类通用的数据分析工作。从应用和表现形式看，Excel的数据分析工具和方法可以分为以下几个类别： 1）?基于工作表函数和公式的分析能力 使用Excel内置的公式计算和统计分析函数，例如通过本期的技巧文章“Excel矩阵函数和公式的使用”中介绍的矩阵函数，可以完成回归分析。使用Excel的公式和函数功能，需了解相关的语法和参数，同时可能还需熟悉所使用的分析方法的数学推导过程。 2）?基于用户界面的数据分析工具 Excel提供用于统计和计量分析的集成界面工具包，使用该工具包可进行描述统计、方差分析、假设检验、回归抽样等统计分析。 在“分析工具库”已正确加载的前提下，点击Excel工具菜单中的“数据分析”选项，可调出数据分析功能选择界面，选择一项具体分析功能后即可进入详细的输入输出和设置界面： 在上步中选择的不同功能项，会弹出不同的分析界面，一般情况下该分析界面包括参数的输入和分析结果的输出选择以及与该功能相关的具体参数选项。 数据分析工具提供交互界面的分析功能，其优点是容易理解和使用，但输出结果是静态的，如需变更输入数据或参数，都需重新启动分析工具以获得修正结果。为了输出动态、可随时更改输入选项的结果，需要使用Excel的函数和公式功能。 3）?其他快捷数据分析方法 Excel中的某些对象操作内含了简单的可视化数据分析能力，例如区域的选择、图表数据的选择等。这些快捷工具可以简化使用函数或界面工具的输入输出过程。 4）?来自用户自定义或第三方的增强数据分析工具 Excel提供了用户开发平台，高级用户可在此基础上开发专用的数据分析函数或工具。同时，由于Excel的通用性，有许多基于Excel的商业统计和数据分析插件可供选择。这些工具和软件在不同程度和不同领域增强和扩充了Excel的数据分析能力。例如，DataDirect MX就是一个可以扩充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的最小化，可以使用Excel Solver工具进行求解。 从工具菜单中启动“规划求解”，将目标单元格设定为E5的最小值，可变单元格为参数B4：B5，界面如下： ? 点击“求解”即返回最优结果： 除了使用规划求解方法，通过Excel进行回归分析有四种途径可供选择： 快捷方式：图表趋势线 界面工具：数据分析 函数方法：LINEST及其他 公式函数方法：矩阵代数 方法一：图表趋势线 用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散点图中，选中图表的序列对象，点击右键弹出快捷菜单： 选择“