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家同类工业企业产量与单位成本资料为例说明相关表的编制表 2 2007年某地区20家同类工业企业产量与单位成本统计表序号产量（吨）单位成本（元/吨）序号产量（吨）单位成本（元/吨）15001 000117009002800890128008803600950131 00072047009601460090059007401580085061 0007001660092075009801770092088009001880087097009101980085010600940209008001． 简单相关表的编制新建Excel工作簿文件，文件名为“相关与回归分析”，将表2中数据复制粘贴到相关与回归分析工作簿中表sheet1中，如图1所示图 1 产量与单位成本原始数据将光标移至B列任意数值上，在“开始”选项卡下，单击“编辑”组的“排序和筛选”按钮，选择升序，即可得到简单相关表，如图2所示通过“数据”选项卡中“排序和筛选”组的相关按钮，同样可以实现数据的排序。
根据简单相关表即可对两变量间相关方向加以判断图 2 产量与单位成本简单相关表2． 单变量分组相关表的编制对图1中sheet1数据，利用数据透视表，对自变量进行分组，编制单变量分组相关表具体操作步骤如下：（1） 将光标移至数据区域任意单元格中（这样做是为了让Excel自动选中连续的数据区域），在“插入”选项卡下“表”组中，单击“数据透视表”，此时会弹出创建数据透视表对话框，点击现有工作表，再点击数据区域外的任意单元格，如F2，即可得到如图3所示内容图 3 创建数据透视表（2） 点确定后，出现数据透视表字段列表对话框，将产量放入行标签区域，再将产量和单位成本均放入数值区域，然后对数值区域中“求和项产量”进行操作：单击下拉按钮→值字段设置→计算类型改为计数；同样将“求和项单位成本”计算类型改为平均值；最后即可得到单变量分组相关表，操作结果如图4所示图 4 数据透视表字段列表及结果3． 双变量分组相关表的编制将自变量和因变量均分组，借助于数据透视表即可编制双变量分组相关表具体步骤如下：（1） 采用IF函数对因变量变位成本进行组距式分组在相关分析工作簿表sheet1中，D1单元格输入“按单位成本分组”字段名，D2单元格中输入公式：=IF(C2<800,"800以下",IF(C2<900,"800~900",IF(C2<1000,"900-1000","1000以上")))，即可实现将单位成本按数值大小，分成“800以下，800-900，900-1000，1000以上”四组。
再利用智能填充按钮将公式从D2复制到D21，即可标示出20个数值所属的组2）利用数据透视表，编制双变量分组相关表操作步骤同单变量相关表的编制，下面重点介绍数据透视表字段列表对话框的设置将产量字段放入列标签，将按单位成本分组字段放入行标签区域和数值区域如图5所示图 5 双变量分组相关表字段列表设置然后右击数据透视表F5至F8任一单元格，选排序→降序，再进行拖动调整即可得到如图6所示双变量分组相关表图 6 双变量分组相关表（二） 相关图的绘制相关图即散点图，是将自变量和因变量在第一象限描绘出来的点组成的图形散点图的绘制步骤如下：在相关与回归分析工作簿sheet1工作表中，选中B1：C21单元格，“插入”选项卡→“图表”组→“散点图”下拉按钮→选第一项（仅带数据标记的散点图）得到原始相关图，选中相关图，通过“设计”和“布局”选项卡，经过对标题栏及坐标轴栏的美化，即可得到如图7所示散点图图 7 相关图（三） 相关系数的计算1．函数计算法运用Excel中CORREL函数可以很方便计算出所要的结果具体操作步骤如下：在相关与回归分析工作簿sheet1工作表中，E2单元格输入公式“=CORREL(B2:B21,C2:C21)”，其中B2:B21表示自变量数列，C2:C21表示因变量数列，可分别通过拖动鼠标选中的形式实现。
回车后即可得到两变量间的相关系数结果如图 8所示图 8 CORREL函数相关系数2．数据分析工具法1）数据分析工具库的加载数据分析工具库需要加载后才能使用，具体加载步骤如下：单击左上角office按钮→Excel选项→加载项→分析工具库→转到，即出现“加载宏”对话框，在分析工具库前的方框内打“√”，单击“确定”即完成分析工具库的加载此时，在“数据”选项卡→“分析”组出现了“数据分析”工具2） 利用数据分析工具计算相关系数在相关与回归分析工作簿sheet1工作表中，单击“数据”选项卡→“分析”组→“数据分析”工具→相关系数→确定，出现“相关系数”对话框，如图9所示将输入区域选中B1:C21，标志位于第一行打“√”，输出区域选中E2，确定后即可计算得到相关系数计算矩阵，如图10所示图 9 分析工具库计算相关系数图 10 分析工具库计算相关系数结果（四） 一元线性回归分析一元线性回归分析的关键是确定回归方程中参数，的值在Excel中参数，的确定，常用的方法有以下三种：1．函数法确定参数，的函数可以有两组，一组是利用SLOPE函数和INTERCEPT函数分别计算和的值；另一组是利用LINEST函数采用数组计算形式直接计算出和的值。
现利用相关与回归分析工作簿sheet1工作表中的数据来介绍两组函数的用法具体公式及计算结果如图11所示图 11 函数计算回归参数方法1中，在H2、H3单元格中分别输入左边的公式，即可得到回归系数和截距的值注意公式中，数值区域范围是先输入因变量的值域，再输入自变量的值域方法2中，同时选中H6和I6单元格，输入公式“=LINEST(C2:C21,B2:B21)”，然后同时按Ctrl+Shift+Enter，进行数组运算，即可在H6和I6单元格得到回归系数和截距的值2．数据分析工具法仍以相关与回归分析工作簿sheet1工作表中的数据为例，操作步骤如下：单击“数据”选项卡→“分析”组→“数据分析”工具→回归→确定，出现“回归”对话框，如图12所示正确输入Y值和X值区域，并标志和置信度前方框内打“√”，输出区域选中E2，确定后即可计算得到回归分析结果，如图13所示图 12 分析工具库进行回归分析图 13 分析工具库进行回归分析通过分析工具库中回归分析，可以得到相关系数、、回归系数、截距和估计标准误差等数值，并且可以进行假设检验和区间估计3．作图法利用散点图，添加趋势线也可以自动计算出回归方程下面利用相关与回归分析工作簿sheet1工作表中的数据来介绍其操作过程。
选中B1:C21→单击“插入”选项卡→“图表”组→散点图→选中点，右击→添加趋势线→“设置趋势线格式”对话框→趋势线选项→在“显示公式”和“显示R平方值”前方框内打“√”，即可得到回归方程和值如图14所示图 14 作图法求回归方程。