利用Excel进行规划求解.doc

PAGE PAGE 13 利用Excel进行规划求解 Excel具有规划求解的基本功能，包括线性规划和非线性规划。对于常规的线性规划问题，Excel就可以给出求解结果。对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab了。不过，大多数规划问题Mathcad即可完成所赋予的任务。利用Excel求解规划问题有些“罗嗦”，但也不难掌握。下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。 【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。有关生产情况的各种数据资料见下表。 品种 工效（h） 储藏量（m3） 定点量(件)* 收益（元） 果汁杯 6 h/百件 10 m3/百件 600件 600元/百件 鸡尾酒杯 5 h/百件 20 m3/百件 0件 400元/百件 *注：定点量为每周生产的最大数量。 若每周工作不超过50小时，且拥有储藏量为140m3的仓库。问： = 1 \* GB2 ⑴ 该个体户如何安排工作时间才能使得每周的收益最大？ = 2 \* GB2 ⑵ 若每周多干1小时，收益增大多少？ = 3 \* GB2 ⑶ 通过加班加点达到的收益极限是多少？ 解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下： 显然，约束条件中的第三个式子x1≤6可以表作1*x1+0*x2≤6，从而有如下矩阵 ，，， 容易看到，上述模型表为矩阵形式便是： 目标函数为 约束条件为 下面是利用Excel求解规划结果的详细步骤： 第一步，录入数据，定义有关单元格 在Excel中，将有关数据资料按一定的规范录入，最好按照资料表格录入。其中单元格B3、B4中的数值为预设的迭代初始值（相当于x1(0)=1，x2(0)=1），当然可以设为其他数值（如x1(0)=0，x2(0)=1）。 图1 录入数据，预设迭代初始值 接着是定义单元格，方法与步骤如下： = 1 \* GB1 ⒈ 定义目标函数 在B1单元格中输入公式“=F3*B3+F4*B4”，回车，这相当于建立目标函数公式 = 2 \* GB1 ⒉ 定义约束条件 在C6单元格中输入公式“=C3*B3+C4*B4”，回车；在D6单元格中输入公式“=D3*B3+D4*B4”，回车；在E6单元格中输入“=E3*B3+E4*B4”，回车。如果想一步到位，则可在C6单元格中输入公式“=$B$3*C3+$B$4*C4”（即在选中B3、B4单元格时，先后按功能键F4），回车以后，用鼠标指向C6单元格的右下角，揿住左键，右拖至E6单元格。这几步相当于输入约束条件左半边 定义完毕以后，数据表给出了基于初始值（x1(0)=1，x2(0)=1）结果（图2）。当然，如果初始值的设置不同，结果也会不同，但不影响最终求解答案。 图2 定义过单元格后的数据表 第二步，规划选项 沿着主菜单的“工具→规划求解”路径打开“规划求解参数”对话框（图3），进行如下设置： = 1 \* GB1 ⒈ 将光标置入“设置目标单元格”对应的空白栏中，再用鼠标选中B1单元格，这相当于将目标函数公式导入。 = 2 \* GB1 ⒉ 在下面的最大值、最小值等选项中，默认“最大值(M)”——因为本题是寻求最大收益。 = 3 \* GB1 ⒊ 将光标置于“可变单元格”对应的空白栏中，用鼠标选中B3:B4单元格，这相当于令B3为x1，B4为x2。 图3 规划求解参数对话框 = 4 \* GB1 ⒋ 接下是添加约束条件：点击图3中的添加(A)按钮，弹出“添加约束”对话框，将光标置于“单元格引用位置”对应的空白栏，用鼠标选中C6单元格；中间的小于等于号（=）不变；再将光标置于“约束值”对应的单元格，用鼠标选中C5单元格（图4）。点击“添加(A)”或“确定”按钮。这一步相当于表达式 图4 添加约束第一步 再次点击图3中的添加按钮，分别在有关位置设置D6单元格，小于等于号=，以及D5单元格（图5）。添加或确定。这一步相当于公式 图5 添加约束第二步 第三次点击图3中的添加按钮，分别在有关位置设置E6单元格，小于等于号=，以及E5单元格（图6）。添加或确定。这一步相当于公式 图6 添加约束第三步 第四次点击图3中的添加按钮，将光标置于“单元格引用位置”对应的空白栏，用鼠标选中B3单元格；中间的小于等于号（=）改为大于等于号（=）；再将光标置于“约束值”对应的单元格，输入0（图7）。添加或确定。这一步相当于 图7 添加约束第四步 第五次点击图3中的添加按钮，分别在有关位置设置B4单元格，大于等于号=，以及0（图8）。确定。这一步相当于公式 图8 添加约束第五步 全部设置完毕以后，对话框的各项内容如下（图9）。如果打开“选项”对话框，还有更多的参数可以设置，不过对于简单的规划求