Excel模拟运算表

《Excel模拟运算表》由会员分享，可在线阅读，更多相关《Excel模拟运算表（52页珍藏版）》请在金锄头文库上搜索。

1、EXCEL基础及应用 湖北生物科技职业学院刘英 Excel数据分析工具及应用 数据分析工具的安装与卸载 模拟运算表 方案分析 1 2 3 4 规划求解 1数据分析工具的安装与卸载 1 1安装分析工具库 分析工具库 是一个加载宏文件 文件名为Analys32 xll 加载宏文件是可以自动执行一系列复杂任务的操作命令的组合 但这一工作在安装MicrosoftOffice组件时已经加载 安装步骤如下打开Excel工作簿 选择菜单栏中 工具 菜单中的 加载宏 如图5 1所示 图1加载宏 在打开 加载宏 对话框 对话框如图5 2所示 单击 分析工具库 复选框 框前即出现 号 表明已被选定 选择 确定 图

2、2选择分析工具库 1 2卸载分析工具库 当不需要使用分析工具库时 可以通过设置来卸载分析工具库 操作步骤 打开Excel工作簿 选择菜单栏中 工具 菜单中的 加载宏 在打开 加载宏 对话框 对话框如图4所示 单击 分析工具库 复选框 将框前出现 号取消 选择取消后 单击 确定 按钮 即可自动卸载分析工具库 此时在 工具 菜单中的 数据分析 选项自动隐藏 图5 4卸载分析工具库 5 2模拟运算表 什么是模拟运算表 是对工作表中一个单元格区域内的数据进行模拟运算 测试使用一个或两个变量的公式中变量对运算结果的影响 模拟运算表的类型 基于一个输入变量的表 用这个输入变量测试它对多个公式的影响 单变量

3、模拟运算表 基于两个输入变量的表 用这两个变量测试它们对于单个公式的影响 双变量模拟运算表 2 1单变量模拟运算表 例5 1 某人想买房 向银行贷款30万 还款年限为10年 目前年利率假设为4 采取每月等额还款的方式 请帮助该买房人计算在现有利率下每月的偿还额 请帮助该买房人计算不同利率条件下的每月偿还额 请帮助该买房人计算出还款期限是5年 10年 15年 20年 25年和30年时 每月等额还款的数额 单变量模拟运算表主要是用来分析当其他因素不变时 一个参数的变化对目标结果的影响 问题 求解 可利用PMT函数计算 1 建立一新工作簿 并选择一张工作表 将住房贷款有关的基本数据输入该工作表 B4

4、 D8 单元格区域所示 2 在D9单元格中输入公式 PMT D5 12 D8 D4 按下 Enter 键 计算出所要的结果 图5 5住房贷款还款的Excel分析图 第一个参数是月利率第二个参数是还款期第三个参数是贷款金额 问题 求解 可利用模拟运算表计算不同利率下的月偿还额 输入贷款期各种可能的不同利率数据 如图5 6 F4 F16 单元格区域所示 图5 6住房贷款还款的Excel模拟运算表分析图 选择包含公式和需要进行模拟运算目标单元区域 F3 G16 如图5 7所示 图5 7住房贷款还款的Excel模拟运算表分析图 单击 数据 模拟运算表 命令 弹出如图5 8所示的对话框 在 输入引用列的

5、单元格 中输入 D 5 再单击 确定 按钮 图5 8住房贷款还款的Excel模拟运算表分析图 图5 9模拟运算表工具使用示意图 各分析值自动填入分析表中 如图5 9 G4 G16 单元格区域所示 2 2双变量模拟运算表 对于问题 分析不同的利率和不同的贷款期限对贷款的偿还额的影响 这时需要使用双变量模拟运算表 问题 求解 选择某个单元格区域作为模拟运算表存放区域 在该区域的最左列输入假设的利率变化范围数据 在该区域的第一行输入可能的贷款年限数据 如图5 10 B11 G11 单元格区域所示 图5 10模拟运算表工具使用示意图 在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式 选定整个模

