线性规划实验1-EXCEL求解LP.doc.doc

线性规划求解实验指导 一、Excel函数 使用Excel求解线性规划问题时，SUMPRODUCT函数可以大大降低资料录入工作量，提高工作效率。计算数组或向量的乘积时，使用SUMPRODUCT函数，格式如下： SUMPRODUCT(数组1,数组2,…,数组n) 其中2n30，即最多可以使用30个数组参数，返回值为n个数组对应元素乘积之和。以图1为例，在单元格D1中输入公式 =SUMPRODUCT(A1:B1,A2:B2,A3:B3) 得到111（相当于A1*A2*A3 + B1*B2*B3 = 1*2*3 + 3*5*7 = 111）。在单元格D2中输入公式 =SUMPRODUCT(A1:C1,A2:C2) 得到53(相当于A1*A2 + B1*B2 + C1*C2 = 1*2 + 3*5 + 4*9 = 53)。 图1. 乘积和(SUMPRODUCT函数)计算结果 二、求解实例 1. 问题描述与模型建立 某玩具厂生产猫和龟两种玩具，制造一个玩具猫可获利30元，制造一个玩具龟可获利20元。制造一个猫需要2小时机工和1小时手工；制造一个龟需要1小时机工和1小时手工。在一周内，机工不能超过100h，手工不能超过80h，猫的产量不能超过45个。求产品的最佳生产量和最大利润。 设为一周内猫的生产量，为一周内龟的生产量。可建立如下线性规划模型 2. 数据录入 （1）启动Excel，建立如图2所示的Excel工作表，输入系数矩阵A到区域C2：D4；输入约束常数b到区域F2：F4；输入目标系数到区域C5：D5。 （2）指定单元格C6和D6存储变量和的值，称之为可变单元格。在可变单元格中输入数字1表示给定初始值，但并非一定这样；若这两个单元格不输入内容，Excel将按0处理，不影响求解。 （3）在有关单元格输入计算公式： 单元格 公 式 E2 =SUMPRODUCT(C2:D2,C6:D6) E3 =SUMPRODUCT(C3:D3,C6:D6) E4 =SUMPRODUCT(C4:D4,C6:D6) C7 =SUMPRODUCT(C5:D5,C6:D6) 单元格E2，E3，E4分表储存3个约束方程左端的函数表达式；单元格C7储存目标函数表达式。图2中的文字是为了使问题表达更清楚，并非必要。 图2. 规划求解数据输入格式 3. 求解操作 延续图2,求最优解的步骤如下： （1）选择“工具”、“规划求解”，显示画面如图3所示。 （2）在“设置目标单元格”输入框内输入C7。 图3. “规划求解参数”对话框 （3）选定“最大值”选项（用鼠标左键点击其圆形复选框）。 （4）在“可变单元格”输入框内输入C6:D6。 （5）点击“添加”按钮，输入约束条件，画面显示如图4。 图4. “添加约束”对话框 （6）在“单元格引用位置”输入框内输入E2:E4，指明约束条件左端函数的存放位置。 （7）选定运算符号 = 。 （8）在“约束值”输入框内输入F2:F4，指明约束条件右端常数的存放位置。 （9）点击“添加”按钮，添加约束条件E2:E4=F2:F4（相当于3个约束条件E2=F2，E3=F3，E4=F4）。 （10）点击“取消”按钮，回到图3所示的“规划求解参数”对话框。 接下来，需要输入变量非负约束。有如下两种方法： ① 点击图3中“选项”按钮，显示“规划求解选项”对话框，画面如图5所示。在图5中，用鼠标左键点击“假定非负”前面的复选框，打上复选标记；而后点击“确定”按钮，返回图3所示的“规划求解参数”对话框。 ② 点击“添加”按钮，显示“添加约束”对话框，画面显示如图6。在“单元格引用位置”输入框内输入C6:D6；在“约束值”输入框内输入0；选定运算符号 = ；点击“确定”按钮添加变量非负信息；最后点击“取消”按钮，返回图3所示的“规划求解参数”对话框。这是，在“约束”对话框内增加了$C$6:$D$6 = 0的约束条件。 到此为止，已完成“原始数据”和“规划求解参数”的全部输入和选择工作。只要点击图3所示的“规划求解参数”对话框中“求解”按钮，即可达到“求最优解”的目的。 图5. “规划求解选项”对话框 图6. “添加约束”对话框 图7. 求解后“变量取值”、及“目标函数值”的变化 此时的图2变为图7；并出现“规划求解结果”对话框如图8。用鼠标左键点击图8中的“计算结果报告