【Excel系列】Excel数据分析:相关与回归分析

相关系数

15.1 相关系数的概念

著名统计学家卡尔·皮尔逊设计了统计指标——相关系数(Correlation coefficient)。相关系数是用以反映变量之间相关关系密切程度的统计指标。相关系数是按积差方法计算,同样以两变量与各自平均值的离差为基础,通过两个离差相乘来反映两变量之间相关程度;着重研究线性的单相关系数。

依据相关现象之间的不同特征,其统计指标的名称有所不同。如将反映两变量间线性相关关系的统计指标称为相关系数(相关系数的平方称为判定系数);将反映两变量间曲线相关关系的统计指标称为非线性相关系数、非线性判定系数;将反映多元线性相关关系的统计指标称为复相关系数、复判定系数等。相关系数的计算公式为:

复相关系数(multiple correlation coefficient):反映一个因变量与一组自变量(两个或两个以上)之间相关程度的指标。它是包含所有变量在内的相关系数。它可利用单相关系数和偏相关系数求得。其计算公式为:

当只有两个变量时,复相关系数就等于单相关系数。Excel中的相关系数工具是单相关系数。

15.2 相关系数工具的使用

CORREL 和 PEARSON 工作表函数均可计算两个测量值变量之间的相关系数,条件是每种变量的测量值都是对 N 个对象进行观测所得到的。(丢失任何对象的任何观测值都会导致在分析中忽略该对象。)相关系数分析工具特别适合于当 N 个对象中的每个对象都有两个以上的测量值变量的情况。它提供一张输出表(相关矩阵),其中显示了应用于每个可能的测量值变量对的 CORREL(或 PEARSON)值。

与协方差一样,相关系数是描述两个测量值变量之间的离散程度的指标。与协方差的不同之处在于,相关系数是成比例的,因此它的值与这两个测量值变量的表示单位无关。(例如,如果两个测量值变量为重量和高度,当重量单位从磅换算成千克时,相关系数的值并不改变。)任何相关系数的值都必须介于 -1 和 +1 之间(包括 -1 和 +1)。

可以使用相关系数分析工具来检验每对测量值变量,以便确定两个测量值变量是否趋向于同时变动,即,一个变量的较大值是否趋向于与另一个变量的较大值相关联(正相关);或者一个变量的较小值是否趋向于与另一个变量的较大值相关联(负相关);或者两个变量的值趋向于互不关联(相关系数近似于零)。

【例】9个小麦品种(分别用A1,A2,...,A9表示)的6个性状资料见表,作相关系数计算。

表 15-1 试验数据

(1)建立一张工作表,输入数据:

图 15-1 EXCEL数据输入

(2)从“数据”选项卡选择“数据分析”,从“数据分析”列表框中选择“相关系数”,单击“确定”弹出相关系数对话框。

图 15-2 相关系数对话框

数据区域可包括变量名称,但不包括样本编号。每个变量的样本按行排列的选择“逐行”按列排列的选择“逐列”。若包括变量名称,则选择“标志位于第一列”

(3)单击“确定”得偏相关系数如下表。

图 15-3 结果输出

Excel分析工具中的“相关系数”仅计算出相关系数的值,并未进行相关性检验。相关系数检验可由相关系数临界值来判断。

相关系数为可决系数的平方根,可决系数为回归平方和与总误差平方和之比,而F统计量为回归均方和与总均方和之比,由于可借助F临界值求得相关系数临界值。即:

本例中n=9,在G9单元格输入=SQRT(FINV(0.05,1,7)/(FINV(0.05,1,7)+7)),在B12输入=IF(ABS(B4)>$G$9,"相关性显著","不显著")并复制,得:

图 15-4 由函数判断显著性

协方差

16.1 协方差的概念

在概率论和统计学中,协方差用于衡量两个变量的总体误差。而方差是协方差的一种特殊情况,即当两个变量是相同的情况。两个实数随机变量X与Y之间的协方差定义为:  COV(X,Y)=E[(X-E(X))(Y-E(Y))]其中,E是期望值。

16.2 协方差工具的使用

当您对一组个体进行观测而获得了 N 个不同的测量值变量时,“相关”和“协方差”工具可在相同设置下使用。“相关”和“协方差”工具都会提供一张输出表(矩阵),其中分别显示每对测量值变量之间的相关系数或协方差。不同之处在于协方差的取值在 -1 和 +1 之间,而协方差没有限定的取值范围。相关系数和协方差都是描述两个变量离散程度的指标。

“协方差”工具为每对测量值变量计算工作表函数 COVAR 的值。(当只有两个测量值变量,即 N=2 时,可直接使用 COVAR,而不要使用“协方差”工具。)在“协方差”工具的输出表中的第 i 行、第 i 列的对角线上的输入值是第 i 个测量值变量与其自身的协方差;这正好是用工作表函数 VARP 计算得出的变量的总体方差。

可以使用“协方差”工具来检验每对测量值变量,以便确定两个测量值变量是否趋向于同时变动,即,一个变量的较大值是否趋向于与另一个变量的较大值相关联(正相关);或者一个变量的较小值是否趋向于与另一个变量的较大值相关联(负相关);或者两个变量中的值趋向于互不关联(协方差近似于零)。

