Ecel在相关与回归分析中应用

《Ecel在相关与回归分析中应用》由会员分享，可在线阅读，更多相关《Ecel在相关与回归分析中应用（8页珍藏版）》请在装配图网上搜索。

1、Excel 在相关与回归分析中应用财会金融学院 李潭Excel 作为强大的数据处理软件，包含大量的相关与回归分析函数和工具。通过这些函 数和工具我们可以方便快捷地进行相关与回归分析，降低对统计专业知识的要求，提高应用 效率。应用好Excel软件，对于利用统计专业知识解决实际问题，具有事半功倍的效果。本 文以Excel2007为例来介绍Excel在相关与回归分析中的应用。一、 相关与回归分析中常用 Excel 函数或功能模块 在相关与回归分析中所用函数和功能模块如表1 所示。表1相关与回归分析常用Excel函数列表函数或功能模 块名称函数说明语法形式或操作步骤CORREL返回单元格区域数列1和

2、数列2之间的相关系数CORREL(array1,array2)Array1第一组数值单元格区域Array2第二组数值单元格区域INTERCEPT利用现有的x值与y值 计算直线与y轴的截距INTERCEPT(known_ys,known_xs)Known_ys为因变的观察值或数据集合Known xs为自变的观察值或数据集合SLOPE返回根据 known_ys和 known_xs中的数据点拟合 的线性回归直线的斜率SLOPE(known_ys,known_xs)Known_ys为因变量数据点数组或单元格 区域。Known xs为自变量数据点集合区域LINEST可通过使用最小一乘法计 算与现有数据最

3、佳拟合的 直线，返回描述此直线的截 距和斜率LINEST(known_ys, known_xs, const, stats)内为可选项，返回回归直线的截距和斜率 (利用数组计算)STEYX返回通过线性回归法计算 每个x的y预测值时所 产生的标准误差STEYX(known_ys,known_xs)Known_ys为因变量数据点数组或区域Known xs为自变量数据点数组或区域TREND返回一条线性回归拟合线 的预测值TREND(known_ys,known_xs,new_xs,const)Known_ys为因变的观察值或数据集合 Known_xs为自变的观察值或数据集合 New xs为用于预测y

4、值的新x值相关系数工具返回两列数据的相关系数 矩阵操作步骤：数据一数据分析一相关系数回归工具返回相关与回归分析的各 项参数及统计检验结果操作步骤：数据一数据分析一回归作图法利用散点图，采用作图法求 解回归方程操作步骤:插入一图表一散点图一(选中点， 右击)添加趋势线f趋势线选项f显示公式 和R平方值二、 Excel 在相关与回归分析中的应用示例(一) 相关表的编制相关表是将自变量x按照从小到大的顺序排列，所对应的y依次对应排列而形成的表格。通过相关表可以判断两个变量间是否具有相关关系以及相关的方向。根据自变量是否分组， 相关表分成简单相关表、单变量分组相关表和双变量分组相关表。下面以表2中 2

5、0家同类 工业企业产量与单位成本资料为例说明相关表的编制。表 22007 年某地区 20 家同类工业企业产量与单位成本统计表序号产量（吨）单位成本（兀/吨）序号产量（吨）单位成本（兀/吨）15001 000117009002800890128008803600950131 00072047009601460090059007401580085061 0007001660092075009801770092088009001880087097009101980085010600940209008001简单相关表的编制新建 Excel 工作簿文件，文件名为“相关与回归分析”，将表2 中数据复制粘贴

6、到相关 与回归分析工作簿中表sheetl中，如图1所示。图 1 产量与单位成本原始数据将光标移至 B 列任意数值上，在“开始”选项卡下，单击“编辑”组的“排序和筛选” 按钮，选择升序，即可得到简单相关表，如图 2 所示。通过“数据”选项卡中“排序和筛选” 组的相关按钮，同样可以实现数据的排序。根据简单相关表即可对两变量间相关方向加以判 断。序号产量次（吨）单位成本F（元/吨）50010005009802I121314：1516171019202110141611IT12151819T20图：2600600600600700700700700800800800800800800900900100

