EXCEL实用教程-第8章.ppt

《EXCEL实用教程-第8章.ppt》由会员分享，可在线阅读，更多相关《EXCEL实用教程-第8章.ppt（46页珍藏版）》请在三一办公上搜索。

1、1,第8章,数据分析,2,8.1 用假设方法求解,单变量求解模拟运算表方案管理,3,功能:对一个公式的计算结果指定一个期望值,从而观察公式引用的某个变量值的变化。,8.1 用假设方法求解,例如：E4=B1*C1,1.单变量求解(“工具”菜单),4,2.模拟运算表(“数据”菜单)(1)单变量模拟运算表,8.1 用假设方法求解,功能：对一个变量不同的值，模拟计算他们对一个或多个公式的值的变化。,5,(2)双变量模拟运算表,8.1 用假设方法求解,功能：对两个变量不同的值，模拟计算他们对一个公式的值引起变化。,6,3.方案管理(“工具”菜单)功能：对多个假设条件(方案)进行模拟计算。一个方案：是一组

2、变量与一组对应的模拟值，观察公式的计算结果。方案名称可变单元格与模拟值（期望值）结果单元格（公式）举例,8.1 用假设方法求解,7,8.2 线性回归分析,在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的关系函数表达式（称回归方程式）。当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析；Y=ax+b当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析。Y=a1x1+a2x2+a3x3+b,8,例1 对销售额进行多元回归分析预测。,8.2 线性回归分析,9,设变量：Y=销售额X1=电视广告费用X2=报纸广告费用方程为：Y=a1X1+a2X2+b通过线性回归

3、分析确定a1,a2,b的值，从而确定方程。,8.2 线性回归分析,10,操作：(1)“工具”菜单加载宏分析工具库(2)“工具”数据分析回归(3)设置:Y，X值输入区，输出区域(4)根据结果得出方程,8.2 线性回归分析,11,“规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。,8.3 规划求

4、解,12,意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果（例如：获得最大的利润、最小的成本等）。注意事项：在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。添加规划求解命令:工具加载宏规划求解,8.3 规划求解,13,方程：MAX Z=90*x1+75*x2+50*x3 约束条件：3*x1+4*x2+5*x3400 4*x1+3*x2+2*x3280 x1 50 x232,14,求解方程组求X,Y,Z技巧：其中一个方程为目标方程，另外2个方程为约束条件,8.3 规划求解,15,8

5、.4 移动平均,移动平均法是根据时间序列数据，逐项推移计算移动平均，反映数据的长期趋势。如：第t时间点的移动平均值Mi，可当作第t+1时间点的预测值。基于特定的过去某段时期变量的均值，对未来值进行预测。,16,5、10、20、30日均线,17,移动平均简单移动平均:每个观测值用相同的权数加权移动平均:每个观测值有不同的权数进行加权平均预测时，通常是先对数据进行加权处理，然后再调用分析工具计算。操作：工具数据分析移动平均举例,8.4 移动平均,18,8.5 指数平滑,指数平滑是在移动平均的基础上的进一步扩展。指数平滑法是用过去时间数列值的加权平均数作为趋势值，越靠近当前时间的指标越具有参考价值，

6、因此给予更大的权重，按照这种随时间指数衰减的规律对原始数据进行加权修匀。,19,8.6 相关分析,功能：研究(估计)变量之间相关的密切程度。在实际问题中，许多变量之间的关系并不是完全确定性的。统计关系：无法一一对应，收入与支出、广告与销售等例如家庭消费与收入的关系不是完全确定的。收入水平相同的家庭，消费额不同；消费额相同的家庭，收入不同。函数关系：一一对应，例如Ya+bX,20,8.6 相关分析,21,线性相关分析:研究两个变量间线性相关程度。计算相关系数r。r是描述线性关系的程度和方向的统计量，无单位，在+1-1之间。正相关：相关系数r0。例如：随着X值的增大，Y也增大；X值的减小，Y也变小

7、。负相关：相关系数r0。例如：随着X值的增大，Y减小；X值的减小，Y增大。r=0，表示无线性关系。,8.6 相关分析,22,r0.8,两个变量高度相关。0.5r0.8,两个变量中度相关。r0.3,两个变量相关较弱。用相关矩阵来描述变量的相关情况。操作：工具数据分析相关系数 举例,8.6 相关分析,23,运用数理的方法对数据进行分析，以鉴别各种因素对研究对象某些特征值的影响大小和影响方式，这种方法叫做方差分析。所关注的对象的特征称为指标，影响指标的各种原因叫做因素，在实验中因素的各种不同状态称为因素的水平。,8.7 方差分析,24,功能：检验3个或3个以上的独立样本的“均值”是否存在差异。方差分

8、析分为：单因素方差分析多因素无重复试验的方差分析多因素重复试验方差分析协方差分析,8.7 方差分析,25,因素（变量）,影响农作物产量的最重要的因素（3个）品种（含不同的水平）施肥量（含不同的水平）地域（含不同的水平）影响广告效果的最主要的因素（5个）广告的形式（含不同的水平）地区的规模（含不同的水平）选择的栏目（含不同的水平）播放的时间（含不同的水平）播放的频率（含不同的水平）,8.7 方差分析,26,8.7.1 单因素方差分析,功能：检验一个“因素”的各“水平”的观测值的均值之间的差异。因素所处的状态称为“水平”。明确观测变量和控制变量例如：考察不同的“地域”对农作物产量的影响（在“品种”

