excel規(guī)劃求解實(shí)例

1、2014 年高教社杯全國(guó)大學(xué)生數(shù)學(xué)建模競(jìng)賽校內(nèi)選拔賽2013 年12月2日關(guān)于水泥廠生產(chǎn)及運(yùn)輸方案的最優(yōu)化求解摘要摘要內(nèi)容 ：本論文主要討論四個(gè)水泥廠往五個(gè)城市提供水泥的生產(chǎn)運(yùn)輸最優(yōu)化問(wèn)題。根據(jù)給出的條件， 做出合理的分析， 通過(guò)建立數(shù)學(xué)模型以及利用電腦軟件 Microsoft excel2003 輔助，求出 2012 年的水泥生產(chǎn)成本，并根據(jù)各地不同的生產(chǎn)成本以及超出需要額外投資的成本， 規(guī)劃求解得出在資源限制范圍內(nèi)最優(yōu)的生產(chǎn)運(yùn)輸方案以及所需要的最低費(fèi)用。關(guān)鍵詞：回歸方程；目標(biāo)函數(shù)；數(shù)學(xué)模型；線性規(guī)劃求解。一、問(wèn)題重述某水泥有限公司現(xiàn)有 4 個(gè)水泥廠，這 4 個(gè)廠生產(chǎn)的水泥都銷(xiāo)往附近的 AB

2、CDE 這 5 個(gè)城市，而這 5 個(gè)城市今年的需求量分別為 110 萬(wàn)噸， 160 萬(wàn)噸， 80 萬(wàn)噸， 200 萬(wàn)噸和 100 萬(wàn)噸。已知資源消耗系數(shù)為 2.5 ，每噸產(chǎn)品的運(yùn)輸費(fèi)用見(jiàn)表一，表二提供了一些其他供參考的數(shù)據(jù)， 表三提供了最近十年這 4 家水泥廠生產(chǎn)每噸水泥的生產(chǎn)成本 ( 萬(wàn)元 ) 。問(wèn)題：請(qǐng)你根據(jù)給定的數(shù)據(jù)設(shè)計(jì)出最優(yōu)的生產(chǎn)及運(yùn)輸方案， 并給該水泥公司管理層寫(xiě)一個(gè)報(bào)告。A 城市B城市C城市D城市E城市水泥廠 18013012030140水泥廠 210060703090水泥廠 34090605040水泥廠 4705090120100表一：每噸水泥的運(yùn)輸費(fèi)用 ( 單位：元 )年生平均

3、每提高 1資源限制產(chǎn)能力(萬(wàn)噸產(chǎn)量所需額(萬(wàn)噸)噸)外投資(萬(wàn)元)水泥廠 11001.4500水泥廠 21500.8800水泥廠 31201.1600水泥廠 41301.5700表二：一些其他供參考的數(shù)據(jù)水泥廠 1水泥廠 2水泥廠 3水泥廠 42011 年1.1231.1690.9001.2442010 年1.0960.9890.9501.0502009 年1.0370.9700.9351.0652008 年0.9950.9590.9201.0242007 年0.9900.9530.9001.0222006 年0.9600.9250.8570.9802005 年0.9360.7580.8150

4、.8992004 年0.8820.7440.7050.8922003 年0.8770.6600.6880.7642002 年0.8430.6220.6840.721表三： 4 家水泥廠的生產(chǎn)成本 ( 萬(wàn)元 / 噸 )注：資源限制是指產(chǎn)地資源的擁有量；資源消耗系數(shù)是指生產(chǎn)單位產(chǎn)品所需消耗的資源數(shù)。二、問(wèn)題分析問(wèn)題中給出最近幾年各個(gè)水泥廠生產(chǎn)成本，由回歸方程可得到每個(gè)水泥廠2012 年的生產(chǎn)成本。設(shè)2012 年每個(gè)水泥廠生產(chǎn)成本分別為W1,W2,W3,W4。四個(gè)水泥廠運(yùn)往五個(gè)城市， 需要的運(yùn)費(fèi)各不相同。 并且各個(gè)水泥廠的生產(chǎn)成本各不相同。超出年生產(chǎn)能力之后生產(chǎn)每噸水泥需要的額外成本也不一樣， 所以

