excel详细教程之回归分析

《excel详细教程之回归分析》由会员分享，可在线阅读，更多相关《excel详细教程之回归分析（14页珍藏版）》请在金锄头文库上搜索。

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

2、基于用户界面的数据分析工具 Excel 提供用于统计和计量分析的集成界面工具包，使用该工具包可进行描述统计、方差分析、 假设检验、回归抽样等统计分析。 在“分析工具库”已正确加载的前提下，点击 Excel 工具菜单中的“数据分析”选项，可调出数据分 析功能选择界面，选择一项具体分析功能后即可进入详细的输入输出和设置界面： 在上步中选择的不同功能项， 会弹出不同的分析界面， 一般情况下该分析界面包括参数的输入和 分析结果的输出选择以及与该功能相关的具体参数选项。 数据分析工具提供交互界面的分析功能，其优点是容易理解和使用，但输出结果是静态的，如需 变更输入数据或参数，都需重新启动分析工具以获得修

3、正结果。为了输出动态、可随时更改输入 选项的结果，需要使用 Excel 的函数和公式功能。 3） 其他快捷数据分析方法其他快捷数据分析方法 Excel 中的某些对象操作内含了简单的可视化数据分析能力，例如区域的选择、图表数据的选择 等。这些快捷工具可以简化使用函数或界面工具的输入输出过程。 4） 来自用户自定义或第三方的增强数据分析工具来自用户自定义或第三方的增强数据分析工具 Excel 提供了用户开发平台，高级用户可在此基础上开发专用的数据分析函数或工具。同时，由 于 Excel 的通用性，有许多基于 Excel 的商业统计和数据分析插件可供选择。这些工具和软件在 不同程度和不同领域增强和扩

4、充了 Excel 的数据分析能力。例如，DataDirect MX 就是一个可以 扩充 Excel 金融数据分析能力的第三方软件。 OLS 回归分析回归分析 一元回归分析是估计一个因变量和一个自变量之间平均关系的统计方法。定义基本关系式为： Y=a+bx，回归通过最小二乘法找出一条能最佳拟合所有观测数据的直线，也即使残差平方和最 小化。方程参数 a 和 b 的计算公式为： 多元回归分析是估算一个因变量和两个或两个以上的自变量之间的平均关系的方法。如价格水 平、广告支出、消费者收入、喜好和竞争状况对销售额的影响；公司收益增长、收益波动、股票 贝塔值、通货膨胀率等对股票价格的影响等。多元回归的模型

5、形式为： 其中，Y 为自变量或被解释变量，x1 到 xk 为自变量或解释变量，b1 到 bk 为回归系数，e 为回 归误差项。 在 Excel 中进行多元回归可利用数据分析中的回归工具，也使用函数 LINEST 获得结构。Excel 最多允许 16 个回归变量，若方程超出此限制，需使用其它计量分析软件。 回归模型的工作表展示回归模型的工作表展示 本例通过工作表的数据组织，直观地说明回归分析的最终目的：残差平方和 SSR 的最小化。 给定自变量 X 和应变量 Y 的数据，同时假定方程参数 a 和 b 也为给定，可以得到根据一元回归 模型计算的应变量预测值 Y、预测值和实际值的差以及残差的平方和数

6、据，进一步通过调整初 始的参数 a 和 b，可以直观观察各数据点的残差及其平方和的变动。 其中 C-E 各列数据均包含公式，即为根据给定的 X 和 Y 原始数据和给定的模型参数 a 和 b 计算 得出： 回归模型的目的是实现残差平方和 SSR 即单元格 E5 的最小化，可以使用 Excel Solver 工具进行 求解。 从工具菜单中启动“规划求解”，将目标单元格设定为 E5 的最小值，可变单元格为参数 B4：B5， 界面如下： 点击“求解”即返回最优结果： 除了使用规划求解方法，通过 Excel 进行回归分析有四种途径可供选择： o快捷方式：图表趋势线 o界面工具：数据分析 o函数方法：LI