7、0950940900920960910900920890900880850870850740800700产量与单位成本简单相关表T加22单变量分组相关表的编制i弘曲皿加亘对图1中sheetl数据，利用数据透视表，对自变量进行分组，编制单变量分组相关表。具体操作步骤如下：（1）将光标移至数据区域任意单元格中（这样做是为了让Excel自动选中连续的数据区域），在“插入”选项卡下“表”组中，单击“数据透视表”，此时会弹出创建数据透视表对话框，点击现有工作表，再点击数据区域外的任意单元格，如F2,即可得到如图3所 示内容。图 3 创建数据透视表（2） 点确定后，出现数据透视表字段列表对话框，将产量放入

8、行标签区域，再将产量 和单位成本均放入数值区域，然后对数值区域中“求和项产量”进行操作:单击下拉按钮一 值字段设置一计算类型改为计数；同样将“求和项单位成本”计算类型改为平均值；最后即可得到单变量分组相关表，操作结果如图4 所示。50029906004927. 57004922. 50006873.3333333900才770110002710值行标签F计数项:产量X平均值项:单拉成本乎数据透視表字段列表在以下医域间拖动字段：V报表筛选蟲列标签图 4 数据透视表字段列表及结果3 双变量分组相关表的编制 将自变量和因变量均分组，借助于数据透视表即可编制双变量分组相关表。具体步骤如 下：(1) 采

9、用IF函数对因变量变位成本进行组距式分组。在相关分析工作簿表sheetl中， D1单元格输入“按单位成本分组”字段名，D2单元格中输入公式：=IF(C2v800,800 以下,IF(C2v900,800900,IF(C2v1000,900-1000,1000 以上)，即可实 现将单位成本按数值大小，分成“800 以下， 800-900， 900-1000， 1000 以上”四组。再利 用智能填充按钮将公式从D2复制到D21，即可标示出20个数值所属的组。(2) 利用数据透视表，编制双变量分组相关表。操作步骤同单变量相关表的编制，下 面重点介绍数据透视表字段列表对话框的设置。将产量字段放入列标签

10、，将按单位成本分组 字段放入行标签区域和数值区域。如图5 所示。图 5 双变量分组相关表字段列表设置然后右击数据透视表F5至F8任一单元格，选排序一降序，再进行拖动调整即可得到如图 6 所示双变量分组相关表。计数项:按单拉成本分组 行标签1列标签-5006007008009001000总计1000以上11900-100014：4110S00-900516200以下123总计24462220图 6 双变量分组相关表（二）相关图的绘制 相关图即散点图，是将自变量和因变量在第一象限描绘出来的点组成的图形。散点图的 绘制步骤如下：在相关与回归分析工作簿sheetl工作表中，选中B1： C21单元格，“

11、插入”选项卡一“图 表”组一“散点图”下拉按钮一选第一项（仅带数据标记的散点图）得到原始相关图，选中 相关图，通过“设计”和“布局”选项卡，经过对标题栏及坐标轴栏的美化，即可得到如图 7 所示散点图。产量与单位成本相关图图 7 相关图（三）相关系数的计算1.函数计算法。运用Excel中CORREL函数可以很方便计算出所要的结果。具体操作步 骤如下：在相关与回归分析工作簿sheetl工作表中，E2单元格输入公式“=CORREL（B2:B21,C2:C21）”，其中B2:B21表示自变量数列，C2:C21表示因变量数列，可分别通过拖动鼠标选中的形式实现。回车后即可得到两变量间的相关系数r。结果如图

12、8所 示。图 8 CORREL 函数相关系数2数据分析工具法。（1）数据分析工具库的加载。数据分析工具库需要加载后才能使用，具体加载步骤如 下：单击左上角office按钮一Excel选项f加载项f分析工具库f转到，即出现“加载宏” 对话框，在分析工具库前的方框内打“丁”单击“确定”即完成分析工具库的加载。此时, 在“数据”选项卡f “分析”组出现了 “数据分析”工具。（2）利用数据分析工具计算相关系数。在相关与回归分析工作簿sheetl工作表中，单 击“数据”选项卡f “分析”组f “数据分析”工具f相关系数f确定，出现“相关系数” 对话框，如图9所示。将输入区域选中B1:C21,标志位于第一

13、行打“厂，输出区域选中E2, 确定后即可计算得到相关系数计算矩阵，如图10所示。图 9 分析工具库计算相关系数产量x（吨）单位成本y 元/吨）产量X（吨）1单位成本y（元/吨-0. 91931图 10 分析工具库计算相关系数结果四） 一元线性回归分析-元线性回归分析的关键是确定回归方程y二a + bx中参数a，b的值。在Excel中参c数a，b的确定，常用的方法有以下三种:1.函数法。确定参数a , b的函数可以有两组，一组是利用SLOPE函数和INTERCEPT 函数分别计算b和a的值；另一组是利用LINEST函数采用数组计算形式直接计算出b和a 的值。现利用相关与回归分析工作簿sheetl

14、工作表中的数据来介绍两组函数的用法。具体 公式及计算结果如图11所示。H2長 | =SL0FEC2:C21, E2 汨 21)BcDEFGHI1产量E（吨）单位成本y （元/吨函数名称公式结果25 001000方法1mlope函数=SLOPEC2:C21,處汨21)-0. 5201 .回归系数b3000890intercept 函数=INTERCEPT(C2:C21, E2:B21)1263. 0725截距a46009505700960方法2linest函数=linest(02:0212汨21)回归系数b截距a6you740-0. 52011263. S725图 11 函数计算回归参数方法1中

