office数据分析课件

1、office数据分析PPT课件1数据分析数据分析数据分析数据分析: :根据电子表格中的数据以图形或数值的方式得出一些有意义的结论.比如:数据的排序,筛选,运算,统计,图表化等等都可以说是数据分析的手段.这里我们主要讲以下几个数据分析问题:一一. .变量求解变量求解: :有些数据之间存在因果关系,这些因果关系可以用数学模型来描述,如方程式,不等式等,如果知道了结果数据,能否根据这些数学模型求出原因变量的值?EXCEL提供一些这样的方法,如单变量求解单变量求解,规划求解规划求解等.二. .模拟运算表模拟运算表: :根据数学模型(公式)为一组假设数据产生预测结果,以供分析决策三三. .相关性分析相关

2、性分析: :四四. .回归分析回归分析: :当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，通常可由一个或多个自变量来预测一个变量的值。office数据分析PPT课件2数据分析方法数据分析方法1.1.分析工具库分析工具库 Excel 提供了一组数据分析工具，称为“分析工具库”，在建立复杂统计或工程分析时可节省步骤。只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或工程函数，在输出表格中显示相应的结果。其中有些工具在生成输出表格时还能同时生成图表。安装“分析工具库”:要使用查看可用的分析工具，请单击“工具”菜单中的“数据分析”命令。如果“工具”菜单中没有“数据分析”

3、命令，则需要安装“分析工具库”。2.2.工作表函数工作表函数 Excel 还提供了其他一些统计、财务和工程函数。某些统计函数是内置函数，而其他一些函数只有在安装了“分析工具库”之后才能使用。预备知识要要使用这些工具，用户还必须熟悉需要进行分析的统计学或工程学的特定使用这些工具，用户还必须熟悉需要进行分析的统计学或工程学的特定领域领域。office数据分析PPT课件3三.相关性分析相关性分析： 判断两组数据集（可以使用不同的度量单位）之间的关系。判断两组数据集（可以使用不同的度量单位）之间的关系。相关系数（R）： 相关性计算的返回值。用来确定两个区域中数据的变化是否相关性计算的返回值。用来确定两

4、个区域中数据的变化是否相关，以及相关的程度。是两组数据集的协方差除以它们标准相关，以及相关的程度。是两组数据集的协方差除以它们标准偏差的乘积。偏差的乘积。1 1、R0R0：一个集合的较大数据与另一个集合的较大数据相对应（：一个集合的较大数据与另一个集合的较大数据相对应（正相关）正相关）2 2、R0R0：一个集合的较大数据与另一个集合的较小数据相对应（：一个集合的较大数据与另一个集合的较小数据相对应（负相关）负相关）3 3、R=0R=0：两个集合中的数据互不相关：两个集合中的数据互不相关。|R|0.4 |R|0.4 ：低度相关；：低度相关；0.4=|R|0.6:0.4=|R|0.6:中度相关；中

5、度相关； 0.6=|R|0.8:0.6=|R|=0.8:|R|=0.8:非常高度相关非常高度相关office数据分析PPT课件41、CORREL函数：函数：CORRELCORREL(array1array1,array2array2)返回单元格区域 array1 和 array2 之间的相关系数。使用相关系数可以确定两种属性之间的关系。例如，可以检测某地的平均温度和空调使用情况之间的关系。Array1 第一组数值单元格区域。Array2 第二组数值单元格区域。如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略，但是包含零值的单元格将计算在内。如果 array1 和 array2 的数

6、据点的数目不同，函数 CORREL 返回错误值 #N/A。office数据分析PPT课件5例例 1 1、利用统计数据计算广告费与销售额之间的相关系、利用统计数据计算广告费与销售额之间的相关系数。数。相关系数相关系数CORREL(B4:B15,C4:C15)=0.92251818office数据分析PPT课件6例例2.2.我们收集了某厂家同一车型中旧车的车龄及其售价数据，求价我们收集了某厂家同一车型中旧车的车龄及其售价数据，求价格与车龄的相关系数格与车龄的相关系数 office数据分析PPT课件7例例 3 3、总平均成绩、出勤率、选修学分与每周打工小时、总平均成绩、出勤率、选修学分与每周打工小时

