Excel的数据分析.pptxVIP

Excel 的数据分析 模拟运算表（数据表） 单变量求解 规划求解与分析工具库 方案分析 §1. 模拟运算表 模拟运算表是一个单元格区域，显示公式中的某些参数值的变化将如何影响公式结果。 模拟运算表有两种，单变量模拟运算表和双变量模拟运算表。 (1) 单变量模拟运算表 单变量模拟运算表查看一个变量的不同值对一个或多个公式结果的影响。 输入值需排在一行或一列。 可变单元格：公式中输入值要替换的单元格。 行引用和列引用 模拟运算表中，输入值要排在一行或一列。如果输入值排在同一列，则在“输入引用列的单元格”框中，键入可变单元格。 如果输入值排在同一行上，则在“输入引用行的单元格”框中，键入可变单元格。 “单变量模拟运算表”中公式位置 当输入值排成一列时，在第一个数值的上一行且处于数值列右侧的单元格中键入所需公式。（可以在公式右边的单元格中键入其它公式） 当输入值排成一行时，在第一个数值左边一列且数值行下方的单元格内键入所需公式。（可以为一个以上的公式） 模拟运算表计算结果 模拟运算表的计算结果存放在数组中，不能单独修改其中的某一个值。要清除计算结果，选中模拟运算表中所有结果，按Delete键即可。 单变量模拟运算表操作步骤： 根据输入值的方向，选择公式的所在位置，输入公式； “数据”|“数据工具”|“假设分析”|“模拟运算表”，在“模拟运算表”对话框中键入引用的单元格； 实例1：单变量模拟运算表 例：计算商场出售商品的获利情况。公式为毛利=进货成本*加价百分比*销售数量-销售费用 操作步骤： 参数写在同一列A10:A14上，因此选择B9单元格输入公式：=B1*B2*B3-B4； 选择A9:B14区域，在“模拟运算表”对话框中，选择 “输入引用列的单元格”，单击B2单元格，则产生结果，此结果区域为数组； 实例2：单变量模拟运算表 “考勤应扣款计算表”中用模拟运算表计算每个部门的基本工资、扣款合计的总额。 操作步骤： 设A19单元格为可变单元格，输入任何一个部门的值； 所有参数值写在C20:C23区域，在D19、E19单元格输入公式：SUMIF(C3:C16,A19,E3:E16)和SUMIF(C3:C16,A19,L3:L16) 操作步骤： 选择C19:E23区域，在“模拟运算表”对话框的“输入引用列的单元格”中输入“A19”，单击“确定”按钮； (2) 双变量模拟运算表 创建双变量模拟运算表的过程与创建单变量模拟运算表类似。 双变量中的两组输入值使用同一个公式，一组输入值在公式下方的同一列上，另外一组输入值在公式右侧的同一行上。 操作步骤： 在某个单元格内输入所需的引用两个输入单元格的公式。在公式下面同一列中键入一组输入值，在公式右边同一行中键入第二组输入数值； 选定公式及输入行和列的单元格区域； “数据”|“数据工具”|“假设分析”|“模拟运算表”，输入行引用的单元格以及输入列引用的单元格； 实例3：双变量模拟运算表 有人要申请等额本息贷款购买房子，其中利率固定，考虑几种可能的贷款年限和贷款金额下求每月还贷额，选择最佳方案。 操作步骤： B5单元格先输入公式，然后在B6：B10区域和C5:G5区域输入输入值； 选择B5:G10区域，在“模拟运算表”对话框中“引用行的单元格”里选择F2，“引用列的单元格”里选择C3； 实例4：双变量模拟运算表 在“考勤应扣款计算表”中用模拟运算表计算不同部门、不同职位的人数。 操作步骤： 在A19:B20区域，创建条件区域； 在D20:D23区域输入部门的所有参数值，在E19:H19区域输入职位的所有参数值； 在D19单元格输入公式：=DCOUNT(A2:L16, “基本工资,A19:B20); 操作步骤： 选择D19:H23区域，在“模拟运算表”对话框的“输入引用列的单元格”中输入 “B20”，“输入引用行的单元格”中输入“A20”，单击“确定”按钮； §2. 单变量求解 单变量求解是在已知结果的情况下推测出形成这个结果的参数。在Excel中根据所提供的目标值，不断改变公式中涉及的引用单元格的输入值，直到达到所需要求的公式的目标值。 公式所在的单元格为目标单元格，在求解过程中可修改其值的单元格为可变单元格，“单变量求解”中可变单元格为一个。 操作步骤： 选择目标单元格，“数据”|“数据工具”|“假设分析”|“单变量求解”，在“单变量求解”对话框中输入目标单元格、目标值以及可变单元格； 实例5：单变量求解 某人准备向银行贷款，贷款期限为30年，贷款利率为6%，每月能承受的还款额为5000元，那么能贷多少。 操作步骤 选择B5单元格，输入公式：=PMT(B4/12,B3,B2)； “数据”| “数据工具”|“假设分析”|“单变量求解”，目标单元格中选择B5，目标值-5000，可变单元格选择B2