Excel 2007实用教程 项目教学 教学课件 作者 许凌羽 项目八 制定生产规划方案.pptVIP

【学习目标】 了解什么是规划求解 熟悉规划求解的操作过程 能够利用规划求解解决最值问题 利用Excel中所提供的“规划求解”功能来解决玩具厂的最值问题。 这个项目主要分解为四个基本任务，它们分别是： 加载规划求解 应用规划求解 修改项目约束条件 建立分析报告 假设某玩具生产厂，在购入了新配件后，准备用来生产语音娃娃、电动小汽车和电动机器人三种不同类型的玩具。其中生产语音娃娃需要的成本为45元，花费的时间为28分钟，可获利32元；生产电动小汽车的成本为55元，花费的时间为30分钟，可获利35元；生产电动机器人需要的成本为60元，花费的时间为35分钟，可获利38元。 由于资金限制，规定每天投入到生产之中的金额不得超过1千元，并且每天最多工作10小时。为了获取最大利润，玩具厂每天将如何分配这3款玩具的生产。 规划求解是一个功能强大的分析工具，可以解决方程组、线性规划以及运筹学等领域的问题。特别是在实际应用中常涉及到很多优化问题，比如求最小成本、最大利润、线性回归和非线性回归等问题。 一般来说，在第一次安装Excel时并不安装规划求解。因此，我们需要先加载它才可以使用。 【步骤解析】 打开 按钮，在下拉菜单中单击 按钮，弹出【Excel选项】对话框，选择该对话框左侧列表中的【加载项】选项，如图8.2所示。 在对话框下方的【管理】框中，选择“Excel加载项”，然后单击按钮，弹出【加载宏】对话框。 在【可用加载宏】列表框中，选中【规划求解加载项】复选框，如图8.3所示。详细介绍操作步骤 单击 按钮，便完成了加载，此时在【数据】选项卡的【分析】组中可以找到 按钮，如图8.4所示。 （一） 分析约束条件 根据本章开头所介绍的玩具厂的实际生产情况分析，将所给出的前提条件进行整理，便得到了以下几个约束条件： 成本约束条件：45*X+55*Y+60*Z≤10000 生产时间约束条件： 25*X+30*Y+35*Z≤10*60 生产数量约束条件：X≥1，Y≥1，Z≥1，且都为整数 其中，X、Y、Z分别对应语音娃娃、电动小汽车和电动机器人的产量。 （二） 建立工作表 在分析完约束条件后，可以建立一张如图8.5所示的工作表。 【步骤解析】 在单元格D7中设置“实际生产成本”公式，实际成本等于每款的生产成本乘以其产量，输入：=B3*E3+B4*E4+B5*E5，按Enter键确认即可，如图8.6所示。 在单元格D8中设置“实际生产时间”公式，实际时间等于每款的生产时间乘以其产量，输入：=C3*E3+C4*E4+C5*E5，按Enter键确认即可，如图8.7所示。 在单元格B9中设置“每天销售利润”公式，每天销售利润等于每款的利润乘以其产量，输入：= D3*E3+D4*E4+D5*E5，按Enter键确认即可，如图8.8所示。 （三） 进行规划求解 【步骤解析】 选择【数据】选项卡中的【分析】组，然后单击该组中的 按钮，弹出如图8.9所示的对话框。 在其中的【设置目标单元格】中键入“$B$9”，表明每天的销售利润为规划求解的目标值；在紧接着下方的单选项中选中“最大值”选项；在【可变单元格】中键入“$E$3:$E$5”，表明三款玩具的产量是规划求解所要求取的变量值。如图8.10所示。 在对话框的【约束】列表框中，添加成本约束条件，即“实际成本”小于等于“生产成本限制”。单击【约束】列表框右侧的 按钮，弹出【添加约束】对话框，在其中的【单元格引用位置】文本编辑框中键入“$D$7”，然后在其右侧的符号下拉列表中选择“≤”项，在【约束值】文本编辑框中键入“$B$7”，然后单击 按钮即可，如图8.11所示。 在【添加约束】对话框中添加时间约束条件，即“实际时间”小于等于“生产时间限制”。在对话框中的【单元格引用位置】文本编辑框中键入“$D$8”，然后在其右侧的符号下拉列表中选择“≤”项，在【约束值】文本编辑框中键入“$B$8”，然后单击 按钮即可，如图8.12所示。 在【添加约束】对话框中添加数量约束条件，即“生产数量”大于等于“1”。在对话框中的【单元格引用位置】文本编辑框中键入“$E$3:$E$5”，然后在其右侧的符号下拉列表中选择“≥”项，在【约束值】文本编辑框中键入“1”，然后单击 按钮即可，如图8.13所示。 在【添加约束】对话框中继续添加数量约束条件，即 “生产数量”均为“整数”。在对话框中的【单元格引用位置】文本编辑框中键入“$E$3:$E$5”，然后在其右侧的符号下拉列表中选择“int”项，在【约束值】文本编辑框中显示“整数”，然后单击