第9章 复杂数据表分析

1、大学计算机高级应用（上大学计算机高级应用（上卷卷）-Excel 2010 高级应用9.19.1 制作企业贷款分析表制作企业贷款分析表9.29.2 案例分析案例分析9.39.3 案例实施案例实施 9.49.4 拓展练习拓展练习第九章第九章 复杂数据表分析复杂数据表分析2 假设在贷款年数（8）和贷款金额（10000000元）不变的情况下，分析：（1）如果月还款额只能是180000元，那么年利率是多少才能行贷？（2）在不同年利率时，每月的还款额是多少？（3）该企业向银行贷款10000000元，计算出贷款总额不变的情况下， 不同年利率与不同贷款期限时，每月的还款额是多少？。39.19.1 制作企业贷款

2、分析表制作企业贷款分析表利用“单变量求解”计算年利率效果图：9.19.1 制作企业贷款分析表制作企业贷款分析表单变量模拟运算结果（列引用）：9.19.1 制作企业贷款分析表制作企业贷款分析表双变量模拟运算结果：9.19.1 制作企业贷款分析表制作企业贷款分析表知识点：单变量求解；单变量模拟运算表；双变量模拟运算表。79.29.2 案例分析案例分析89.39.3 案例实施案例实施9.3.1 单变量求解9.3.2 单变量模拟运算表9.3.3 双变量模拟运算表知识点：1.假设分析工具2.依赖公式3.输入值未知4.一元求解计算99.3.1 9.3.1 单变量求解单变量求解9.3.1 9.3.1 单变量

3、求解单变量求解建立“贷款计算表”9.3.1 9.3.1 单变量求解单变量求解数据选项卡数据工具中的模拟分析选项卡，打开“单变量求解”，并输入如图数据。9.3.1 9.3.1 单变量求解单变量求解确定，弹出“单变量求解状态”。确定按钮，关闭“单变量求解状态”。知识点：1.建立计算模型2.建立模拟运算表区域3.选定模拟运算表区域4.模拟运算139.3.29.3.2 单变量模拟运算表单变量模拟运算表1.建立计算模型2.建立模拟运算表区域149.3.29.3.2 单变量模拟运算表单变量模拟运算表9.3.29.3.2 单变量模拟运算表单变量模拟运算表3.选定模拟运算表区域 列引用时，模拟运算表区域为A5

4、:B14，公式单元格B5和计算结果显示区域B6:B14。4.模拟运算 数据工具选项组中，单击模拟分析，单击模拟运算表命令 弹出模拟运算表对话框。小贴士：小贴士： 在已有的模拟运算表中，可以再添加多个模拟运算公式进行运算，得到相应的模拟运算结果。列引用时，新公式添加的位置在原公式右边同行的单元格中；行引用时，新公式添加的位置在原公式下面同列的单元格中。9.3.9.3.3 3 双变量模拟运算表双变量模拟运算表 单变量模拟运算表是一个变量的不同值对一个或多个公式计算结果的影响，双变量模拟运算表则是一个公式中的两个变量取不同值时对该公式计算结果的影响。知识点：1.建立计算模型2.建立模拟运算表区域3.

5、选定模拟运算表区域9.3.9.3.3 3 双变量模拟运算表双变量模拟运算表1.建立计算模型2.建立模拟运算表区域9.3.9.3.3 3 双变量模拟运算表双变量模拟运算表3.选定模拟运算表区域 双变量模拟运算表的区域包括公式和两组变量值，此例为A5:E12。4.模拟运算 在模拟分析选项卡，选择模拟运算表命令，打开“模拟运算表”对话框，在“输入引用行的单元格”输入地址B2，在“输入引用列的单元格”输入地址B3。20 9.49.4 拓展练习拓展练习上述操作中都运用列引用进行，请变换为行引用，重新完成以上操作。9.5 9.5 案例描述案例描述9.69.6 案例分析案例分析9.79.7 案例实施案例实施

