下载
加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 Excel回归分析

Excel回归分析.doc

Excel回归分析

Sally景
2017-09-26 0人阅读 举报 0 0 暂无简介

简介:本文档为《Excel回归分析doc》,可适用于综合领域

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

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/14

Excel回归分析

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利