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

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