EXCEL规化求解.pptVIP

National Yunlin University of Science and Technology Microsoft Excel – 規劃求解工具 非線性最佳化程式碼 Leon Lasdon, University of Texas at Austin Allan Waren, Cleveland State University EXCEL規劃求解問題 線性規劃 - 單形法 整數規劃 - 分支界定法 EXCEL最多僅能提供200個決策變數 EXCEL能求得最佳解嗎? EXCEL以「動態資料交換」作為連繫，在楚理過程中不斷將新資料送達工作表中調整儲存格，並令其重新計算，再回傳到「規劃求解」 由於規劃求解是以數值分析的方式來解最佳化模式，所得的解並非精確的解 規劃求解只能於初始值給定的範圍附近求解，故確保所得解為最佳，應嘗試以不同的起始值來求解 Excel 「規劃求解」之啟用 工具 - 增益集 - 規劃求解 EXCEL規劃求解三要素 目標儲存格 我們的要求就是要讓目標儲存格最小化或最大化 變數儲存格 變數儲存格就是我們可以進行變更或調整，藉以最佳化目標儲存格的試算表儲存格 限制式 限制式就是您對變數儲存格所設定的限制 相關名詞定義 範例實作 - 1 利用規劃求解下列方程式 2*X = 4 進行求解步驟 求解報表 – 運算結果報表 運算結果 – 敏感度報表 運算報表 – 極限值報表 範例實作 – 2 利用規劃求解最小值問題 X^2 - 2*X - 3 進行求解步驟 範例實作 – 3 假設 X為生產A產品數量 Y為生產B產品數量 Z為最大利潤 目標函數 Max Z = 10X + 8Y 限制式 4X + 2Y ≦ 80 2X + 4Y ≦ 100 X ≧ 0 Y ≧ 0 EXCEL參數輸入畫面 進行求解步驟 求解結果 運算報表 解除整數限制後的運算結果報表 極限報表 敏感度分析 – 非線性 敏感度分析 – 線性 規劃求解與回歸研究範例 步驟1 步驟2 步驟3 步驟4 步驟5 步驟6 步驟7 步驟8 步驟9 * * 規劃求解 規劃求解 規劃求解 規劃求解 規劃求解 可輸入最大值為32,767秒 限制中間運算次數的方式 決定限制式儲存格的數值，是否滿足目標值上限或下限，精確度在0~1之間 規劃求解 規劃求解 規劃求解 規劃求解 規劃求解 規劃求解 規劃求解 規劃求解 規劃求解 =B5*B4+C5*C4 =B5*B2+C5*C2 =B5*B3+C5*C3 規劃求解 規劃求解 總利潤 = 260 產品A和B應生產數量 規劃求解 規劃求解 Binding(達到限制值) No Binding(未達到限制式) Slack(墊數值) 規劃求解 下限：能夠適合其他變數儲存格，又能滿足限制式的變數儲存格之最小值。 上限：能夠適合其他變數儲存格，又能滿足限制式的變數儲存格之最大值。 規劃求解 拉格蘭茲乘數(Lagrange Multiplier) 規劃求解 影子價格(Shadow Price) 規劃求解 回歸分析資料 變數儲存格 回歸方程式 規劃求解 =$J$3+$J$4*B4+$J$5*C4+$J$6*E4 規劃求解 =$J$3+$J$4*B4+$J$5*C4+$J$6*E4 依序往下拉 給定變數儲存格起始值 規劃求解 =SQRT(SUMXMY2(F4:F78,G4:G78)/75) SQRT：傳回數值的正平方根 =SQRT(16) = 4 兩個陣列中對應數值差的平方和 規劃求解 工具 - 規劃求解 規劃求解 規劃求解 =AVERAGE(F4:F78) * * Graduate School of Construction Engineering 簡報者：楊宏達 任課老師：郭雅玲 作業研究-EXCEL相關講解 *