7、数的关系。数的关系。2、使用数据分析工具求相关矩阵、使用数据分析工具求相关矩阵利用CORREL函数只能返回两种属性之间的相关系数，如果使用Excel提供的“数据分析”工具可以计算多组数据间的相关系数，组成相关系数表。office数据分析PPT课件8例例 利用利用“数据分析数据分析”工具计算汽车扳金、省油与价格的工具计算汽车扳金、省油与价格的满意度之间的相关系数。满意度之间的相关系数。汽车的扳金、省油与价格的满意度汽车的扳金、省油与价格的满意度(5-很满意很满意,1-很不满意很不满意)扳金扳金省油省油价格价格44332255221144333333443333334444333311555544

8、3333333344443333552222334433225555115555扳金扳金省油省油价格价格扳金扳金1 1省油省油-0.94-0.941 1价格价格-0.91-0.91 0.8350.8351 1office数据分析PPT课件9回归分析回归分析:当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，通常可由一个或多个自变量来预测一个变量的值。回归方程回归方程:表达参数与结果之间相互关系的数学方程式(数学模型)线性回归线性回归: :如果变量与结果之间具有线性关系,我们可以用线性方程式来描述它们之间的关系,这种回归方法叫线性回归.非线性回归非线性回归: :如果变量与结果之间不具有

9、线性关系,我们必须用非线性方程式来描述它们之间的关系(如指数关系,对数关系等等),这种回归方法叫非线性回归.单回归单回归:当一个结果只与一个参数存在联系时，进行的回归分析称为单回归。复回归复回归:当一个结果与多个参数存在联系时，进行的回归分析称为复回归。判定系数判定系数(R(R2 2):):用来确定回归方程式的可解释性，即吻合程度。范围在0-1之间，越接近1，解释性越强，即吻合程度越高。回归方法：回归方法：1、给图表增加趋势线；2、使用Excel提供的“数据分析工具”；3、利用回归函数四四. .回归分析回归分析 office数据分析PPT课件10例例4(4(线性回归线性回归) )、 我们收集了

10、某厂家同一车型中旧车的车龄及其售价数我们收集了某厂家同一车型中旧车的车龄及其售价数据，请使用据，请使用给图表增加趋势线给图表增加趋势线，求车龄对售价的回归方程，求车龄对售价的回归方程, ,并计算车龄为并计算车龄为6.56.5年的旧车售价是多少年的旧车售价是多少. .车龄车龄价格价格( (万万) )156.0248.5342.0437.6532.5628.7722.2818.5915.01012.5旧车车龄与售价关系图旧车车龄与售价关系图y = -4.8091x + 57.8R2 = 0.98650.010.020.030.040.050.060.0024681012车龄售价6.5年的旧车车价年

11、的旧车车价=-4.8091*6.5+57.8=26.541 1、给图表增加趋势线进行回归分析、给图表增加趋势线进行回归分析office数据分析PPT课件11office数据分析PPT课件12有些数据间并不是简单的线性关系，如果用线性模式求其回归方程式，判定系有些数据间并不是简单的线性关系，如果用线性模式求其回归方程式，判定系数（数（R R2 2）很小，根本不具有任何解释力。因此要引入非线性回归，如多项式、）很小，根本不具有任何解释力。因此要引入非线性回归，如多项式、指数、对数等回归方法。指数、对数等回归方法。例例5(5(非线性回归非线性回归) )、 我们收集了年龄与月收入关系的数据，请绘制该数

12、我们收集了年龄与月收入关系的数据，请绘制该数据的散点图，并求年龄对月收入的回归方程式。据的散点图，并求年龄对月收入的回归方程式。年龄年龄每月所得每月所得15156000.06000.0202010000.010000.0252515000.015000.0303026000.026000.0353535000.035000.0404042000.042000.0454550500.050500.0505040500.040500.0555537650.037650.0606030500.030500.0656525000.025000.0707015800.015800.0757510200.

