利用Excel进行规划求解

利用Excel进行规划求解 Excel具有规划求解的基本功能，包括线性规划和非线性规划。对于常规的线性规划问题，Excel就可以给出求解结果。对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab了。不过，大多数规划问题Mathcad即可完成所赋予的任务。利用Excel求解规划问题有些“罗嗦”，但也不难掌握。下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。 【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。有关生产情况的各种数据资料见下。 品种 工效（h） 储藏量（m3） 定点量(件)* 收益（元） 果汁杯 6 h/百件 10 m3/百件 600件 600元/百件 鸡尾酒杯 5 h/百件 20 m3/百件 0件 400元/百件           *注：定点量为每周生产的最大数量。 若每周工作不超过50小时，且拥有储藏量为140m3的仓库。问： 该个体户如何安排工作时间才能使得每周的收益最大？ 若每周多干1小时，收益增大多少？ 通过加班加点达到的收益极限是多少？ 解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下： 显然，约束条件中的第三个式子x1≤6可以表作1*x1+0*x2≤6，从而有如下矩阵 ， ， ， 容易看到，上述模型表为矩阵形式便是： 目标函数为 约束条件为 下面是利用Excel求解规划结果的详细步骤： 第一步，录入数据，定义有关单元格 在Excel中，将有关数据资料按一定的录入，最好按照资料表格录入。其中单元格B3、B4中的数值为预设的迭代初始值（相当于x1(0)=1，x2(0)=1），当然可以设为其他数值（如x1(0)=0，x2(0)=1）。 图1 录入数据，预设迭代初始值 接着是定义单元格，方法与步骤如下： 定义目标函数 在B1单元格中输入公式“=F3*B3+F4*B4”，回车，这相当于建立目标函数公式 定义约束条件 在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），进行如下设置： 将光标置入“设置目标单元格”对应的空白栏中，再用鼠标选中B1单元格，这相当于将目标函数公式导入。 在下面的最大值、最小值等选项中，默认“最大值(M)”——因为本题是寻求最大收益。 将光标置于“可变单元格”对应的空白栏中，用鼠标选中B3:B4单元格，这相当于令B3为x1，B4为x2。 图3 规划求解参数对话框 接下是添加约束条件：点击图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）。如果打开“选项”对话框，还有更多的参数可以设置，不过对于简单的规划求解（如本例），那些选项暂时用不到。 图9 设置完毕以后的规划求解参数对话框 第三步，输出结果 在图9所示的对话框中，点击“求解”按钮，随机弹出“规划求解结果”选项框。若想知道详细的求解情况，可以选中“(R)”中的三个报告名称（图10）。 图10 规划求解结果对话框 点击图10所示的“确定”按钮，立即得到求解结果（图11）： 图11 规划求解结果 图12 运算结果报告 图13 敏感性报告 图14 极限值报告 另外给出了三个求解报告，包括：运算结果报告、敏感性报告和极限制报告（图12-14）。这几个报告比较简明，容易读懂，不多解释。 至此可知，对于这为个体户而言，他每周应该生产x1=6百件果汁杯，x2=2.8百件鸡尾酒杯。时间分配显然是：果汁杯6×6=36小时；鸡尾酒杯2.8×5=14小时，合计50小时。这样，他每周可以得到fmax=600×6＋400×2.8＝4720元的收入（毛收入）。 第四步，进一步的运算 将图2所示的C5单元格中的50改为51，重复上述规划求解过程，可知：每周多干1小时，可得4800元的收入，亦即多干1小时可以增加收入4800-4820=80元的收入（图15）。 图15 每周多干1小时的规划求解结果 继续增加工作时间，即不断推延工作时间的限量，在此过程中反复重复上述规划求解过程，可以发现，当工作时间延长到56小时时，工效和储藏量等约束都达到饱和值，此时的求解结果为x1=6，x2=4，fmax=5200（图16）。 图16 每周工作56小时的规划求解结果 如果继续延长工时，如每周工作60小时，我们发现，求解结果没有任何变化（图17），因为储藏量总量限定了生产的规模。 图17 每周工作60小时的规划求解结果 【例2】某矿业公司拥有两个矿场，生产的砂石分为三级：高级、中级和低级。该公司与某炼矿厂订有，每周供给高级矿12吨，中级矿8吨，低级矿24吨。该公司经营第一矿场日需1000元，经营第二矿场日需800元。但经营第一矿场每日可生产6吨高级矿，2吨中级矿与4吨低级矿，而经营第二矿场，每日可生产2吨高级矿，2吨中级矿与12吨低级矿。试问：两个矿场每周应分别经营多少日，方能使得该公司的生产最为经济？ 解：这是一个求最小成本问题，为了建模方便，不妨将所给资料列于表格之中：   高级矿 中级矿 低级矿 成本 第一矿场 6 2 4 1000 第二矿场 2 2 12 800 生产低限 12 8 24             根据主题思想和表中数据，可以建立线性规划模型如下： 模型中的参数可用矩阵表示 ， ， ， 可见，上述模型表为矩阵形式便是： 目标函数为 约束条件为 在Excel上实现上述规划求解的步骤与例1大同小异。显然，由于本例是求最小成本，与求最大收益肯定有不同之处。数据的排列方式与上例相同（图18，当然可以采用其他方式，总之要使定义变量来得方便为准）。 图18 录入数据并定义过单元格 在设置规划求解参数对话框时，对应于目标函数应选“最小值(N)”；在添加的约束条件栏目，所有的小于等于号“<=”全部改成大于等于号“>=”（图19）。利用线性规划方法求最小成本与求最大收益的模型区别就在于此，不论借助什么软件求解，这一点区分都是关键——从后面讲述的 “利用Mathcad进行规划求解”一节的有关内容可以看出这种区别。 图19 设置完毕以后的规划求解参数对话框 大于等于号的调用方法是：在添加约束条件的选项框中，用鼠标指向中间栏目下向三角符号 ，从下拉选项单中选择大于等于号（图20）。全部的求解结果如下（图21-24）。 图20 添加求最小值吨约束条件 图21 例2的运算结果报告 图22 例2的敏感性报告 图23 例2的极限值报告 基本的答案在初步计算结果中即可看到（图24）。 图24 例2的规划求解结果 借助Excel还可以处理非线性规划求解问题。不过，非线性规划不详线性规划那么单一，其模型表达形式可谓是千变万化。但不管怎么变化，基本原理是不变的。下面用最简单的实例进行说明。 【例3】给定一条1米长的铁丝，要求将其弯成一个矩形，使得该矩形包围的面积最大。 解：这是一个最简单的非线性规划问题：目标是矩形面积最大，约束条件是长度不超过1米，未知量是矩形的长短边的长度。不妨设两个边长分别为x1和x2米，于是建立非线性规划模型如下： 目标函数：                约束条件：                在高等数学中，这个问题是利用Lagrange乘数求条件极值的。根据目标函数和约束条件建立一个Lagrange函数 式中λ为La氏乘数（或称乘子）。分别对x1、x2求偏导得 ， 我们知道，函数仅在切线的斜率为0处有极值——所谓导数，其几何意义实则曲线某点的切线的斜率。因此，为求极值，令求导结果为0，得到 故最终的矩形实为一个正方形。将上式代入约束条件中的第一个式子，并取等号，即2x1+2x2=1，立即得到x1=x2=0.25米，λ=x1/2=x2/2=0.125，S=x1x2=0.0625平方米。 这个问题在Excel中求解及其方便，基本原理正是用了Lagrange函数。目标单元格不妨仍用B1单元格表示，表征未知量的可变单元格还用B3:B4表示（即用B3代表x1，用B4代表x2），约束值用B5单元格表示，初始值依然设为x1(0)=1，x2(0)=1（图25a）——显然，x1(0)+x2(0)=2>1，超过约束条件4倍，但是没关系，求解过程会自动收敛到最佳值（图25b）。 a 定义约束条件                b 求解的结果 图25 初始数据与求解结果 在目标单元格B1中输入目标函数表达式“=B3*B4”，这相当于 在约束条件单元格输入函数表达式“=2*B3+2*B4”，这相当于 规划求解参数对话框的设置如下（图26）。其中约束条件分别相当于 图26 规划求解参数的设置 求解的初步结果在图25b中给出：x1=x2=0.25米，最大面积为Smax=0.252=0.0625米2。详细结果可以参考下面的三个报告（图27－29）。 图27 例3的运行结果报告 图28 例3的敏感值报告 图29 例3的极限值报告