Excel在相关与回归分析中应用.doc

Excel在相关与回归分析中应用
财会金融学院李潭
Excel作为强大的数据处理软件，包含大量的相关与回归分析函数和工具。通过这些函数和工具我们可以方便快捷地进行相关与回归分析，降低对统计专业知识的要求，提高应用效率。应用好Excel软件，对于利用统计专业知识解决实际问题，具有事半功倍的效果。本文以Excel2007为例来介绍Excel在相关与回归分析中的应用。
相关与回归分析中常用Excel函数或功能模块
在相关与回归分析中所用函数和功能模块如表1所示。
表1相关与回归分析常用Excel函数列表
函数或功能模块名称函数说明语法形式或操作步骤CORREL返回单元格区域数列1和数列2之间的相关系数CORREL(array1,array2)
Array1第一组数值单元格区域
Array2第二组数值单元格区域INTERCEPT利用现有的x值与y值计算直线与y轴的截距INTERCEPT(known_y's,known_x's)
Known_y's为因变的观察值或数据集合
Known_x's为自变的观察值或数据集合SLOPE返回根据known_y's和known_x's中的数据点拟合的线性回归直线的斜率SLOPE(known_y's,known_x's)
Known_y's为因变量数据点数组或单元格区域。
Known_x's为自变量数据点集合区域LINEST可通过使用最小二乘法计算与现有数据最佳拟合的直线，返回描述此直线的截距和斜率LINEST(known_y's,[known_x's],[const],[stats])
[]内为可选项，返回回归直线的截距和斜率（利用数组计算）STEYX返回通过线性回归法计算每个x的y预测值时所产生的标准误差STEYX(known_y's,known_x's)
Known_y's为因变量数据点数组或区域
Known_x's为自变量数据点数组或区域TREND返回一条线性回归拟合线的预测值TREND(known_y's,known_x's,new_x's,const)
Known_y's为因变的观察值或数据集合
Known_x's为自变的观察值或数据集合
New_x's为用于预测y值的新x值相关系数工具返回两列数据的相关系数矩阵操作步骤：数据→数据分析→相关系数回归工具返回相关与回归分析的各项参数及统计检验结果操作步骤：数据→数据分析→回归作图法利用散点图，采用作图法求解回归方程操作步骤：插入→图表→散点图→（选中点，右击）添加趋势线→趋势线选项→显示公式和R平方值
Excel在相关与回归分析中的应用示例
相关表的编制
相关表是将自变量x按照从小到大的顺序排列，所对应的y依次对应排列而形成的表格。通过相关表可以判断两个变量间是否具有相关关系以及相关的方向。根据自变量是否分组，相关表分成简单相关表、单变量分组相关表和双变量分组相关表。下面以表2中20家同类工业企业产量与单位成本资料为例说明相关表的编制。
表22007年某地区20家同类工业企业产量与单位成本统计表
序号产量（吨）单位成本（元/吨）序号产量（吨）单位成本（元/吨）1500100011700900280089012800880360095013100072047009601460090059007401580085061000700166009207500980177009208800900188008709700910198008501060094020900800
简单相关表的编制
新建Excel工作簿文件，文件名为“相关与回归分析”，将表2中数据复制粘贴到相关与回归分析工作簿中表sheet1中，如图1所示。
图1产量与单位成本原始数据

将光标移至B列任意数值上，在“开始”选项卡下，单击“编辑”组的“排序和筛选”按钮，选择升序，即可得到简单相关表，如图2所示。通过“数据”选项卡中“排序和筛选”组的相关按钮，同样可以实现数据的排序。根据简单相关表即可对两变量间相关方向加以判断。
图2产量与单位成本简单相关表

单变量分组相关表的编制
对图1中sheet1数据，利用数据透视表，对自变量进行分组，编制单变量分组相关表。具体操作步骤如下：
将光标移至数据区域任意单元格中（这样做是为了让Excel自动选中连续的数据区域），在“插入”选项卡下“表”组中，单击“数据透视表”，此时会弹出创建数据透视表对话框，点击现有工作表，再点击数据区域外的任意单元格，如F2，即可得到如图3所示内容。
图3创建数据透视表

点确定后，出现数据透视表字段列表对话框，将产量放入行标签区域，再将产量和单位成本均放入数值区域，然后对数值区域中“求和项产量”进行操作：单击下拉按钮→值字段设置→计算类型改为计数；同样将“求和项单位成本”计算类型改为平均值；最后即可得到单变量分组相关表，