Excel数据分析工具及应用52.ppt

EXCEL基础及应用 第5章Excel数据分析工具及应用 数据分析工具的安装与卸载 模拟运算表 方案分析 1 2 3 4 规划求解 5 2模拟运算表 什么是模拟运算表 Excel模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算 测试使用一个或两个变量的公式中变量对运算结果的影响 模拟运算表的类型 基于一个输入变量的表 用这个输入变量测试它对多个公式的影响 单变量模拟运算表 基于两个输入变量的表 用这两个变量测试它们对于单个公式的影响 双变量模拟运算表 5 2 1单变量模拟运算表 例5 1 某人想买房 由于资金不足需要向银行贷款30万 还款年限为10年 目前年利率假设为4 采取每月等额还款的方式 请帮助该买房人计算在现有利率下每月的偿还额 根据国家经济的发展 银行利率也会有调整 要了解利率变动对自身的偿还能力的影响 请帮助该买房人计算不同利率条件下的每月偿还额 由于买房人经济收入并不是很稳定 需要根据实际情况调整还款期限 请帮助该买房人计算出还款期限是5年 10年 15年 20年 25年和30年时 每月等额还款的数额 单变量模拟运算表主要是用来分析当其他因素不变时 一个参数的变化对目标结果的影响 问题 求解 可利用PMT函数计算 1 使用 文件 新建 或 新建 工具 建立一新工作簿 并选择一张工作表 将住房贷款有关的基本数据输入该工作表 如图5 5 B4 D8 单元格区域所示 2 在D9单元格中输入公式 PMT D5 12 D8 D4 按下 Enter 键 计算出所要的结果 上述公式中三个参数的含义是 第一个参数是利率 由于偿还额按月计算 所以年利率除以12 将其转换为月利率 第二个参数是还款期 10年共120各月 第三个参数是贷款金额 图5 5住房贷款还款的Excel分析图 问题 求解 可利用模拟运算表计算不同利率下的月偿还额 输入贷款期各种可能的不同利率数据 如图5 6 F4 F16 单元格区域所示 图5 6住房贷款还款的Excel模拟运算表分析图 选择包含公式和需要进行模拟运算目标单元区域 F3 G16 如图5 7所示 图5 7住房贷款还款的Excel模拟运算表分析图 单击 数据 模拟运算表 命令 弹出如图5 8所示的对话框 在 输入引用列的单元格 中输入 D 5 再单击 确定 按钮 各分析值自动填入分析表中 如图5 9 G4 G16 单元格区域所示 图5 8住房贷款还款的Excel模拟运算表分析图 图5 9模拟运算表工具使用示意图 5 2 2双变量模拟运算表 对于问题 我们不仅要考虑贷款年利率的变化 而且还要考虑还款年限 这时需要分析不同的利率和不同的贷款期限对贷款的偿还额的影响 这时需要使用双变量模拟运算表 问题 求解 选择某个单元格区域作为模拟运算表存放区域 在该区域的最左列输入假设的利率变化范围数据 在该区域的第一行输入可能的贷款年限数据 如图5 10 B11 G11 单元格区域所示 图5 10模拟运算表工具使用示意图 在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式 选定整个模拟运算表区域 如图5 11 A11 G24 单元格区域所示 图5 11模拟运算表工具使用示意图 单击数据菜单中的模拟运算表命令 在模拟运算表对话框 如图5 12 的输入引用行的单元格框中输入 C 6 年份 在输入引用列的单元格框中输入 C 5 年利率 单击 确定 双变量模拟运算表的计算结果如图5 13所示 其中B12 G24单元格区域的计算公式为 表 C6 C5 表示其是一个以C6为行变量 C5为列变量的模拟运算表 5 2 3单变量求解 什么是单变量求解 单变量求解就是求解只有一个变量的方程的根 方程可以是线性方程 也可以是非线性方程 例5 2 某公司想向银行贷款900万元人民币 贷款利率是8 贷款限期为5年 如果公司每年可偿还100万元 该公司最多可贷款多少金额 1 在工作表中输入原始数据 如图5 15 图5 15贷款总额计算 2 建立可变数公式 在单元格B2