利用Excel2000提供的規(guī)劃求解可以解作業(yè)研究的問題,諸如線性規(guī)劃、運(yùn)輸問題、食譜問題、飼料配方問題、人事安排等,只要與生產(chǎn)、制造、分配、投資、財(cái)務(wù)、工程等有關(guān)的求最大利潤,最小費(fèi)用等問題均可使用規(guī)劃求解法找到答案。利用Excel2000提供的規(guī)劃求解可以解決手工狀態(tài)下圖解法的不準(zhǔn)確性和單純形法的繁瑣。本文介紹Excel2000在飼料配方上的應(yīng)用,給其他方面的應(yīng)用起個(gè)拋磚引玉的作用。
范例:某生產(chǎn)單位生產(chǎn)混合飼料,規(guī)定所含各種營養(yǎng)成份為:A(蛋白質(zhì))至少有15%,B(脂肪)至少含有4.5%,C(淀粉)至少含有30%,D(纖維素)不得超過10%。所能提供的原料有四種,甲(花生餅),每噸單價(jià)500元,含有各種營養(yǎng)成份分別為A25%、B2%、C10%、D2%;乙原料(花生秧),每噸單價(jià)為50元,四種營養(yǎng)成份的含量分別為A8%、B1%、C5%、D40%;丙原料(骨粉),每噸單價(jià)350元,含四種營養(yǎng)成份分別為A20%、B8%、C1%、D0.5%;丁原料(玉米),每噸單價(jià)為450元,含四種營養(yǎng)成份分別為A7%、B5%、C40%、D6%。求在符合飼料營養(yǎng)成份要求的前提下,如何配合這四種飼料,使飼料配合的費(fèi)用最低。
這是一項(xiàng)多項(xià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.選定“最小值”選項(xiàng)。
4.在“可變單元格”窗口中輸入D5:G5。
5.選取“添加”,出現(xiàn)“添加約束”窗口,在“添加約束”窗口輸入:
單元格引用位置 運(yùn)算符號 約束值
C7:C9 >= B7:B9
單擊“確定”,完成輸入。
按照上述操作步驟,再輸入以下兩條約束條件:
C10 <= B10
D5:G5 >= 0
6.在“規(guī)劃求解參數(shù)”窗口,選擇“求解”。
7.選擇“確定”,計(jì)算結(jié)果如下圖所示:
8.在“規(guī)劃求解結(jié)果”對話框中選定保存“規(guī)劃求解結(jié)果”,單擊“確定”。
結(jié)果顯示:滿足條件的飼料配方每噸用花生餅0.328849噸、花生秧0.135426噸、骨粉0.05747噸、玉米0.649423噸。每噸最低費(fèi)用483.55元。此結(jié)果經(jīng)過驗(yàn)證,與單純形法求解結(jié)果一致。
利用Excel2000進(jìn)行規(guī)劃求解一例
- 發(fā)表評論
- 我要糾錯(cuò)