在excel中相关系的求法

1、实验三EXCEL在相关与回归分析中的应用一、相关图的绘制如图1所示。图1表7 - 1的EXCEL工作表制作相关图的步骤如下：选择区域A1:B11，如图1所示;点击EXCEL图表向导；Mi crosoft Excel文件（I）编辑（D视E 插入（X）格式）工具（I）数JE（E）窗口 （!）帮助皿D罗耐|=标准类型|自定义类型|图表类型：M条形圈室折线圈3饼图g面粗图噂圆环图.酒，雷达图由曲面图:气泡圈图表向导-4步骤之1 -图表类型子图表类型心:&910n.12131415散点图比较成对的数值在“图表类型”中选择“XY按下不放可查看示例1 ）完成|如图2。图2散点图的制作在“子图表类型”中选择第

2、一种散点图，并点击“下一步”即图2散点图的制作图3散点图的制作点击“完成”，并对图形进行修饰编辑，最后得到如图4所示广告投入与月平均销售额之间的散点图。销售额图4广告投入与月平均销售额的散点图二、相关系数在EXCEL中，相关系数函数和相关系数宏提供了两种计算相关 系数的方法。（一）相关系数函数在EXCEL中，CORREL函数和PERSON函数提供了计算两个 变量之间的相关系数的方法，这两个函数是等价的。与相关系数有关 的函数还有RSQ （相关系数的平方，即判定系数瘁）和COVAR （协 方差函数）。在这里我们以CORREL函数和表7-1中资料为例，介绍利用函 数计算相关系数的方法。首先，点击E

3、XCEL函数图钮“f”，选择“统计”函数；x在统计函数点击“CORREL”，进入函数向导；在“arrayl”中输入第一个变量“广告投入”的数据区域A2:A11 “array2”中输入第二个变量“月均销售额”的数据区域B2:B11样，即可在当前光标所在单元格显示函数的计算结果。如图5所示。图5 CORREL函数计算相关系数（二）相关系数宏在EXCEL数据分析宏中，EXCEL专门提供了计算相关系数宏过 程。利用此宏过程，可以计算多个变量之间的相关矩阵。仍然以表7-1中资料为例，利用相关系数宏计算相关系数矩阵的 过程如下：点击EXCEL 工具”菜单，选择“数据分析”过程；在“数据分析”宏过程中，选择

4、“相关系数”过程。如图6所 示；在“输入区域”中输入两个变量所在区域A2:B11，数据以列 排列，输出区域选择在同一工作表中的以D1:E5区域里。计算结果如 图7所示。图6相关系数宏图7利用相关系数宏计算的相关系数矩阵三、回归分析宏除了回归分析宏外，EXCEL虽然提供了 9个函数用于建立回归模 型和回归预测。这9个函数列于表7-9中。但EXCEL提供的回归 分析宏仍然具有更方便的特点。仍以表7-1中广告投入与销售额的 关系资料为例，利用一元线性回归方程确定两个变量之间的定量关 系。表7-9用于回归分析的工作表函数函数名INTERCEP TSLOPERSQFORECASTSTEYXTREND G

5、ROWTH LINEST定 义一元线性回归模型截距的估计值一元线性回归模型斜率定系计值（r2）依照一元线性回归模型的预测值 的标准误差序依照多元线性回归模型的预测值 依照多元指数回归模型的预测值 A、一 皿回归模型的未知参的标准误差估计多元线性依照一元线性回归模型的预测值数LOGEST估计多元指数回归模型的未知参数回归宏确定两个变量之间定量关系的过程如下:图8回归分析宏过程在“工具栏”菜单“数据分析”过程中选择“回归”宏过程；在“Y值输入区域”内输入B2:B11,在“X值输入区域”输入A2： A11，如果是多元线性回归，则X值的输入区就是除Y变量以 外的全部解释变量；选择“标志”；置信度水平为

6、95%，输出结果选择在一张新的 工作表中；选择“残差分析”，并绘制回归拟合图，点击“确定”即得到图 9所示的回归分析结果和图10的残差表。鬟Microsoft Excel-六章四文件但）编辑但）视图）插入0）格式 工具 图表但）窗口也）帮助冬） q a a as 霆我乳岫区,宋体-12- B数值m .=ABCD1_ E1_ FGH1_ ISUOARE OUTPUTI回归统计Multiple 0.994198R SquZr已 。 98扇o O5销售额Adjusted 0.926924 标准误差L 630011 观测值W10方差分析广告投入11df SS MS析计差计回残总1 1815.93 18

7、15.93 683.468 4. 92E-098 21.25547 2.656934，18：对.18515H171 QCM.ffi%rr标准误差Intercept 11.61492 1.280176V Ho”； pRI fl 口口/1 口口U fl fTQ.-1Qt Stat PfMue Lower 95线11如盐 95WF限 95. 0：上限 95. 0% 9.072908 1. 75E-05 8.662824 14. 56701 8.662824 i4.56701 9P. 1Fl aQR-na ri 9f-|P.Q.-19 fl OP.90.10 fl 口1K口/1 叮 fl Of；90.-IQ图9回归分析结果RESIDUAL 0UTFIJT8910. （卜* 2040 6&预测22. 6761125.153 殴 3Z L4449 34. 9761625892D553246. 479851. 6121960. 6381365.项加无残差-1. 47611T.2538N0. 755507 -0. 876162410831.144677SW0199 1.187805 -1