ecel規(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)的水泥都銷往附近的 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)輸方案，并給該水泥公司管理層寫一個(gè)報(bào)告。A城市B城市C城市D城市E城市水泥廠18013012030140水泥廠210060703090水泥廠34090605040水泥廠4705090120100表一：每噸水泥的運(yùn)輸費(fèi)用（單位：元）年生 產(chǎn)能力（萬(wàn)噸）平均每提咼1 噸產(chǎn)量所需額 外投資（萬(wàn)元）資源

3、限制（萬(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.8992004 年0.8820.7440.7050.8922003

4、年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)每噸水泥需要的額外成本也不一樣，所以本題需要設(shè)兩個(gè)主要的函數(shù)，分別為年生產(chǎn)能力之內(nèi)每個(gè)水泥廠運(yùn)往每個(gè)地方的 水泥數(shù)量，以及年生產(chǎn)能

5、力之外每個(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)噸,120萬(wàn)噸,150萬(wàn)噸水泥。再根據(jù)每個(gè)水泥廠的產(chǎn)能限制以及運(yùn)費(fèi)和各地的需求量列出等式和不等式 組，利用線性

6、規(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,W表示2012年各個(gè)水泥廠的生產(chǎn)成本（萬(wàn)元/噸） 設(shè)所需要的總費(fèi)用為 M四個(gè)水泥廠按照題干要求的生產(chǎn)總成本為 Z,四個(gè)水泥 廠的運(yùn)輸總費(fèi)用為Y。則M=Z+Y目的是求使得M為最小值的生產(chǎn)運(yùn)輸方案。四、模型的建立與求解4.1模型的建立這里統(tǒng)一

7、單位為萬(wàn)元/噸水泥廠年產(chǎn)能之內(nèi)的生產(chǎn)運(yùn)輸量BBB3B4B5年生產(chǎn)能力AX11X12X13X14X151000000AX21X22X23X24X251500000AX31X32X33X34X351200000AX41X42X43X44X451300000年產(chǎn)能之外的生產(chǎn)運(yùn)輸量資源限制AY11Y12Y13Y14Y151000000AY21Y22Y23Y24Y251700000AY31Y32Y33Y34Y351200000AY41Y42Y43Y44Y451500000需求量（噸）1100000160000080000020000001000000每個(gè)水泥廠運(yùn)往每個(gè)城市的水泥成本為運(yùn)費(fèi)加上生產(chǎn)成本。根

8、據(jù)計(jì)算出的2012年生產(chǎn)成本以及運(yùn)費(fèi)可以列出一下表格水泥廠2012年產(chǎn)能之內(nèi) 生產(chǎn)成本（萬(wàn)元/噸）2012年產(chǎn)能之外每噸所需 額外投資（萬(wàn)元/噸）2012年產(chǎn)能之外總成本（萬(wàn)元/噸）AW11.4W1+1.4A2W20.8W2+0.8AW31.1W3+1.1AW41.5W4+1.5水泥廠運(yùn)往地BB2B3B5A0.0080.0130.0120.0030.014A0.010.0060.0070.0030.009A0.0040.0090.0060.0050.004A0.0070.0050.0090.0120.01j 1每噸水泥的運(yùn)輸費(fèi)用X11（單位：萬(wàn)元）Y11X12Y12Y13Y14+(0.01 0

9、.006 0.007 0.003 0.009)Z= ( 0.008 0.013 0.012 0.003 0.014)X13X14X15Y15X21 Y21X31 Y31X22 Y22X32 Y32X23 Y23 +(0.004 0.009 0.006 0.005 0.004)X33 Y33X24 Y24X34 Y34X25 Y25X35 Y35+(0.007 0.005 0.0090.012 0.01)X41Y41X42Y42X43Y43X44Y44X45Y455(X1j j 15Y1j)Y=(W1 W2 W3 W4)(X2jj 15(X3jj 15(X4j1Y2j)Y3j)Y4j)+(1.4

10、 0.8 1.1 1.5)5Y1jj 15Y2jj 15Y3jj 15Y4jj 1M=Y+Z限制條件41:(Xi1i 1Yi1) =1100000,4(Xi 2 Yi2) =1600000,i 14(Xi 3 Yi3) =800000,i 14(Xi 4i 14Yi4) =2000000,(Xi 5i 1Yi5) =1000000；限制條件52: 0 w X1j w 1000000 ,j 150wX2j w 1500000 , 0 wj 15X3j w 1200000, 0 wj 15X4j w 1300000;555限制條件 3: 0Y1j w 1000000, 0wY2j 1700000,

11、 0 Y3j 1200000, 0 j ij ij i5Y4j w 1500000;j i4.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)往各地的運(yùn)費(fèi)，以及產(chǎn)能之內(nèi)和產(chǎn)能之外的生產(chǎn)成本。為了方便使用SUMPRODU函數(shù)，我們將生產(chǎn)成本同樣設(shè)計(jì)成4X 5的表格（圖2）圖（3）圖3利用SUMPRODU函數(shù)求出上述模型中的 Z、Y、M的值。分別對(duì)應(yīng)圖 3表格中的B100、C100、B101

12、單元格。其中SUMPRODU函數(shù)的功能是是具有相同維數(shù)的表格數(shù)據(jù)對(duì)應(yīng)相乘， 返回值為這些乘積的和。圖（4）接下來(lái)就是規(guī)劃求解了，利用先從Excel2003工具中加載規(guī)劃求解宏，然后打開規(guī)劃求解工具，就能看到圖（4）的界面。我們先來(lái)設(shè)置規(guī)劃求解參數(shù)。目標(biāo)單元格為總費(fèi)用的B101單元格，目的是使其值最小，我們點(diǎn)選最小值選項(xiàng)?？勺儐卧駷閳D（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)噸）ABCDE100010002012080100032000010049040000只有水泥廠