Excel2000 应用案例之四十一

9.2  规划模型求解

    建立好规划模型后，即可使用Excel 2000的规划求解工具求解了。由于在默认情况下，Excel 2000不加载规划求解工具。所以要应用规划求解工具，且Excel 2000的工具菜单中没有规划求解命令时，应先加载规划求解工具。其操作步骤如下：
    单击工具菜单中的加载宏命令，这时将出现加载宏对话框。
    在当前加载宏列表框中，选定规划求解的复选框，单击确定。
    这以后的工具菜单中，将会出现规划求解命令。当需要进行规划求解操作时，直接执行该命令即可。如果不再需要进行规划求解操作时，可以按照类似的方法，通过加载宏命令，取消当前加载宏列表中规划求解的复选框。这样将会把规划求解命令从工具菜单中移去。
   

9.2.1  建立工作表

    规划求解的第一步，是将规划模型的有关数据输入到工作表中。其具体步骤如下：
    在B5、B6、B7和B8单元格分别输入第一季到第四季的应交货数量。
    设在C5、C6、C7和C8单元格分别存放第一季到第四季的生产数量。先设置其初始值与应交货数量相同。可以直接将B5:B8单元格区域的内容复制到C5:C8单元格区域。
    在D5单元格建立计算第一季生产费用的公式：“=80+98*C5-0.12*C5^2”，并将其填充到D6、D7和D8单元格区域。计算出其它季度的生产费用。
    在E5单元格建立计算第一季存储数量的公式：“=C5-B5”，即应等于第一季的生产数量减去第一季的应交货数量。
    在E6单元格建立计算第二季存储数量的公式：“=E5+C6-B6”，即应等于第一季的存储数量加上第二季的生产数量减去第二季的应交货数量。并将其填充到E7和E8单元格区域。计算出第三季和第四季的存储数量。
    在F5单元格建立计算第一季存储费用的公式：“=16*E5”，并将其填充到F6、F7和F8单元格区域。计算出其它季度的存储费用。
    在G5:G8单元格区域输入生产能力限制。
    在H5单元格建立计算第一季可交货数量的公式：“=C5”，即应等于第一季的生产数量。
    在H6单元格建立计算第二季可交货数量的公式：“=E5+C6”，即应等于第一季的存储数量加上第二季的生产数量。并将其填充到H7和H8单元格区域。计算出第三季和第四季的可交货数量。
    在B9:F9单元格区域输入计算上述单元格的合计的公式。
    在B2单元格输入计算目标函数的公式：“=D9+F9”。即等于生产费用和存储费用的总和。
    建立好的工作表如图9－1所示。
   

图9－1

    从图9－1可以看出，按照交货数量安排生产计划时，目标函数，即总的费用为26136元。下面考查一下其它的生产计划方案。
    先考虑均衡生产方式，即按80、70、70和70的数量安排生产计划，计算结果如图9－2所示。
   

   

图9－2

这时的生产费用和存储费用分别为26208元和480元，总费用为26688元。即效益不如图9－1的方案。
    通过生产函数可知，生产规模越大，单位生产费用越低。故考查按120、40、40和90的数量安排生产计划，计算结果如图9－3所示。
   

图9－3

    该方案的生产费用和存储费用分别为25656元和960元，总费用为26616元。即效益介于图9－1和图9－2方案之间。
   

9.2.2  规划求解

    显然，可选的方案很多。利用Excel 2000的规划求解工具可以迅速帮助找到最佳方案。其具体操作步骤如下：
    单击工具菜单中的规划求解命令，这时将出现规划求解参数对话框，如图9－4所示。
   

图9－4

    设置目标函数。指定设置目标单元格为目标函数所在的单元格$B$2，并选定最小值单选钮。
    设置决策变量。指定可变单元格为决策变量所在的单元格区域$C$5:$C$8。
    设置约束条件。单击添加按钮，这时将出现添加约束对话框。如图9－5所示。在单元格引用位置中指定决策变量第一季生产数量所在单元格的地址$C$5，选择“>=”关系运算符，在约束值中键入第一季应交货数量所在的单元格地址$B$5，单击添加按钮，即添加了一个约束条件：“$C$5>=$B$5”。第一季的生产数量应大于或等于第一季的应交货数量。
   

图9－5

    按照上述步骤逐个添加下表中的各约束条件。添加完毕后，单击确定按钮。这时的规划求解参数对话框如图9－6所示。
   

约束条件	说明
$C$5<=$G$5	第一季的生产数量应小于或等于第一季的生产能力。
$C$6<=$G$6	第二季的生产数量应小于或等于第二季的生产能力。
$C$7<=$G$7	第三季的生产数量应小于或等于第三季的生产能力。
$C$8<=$G$8	第四季的生产数量应小于或等于第四季的生产能力。
$H$6>=$B$6	第二季的可交货数量应大于或等于第二季的应交货数量。
$H$7>=$B$7	第三季的可交货数量应大于或等于第三季的应交货数量。
$H$8=$B$8	第四季的可交货数量应等于第四季的应交货数量。

图9－6

    单击求解按钮。Excel 2000即开始进行计算，最后出现规划求解结果对话框，如图9－7所示。
   

图9－7

    根据需要选择是保存规划求解结果还是恢复为原值；是否保存方案，是否生成运算结果报告、敏感度分析报告和限制范围报告。这里选择保存规划求解结果，并生成运算结果报告、敏感度分析报告和限制范围报告。最后的计算结果如图9－8所示。
   

图9－8

    从计算结果可以看出，最佳生产方案是第一季到第四季分别生产130、10、60和90。其生产费用和存储费用分别为25296元和800元，总费用为26096元。该方案较原方案节省520元。