返回目录：excel表格制作
- EXCEL在相关与回归分析中的应用
将本章表7–1中的资料建立EXCEL工作表，如图1所示。
图1 表7–1的EXCEL工作表
制作相关图的步骤如下：
选择区域A1:B11，如图1所示；
◆点击EXCEL图表向导；
◆在“图表类型”中选择“XY散点图”；如图2。
图2 散点图的制作
◆在“子图表类型”中选择第一种散点图，并点击“下一步”，即可得到图1和2。
图2 散点图的制作
图3 散点图的制作
◆点击“完成”，并对图形进行修饰编辑，最后得到如图4所示广告投入与月平均销售额之间的散点图。
图4 广告投入与月平均销售额的散点图
二、相关系数
在EXCEL中，相关系数函数和相关系数宏提供了两种计算相关系数的方法。
（一）相关系数函数
在EXCEL中，CORREL函数和PERSON函数提供了计算两个变量之间的相关系数的方法，这两个函数是等价的。与相关系数有关的函数还有RSQ（相关系数的平方，即判定系数r2）和COVAR（协方差函数）。
在这里我们以CORREL函数和表7–1中资料为例，介绍利用函数计算相关系数的方法。
◆首先，点击EXCEL函数图钮“fx”，选择“统计”函数；
◆在统计函数点击“CORREL”，进入函数向导；
在“array1”中输入第一个变量“广告投入”的数据区域A2:A11“array2”中输入第二个变量“月均销售额”的数据区域B2:B11样，即可在当前光标所在单元格显示函数的计算结果。如图5 所示。
图5 CORREL函数计算相关系数
（二）相关系数宏
在EXCEL数据分析宏中，EXCEL专门提供了计算相关系数宏过程。利用此宏过程，可以计算多个变量之间的相关矩阵。
仍然以表7–1中资料为例，利用相关系数宏计算相关系数矩阵的过程如下：
◆点击EXCEL“工具”菜单，选择“数据分析”过程；
◆在“数据分析”宏过程中，选择“相关系数”过程。如图6所示；
◆在“输入区域”中输入两个变量所在区域A2:B11，数据以列排列，输出区域选择在同一工作表中的以D1:E5区域里。计算结果如图7所示。
图6 相关系数宏
图7 利用相关系数宏计算的相关系数矩阵
三、回归分析宏
除了回归分析宏外，EXCEL虽然提供了9个函数用于建立回归模型和回归预测。这9个函数列于表7–9中。但EXCEL提供的回归分析宏仍然具有更方便的特点。仍以表7–1中广告投入与销售额的关系资料为例，利用一元线性回归方程确定两个变量之间的定量关系。
表7–9 用于回归分析的工作表函数
| 函数名 | 定 义 |
| INTERCEPT | 一元线性回归模型截距的估计值 |
| SLOPE | 一元线性回归模型斜率的估计值 |
| RSQ | 一元线性回归模型的判定系数（r2） |
| FORECAST | 依照一元线性回归模型的预测值 |
| STEYX | 依照一元线性回归模型的预测值的标准误差 |
| TREND | 依照多元线性回归模型的预测值 |
| GROWTH | 依照多元指数回归模型的预测值 |
| LINEST | 估计多元线性回归模型的未知参数 |
| LOGEST | 估计多元指数回归模型的未知参数 |
回归宏确定两个变量之间定量关系的过程如下：
图8 回归分析宏过程
◆在“工具栏”菜单“数据分析”过程中选择“回归”宏过程；
◆在“Y值输入区域”内输入B2:B11，在“X值输入区域”输入A2：A11，如果是多元线性回归，则X值的输入区就是除Y变量以外的全部解释变量；
◆选择“标志”；置信度水平为95％，输出结果选择在一张新的工作表中；
◆选择“残差分析”，并绘制回归拟合图，点击“确定”即得到图9所示的回归分析结果和图10的残差表。
图9 回归分析结果
图10 残差分析表