EXCEL实用教程第8章.ppt

《EXCEL实用教程第8章.ppt》由会员分享，可在线阅读，更多相关《EXCEL实用教程第8章.ppt（46页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

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

2、值值1值值2值值1值值28.1 用假设方法求解用假设方法求解功能：对两个变量不同的值，模拟计算他们对一个公式的值引起变化。53.方案管理(“工具”菜单)功能：对多个假设条件(方案)进行模拟计算。一个方案：是一组变量与一组对应的模拟值，观察公式的计算结果。o方案名称o可变单元格与模拟值（期望值）o结果单元格（公式）n举例8.1 用假设方法求解用假设方法求解68.2 线性回归分析线性回归分析o在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的关系函数表达式（称回归方程式）。o当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析；Y=ax+bo当研究的因果关系涉及因变量和两个或

3、两个以上自变量时，叫做多元回归分析。Y=a1x1+a2x2+a3x3+b7 例1 对销售额进行多元回归分析预测。8.2 线性回归分析线性回归分析8设变量：Y=销售额X1=电视广告费用X2=报纸广告费用方程为：Y=a1X1+a2X2+b通过线性回归分析确定a1,a2,b的值，从而确定方程。8.2 线性回归分析线性回归分析9操作：(1)“工具”菜单加载宏分析工具库(2)“工具”数据分析回归(3)设置:Y，X值输入区，输出区域(4)根据结果得出方程8.2 线性回归分析线性回归分析10n“规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学

4、规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。n规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。8.3 规划求解规划求解11o意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果（例如：获得最大的利润、最小的成本等）。o注意事项：n在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。n添加规划求解命令:工具加载宏规划求解8.3 规划求解规划求

5、解12o 方程：MAX Z=90*x1+75*x2+50*x3 o约束条件：3*x1+4*x2+5*x3400 4*x1+3*x2+2*x3280 x1 50 x232A AB BC C合计合计合计合计利润利润利润利润/件件件件9090元元元元7575元元元元5050元元元元机工机工机工机工3 3小时小时小时小时4 4小时小时小时小时5 5小时小时小时小时=400=400小时小时小时小时手工手工手工手工4 4小时小时小时小时3 3小时小时小时小时2 2小时小时小时小时=280=280小时小时小时小时最大数量最大数量最大数量最大数量=50=32=32件件件件求：数量求：数量求：数量求：数量x1x

6、1x2x2x3x3使利润使利润使利润使利润z z最大最大最大最大13o求解方程组n求X,Y,Zn技巧：其中一个方程为目标方程，另外2个方程为约束条件8.3 规划求解规划求解148.4 移动平均移动平均o移动平均法是根据时间序列数据，逐项推移计算移动平均，反映数据的长期趋势。o如：第t时间点的移动平均值Mi，可当作第t+1时间点的预测值。o基于特定的过去某段时期变量的均值，对未来值进行预测。155、10、20、30日均线日均线16o移动平均n简单移动平均:每个观测值用相同的权数n加权移动平均:每个观测值有不同的权数o进行加权平均预测时，通常是先对数据进行加权处理，然后再调用分析工具计算。o操作：

7、工具数据分析移动平均o举例8.4 移动平均移动平均178.5 指数平滑指数平滑o指数平滑是在移动平均的基础上的进一步扩展。o指数平滑法是用过去时间数列值的加权平均数作为趋势值，越靠近当前时间的指标越具有参考价值，因此给予更大的权重，按照这种随时间指数衰减的规律对原始数据进行加权修匀。188.6 相关分析相关分析o功能：研究(估计)变量之间相关的密切程度。o在实际问题中，许多变量之间的关系并不是完全确定性的。n统计关系：无法一一对应，收入与支出、广告与销售等o例如家庭消费与收入的关系不是完全确定的。n收入水平相同的家庭，消费额不同；n消费额相同的家庭，收入不同。n函数关系：一一对应，例如Ya+b

8、X19 不相关不相关不相关不相关不相关不相关 负线性相关负线性相关负线性相关负线性相关负线性相关负线性相关 正线性相关正线性相关正线性相关正线性相关正线性相关正线性相关 非线性相关非线性相关非线性相关非线性相关非线性相关非线性相关 完全负线性相关完全负线性相关完全负线性相关完全负线性相关完全负线性相关完全负线性相关完全正线性相关完全正线性相关完全正线性相关完全正线性相关完全正线性相关完全正线性相关 8.6 相关分析相关分析20oo线性相关分析:研究两个变量间线性相关程度。计算相关系数r。r是描述线性关系的程度和方向的统计量，无单位，在+1-1之间。o正相关：相关系数r0。例如：随着X值的增大，

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

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

11、变量例如：考察不同的“地域”对农作物产量的影响（在“品种”和“施肥量”不变）o观测变量：农作物产量。o控制变量“地域”（因素）：华南，华东，东北（3个水平，3个总体的比较）26n例如：分析不同的收入水平（高，中、低）的人，看电视的时间是否有差异？n待检假设为：8.7.1 单因素方差分析单因素方差分析27o方差分析技巧是认为不同的处理组的均数间的差异来源有两个：o组内差异（随机误差）：可能是由于一些误差造成的差异。n各组均值与组内变量的偏差平方和的总和。o组间差异（处理条件）：不同的处理方法造成的差异。n各组均值与总均值偏差的平方和。8.7.1 单因素方差分析单因素方差分析28o单因素方差分析的

12、作用是通过对某一因素的不同水平进行多次观测，然后统计分析判断该因素的不同水平对考察指标的影响是否相同。o检验几个等方差正态总体的等均值假设。o单因素方差分析的基本假设是各组的均值相等。8.7.1 单因素方差分析单因素方差分析29n工具数据分析方差分析单因素方差分析o考察不同的销售渠道对总销售额的贡献是否相同30运行结果运行结果31运算结果说明o概要：样本数、合计、均值和方差。o方差分析：nSS：离差平方和；df：自由度；nMS：均方差；F：F统计量；nP-value（概率）是原假设成立的概率(越接近0，说明原假设成立的可能性越小，反之原假设成立的可能性越大)nF crit(F临界值):拒绝域的

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

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

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

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