EXCEL实用教程-第8章.ppt

第8章 数据分析 1 8.1 用假设方法求解 o单变量求解 o模拟运算表 o方案管理 2 功能:对一个公式的 计算结果指定一个 期望值,从而观察公 式引用的某个变量 值的变化。 8.1 用假设方法求解 例如：E4=B1*C1 1.单变量求解(“工具”菜单 ) 3 2.模拟运算表(“数据”菜 单) (1)单变量模拟运 算表 值值1 1值值2 2 公式公式1 1 公式公式2 2 公式公式1 1 公式公式2 2 值值1 1 值值2 2 8.1 用假设方法求解 功能：对一个变量不同的值 ，模拟计算他们对一个或多 个公式的值的变化。 4 (2)双变量模拟运算表 公式公式值值1 1值值2 2 值值1 1 值值2 2 8.1 用假设方法求解 功能：对两个变量不同的值，模拟计算他们对 一个公式的值引起变化。 5 3.方案管理(“工具”菜单) 功能：对多个假设条件(方案)进行模拟计算。 一个方案：是一组变量与一组对应的模拟值， 观察公式的计算结果。 o方案名称 o可变单元格与模拟值（期望值 ） o结果单元格（公式） n举例 8.1 用假设方法求解 6 8.2 线性回归分析 o在大量观察数据的基础上，利用数理统 计方法建立因变量与自变量之间的关系函 数表达式（称回归方程式）。 o当研究的因果关系只涉及因变量和一个 自变量时，叫做一元回归分析； Y=ax+b o当研究的因果关系涉及因变量和两个或 两个以上自变量时，叫做多元回归分析。 Y=a1x1+a2x2+a3x3+b 7 例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 线性回归分析 10 n“规划”是数学概念，它是指运用微积 分和线性代数的方法，在满足一组约束 条件的情况下，求出一个多变量函数极 值的模型。数学规划是运筹学的一个分 支，主要包括线性规划、非线性规划、 动态规划和整数规划等。 n规划求解可求得工作表上某个单元格 （目标单元格）中公式的最优值（对直 接或间接与目标单元格中公式相关联的 一组单元格中的数值进行调整，最终在 目标单元格公式中求得期望的结果。 8.3 规划求解 11 o意义：规划求解用于在生产或工作中的一些 问题或项目在受多个因素的制约的前提下如何 获得最佳的结果（例如：获得最大的利润、最 小的成本等）。 o注意事项： n在创建模型过程中，对“规划求解” 模型中的可变单元格数值应用约束条件 ，而且约束条件可以引用其他影响目标 单元格公式的单元格。 n添加规划求解命令: 工具加载宏规划求解 8.3 规划求解 12 o 方程：MAX Z=90*x1+75*x2+50*x3 o约束条件：3*x1+4*x2+5*x3400 4*x1+3*x2+2*x3280 x1 50 x232 A A B B C C 合计合计 利润利润/ /件件9090元元7575元元5050元元 机工机工3 3小时小时4 4小时小时5 5小时小时=32=32件件 求：数量求：数量x1x1x2x2x3x3使利润使利润z z最大最大 13 o求解方程组 n求X,Y,Z n技巧：其中一个方程为目标方程 ，另外2个方程为约束条件 8.3 规划求解 14 8.4 移动平均 o移动平均法是根据时间序列数据，逐项 推移计算移动平均，反映数据的长期趋势 。 o如：第t时间点的移动平均值Mi，可当作第 t+1时间点的预测值。 o基于特定的过去某段时期变量的均值，对未 来值进行预测。 15 5、10、20、30日均线 16 o移动平均 n简单移动平均:每个观测值用相同的 权数 n加权移动平均:每个观测值有不同的 权数 o进行加权平均预测时，通常是 先对数据进行加权处理，然后再调用分 析工具计算。 o操作：工具数据分析移动平均 o举例 8.4 移动平均 17 8.5 指数平滑 o指数平滑是在移动平均的基础上的 进一步扩展。 o指数平滑法是用过去时间数列值的 加权平均数作为趋势值，越靠近当前 时间的指标越具有参考价值，因此给 予更大的权重，按照这种随时间指数 衰减的规律对原始数据进行加权修匀 。 18 8.6 相关分析 o功能：研究(估计)变量之间相关的密切程度 。 o在实际问题中，许多变量之间的关系并不是 完全确定性的。 n统计关系：无法一一对应，收入与支 出、广告与销售等 o例如家庭消费与收入的关系不是 完全确定的。 n收入水平相同的家庭，消 费额不同； n消费额相同的家庭，收入 不同。 n函数关系：一一对应，例如Ya+bX 19 不相关不相关 负线性相关负线性相关 正线性相关正线性相关 非线性相关非线性相关 完全负线性相关完全负线性相关完全正线性相关完全正线性相关 8.6 相关分析 20 oo线性线性相关分析:研究两个变量间线性相关程度。 计算相关系数r。 r是描述线性关系的程度和方向的统计量，无单 位，在 +1 -1之间。 o正相关：相关系数r0。例如：随着X值的增大 ，Y也增大；X值的减小，Y也变小。 o负相关：相关系数r0.8,两个变量高度相关。 o0.5r0.8,两个变量中度相 关。 or0.3,两个变量相关较弱。 o用相关矩阵来描述变量的相关情况 。 o操作：工具数据分析相关系数 o举例 8.6 相关分析 22 o运用数理的方法对数据进行分析， 以鉴别各种因素对研究对象某些特征 值的影响大小和影响方式，这种方法 叫做方差分析。 o所关注的对象的特征称为指标，影 响指标的各种原因叫做因素，在实验 中因素的各种不同状态称为因素的水 平。 8.7 方差分析 23 o功能：检验3个或3个以上的独立 样本的“均值”是否存在差异。 o方差分析分为： n单因素方差分析 n多因素无重复试验的方差分析 n多因素重复试验方差分析 n协方差分析 8.7 方差分析 24 因素（变量） o影响农作物产量的最重要的因素（3个） n品种（含不同的水平） n施肥量（含不同的水平） n地域（含不同的水平） o影响广告效果的最主要的因素（5个） n广告的形式（含不同的水平） n地区的规模（含不同的水平） n选择的栏目（含不同的水平） n播放的时间（含不同的水平） n播放的频率（含不同的水平） 8.7 方差分析 25 8.7.1 单因素方差分析 o功能：检验一个“因素”的各“水平”的观测 值的均值之间的差异。 n因素所处的状态称为“水平” 。 n明确观测变量和控制变量 例如：考察不同的“地域”对农 作物产量的影响（在“品种”和“施肥量 ”不变） o观测变量：农作物产量。 o控制变量“地域”（因素） ：华 南，华东，东北（3个水平，3个总体 的比较） 26 n例如： 分析不同的收 入水平（高， 中、低）的人 ，看电视的时 间是否有差异 ？ n待检假设 为： 8.7.1 单因素方差分析 27 o方差分析技巧是认为不同的处理组的 均数间的差异来源有两个： o组内差异（随机误差）：可能是由于 一些误差造成的差异。 n各组均值与组内变量的偏差平方和 的总和。 o组间差异（处理条件）：不同的处理 方法造成的差异。 n各组均值与总均值偏差的平方和。 8.7.1 单因素方差分析 28 o单因素方差分析的作用是通过对某 一因素的不同水平进行多次观测，然 后统计分析判断该因素的不同水平对 考察指标的影响是否相同。 o检验几个等方差正态总体的等均值 假设。 o单因素方差分析的基本假设是各组 的均值相等。 8.7.1 单因素方差分析 29 n工具数据分析方差分析单因素方差分 析 o考察不同的销 售渠道对总销售 额的贡献是否相 同 30 运行结果 31 运算结果说明 o概要：样本数、合计、均值和方差。 o方差分析： nSS：离差平方和；df：自由度； nMS：均方差；F：F统计量； nP-value（概率）是原假设成立的概 率(越接近0，说明原假设成立的可能 性越小，反之原假设成立的可能性越 大) nF crit(F临界值):拒绝域的临界值。 32 o分析组内和组间离差平方和在总离差平方和 中所占的比重，可以直观地看出各组数据对总 体离差的贡献。将F统计量的值与临界值比较 ，可以判定是否接受等均值的假设。其中F临 界值是用FINV函数计算出来的。 o本例中F统计值是0.848783，远远小于F临 界值3.098393。所以，接受等均值假设。即 认为四种渠道的总体水平没有明显差距。从显 著性分析上也可以看出，概率高达0.48，远远 大于0.05。 结论 33 8.7.2 无重复双因素方差分析 o考察两个因素的不同水平对指标的 影响是否相同。 o实际上是检验几组等方差正态总体 下的均值假设。 o基本假设分别是各行和各列的均值 相等。 34 o例如考 察不同的 广告媒体 和费用对 总销售额 的影响。 n工具数据分析方差分析无重复双因素 分析 35 运行结果 36 结论 o行间、列间和误差的离差平方和水平接近。 o行间F统计值是3.427708081，略小于F临界 值3.86254。显著性分析的概率值0.06583也 大于0.05，所以接受行间等均值假设。即认为 不同广告媒体对销售业绩的影响无明显区别。不 过当置信度稍稍降低时，F统计量将大于F临界值 ，所以建议对不同媒体做进一步研究分析。 o列间F统计值是30.004038，远大于F临界值 3.86254。显著性分析的概率值只有0.000051 ，所以拒绝列间等均值假设。认为不同的广告投 放力度对销售有明显的影响。 37 8.7.3 可重复双因素方差分析 o使两个有协同作用的因素同时作用于考 察对象，并重复试验，然后通过统计分析 判断不同的因素组合在多次试验中对指标 的影响是否相同。 o仍然是在检验几组等方差正态总体下的 均值假设。 o基本假设是三个，分别是各行、各列和 各行列（可以假设是各“平面”）的均值相 等。 38 考察不同的CPU和不同的主 板搭配是否有不同的效果， 在保证其他配置相同的条件 下，三种CPU和四种主板搭 配后各自进行三次试验，分 别测量整机的综合测试指标 T-Mark。要求用可重复双 因素方差分析研究不同的 CPU、主板以及两者的组合 对整机性能的影响。 n工具数据分析方差分析单因素方差分 析 39 运行结果 40 结论 o行间F统计值是12.14336，远大于F临界值 3.402832。概率值为0.000227也很小。拒 绝行间等均值假设，认为不同的CPU对整机的 性能有明显影响。 o列间F统计值是2.507735，略小于F临界值 3.008786。概率值为0.08301也较大。接受 列间等均值假设，认为不同的主板对整机的性 能无明显影响。 o交互的F统计值是1.980174，小于F临界值 2.508187，概率值0.10847更大，接受交互 等均值假设。认为不同的CPU和主板的搭配对 整机性能无明显影响。 41 o在总体方差已知的条件下，检验两个 样本均值是否有差异。 8.8 Z-检验 42 例某企业对 采用两种方法组装新产品所需的时间 （分钟） 进行测试 ，随机抽取6个工人，让他们分别采用两种方法组 装同一种产品。假设组 装的时间 服从正态分布，以=0.05 的显著性水平比较两种组装方法是否有差