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_ys,known_xs) Known_ys?? 为因变的观察值或数据集合 Known_xs?? 为自变的观察值或数据集合 SLOPE 返回根据 known_ys 和 known_xs 中的数据点拟合的线性回归直线的斜率 SLOPE(known_ys,known_xs) Known_ys?? 为因变量数据点数组或单元格区域。 Known_xs?? 为自变量数据点集合区域 LINEST 可通过使用最小二乘法计算与现有数据最佳拟合的直线，返回描述此直线的截距和斜率 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 值的新 x 值 相关系数工具 返回两列数据的相关系数矩阵 操作步骤：数据→数据分析→相关系数 回归工具 返回相关与回归分析的各项参数及统计检验结果 操作步骤：数据→数据分析→回归 作图法 利用散点图，采用作图法求解回归方程 操作步骤：插入→图表→散点图→（选中点，右击）添加趋势线→趋势线选项→显示公式和R平方值 二、 Excel在相关与回归分析中的应用示例 （一） 相关表的编制 相关表是将自变量x按照从小到大的顺序排列，所对应的y依次对应排列而形成的表格。通过相关表可以判断两个变量间是否具有相关关系以及相关的方向。根据自变量是否分组，相关表分成简单相关表、单变量分组相关表和双变量分组相关表。下面以表2中20家同类工业企业产量与单位成本资料为例说明相关表的编制。 表 2 2007年某地区20家同类工业企业产量与单位成本统计表 序号 产量（吨） 单位成本（元/吨） 序号 产量（吨） 单位成本（元/吨） 1 500 1 000 11 700 900 2 800 890 12 800 880 3 600 950 13 1 000 720 4 700 960 14 600 900 5 900 740 15 800 850 6 1 000 700 16 600 920 7 500 980 17 700 920 8 800 900 18 800 870 9 700 910 19 800 850 10 600 940 20 900 800 1． 简单相关表的编制 新建Excel工作簿文件，文件名为“相关与回归分析”，将表2中数据复制粘贴到相关与回归分析工作簿中表sheet1中，如图1所示。 将光标移至B列任意数值上，在“开始”选项卡下，单击“编辑”组的“排序和筛选”按钮，选择升序，即可得到简单相关表，如图2所示。通过“数据”选项卡中“排序和筛选”组的相关按钮，同样可以实现数据的排序。根据简单相关表即可对两变量间相关方向加以判断。 2． 单变量分组相关表的编制 对图1中sheet1数据，利用数据透视表，对自变量进行分组，编制单变量分组相关表。具体操作步骤如下： （1） 将光标移至数据区域任意单元格中（这样做是为了让Excel自动选中连续的数据区域），在“插入”选项卡下“表”组中，单击“数据透视表”，此时会弹出创建数据透视表对话框，点击现有工作表，再点击数据区域外的任意单