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

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

　　范例：某生產(chǎn)單位生產(chǎn)混合飼料，規(guī)定所含各種營養(yǎng)成份為：A（蛋白質(zhì)）至少有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)成份要求的前提下，如何配合這四種飼料，使飼料配合的費用最低。

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

　　25X1＋8X2＋20X3＋7X4≥15（蛋白質(zhì)約束條件）
　　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（非負(fù)條件）
　　Z=500X1＋50X2＋350X3＋450X4（目標(biāo)函數(shù)值最�。�

　　Excel2000規(guī)劃求解步驟：

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

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

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

　　在上圖有關(guān)單元格輸入如下公式：
　　單元格地址　　　　　公式
　　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ī)劃求解.....]出現(xiàn)如下對話窗：

　　2.在“設(shè)置目標(biāo)單元格”窗口，輸入D14。

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

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

　　5.選取“添加”，出現(xiàn)“添加約束”窗口，在“添加約束”窗口輸入：

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

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

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

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

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