第一章用Excel求解規劃問題Excel具有內置的規劃求解功能。MicrosoftExcel的“規劃求解”工具取自德克薩斯大學奧斯汀分校的LeonLasdon和克里夫蘭州立大學的AllanWaren共同開發的GeneralizedReducedGradient(廣義既約梯度法)非線性最優化代碼。線性和整數規劃問題取自FrontlineSystems公司的JohnWatson和DanFylstra提供的有界變量單純形法和分支邊界法。簡介第一章用Excel求解規劃問題1、啟動Excel；2、Tools－》Add－Ins3、選中SolverAdd－in開啟“Solver”Office07在excel選項-》加載項，點選“轉到”，選中“規劃求解”，然后在“數據”=》分析第一章用Excel求解規劃問題4、重新打開Tools菜單；5、選中Solver即可。開啟“Solver”第一章用Excel求解規劃問題例：某工廠要生產兩種產品：門和窗；每扇門需要在車間1加工1小時，車間3加工3小時；每扇窗需要在車間2加工2小時，車間3加工2小時；車間用于生產這兩種產品的時間：車間1為4小時，車間2為12小時，車間3為18小時；每扇門的利潤300元，窗的利潤500元；如何確定產品周生產計劃，使總利潤最大？入門：一個簡單的例子第一章用Excel求解規劃問題規劃模型：x1＝門生產量；x2=窗生產量入門：一個簡單的例子Maxz=300*x1+500*x2s.t.X1<=4;2*X2<=12;3*x1+2*x2<=18;x1,x2>=0;第一章用Excel求解規劃問題打開Excel，輸入模型參數的常量部分：上面是目標系數，下面是約束（黃色的是常量）；入門：一個簡單的例子Examp01-01.xls第一章用Excel求解規劃問題然后輸入模型參數的變量相關部分：即決策變量、與決策變量相關的量，如總利潤和實際使用工時（藍色是變量部分）；入門：一個簡單的例子第一章用Excel求解規劃問題選“Solver”，輸入如下參數：入門：一個簡單的例子第一章用Excel求解規劃問題點擊Options，選擇“Assumelinearmodel”和“Assumenon－linear”。點擊【OK】，【solve】：入門：一個簡單的例子第一章用Excel求解規劃問題在彈出的“SolverResults”中，選擇“Reports”中的“Answer”,選擇【OK】。入門：一個簡單的例子第一章用Excel求解規劃問題結果如下：入門：一個簡單的例子第一章用Excel求解規劃問題同時，決策變量的最優值以及和決策變量相關變量的值也自動被填充。入門：一個簡單的例子第一章用Excel求解規劃問題靈敏度分析能夠為決策提供有用的信息，能夠幫助管理者進行正確的決策。例如，模型中的有些系數（例如上例中的可用工時）被認為是確定的，優化也在此基礎上進行。但是，實際情況中，環境變化可能使系數發生變化，而這些變化可能又影響當前的最優解。因此，需要研究最優解對系數變化的反應程度，以適應各種偶然變化。用Excel進行靈敏度分析第一章用Excel求解規劃問題回顧上節的例子：用Excel進行靈敏度分析某工廠要生產兩種產品：門和窗；每扇門需要在車間1加工1小時，車間3加工3小時；每扇窗需要在車間2加工2小時，車間3加工2小時；車間用于生產這兩種產品的時間：車間1為4小時，車間2為12小時，車間3為18小時；每扇門的利潤300元，窗的利潤500元；如何確定產品周生產計劃，使總利潤最大？最優解：x1=2;x2=6,最大利潤3600。第一章用Excel求解規劃問題那么，考慮以下的問題：用Excel進行靈敏度分析如果門的利潤由300提高到400，最優解是否改變？對總利潤有怎樣的影響？如果車間1的可用工時增加2個小時，總利潤是否變化？如何變化，最優解是否發生變化？如果車間2更新工藝，生產一扇窗由以前的2小時下降到1小時，最優解如何變化？如果工廠新增加用電限制，是否會改變原來的最優方案？。。。第一章用Excel求解規劃問題用Excel進行靈敏度分析用Excel進行靈敏度分析最簡單的方法，就是修改參數并重新進行求解，但逐個嘗試工作量很大；Excel提供了敏感性報告的功能，可以利用其結果方便的進行靈敏度分析。第一章用Excel求解規劃問題用Excel進行靈敏度分析上例的靈敏度分析結果：第一章用Excel求解規劃問題用Excel進行靈敏度分析上例的可變單元格的靈敏度分析結果：最優解當該非基變量增加一個單位時目標函數減少的量(對max型問題)目標系數（已知）目標函數在允許的增量范圍內變化時，最優解不變目標函數在允許的遞減范圍內變化時，最優解不變故c1的范圍（300－300，300＋450）；c2的范圍（500－300，500＋無窮）；第一章用Excel求解規劃問題用Excel進行靈敏度分析上例的靈敏度分析結果：如果保證最優解不變，那么故車間1工時的范圍（4－2，4＋無窮）；故車間1工時的范圍（12-6,12+6）;故車間3工時的范圍(18-6,18+6);終值－工時實際使用量約束限制值－不等式右邊的常量允許的增量(或漸量)：右端值在允許的增量和漸量范圍內變化時，影子價格不變(但最優解可能變化)。影子價格，表示約束右端值每增加和減少1時，目標函數值的相應增量或漸量第一章用Excel求解規劃問題用Excel進行靈敏度分析影子價格第一個約束：增加車間1一個單位的可用工時，總利潤并不變（因為時間使用工時2小時小于可用工時4小時）；第二個約束：在允許的范圍內(12-6,12+6)，增加車間2一個單位的可用工時，將增加利潤150（因為車間2的可用工時都用來生產了產品），這對管理者很有決策的意義。第三個約束：。。。第一章用Excel求解規劃問題用Excel進行靈敏度分析影子價格的經濟意義：影子價格實際上是一種機會成本。當資源的價格低于影子價格時，可買進該資源，反之可賣出。隨著資源變化，影子價格也隨之變化。當影子價格與市場價格相同時，才處于平衡狀態；當影子價格＝0，表示該資源未得到充分利用；否則表示資源在生產中已全部利用；在公司內部，可借助資源的影子價格確定內部結算價格，以控制有限資源的使用，使有限資源得到更大的經濟效益。第一章用Excel求解規劃問題練習：文具生產方案和臨時工估計某廠利用原材料白坯紙生產稿紙、日記簿和練習本三種產品；現有工人100人，白坯紙的日供應量30000公斤；若單獨生產產品，工人每日可生產稿紙30捆、日記簿30打、練習本30箱；資源消耗情況：每捆稿紙用白坯紙10/3公斤,每打日記簿用白坯紙40/3公斤,每箱練習本用白坯紙80/3公斤；產品盈利情況：每捆稿紙1元，每打日記簿2元，每箱練習本3元問題1：每天生產稿紙、日記簿和練習本各多少（可取小數），利潤才最好？問題2：如果工人數量不足，可招臨時工，其費用每天每人15元，問在目前的條件下，該廠是否需要招臨時工？如果招臨時工，那么招多少才好？第一章用Excel求解規劃問題規劃求解中的參數和設置SetTargetCell(設置目標單元格)：一些單元格、具體數值、運算符號的組合。注意：目標單元格一定要是公式，即一定是以“=”開始。類似于線性規劃中的目標函數。第一章用Excel求解規劃問題規劃求解中的參數和設置Max,Min,Valueof(最大值、最小值、給定值)：在此指定是否希望目標單元格為最大值、最小值或某一特定數值。如果需要指定數值，請在右側編輯框中鍵入該值;ByChangingcells(可變單元格)：在此指定可變單元格。求解時其中的數值不斷調整，直到滿足約束條件并且“設置目標單元格”框中指定的單元格達到目標值。可變單元格必須直接或間接地與目標單元格相關聯。類似于線性規劃中的變量。第一章用Excel求解規劃問題規劃求解中的參數和設置Guess(推測)：單擊此按鈕，自動推測“設置目標單元格”框中的公式所引用的所有非公式單元格，并在“可變單元格”框中定位這些單元格的引用。SubjecttotheConstraints(約束)：列出了規劃求解的所有約束條件。Add(添加)：顯示“添加約束”對話框。Change(更改)：顯示“更改約束”對話框。注意：單擊此按鈕的時候，要先選擇需要更改的約束。第一章用Excel求解規劃問題規劃求解中的參數和設置Delete(刪除)：刪除選定的約束條件。同樣單擊此按鈕前，要先選擇需要刪除的約束。Solve(求解)：對定義好的問題進行求解。Close(關閉)：關閉對話框，不進行規劃求解。但保留通過“選項”、“添加”、“更改”或“刪除”按鈕所做的更改。也就是說，當你下次再次單擊“規劃求解”按鈕后，對話框顯示上回所設置的參數。第一章用Excel求解規劃問題規劃求解中的參數和設置Options(選項）：顯示“規劃求解選項”對話框。在其中可加載或保存規劃求解模型，并對求解過程的高級屬性進行控制。下面詳細介紹選項中的各項設置的具體含義。第一章用Excel求解規劃問題規劃求解中的參數和設置MaxTime(最長運算時間)：在此設定求解過程的時間。可輸入的最大值為32767（秒），默認值100（秒）可以滿足大多數小型規劃求解要求。Durations(迭代次數)：在此設定求解過程中迭代運算的次數，限制求解過程的時間。可輸入的最大值為32767，默認值100次可滿足大多數小型規劃求解要求。Precision(精度)：在此輸入用于控制求解精度的數字，以確定約束條件單元格中的數值是否滿足目標值或上下限。精度值必須表示為小數（0到1之間），輸入數字的小數位越多，精度越高。第一章用Excel求解規劃問題規劃求解中的參數和設置Tolerance(允許誤差)：在此輸入滿足整數約束條件并可被接受的目標單元格求解結果與真實的最佳結果間的百分偏差。這個選項只應用于具有整數約束條件的問題。設置的允許誤差值越大，求解過程就越快。Convergence(收斂度)：在此輸入收斂度數值，當最近五次迭代后目標單元格中數值的變化小于“收斂度”框中設置的數值時，“規劃求解”停止運行。收斂度只應用于非線性規劃求解問題，并且必須表示為小數（0到1之間）。設置的數值越小，收斂度就越高。例如，0.0001表示比0.01更小的相對差別。收斂度越小，“規劃求解”得到結果所需的時間就越長。第一章用Excel求解規劃問題規劃求解中的參數和設置AssumeLinearModel(采用線性模型)：當模型中的所有關系都是線性的，并且希望解決線性優化問題時，選中此復選框可加速求解進程。AssumeNon-Negative(假定非負)：如果選中此復選框，則對于在“添加約束”對話框的“約束值”框中沒有設置下限的所有可變單元格，假定其下限為0（零）。UseAutomaticScaling(自動按比例縮放)：如果選中此復選框，當輸入和輸出值量級差別很大時，可自動按比例縮放數值。例如，基于百萬美元的投資將利潤百分比最大化。第一章用Excel求解規劃問題規劃求解中的參數和設置ShowDurationResults(顯示迭代結果)：如果選中此復選框，每進行一次迭代后都將中斷“規劃求解”，并顯示當前的迭代結果。Estimates(估計)：指定在每個一維搜索中用來得到基本變量初始估計值的逼近方案。正切函數：使用正切向量線性外推二次方程：用二次方程外推法，提高非線性規劃問題的計算精度。第一章用Excel求解規劃問題規劃求解中的參數和設置Derivatives(導數)：指定用于估計目標函數和約束函數偏導數的差分方案。Forward(向前差分)：用于大多數約束條件數值變化相對緩慢的問題。Center(中心差分)：用于約束條件變化迅速，特別是接近限定值的問題。雖然此選項要求更多計算，但在“規劃求解”不能返回有效解時也許有幫助。Search(搜索)：指定每次的迭代算法，以確定搜索方向。牛頓法：用準牛頓法迭代需要的內存比共軛法多，但所需的迭代次數少。共軛法：比牛頓法需要的內存少，但要達到指定精度需要較多次的迭代運算。當問題較大和內存有限，或步進迭代進程緩慢時，可用此選項。第一章用Excel求解規劃問題規劃求解中的參數和設置LoadModel(裝入模型)：顯示“裝入模型”對話框，輸入對所要加載的模型的引用。SaveModel(保存模型)：顯示“保存模型”對話框，在其中可指定保存模型的位置。只有需要在工作表上保存多個模型時，才單擊此命令。第一個模型會自動保存。第一章用Excel求解規劃問題用Excel求解整數規劃用Excel求解整數規劃的步驟與一般線性規劃問題相同，只是在約束條件中添加一個“整數”約束。在Excel的“添加約束”對話框中，用int表示整數，因此，只要在該參數對話框中添加一個約束條件，在左面輸入要求取整的決策變量的單元格地址，然后選擇“int”即可。第一章用Excel求解規劃問題用Excel求解整數規劃例：某公司的新產品選擇問題。某公司開發了三種新產品，相關數據如表。管理層決定，選擇其中兩種產品進行生產，且兩個工廠中必須選出一個專門生產新產品，那么如何選擇產品、工廠并確定各種產品的周產量，使得總利潤最大。第一章用Excel求解規劃問題用Excel求解整數規劃決策變量：

x1,x2,x3:產品1，2，3的每周產量；

y1,y2,y3（0-1量）:y1=1表示生產產品1，=0表示不生產；

y2=1表示生產產品2，=0表示不生產；

y3=1表示生產產品3，=0表示不生產；

y（0-1量）:y=0表示選擇工廠1，=1表示選擇工廠2；約束1:選擇其中兩種產品進行生產y1+y2+y3<=2;x1<=M*y1;x2<=M*y2;x3<=M*y3;(M為大數)

（x1如果非零，那么y1必須為1）第一章用Excel求解規劃問題用Excel求解整數規劃約束2:兩個工廠中必須選出一個專門生產新產品3*x1+4*x2+2*x3<=30+M*y;(y如果等于1，即選擇工廠2，則不等式無效)4*x1+6*x2+2*x3<=40+M*(1-y);約束3：周產量受到周可銷售量的限制x1<=7;x2<=5;x3<=9;考慮x1<=M*y1;x2<=M*y2;x3<=M*y3;(M為大數)因為每種產品的周最大產量就是周可銷售量，因此二者可合并為

x1<=7*y1;x2<=5*y2;x3<=9*y3約束4：變量類型約束x1,x2,x3>=0,y1,y2,y3=1or0,y=1or0;第一章用Excel求解規劃問題用Excel求解整數規劃綜上，該問題的整數規劃模型如下：Maxz=5*x1+7*x2+3*x3s.t.y1+y2+y3<=2;x1<=7*y1;x2<=5*y2;x3<=9*y3;3*x1+4*x2+2*x3<=30+M*y;4*x1+6*x2+2*x3<=40+M*(1-y);x1,x2,x3>=0;y1,y2,y3=1or0;y=1or0;第一章用Excel求解規劃問題用Excel求解整數規劃建立Excel規劃模型：常量(黃色);決策變量(綠色);中間變量(藍色)Examp01-02.xls第一章用Excel求解規劃問題用Excel求解整數規劃建立Excel規劃模型：常量(黃色);決策變量(綠色);中間變量(藍色)注意：SUMPRODUCT的用法即兩個矩陣各個相對應元素乘積的和。第一章用Excel求解規劃問題用Excel求解整數規劃建立Excel規劃模型：常量(黃色);決策變量(綠色);中間變量(藍色)第一章用Excel求解規劃問題用Excel求解整數規劃Options：Assum