Excel線性規(guī)劃求解農場最大利潤

1、姓名： 學號：200900609 專業(yè)：資源環(huán)境與城鄉(xiāng)規(guī)劃管理已知：某地有三個農場共用一條灌渠，每個農場的可灌溉耕地及可分配的最大用水量如表1：表1：各農場可灌溉耕地及允許最大用水量農場序號可灌溉耕地（畝）可分配水量（百方）140060026008003300375各農場均可種植甜菜、棉花及高粱三種作物，各作物的用水量、凈收益以及國家規(guī)定的該地區(qū)各種作物種植總面積最高限額如表2：表2：各種作物需水量、凈收益及種植額限作物種類種植限額（畝）耗水量（百方/畝）凈收益（元/畝）甜菜6003400棉花5002300高粱3251100三個農場達成協(xié)議，他們的播種面積與其可灌溉面積之比應該相等，各農場種何

2、種作物并無限制。如何制定各農場種植計劃才能在上述條件下，是本地區(qū)的三個農場的總凈收益最大。解：1、首先新建Excel表格，加載規(guī)劃求解加載宏，“工具”“加載宏”，選中“規(guī)劃求解”復選框，單擊確定即可。2、在表中輸入已知數(shù)據，如下圖所示：3、設各農場三種作物的種植面積分別為x1、x2、x3、x4、x5、x6、x7、x8、x9，根據已知條件可知目標函數(shù)總利潤為：Y= 400*(x1+x4+x7)+300*(x2+x5+x8)+100*(x3+x6+x7)4、由已知條件可知約束條件為：3*x1+2*x2+x3=600 約束農場1最大用水量3*x4+2*x5+x6=800 約束農場2最大用水量3*x7

3、+2*x8+x9=375 約束農場3最大用水量x1+x4+x7=600 約束甜菜種植限額x2+x5+x8=500 約束棉花種植限額x3+x6+x9=325 約束高粱種植限額x1+x2+x3=400 約束農場1耕地面積x4+x5+x6=600 約束農場2耕地面積x7+x8+x9=0 約束各耕地面積有意義5、在對應單元格編輯相關函數(shù)：如上圖右下角表格所示，依次進行對應單元格相關函數(shù)的編輯。總計面積=SUM(G10:G12)、SUM(H10:H12)、SUM(I10:I12)總計=SUM(G10:I10)、SUM(G11:I11)、SUM(G12:I12) 農場所需水量=3*G10+2*G11+G1

4、2、3*H10+2*H11*H12、3*I10+2*I11+I12 耕地比例=G13/400、H13/600、I13/300 利潤=400*J10+300*J11+100*J126、進行規(guī)劃求解：“工具”“規(guī)劃求解”，彈出如下對話框： 設置目標單元格為G16(利潤對應單元格)； 設置目標單元格為G10I12,共九個；單擊“添加”按鈕，進行約束條件的添加 其中“單元格引用位置”為編輯的函數(shù)所對應的單元格：例如編輯第一個約束條件：3*x1+2*x2+x3=600 約束農場1最大用水量則“單元格引用位置”選取單元格G14即可（因為單元格G14對應的函數(shù)為：=3*G10+2*G11+G12）。其它約束條件的添加類似。注：(x1+x2+x3)/400=(x4+x5+x6)/600=(x7+x8+x9)/300 約束各耕地面積比例此約束條件的添加可如下設置： G15=H15；（農場1使用耕地比例=農場2使用耕地比例） G15=I15；（農場1使用耕地比例=農場3使用耕地比例）添加所有的約束條件后，單擊“求解”按鈕，Excel將自動進行線性求解計算，并得出結果，確定保存即可。如下圖示：至此，結果已經得出，有表格可知三個農場的最大總利潤為25.