13、010200.080808000.08000.0年龄与每月所得的关系图(多项式回年龄与每月所得的关系图(多项式回归)归)y = -36.54x2 + 3463.7x -42087R2 = 0.8840.020000.040000.060000.0020406080100年龄每月所得office数据分析PPT课件13例例6(6(非线性回归非线性回归) )、 我们收集了一个原始森林中树的直径与高度之间的我们收集了一个原始森林中树的直径与高度之间的关系数据关系数据, ,请绘制该数据的散点图，并求直径与高度的回归方程式请绘制该数据的散点图，并求直径与高度的回归方程式( (指数指数回归回归) )。直径直

14、径高度高度0.90.917171.21.225252.92.932323 335353.33.346464 458586.56.569699.69.6757512.412.4727216.216.2787827.227.28585树的直径与高度关系图y = 21.512Ln(x) + 19.478R2 = 0.92570102030405060708090100051015202530直径高度office数据分析PPT课件14加入趋势线可以进行简单的回归分析，但要获得更多的统计数据，可以使用数据分析工具，求简单相关系数、判定系数、用F检定判定变量与自变量间是否有回归关系存在、用t检定判定各回归

15、系数是否不为0、计算回归系数的置信度、标准残差等。例 7、根据广告费与销售额统计数据使用分析工具进行回归分析。2 2、使用、使用“数据分析工具数据分析工具”进行回归分析进行回归分析月份月份 广告费广告费(万万) 销售量销售量(万万)1 1245245300030002 2280280320032003 3175175185018504 4165165165016505 5150150150015006 6190190240024007 7240240280028008 8610610296029609 9190190240024001010150150160016001111140140150

16、01500121222022023502350回归分析信息说明回归分析信息说明office数据分析PPT课件15方差分析方差分析dfSSMSFSignificance F回归分析117654421765442 6.7260.026794856残差102624983 262498.3总计114390425结结论论:F检定中的显著水准检定中的显著水准SignificanceF=0.026794856a=0.05说明说明:销售量与广告费存在回归关系销售量与广告费存在回归关系.方差分析方差分析(F检定检定):用回归分析检定用回归分析检定,判定变量与自变量之间是否有显著的判定变量与自变量之间是否有显著的

17、回归关系存在。如果显著水准回归关系存在。如果显著水准(SignificanceF)a值值,回归关系存在回归关系存在,否则不否则不存在。存在。(在这里判定系数在这里判定系数a=1-置信度置信度,在我们回归分析中置信度取在我们回归分析中置信度取95%,所以所以a=1-95%=0.05)office数据分析PPT课件16t 检定t 检定Coefficients 标准误差 t Stat P-value Lower 95% Upper 95% 下限 95.0% 上限 95.0%Intercept1545.831536 315.1378 4.905255 0.0006 843.6605796 2248.0

18、02843.66058 2248.0025广告费(万)3.14338351 1.212087 2.593364 0.0268 0.442684371 5.844083 0.44268437 5.8440827T检定检定:判断回归系数与常数项是否为判断回归系数与常数项是否为0Intercept:回归方程中的常数项信息回归方程中的常数项信息广告费广告费(万万):自变量自变量X的回归系数信息的回归系数信息结结论论:自变量广告费的显著水准自变量广告费的显著水准P-value=0.026795a=0.05说明：广告费与销售量间存在直线关系，其系数为说明：广告费与销售量间存在直线关系，其系数为3.1433

19、8351，正相关。，正相关。回归方程式为回归方程式为:Y=3.143384X+1545.832t检定结果中，常数项为检定结果中，常数项为1545.832，P-value=0.00061826a=0.05说明说明:回归方程式的常数项不应为回归方程式的常数项不应为0，不可将其省略。，不可将其省略。office数据分析PPT课件17车龄车龄价格价格( (万万) )156.0248.5342.0437.6532.5628.7722.2818.5915.01012.5例例8.8.我们收集了某厂家同一车型中旧车的车龄及其售价数据，请使我们收集了某厂家同一车型中旧车的车龄及其售价数据，请使用用数据分析工具数

