利用excel进行规划求解

《利用excel进行规划求解》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、利用Excel进行规划求解Excel具有规划求解的基本功能，包括线性规划和非线性规划。对于常规的线性规划问题，Excel就可以给出求解结果。对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab了。不过，大多数规划问题Mathcad即可完成所赋予的任务。利用Excel求解规划问题有些“罗嗦”，但也不难掌握。下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。有关生产情况的各种数据资料见下表。3*品种工效（h）储藏量（m）定点量

2、(件)收益（元）3果汁杯6h/百件10m/百件600件600元/百件3鸡尾酒杯5h/百件20m/百件0件400元/百件*注：定点量为每周生产的最大数量。若每周工作不超过50小时，且拥有储藏量为140m3的仓库。问：⑴该个体户如何安排工作时间才能使得每周的收益最大？⑵若每周多干1小时，收益增大多少？⑶通过加班加点达到的收益极限是多少？解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下：Maxf(x)=600x+400x12⎧6x1+5x2≤50⎪⎪10x1+20x2≤140s.t.⎨x≤6⎪1⎪x≥0,x≥0⎩

3、12显然，约束条件中的第三个式子x1≤6可以表作1*x1+0*x2≤6，从而有如下矩阵⎡65⎤⎡50⎤⎡600⎤⎡x1⎤⎢⎥⎢⎥c=⎢⎥，x=⎢⎥，A=⎢1020⎥，b=⎢140⎥⎣400⎦⎣x2⎦⎢⎣10⎥⎦⎢⎣6⎥⎦容易看到，上述模型表为矩阵形式便是：目标函数为T[]⎡x1⎤Maxf(x)=cx=600400⎢⎥x⎣2⎦约束条件为⎧⎡65⎤⎡50⎤⎪⎢⎥⎢⎥Ax=1020≤b=140⎪⎢⎥⎢⎥⎪s.t.⎨⎢⎣10⎥⎦⎢⎣6⎥⎦⎪⎪⎡x1⎤x=⎢⎥≥0⎪x⎩⎣2⎦1下面是利用Excel求解规划结果的详细步骤：第一步，录入数据，定义有关

4、单元格在Excel中，将有关数据资料按一定的规范录入，最好按照资料表格录入。其中单元格B3、B4中的数值为预设的迭代初始值（相当于x1(0)=1，x2(0)=1），当然可以设为其他数值（如x1(0)=0，x2(0)=1）。图1录入数据，预设迭代初始值接着是定义单元格，方法与步骤如下：⒈定义目标函数在B1单元格中输入公式“=F3*B3+F4*B4”，回车，这相当于建立目标函数公式f(x)=600x+400x12⒉定义约束条件在C6单元格中输入公式“=C3*B3+C4*B4”，回车；在D6单元格中输入公式“=D3*B3+D4*B4”，回车；

5、在E6单元格中输入“=E3*B3+E4*B4”，回车。如果想一步到位，则可在C6单元格中输入公式“=$B$3*C3+$B$4*C4”（即在选中B3、B4单元格时，先后按功能键F4），回车以后，用鼠标指向C6单元格的右下角，揿住左键，右拖至E6单元格。这几步相当于输入约束条件左半边⎧6x1+5x2⎪⎨10x1+20x2⎪6x+0x⎩12定义完毕以后，数据表给出了基于初始值（x1(0)=1，x2(0)=1）结果（图2）。当然，如果初始值的设置不同，结果也会不同，但不影响最终求解答案。图2定义过单元格后的数据表第二步，规划选项沿着主菜单的“工

6、具→规划求解”路径打开“规划求解参数”对话框（图3），进行如下设置：⒈将光标置入“设置目标单元格”对应的空白栏中，再用鼠标选中B1单元格，这相2当于将目标函数公式导入。⒉在下面的最大值、最小值等选项中，默认“最大值(M)”——因为本题是寻求最大收益。⒊将光标置于“可变单元格”对应的空白栏中，用鼠标选中B3:B4单元格，这相当于令B3为x1，B4为x2。图3规划求解参数对话框⒋接下是添加约束条件：点击图3中的添加(A)按钮，弹出“添加约束”对话框，将光标置于“单元格引用位置”对应的空白栏，用鼠标选中C6单元格；中间的小于等于号（<=）不变

7、；再将光标置于“约束值”对应的单元格，用鼠标选中C5单元格（图4）。点击“添加(A)”或“确定”按钮。这一步相当于表达式6x+5x≤5012图4添加约束第一步再次点击图3中的添加按钮，分别在有关位置设置D6单元格，小于等于号<=，以及D5单元格（图5）。添加或确定。这一步相当于公式10x+20x≤14012图5添加约束第二步第三次点击图3中的添加按钮，分别在有关位置设置E6单元格，小于等于号<=，以及E5单元格（图6）。添加或确定。这一步相当于公式x+0x≤6123图6添加约束第三步第四次点击图3中的添加按钮，将光标置于“单元格引用位置

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