规划求解Excel上机手册.docVIP

附录I: 规划求解Excel上机手册 线性规划，0-1规划, 整数线性规划都可以在Excel上机求解。步骤是：打开Excel→工具→规划求解, 如图1所示 图1 如果工具栏中没有“规划求解”, 则在工具栏中打开“加载宏”, 再打开“规划求解” 如图2所示，然后选“确定”即有“规划求解”。如果按上述操作没有出现“规划求解”，则是在安装Excel时没有安装“规划求解”，这就要添加安装“规划求解”。 打开“规划求解”后，如图3所示。第一行为“设置目标单元格”，此格是设置目标函数值，可以根据方便、美观的要求设置在什么地方都可以.。第二行的“最大值”、“最小值”可根据求解的模型选择：若要解的模型是求最大，则选择“最大值”，在“最大值”前面的圈内有黑点表示选中；若要解的模型是求最小，则选择“最小值”，在“最小值”前面的园内有黑点表示选中。第三行是“可变单元格”，这里是设置变量的地方，可以根据方便、美观的要求设置在什么地方都可以。第四行是约束条件添加、删除与更改的地方。下面结合实际例子说明。 例 生产计划问题 胜利家具厂生产桌子和椅子两种家具。桌子售价50元／个，椅子售价30元／个，生产桌子和椅子需要木工和油漆工两种工种。生产一个桌子需要木工工时为4小时，油漆工工时为2小时。生产一个椅子需要木工工时为3小时，油漆工工时为1小时。该厂每月可用木工工时为120小时，油漆工工时为50小时。问该厂如何组织生产才能使每月的销售收入最大？ 图2 图3 解 设分别代表桌子和椅子的生产数量，则问题的线性规划模型为： 打开Excel如图4所示。 图4 图5 步骤1：第1列(A列)用于说明：在A1输入MaxZ=；A2输入C:；A3输入X:；A4输入S.T:。在B1输入公式“=B2*B3+C2*C3”(此式表示50 x1 +30 x2 ) 。第2行是目标函数系数行：目标函数系数c1, c2, …分别在B2, C2,…输入；本例在B2输入“50”，在C2输入“30”。第3行是变量行：B3,C3,D3,…分别表示x1,x2,x3,….。第4行起是约束行：本例在B4输入“4”，在C4输入“3”，在B5输入“2”，在C5输入“1”。(注：输入完变量系数后留一列用于输入计算公式)。 然后在E4输入“120”，在E5输入“50”，输入结果如图4所示。 步骤2：在预留列中输入约束公式。本例在D4输入“=$B$3*B4+$C$3*C4”。(注：输入公式一定不要忘记输入=，此式表示4x1+3x2。另外，变量采用绝对地址，如$B$3，$C$3；系数用相对地址，如B4, C4。这样复制起来就方便得多了。本例操作如图5所示：选D4，复制并粘贴到D5即可。 步骤3：打开Excel的工具条并选择“规划求解”，界面显示见图6。然后在“设置目标单元格”输入“$B$1”，在第二行选择“最大值”，在“可变单元格”输入“$B$3:$C$3”，界面显示见图7。最后选择“添加”约束条件，结果如图8所示。 在图8中，单元格引用位置输入“D4”，下拉中间选择框并选择=，约束值输入“E4”。表示约束条件：4x1+3x2 ≤120。类似地，继续添加其余外部约束条件，添加结果如图9所示。然后输入内部约束条件：在单元格引用位置输入全部变量，下拉中间选择框并选择=，约束值输入“0”(注：选中int表示此变量为整数变量, 选中bin表示此变量为0-1变量)。所有约束条件添加完毕，其结果如图10所示。 步骤4：选择“求解”，界面显示见图11。此时Excel已经对问题求解完毕：最优解(在B3与C3显示) ：x1=15, x2=20。目标值(在B1显示)：MaxZ=1350。然后，可以选择三个报告：运算结果报告见图12，敏感性分析报告见图13，极限值报告见图14。 图6 图7 图8 图9 图10 图11 图12为运算结果报告。其中第一张表是目标值的输出情况，该值从0变化到1350。 第二张表是变量的输出情况，桌子从0变化到15；椅子从0变化到20。第三张表是约束条件的输出情况，D4=120表示木工工时120全部用完，属于紧约束；同时，油漆工工时已全部用完，也是紧约束。 图13为敏感性分析报告，其中第一张表是变量的输出情况，例如桌子($B$3)终值=15, 其边际贡献为0(递减梯度=0)。第二张表是约束的输出情况，例如木工工时($D$4)终值=120，属于紧缺资源，其影子价格为5(拉格朗日乘数为5)。 图12 图13 图14为极限值分析报告。其中第一张表是目标值的极值情况，最大目标值($B$1)等于1350。第二张表是变量的输出情况，例如桌子($B$3)的最优产量为15张，对应目标值为1350；最少生产0张，对应目标值为600。 注：整数规划与0-1线性规划没有输出上述三个报表。 图14