Excel模的拟运算表.ppt

EXCEL基础及应用 湖北生物科技职业学院 刘英 1 数据分析工具的安装与卸载 1.1 安装分析工具库 “分析工具库”是一个加载宏文件，文件名为Analys32.xll（加载宏文件是可以自动执行一系列复杂任务的操作命令的组合）。但这一工作在安装Microsoft Office组件时已经加载。 安装步骤如下 打开Excel工作簿，选择菜单栏中【工具】菜单中的【加载宏】，如图5-1所示。 在打开“加载宏”对话框，对话框如图5-2所示，单击【分析工具库】复选框，框前即出现√号，表明已被选定，选择【确定】。 1.2 卸载分析工具库 当不需要使用分析工具库时，可以通过设置来卸载分析工具库。 操作步骤： 打开Excel工作簿，选择菜单栏中【工具】菜单中的【加载宏】。 在打开“加载宏”对话框，对话框如图4所示，单击【分析工具库】复选框，将框前出现√号取消。 选择取消后，单击【确定】按钮，即可自动卸载分析工具库。此时在“工具”菜单中的“数据分析”选项自动隐藏。 5.2 模拟运算表 什么是模拟运算表？ 是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。 模拟运算表的类型 ①基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；——单变量模拟运算表 ②基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响——双变量模拟运算表 2.1 单变量模拟运算表 【例5-1】某人想买房，向银行贷款30万，还款年限为10年，目前年利率假设为4%，采取每月等额还款的方式。 ① 请帮助该买房人计算在现有利率下每月的偿还额。 ② 请帮助该买房人计算不同利率条件下的每月偿还额。 ③ 请帮助该买房人计算出还款期限是5年、10年、15年、20年、25年和30年时，每月等额还款的数额。 单变量模拟运算表主要是用来分析当其他因素不变时，一个参数的变化对目标结果的影响。 问题①求解：可利用PMT函数计算。 (1) 建立一新工作簿，并选择一张工作表，将住房贷款有关的基本数据输入该工作表（B4:D8）单元格区域所示， (2) 在D9单元格中输入公式“=PMT(D5/12,D8,D4)”，按下【Enter】键，计算出所要的结果。 问题②求解：可利用模拟运算表计算不同利率下的月偿还额。 输入贷款期各种可能的不同利率数据，如图5-6（F4:F16）单元格区域所示。 选择包含公式和需要进行模拟运算目标单元区域（F3:G16），如图5-7所示。 单击【数据】→【模拟运算表】命令，弹出如图5-8所示的对话框。在“输入引用列的单元格”中输入$D$5，再单击【确定】按钮， 2.2 双变量模拟运算表 对于问题③分析不同的利率和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变量模拟运算表。 问题③求解： 选择某个单元格区域作为模拟运算表存放区域，在该区域的最左列输入假设的利率变化范围数据；在该区域的第一行输入可能的贷款年限数据。如图5-10（B11:G11）单元格区域所示。 在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。选定整个模拟运算表区域。如图5-11（A11:G24）单元格区域所示。 单击数据菜单中的模拟运算表命令。在模拟运算表对话框（如图5-12）的输入引用行的单元格框中输入“$C$6”（年份）；在输入引用列的单元格框中输入“$C$5”（年利率）,单击【确定】。 双变量模拟运算表的计算结果如图5－13所示。其中B12：G24单元格区域的计算公式为“{=表(C6,C5) }”，表示其是一个以C6为行变量，C5为列变量的模拟运算表。 单变量模拟运算表 假设有一个制衣厂，生产的裤子单价100元，其中单位人工费10元， 单位材料费15元， 单位产品制造过程中所耗费的水电费10元,可做表如下： 单变量模拟运算表 B2=B3+B4+B5, B7=B1－B2 B8=B9+B10+B11 单变量模拟运算表 B14=B13×B7 B15=B13×B1 B16=B8+B13×B2 B17=B15－B16 边际贡献总值=单位边际贡献 ×销量，盈亏平衡量=固定成本/单位边际贡献， 即B19=B8÷B7 其中： C1到F1的标题手工填入， 输入C2=0 和C3=1500时的值 选择C1到F3，然后按工具栏上的 图表向导。 选择XY散点图， 子图表类型选择 无数据点折线散点图， 然后按一步 2.3 单变量求解 什么是单变量求解？ 单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。 【例5-2】某公司想向银行贷款900万元人民币，贷款利率是8%，贷款限期为5年，如果公司每年可偿还100万元，该公司最多可贷款多少金额？ (1) 在工作表中输入原始数据，如图5-15