Excel数据分析教程-课件第3章模拟运算.pptxVIP

Excel数据分析教程;;学习目标：;本章知识点;所谓模拟运算表实际上是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数的值的变化对计算结果的影响。具体来说，模拟运算表是假设公式中的变量有一组替代值，代入公式取得一组结果值时使用的，该组结果值就构成一个模拟运算表。 不过，Excel一个模拟运算表一次只能处理1~2个输入单元格，因此，模拟运算表又分为单变量模拟运算表和双变量模拟运算表。;单变量模拟运算表：输入一个变量的不同替代值，并显示此变量对一个或多个公式的影响。下表所示为单变量模拟运算表的一般形式：;例3-1 计算一元方程式： 例如，一个简单的数学方程式 中含有变量x，只要代入x的值后，即可算出该方程式的答案。也就是说，如果x代入0则答案为-7，如果x代入1则答案为0。当有大量的数字要代入此方程式中并分别计算出不同的结果时，就可以使用模拟运算表。;例3-2 计算购房贷款： 例如，用户贷款10万购房，需要了解不同利率情况下每月的还贷金额，就需要使用模拟运算表，不过得先熟悉一下PMT函数，该函数主要是根据固定利率、定期付款和贷款金额，求出每期应偿还的贷款金额。PMT函数格式如下： 其中： Rate：每期的利息 Nper：付款期数 Pv：贷款金额 Fv：未来终值，默认值为0，一般银行贷款此值为0 Type：缺省为0，表明期末付款，如为1，则表明期初付款;例3-3 计算公司贷款： 假设某公司要贷款1000万元，年限为10年，目前的年利率为5％，分月偿还。则利用PMT函数可以计算出每月的偿还额。 PMT函数除了用于贷款分析之外，还可以计算出别的以年金方式付款的支付额。例如，如果需要以按月定额存款方式在20年中存款100000，假设存款年利率为4%，则函数 PMT 可以用来计算月存款额：“＝PMT（4% / 12，20*12，0，100000）”，公式计算结果为“272.65”。即向年利率4%的存款账户每月存入272.65元，20年后连本带利可获得100000元。;双变量模拟运算表：当需要其它因素不变时，两个参数的变化对目标值的影响时，需要使用双变量模拟运算表，其排列方式如下表所示：;例3-4 二元方程： 例如，利用双变量模拟运算表??解数学方程式。设有二元函数如下：;例3-5 公司贷款——利率加年限： 又如例3-3中，如果不仅要考虑利率的变化，还可以选择贷款年限，这时需要分析不同的利率和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变量模拟运算表。;例3-6 购房贷款——利率加额度： 又如例3-2中，利用双变量模拟运算表求解不同贷款与利率的偿还额。如果要同时考虑利率变化情况和贷款额度的不同对月偿还额的影响，可使用又变量模拟运算表求解。;对于不再需要的运算结果，可以将它们从工作表中清除。由于运算结果是在数组中，所以我们不能清除单个值，而必须清除所有的值。 只能选中整个表，包括输入替代值的行、列和计算公式所在的单元格，然后在“开始 | 编辑”组中选择“清除”命令下的“全部清除”。当然，也可以只选择结果区域执行这个命令，但必须选中所有结果区域，因为模块运算的结果是存放在数组中的。;模拟运算表主要用来考查一个或两个决策变量的变动对于分析结果的影响，但对于一些更复杂的问题，常常需要考查更多的因素，这就要用到方案了。 方案是用于预测工作表模型结果的一组数值，是一组称为可变单元格的输入值，并按照用户指定的名字保存起来。每个可变单元格的集合代表一组模拟分析的前提，用户可以在工作表中创建并保存多组不同的数值，并在这些方案之间任意切换，从而查看不同的方案结果。 我们可以为每个方案定义多达32个可变单元格，也就是说对一个模型我们可以使用多达32个变量来进行模拟分析。;例3-7 公司损益方案 如图所示的是公司某时点的损益表，其中包括了各项指标的计算公式。管理人员希望分析，通过增加销售收入，减少生产费用，降低销售成本等措施对公司利润总额的影响。这可以利用Excel 2016的方案工具进行分析，主要包括下述操作。;例3-8 购房贷款时多家银行的选择 银行1：允许贷款20万，年利率7.5%，贷款年限最长15年； 银行2：允许贷款25万，年利率8%，贷款年限最长18年； 银行3：允许贷款30万，年利率8.5%，贷款年限最长20年。 要求出各自的结果，然后根据自己目前的工资收入来决定选择哪家银行。;例3-9 求解最佳的销售方案 “新天地蛋糕专卖店”打算推出 “青豆派”，请求解最佳的销售方案： 方案A：一个月预计销售900个，单价80元，需要两位糕点师，月支付工资共4800元； 方案B：一个月预计销售700个，单价60元，需要一位糕点师，月支付工资共2600元； 方案C：一个月预计销售800个，单价55元，需要一