7、NEST 及其他 o公式函数方法：矩阵代数 方法一：图表趋势线方法一：图表趋势线 用 Excel 的图表向导建立一个 XY 散点图，而后选中数据系列，在右键菜单中选择“添加趋势线”， 可对图表数据生成一个快捷的拟合序列。在添加趋势线功能中，可供选择的趋势线模型包括： ? 对数：y = c + b*ln(x) ，即线性-对数模型。 ? 指数： y = a*exp(bx)，即对数-线性模型，两边取对数后变成 ln(y) = ln(a) + b*x ? 乘幂：y = a*(xb)，即双对数模型，因取对数后模型变成 ln(y) = ln(a) + b*ln(x) ? 多项式：y = a + b*x +

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

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

10、而 改变。 方法四：矩阵代数方法四：矩阵代数 使用矩阵代数方法需要了解 Excel 矩阵函数的使用和回归模型参数的矩阵代数表达式。 通过在工作表中的步步推导和计算，可以得出回归的参数结果： 在熟悉矩阵操作的情况下，仅需一步操作就可达到目的： 回归分析方法的选择回归分析方法的选择 o趋势线是最简捷的办法，但仅适用于一元回归； o界面工具方法的输入输出更友好，但只能返回静态结果； o函数方法可以返回动态数据，但需了解函数语法和输出格式； o矩阵代数方法灵活性最高，但用户友好程度最低。 回归模型的计算公式说明回归模型的计算公式说明 回归分析可以灵活应用于描述不同函数形式的变量关系。一元回归模型中的参

11、数的计算公式为， 其中，n 为样本观测点数，上划线的 X 和 Y 分别代表均值。 根据上述公式，可以在原始数据基础上逐步计算回归的参数估计值。 可以看出： 1） 回归线通过 X 和 Y 的均值点； 2） 最小二乘斜率是样本 Y 值的加权平均值； 3） 权重之和为零； 回归模型的函数形式回归模型的函数形式 回归分析可以灵活应用于描述不同函数形式的变量关系。 线性模型可分为参数线性和变量线性模 型，线性回归仅指参数线性的回归模型，而解释变量无需是线性的。比较： 。 主要的参数线性变量非线性模型形式： ? 线性-对数：y = a + b*ln(x) + u ? 对数-线性： ln(y) = a +

12、b*x + u ? 双对数：ln(y) = a + b*ln(x) + u ? 多项式：y = a + b*x + c*x2 + d*x3 + + u ? 双曲：y = a + b*(1/x) + u Excel 中处理非线性模型，可通过两种方法实现：数据变换或趋势线方法。前者是将非现性的数 据转换为线性数据后进行回归分析，方法和普通回归分析相同，后者则是利用 Excel 的添加趋势 线功能，选择合适的模型形式。 本例采用三种不同的模型形式进行拟合，效果及比较结果如下： 对本例不同模型拟合的对比结果表明双曲模型的残差平方和最低。 OLS 回归的缺陷：蒙特卡洛模拟演示回归的缺陷：蒙特卡洛模拟演示

13、 OLS 回归在处理异常值时的表现较差。本节通过一个实例和蒙特卡洛模拟分析方法说明 OLS 回 归在稳健性方面的缺陷。 考虑两个数据样本，其中一个为干净数据，另一个样本包含一个异常值（J19 单元格）： 从图表和样本的描述统计可以看出，异常值的引入导致 OLS 回归效果发生很大变动，也即单个 数据观察值的变动可以完全破坏 OLS 回归结果，因此 OLS 回归的稳健性存在较大缺陷。 假定方程误差项服从均值为 0， 标准差为 10 的正态分布， 进一步通过蒙特卡洛模拟可以观察 OLS 回归的参数结果。工作表中给定 B5 单元格的随机项标准差，而后通过函数 “=NORMINV(RAND(),0,s)”生成该分布假设下的随机值，其中 s 为标准差。模拟结果的统计和直 方图如下： 本步中蒙特卡洛模拟的步骤是： 1） 获得初始的模型参数值和随机项的标准差 2） 生成随机数并根据模型形式生成匹配的数据样本 3） 运行回归分析，记录估算的参数值 4） 重复上述步骤 1000 次 5） 计算 1000 次模拟结果的均值、标准差等描述统计值 6） 计算 1000 次模拟结果的区间分布并生成直方图