利用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元。此結果經過驗證，與單純形法求解結果一致。