用Excel软件求解规划问题的方法.docVIP

用Excel软件求解规划的方法 Microsoft Excel软件是当今十分流行的功能强大操作方便的软件。在Microsoft Excel软件中，具有规划求解功能。如图1，在工具菜单下，一般有“规划求解”项，若未有，则应先运行“加载宏”项目把其安装上。 图1 图2 1 一般线性规划的求解 现在让我们以下面的模型为例，介绍如何利用Microsoft Excel软件求解线性规划模型的操作方法。 首先，打开Microsoft Excel 的一个工作簿，把模型的约束系数矩阵置于A1至B4范围，约束常数置于D1至D4范围，而利润系数则置于A5至B5范围。选择A7至B7范围作可变单元（即这两个格相当于变量X1与X2），并输入初值０。然后，在单元格C1处输入“=A1*A7+B1*B7”，即第一个约束不等式的左边；同理，在单元格C2处输入“=A2*A7+B2*B7”，即第二个约束不等式的左边；对C3与C4也同样处理。最后，以单元格C5作目标单元格，输入“=A5*A7+B5*B7”。如图2。 接下来，按下主菜单的工具处，再在下拉菜单处选择“规划求解”，则弹出窗口如图3。 图3 图4 在“设置目标单元格”处输入“C5”,然后选“最大值”，再在“可变单元格”处输入“A7:B7”,在“约束”处按一下“添加”按钮，又弹出如图4的窗口。 在此，我们要添加5个约束：“C1 = D1”、 “C2 = D2”、 “C3 = D3”、 “C4 = D4”、 “A7：B7 = 0”。对第一个约束，在“单元格引用位置”处输入“C1”,在中间下拉框选择“=”, 再在“约束值”处输入“D1”。然后按“添加”按钮，再类似地添加其它约束。当然，这里前四个约束也可以简化一个约束：“C1:C4=D1:D4”。最后按“确定”按钮，返回前一窗口如图5。再在“选项”中选择“采用线性模型”。 图5 此时按“求解”按钮即可获得结果如图6。 图6 这时从A7至B7处可读出模型的最优解为:X1=1000,X2=300,再从C5处读出模型的最优值为4750。 我们在这里介绍的方法是直观的输入法，全部数据在表格上一目了然，便于观察与修改。当然，只要你愿意，也可以换一种输入方式，即把数据隐藏在单元格的公式内部，这样可省去单元格A1至B5与D1至D4，在其它单元格的公式或约束条件中，若引用到它们时，就用相应的常数代替。 2．混合线性规划的求解 求解混合线性规划模型： 现在我们按以下步骤来求解模型。 打开Microsoft Excel 的一个工作表； 把模型的目标函数系数矩阵置于A1至E4区域，约束常数25000、30000和21000分别置于G6、G7和G8单元格； 选择A6至E9范围作可变单元，并输入初值1。其中A6至E8区域对应变量xij(i=1,2,3; j=0,1,2,3,4,5)，而B9至E9则分别对应变量y1, y2, y3,和y4,A9则恒为1； 在F6、F7、F8和F9处分别输入“=SUM（A6：E6）”、“=SUM（A7：E7）”、“=SUM（A8：E8）”、“=SUM（B9：E9）”,再在A10至E10处分别输入“=A6+A7+A8”、 “=B6+B7+B8-41000*B9”、 “=C6+C7+C8-41000*C9”、 “=D6+D7+D8-41000*D9”、“=E6+E7+E8-41000*E9”表示约束等式的左边； 选择单元格A11, 输入“=A1*A6”,再把其引用至单元格E14;即用鼠标按着单元格A11的右下角，先拖至A14, 再拖至E14; 以单元格F14作目标单元格，输入“=SUM(A11:E14)” 这几步的结果如图7所示。 图7 进入“规划求解”界面。“设置目标单元格”处输入“F14”,然后选“最小值”，再在“可变单元格”处输入“A6:E9”,在“约束”处添加12个约束：⑴“A8：E8=0”、⑵“A9=1”、⑶“B9：E9＝二进制”、⑷“A10=35000”、⑸“B10=0”、⑹ “C10=0”、⑺ “D10=0”、⑻ “E10=0”、⑼“F6=G6”、⑽ “F7=G7”、⑾ “F8=G8”、⑿ “F9=1”。最后，规划求解参数界面如图8。再在“选项”中选择“采用线性模型”。 图8 此时按“求解”按钮即可获得结果如图9。 图9 这时从A6至E9处可读出模型的最优解为: x20=14000、 x30 =21000、x11=25000、x21=16000、y1=1,其余变量均为0。再从F14处读出模型的最优值为2384095。