6、9.89.8 拓展练习拓展练习21案例案例16 16 如何解决最优化问题如何解决最优化问题229.5 9.5 案例描述案例描述知识点：1.方案管理器2.规划求解239.69.6 案例分析案例分析知识点：知识点：建立计算模型建立方案显示方案建立方案报告249.7.19.7.1 方案管理器方案管理器9.7.19.7.1 方案管理器方案管理器1.建立计算模型9.7.19.7.1 方案管理器方案管理器2.建立方案 单击模拟分析命令中的方案管理器命令，弹出方案管理器对话框。9.7.19.7.1 方案管理器方案管理器 在方案管理器对话框中单击添加按钮，打开添加方案对话框，输入如图数据 9.7.19.7.1

7、 方案管理器方案管理器打开方案变量值对话框 在方案变量值可变单元格文本框中输入鼠标的成本和利润率，确认，返回方案管理器，如图所示9.7.19.7.1 方案管理器方案管理器 再次打开添加方案对话框；在添加方案对话框输入“硬盘”，“可变单元格”的内容不变，确认，又一次打开方案变量值对话框 此时，在方案管理器对话框的“方案”添加显示“硬盘”。重复以上步骤建立“风扇”方案。9.7.19.7.1 方案管理器方案管理器3.显示方案 设定了各种模拟方案后，在任何时候都可以执行方案，查看模拟的结果。具体操作如下： 在数据选项卡的数据工具选项组中，单击模拟分析命令，在弹出的下拉菜单中，单击方案管理器命令，则弹出

8、方案管理器对话框。在方案管理器对话框的“方案”列表框中，选定要显示的方案。加入选定“风扇”。 单击方案管理器对话框的显示按钮，则被选定方案中可变单元格的值出现在工作表的可变单元格中，同时工作表重新计算，以反映模拟结果。9.7.19.7.1 方案管理器方案管理器显示方案图示：9.7.19.7.1 方案管理器方案管理器4.建立方案报告方案管理器中，单击“摘要”，打开“方案摘要”对话框 在“报表类型”中选择“方案摘要”，在“结果单元格”中输入结果引用地址（C4，利润值），确定并在此工作表中创建了方案摘要报告知识点：知识点：1.加载规划求解工具2.建立工作表3.应用规划求解4.规划求解结果339.7.

9、29.7.2 规划求解规划求解9.7.29.7.2 规划求解规划求解1.加载规划求解工具 打开【文件】中Excel选项对话框，打开加载项选项卡。在管理下选择Excel加载项，单击转到命令，弹出加载宏选择中勾选“规划求解加载项”复选项。9.7.29.7.2 规划求解规划求解加载规划求解加载宏后，在数据选项卡分析选项组中就出现规划求解的命令按钮9.7.29.7.2 规划求解规划求解2.建立工作表 在B7中，输入公式“=B5*B6”，再计算出“硬盘”和“风扇”的毛利合计值。在B17中，输入生产成本的公式“=SUMPRODUCT(B3:D3,B6:D6)”；在B18单元格中，输入“=SUMPRODUC

10、T(B4:D4,B6:D6)”；在B19单元格中，输入“=SUM(B7:D7)”。规划求解确定最优生产方案工作表:9.7.29.7.2 规划求解规划求解3.应用规划求解，求得利润最大化生产方案 选定目标单元格B19，在数据选项卡的分析选项组中，单击规划求解命令，弹出规划求解参数对话框。 设置目标函数。由于之前选定目标单元格，所以在规划求解参数对话框中的“设置目标单元格”文本框中显示B19，但如果之前没有选定，也可此时选定。此时默认最大值单选项，可根据需要选择最小值。 设置可变单元格，即决策变量。单击“可变单元格”文本框，在工作表中选择单元格区域B6:D6,设置可变单元格，见下图。9.7.29.7.2 规划求解规划求解如图所示：9.7.29.7.2 规划求解规划求解 单击“遵守约束”文本框右侧的添加按钮，单击“单元格应用位置”文本框，选择B17；选择“=”；单击“约束值”文本框，选择B12。单击添加按钮，继续添加其他约束。9.7.29.7.2 规划求解规划求解 添加实际生产时间约束条件，单击“单元格应用位置”文本框，选择B18；选择“=，单击“约束值”文本框，选择：B11:D11。添加，再次打开添加约束对话框。在弹出的对话框中设置三种产品的产量约束值为整数，确定产品产量约束设置：添加完成的约束条件列表：产量约束为整数：9.7.29.7.2 规划求解规划求