6、拟运算表区域 如图5 11 A11 G24 单元格区域所示 图5 11模拟运算表工具使用示意图 单击数据菜单中的模拟运算表命令 在模拟运算表对话框 如图5 12 的输入引用行的单元格框中输入 C 6 年份 在输入引用列的单元格框中输入 C 5 年利率 单击 确定 双变量模拟运算表的计算结果如图5 13所示 其中B12 G24单元格区域的计算公式为 表 C6 C5 表示其是一个以C6为行变量 C5为列变量的模拟运算表 单变量模拟运算表 假设有一个制衣厂 生产的裤子单价100元 其中单位人工费10元 单位材料费15元 单位产品制造过程中所耗费的水电费10元 可做表如下 单变量模拟运算表 B2 B3

7、 B4 B5 B7 B1 B2B8 B9 B10 B11 B14 B13 B7B15 B13 B1B16 B8 B13 B2B17 B15 B16边际贡献总值 单位边际贡献 销量 盈亏平衡量 固定成本 单位边际贡献 即B19 B8 B7 单变量模拟运算表 其中 C1到F1的标题手工填入 输入C2 0和C3 1500时的值 选择C1到F3 然后按工具栏上的图表向导 选择XY散点图 子图表类型选择无数据点折线散点图 然后按一步 2 3单变量求解 什么是单变量求解 单变量求解就是求解只有一个变量的方程的根 方程可以是线性方程 也可以是非线性方程 例5 2 某公司想向银行贷款900万元人民币 贷款利率

8、是8 贷款限期为5年 如果公司每年可偿还100万元 该公司最多可贷款多少金额 1 在工作表中输入原始数据 如图5 15 图5 15贷款总额计算 2 建立可变数公式 在单元格B2中输入公式 PMT B1 B3 B4 3 设置求解公式 菜单 工具 单变量求解 对话框中输入 目标单元格 目标值 可变单元格 如图5 16所示 图5 16 单变量求解 对话框 3方案分析 方案 就是已命名的一组输入值 这组输入值保存在工作表中 并可用来自动替换某个计算模型的输入值 用来预测模型的输出结果 对于同一解题方案的模型参数 可以创建多组不同的参数值 得到各组不同的结论 每组参数和结论都是一个方案 例5 3 某化工

9、企业生产产品乙烯 丙烯 丁二烯 在2008年的销售额分别为400万元 600万元和300万元 销售成本分别为300万元 380万元和220万元 根据市场情况推测 2009年产品的销售情况有好 一般和差三种情况 每种情况下的销售额及销售成本的增长率如图5 17所示 图5 17产品销售资料及预计增长率 3 1建立方案 1 设计方案计算分析 如图5 17所示 选择单元格H7并输入公式 SUMPRODUCT B3 B5 1 H4 H6 SUMPRODUCT C3 C5 1 I4 I6 2 对不同的产品进行命名 以便分析 分别将单元格H4的名字为 乙烯销售额增长率 单元格I4的名字为 乙烯销售成本增长率

10、 单元格H5的名字为 丙烯销售额增长率 单元格I5的名字为 丙烯销售成本增长率 单元格H6的名字为 丁二烯销售额增长率 单元格I6的名字为 丁二烯销售成本增长率 单元格H7的名字为 总销售利润 3 单击 工具 菜单 选择 方案 项 系统弹出 方案管理器 对话框 如图5 18所示 单击 添加 按钮 系统弹出 添加方案 对话框 如图5 19所示 图5 18 方案管理器 对话框图5 19 添加方案 对话框 4 在 添加方案 对话框中 方案名 编辑框中输入 方案1好 可变单元格 编辑框中输入 H 4 I 6 单击 确定 按钮 系统弹出 方案变量值 对话框 如图5 20所示 图5 20 方案变量值 对话

11、框 5 在 方案变量值 对话框中输入每个可变单元格的值 这里要按行输入 完毕后单击 添加 按钮 系统会弹出如图5 19所示的 添加方案 对话框 对第2个方案进行输入 待所有方案输入完毕后 单击 方案变量值 对话框中的 确定 按钮 系统返回到 方案管理器 对话框 如图5 21所示 此时 可单击 关闭 按钮 回到工作表 图5 21 方案管理器 对话框 3 2浏览方案 1 打开原工作表 并激活工作表 2 单击 工具 菜单 选择 方案 项 系统弹出 方案管理器 对话框 如图5 21所示 选择要想查看的方案 单击 显示 按钮 则系统就自动显示出该方案的执行结果 如图5 17所示 5 3 3编辑方案 对做

