Excel 2016数据处理与分析 第10章 模拟分析与规划求解
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第10章 模拟分析与规划求解
主要内容
10.1 单变量求解 10.2 模拟运算表 10.3 方案管理器 10.5 规划求解
10.1 单变量求解
单变量求解是对函数公式的逆运算，主要解决假定 一个公式要取得某一结果值，公式中的某个变量应 取值为多少的问题。
【例10-1】简单函数Y=2X+10的单变量求解。
【例10-4】单变量模拟运算表 操作步骤
② 选定整个模拟运算表区域 （即D1:E13），单击“数据” 选项卡“预测”选项组中的 “模拟分析”按钮，选择 “模拟运算表”命令，弹出 “模拟运算表”对话框。
③ 在该对话框的“输入引用 列的单元格”框中输入$B$4。
【例10-4】单变量模拟运算表
计算结果
【例10-2】贷款问题的单变量求解。
求解结果：
【例10-3】年终奖金目标的单变量求解。
某公司员工的年终奖金的计算方法为全年销售额的8%，李 小 明 前 3 个 季 度 的 销 售 额 已 经 知 道 了 ， 分 别 是 37554 元 、 19986元和29800元，他想知道第4季度的销售额为多少时， 才能保证年终奖金为10000元。
【例10-4】单变量模拟运算表
分 析 ： 在 B2 输 入 贷 款 金 额
1000万元；B3输入贷款年限
10 年 ； B4 输 入 年 利 率 4% ；ຫໍສະໝຸດ B5是每月等额还款额的计算
公
式
“ =PMT(B4/12,B3*12,B2) ” 。
计算结果为￥-101245.14。
使用单变量模拟运算表可以 很直观地以表格的形式，将 偿还贷款的能力与利率变化 的关系列出来。
例如，不同的年化收益率对理财产品收益的影响， 不同的贷款年利率对还款额度的影响等。
【例10-4】某公司计划贷款1000万元，年限为 10年，采取每月等额偿还本息的方法归还贷款本 金并支付利息，目前的年利率为4％，每月的偿 还额为101245.14元。但根据宏观经济的发展情 况，国家会通过调整利率对经济发展进行宏观调 控。投资人为了更好地进行决策，需要全面了解 利率变动对偿贷能力的影响。
【例10-5】基于例10-4，除了考虑年利率的变化， 还需要同时分析不同贷款年限对偿还额的影响。
① 选择模拟运算表存放区域。本例选择A7:M13单元格区域， 其中B7:M7单元格区域列出年利率的所有取值，分别为3.25 ％、3.50％、…、6.00％；A8:A13单元格区域列出贷款年 限的所有取值，分别为5、10、…、30。在A7单元格输入计 算每月偿还额的计算公式“=PMT(B4/12,B3*12,B2)” 。
分析：在B2输入x的值5；B3输入函数的截距10；B4中是y 值的计算公式“=2*B2+B3”，计算结果为20。如果想让B4 （y）为某个特定值，那么B2（x）值应该是多少呢？即如 何根据y值求出x的值呢？这是典型的逆运算问题。
【例10-1】简单函数Y=2X+10的单变量求解。
假设y的目标值为100，通过单变量求解出x值： ① 单击“数据”选项卡“预测”选项组中的“模拟分析”按 钮，选择“单变量求解”命令，弹出“单变量求解”对话框。 ② 在对话框中将目标单元格设置为$B$4，目标值设置为100， 可变单元格设置为$B$2。
【例10-3】年终奖金目标的单变量求解。
分析：在B2:B4区域输入前3个季度的销售额；第4季度的 销 售 额 B5 未 知 ； B6 输 入 年 终 奖 金 的 计 算 公 式 “=(B2+B3+B4+B5)*8%”，当前的年终奖金为6987元。可 以确定第4季度的销售额B5单元格是可变单元格，年终奖金 B6是目标单元格，目标值是10000。
【例10-1】简单函数Y=2X+10的单变量求解。
假设y的目标值为100，通过单变量求解出x值： ① 单击“数据”选项卡“预测”选项组中的“模拟分析”按 钮，选择“单变量求解”命令，弹出“单变量求解”对话框。 ② 在对话框中将目标单元格设置为$B$4，目标值设置为100， 可变单元格设置为$B$2。 ③ 单击“确定”按钮，执行单变量求解。最终得出使目标单 元格（B4）等于目标值100时，可变单元格（B2）的值为45。
【例10-3】年终奖金目标的单变量求解。
求解结果：
10.2 模拟运算表
10.2.1 单变量模拟运算表
分析一个变量值的变化对公式运算结果的影响。
10.2.2 双变量模拟运算表
分析两个变量值同时变化对公式运算结果的影响。
10.2.1 单变量模拟运算表
当需要分析单个决策变量变化对某个计算公式的影 响时，可以使用单变量模拟运算表实现。
【例10-2】贷款问题的单变量求解。
某人买房计划贷款100万元，年限为10年，采取每月等额偿 还本息的方法归还贷款本金并支付利息，按目前银行初步提 出的年利率5.5%的方案，利用财务函数PMT可以计算出每 月需支付￥-10852.63。但目前每月可用于还贷的资金只有 8000元。因此要确定在年利率和贷款年限不变的条件下，可 以申请贷款的最大额度。
【例10-4】单变量模拟运算表 操作步骤
① 选择模拟运算表存放区 域，本例选择D1:E13单元格 区域。其中D2:D13单元格区 域列出了利率的所有取值， 本 例 为 3.25 ％ 、 3.50 ％ 、 … 、 6.00％。并在E1单元格输入 每月偿还额的计算公式 “ =PMT(B4/12,B3*12,B2) ”。
10.2.2 双变量模拟运算表
单变量模拟运算表只能解决一个变量值变化对公式 计算结果的影响，如果想查看两个变量值变化对公 式计算结果的影响就需要用到双变量模拟运算表。
【例10-5】基于例10-4，除了考虑年利率的变 化，还需要同时分析不同贷款年限对偿还额的影 响。
分析：这里涉及到两个变量，需要使用双变量模拟运算表 进行计算。
【例10-2】贷款问题的单变量求解。
分析：在B2输入贷款金额100万元；B3输入贷款年限10年； B4 输 入 年 利 率 5.5% ； B5 是 每 月 等 额 还 款 额 的 计 算 公 式 “=PMT(B4/12,B3*12,B2)”，计算结果为￥-10852.63。可 以确定贷款金额B2是可变单元格，每月等额还款额B5是目 标单元格，目标值是-8000。
主要内容
10.1 单变量求解 10.2 模拟运算表 10.3 方案管理器 10.5 规划求解
10.1 单变量求解
单变量求解是对函数公式的逆运算，主要解决假定 一个公式要取得某一结果值，公式中的某个变量应 取值为多少的问题。
【例10-1】简单函数Y=2X+10的单变量求解。
【例10-4】单变量模拟运算表 操作步骤
② 选定整个模拟运算表区域 （即D1:E13），单击“数据” 选项卡“预测”选项组中的 “模拟分析”按钮，选择 “模拟运算表”命令，弹出 “模拟运算表”对话框。
③ 在该对话框的“输入引用 列的单元格”框中输入$B$4。
【例10-4】单变量模拟运算表
计算结果
【例10-2】贷款问题的单变量求解。
求解结果：
【例10-3】年终奖金目标的单变量求解。
某公司员工的年终奖金的计算方法为全年销售额的8%，李 小 明 前 3 个 季 度 的 销 售 额 已 经 知 道 了 ， 分 别 是 37554 元 、 19986元和29800元，他想知道第4季度的销售额为多少时， 才能保证年终奖金为10000元。
【例10-4】单变量模拟运算表
分 析 ： 在 B2 输 入 贷 款 金 额
1000万元；B3输入贷款年限
10 年 ； B4 输 入 年 利 率 4% ；ຫໍສະໝຸດ B5是每月等额还款额的计算
公
式
“ =PMT(B4/12,B3*12,B2) ” 。
计算结果为￥-101245.14。
使用单变量模拟运算表可以 很直观地以表格的形式，将 偿还贷款的能力与利率变化 的关系列出来。
例如，不同的年化收益率对理财产品收益的影响， 不同的贷款年利率对还款额度的影响等。
【例10-4】某公司计划贷款1000万元，年限为 10年，采取每月等额偿还本息的方法归还贷款本 金并支付利息，目前的年利率为4％，每月的偿 还额为101245.14元。但根据宏观经济的发展情 况，国家会通过调整利率对经济发展进行宏观调 控。投资人为了更好地进行决策，需要全面了解 利率变动对偿贷能力的影响。
【例10-5】基于例10-4，除了考虑年利率的变化， 还需要同时分析不同贷款年限对偿还额的影响。
① 选择模拟运算表存放区域。本例选择A7:M13单元格区域， 其中B7:M7单元格区域列出年利率的所有取值，分别为3.25 ％、3.50％、…、6.00％；A8:A13单元格区域列出贷款年 限的所有取值，分别为5、10、…、30。在A7单元格输入计 算每月偿还额的计算公式“=PMT(B4/12,B3*12,B2)” 。
分析：在B2输入x的值5；B3输入函数的截距10；B4中是y 值的计算公式“=2*B2+B3”，计算结果为20。如果想让B4 （y）为某个特定值，那么B2（x）值应该是多少呢？即如 何根据y值求出x的值呢？这是典型的逆运算问题。
【例10-1】简单函数Y=2X+10的单变量求解。
假设y的目标值为100，通过单变量求解出x值： ① 单击“数据”选项卡“预测”选项组中的“模拟分析”按 钮，选择“单变量求解”命令，弹出“单变量求解”对话框。 ② 在对话框中将目标单元格设置为$B$4，目标值设置为100， 可变单元格设置为$B$2。
【例10-3】年终奖金目标的单变量求解。
分析：在B2:B4区域输入前3个季度的销售额；第4季度的 销 售 额 B5 未 知 ； B6 输 入 年 终 奖 金 的 计 算 公 式 “=(B2+B3+B4+B5)*8%”，当前的年终奖金为6987元。可 以确定第4季度的销售额B5单元格是可变单元格，年终奖金 B6是目标单元格，目标值是10000。
【例10-1】简单函数Y=2X+10的单变量求解。
假设y的目标值为100，通过单变量求解出x值： ① 单击“数据”选项卡“预测”选项组中的“模拟分析”按 钮，选择“单变量求解”命令，弹出“单变量求解”对话框。 ② 在对话框中将目标单元格设置为$B$4，目标值设置为100， 可变单元格设置为$B$2。 ③ 单击“确定”按钮，执行单变量求解。最终得出使目标单 元格（B4）等于目标值100时，可变单元格（B2）的值为45。
【例10-3】年终奖金目标的单变量求解。
求解结果：
10.2 模拟运算表
10.2.1 单变量模拟运算表
分析一个变量值的变化对公式运算结果的影响。
10.2.2 双变量模拟运算表
分析两个变量值同时变化对公式运算结果的影响。
10.2.1 单变量模拟运算表
当需要分析单个决策变量变化对某个计算公式的影 响时，可以使用单变量模拟运算表实现。
【例10-2】贷款问题的单变量求解。
某人买房计划贷款100万元，年限为10年，采取每月等额偿 还本息的方法归还贷款本金并支付利息，按目前银行初步提 出的年利率5.5%的方案，利用财务函数PMT可以计算出每 月需支付￥-10852.63。但目前每月可用于还贷的资金只有 8000元。因此要确定在年利率和贷款年限不变的条件下，可 以申请贷款的最大额度。
【例10-4】单变量模拟运算表 操作步骤
① 选择模拟运算表存放区 域，本例选择D1:E13单元格 区域。其中D2:D13单元格区 域列出了利率的所有取值， 本 例 为 3.25 ％ 、 3.50 ％ 、 … 、 6.00％。并在E1单元格输入 每月偿还额的计算公式 “ =PMT(B4/12,B3*12,B2) ”。
10.2.2 双变量模拟运算表
单变量模拟运算表只能解决一个变量值变化对公式 计算结果的影响，如果想查看两个变量值变化对公 式计算结果的影响就需要用到双变量模拟运算表。
【例10-5】基于例10-4，除了考虑年利率的变 化，还需要同时分析不同贷款年限对偿还额的影 响。
分析：这里涉及到两个变量，需要使用双变量模拟运算表 进行计算。
【例10-2】贷款问题的单变量求解。
分析：在B2输入贷款金额100万元；B3输入贷款年限10年； B4 输 入 年 利 率 5.5% ； B5 是 每 月 等 额 还 款 额 的 计 算 公 式 “=PMT(B4/12,B3*12,B2)”，计算结果为￥-10852.63。可 以确定贷款金额B2是可变单元格，每月等额还款额B5是目 标单元格，目标值是-8000。