信息技術(shù)應用用Excel解線性規(guī)劃問題舉例

1、EXCELEXCEL線性規(guī)劃求解線性規(guī)劃求解主要內(nèi)容主要內(nèi)容 Excel規(guī)劃求解功能的加載規(guī)劃求解功能的加載 建立線性規(guī)劃問題的建立線性規(guī)劃問題的excel模型模型 線性規(guī)劃的線性規(guī)劃的Excel求解過程求解過程 求解結(jié)果分析求解結(jié)果分析一、一、Excel規(guī)劃求解功能的加載規(guī)劃求解功能的加載1、打開、打開Excel 點擊點擊 “工具工具”菜單菜單在下拉菜單中選在下拉菜單中選擇擇“加載宏加載宏”；2、在彈出式菜單中勾選、在彈出式菜單中勾選“規(guī)劃求解規(guī)劃求解”，并點擊，并點擊“確確定定”，則規(guī)劃求解功能被加載，則規(guī)劃求解功能被加載(如果如果MS Office 2003未完全安裝，則需要插入安裝盤，

2、才能順利加載未完全安裝，則需要插入安裝盤，才能順利加載)；3、再次點擊、再次點擊 “工具工具”菜單，便發(fā)現(xiàn)下拉菜單中包含菜單，便發(fā)現(xiàn)下拉菜單中包含有有“規(guī)劃求解規(guī)劃求解”功能。功能。(注意：注意：MS Office 2007 加加載方法不同載方法不同:開始，選項，加載，選中線性規(guī)劃，開始，選項，加載，選中線性規(guī)劃，轉(zhuǎn)到轉(zhuǎn)到)二、建立線性規(guī)劃問題的二、建立線性規(guī)劃問題的excel模型模型p案例案例 開米開米(Kamei)是一家生產(chǎn)新型家用清潔產(chǎn)品的公司，為增加市場份額，是一家生產(chǎn)新型家用清潔產(chǎn)品的公司，為增加市場份額，管理層決定采用廣告集中宣傳一種噴霧去污劑和一種新型液體洗滌劑。管理層決定采用廣

3、告集中宣傳一種噴霧去污劑和一種新型液體洗滌劑。新型液體洗滌劑將在全國各電視臺做廣告推廣。印刷媒體廣告將同時被新型液體洗滌劑將在全國各電視臺做廣告推廣。印刷媒體廣告將同時被用于促銷兩種產(chǎn)品。管理部門已經(jīng)設(shè)定了廣告活動的最低目標：用于促銷兩種產(chǎn)品。管理部門已經(jīng)設(shè)定了廣告活動的最低目標：(1)噴霧噴霧去污劑市場份額再增加去污劑市場份額再增加%；(2)新型液體洗滌劑獲得新型液體洗滌劑獲得18%的市場份額。的市場份額。下表顯示了在各種媒體上做一單位廣告，相應的產(chǎn)品市場份額的估計增下表顯示了在各種媒體上做一單位廣告，相應的產(chǎn)品市場份額的估計增加額。表中最底行顯示了在每一種媒體上做廣告的單位成本。如何投放加

4、額。表中最底行顯示了在每一種媒體上做廣告的單位成本。如何投放廣告才能使宣傳費用最低。廣告才能使宣傳費用最低。x1為電視廣告投放量為電視廣告投放量x2為印刷媒體上投放量為印刷媒體上投放量去污噴劑22%4%x 新型液洗劑 123%2%18%xx決策變量決策變量 目標函數(shù)目標函數(shù) 12150200Max zxx約束條件約束條件非負約束 12,0 x x ，整數(shù)p建立線性規(guī)劃模型建立線性規(guī)劃模型p線性規(guī)劃的線性規(guī)劃的excel求解模型求解模型 (1) 在在excel電子表格中輸入如下內(nèi)容：電子表格中輸入如下內(nèi)容： (2) 確定用于確定用于表示變量表示變量的單元格，稱為的單元格，稱為可變單元格，可變單元

