Excel模拟运算表

图5-22【方案总结】对话框
图5-23方案摘要
4 规划求解

在经济管理中，经常会遇到各种规划问题， 例如：人力资源的调度、产品生产的安排、 运输线路的规划、生产材料的搭配、采购批 次的确定等。这类问题有一个共同要求，那 就是：如何合理利用各种有限的资源实现最 佳的经济效益，也就是达到利润最大、成本 最低、费用最省等目标。
EXCEL基础及应 用
湖北生物科技职业学院 刘英
Excel数据分析工具及应用
1 2 3 4
数据分析工具的安装与卸载
模拟运算表
方案分析 规划求解
1
数据分析工具的安装与卸载
1.1 安装分析工具库
“分析工具库”是一个加载宏文件，文件名 为Analys32.xll（加载宏文件是可以自动 执行一系列复杂任务的操作命令的组合）。 但这一工作在安装 Microsoft Office组件 时已经加载。
元、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的名字为“乙烯销售成本增长率”，单元格H5 的名字为“丙烯销售额增长率”，单元格I5的名 字为“丙烯销售成本增长率”，单元格H6的名字 为“丁二烯销售额增长率”，单元格I6的名字为 “丁二烯销售成本增长率”，单元格H7的名字为 “总销售利润”。
4.1 建立规划求解模型
Excel规划求解问题的基本构成
（1）决策变量（variable）
一个或一组可变单元格，可变单元格称为决策变 量，一组决策变量代表一个规划求解的方案
（2）目标函数
目标函数表示规划求解要达到的最终目标，是规 划求解的关键。它是规划求解中可变量的函数
（3）约束条件
约束条件是实现目标的限制条件。
各分析值自动填入分析表中， 如图5-9（ G4：G16 ）单元 格区域所示。
图5-9模拟运算表工具使用示意图
2.2 双变量模拟运算表
对于问题③分析不同的利率和不同的贷款期 限对贷款的偿还额的影响，这时需要使用双 变量模拟运算表。 问题③求解：

选择某个单元格区域作为模拟运算表存放区 域，在该区域的最左列输入假设的利率变化
选择包含公式和需要进行模拟运算目标单 元区域（F3:G16），如图5-7所示。
图5-7住房贷款还款的Excel模拟运算表分析图

单击【数据】→【模拟运算表】命令，弹出 如图5-8所示的对话框。在“输入引用列的 单元格”中输入$D$5，再单击【确定】按钮，
图5-8住房贷款还款的Excel模拟运算表分析图
第一个参数是月利 率 分析图 图5-5住房贷款还款的Excel 第二个参数是还款 期 第三个参数是贷款 金额
问题②求解：可利用模拟运算表计算
不同利率下的月偿还额。
输入贷款期各种可能的不同利率数据，
如图5-6（F4:F16）单元格区域所示。
图5-6住房贷款还款的Excel模拟运算表分析图

图5-16【单变量求解】对话框
3 方案分析

方案，就是已命名的一组输入值，这组输入 值保存在工作表中，并可用来自动替换某个 计算模型的输入值，用来预测模型的输出结 果。对于同一解题方案的模型参数，可以创 建多组不同的参数值，得到各组不同的结论， 每组参数和结论都是一个方案。
【例5-3】某化工企业生产产品 乙烯、 丙烯、 丁二烯，在2008年的销售额分别为400万元、 600万元和300万元，销售成本分别为300万

安装步骤如下
菜单中的【加载宏】，如图5-1所示。
打开 Excel 工作簿，选择菜单栏中【工具】
图1加载宏
在打开“加载宏”对话框，对话框如图5-2
所示，单击【分析工具库】复选框，框前 即出现√号，表明已被选定，选择【确定】。
图2选择分析工具库
1.2 卸载分析工具库

当不需要使用分析工具库时，可以通过设置 来卸载分析工具库。 操作步骤：

