利用Excel进行规划求解

利用Excel 进行规划求解

Excel 具有规划求解的基本功能，包括线性规划和非线性规划。对于常规的线性规划问题，Excel 就可以给出求解结果。对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab 了。不过，大多数规划问题Mathcad 即可完成所赋予的任务。利用Excel 求解规划问题有些“罗嗦”，但也不难掌握。下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。

【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。有关生产情况的各种数据资料见下表。

3 果汁杯

6 h/百件 10 m 3/百件 600件 600元/百件 鸡尾酒杯 5 h/百件 20 m 3/百件 0件 400元/百件 *注：定点量为每周生产的最大数量。

若每周工作不超过50小时，且拥有储藏量为140m3的仓库。问：

⑴ 该个体户如何安排工作时间才能使得每周的收益最大？

⑵ 若每周多干1小时，收益增大多少？

⑶ 通过加班加点达到的收益极限是多少？

解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下：

21400600)(Max x x x f +=

⎪⎪⎩⎪⎪⎨⎧≥≥≤≤+≤+0

,06

14020105056 s.t.2112121x x x x x x x 显然，约束条件中的第三个式子x 1≤6可以表作1*x 1+0*x 2≤6，从而有如下矩阵

⎥⎦⎤⎢⎣⎡=400600c ，⎥⎦⎤⎢⎣⎡=21x x x ，⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=01201056A ，⎥⎥⎥⎦

⎤⎢⎢⎢⎣⎡=614050b 容易看到，上述模型表为矩阵形式便是：

目标函数为

[]⎥⎦

⎤⎢⎣⎡==21400600)(Max x x x c x f T 约束条件为

⎪⎪⎪⎩

⎪⎪⎪⎨⎧≥⎥⎦⎤⎢⎣⎡=⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=≤⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=061405001201056 s.t.21x x x b Ax

下面是利用Excel 求解规划结果的详细步骤：

第一步，录入数据，定义有关单元格

在Excel 中，将有关数据资料按一定的规范录入，最好按照资料表格录入。其中单元格B3、B4中的数值为预设的迭代初始值（相当于x 1(0)=1，x 2(0)=1），当然可以设为其他数值（如x 1(0)=0，x 2(0)=1）。

图1 录入数据，预设迭代初始值

接着是定义单元格，方法与步骤如下：

⒈ 定义目标函数

在B1单元格中输入公式“=F3*B3+F4*B4”，回车，这相当于建立目标函数公式

21400600)(x x x f +=

⒉ 定义约束条件

在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单元格。这几步相当于输入约束条件左半边

⎪⎩⎪⎨⎧+++21

212106201056x x x x x x

定义完毕以后，数据表给出了基于初始值（x 1(0)=1，x 2(0)=1）结果（图2）

。当然，如果初始值的设置不同，结果也会不同，但不影响最终求解答案。

图2 定义过单元格后的数据表

第二步，规划选项

沿着主菜单的“工具→规划求解”路径打开“规划求解参数”对话框（图3），进行如下设置：

⒈ 将光标置入“设置目标单元格”对应的空白栏中，再用鼠标选中B1单元格，这相

当于将目标函数公式导入。

⒉ 在下面的最大值、最小值等选项中，默认“最大值(M)”——因为本题是寻求最大收益。

⒊ 将光标置于“可变单元格”对应的空白栏中，用鼠标选中B3:B4单元格，这相当于令B3为x 1，B4为x 2。

图3 规划求解参数对话框

⒋ 接下是添加约束条件：点击图3中的添加(A)按钮，弹出“添加约束”对话框，将光标置于“单元格引用位置”对应的空白栏，用鼠标选中C6单元格；中间的小于等于号（<=）不变；再将光标置于“约束值”对应的单元格，用鼠标选中C5单元格（图4）。点击“添加(A)”或“确定”按钮。这一步相当于表达式

505621≤+x x

图4 添加约束第一步

再次点击图3中的添加按钮，分别在有关位置设置D6单元格，小于等于号<=，以及D5单元格（图5）。添加或确定。这一步相当于公式

140201021≤+x x

图5 添加约束第二步

第三次点击图3中的添加按钮，分别在有关位置设置E6单元格，小于等于号<=，以及E5单元格（图6）。添加或确定。这一步相当于公式

6021≤+x x

图6 添加约束第三步

第四次点击图3中的添加按钮，将光标置于“单元格引用位置”对应的空白栏，用鼠标选中B3单元格；中间的小于等于号（<=）改为大于等于号（>=）；再将光标置于“约束值”对应的单元格，输入0（图7）。添加或确定。这一步相当于

01≥x

图7 添加约束第四步

第五次点击图3中的添加按钮，分别在有关位置设置B4单元格，大于等于号>=，以及0（图8）。确定。这一步相当于公式

02≥x

图8 添加约束第五步

全部设置完毕以后，对话框的各项内容如下（图9）。如果打开“选项”对话框，还有更多的参数可以设置，不过对于简单的规划求解（如本例），那些选项暂时用不到。

图9 设置完毕以后的规划求解参数对话框