1、Excel 在相关与回归分析中应用财会金融学院 李潭Excel 作为强大的数据处理软件，包含大量的相关与回归分析函数和工具。通过这些函数和工具我们可以方便快捷地进行相关与回归分析，降低对统计专业知识的要求，提高应用效率。应用好 Excel 软件，对于利用统计专业知识解决实际问题，具有事半功倍的效果。本文以 Excel2007 为例来介绍 Excel 在相关与回归分析中的应用。一、 相关与回归分析中常用 Excel 函数或功能模块在相关与回归分析中所用函数和功能模块如表 1 所示。表 1 相关与回归分析常用 Excel 函数列表 函数或功能模块名称 函数说明 语法形式或操作步骤CORREL 返回
2、单元格区域 数列 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 为自变量数据点集合
3、区域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_
4、xs 为自变的观察值或数据集合New_xs 为用于预测 y 值的新 x 值相关系数工具 返回两列数据的相关系数矩阵 操作步骤：数据数据分析相关系数回归工具 返回相关与回归分析的各项参数及统计检验结果 操作步骤：数据数据分析回归作图法 利用散点图，采用作图法求解回归方程操作步骤：插入图表散点图（选中点，右击）添加趋势线趋势线选项显示公式和 R 平方值二、 Excel 在相关与回归分析中的应用示例（一） 相关表的编制相关表是将自变量 x 按照从小到大的顺序排列，所对应的 y 依次对应排列而形成的表格。通过相关表可以判断两个变量间是否具有相关关系以及相关的方向。根据自变量是否分组，相关表分成简单相关
5、表、单变量分组相关表和双变量分组相关表。下面以表 2 中 20家同类工业企业产量与单位成本资料为例说明相关表的编制。表 2 2007 年某地区 20 家同类工业企业产量与单位成本统计表序号 产量（吨） 单位成本（元/吨） 序号 产量（吨） 单位成本（元/吨）1 500 1 000 11 700 9002 800 890 12 800 8803 600 950 13 1 000 7204 700 960 14 600 9005 900 740 15 800 8506 1 000 700 16 600 9207 500 980 17 700 9208 800 900 18 800 8709 700
6、 910 19 800 85010 600 940 20 900 8001 简单相关表的编制新建 Excel 工作簿文件，文件名为 “相关与回归分析”，将表 2 中数据复制粘贴到相关与回归分析工作簿中表 sheet1 中，如图 1 所示。图 1 产量与单位成本原始数据将光标移至 B 列任意数值上，在 “开始”选项卡下，单击“编辑”组的“排序和筛选”按钮，选择升序，即可得到简单相关表，如图 2 所示。通过“数据”选项卡中“排序和筛选”组的相关按钮，同样可以实现数据的排序。根据简单相关表即可对两变量间相关方向加以判断。图 2 产量与单位成本简单相关表2 单变量分组相关表的编制对图 1 中 shee
7、t1 数据，利用数据透视表，对自变量进行分组，编制单变量分组相关表。具体操作步骤如下：（1） 将光标移至数据区域任意单元格中（这样做是为了让 Excel 自动选中连续的数据区域），在“插入”选项卡下“表”组中，单击“数据透视表”，此时会弹出创建数据透视表对话框，点击现有工作表，再点击数据区域外的任意单元格，如 F2，即可得到如图 3所示内容。图 3 创建数据透视表（2） 点确定后，出现数据透视表字段列表对话框，将产量放入行标签区域，再将产量和单位成本均放入数值区域，然后对数值区域中“求和项产量”进行操作：单击下拉按钮值字段设置计算类型改为计数；同样将“求和项单位成本”计算类型改为平均值；最后即
8、可得到单变量分组相关表，操作结果如图 4 所示。图 4 数据透视表字段列表及结果3 双变量分组相关表的编制将自变量和因变量均分组，借助于数据透视表即可编制双变量分组相关表。具体步骤如下：（1） 采用 IF 函数对因变量变位成本进行组距式分组。在相关分析工作簿表 sheet1 中，D1 单元格输入“按单位成本分组”字段名，D2 单元格中输入公式：=IF(C2800,“800 以下“,IF(C2900,“800900“,IF(C21000,“900-1000“,“1000 以上“)，即可实现将单位成本按数值大小，分成“800 以下，800-900 ，900-1000 ，1000 以上”四组。再利用
9、智能填充按钮将公式从 D2 复制到 D21，即可标示出 20 个数值所属的组。（2 ）利用数据透视表，编制双变量分组相关表。操作步骤同单变量相关表的编制，下面重点介绍数据透视表字段列表对话框的设置。将产量字段放入列标签，将按单位成本分组字段放入行标签区域和数值区域。如图 5 所示。图 5 双变量分组相关表字段列表设置然后右击数据透视表 F5 至 F8 任一单元格，选排序降序，再进行拖动调整即可得到如图 6 所示双变量分组相关表。图 6 双变量分组相关表（二） 相关图的绘制相关图即散点图，是将自变量和因变量在第一象限描绘出来的点组成的图形。散点图的绘制步骤如下：在相关与回归分析工作簿 sheet
10、1 工作表中，选中 B1：C21 单元格， “插入”选项卡“图表”组“散点图”下拉按钮选第一项（仅带数据标记的散点图）得到原始相关图，选中相关图，通过“设计”和“布局”选项卡，经过对标题栏及坐标轴栏的美化，即可得到如图 7 所示散点图。图 7 相关图（三） 相关系数的计算1函数计算法。运用 Excel 中 CORREL 函数可以很方便计算出所要的结果。具体操作步骤如下：在相关与回归分析工作簿 sheet1 工作表中，E2 单元格输入公式“=CORREL(B2:B21,C2:C21)”，其中 B2:B21 表示自变量数列， C2:C21 表示因变量数列，可分别通过拖动鼠标选中的形式实现。回车后即
11、可得到两变量间的相关系数 。结果如图 8 所r示。图 8 CORREL 函数相关系数2数据分析工具法。（1 ）数据分析工具库的加载。数据分析工具库需要加载后才能使用，具体加载步骤如下：单击左上角 office 按钮 Excel 选项加载项分析工具库转到，即出现“加载宏”对话框，在分析工具库前的方框内打“” ，单击“确定”即完成分析工具库的加载。此时，在“数据”选项卡“分析”组出现了“数据分析”工具。（2 ） 利用数据分析工具计算相关系数。在相关与回归分析工作簿 sheet1 工作表中，单击“数据”选项卡“分析”组“数据分析”工具相关系数确定，出现“相关系数”对话框，如图 9 所示。将输入区域选
12、中 B1:C21，标志位于第一行打“” ，输出区域选中 E2，确定后即可计算得到相关系数计算矩阵，如图 10 所示。图 9 分析工具库计算相关系数图 10 分析工具库计算相关系数结果（四） 一元线性回归分析一元线性回归分析的关键是确定回归方程 中参数 ， 的值。在 Excel 中bxaycb参数 ， 的确定，常用的方法有以下三种：ab1函数法。确定参数 ， 的函数可以有两组，一组是利用 SLOPE 函数和abINTERCEPT 函数分别计算 和 的值；另一组是利用 LINEST 函数采用数组计算形式直接计算出 和 的值。现利用相关与回归分析工作簿 sheet1 工作表中的数据来介绍两组函数ba
13、的用法。具体公式及计算结果如图 11 所示。图 11 函数计算回归参数方法 1 中，在 H2、H3 单元格中分别输入左边的公式，即可得到回归系数 和截距b的值。注意公式中，数值区域范围是先输入因变量的值域，再输入自变量的值域。a方法 2 中，同时选中 H6 和 I6 单元格，输入公式“=LINEST(C2:C21,B2:B21)”，然后同时按 Ctrl+Shift+Enter，进行数组运算，即可在 H6 和 I6 单元格得到回归系数 和截距的值。2数据分析工具法。仍以相关与回归分析工作簿 sheet1 工作表中的数据为例，操作步骤如下：单击“数据”选项卡“分析”组“数据分析” 工具回归确定，出
14、现“回归”对话框，如图 12 所示。正确输入 Y 值和 X 值区域，并标志和置信度前方框内打“” ，输出区域选中 E2，确定后即可计算得到回归分析结果，如图 13 所示。图 12 分析工具库进行回归分析图 13 分析工具库进行回归分析通过分析工具库中回归分析，可以得到相关系数 、 、回归系数 、截距 和估计r2ba标准误差等数值，并且可以进行假设检验和区间估计。3作图法。利用散点图，添加趋势线也可以自动计算出回归方程。下面利用相关与回归分析工作簿 sheet1 工作表中的数据来介绍其操作过程。选中 B1:C21单击“插入”选项卡“图表”组散点图选中点，右击添加趋势线“设置趋势线格式”对话框趋势线选项在“显示公式”和“显示 R 平方值”前方框内打“” ，即可得到回归方程和值。如图 14 所示。2r图 14 作图法求回归方程
