EXCEL8(教程)[1]

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,57,第,8,章,数据分析,8,课时,本章重点：,用假设方法求解,线性回归分析,规划求解,指数平滑,相关分析,单因素方差分析,数据分析,8.1,用假设方法求解,单变量求解,模拟运算表,方案管理,功能,:,对一个公式的计算结果指定一个期望值,观察公式引用的某个变量值的变化。,例如：某企业拟向银行以,7%,的年利率贷款，期限,5,年，企业年偿还能力,100,万，问企业最多可以贷多少款？,8.1,用假设方法求解,1.,单变量求解,(,“,工具,”,菜单,),以上例题等价,=PV(B1,B3,100,0,0),2.,模拟运算表,(,“,数据,”,菜单,),(1),单变量,模拟运算表,对,一个变量,的多个取值，计算与该变量相关的一个或,多个公式的值,。,8.1,用假设方法求解,填入变量所在的单元格地址,(2),双变量模拟运算表,8.1,用假设方法求解,功能：对,两个变量,在不同的取值组合情况下，模拟计算与他们相关的,一个公式,的结果。,行变量值,列,变,量,值,3.,方案管理,(,“,工具,”,菜单,),功能：对多个假设条件,(,方案,),进行模拟计算。,一个方案：是一组变量与一组对应的模拟值，观察一组相关公式的计算结果。,例如 书的单价,20,元，购买,方案,1,：,1,本，折扣,0.98,，计算费用,方案,2,：,5,本，折扣,0.95,，计算费用,方案,3,：,10,本，折扣,0.85,，计算费用,方案,2,：,40,本，折扣,0.7,，计算费用,8.1,用假设方法求解,8.1 用假设方法求解,创建方案：,“工具”菜单,/,方案,/,添加,/,键入,方案,名称,在“可变单元格”框输入对要更改的单元格的引用,/,“确定”,在“方案变量值” 框中，为可变单元格键入数值,如果创建其他方案,/,添加,创建方案汇总报告,“工具”菜单上,/,方案,/,摘要,选择“方案摘要”或“方案数据透视表”,在“结果单元格”框中，输入单元格的引用，该单元格为,方案,用于更改结果的单元格。多个引用需用逗号分隔开。,方案中的可变单元格个数：,=32,8.2,线性回归分析,在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的关系函数表达式（称回归方程式）。,当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析；,Y=ax+b,当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析。,Y=a,1,x,1,+a,2,x,2,+a,3,x,3,+,+b,例,1,对销售额进行多元回归分析预测。,8.2,线性回归分析,通过对一组观察值使用“最小二乘法”直线拟合来执行线性回归分析,设变量：,Y=,销售额,X,1,=,电视广告费用,X,2,=,报纸广告费用,方程为：,Y=a,0,+a,1,X,1,+a,2,X,2,通过线性回归分析确定,a,0,a,1,a,2,的值，从而确定方程。,8.2,线性回归分析,操作：,(1),“,工具,”,菜单加载宏分析工具库,(2),“,工具,”,数据分析回归,(3),设置,:Y,，,X,值输入区，输出区域,(4),根据结果得出方程,8.2,线性回归分析,8.2 线性回归分析-结论,回归分析是根据历史资料的变化规律寻找自变量与因变量之间的回归方程式，确定参数模型而做出预测。在回归模型的确定上，根据历史资料对未来发展进行分析。由于模型是基于历史资料进行的回归分析，能较好地拟合过去，但对未来的预测效果会随时间的延长而减弱。,运用该方法的优点是预测过程简单，参数估计技术比较成熟，缺点是线性回归分析模型预测精度较低,;,而非线性回归预测计算开销大，预测过程复杂。,“,规划,”,是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。,规划求解可求得工作表上某个单元格（目标单元格）中公式的,最优值,（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。,8.3,规划求解,规划问题可以涉及到众多的生产或经营领域的常见问题。,例如,生产的组织安排问题：,如果要生产若干种不同的产品，每种产品需要在不同的设备上加工，需要加工的时间不同，每种产品所获得的利润也不同。要求在各种设备生产能力的限制下，如何安排生产可获得,最大利润,。,例如,运输的调度问题：,如果某种产品的产地和销地有若干个，从各产地到各销地的运费不同。要求在满足各销地的需要量的情况下，如何调度可使得,运费最小,。,8.3,规划求解,8.3 规划求解,例如,作物的合理布局问题：,不同的作物在不同性质的土壤上单位面积的产量是不同的。要求在现有种植面积和完成种植计划的前提下，如何因地制宜使得,总产值最高,。,例如,原料的恰当搭配问题：,在食品、化工、冶金等企业，经常需要使用多种原料配置包含一定成份的产品，不同原料的价格不同，所含成份也不同。要求在满足产品成份要求的情况下，如何配方可使产品,成本最小,。,方程：,MAX Z=90*,x1,+75*,x2,+50*,x3,约束条件：,3*,x1,+4*,x2,+5*,x3,400,4*,x1,+3*,x2,+2*,x3,280,x1, 50,x3,32,8.3,规划求解,规划求解步骤：,确定决策变量，等待解决的变量。,X1,，,X2,，,(2),确定目标函数,Z,：,最大，最小，某整数,(3),确定约束条件：,限制条件（人力、物力、财力等资源）,(4),求解方程，得到最优解,添加规划求解命令,:,工具加载宏规划求解,8.3 规划求解,如果约束条件和目标函数都是线性函数，则称作线性规划；否则为非线性规划。,如果要求决策变量的值为整数，则称为整数规划。,规划求解问题的首要问题是将实际问题数学化、模型化。即将实际问题通过一组决策变量、一组用不等式或等式表示的约束条件以及目标函数来表示。这是求解规划问题的关键。然后用,Excel,的规划求解工具求解。,求解方程组,求,X,Y,Z,技巧：其中一个方程为目标方程，另外,2,个方程为约束条件,规划求解中的可调单元格个数：,0,。例如：随着,X,值的增大，,Y,也增大；,X,值的减小，,Y,也变小。,负相关：相关系数,r0.8,两个变量高度相关。,0.5,r,0.8,两个变量中度相关。,r,0.05,假设成立,8.7.1,单因素方差分析,工具数据分析方差分析单因素方差分析,考察不同的销售渠道对总销售额的贡献是否相同,运行结果,运算结果说明,概要：样本数、合计、均值和方差。,方差分析：,SS,：离差平方和；,df,：自由度；,MS,：均方差；,F,：,F,统计量；,P-value,（,概率）,是原假设成立的概率,.,越接近,0,，假设成立的可能性越小，反之,(0.05),假设成立的可能性越大,.,F crit(,F,临界值,):,拒绝域的临界值。,分析组内和组间离差平方和在总离差平方和中所占的比重，可以直观的看出各组数据对总体离差的贡献。将,F,统计量的值与临界值比较，可以判定是否接受等均值的假设。其中,F,临界值是用,FINV,函数计算出来的。,本例中,F,统计值是,0.848783,，远远小于,F,临界值,3.098393,。所以，接受等均值假设。即认为四种渠道的总体水平没有明显差距。从显著性分析上也可以看出，概率高达,0.48,，远远大于,0.05,。,结论,8.7.2,无重复双因素方差分析,考察两个因素的不同水平对指标的影响是否相同。,实际上是检验几组等方差正态总体下的均值假设。,基本假设分别是各行和各列的均值相等。,例如考察不同的广告媒体和费用对总销售额的影响。,工具数据分析方差分析无重复双因素分析,运行结果,结论,行间、列间和误差的离差平方和水平接近。,行间,F,统计值是,3.427708081,，略小于,F,临界值,3.86254,。显著性分析的概率值,0.06583,也大于,0.05,，所以接受行间等均值假设。即认为不同广告媒体对销售业绩的影响无明显区别。不过当置信度稍稍降低时，,F,统计量将大于,F,临界值，所以建议对不同媒体做进一步研究分析。,列间,F,统计值是,30.004038,，远大于,F,临界值,3.86254,。显著性分析的概率值只有,0.000051,，所以拒绝列间等均值假设。认为不同的广告投放力度对销售有明显的影响。,8.7.3,可重复双因素方差分析,使两个有协同作用的因素同时作用于考察对象，并重复试验，然后通过统计分析判断不同的因素组合在多次试验中对指标的影响是否相同。,仍然是在检验几组等方差正态总体下的均值假设。,基本假设是三个，分别是各行、各列和各行列（可以假设是各,“,平面,”,）的均值相等。,考察不同的,CPU,和不同的主板搭配是否有不同的效果，在保证其他配置相同的条件下，三种,CPU,和四种主板搭配后各自进行三次试验，分别测量整机的综合测试指标,T-Mark,。要求用可重复双因素方差分析研究不同的,CPU,、主板、以及两者的组合对整机性能的影响。,工具数据分析方差分析单因素方差分析,运行结果,结论,行间,F,统计值是,12.14336,，远大于,F,临界值,3.402832,。概率值为,0.000227,也很小。拒绝行间等均值假设，认为不同的,CPU,对整机的性能有明显影响。,列间,F,统计值是,2.507735,，略小于,F,临界值,3.008786,。概率值为,0.08301,也较大。接受列间等均值假设，认为不同的主板对整机的性能无明显影响。,交互的,F,统计值是,1.980174,，小于,F,临界值,2.508187,，概率值,0.10847,更大，接受交互等均值假设。认为不同的,CPU,和主板的搭配对整机性能无明显影响。,在总体方差已知的条件下，检验两个样本均值是否有差异。,8.8 Z-,检验,例,某企业对采用两种方法组装新产品所需的时间（分钟）进行测试，随机抽取,6,个工人，让他们分别采用两种方法组装同一种产品。假设组装的时间服从正态分布，以,=0.05,的显著性水平比较两种组装方法是否有差别。,工具数据分析,Z-,检验,:,双样本平均差检验,输出结果,可以根据,P,值进行判断，也可以根据统计量和临界值比较进行判断。,本例采用的是单尾检验，其单尾,P,值为,0.17,，大于给定的显著性水平,0.05,，所以应该接受原假设，即方法,A,与方法,B,相比没有显著差别,若用临界值判断，得出的结论是一样的，,Z,值为,0.938194,，小于临界值,1.644853,，由于是右尾检验，所以也是接受原假设。,结论,作业,小结,教材,P,146,习题,8,P178,第,12,章,实验,1,实验,5,