Excel实用教程 教学课件 作者 杨尚群 曹淑艳 EXCEL实用教程－第8章.pptVIP

第8章 数据分析 8.1 用假设方法求解 单变量求解 模拟运算表 方案管理 8.1 用假设方法求解 功能:对一个公式的计算结果指定一个期望值,从而观察公式引用的某个变量值的变化。 2.模拟运算表(“数据”菜单) (1)单变量模拟运算表 8.1 用假设方法求解 (2)双变量模拟运算表 8.1 用假设方法求解 3.方案管理(“工具”菜单) 功能：对多个假设条件(方案)进行模拟计算。 一个方案：是一组变量与一组对应的模拟值，观察公式的计算结果。 方案名称 可变单元格与模拟值（期望值） 结果单元格（公式） 举例 8.2 线性回归分析 在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的关系函数表达式（称回归方程式）。 当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析； Y=ax+b 当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析。 Y=a1x1+a2x2+a3x3+…+b 8.2 线性回归分析 [例1] 对销售额进行多元回归分析预测。 8.2 线性回归分析 设变量： Y=销售额 X1=电视广告费用 X2=报纸广告费用 方程为：Y=a1X1+a2X2+b 通过线性回归分析确定a1,a2,b的值，从而确定方程。 8.2 线性回归分析 操作： (1)“工具”菜单→加载宏→分析工具库 (2)“工具”→数据分析→回归 (3)设置:Y，X值输入区，输出区域 (4)根据结果得出方程 8.3 规划求解 “规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。 规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。 8.3 规划求解 意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果（例如：获得最大的利润、最小的成本等）。 注意事项： 在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。 添加规划求解命令: 工具→加载宏→规划求解 8.3 规划求解 求解方程组 求X,Y,Z 技巧：其中一个方程为目标方程，另外2个方程为约束条件 8.4 移动平均 移动平均法是根据时间序列数据，逐项推移计算移动平均，反映数据的长期趋势。 如：第t时间点的移动平均值Mi，可当作第t+1时间点的预测值。 基于特定的过去某段时期变量的均值，对未来值进行预测。 5、10、20、30日均线 8.4 移动平均 移动平均 简单移动平均:每个观测值用相同的权数 加权移动平均:每个观测值有不同的权数 进行加权平均预测时，通常是先对数据进行加权处理，然后再调用分析工具计算。 操作：工具→数据分析→移动平均 举例 8.5 指数平滑 指数平滑是在移动平均的基础上的进一步扩展。 指数平滑法是用过去时间数列值的加权平均数作为趋势值，越靠近当前时间的指标越具有参考价值，因此给予更大的权重，按照这种随时间指数衰减的规律对原始数据进行加权修匀。 8.6 相关分析 功能：研究(估计)变量之间相关的密切程度。 在实际问题中，许多变量之间的关系并不是完全确定性的。 统计关系：无法一一对应，收入与支出、广告与销售等 例如家庭消费与收入的关系不是完全确定的。 收入水平相同的家庭，消费额不同； 消费额相同的家庭，收入不同。 函数关系：一一对应，例如Y＝a+bX 线性相关分析:研究两个变量间线性相关程度。 计算相关系数r。 r是描述线性关系的程度和方向的统计量，无单位，在 +1～ -1之间。 正相关：相关系数r0。例如：随着X值的增大，Y也增大；X值的减小，Y也变小。 负相关：相关系数r0。例如：随着X值的增大，Y减小；X值的减小，Y增大。 r=0，表示无线性关系。 ｜r｜0.8,两个变量高度相关。 0.5｜r｜0.8,两个变量中度相关。 ｜r｜0.3,两个变量相关较弱。 用相关矩阵来描述变量的相关情况。 操作：工具→数据分析→相关系数 举例 8.7 方差分析 运用数理的方法对数据进行分析，以鉴别各种因素对研究对象某些特征值的影响大小和影响方式，这种方法叫做方差分析。 所关注的对象的特征称为指标，影响指标的各种原因叫做因素，在实验中因素的各种不同状态称为因素的水平。 8.7 方差分析 功能：检验3个或3个以上的独立样本的“均值”是否存在差异。 方差分析分为： 单因素方差分析 多因素无重复试验的方差分析 多因素重复试验方差分析 协方差