excel第7单元高级数据管理.ppt

第七单元 高级数据管理 统计各班运动会成绩 掌握合并计算的使用方法 任务：对“运动会成绩表”进行统计，其中包括使用合并计算功能统计各班运动会成绩、对各班运动会成绩进行排序。  掌握数据透视表的使用方法 本单元主要内容 工作表表间汇总计算 分期付款函数PMT的应用 选择工作表：总公司 选择合并计算功能 数据－合并计算 设置内容 （进行合并算的工作表应具有相同结构） 选取合并计算函数 选取合并计算的数据区域 ▲创建连至源数据的链接，使计算机结果与源数据的变化进行同步 任务一、销售表的合并计算 任务二、购房贷款方案分析 分期付款函数PMT 功能：计算在指定的周期内分期偿还一笔贷款所需的定期支付额。 函数语法： PMT(rate,number of periods, present value,futre value ,type) rate:贷款利率 numer of periods:贷款总期限，即分期付款期限 present value:现值，也称本金，即贷款额。 future value:最后一次付款后得到的余额，如果 省略，值为0。 type: 支付日期，值为数字0或1。 0表示付款时间在期末，1表示付款时间在期 初，默认为0。 参数说明 A、贷款额固定，利率固定，计算月偿还额 例：贷款10万元，利率5.58%，问贷款期限 为1年（12个月）时的月偿还额 任务分析 完成任务A：计算月还款额 选定单元格，插入PMT函数 函数－财务函数－PMT 输入函数参数，计算结果 设计计算结果单元格格式 B、利用单变量模拟运算表计算利率不变时不同还款期限的月偿还额 即：贷款额固定，利率固定，计算不同贷款期限的月偿还额 例：贷款10万元，利率5.58%，问贷款期限为1年、2年、3年……的月偿还额。 完成任务B：使用“单变量模拟运算表”计算利率不变时不同还款期限的月偿还额 单变量模拟运算规则： 替换输入单元格的数据必须排列在一列 （列方向）或一行（行方向）中。 公式的位置是有限制的，如果替换值排成一列，公式应在第一个替换值所在单元格区域右上方单元格内；如果替换值排成一行，公式应在第一个替换值所在单元格区域左下方单元格内。 公式中必须引用输入单元格。 选择单元格区域； 选择模拟运算表功能： 数据－模拟运算表 输入所引用的列单元格 设计计算结果单元格格式 C、利率固定，不同贷款额，不同贷款期限 的月偿还额 例：贷款利率为6.03％，计算贷款额分别为 12万，14万，16万，20万……，贷 款期限分别为10年，11年，12年…… 时的月偿还额。 双变量模拟运算表中公式的位置是有限的，要用到的公式必须放在替换行替换列交叉的单元格中。 例：贷款利率为6.03％，计算贷款额分别为 12万，14万，16万，20万……，贷 款期限分别为10年，11年，12年…… 时的月偿还额。 完成任务C：用“双变量模拟运算表”计算 贷款总额与贷款期限改变时的月还款额。 选择单元格区域 选择“模拟运算表”功能 数据－模拟运算表 输入行引用单元格，列引用单元格 设计计算结果单元格格式 D、单变量求解贷款期限 即：给定月偿还额，利率，贷款额度 求贷款的偿还期限 例：如贷款额10万元，每月还款1200元，年利率6.03％，求还款期限多长。 选定公式－工具－单变量求解 （公式的逆向求解过程） 目标单元格：存放公式计算结果的单元格 目标值：公式的假定计算结果 可变单元格：公式中贷款期限所在单元格 完成任务D：使用“单变量求解”工具计算月偿还 额固定，利率固定时固定贷款额度的偿还期限。 E、贷款期限固定，月还款额固定，利率固定，计算可贷款的总额度 例：确定贷款20年，月还款1200元，年利率 6.03%，计算可以从银行贷款多少钱。 选定公式－工具－单变量求解