如何在Excel中建立并求解線性規(guī)劃模型

1、如何在Excel中建立并求解線性規(guī)劃模型劉桂蓮摘要：數(shù)學中線性規(guī)劃問題的求解一直是很繁瑣的，功能強大的 Excel軟件 為我們提供了一種很好的求解方法，但這種方法卻很少被人了解。本文就如何在 Excel中建立并求解線性規(guī)劃模型作了較詳盡的論述。關鍵詞：線性規(guī)劃數(shù)學模型電子表格模型規(guī)劃求解Excel線性規(guī)劃是運籌學的一個分支，它的應用已愈來愈深入到社會生產(chǎn)和經(jīng)濟活 動的各個領域。描述線性規(guī)劃問題的抽象的數(shù)學式子是線性規(guī)劃問題的數(shù)學模 型。建立數(shù)學模型后，求解滿足約束條件的目標函數(shù)的最優(yōu)解是解決線性規(guī)劃問 題的關鍵。數(shù)學中常用的方法是圖解法和單純形法，而圖解法只適用于兩個變量 的目標函數(shù)，單純形法

2、則計算量相當大，步驟煩瑣，容易出錯。在Excel中建立電子表格模型，并利用它提供的“規(guī)劃求解”工具，能輕松快捷地求解模型的解。例如，某玻璃制品公司有三個工廠，公司目前決定停止不贏利產(chǎn)品的生產(chǎn)并 撤出生產(chǎn)能力來生產(chǎn)兩種新開發(fā)的產(chǎn)品：玻璃門和雙把窗。估計三個工廠每周可 用來生產(chǎn)新產(chǎn)品的時間分別為 4小時、12小時、18小時，而每扇門需工廠1生 產(chǎn)時間1個小時和工廠3生產(chǎn)時間3個小時，每扇窗需工廠2和工廠3生產(chǎn)時間 各為2個小時，預測門的單位利潤是300元，窗的單位利潤是500元，問每周兩 種新產(chǎn)品數(shù)量的哪種組合能使總利潤最大？這是一個典型的產(chǎn)品組合問題，現(xiàn)將問題中的有關數(shù)據(jù)列表如下:工廠生產(chǎn)每一個

3、單位產(chǎn)品所需的時間每周用得時間門窗11小時04小時202小時12小時33小時2小時18小時單位利潤300元500元問題的決策變量有兩個：每周門的生產(chǎn)數(shù)量和窗的生產(chǎn)數(shù)量，目標是總利潤 最大，需滿足的條件是：三個工廠每周用于生產(chǎn)新產(chǎn)品的時間w每周可得時間每周門、窗的生產(chǎn)數(shù)量均0。設每周門的生產(chǎn)數(shù)量為X，窗的生產(chǎn)數(shù)量為y， 則該問題的數(shù)學模型即為：最大化利潤 P =300x+500y,約束條件：xw4, 2y0和 y0。將上表的有關數(shù)據(jù)輸入到 Excel中，建立如圖1所示的電子表格模型。被輸 入已知數(shù)據(jù)的單元格是數(shù)據(jù)單元格，如單元格 C5： D8，G5： G7。決策變量(即 兩種產(chǎn)品每周的生產(chǎn)量)放

4、在單元格C9和D9，正好定位在這些產(chǎn)品所在列的數(shù)據(jù)單元格下面，這種含有需要做出決策的單元格是可變單元格。單元格E5：E7是用來計算各個工廠每周的總生產(chǎn)時間，如單元格E5就是用C5： D5和C9:D9的對應數(shù)值各自相乘再總加得到。Excel中有一個叫SUMPRODUCT的函數(shù)能對相等行數(shù)和相等列數(shù)的兩個變化范圍的單元格中的值乘積后進行加和。被加和的每個值是對第一個變化范圍的一些值和對應位置的第二個變化范圍的一些 值的積。女口 E5=SUMPRODUCT(C5 : D5，C9： D9)是把C5： D5變化范圍的每個 值與C9 : D9變化范圍中對應的每個值相乘，然后各個積相加。同樣E6=SUMPR

5、ODUCT(C6 : D6, C9： D9)，E7=SUMPRODUCT(C7 : D7, C9： D9)，E5、E6、E7這些單元格的數(shù)值是依賴于可變單元格的，它們是輸出單元格。單 元格F5、F6、F7中的“W”符號表示它們左邊的總值不允許超過列G中的對應數(shù)值，體現(xiàn)了函數(shù)的約束條件。目標函數(shù)值（利潤）被放在E8單元格，正好在用來幫助計算總利潤的數(shù)據(jù)單元格右邊， 與列E中的其中它的數(shù)據(jù)相像，它也是 一些乘積的加和，E8=SUMPRODUCT（C8：D89：D9），E8是特殊的輸出單元格，是 顯示目標函數(shù)值的，是目標單元格。在沒有計算之前，可變單元格和輸出單元格 的數(shù)值均顯示為0。圖1：ABCD

