《运筹学研究生辅导课件》用Excel求解LP.docVIP

用Excel求解LP(线性规划)问题 首先检查是否加载了宏“规划求解”？即查看Excel窗口的“工具”菜单下是否有“规划求解”菜单条？如尚未加载，可单击Excel窗口的“工具”(“加载宏”，在所弹出的“加载宏”对话框中选“规划求解”，单击“确定”(如下图1)。 图1一、建立线性规划问题的电子表格模型 使用Excel求解线性规划问题时，电子表格是输入和输出的载体，因此设计良好的电子表格，更加易于阅读。下面以生产计划为例说明建立线性规划问题的电子表格模型。其数据如下表： 工厂 单位产品的生产时间 每周可用工时 门 窗 1 2 3 1小时 0 3小时 0 2小时 2小时 4小时 12小时 18小时 单位利润（元） 300 500 本例的电子表格设计如下图所示：（见例1.1 xls）建立线性规划问题的电子表格模型需要完成以下工作　　1．在工作表中输入数据。显示数据的单元格称为数据单元格。为单元格命名可以使表格更容易理解和使用。在例1.1 xls电子表格中，对数据单元格做了如下命名：单位利润（C4:D4），可用工时（G7:G9）。为单元格命名，首先选中单元格，然后从“插入”菜单中选择“命名”选项，再输入名字（或者单击数据表上公式栏左侧的名称框，输入名字）。 2．在工作表中输入模型部分（包括决策变量、约束条件、目标函数）。（1）确定每个决策变量所对应的单元格，称为可变单元格。在例1.1 xls电子表格中，决策变量所对应的单元格为（C12:D12），并命名为每周产量。 （2）选择单元格输入公式,计算每个约束条件左边的值。在例1.1 xls电子表格中，三个约束的左边的值所对应的单元格为（E7:E9），并命名为实际使用。分别输入公式为： 单元格E7： =C7*C12+D7*D12 单元格E8： =C8*C12+D8*D12 单元格E9： =C9*C12+D9*D12 单元格（E7:E9）中的每一个都给出了依赖于可变单元格（C12:D12）的输出结果，他们称为输出单元格。 注意输出单元格（E7:E9）中输入的公式都是两组数相乘后相加，Excel中的函数SUMPRODUCT可以实现这一功能，它能将2至30个大小相同的单元格区域（每个单元格区域用逗号隔开）中的对应数值型元素相乘后再相加。例如单元格E7中的公式用这个函数表达为： 单元格E7：=SUMPRODUCT(C7:D7, C12:D12) 利用Excel函数的复制功能，这个公式可以通过引用方法复制到E8、E9单元格中，避免了重复输入公式的烦恼。因为是纵向复制，可以把放有资源系数的单元格区域做相对引用，把放有决策变量的单元格区域做绝对引用或混合引用，即将公式变为： 单元格E7：=SUMPRODUCT(C7:D7, C$12:D$12) 其中符号“$”表示绝对引用，“$”后面的数字或符号在拖动复制过程中不变。这样，单元格E7中的公式就可以通过拖动复制到E8、E9单元格中。 因为单元格（C12:D12）命名为每周产量，所以单元格E7中的公式也可以写为： 单元格E7：=SUMPRODUCT(C7:D7,每周产量) 在单元格（F7:F9）中，输入“=”，它只起提示作用，并不参与运算。 （3）选择某一单元格，输入目标函数的公式，称为目标单元格。在例1.1 xls电子表格中，目标函数所对应的单元格为（G12），并命名为总利润，输入目标函数的公式为=C4*C12+D4*D12，或=SUMPRODUCT(单位利润,每周产量)。 二、用Excel规划求解工具求解线性规划问题 下面以例1.1 xls为例，说明用Excel规划求解工具求解线性规划问题的步骤。 1．选择“工具”菜单，在其弹出的子菜单中选择“规划求解”选项，打开“规划求解参数”对话框，如图3所示。图3 “规划求解参数”对话框 2．设置目标单元格，例1.1 xls电子表格中，目标函数所对应的单元格为G12，所以单击G12单元格（或直接输入$G$12）。 3．目标如果是极大化，则单击“最大值”单选按钮；目标如果是极小化，则单击“最小值”单选按钮；如果目标函数需要达到某个值，则单击“值为” 单选按钮并在文本框输入需要达到的值。 4．设置可变单元格，例1.1 xls电子表格中，决策变量所对应的单元格为（C12:D12），所以在“可变单元格(B)”栏后的空白中填入$C$12:$D$12, 或选择C12:D12单元格。 5．添加约束，光标指向“约束”栏，按“添加”，出现“添加约束”对话框（如图4），图4 “添加约束”对话框 在添加约束对话框中有3个选项需要填写， （1）在“单元格引用位置” 填入约束不等式左端的值所对应的单元格或单元格区域。例1.1 xls应填入（E7:E9）。 （2）选择关系符，例1.1 xls应选