利用Excel2000进行规划求解一例

 利用Excel2000提供的规划求解可以解作业研究的问题，诸如线性规划、运输问题、食谱问题、饲料配方问题、人事安排等，只要与生产、制造、分配、投资、财务、工程等有关的求最大利润，最小费用等问题均可使用规划求解法找到答案。利用Excel2000提供的规划求解可以解决手工状态下图解法的不准确性和单纯形法的繁琐。本文介绍Excel2000在饲料配方上的应用，给其他方面的应用起个抛砖引玉的作用。

　　范例：某生产单位生产混合饲料，规定所含各种营养成份为：A（蛋白质）至少有15％，B（脂肪）至少含有4.5％，C（淀粉）至少含有30％，D（纤维素）不得超过10％。所能提供的原料有四种，甲（花生饼），每吨单价500元，含有各种营养成份分别为A25％、B2％、C10％、D2％；乙原料（花生秧），每吨单价为50元，四种营养成份的含量分别为A8％、B1％、C5％、D40％；丙原料（骨粉），每吨单价350元，含四种营养成份分别为A20％、B8％、C1％、D0.5％；丁原料（玉米），每吨单价为450元，含四种营养成份分别为A7％、B5％、C40％、D6％。求在符合饲料营养成份要求的前提下，如何配合这四种饲料，使饲料配合的费用最低。

　　这是一项多项资源配合生产问题，为便于列出数学模型，可将问题归纳为下表：
　　
　　根据以上资料，设四种原料的需要量分别为X1、X2、X3、X4，可列线性规划模型如下：

　　25X1＋8X2＋20X3＋7X4≥15（蛋白质约束条件）
　　2X1＋1X2＋8X3＋5X4≥4.5(脂肪约束条件)
　　10X1＋5X2＋1X3＋40X4≥30（淀粉约束条件）
　　2X1＋40X2＋0.5X3＋6X4≤10（纤维素约束条件）
　　X1≥0，X2≥0，X3≥0，X4≥0（非负条件）
　　Z=500X1＋50X2＋350X3＋450X4（目标函数值最小）

　　Excel2000规划求解步骤：

　　一、安装规划求解加载宏

　　用鼠标单击Excel2000[工具]菜单，在弹出的下拉菜单中单击[加载宏]命令，出现加载宏对话窗，在“当前加载宏”下拉列表窗里选定“规划求解”，然后单击[确定]按钮。加载后在[工具]菜单上出现[规划求解....]命令。

　　二、建立规划求解工作表（如上图所示）

　　在上图有关单元格输入如下公式：
　　单元格地址　　　　　公式
　　C7=D7＊D＄5＋E7＊E＄5＋F7＊F＄5＋G7＊G＄5
　　C8=D8＊D＄5＋E8＊E＄5＋F8＊F＄5＋G8＊G＄5
　　C9=D9＊D＄5＋E9＊E＄5＋F9＊F＄5＋G9＊G＄5
　　C10=D10＊D＄5＋E10＊E＄5＋F10＊F＄5＋G10＊G＄5
　　D13=D5＊D4
　　E13=E5＊E4
　　F13=F5＊F4
　　G13=G5＊G4
　　D14=SUM(D13:G13)

　　三、求最佳组合解：

　　1.选取[工具]→[规划求解.....]出现如下对话窗：

　　2.在“设置目标单元格”窗口，输入D14。

　　3.选定“最小值”选项。

　　4.在“可变单元格”窗口中输入D5:G5。

　　5.选取“添加”，出现“添加约束”窗口，在“添加约束”窗口输入：

　　单元格引用位置　　运算符号　　约束值
　　C7:C9　　　　　　　　>=　　　B7:B9
　　单击“确定”，完成输入。
　　按照上述操作步骤，再输入以下两条约束条件：
　　C10　　　　　　　　　<=　　　　B10
　　D5:G5　　　　　　　　>=　　　　0

　　6.在“规划求解参数”窗口，选择“求解”。

　　7.选择“确定”，计算结果如下图所示：

　　8.在“规划求解结果”对话框中选定保存“规划求解结果”，单击“确定”。

　　结果显示：满足条件的饲料配方每吨用花生饼0.328849吨、花生秧0.135426吨、骨粉0.05747吨、玉米0.649423吨。每吨最低费用483.55元。此结果经过验证，与单纯形法求解结果一致。