利用Excel2000進行規(guī)劃求解一例

    利用Excel2000提供的規(guī)劃求解可以解作業(yè)研究的問題，諸如線性規(guī)劃、運輸問題、食譜問題、飼料配方問題、人事安排等，只要與生產、制造、分配、投資、財務、工程等有關的求最大利潤，最小費用等問題均可使用規(guī)劃求解法找到答案。利用Excel2000提供的規(guī)劃求解可以解決手工狀態(tài)下圖解法的不準確性和單純形法的繁瑣。本文介紹Excel2000在飼料配方上的應用，給其他方面的應用起個拋磚引玉的作用。

　　范例：某生產單位生產混合飼料，規(guī)定所含各種營養(yǎng)成份為：A（蛋白質）至少有15％，B（脂肪）至少含有4.5％，C（淀粉）至少含有30％，D（纖維素）不得超過10％。所能提供的原料有四種，甲（花生餅），每噸單價500元，含有各種營養(yǎng)成份分別為A25％、B2％、C10％、D2％；乙原料（花生秧），每噸單價為50元，四種營養(yǎng)成份的含量分別為A8％、B1％、C5％、D40％；丙原料（骨粉），每噸單價350元，含四種營養(yǎng)成份分別為A20％、B8％、C1％、D0.5％；丁原料（玉米），每噸單價為450元，含四種營養(yǎng)成份分別為A7％、B5％、C40％、D6％。求在符合飼料營養(yǎng)成份要求的前提下，如何配合這四種飼料，使飼料配合的費用最低。

　　這是一項多項資源配合生產問題，為便于列出數學模型，可將問題歸納為下表：
　　
　　根據以上資料，設四種原料的需要量分別為X1、X2、X3、X4，可列線性規(guī)劃模型如下：

　　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規(guī)劃求解步驟：

　　一、安裝規(guī)劃求解加載宏

　　用鼠標單擊Excel2000[工具]菜單，在彈出的下拉菜單中單擊[加載宏]命令，出現加載宏對話窗，在“當前加載宏”下拉列表窗里選定“規(guī)劃求解”，然后單擊[確定]按鈕。加載后在[工具]菜單上出現[規(guī)劃求解....]命令。

　　二、建立規(guī)劃求解工作表（如上圖所示）

　　在上圖有關單元格輸入如下公式：
　　單元格地址　　　　　公式
　　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.選取[工具]→[規(guī)劃求解.....]出現如下對話窗：

　　2.在“設置目標單元格”窗口，輸入D14。

　　3.選定“最小值”選項。

　　4.在“可變單元格”窗口中輸入D5:G5。

　　5.選取“添加”，出現“添加約束”窗口，在“添加約束”窗口輸入：

　　單元格引用位置　　運算符號　　約束值
　　C7:C9　　　　　　　　>=　　　B7:B9
　　單擊“確定”，完成輸入。
　　按照上述操作步驟，再輸入以下兩條約束條件：
　　C10　　　　　　　　　<=　　　　B10
　　D5:G5　　　　　　　　>=　　　　0

　　6.在“規(guī)劃求解參數”窗口，選擇“求解”。

　　7.選擇“確定”，計算結果如下圖所示：

　　8.在“規(guī)劃求解結果”對話框中選定保存“規(guī)劃求解結果”，單擊“確定”。

　　結果顯示：滿足條件的飼料配方每噸用花生餅0.328849噸、花生秧0.135426噸、骨粉0.05747噸、玉米0.649423噸。每噸最低費用483.55元。此結果經過驗證，與單純形法求解結果一致。