20、据分析工具，求车龄对售价的回归方程，求车龄对售价的回归方程SUMMARY OUTPUT回归统计Multiple R0.993241R Square0.986527Adjusted R Square0.984843标准误差1.804792观测值10方差分析dfSSMSFSignificance F回归分析1 1908.007 1908.007 585.76829.0595E-09残差8 26.05818 3.257273总计9 1934.065Coefficients 标准误差 t StatP-value Lower 95% Upper 95%下限 95.0% 上限 95.0%Intercept

21、57.8 1.232907 46.88106 4.74E-11 54.9569086 60.64309 54.95691 60.64309车龄-4.809090909 0.198701 -24.2026 9.06E-09 -5.2672966 -4.35089 -5.2673 -4.35089y=-4.8091x+57.8office数据分析PPT课件18练习题练习题:以下是银行客户的存款金额与贷款金额对照表以下是银行客户的存款金额与贷款金额对照表,用数据分析工用数据分析工具求存款金额对贷款金额的回归方程具求存款金额对贷款金额的回归方程,并预测当存款金额为并预测当存款金额为80千万时千万时,贷

22、款金额为多少贷款金额为多少?存款金额(千万)贷款金额(千万)655410287423458489581736232251241071351174537回归统计Multiple R0.999956R Square0.999912Adjusted R Square0.999901标准误差0.314492观测值10方差分析dfSSMSFSignificance F回归分析1 8970.809 8970.8907011.65436E-17残差8 0.791241 0.0989总计98971.6Coefficients 标准误差 t Stat P-valueLower 95%Upper 95%下限 95

23、.0% 上限 95.0%Intercept-3.44460939 0.248681 -13.857E-07 -4.018069372 -2.87115 -4.018069 -2.8711494存款金额(千万) 0.890332158 0.002956 301.172E-17 0.883514957 0.8971490.883515 0.89714936y=0.8903x3.4461office数据分析PPT课件19通过给图表加入趋势线只能进行简单的单一参数的回归分析，但在通过给图表加入趋势线只能进行简单的单一参数的回归分析，但在许多情况下需要使用多个自变量来预测一个变量的情况许多情况下需要使用

24、多个自变量来预测一个变量的情况, ,这种回归分这种回归分析叫复回归，复回归必须通过数据分析工具进行。析叫复回归，复回归必须通过数据分析工具进行。复回归复回归office数据分析PPT课件20例9、银行为了核发信用卡，收集了申请人的每月总收入、不动产、动产、每月房贷与抚养支出费用等数据，并以业务主管员的经验，主观地给予一个信用分数。为使评估信用分数能有一套公式，免得老是依赖主管评分，请使用复回归求其回归方程式。每月总收每月总收入(万)入(万)不动产不动产(百万)(百万)动产动产(百万)(百万)每月房每月房贷(万)贷(万)抚养费抚养费(万)(万)信用信用指数指数6.56.512.012.03.03

25、.02.02.02.02.082827.27.28.08.02.02.00.00.02.02.086863.83.80.00.01.01.00.00.01.01.070708.58.515.015.03.53.52.82.82.02.090904.24.20.00.00.50.50.00.01.51.575756.36.36.06.02.02.01.81.81.51.580803.03.00.00.00.60.60.00.00.00.070703.23.23.03.01.01.00.00.02.02.06565图表标题y=a1x1+a2x2+a3x3+a4x4+a5x5+boffice数据分析

26、PPT课件21回归统计Multiple R0.990989406R Square0.982060003Adjusted R Square0.93721001标准误差2.179360967观测值8结 结论 论:R=0.999099,R2=0.98206,调整后的调整后的R=0.93721说明说明:回归方程式可解释信用分数的差异程度相当高回归方程式可解释信用分数的差异程度相当高.方差分析dfSSMSFSignificance F回归分析552010421.90.044248345残差2 9.499 4.75总计7 529.5结结论论:F检定中的显著水准检定中的显著水准SignificanceF=0

