EXCEL与数据分析.ppt

授课教师：马银戌,第四章 数据间的相关性分析,第一节 数据间的相关性,第二节 相关图表与相关关系 的初步判断,本章主要教学内容,第三节 相关系数的计算和分析,第四节 线性回归分析,第一节 数据间的相关性,一、函数关系和相关关系 二、线性相关和非线性相关 三、正线性相关和负线性相关,一、函数关系和相关关系,任何事物都不是孤立存在的，总会与其他事物有或多或少的联系，其运行轨迹会受到其他事物的影响，同时也影响着其他事物的发展。因此，仅从事物本身出发研究其特征和规律是不全面的，还应对事物之间的相关性进行分析。在自然界和社会现象中，客观现象之间的数量关系其通常有两种类型，即：,现象之间的数量关系,函数关系,相关关系,函数关系是现象（变量）之间客观存在的确定性的数量对应关系。,如：圆面积与半径的关系。,相关关系即现象（变量）之间客观存在的非确定性的数量依存关系。,即当一个现象取一定值时，与其相对应的另一个现象的值不完全确定，而是有多个值与其对应。,例如：消费支出与消费收入的关系、学习成绩与学习时间的关系、亩产量和浇水量之间的关系、企业生产规模和综合单位成本之间的关系等。,二、线性相关和非线性相关,事物间的相关性又可以从相关的表现形式划分为线性相关和非线性相关。,线性相关：,相关的两个事物的对应值画在直角坐标图上，其数据散布点趋向直线形式，则称为线性相关。如商品的销售量与销售额之间就是一种线性相关关系。,非线性相关：,如果其散布点分布趋向某种曲线形式，则称为非线性（曲线）相关。,例如，随着家庭年收入的增长，购买轿车的可能性也会增加。但当年收入处在较低水平时，年收入增加，轿车的购买量增长幅度不会很大；当年收入达到一定水平时，购买量的增长幅度会较大；而当年收入达到一个较高水平时，购买量的增长幅度将会再次保持平稳。这就是一个典型的非线性相关关系。,线性相关关系是有方向性的，按照相关的方向不同可以分为正线性相关和负线性相关。,负相关：,如果自变量x的数值增加(或减少)，因变量y的数值则随之减少(或增加)，即自变量与因变量的变动方向是相反的，这种相关关系称为负相关。如商品的价格和销售量之间的关系。,三、正线性相关和负线性相关,正相关：,当自变量x数值增加(或减少)时,因变量y的数值也将随之相应的增加(或减少)，这种相关关系称为正相关。如学习时间和学习成绩之间的关系。,第二节 相关表、相关图与相关关系的初步判断,一、相关表的制作 二、相关图的制作 三、相关关系的初步判断,（一)定性分析,（二)定量分析,相关表,相关图,相关系数,分析和判断两数值型数据间的相关性，可采用理论分析（定性分析）方法和定量分析方法两大类。定量分析方法又包括图表分析方法和数值分析方法。数据间相关性的初步判断可通过定性分析及定量分析中的图表分析方法。,一、相关表的制作,相关表是用来观察两个现象之间是否具有相关关系的数据表格，它是将数据表格中的其中一个现象进行排序（降序或升序均可），然后观察另一个数据随着前一个数据的增加或减少是否呈现出有规律的变化，从而初步判断两个现象之间是否具有相关关系。相关表的制作很简单，将两个现象的数据分别两列录入Excel，然后将其中一列排序即可。,表4-1 学习时间与学习成绩相关表,二、相关图的制作,相关图又称散点图，它是将相关表中的数值在平面直角坐标系中用坐标点描绘出来，以表明相关数据点的分布状况。通过相关图可观察两个现象之间关系的总体模式，初步判断它们之间是否具有相关关系以及相关关系的强弱程度和相关的方向。,相关图的制作步骤：（1）将搜集的两个现象的数据分两列录入Excel表；（2）选中制作图表的数据区域；,（3）点击“插入”点击“图表”，出现“图表向导”的复选框；或直接在Excel表上方的工具栏中点击图表向导：,（4）在“图表类型”中选择“XY散点图”，并选择“子图表类型”中的第一类；（5）单击“下一步”“下一步”，填写图表标题及X轴和Y轴的标题；（6）单击“下一步”单击“完成”。案例：分析我国固定资产投资与GDP的相关性。,相关图描述了两个现象之间的大致关系，比较典型的形态有以下几种：（1）所有点均落在左下右上的一条直线上，表明数据间为完全正相关；（2）所有点均落在左上右下的一条直线上，表明数据间为完全负相关；上述两种情况表明两个事物之间是函数关系，而不是相关关系。（3）数据点杂乱无章，从形态上看不出任何特征和规律，表明现象之间不相关。,（1）数据点大致落在左下右上的一条直线周围，表明数据间存在正线性相关关系；（2）数据点大致落在左上右下的一条直线周围，表明数据间存在负线性相关关系；（3）数据点大致呈现某种曲线形态，如抛物线等，表明现象之间不存在线性相关关系，但存在非线性相关关系。几种主要的相关图形如下：,完全正相关,不相关,几种主要的相关图,完全负相关,直线相关,曲线相关,学习时间,学习成绩,商品价格,购买量,正相关,负相关,几种主要的相关图,第三节 相关系数的计算和分析,一、相关系数的含义二、相关系数的计算三、案例分析,一、相关系数的含义,（一）相关系数的概念 相关表和相关图虽然能够直观展现数据之间的相关关系，但只是大概，不精确。（简单）相关系数是线性相关条件下，用来说明两个变量之间相关关系密切程度和相关方向的统计指标。它通过数字的方式准确描述了数据间线性相关的方向和强弱程度。,相关系数是一个用来度量数值型数据间的线性相关关系的系数，不受变量值计量单位的影响，一般记为 r。其数学定义为：,（二)相关系数r 的取值范围,1r+1,（三)相关系数r 的取值含义,或,0r1,二、相关系数的计算,在EXCEL中，相关系数的计算可通过“数据分析”命令计算。基本操作步骤如下：,（1）选择“工具”菜单中的“数据分析”命令，出现如下对话框：,（2）选择“相关系数”，单击“确定”，出现“相关系数”的复选框：,（3）在“输入区域”框中输入待分析数据所在的单元格地址，并在“输出选项”中进行选择，单击“确定”，即可出现相关系数的计算结果。,三、案例分析,根据我国2010年各地区GDP和固定资产投资、居民消费水平的数据资料，分析固定资产投资、居民消费水平与各地区GDP之间的相关性。,利用EXCEL计算的相关系数结果为：,计算结果表明，各地区固定资产投资与地区生产总值的相关系数为0.8881，说明固定资产投资与地区生产总值之间为高度正相关；各地区居民消费水平与地区生产总值的相关系数为0.4058，说明居民消费水平与地区生产总值之间为低度正相关。上述数据分析结果显示，我国的经济增长方式主要为投资拉动型，固定资产投资是拉动经济增长的主要因素，而居民消费对经济增长的拉动不力。从长远的意义来看，这种状态不利于国民经济的可持续发展，因此，政府应通过制定相应的政策转变经济增长方式，约束投资的数量，提高投资的质量；并进一步采取有效政策，引导和鼓励居民消费，提高居民消费水平和消费质量。,第四节 线性回归分析,一、相关分析与回归分析的关系 二、简单线性回归分析 三、回归分析的EXCEL实现,一、相关分析与回归分析的关系,回归分析就是对具有相关关系的多个变量之间的数量变化进行数量测定，配合一定的数学方程（模型），以便由自变量的数值对因变量的可能值进行估计或预测的一种统计方法。,相关分析(相关系数)能够确定变量之间相关方向和相关的密切程度,但它不能指出两个变量之间相互关系的具体形式,也无法从一个变量的数量变化来推测另一个变量的变化情况。（另外，在相关分析中，一般不必区分自变量和因变量，它不能说明两个变量是主从关系还是因果关系。）,（一）回归分析的概念,（二）相关分析和回归分析的关系,相关分析和回归分析既有联系，也有区别。联系：1、相关分析是回归分析的前提和基础；2、回归分析是相关分析的继续和深入。区别：1、相关分析中两变量都是随机的，不必确定自变量与因变量；而回归分析中只有因变量是随机的,必须事先确定自变量和因变量。2、相关分析中只能算出一个相关系数，而回归分析中可有两个回归方程。3、相关分析的内容是判断事物之间是否具有相关关系及相关的方向和密切程度；而回归分析则是要分析当自变量发生变化时，因变量平均发生怎样的变动。,二、简单线性回归分析,回归分析的种类,按照回归线的形状,按自变量的个数,多元回归,一元回归,非线性（曲线）回归,线性（直线）回归,根据回归分析的方法，得出的数学表达式称为回归方程（回归模型），它有多种形式，可以是直线方程，也可以是曲线方程。,用回归方程来表明两个变量之间线性相互关系的方程式，称为简单线性回归方程（模型）。这种分析方法称为简单线性回归分析。,实际中，多数现象的调查采用抽样调查，因此，通常使用样本直线回归方程作为总体回归方程的估计值。,a：直线起点值，数学上称为直线的截距；,b：数学上称为直线的斜率；统计学上称为回归系数。它表示自变量x每变动一个单位时，因变量y平均变动的数值。,式中：,（一）简单直线回归方程（样本方程）,简单线性回归分析的任务 就是如何根据实际样本资料来 确定参数a、b，即从各个相关点中配合一条合适的直线，使其能代表相关点的变动趋势（即用其表明两变量x与y在线性相关条件下的具体变动关系）。,数学证明，符合“离差平方和最小”的直线是最合适的。这种决定直线方程的方法，称为最小二乘法（最小平方法）。,（二）直线回归方程的求解,x,y,根据“离差平方和最小”的原则，a、b 的求解公式为：,（三）利用回归方程进行预测,回归方程描述了当自变量发生变化时，因变量的平均变化情况。因此，可用回归方程预测当自变量为某个特定值时的因变量的取值。即，建立了回归方程并通过了统计检验之后，回归方程的一个重要应用就是根据给定的自变量x的值，对因变量y 作出相应的预测估计。,回归方程的预测包括定值预测和区间预测。利用回归方程，对自变量x的一个特定值 x0，求出因变量y 的一个估计值y0，这就是点估计，也称定值预测。利用回归方程，对自变量x的一个特定值 x0，以一定的把握程度，计算出因变量y 的一个估计值y0 的置信区间，这就是区间估计，也称区间预测。当把握程度为95%时（实际中常用），y0 的置信区间（简单算法）为：,其中，Sy 称为估计标准误差，是衡量因变量y的实际值和估计值离差一般水平的分析指标，sy的大小与回归方程的代表性成反比。,三、回归分析的EXCEL实现,（1）选择“工具”菜单中的“数据分析”子菜单，在“分析工具”中选择“回归”；,（2）单击“确定”，出现如下对话框：,利用EXCEL进行回归分析，是通过“数据分析”命令实现的。具体操作如下：,（3）给出因变量Y和自变量X的数据所在的单元格区域，在“输出选项”中选择输出的位置，同时选择“线性拟合图”；,（4）单击“确定”，输出回归结果。,对输出结果的解释：（结合固定资产投资与地区GDP的关系案例）,输出结果包括“SUMMARY OUTPUT”（摘要输出）和“RESIDUAL OUTPUT”（残差输出）两部分以及线性拟合图。,（1）“Multiple R”是自变量X和因变量Y之间的简单相关系数 r，等于0.8881，表明两者间高度正相关；（2）“R Square”是判定系数R2,等于0.7887。判定系数R2是测定直线回归模型拟合优度的一个重要指标，其意义同相关系数 r具有一致性。计算结果表明，GDP的总误差中有78.87%可以由固定资产投资与GDP的依存关系来解释，只有21.13%属于随机因素的影响，因此这条回归线是比较合适的；（3）“Adjusted R Square”是调整的R2,在简单线性回归分析中没有特别意义；（4）“标准误差”是回归方程的估计标准差Sy，此例中的估计标准差为5330，很大，表明回归方程的拟合度不高；,（5）“方差分析”中的“SS”下面的三个数分别是回归平方和SSR、剩余平方和SSE和总离差平方和SST；其中SSR反映了自变量的变化所引起的因变量y值的波动，其大小反映了自变量x的重要程度；SSE是 y 的实际值与估计值之差的平方和，它主要反映随机因素对因变量的影响程度。在总离差平方和中，SSR占的比例越大，说明自变量对因变量的影响越大，即x与y线性相关的程度越高，回归方程的质量越高；反之，SSE所占的比例越大，说明随机因素对因变量的影响越大，而自变量与因变量的关系越不密切，回归方程的质量越低。F 统计量就是反映SSR与SSE比例关系、并用于对回归 方程进行显著性检验的统计量。,（6）F下面的数是回归方程显著性检验中的F统计量的值，等于108.27；其右侧单元格中的数是F统计量在原假设(X与Y线性相关不显著)成立时发生的概率，为2.6610-11，当把握程度为95%时，由于2.6610-5(1-0.95)，所以拒绝原假设，认为两个变量之间的线性相关是显著的，线性回归模型检验通过；（7）“t Stat”下面的数是回归方程显著性检验中的 t 统计量的值，等于10.41；其右侧单元格中的数是t 统计量在原假设(回归系数为0)成立时发生的概率，为2.6610-11，当把握程度为95%时，由于2.6610-11(1-0.95)，所以拒绝原假设，认为回归系数显著不为0，自变量对因变量的线性解释有贡献，应保留在 回归方程中；,（8）“Coefficients”下面的两个数是回归方程的两个参数值，其中，截距 a=-856.07，回归系数 b=1.71；据此，可写出样本回归方程：,表明固定资产投资每增加1亿元，GDP平均增加1.71亿元。（9）“标准误差”下面的两个数是参数a和b的标准差；（10）“下限 95%和上限95%”下面的数字是在95%的把握下a和b的置信区间；（11）“RESIDUAL OUTPUT”下面的数字是GDP的预测值及预测值和实际值之间的误差(残差）。,*利用回归方程进行预测，在EXCEL中可通过编辑公式进行计算。例，若某省固定资产投资为8000亿元，利用回归方程对该省GDP进行预测：定值预测：,区间预测：在95%的把握程度下，该省GDP预测值的置信区间为：,即：当某省固定资产投资为8000亿元，以95%的把握程度预测该省GDP在2164亿元23484亿元之间。置信区间过大，通过固定资产预测GDP是不合适的。,案例分析：根据对35家家电生产企业进行产量和单位产品成本的调查资料进行相关和回归分析，并预测：当某企业计划将年产量调整为120万台时，单位成本估计值是多少？,利用Excel对数据处理的结果显示：产量和单位成本之间的相关系数为-0.9568，表明两者之间呈现线性高度负相关。即，年产量越大，单位产品成本越低。进一步进行产量和单位产品成本的回归分析，获得回归方程为：,方程显著性检验（F检验、t检验）通过，估计标准差为61.67，较小，说明回归方程质量较高。,当某企业计划将年产量调整为120万台时，单位成本的定值预测：,在95%的把握程度下，该企业单位产品成本预测值的置信区间为：,即：当某企业将年产量调整为120万台时，单位成本的点估计值为889.58元，在95%的把握程度下，该企业单位产品成本预测值的置信区间在766.24元1012.92元之间。,