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