5、本題需要設(shè)兩個(gè)主要的函數(shù)， 分別為年生產(chǎn)能力之內(nèi)每個(gè)水泥廠運(yùn)往每個(gè)地方的水泥數(shù)量，以及年生產(chǎn)能力之外每個(gè)水泥廠運(yùn)往每個(gè)地方的水泥數(shù)量。設(shè)四個(gè)水泥廠的代號(hào)為 A1，A2， A3，A4，五個(gè)城市的代號(hào)為 B1，B2，B3，B4，B5，設(shè)產(chǎn)能之內(nèi)各個(gè)水泥廠運(yùn)往每個(gè)城市的水泥噸位為 Xij ，產(chǎn)能之外各個(gè)水泥廠運(yùn)往每個(gè)城市的水泥為 Yij 。則總費(fèi)用即為運(yùn)輸量乘以運(yùn)費(fèi)與生產(chǎn)成本的和。由資源消耗系數(shù)以及資源限制量可求的四個(gè)水泥廠可生產(chǎn)的水泥最多分別為 200 萬(wàn)噸 ,320 萬(wàn)噸 ,240 萬(wàn)噸 ,280 萬(wàn)噸 . 各自減去產(chǎn)能可得到超出生產(chǎn)能力后每個(gè)水泥廠分別最多可以再生產(chǎn) 100 萬(wàn)噸 ,170 萬(wàn)

6、噸 ,120 萬(wàn)噸 ,150 萬(wàn)噸水泥。再根據(jù)每個(gè)水泥廠的產(chǎn)能限制以及運(yùn)費(fèi)和各地的需求量列出等式和不等式組，利用線性規(guī)劃求出總費(fèi)用的最小值。三、模型假設(shè)及符號(hào)說(shuō)明3.1 模型假設(shè)由每個(gè)水泥廠運(yùn)往每個(gè)城市的水泥量， 以及使得其滿足費(fèi)用最小化的約束條件，建立成一個(gè)模型。3. 2 符號(hào)說(shuō)明A1 ， A2，A3，A4代表四個(gè)水泥廠， B1， B2，B3， B4，B5 代表五個(gè)城市。 Xij 為產(chǎn)能之內(nèi)每個(gè)工廠向各地運(yùn)輸?shù)乃嗔俊?Yij 為產(chǎn)能之外每個(gè)水泥廠往各地運(yùn)輸?shù)乃嗔俊?W1,W2,W3,W4表示 2012 年各個(gè)水泥廠的生產(chǎn)成本 （萬(wàn)元 / 噸）。設(shè)所需要的總費(fèi)用為 M，四個(gè)水泥廠按照題干要

7、求的生產(chǎn)總成本為 Z，四個(gè)水泥廠的運(yùn)輸總費(fèi)用為 Y。則 M=Z+Y目.的是求使得 M為最小值的生產(chǎn)運(yùn)輸方案。四、模型的建立與求解4.1 模型的建立這里統(tǒng)一單位為萬(wàn)元/ 噸年產(chǎn)能之內(nèi)的生產(chǎn)運(yùn)輸量水泥廠B1B2B3B4B5年生產(chǎn)能力AX11X12X13X14X1510000001AX21X22X23X24X2515000002A3X31X32X33X34X351200000A4X41X42X43X44X451300000年產(chǎn)能之外的生產(chǎn)運(yùn)輸量資源限制A1Y11Y12Y13Y14Y151000000A2Y21Y22Y23Y24Y251700000A3Y31Y32Y33Y34Y351200000A4Y

8、41Y42Y43Y44Y451500000需求量1100000160000080000020000001000000（噸）每個(gè)水泥廠運(yùn)往每個(gè)城市的水泥成本為運(yùn)費(fèi)加上生產(chǎn)成本。根據(jù)計(jì)算出的2012年生產(chǎn)成本以及運(yùn)費(fèi)可以列出一下表格水泥廠2012 年產(chǎn)能之內(nèi)2012 年產(chǎn)能之外每噸所需2012 年產(chǎn)能之生產(chǎn)成本（萬(wàn)元噸）額外投資（萬(wàn)元噸）外總成本（萬(wàn)元噸）AW11.4W1+1.41AW20.8W2+0.82A3W31.1W3+1.1A4W41.5W4+1.5水泥廠運(yùn)往地B1B2B3B4B5A10.0080.0130.0120.0030.014A20.010.0060.0070.0030.009A3