27、.044248a=0.05说明说明:每月总收入、不动产、动产、每月房贷、抚养每月总收入、不动产、动产、每月房贷、抚养支出与信用分数整体间有明显回归关系存在。支出与信用分数整体间有明显回归关系存在。Coefficients 标准误差 t Stat P-value Lower 95% Upper 95% 下限 95.0% 上限 95.0%Intercept57.07610213 4.9504323 11.53 0.00744 35.7760961 78.376108 35.7760961 78.3761082每月总收入(万) 5.3509127050.995484 5.375 0.03291 1.

28、06768767 9.6341377 1.06768767 9.63413774不动产(百万)0.703920828 0.9303825 0.757 0.52827 -3.2991947 4.7070363 -3.2991947 4.70703632动产(百万)-4.9618931 5.4451066 -0.91 0.45835 -28.390312 18.466526 -28.39031218.466526每月房贷(万)-0.089892521 1.7158095 -0.05 0.96298 -7.47243017.292645 -7.4724301 7.29264503抚养费(万)-2.4

29、99189095 1.7049759 -1.47 0.28034 -9.8351133 4.8367351 -9.8351133 4.83673514结结论论:即：信用分数即：信用分数=5.350913*每月总收入每月总收入+57.0761。从回归方程式中排除掉，最后的回归方程式为：从回归方程式中排除掉，最后的回归方程式为：Y=5.350913X+57.0761。说明：这些参数与信用分数间不存在显著的直线关系，所以可将这些参数的系数说明：这些参数与信用分数间不存在显著的直线关系，所以可将这些参数的系数其余的不动产、动产、每月房贷、抚养支出等四个参数的显著水准（其余的不动产、动产、每月房贷、抚养

30、支出等四个参数的显著水准（P-value）均大于a=0.05说明：每月总收入与信用分数间存在直线关系，其系数为说明：每月总收入与信用分数间存在直线关系，其系数为5.350913，正相关。，正相关。其它五个自变量中，只有每月总收入的显著水准其它五个自变量中，只有每月总收入的显著水准P-value=0.032912a=0.05t检定结果中，常数项为检定结果中，常数项为57.0761,其显著水准其显著水准P-value=0.007439a=0.05说明说明:回归方程式的常数项不应为回归方程式的常数项不应为0，不可将其省略。，不可将其省略。office数据分析PPT课件22例例10.10.我们收集了某

31、厂家同一车型中旧车的车龄我们收集了某厂家同一车型中旧车的车龄, ,行驶里程与售价数据，请使行驶里程与售价数据，请使用用数据分析工具数据分析工具，求车龄，求车龄, ,行驶里程对售价的回归方程行驶里程对售价的回归方程车龄车龄里程里程(万万公里公里)价格价格(万)(万)1 18.58.561.061.02 2212153.553.53 3353546.546.54 4434341.541.55 5505037.037.06 6585832.032.07 7656527.527.58 8767621.021.09 9808018.018.01010909012.012.0SUMMARY OUTPUTS

32、UMMARY OUTPUT回归统计回归统计Multiple RMultiple R0.99990.9999R SquareR Square0.99990.9999Adjusted R SquareAdjusted R Square0.99980.9998标准误差标准误差0.20550.2055观测值观测值1010方差分析方差分析dfdfSSSSMSMSF FSignificance FSignificance F回归分析回归分析2 2 2275.72275.7 1137.851137.85 26937.3526937.352.49919E-142.49919E-14残差残差7 7 0.2957

33、0.2957 0.042240.04224总计总计9 922762276CoefficientsCoefficients 标准误差标准误差 t Statt StatP-valueP-valueLower 95%Lower 95%Upper 95%Upper 95% 下限 95.0%下限 95.0% 上限 95.0%上限 95.0%InterceptIntercept65.9958724265.99587242 0.1862630.186263 354.315354.315 3.77E-163.77E-16 65.555430565.5554305 66.43631466.436314 65.5

34、5543165.555431 66.436314366.4363143车龄车龄-1.540712946-1.540712946 0.2072370.207237 -7.4345-7.4345 0.0001450.000145 -2.0307505-2.0307505 -1.050675-1.050675 -2.030751-2.030751 -1.0506754-1.0506754里程里程-0.427767355-0.427767355 0.0238440.023844-17.94-17.94 4.13E-074.13E-07 -0.4841495-0.4841495 -0.371385-0.