15、，在H2、H3单元格中分别输入左边的公式，即可得到回归系数b和截距a的 值。注意公式中，数值区域范围是先输入因变量的值域，再输入自变量的值域。方法2中，同时选中H6和I6单元格，输入公式“ =LINEST（C2:C21,B2:B21）”，然后 同时按Ctrl+Shift+Enter,进行数组运算，即可在H6和I6单元格得到回归系数b和截距a的 值。2数据分析工具法。仍以相关与回归分析工作簿sheetl工作表中的数据为例，操作步 骤如下：单击“数据”选项卡一“分析”组一“数据分析”工具一回归一确定，出现“回归” 对话框，如图12所示。正确输入Y值和X值区域，并标志和置信度前方框内打“丁”输出 区

16、域选中E2,确定后即可计算得到回归分析结果，如图13所示。SUMAEY OUTFIT：r回归统计Multiple R0. 92Fl Squar 已0. 85Adjusted R Sqi0. 84标准误差33.51观测值20. 00方差分析ifS3MSFSignif icmce F回归分析1. 00110364.80110364.8098. 270. 00残差18.0020215.201123.07总计19. 00130580.00Coefficients标准误差t St atF-valueLower 95!*5Upper 95K下限95. 0%上限95. 0%Intercept1263.873

17、9. 5431.960. 001180.801346.941180.801346.94产量X（吨）-0.520. 05-9.910. 00-0. 63-0.41-0. 63-0.41图 13 分析工具库进行回归分析通过分析工具库中回归分析，可以得到相关系数R、R2、回归系数B、截距a和估计标准误差等数值，并且可以进行假设检验和区间估计。3作图法。利用散点图，添加趋势线也可以自动计算出回归方程。下面利用相关与回 归分析工作簿sheetl工作表中的数据来介绍其操作过程。选中B1:C21-单击“插入”选项 卡一“图表”组一散点图一选中点，右击一添加趋势线一“设置趋势线格式”对话框一趋势线选项一在“显示公式”和“显示R平方值”前方框内打“丁”，即可得到回归方程和R2值。如图 14 所示。图 14 作图法求回归方程