9、0.0040.0090.0060.0050.004A0.0070.0050.0090.0120.014每噸水泥的運(yùn)輸費(fèi)用 ( 單位：萬(wàn)元 )X11Y11X12Y12Z=（ 0.008 0.013 0.012 0.003 0.014） X13Y13+(0.01 0.006 0.007 0.003 0.009)X14Y14X15Y15X 21Y 21X 31Y31X 22Y22X 32Y32X 23Y 23+(0.004 0.009 0.006 0.005 0.004)X 33Y33 +(0.007 0.005 0.009X 24Y24X 34Y34X 25Y 25X 35Y 35X 41Y41X

10、 42Y 420.012 0.01)X 43Y43X 44Y 44X 45Y4555( X1 j Y1 j )Y1 jj1j155( X 2 j Y 2 j )Y2 jY=(W1 W2 W3 W4)j1j15+(1.4 0.8 1.1 1.5)5( X 3 j Y3 j )Y3 jj1j155( X 4 j Y 4 j )Y4 jj1j1M=Y+Z444限制條件1： （ Xi 1Y i1）=1100000， （ Xi 2Y i 2）=1600000， （ Xi 3Y i 3）=800000，i 1i 1i 144（ Xi 4Yi 4）=2000000， （ Xi 5Y i5）=1000000；

11、i 1i 1555限制條件2:0 X1j 1000000 ， 0X2j 1500000 ， 0X3j 1200000， 0 j1j1j15X4j 1300000；j 1555限制條件3：0Y1j 1000000 ， 0Y2j 1700000， 0Y3j 1200000， 0 j 1j 1j15Y4j 1500000；j 14.2 模型的求解接下來(lái)利用 Microsoft Excel2003 列出表格數(shù)據(jù)以及規(guī)劃求解，為方便求解，這里的水泥量單位統(tǒng)一為噸，費(fèi)用單位統(tǒng)一為萬(wàn)元。圖（ 1）如圖 1，陰影部分為這次建模主要求解的部分，即各個(gè)水泥廠的生產(chǎn)運(yùn)輸方案.圖（ 2）此表格中的數(shù)據(jù)為各個(gè)水泥廠運(yùn)往

12、各地的運(yùn)費(fèi)，以及產(chǎn)能之內(nèi)和產(chǎn)能之外的生產(chǎn)成本。為了方便使用SUMPRODUCT函數(shù)，我們將生產(chǎn)成本同樣設(shè)計(jì)成4× 5 的表格（圖2）圖（ 3）圖 3 利用 SUMPRODUCT函數(shù)求出上述模型中的Z、Y、 M的值。分別對(duì)應(yīng)圖3 表格中的 B100、C100、 B101 單元格。其中SUMPRODUCT函數(shù)的功能是是具有相同維數(shù)的表格數(shù)據(jù)對(duì)應(yīng)相乘，返回值為這些乘積的和。圖（ 4）接下來(lái)就是規(guī)劃求解了，利用先從Excel2003 工具中加載規(guī)劃求解宏，然后打開(kāi)規(guī)劃求解工具，就能看到圖（4）的界面。我們先來(lái)設(shè)置規(guī)劃求解參數(shù)。目標(biāo)單元格為總費(fèi)用的B101單元格，目的是使其值最小，我們點(diǎn)選最小

13、值選項(xiàng)。可變單元格為圖（1）中的陰影部分，即為各個(gè)工廠的生產(chǎn)運(yùn)輸方案。添加的約束即為上述模型中的三組限制條件中的右邊部分。圖（ 5）如圖 5，在選項(xiàng)中，我們勾選假定非負(fù)，這樣上述三組限制條件中的右邊部分也已將添加了約束。然后勾選采用線性模型。圖（ 6）點(diǎn)擊確定后，規(guī)劃求得結(jié)果，找到一解，可滿足所有的約束及最優(yōu)狀況。圖（ 7）圖（ 8）表格 7 為這次所求的運(yùn)費(fèi)以及生產(chǎn)費(fèi)用的最小值。 表格 8 即為滿足使得總費(fèi)用最小的生產(chǎn)運(yùn)輸方案。由表格可以看出，四個(gè)水泥廠要生產(chǎn)的水泥分別為 100 萬(wàn)噸， 300 萬(wàn)噸， 120 萬(wàn)噸， 130 萬(wàn)噸。水泥廠 1運(yùn)往 D 城市 100萬(wàn)噸水泥。水泥廠 2運(yùn)往 B 城市 120萬(wàn)噸， C城市 80 萬(wàn)噸，運(yùn)往 D城市 100 萬(wàn)噸。水泥廠 3運(yùn)往 A城市 20萬(wàn)噸，運(yùn)往 E 城市 100 萬(wàn)噸。水泥廠 4運(yùn)往 A城市 90萬(wàn)噸，運(yùn)往 B 城 40 萬(wàn)噸，運(yùn)往 C 城市 40 萬(wàn)噸。表格為：水泥廠運(yùn)往各城市的水泥（萬(wàn)噸）ABCDE100010002012080100032000010