用Excel求解规划问题 陈岁松 1999年 第38期 34版 　　既要马儿跑得好，又要马儿少吃草，在运筹学上称为最优化原则。最优化的典型问题就是“规划问题”。规划问题可以从两个方面进行阐述：一是用尽可能少的人力、物力、财力资源去完成给定的任务；二是用给定的人力、物力、财力资源去完成尽可能多的工作。两种说法，一个目的，这就是“既要马儿跑得好，又要马儿少吃草”。 　　求解“规划问题”一般要经过四个步骤： 　　1.确定决策变量。决策变量就是问题等待决定的数量，用X1、X2……Xn表示。 　　2.确定目标函数Z。将决策变量用数学公式表达出来，就是目标函数。目标函数可以是最大（max）、最小（min），或某个具体确定值。 　　3.确定约束条件。约束条件就是人力、物力、财力资源的限制范围，用≥、≤或=表示，还有非负约束（≥0）和整数约束（=int）。 　　4.求解规划方程组，获取目标函数的最优化解。 　　传统求解规划问题的方法都比较麻烦，Excel给我们提供了最简捷的计算工具。 　　Excel“规划求解”在“工具”的下拉菜单中可以找到，如果没有，可通过“加载宏”将“规划求解”加载进来。Excel5.0加载比较方便。Excel97加载就比较麻烦，要通过“安装/删除”将Excel再安装一次。 　　做规划求解关键要设计一个好表格，将决策变量、约束条件、目标函数依次排列，然后点击“工具”菜单中的“规划求解”。在“规划求解参数”对话框中输入“目标单元格”（用鼠标选取即可），目标单元格中必须事先输入含决策变量的计算公式，目标值可以根据需要设置为“最大值”、“最小值”或“目标值”。如设置为“目标值”，应输入目标数值。“可变单元格”即决策变量的单元格，决策变量一般是一个组。“约束”栏输入约束条件，点“增加”输入一个约束条件，再点“增加”再输入一个，直到输完为止。点“选项”，可修改迭代运算的参数，选取“采用线性模型”可以加快运算速度，选取“自动按比例缩放”可以避免数值相差过大引起的麻烦。以上设置完成后，点击“求解”，Excel自动完成求解计算。需要说明的是，在求解之前，最好将决策变量设置一个近似的值，以便缩短求解计算次数。如果一次求解结果不理想，还可再来一次，一般两三次就可以了。 　　实例1：房屋开发问题。某房屋建筑开发公司，现有资金9000万元，拟建造350套住宅。住宅共3种规格：二室一厅、三室一厅、四室一厅。通过市场调查，三种规格的住宅需求不同，二室一厅占15％，三室一厅占60％，四室一厅占25％。三种规格的住宅，每套造价依次为20万元、25万元、30万元。利润依次为2万元、3万元、4万元。根据这些条件，如何安排建设才能使公司的利润获得最大? 　　根据题意，设决策变量X1、X2、X3代表三种住宅的建造数量，目标函数取最大maxZ=2X1＋3X2＋4X3，约束条件依次为： 　　资金约束：20X1＋25X2＋30X3≤9000 　　住宅套数约束：X1＋X2＋X3≥350 　　二室比例约束：X1/（X1＋X2＋X3）≤0.15 　　三室比例约束：X2/（X1＋X2＋X3）≤0.60 　　四室比例约束：X3/（X1＋X2＋X3）≤0.25 　　同时要求非负约束：X1≥0、X2≥0、X3≥0 　　表格设计如表1：C3、C4、C5设置为决策变量单元格，E3：E7输入约束值。 　　F3输入资金约束公式：“=20＊C3＋25＊C4＋30＊C5” 　　F4输入建造总套数约束公式：“=C3＋C4＋C5” 　　F5输入二室比例约束公式：“=C3/（C3＋C4＋C5）” 　　F6输入三室比例约束公式：“=C4/（C3＋C4＋C5）” 　　F7输入四室比例约束公式：“=C5/（C3＋C4＋C5）” 　　G3：G7为“剩余值”，输入约束值和求解值的差，以便直观地了解满足约束条件的情况。在单元格G3输入公式“=E3－F3”，然后点住G3右下角向下拖动，完成其他公式的输入。 　　规划求解参数的设置如^383401e^。求解结果见^383401a^：二室住宅建造53套、三室住宅建造212套、四室住宅建造88套，合计353套，此时可获利润1094.12万元。9000万元资金正好用完，套数比例完全满足市场需要。 　　实例2：食谱配方问题。某医院营养室在制订一周的菜单时，列出了6种蔬菜的营养含量、价格和每份菜对营养的要求（见^383401d^）。医院还规定，每周供应蔬菜品种为14种，蔬菜品种可以重复，但白菜重复次数不超过2次，其它蔬菜重复次数不超过4次。根据这些要求，如何安排菜谱才能使每周蔬菜的价格最低? 　　根据题意，设决策变量X1、X2、…X6分别代表6种蔬菜供应次数。 　　目标函数取最小：minZ=0.5X1＋0.5X2＋0.8X3＋0.2X4＋0.6X5＋0.3X6 　　每周蔬菜品种约束：X1＋X2＋X3＋X4＋X5＋X6=14 　　铁含量约束：0.45X1＋0.45X2＋1.05X3＋0.4X4＋0.5X5＋0.5X6≥6 　　磷含量约束：10X1＋28X2＋50X3＋25X4＋22X5＋75X6≥325 　　维生素A约束：415X1＋9065X2＋2550X3＋75X4＋15X5＋235X6≥17500 　　维生素C约束：8X1＋3X2＋53X3＋27X4＋5X5＋8X6≥245 　　烟酸含量约束：0.3X1＋0.35X2＋0.6X3＋0.15X4＋0.25X5＋0.8X6≥5 　　非负约束：X1、X2、X3、X4、X5、X6≥0。 　　表格设计如表3。其中，C2：C7为决策变量单元格，E2：E7为各约束条件的数值。 　　F2输入蔬菜品种约束公式：“=SUN（C2：C7）” 　　F3输入铁含量约束公式：“=0.45＊C2＋0.45＊C3＋1.05＊C4＋0.5＊C6＋0.5＊C7” 　　F4输入磷含量约束公式：“=10＊C2＋28＊C3＋50＊C4＋25＊C5＋22＊C6＋75＊C7” 　　F5输入维生素A约束公式：“=415＊C2＋9065＊C3＋2550＊C4＋75＊C5＋15＊C6＋235＊C7” 　　F6输入维生素C约束公式：“=8＊C2＋3＊C3＋53＊C4＋27＊C5＋5＊C6＋8＊C7” 　　F7输入烟酸含量约束公式：“=0.3＊C2＋0.35＊C3＋0.6＊C4＋0.15＊C5＋0.25＊C6＋0.8＊C7” 　　G2输入目标函数公式：“=0.5＊C2＋0.5＊C3＋0.8＊C4＋0.2＊C5＋0.6＊C6＋0.3＊C7” 　　规划求解参数设置如^383401b^。求解结果如^383401c^所示：青豆安排4次、萝卜安排1次、椰菜安排3次、白菜安排2次、土豆安排4次、甜菜不安排。这样的菜谱，营养完全满足要求，而蔬菜价格最低，只有6.5元。 　　EXcel“规划求解”还可以进行动态规划分析、灵敏度分析等，限于篇幅，本文从略。