Excel回归分析.doc

Excel回归分析(一)除了数据存储和笛理功能,Excel为皐于工作表的数据分析提供了各类不同的工具和方法,用于各类通用的数据分析工作。从应用和表现形式看,Reel的数据分析工具和方法可以分为以下几个类别:1) 基于工作表函数和公式的分析能力使用Excel内置的公式计算和统计分析函数,例如通过本期的技巧文章“Excel矩阵函数和公式的使用”屮介绍的矩阵函数,可以完成冋归分析。使用氏cel的公式和函数功能,需了解相关的语法和参数,同时可能还需熟悉所使用的分析方法的数学推导过程。2) 基于用户界面的数据分析工具Excel提供用于统计和计量分析的集成界面工具包,使用该工具包可进行描述统计、方差分析、假设检验、冋归抽样等统计分析。在“分析工具库”已正确加载的前提下,点击B<cel工具菜单中的“数据分析”选项,可调出数据分析功能选择界面,选择一项具体分析功能后即可进入详细的输入输出和设置界協在上步屮选择的不同功能项,会弹出不同的分析界而,一般情况下该分析界而包括参数的输入和分析结杲的输出选择以及与该功能相关的具体参数选项。数据分析工具提供交互界面的分析功能,英优点是容易理解和使用,但输出结果是静态的,如需变更输入数据或参数,都需重新启动分析工具以获得修止结杲。为了输出动态、可随时更改输入选项的结果,需要使用Excel的函数和公式功能。3) 其他快捷数据分析方法Excel屮的某些对象操作内含了简单的可视化数据分析能力,例如区域的选择、图表数据的选择等。这些快捷工具可以简化使用函数或界面工具的输入输出过程。4) 来自用户自定义或第三方的增强数据分析工具Excel提供了用户开发平台,高级用户可在此棊础上开发专用的数据分析函数或工具。同时,由于Excel的通用性,有许多棊于Excel的商业统计和数据分析插件可供选择。这些工具和软件在不同程度和不同领域增强和扩充了Excel的数据分析能力。例如,DataDirectMX就是一个可以扩充Excel金融数据分析能力的第三方软件。OLS回归分析一元冋归分析是估计一个因变量和一个自变量之间平均关系的统计方法。従义基本关系式为:Y=a+bx,|叫归通过最小二乘法找出一条能最佳拟合所有观测数据的宜线,也即使残劳平方和最小化。'方程参数a和b的计算公式为:多元冋归分析是估算一个I大1变量和两个或两个以上的自变量之间的平均关系的方法。如价格水平、广告支出、消费者收入、喜好和竞争状况对销售额的影响;公司收益增长、收益波动、股票贝塔值、通货膨胀率等对股票价格的影响等。多元冋归的模型形式为:y=S+坯耳+b2x2+.・・+耳耳+z英屮,Y为自变量或被解释变量,x1到xk为自变量或解释变量,b1到bk为冋归系数,e为冋归谋差项。在Excel屮进行多元冋归可利用数据分析屮的冋归工具,也使用函数LINEST获得结构。Excel最多允许16个回归变量,若方程超出此限制,需使用莫它计量分析软件。回归模型的工作表展示本例通过工作表的数据组织,玄观地说明回归分析的最终目的:残差平方和SSR的最小化。给泄口变量X和应变量丫的数据,同时假泄方程参数a和b也为给眾,可以得到根据一元冋归模型计算的应变量预测值Y、预测值和实际值的差以及残差的平方和数据,进一步通过调整初始的参数a和b,可以宜观观察各数据点的残差及英平方和的变动。ABC1DE 11 」*2通辻调整B痢B5中的歩数初始佰•观察転ES的变动3€L 1 410045斜率b98SR27f4066XY预汎Y殘菱歿羞平方和7187109--12-0**********-**********--6804624**********.0291615992181--•**********.0161913174217---,958)S) .R|QO Lfee彳♦2・6D・(Jioao )5英屮C-E各列数据均包含公式,即为根据给疋的X和丫原始数据和给龙的模型参数a和b计算得出:A1BcDE1!12通11训空B4T13141005斜率b988R^SUM(E7:E20)6X¥预測Y残差殘差平方勿7187=$B$4+$B$SWA7-B7-C7=07^282133s$B$4+$B$5mA8sB8<8=08^293115=$B$4+$B|5WA9=B9-C9=09^210490=$B$4+$B$5"A10■B10-C10=010^21151B3=B11-C11=