5、格，這里這里分別用分別用C7，D7表示表示x1和和x2。 (3) 確定用于確定用于表示原約束方程的左邊表示原約束方程的左邊的單元格，稱為的單元格，稱為輸出輸出單元格。單元格。這里分別用這里分別用E4，E5表示第一和第二個約束的左邊，表示第一和第二個約束的左邊，由于約束左邊決定于變量的取值，即決定于可變單元格由于約束左邊決定于變量的取值，即決定于可變單元格C7和和D7的值，所以的值，所以E4，E5取值決定于取值決定于C7，D7。p線性規(guī)劃的線性規(guī)劃的excel求解模型求解模型 在單元格在單元格E4和和E5中分別輸入：中分別輸入：E4 =C4*C7+D4*D7E5 =C5*C7+D5*D7 (4)

6、 確定用于確定用于表示目標函數(shù)值表示目標函數(shù)值的單元格，稱為的單元格，稱為目標單元目標單元格格，這里用，這里用E6表示。在表示。在E6輸入：輸入：E6 =C6*C7+D6*D7 注意：特別關(guān)注注意：特別關(guān)注C7，D7和和E6，所以將其背景刷灰，所以將其背景刷灰p線性規(guī)劃的線性規(guī)劃的excel求解模型求解模型注意注意: (1) 特別關(guān)注決策變量的取值以及目標函數(shù)值，所以特別關(guān)注決策變量的取值以及目標函數(shù)值，所以C7，D7和和E6，所以將其背景刷灰；，所以將其背景刷灰； (2) 單元格內(nèi)沒有任何輸入時，默認取值為單元格內(nèi)沒有任何輸入時，默認取值為0；單元格內(nèi)；單元格內(nèi)輸入輸入“=”表示單元格等于輸

7、入的數(shù)字、函數(shù)或公式；表示單元格等于輸入的數(shù)字、函數(shù)或公式； (3) 可采用可采用excel函數(shù)庫提供的函函數(shù)庫提供的函SUMPRODUCT ，來計，來計算上述約束左邊算上述約束左邊(E4,E5)及目標函數(shù)及目標函數(shù)(E6)中的相加相乘公式，中的相加相乘公式，如下述形式。當約束左邊或目標函數(shù)復雜時比較方便。如下述形式。當約束左邊或目標函數(shù)復雜時比較方便。三、線性規(guī)劃的三、線性規(guī)劃的Excel求解過程求解過程 在在Excel菜單欄中選擇菜單欄中選擇“工具工具/規(guī)劃求解規(guī)劃求解”，便會彈出，便會彈出“規(guī)劃求解參數(shù)規(guī)劃求解參數(shù)”對話框，如下圖所示。對話框，如下圖所示。三、線性規(guī)劃的三、線性規(guī)劃的Ex

8、cel求解過程求解過程p模型參數(shù)設(shè)置模型參數(shù)設(shè)置 在開始求解之前，需先在對話框中設(shè)置好各種參數(shù)，包在開始求解之前，需先在對話框中設(shè)置好各種參數(shù)，包括目標單元格、問題類型（求最大值還是最小值）、可變單括目標單元格、問題類型（求最大值還是最小值）、可變單元格以及約束條件等。元格以及約束條件等。 三、線性規(guī)劃的三、線性規(guī)劃的Excel求解過程求解過程p規(guī)劃求解選項規(guī)劃求解選項 在設(shè)置完模型參數(shù)之后，需要設(shè)置計算參數(shù)，點擊在設(shè)置完模型參數(shù)之后，需要設(shè)置計算參數(shù)，點擊“選選項項”按鈕，選擇運算參數(shù)。按鈕，選擇運算參數(shù)。三、線性規(guī)劃的三、線性規(guī)劃的Excel求解過程求解過程p求解求解 在設(shè)置各項參數(shù)后，點擊在設(shè)置各項參數(shù)后，點擊“求解求解” 按鈕。彈出如下窗按鈕。彈出如下窗體：體：