Excel 2016数据处理与分析应用教程 课件 第10章 模拟分析

第10章模拟分析主要内容10.1单变量求解10.2模拟运算表10.3方案管理器10.1单变量求解单变量求解是对函数公式的逆运算，主要解决假定一个公式要取得某一结果值，公式中的某个变量应取值为多少的问题。10.1单变量求解【例10-1】简单函数y=2x+10的单变量求解。分析：在B2输入x的值5；B3输入函数的截距10；B4中是y值的计算公式“=2*B2+B3”，计算结果为20。如果想让B4（y）为某个特定值，那么B2（x）值应该是多少呢？即如何根据y值求出x的值呢？这是典型的逆运算问题。10.1单变量求解【例10-1】简单函数y=2x+10的单变量求解。假设y的目标值为100，通过单变量求解出x值：①单击“数据”选项卡“预测”选项组中的“模拟分析”按钮，选择“单变量求解”命令，弹出“单变量求解”对话框。②将目标单元格设置为$B$4，目标值设置为100，可变单元格设置为$B$2。10.1单变量求解【例10-1】简单函数y=2x+10的单变量求解。求解结果：10.1单变量求解【例10-2】贷款问题的单变量求解。某人计划贷款100万元，10年，采取每月等额偿还本息的方法，按年利率5.5%的方案，利用财务函数PMT可以计算出每月需还款额。但目前每月可用于还贷的资金只有8000元，可申请贷款的最大额度是多少？分析：B5的计算公式“=PMT(B4/12,B3*12,B2)”

B2是可变单元格，B5是目标单元格，目标值是-8000。10.1单变量求解【例10-2】贷款问题的单变量求解。求解结果：在年利率和贷款年限不变的条件下，可以申请贷款的最大额度。10.2模拟运算表10.2.1单变量模拟运算表

分析一个变量值的变化对公式运算结果的影响。10.2.2双变量模拟运算表

分析两个变量值同时变化对公式运算结果的影响。10.2.1单变量模拟运算表当需要分析单个决策变量变化对某个计算公式的影响时，可以使用单变量模拟运算表实现。例如，不同的年化收益率对理财产品收益的影响，不同的贷款年利率对还款额度的影响等。10.2.1单变量模拟运算表【例10-3】某公司计划贷款1000万元，年限为10年，采取每月等额偿还本息的方法归还贷款本金并支付利息，目前的年利率为4%，每月的偿还额为101245.14元。但根据宏观经济的发展情况，国家会通过调整利率对经济发展进行宏观调控。投资人为了更好地进行决策，需要全面了解利率变动对偿贷能力的影响。10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。分析：B2贷款金额1000万元；B3贷款年限10年；B4年利率4%；B5的计算公式“=PMT(B4/12,B3*12,B2)”。使用单变量模拟运算表可以很直观地以表格的形式，将偿还贷款的能力与利率变化的关系列出来。10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。操作步骤①选择模拟运算表存放区域。本例选择D1:E13。其中D2:D13列出了利率的所有取值，E1是计算每月偿还额的公式“=PMT(B4/12,B3*12,B2)”10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。操作步骤①选择模拟运算表存放区域。本例选择D1:E13。其中D2:D13列出了利率的所有取值，E1是计算每月偿还额的公式“=PMT(B4/12,B3*12,B2)”10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。操作步骤②选定D1:E13，单击“数据”选项卡“预测”选项组中的“模拟分析”按钮，选择“模拟运算表”选项，弹出“模拟运算表”对话框。③在该对话框的“输入引用列的单元格”框中输入“$B$4”。10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。计算结果10.2.1单变量模拟运算表【例10-3】贷款问题的单变量模拟运算表。将利率值按行存放，A8中输入计算公式，$B$4为引用行的单元格，则计算结果：10.2.2双变量模拟运算表

单变量模拟运算表只能解决一个变量值变化对公式计算结果的影响，如果想查看两个变量值变化对公式计算结果的影响就需要用到双变量模拟运算表。10.2.2双变量模拟运算表【例10-4】基于例10-3，除了年利率的变化，还需要同时分析不同贷款年限对偿还额的影响。分析：这里涉及到年利率和贷款年限两个变量，需要使用双变量模拟运算表进行计算。10.2.2双变量模拟运算表【例10-4】基于例10-3，除了年利率的变化，还需要同时分析不同贷款年限对偿还额的影响。操作步骤①选择模拟运算表存放区域A7:M13。B7:M7列出年利率；A8:A13列出贷款年限。A7是每月偿还额的计算公式“=PMT(B4/12,B3*12,B2)”10.2.2双变量模拟运算表【例10-4】基于例10-3，除了年利率的变化，还需要同时分析不同贷款年限对偿还额的影响。操作步骤②选定整个模拟运算表区域A7:M13，单击“数据”选项卡“预测”选项组……，打开“模拟运算表”对话框。③“输入引用行的单元格”为“$B$4”，“输入引用列的单元格”为“$B$3”，即行变量是年利率，列变量是贷款年限。10.2.2双变量模拟运算表【例10-4】基于例10-3，除了年利率的变化，还需要同时分析不同贷款年限对偿还额的影响。计算结果10.3方案管理器如果要解决包括更多可变因素的问题，或是要在多种假设分析中找出最佳执行方案，单变量模拟运算表和双变量模拟运算表就无法实现了。方案管理器主要用于解决多方案求解问题，寻求最佳的解决方案。10.3方案管理器【例10-5】基于例10-4双变量模拟运算表中的贷款问题，要求同时分析不同贷款年利率、贷款年限和贷款金额对每月偿还额的影响。分析：这里涉及到年利率、贷款年限和贷款金额共三个变量，即变量超过了两个，使用方案管理器。方案管理器是在双变量模拟运算表的基础上创建的。10.3方案管理器【例10-5】方案管理器。操作步骤①单击“数据”选项卡“预测”选项组中的“模拟分析”按钮，选择“方案管理器”选项，弹出“方案管理器”对话框。②在“方案管理器”对话框中单击“添加”按钮，弹出“添加方案”对话框。10.3方案管理器【例10-5】方案管理器。操作步骤③在“添加方案”对话框的“方案名”中输入“贷款金额-800”，然后指定“贷款金额”所在的B2单元格为可变单元格。④单击“确定”按钮。出现“方案变量值”对话框，将文本框中显示的可变单元格原始数据修改为方案模拟数值800万元。10.3方案管理器【例10-5】方案管理器。操作步骤⑤“贷款金额-800”方案创建完毕后，相应的方案自动添加到“方案管理器”的方案列表中。⑥同样的方法，建立“贷款金额-900”“贷款金额-1000”“贷款金额-1100”和“贷款金额-1200”等方案。10.3方案管理器【例10-5】方案管理器。查看方案“贷款金额-800”选中方案，单击“显示”。10.3方案管理器【例10-5】方案管理器。查看方案“贷款金额-1100”10.3