35、371385-0.48415-0.48415 -0.3713852-0.3713852y=-1.5407*车龄车龄-0.4278*里程里程+65.9959office数据分析PPT课件23练习练习 影响出勤率的因素分析影响出勤率的因素分析,1-5,1-5代表影响程度，请使用代表影响程度，请使用复回归求其回归方程式。复回归求其回归方程式。office数据分析PPT课件24非线性的复回归非线性的复回归年龄平方年龄 每月所得225156,000 4002010,000 6252515,000 9003026,000 12253535,000 16004042,000 20254550,500 250

36、05040,500 30255537,650 36006030,500 42256525,000 49007015,800 56257510,200 6400808,000 例例11.11.我们收集了年龄与月收入关系的数据，使用数据分析工具求年龄对我们收集了年龄与月收入关系的数据，使用数据分析工具求年龄对月收入的回归方程式。月收入的回归方程式。年龄 每月所得156,000 2010,000 2515,000 3026,000 3535,000 4042,000 4550,500 5040,500 5537,650 6030,500 6525,000 7015,800 7510,200 808,

37、000 y=a1x2+a2x+boffice数据分析PPT课件25回归统计Multiple R0.94023005R Square0.88403254Adjusted R Square0.86294755标准误差5383.55004观测值14方差分析dfSSMSFSignificance F回归分析2 2.43E+09 1.22E+0941.92717.1425E-06残差11 3.19E+08 28982611总计13 2.75E+09Coefficients标准误差t StatP-valueLower 95% Upper 95% 下限 95.0%上限 95.0%Intercept-4208

38、7.0478250.414-5.1012 0.000343 -60246.0938-23928 -60246.09-23928年龄平方-36.5398353.990553-9.15658 1.77E-06 -45.3229871 -27.75668 -45.32299-27.75668年龄3463.74588385.7649 8.978904 2.14E-062614.68262 4312.8091 2614.6826 4312.8091y=-36.54x2+3463.7x-42087office数据分析PPT课件26(1)(1)直线回归函数直线回归函数LINEST()LINEST()使用最小

39、平方法计算最适合于变量区域的回归直线公式,并传回该直线公式的数组。可以用于单回归和复回归.语法语法: :LINEST(变量区域,自变量区域,常数项是否不为零,是否返回附加的统计值)操作方法操作方法: :1.选定输出区域 2.输入公式 3.Ctrl+Shift+Enter例12.以广告费与销售量的单一参数数据为例使用LINEST函数,求线性回归方程式。3 3、使用回归函数进行回归分析、使用回归函数进行回归分析系数(b)常数标准误差(b)标准误差判定系数(R2)对y估计值的标准误差F统计值F检定之自由度回归平方残差平方单回归单回归(y=bx+c)(y=bx+c)输出结果输出结果9.18462980

40、3299.8687911.215127545261.6524490.851039787229.16244657.13202018103000312.402525154.264y=9.1846x+299.8688office数据分析PPT课件27系数(bn)系数(bn-1)系数(b1)常数(c)标准误差(bn) 标准误差(bn-1)标准误差(b1)标准误差判定系数(R2) 判定系数(R2)判定系数(R2) 对y估计值的标准误差F统计值F统计值F统计值F检定之自由度回归平方回归平方回归平方残差平方复回归复回归(y=b(y=bn nx+bx+bn-1n-1x+bx+b1 1x+c)x+c)输出结果输出结果例例13.13.我们收集了某厂家同一车型中旧车的车龄我们收集了某厂家同一车型中旧车的车龄, ,行驶里程与售价数据，请使行驶里程与售价数据，请使用用LINESTLINEST函数，求车龄函数，求车龄, ,行驶里程对售价的回归方程行驶里程对售价的回归方程车龄车龄里程里程(万万公里公里)价格价格(万)(万)1 18.58.561.061.02 2212153.553.53 335