9、和“施肥量”不变）观测变量：农作物产量。控制变量“地域”（因素）：华南，华东，东北（3个水平，3个总体的比较）,27,例如：分析不同的收入水平（高，中、低）的人，看电视的时间是否有差异？待检假设为：,8.7.1 单因素方差分析,28,方差分析技巧是认为不同的处理组的均数间的差异来源有两个：组内差异（随机误差）：可能是由于一些误差造成的差异。各组均值与组内变量的偏差平方和的总和。组间差异（处理条件）：不同的处理方法造成的差异。各组均值与总均值偏差的平方和。,8.7.1 单因素方差分析,29,单因素方差分析的作用是通过对某一因素的不同水平进行多次观测，然后统计分析判断该因素的不同水平对考察指标的影

10、响是否相同。检验几个等方差正态总体的等均值假设。单因素方差分析的基本假设是各组的均值相等。,8.7.1 单因素方差分析,30,工具数据分析方差分析单因素方差分析,考察不同的销售渠道对总销售额的贡献是否相同,31,运行结果,32,运算结果说明,概要：样本数、合计、均值和方差。方差分析：SS：离差平方和；df：自由度；MS：均方差；F：F统计量；P-value（概率）是原假设成立的概率(越接近0，说明原假设成立的可能性越小，反之原假设成立的可能性越大)F crit(F临界值):拒绝域的临界值。,33,分析组内和组间离差平方和在总离差平方和中所占的比重，可以直观地看出各组数据对总体离差的贡献。将F统

11、计量的值与临界值比较，可以判定是否接受等均值的假设。其中F临界值是用FINV函数计算出来的。本例中F统计值是0.848783，远远小于F临界值3.098393。所以，接受等均值假设。即认为四种渠道的总体水平没有明显差距。从显著性分析上也可以看出，概率高达0.48，远远大于0.05。,结论,34,8.7.2 无重复双因素方差分析,考察两个因素的不同水平对指标的影响是否相同。实际上是检验几组等方差正态总体下的均值假设。基本假设分别是各行和各列的均值相等。,35,例如考察不同的广告媒体和费用对总销售额的影响。,工具数据分析方差分析无重复双因素分析,36,运行结果,37,结论,行间、列间和误差的离差平

12、方和水平接近。行间F统计值是3.427708081，略小于F临界值3.86254。显著性分析的概率值0.06583也大于0.05，所以接受行间等均值假设。即认为不同广告媒体对销售业绩的影响无明显区别。不过当置信度稍稍降低时，F统计量将大于F临界值，所以建议对不同媒体做进一步研究分析。列间F统计值是30.004038，远大于F临界值3.86254。显著性分析的概率值只有0.000051，所以拒绝列间等均值假设。认为不同的广告投放力度对销售有明显的影响。,38,8.7.3 可重复双因素方差分析,使两个有协同作用的因素同时作用于考察对象，并重复试验，然后通过统计分析判断不同的因素组合在多次试验中对指

13、标的影响是否相同。仍然是在检验几组等方差正态总体下的均值假设。基本假设是三个，分别是各行、各列和各行列（可以假设是各“平面”）的均值相等。,39,考察不同的CPU和不同的主板搭配是否有不同的效果，在保证其他配置相同的条件下，三种CPU和四种主板搭配后各自进行三次试验，分别测量整机的综合测试指标T-Mark。要求用可重复双因素方差分析研究不同的CPU、主板以及两者的组合对整机性能的影响。,工具数据分析方差分析单因素方差分析,40,运行结果,41,结论,行间F统计值是12.14336，远大于F临界值3.402832。概率值为0.000227也很小。拒绝行间等均值假设，认为不同的CPU对整机的性能有

14、明显影响。列间F统计值是2.507735，略小于F临界值3.008786。概率值为0.08301也较大。接受列间等均值假设，认为不同的主板对整机的性能无明显影响。交互的F统计值是1.980174，小于F临界值2.508187，概率值0.10847更大，接受交互等均值假设。认为不同的CPU和主板的搭配对整机性能无明显影响。,42,在总体方差已知的条件下，检验两个样本均值是否有差异。,8.8 Z-检验,43,例某企业对采用两种方法组装新产品所需的时间（分钟）进行测试，随机抽取6个工人，让他们分别采用两种方法组装同一种产品。假设组装的时间服从正态分布，以=0.05的显著性水平比较两种组装方法是否有差别。,工具数据分析Z-检验:双样本平均差检验,44,输出结果,45,可以根据P值进行判断，也可以根据统计量和临界值比较进行判断。本例采用的是单尾检验，其单尾P值为0.17，大于给定的显著性水平0.05，所以应该接受原假设，即方法A与方法B相比没有显著差别若用临界值判断，得出的结论是一样的，Z值为0.938194，小于临界值1.644853，由于是右尾检验，所以也是接受原假设。,结论,46,小结作业：教材P 146/习题教材P178-189/实验1实验5,