實訓五利用EXCEL求解線性規劃問題注意：如果在【數據】菜單中沒有見到【規劃求解】命令，則要單擊【文件】【選項】【加載項】【轉到】命令，在彈出的對話框中的列表框中，選定【規劃求解加載項】選項前的復選框．【實訓目的】掌握使用EXCEL自定義函數進行數據處理掌握線性規劃問題模型建立掌握EXCEL求解線性規劃問題【實訓內容】一、線性規劃問題的三要素．1.有一組決策變量；2.有一個線性目標函數;3.有一組線性約束條件。線性規劃問題的數學模型可表示如下線性規劃問題的求解有很多方法，也有很多工具，比如常用的Matlab、Lingo、Excel等.Excel具有強大的規劃求解功能，可以解決最多有200個變量，100個外在約束和400個簡單約束（決策變量整數約束的上下邊界）的線性規劃與非線性規劃問題，本節主要講解如何使用Excel求解線性規劃問題.使用Excel求解線性規劃規劃求解加載宏是一組命令構成的一個子程序，其功能是可以求出線性和非線性數學規劃問題的最優解和最優值.使用規劃求解加載宏求解數學規劃的步驟：第一步：在Excel工作表中輸入目標函數的系數、決策變量、約束條件的系數、約束條件左端的值和約束條件右端的值（每一個單元格輸入一個數據）；第二步：選定一個單元格存儲目標函數，用定義公式的方式在這個目標單元格內定義目標函數；第三步：選定與決策變量個數相同的單元格（稱為可變單元格），用以存儲決策變量；再選擇與約束條件個數相同的單元格，用定義公式的方式在每一個單元格內計算出相應的約束函數（稱為約束函數單元格）；第四步：點擊規劃求解按鈕，打開規劃求解參數設定對話框，添加約束條件，完成規劃模型的設定.【實訓案例】案例1求解線性規劃問題：Excel演算步驟第一步：啟動Excel，在工作表中的A1，A2，A3，A10，E3，F3單元格中分別輸入文字“目標函數系數”，“決策變量”，“約束條件”，“目標函數值”，“約束條件左端的值”，“約束條件右端的值”；在B1，C1，D1單元格中輸入目標函數的系數1，-2，1，在B4，C4，D4單元格中輸入第一個約束條件的系數1，1，1；同理，在相應單元格中輸入其他約束條件的系數與約束條件右端的值，如下圖4-1所示：圖4-1數據輸入第二步：計算約束條件左端的值和目標函數值．因為約束條件左端的值等于約束條件的系數乘以相應的決策變量，所以在E4單元格中輸入公式“=B4*B2+C4*C2+D4*D2”，在E5單元格中輸入公式“=B5*B2+C5*C2+D5*D2”，依次類推在E9單元格中輸入公式“=B9*B2+C9*C2+D9*D2目標函數的值等于目標函數系數乘以決策變量，從而在D10單元格中輸入公式“=B1*B2+C1*C2+D1*D2”，如圖4-2圖4-2計算約束條件左端的值和目標函數值事實上，在計算約束條件左端值時，只需要在E4單元格中輸入公式“=B4*$B$2+C4*$C$2+D4*$D$2”，然后單擊E4單元格，將鼠標至于E4單元格右下角，當光標變為小黑十字時拖曳至E9單元格第三步：單擊【工具】菜單中的【規劃求解】命令，在彈出的規劃求解對話框中輸入各項參數．（1）設置目標單元格和可變單元格在“規劃求解參數”對話框中選中“最大值”前的單選按鈕，設置目標單元格為“$D$10”，可變單元格為“$B$2:$D$2”，如圖4圖4-3“規劃求解參數”對話框（2）添加約束條件單擊【規劃求解參數】對話框中的【添加】按鈕，打開【添加約束】對話框，單擊單元格引用位置文本框，然后選定工作表中的E4單元格，則在文本框中顯示“$E$4”，選擇“<=”約束條件；單擊約束值文本框，然后選定工作表中的F4單元格，如圖4-4所示．圖4-4“添加約束”對話框圖中所示約束條件表示，依此類推，把所有約束條件都添加到【規劃求解參數】對話框的【約束】列表框中．第四步：在“選擇求解方法中”點擊“單純線性規劃”，單擊【求解】按鈕，彈出圖4-5所示的【規劃求解結果】對話框，選中【保存規劃求解結果】單選按鈕．圖4-5“規劃求解結果”對話框第五步：在【規劃求解結果】對話框中，單擊【確定】按鈕，工作表中就顯示出規劃求解的結果，如圖4-6所示．圖4-6結果顯示如果要生成運算結果報告，可在【規劃求解】對話框中選擇【報告】列表框中的【運算結果報告】．單擊【確定】按鈕，則產生如圖4-7所示的運算結果報告表，在該表中對約束條件和結果作出了詳細的說明．圖4-7運算結果報告表從圖4-6或圖4-7可以很容易看出，當變量時，目標函數的最大值為．案例2某奶制品加工廠用牛奶生產兩種奶制品，1桶牛奶可以在設備甲上用12小時加工成3公斤，或者在設備乙上用8小時加工成4公斤．根據市場需求，生產的全部能售出，且每公斤獲利24元，每公斤獲利16元．現在加工廠每天能得到50桶牛奶的供應，每天正式工人總的勞動時間為480小時，并且設備甲每天至多能加工100公斤，設備乙的加工能力沒有限制．請為該廠制定一個生產計劃，使得工廠每天獲利最大．解決方案:1.模型建立第一步：根據實際問題，設置決策變量．設每天用桶牛奶生產，用桶牛奶生產.第二步：確定目標函數．設每天獲利元．桶牛奶可生產3公斤，獲利，桶牛奶可生產4公斤，獲利，故.第三步：分析各種資源限制，列出約束條件．原料供應：生產的原料(牛奶)總量不超過每天的供應，即桶；勞動時間：生產的總加工時間不超過總的勞動時間，即；設備能力：的產量不得超過設備甲每天的加工能力，即；非負約束：均不能為負值，即.第四步：綜合目標函數和各個約束條件，寫出整個線性規劃模型如下：2.Excel求解第一步：在工作表中的A14，A15，A16，E15，E16，A22單元格中分別輸入“目標函數系數”，“決策變量”，“約束條件系數”，“約束條件左端的值”，“約束條件右端的值”,“目標函數值”；在B14，C14單元格中輸入目標函數的系數72，64，在B16，C16單元格中輸入第一個約束條件的系數1，1；同理，在相應單元格中輸入其他約束條件的系數與約束條件右端的值，如下圖4-8所示：圖4-8數據輸入第二步：計算約束條件左端的值和目標函數值．在E16單元格中輸入公式“=B16*$B$15+C16*$C$15”，并使用句柄填充拖曳至E20單元格.在B22單元格中輸入公式“=B14*B15+C14*C15”，如圖4-9所示．圖4-9計算約束條件左端的值和目標函數值第三步：單擊【數據】菜單中的【規劃求解】命令，在彈出的規劃求解對話框中輸入各項參數．設置目標單元格和可變單元格在“規劃求解參數”對話框中選中“最大值”前的單選按鈕，設置目標單元格為“$B$22”，可變單元格為“$B$15:$C$15”，如圖4-10所示．圖4-10“規劃求解參數”對話框添加約束條件單擊【添加】按鈕，打開【添加約束】對話框，單擊單元格引用位置文本框，然后選定工作表中的E16至E18單元格，則在文本框中顯示“$E$16:$E$18”，選擇“<=”約束條件；單擊約束值文本框，然后選定工作表中的F16至F18單元格，如圖4-11所示．圖4-11“添加約束”對話框依此類推，把非負約束條件添加到約束列表框中．第四步：在【規劃求解結果】對話框中，單擊【確定】按鈕，工作表中就顯示出規劃求解的結果，如圖4-12所示．圖4-12“規劃求解結果”對話框從圖4-12可以很容易看出，當變量時，目標函數的最大值為．【實訓練習】實訓1某單位一次性購買了10本圖書，書名、數量、單價數據見表7-11：表7-11圖書單價及數量表書名單價數量書名單價數量大學語文18.320政治經濟學2813高等數學3420文學5.512化工與化學23.58自然地理197大學英語2015計算機3840寫作1830英語寫作13.524任務1：請利用EXCEL自定義函數計算出每本書的購買總價.任務2：請利用EXCEL自定義函數計算出購買平均價格、平均數量.實訓2某工廠用A、B兩種配件生產甲、乙兩種產品，每生產1件甲產品使用4個A配件耗時1h，每生產1件乙產品使用4個B配件耗時2h.該廠每天最多可從配件廠獲得16個A配件和12個B配件，按每天工作8h計算，若生產1件甲產品獲利2萬元，生產1件乙產品獲利3萬元，問工廠應如何安排生產，以使得總利潤最大？任務1：建立使總利潤最大的線性規劃模型.任務2：使用EXCEL求解該線性規劃模型.實訓3某農場每天需要使用一種特殊飼料800千克，這種特殊飼料由玉米和大豆粉配制而成，營養要求是至少含有30%的蛋白質和至多5%的纖維，具體成份見表7-12.表7-12飼料每千克飼料中含有成份總量（千克）費用（元/千克）蛋白質纖維玉米0.090.020.3大豆粉0.