office数据分析

单击以编辑,母版标题样式,单击以编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,*,数据分析,数据分析:,根据电子表格中的数据以图形或数值的方式得出一些有意义的结论.比如:数据的排序,筛选,运算,统计,图表化等等都可以说是数据分析的手段.,这里我们主要讲以下几个数据分析问题:,一.变量求解:,有些数据之间存在因果关系,这些因果关系可以用数学模型来描述,如方程式,不等式等,如果知道了结果数据,能否根据这些数学模型求出原因变量的值?,EXCEL,提供一些这样的方法,如,单变量求解,规划求解,等.,二,.模拟运算表:,根据数学模型(公式)为一组假设数据产生预测结果,以供分析决策,三.相关性分析:,判断两组数据集（可以使用不同的度量单位）之间的关系。,四.回归分析:,当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，通常可由一个或多个自变量来预测一个变量的值。,1,数据分析方法,1.分析工具库,Excel,提供了一组数据分析工具，称为“分析工具库”，在建立复杂统计或工程分析时可节省步骤。只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或工程函数，在输出表格中显示相应的结果。其中有些工具在生成输出表格时还能同时生成图表。,安装“分析工具库”:,要使用查看可用的分析工具，请单击“工具”菜单中的“数据分析”命令。如果“工具”菜单中没有“数据分析”命令，则需要安装“分析工具库”。,2.工作表函数,Excel,还提供了其他一些统计、财务和工程函数。某些统计函数是内置函数，而其他一些函数只有在安装了“分析工具库”之后才能使用。,预备知识,要,使用这些工具，用户还必须熟悉需要进行分析的统计学或工程学的特定,领域,。,2,三.相关性分析,相关性分析：,判断两组数据集（可以使用不同的度量单位）之间的关系。,相关系数（,R）：,相关性计算的返回值。用来确定两个区域中数据的变化是否相关，以及相关的程度。是两组数据集的协方差除以它们标准偏差的乘积。,1、,R0：,一个集合的较大数据与另一个集合的较大数据相对应（正相关）,2、,R0：,一个集合的较大数据与另一个集合的较小数据相对应（负相关）,3、,R=0：,两个集合中的数据互不相关,。,|,R|0.4 ：,低度,相关；,0,.4,=|,R|0.6:,中度相关；,0,.6,=|,R|=0.8:,非常高度相关,3,1、,CORREL,函数：,CORREL,(,array1,array2,),返回单元格区域,array1,和,array2,之间的相关系数。使用相关系数可以确定两种属性之间的关系。例如，可以检测某地的平均温度和空调使用情况之间的关系。,Array1,第一组数值单元格区域。,Array2,第二组数值单元格区域。,如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略，但是包含零值的单元格将计算在内。如果,array1,和,array2,的数据点的数目不同，函数,CORREL,返回错误值#,N/A。,4,例 1、利用统计数据计算广告费与销售额之间的相关系数。,相关系数,CORREL(B4:B15,C4:C15)=,0.92251818,5,例2.我们收集了某厂家同一车型中旧车的车龄及其售价数据，求价格与车龄的相关系数,6,例 3、总平均成绩、出勤率、选修学分与每周打工小时数的关系。,2、,使用数据分析工具求相关矩阵,利用,CORREL,函数只能返回两种属性之间的相关系数，如果使用,Excel,提供的“数据分析”工具可以计算多组数据间的相关系数，组成相关系数表。,7,例 利用“数据分析”工具计算汽车扳金、省油与价格的满意度之间的相关系数。,8,回归分析,:,当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，通常可由一个或多个自变量来预测一个变量的值。,回归方程,:表达参数与结果之间相互关系的数学方程式(数学模型),线性回归:,如果变量与结果之间具有线性关系,我们可以用线性方程式来描述它们之间的关系,这种回归方法叫线性回归.,非线性回归:,如果变量与结果之间不具有线性关系,我们必须用非线性方程式来描述它们之间的关系(如指数关系,对数关系等等),这种回归方法叫非线性回归.,单回归,:,当一个结果只与一个参数存在联系时，进行的回归分析称为单回归。,复回归,:,当一个结果与多个参数存在联系时，进行的回归分析称为复回归。,判定系数(,R,2,):,用来确定回归方程式的可解释性，即吻合程度。范围在0-1之间，越接近1，解释性越强，即吻合程度越高。,回归方法：,1、给图表增加趋势线；,2、使用,Excel,提供的“数据分析工具”；,3、利用回归函数,四.回归分析,9,例4(线性回归)、我们收集了某厂家同一车型中旧车的车龄及其售价数据，请使用,给图表增加趋势线,，求车龄对售价的回归方程,并计算车龄为6.5年的旧车售价是多少.,车龄,价格(万),1,56.0,2,48.5,3,42.0,4,37.6,5,32.5,6,28.7,7,22.2,8,18.5,9,15.0,10,12.5,6.5年的旧车车价=-4.8091*6.5+57.8=26.54,1、给图表增加趋势线进行回归分析,10,11,有些数据间并不是简单的线性关系，如果用线性模式求其回归方程式，判定系数（,R,2,）,很小，根本不具有任何解释力。因此要引入非线性回归，如多项式、指数、对数等回归方法。,例5(非线性回归)、我们收集了年龄与月收入关系的数据，请绘制该数据的散点图，并求年龄对月收入的回归方程式。,12,例6(非线性回归)、我们收集了一个原始森林中树的直径与高度之间的关系数据,请绘制该数据的散点图，并求直径与高度的回归方程式(指数回归)。,13,加入趋势线可以进行简单的回归分析，但要获得更多的统计数据，可以使用数据分析工具，求简单相关系数、判定系数、用,F,检定判定变量与自变量间是否有回归关系存在、用,t,检定判定各回归系数是否不为0、计算回归系数的置信度、标准残差等。,例 7、根据广告费与销售额统计数据使用分析工具进行回归分析。,2、使用“数据分析工具”进行回归分析,回归分析信息说明,14,方差分析(,F,检定):用回归分析检定,判定变量与自变量之间是否有显著的回归关系存在。如果显著水准(,Significance F)a,值,回归关系存在,否则不存在。,(在这里判定系数,a=1-,置信度,在我们回归分析中置信度取95%,所以,a=1-95%=0.05),15,T,检定:判断回归系数与常数项是否为0,Intercept:,回归方程中的常数项信息,广告费(万):自变量,X,的回归系数信息,16,车龄,价格(万),1,56.0,2,48.5,3,42.0,4,37.6,5,32.5,6,28.7,7,22.2,8,18.5,9,15.0,10,12.5,例8.我们收集了某厂家同一车型中旧车的车龄及其售价数据，请使用,数据分析工具,，求车龄对售价的回归方程,y=-4.8091x+57.8,17,练习题:以下是银行客户的存款金额与贷款金额对照表,用数据分析工具求存款金额对贷款金额的回归方程,并预测当存款金额为80千万时,贷款金额为多少?,y=0.8903x 3.4461,18,通过给图表加入趋势线只能进行简单的单一参数的回归分析，但在许多情况下需要使用多个自变量来预测一个变量的情况,这种回归分析叫复回归，复回归必须通过数据分析工具进行。,复回归,19,例9、银行为了核发信用卡，收集了申请人的每月总收入、不动产、动产、每月房贷与抚养支出费用等数据，并以业务主管员的经验，主观地给予一个信用分数。为使评估信用分数能有一套公式，免得老是依赖主管评分，请使用复回归求其回归方程式。,y=a,1,x,1,+a,2,x,2,+a,3,x,3,+a,4,x,4,+a,5,x,5,+b,20,21,例10.我们收集了某厂家同一车型中旧车的车龄,行驶里程与售价数据，请使用,数据分析工具,，求车龄,行驶里程对售价的回归方程,y=-1.5407*,车龄-0.4278*,里程+65.9959,22,练习 影响出勤率的因素分析,1-5代表影响程度，请使用复回归求其回归方程式。,23,非线性的复回归,例11.我们收集了年龄与月收入关系的数据，使用数据分析工具求年龄对月收入的回归方程式。,y=a,1,x,2,+a,2,x+b,24,y=-36.54x,2,+3463.7x-42087,25,(1)直线回归函数,LINEST(),使用最小平方法计算最适合于变量区域的回归直线公式,并传回该直线公式的数组。可以用于单回归和复回归.,语法:,LINEST(,变量区域,自变量区域,常数项是否不为零,是否返回附加的统计值),操作方法:,1.选定输出区域,2.输入公式,3.,Ctrl+Shift+Enter,例12.以广告费与销售量的单一参数数据为例使用,LINEST,函数,求线性回归方程式。,3、使用回归函数进行回归分析,单回归(,y=,bx,+c),输出结果,y=9.1846x+299.8688,26,复回归(,y=,b,n,x,+,b,n,-1,x+b,1,x+c),输出结果,例13.我们收集了某厂家同一车型中旧车的车龄,行驶里程与售价数据，请使用,LINEST,函数，求车龄,行驶里程对售价的回归方程,y=-1.5407*,车龄-0.4278*,里程+65.9959,27,(2)线性预测函数(,FORECAST),根据给定的数据计算或预测未来值。以数组或数据区域的形式给定,x,值和,y,值后，返回基于,x,的线性回归预测值。使用此函数可以对未来销售额、库存需求或消费趋势进行预测。,FORECAST,(,x,known_y,known_x,),X,为需要进行预测的数据点。,Known_y,为因变量数组或数据区域。,Known_x,为自变量数组或数据区域。,例 14.以广告费与销售量的单一参数数据为例使用,Forecast,函数预测当广告费用为500万元时销售量为多少。,FORECAST(500,$C$2:$C$13,$B$2:$B$13)=4892万,28,(3)线性趋势函数（,TREND）,返回一条线性回归拟合线的一组纵坐标值（,y,值）。即找到适合给定的数组,known_y,和,known_x,的直线（用最小二乘法），并返回指定数组,new_xs,值在直线上对应的,y,值。,语法：,TREND(known_y,known_x,new_x,const),操作方法:,1.选定输出区域,2.输入公式,3.,Ctrl+Shift+Enter,例15、以广告费与销售量的单一参数数据为例使用,TREND,函数预测一组广告费的销售量。,29,(4)指数回归函数,LOGEST：,指数回归函数，计算最符合观测数据组的指数回归拟合曲线，并返回描述该曲线的数组。此曲线的方程为：,y=b*,m,x,or y=(b*(m,1,x1,)*(m,2,x2,)*)（,如果有多个,x,值）,LOGEST,(,known_ys,known_xs,const,stats),GROWTH,:,指数趋势函数，根据给定的数据预测指数增长值。根据已知的,x,值和,y,值，函数,GROWTH,返回一组新的,x,值对应的,y,值。可以使用,GROWTH,工作表函数来拟合满足给定,x,值和,y,值的指数曲线。,语法：,GROWTH,(,known_ys,known_xs,new_xs,const),选定输出区域输入公式,Ctrl+Shift+Enter,30,