6、EFG1玻璃制品公司的產(chǎn)品組合問題23生產(chǎn)單位產(chǎn)品的時間4門窗時間總數(shù)可得時間5工廠1100=46工廠2020=127工廠3320=188單位利潤30050009結論00Excel中有一個叫“規(guī)劃求解”的工具，能快速求解線性規(guī)劃問題，步驟如下：1在工具菜單中選擇“規(guī)劃求解”，會彈出一個對話框。在對話框中，將 E8 鍵入目標單元格，將C9： D9鍵入可變單元格，既然目標是要最大化目標單元格， 還必須選中“最大值” （Max）2 點擊對話框中的“添加”按紐（Add），彈出添加約束對話框，將約束條 件具體化。左端輸入范圍 E5： E7,右端輸入范圍G5： G7,中間的符號可選擇 “=”。如果還要添加

7、更多的函數(shù)約束就再點擊“添加”按鈕以彈出一個新的添 加約束對話框，在這個例子中沒有其它約束了。下一步只要點擊“確定”按鈕回 到“規(guī)劃求解”對話框。3點擊“選項”按鈕，在新彈出的對話框中，選中“采用線性模型”和“假 定非負”選項，這就告訴了計算機要求解的問題是一個線性規(guī)劃問題以及非負約 束，點擊“確定”再回到“規(guī)劃求解”對話框。4點擊“求解”按鈕，計算機會在后臺開始對問題進行求解。幾秒鐘之后 會顯示運行結果，一般而言，它會顯示已經(jīng)找到一個最優(yōu)解。如果模型沒有可行 解或沒有最優(yōu)解，對話框會顯示“規(guī)劃求解找不到可行解”。求解模型之后，最優(yōu)值就代替了可變單元格中的初始值，最優(yōu)解是每周2扇門和6扇窗，目

8、標單元格的對應數(shù)值（每周總利潤）為 3600元。求解的電子表格模型（圖2）如下：ABCDEFG1玻璃制品公司的產(chǎn)品組合問題23生產(chǎn)單位產(chǎn)品的時間4門窗時間總數(shù)可得時間5工廠1102=46工廠20212=127工廠332180 (i=1、2、3, j=1、2、3、4)電子表格模型如圖3:圖3ABCDEFGHI1嬰兒車的配送問題2到各配送中心的單位運輸成本312344工廠1506040205工廠2209010306工廠33040201078運送到各配送中心的單位數(shù)91234總量工廠產(chǎn)量10工廠100000=1211工廠200000=1712工廠300000=1113總量0000014=15中心接受

9、量10101010模型中的單元格C4: F6, C15: F15,以及110: 112分別輸入了單位運輸成 本，中心接受量及工廠產(chǎn)量的數(shù)值，它們是數(shù)據(jù)單元格。 12個決策變量(各工 廠送到各分配中心的單位數(shù))放在單元格 C10： F12中，它們是可變單元格，起 始值設置為0單元格 C13： F13 及 G10： G12 是輸出單元格 C13=SUMPRODUCT(C10 : C12),D13= SUMPRODUCT(D10 : D12),E13= SUMPRODUCT(E10 : E12),F13= SUMPRODUCT(E10 :E12),G1O=SUMPRODUCT(C1O :F10),G

10、11=SUMPRODUCT(C11: F11), G12=SUMPRODUCT(C12: F12), G13是目標單 元格(總成本)，它正好是數(shù)據(jù)單元格C4: F6和可變單元格C10: F12對應數(shù)值 相乘再加和得到即G13=SUMPRODUCT( C4: F6, C10: F12)，模型中的等號 體現(xiàn)了函數(shù)的約束。以上模型通過點擊“規(guī)劃求解”工具，因為是最小化目標單元格 G13,所以 選“最小值”(Min)；在“添加”約束里，添加“ G10: G12=I10: I12 ”和“ C13: F13=C15: F15”，仍然選“采用線性模型”和“假定非負”，得出如下結論：(圖 4)ABCDEFGH

11、I1嬰兒車的配送問題2到各配送中心的單位運輸成本312344工廠1506040205工廠2209010306工廠33040201078運送到各配送中心的單位數(shù)91234總量工廠產(chǎn)量10工廠10201012=1211工廠21007017=1712工廠3083011=1113總量1010101097014=15中心接受量10101010即工廠1運送到配送中心2和配送中心4分別為2和10個單位；工廠2運送到 配送中心1和配送中心3分別為10和7個單位；工廠3運送到配送中心2和配 送中心3分別8和3個單位，這樣總的運輸成本最低，最低成本是 970元。需要注意的是：用電子表格模型表述函數(shù)約束條件時，往往用到輸出單元格， 而輸出單元格必須使用 SUMPROD