12、好的方案进行修改 只需在图5 21所示的 方案管理器 对话框中选中需要修改的方案 单击 编辑 按钮 系统弹出如图5 20所示的对话框 进行相应的修改即可 若要删除某一方案 则在图5 21所示的 方案管理器 对话框中选中需要删除的方案 单击 删除 按钮 若要增加方案 则在图5 21所示的 方案管理器 对话框中单击 添加 按钮 然后在图5 19所示的对话框填写相关的项目 3 4建立方案摘要 1 在图5 21所示的 方案管理器 对话框中单击 摘要 按钮 弹出 方案摘要 对话框 如图5 22所示 在 结果类型 中选择 方案摘要 项 在 结果单元格 中输入 H7 然后单击 确定 按钮 则系统在当前工作簿

13、中自动建立一个名为 方案总结 的工作表 如图5 23所示 图5 22 方案总结 对话框 图5 23方案摘要 4规划求解 在经济管理中 经常会遇到各种规划问题 例如 人力资源的调度 产品生产的安排 运输线路的规划 生产材料的搭配 采购批次的确定等 这类问题有一个共同要求 那就是 如何合理利用各种有限的资源实现最佳的经济效益 也就是达到利润最大 成本最低 费用最省等目标 规划求解具有如下三个特点 1 所求问题都有单一的目标 如求生产的最低成本 求运输的最佳路线 求产品的最大盈利 求产品周期的最短时间以及求其他目标函数的最佳值等 2 总是有明确的不等式约束条件 比如库存不能低于一定的数量 否则造成原

14、料短缺或产品缺货 生产产品不能超过一定额度 否则会造成商品积压等 3 问题都有直接或间接影响约束条件的一组输入值 4 1建立规划求解模型 Excel规划求解问题的基本构成 1 决策变量 variable 一个或一组可变单元格 可变单元格称为决策变量 一组决策变量代表一个规划求解的方案 2 目标函数目标函数表示规划求解要达到的最终目标 是规划求解的关键 它是规划求解中可变量的函数 3 约束条件约束条件是实现目标的限制条件 建立规划求解模型 第1步 建立求解工作表 输入原始数据及相应的各公式 第2步 设置求解参数选择 工具 规划求解 菜单 设置以下求解的各项参数 设置目标单元格 输入目标函数所在单

15、元格 为总余额单元格 设置目标 最大值 最小值或值的数值 最大利润 即最大值 设置可变单元格 它的确定决定结果 为生产数量 设置约束条件 单击 添加 按钮 输入约束条件 按添加 依次输入所有约束条件 确定第3步 保存求解结果在规划求解对话框中按 求解 在规划求解结果对话框中按 保存规划求解结果 4 2求解优化问题 例5 4 某农场每天至少使用800磅特殊饲料 这种特殊饲料由玉米和大豆粉配制而成 含有以下成份 特殊饲料的营养要求是至少30 的蛋白质和至多5 的纤维 该农场希望确定每天最小成本的饲料配制 求解过程 步骤1 根据问题建立数学模型因为饲料由玉米和大豆粉配制而成 所以模型的决策变量定义为

16、 x 每天混合饲料中玉米的重量 磅 y 每天混合饲料中大豆粉的重量 磅 目标函数是使配制这种饲料的每天总成本最小 因此表示为 z 0 3 x 0 9 y 模型的约束条件是饲料的日需求量和对营养成份的需求量 具体表示为 x y 800 每天所需饲料0 09 x 0 6 y 0 3 x y 蛋白质0 02 x 0 06 y 0 05 x y 纤维将上述不等式化简后 完整的模型为 minz 0 3 1 0 9 2s t x y 8000 21 x 0 3 y 00 03 x 0 01 y 0 x y 0 步骤2建立工作表工作表设计如图5 23所示 图5 23建立营养配方模型工作表 其中 输入数据的单元格使用了阴影格式 即B4 C7和F5 F7 变量和目标函数单元格为B12 D12 D4 D7中输入了约束公式到D4 SUMPRODUCT B4 C4 B 12 C 12 D5 SUMPRODUCT B5 C5 B 12 C 12 D6 SUMPRODUCT B6 C6 B 12 C 12 D7 SUMPRODUCT B7 C7 B 12 C 12 步骤3应用规划求解工具 单击 工具 规划求解 出