什么是模拟运算表？ 是对工作表中一个单元格区域内的数据进行 模拟运算，测试使用一个或两个变量的公式 中变量对运算结果的影响。 模拟运算表的类型 ①基于一个输入变量的表，用这个输入变量 测试它对多个公式的影响；——单变量模 拟运算表 ②基于两个输入变量的表，用这两个变量测 试它们对于单个公式的影响——双变量模 拟运算表
建立规划求解模型：
第1步——建立求解工作表(输入原始数据及相应的各公式) 第2步——设置求解参数 选择“工具” —“规划求解”菜单，设置以下求解的各项参数： 设置目标单元格：输入目标函数所在单元格(为总余额单元 格) 设置目标：最大值、最小值或值的数值(最大利润,即最大值) 设置可变单元格：它的确定决定结果（为生产数量） 设置约束条件：单击【添加】按钮—输入约束条件—按添 加—依次输入所有约束条件—确定 第3步——保存求解结果 在规划求解对话框中按“求解”—在规划求解结果对话框中 按“保存规划求解结果”

单击数据菜单中的模拟运算表命令。在模 拟运算表对话框（如图5-12）的输入引用 行的单元格框中输入“$C$6”（年份）；在 输入引用列的单元格框中输入“$C$5”（年 利率）,单击【确定】。

双变量模拟运算表的计算结果如图5－13 所示。其中B12：G24单元格区域的计算公 式为“{=表(C6,C5) }”，表示其是一个以 C6为行变量，C5为列变量的模拟运算表。
决策变量定义为：
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) min z=0.3×1+0.9×2 s.t. x+y≥800 0.21×x-0.3×y≤0 0.03×x-0.01×y≥0 x,y≥0 //每天所需饲料 //蛋白质 //纤维
（3）单击【工具】菜单，选择【方案】项，系统 弹出【方案管理器】对话框，如图5-18所示，单击 【添加】按钮，系统弹出【添加方案】对话框，如 图5-19所示。
图5-18【方案管理器】对话框
图5-19【添加方案】对话框
（4）在【添加方案】对话框中，【方案名】 编辑框中输入“方案1好”，【可变单元格】 编辑框中输入“$H$4:$I$6”，单击【确定】 按钮，系统弹出【方案变量值】对话框， 如图5-20所示。

其中： C1到F1的标题手 工填入， 输入C2=0 和C3=1500时的值

选择C1到F3，然 后按工具栏上的 图表向导。 选择 XY散点图， 子图表类型选择 无数据点折线散 点图， 然后按一 步

2.3 单变量求解
什么是单变量求解？
单变量求解就是求解只有一个变量的方程
的根，方程可以是线性方程，也可以是非 线性方程。
打开Excel工作簿，选择菜单栏中【工具】
菜单中的【加载宏】。 在打开“加载宏”对话框，对话框如图4所 示，单击【分析工具库】复选框，将框前出 现√号取消。 选择取消后，单击【确定】按钮，即可自动 卸载分析工具库。此时在“工具”菜单中的
“数据分析”选项自动隐藏。
图5-4卸载分析工具库
5.2 模拟运算表
规划求解具有如下三个特点：
（1）所求问题都有单一的目标，如求生产的 最低成本，求运输的最佳路线，求产品的最 大盈利，求产品周期的最短时间以及求其他 目标函数的最佳值等。 （2）总是有明确的不等式约束条件。比如库 存不能低于一定的数量，否则造成原料短缺 或产品缺货；生产产品不能超过一定额度， 否则会造成商品积压等。 （3）问题都有直接或间接影响约束条件的一 组输入值。
4.2 求解优化问题
【例5-4】某农场每天至少使用800磅特殊饲 料。这种特殊饲料由玉米和大豆粉配制而成， 含有以下成份：
特殊饲料的营养要求是至少30%的蛋白质 和至多5%的纤维。该农场希望确定每天 最小成本的饲料配制。

求解过程：
步骤1：根据问题建立数学模型
因为饲料由玉米和大豆粉配制而成，所以模型的
图5-20【方案变量值】对话框
（5）在【方案变量值】对话 框中输入每个可变单元格的 值（这里要按行输入），完 毕后单击【添加】按钮，系 统会弹出如图5-19所示的 【添加方案】对话框，对第 2个方案进行输入；待所有 方案输入完毕后，单击【方 案变量值】对话框中的【确 定】按钮，系统返回到【方 案管理器】对话框，如图521所示。此时，可单击 图5-21【方案管理器】对话框 【关闭】按钮，回到工作表。
单变量模拟运算表

假设有一个制衣厂，生产的裤子单价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