【例】9个小麦品种(分别用A1,A2,...,A9表示)的5个性状资料见表,作协方差计算。

(1)建立一张工作表,输入数据:

图 16-1 输入数据

(2)从“数据”选项卡选择“数据分析”,从“数据分析”列表框中选择“协方差”,单击“确定”弹出协方差对话框。

图 16-2 协方差工具对话框

数据区域可包括变量名称,但不包括样本编号。每个变量的样本按行排列的选择“逐行”按列排列的选择“逐列”。若包括变量名称,则选择“标志位于第一列”

(3)单击“确定”得协方差如下表。

图 16-3 结果输出

回归

17.1 回归基本公式

对于多元回归,仍可用最小二乘法。

决定系数与调整的决定系数:

17.2 回归工具的使用

“回归”分析工具通过对一组观察值使用“最小二乘法”直线拟合来执行线性回归分析。本工具可用来分析单个因变量是如何受一个或几个自变量的值影响的。

例:某企业成本与销售额如下表(单元:万元),试进行多元线性回归。

表 17-1 数据资料

(1)打开一张EXCEL表格,输入数据如下。

图 17-1 在EXCEL输入数据

(2)数据|分析|数据分析|回归,弹出回归对话框并设置如下:

图 17-2 回归对话框设置

(3)单击“确定”得如下输出结果。

摘要表:

表中Multiple R为复相关系数;R Square为决定系数;Adjusted R Square调整的决定系数。

表 17-2 输出结果(摘要) SUMMARY OUTPUT

表 17-3 输入结果(方差分析表)

参数值、参数检验与置信区间。

表 17-4 输出结果(回归系数、t检验及置信区间

表 17-5 输出结果(残差)RESIDUAL OUTPUT

表中的标准残差=(残差-残差的均值)/残差的标准差即

图 17-3 结果输出残差图

表 17-6 输入结果(百分比排位)PROBABILITY OUTPUT

图 17-4 正态分布图

原文发布于微信公众号 - 大数据杂谈(BigData07)

原文发表时间:2017-07-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏AI研习社

教你几招搞定 LSTMs 的独门绝技(附代码)

如果你用过 PyTorch 进行深度学习研究和实验的话,你可能经历过欣喜愉悦、能量爆棚的体验,甚至有点像是走在阳光下,感觉生活竟然如此美好 。但是直到你试着用 ...

69710
来自专栏闪电gogogo的专栏

压缩感知重构算法之子空间追踪(SP)

SP的提出时间比CoSaMP提出时间稍晚一些,但和压缩采样匹配追踪(CoSaMP)的方法几乎是一样的。SP与CoSaMP主要区别在于“In each itera...

22070
来自专栏磐创AI技术团队的专栏

使用Keras进行深度学习:(三)使用text-CNN处理自然语言(上)

上一篇文章中一直围绕着CNN处理图像数据进行讲解,而CNN除了处理图像数据之外,还适用于文本分类。CNN模型首次使用在文本分类,是Yoon Kim发表的“Con...

73980
来自专栏机器之心

教程 | 从头开始了解PyTorch的简单实现

选自GitHub 机器之心编译 参与:路 本教程展示了如何从了解张量开始到使用 PyTorch 训练简单的神经网络,是非常基础的 PyTorch 入门资源。Py...

60650
来自专栏量子位

北大新成果!首次成功地将CNN解码器用于代码生成 | 论文+代码

最近,有一篇论文提出了一种基于语法的结构化CNN代码生成器,用《炉石传说》(HearthStone)基准数据集进行实验的结果表明:

14830
来自专栏集智的专栏

使用腾讯云 GPU 学习深度学习系列之五:文字的识别与定位

本系列文章主要介绍如何使用腾讯云GPU服务器进行深度学习运算,前面主要介绍原理部分,后期则以实践为主。

13.3K80
来自专栏人工智能

利用神经网络算法的C#手写数字识别

尽管在过去几年已经有许多系统和分类算法被提出,但是手写识别任然是模式识别中的一项挑战。

1.4K110
来自专栏大数据挖掘DT机器学习

机器学习算法-决策树C4.5练习

决策树是一个预测模型;他代表的是对象属性与对象值之间的一种映射关系。树中每个节点表示某个对象,而每个分叉路径则代表的某个可能的属性值,而每个叶结点则对应...

40160
来自专栏CreateAMind

神经网络里的信息存储在哪里?如何更好的存储和提取?

神经元的活性和神经元之间的权重都存储了重要信息,有没有更好的存储方式呢?如何向生物记忆学习呢?

9720
来自专栏机器之心

谷歌开放GNMT教程:如何使用TensorFlow构建自己的神经机器翻译系统

选自谷歌 机器之心编译 参与:机器之心编辑部 近日,谷歌官方在 Github 开放了一份神经机器翻译教程,该教程从基本概念实现开始,首先搭建了一个简单的NMT模...

60460

扫码关注云+社区

领取腾讯云代金券