Excel規劃求解：[3]解簡單的線性規劃應用題

電腦
關注：3.17W次

Excel的規劃求解工具有着很強大的功能，可以幫助我們解決線性規劃問題，運籌學方面等實際問題。本系列將以Excel2010為例詳細的介紹規劃求解的在各方面的應用。
上一篇講解了高中數學所涉及到的簡單的線性規劃如何使用“規劃求解”功能進行求解。本篇將開始使用“規劃求解”工具解決實際問題，從簡單的應用題為例開始講解“線性求解”工具在實際問題裏的運用。

操作方法

（01）某工廠要在A、B、C三條流水線上生產甲、乙兩種新產品。經測算，每生產一單位甲產品需要佔用流水線A的1工時，佔用流水線B的3工時；每生產一單位乙產品需要佔用流水線B的2工時，佔用流水線C的2工時。而流水線A、B、C每天可用於生產這兩種新產品的時間分別是4工時、18工時、12工時。已知一單位的甲產品的利潤為300元，一單位的乙產品的利潤為500元。問工廠應當如何安排這兩種新產品的生產計劃，以獲得最大的利潤？

（02）這個問題只有兩個變量，三個約束，可以較容易的列出約束條件，通過在平面直角座標系中畫圖求出最大利潤點。如果當新產品（變量）較多時，要這樣計算就會非常的困難。對於變量和約束條件較少的問題，在使用“規劃求解”工具計算時可以不用設出變量和列出約束條件，直接製作表格進行計算即可。

（03）首先我們還是需要設出生產產品甲、產品乙分別為x單位、y單位。對於需要求的最大值（即每日總利潤）可表示為z=300x+500y。接下來列出約束條件。第一個約束條件是流水線A每日可用工時的限制。即每種產品需要佔用流水線A的時間乘該產品的產量，最後加起來必須小於等於流水線A每日可用的工時。因此列式為x≤4（1x+0y≤4）。同理，第二個約束條件應為3x+2y≤18，第三個約束條件應為2y≤12（0x+2y≤12）。實際上還有一個非負的約束條件，只是在計算過程中很少用上，容易被忽略。即x≥0，y≥0。

（04）接下來就需要在Excel中建立模型，以使用“規劃求解”工具進行計算。建立表格如圖，與上一篇經驗一樣，為了便於理解，分別將已知條件，變量，目標值分別用藍色、橙色、綠色填充。

（05）現在需要在F3:F5區域補齊約束條件，即分別錄入約束條件中前三個不等式中不等號左邊的內容。以F3單元格為例，約束條件是x≤4（1x+0y≤4），就要在F3單元格內錄入“1x+0y”。可以看到D3和E3單元格的數據分別是“1”和“0”，而x和y所對應的單元格是D7和E7。這裏使用對應相乘後求和的SUMPRODUCT函數，在F3單元格輸入公式“=SUMPRODUCT(D3:E3,D7:E7)”，即D3*D7+E3*E7。

（06）對於F4和F5單元格，可以使用F3單元格直接向下拖動填充。但是必須要注意的是，D7:E7區域需要添加絕對引用，避免拖動填充的時候，這個區域隨拖動而變動。因此將F3單元格的公式改為“=SUMPRODUCT(D3:E3,$D$7:$E$7)”後對下面的單元格進行填充。

（07）還需要輸入公式的是目標值H9單元格，對於目標函數z=300x+500y，需要在H9單元格輸入“300x+500y”部分。D9和E9單元格的數據分別是“300”和“500”，變量x和y的值分別在D7和D9單元格，依然使用SUMPRODUCT函數，在H9單元格輸入公式“=SUMPRODUCT(D7:E7,D9:E9)”。

（08）運行“規劃求解”工具，如圖分別選擇各項數據後點擊“求解”。