ecel規(guī)劃求解實例

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

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

3、限制（萬噸）水泥廠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)成本（萬元/噸）注：資源限制是指產(chǎn)地資源的擁有量；資源消耗系數(shù)是指生產(chǎn)單位產(chǎn)品所 需消耗的資源數(shù)。問題分析問題中給出最近幾年各個水泥廠生產(chǎn)成本，由回歸方程可得到每個水泥廠2012年的生產(chǎn)成本。設2012年每個水泥廠生產(chǎn)成本分別為 W1,W2,W3,W4四個水泥廠運往五個城市，需要的運費各不相同。并且各個水泥廠的生產(chǎn)成 本各不相同。超出年生產(chǎn)能力之后生產(chǎn)每噸水泥需要的額外成本也不一樣，所以本題需要設兩個主要的函數(shù)，分別為年生產(chǎn)能力之內(nèi)每個水泥廠運往每個地方的 水泥數(shù)量，以及年生產(chǎn)能

5、力之外每個水泥廠運往每個地方的水泥數(shù)量。設四個水泥廠的代號為 A1, A2, A3, A4,五個城市的代號為B1，B2, B3, B4, B5,設產(chǎn)能之內(nèi)各個水泥廠運往每個城市的水泥噸位為Xij ,產(chǎn)能之外各個水泥廠運往每個城市的水泥為 Yij。則總費用即為運輸量乘以運費與生產(chǎn)成本的 和。由資源消耗系數(shù)以及資源限制量可求的四個水泥廠可生產(chǎn)的水泥最多分別 為200萬噸,320萬噸,240萬噸,280萬噸.各自減去產(chǎn)能可得到超出生產(chǎn)能力后 每個水泥廠分別最多可以再生產(chǎn) 100萬噸,170萬噸,120萬噸,150萬噸水泥。再根據(jù)每個水泥廠的產(chǎn)能限制以及運費和各地的需求量列出等式和不等式 組，利用線性

6、規(guī)劃求出總費用的最小值。三、模型假設及符號說明3.1模型假設由每個水泥廠運往每個城市的水泥量，以及使得其滿足費用最小化的約束條 件，建立成一個模型。3. 2符號說明A1，A2, A3, A4代表四個水泥廠，B1，B2, B3, B4, B5代表五個城市。Xij為產(chǎn)能之內(nèi)每個工廠向各地運輸?shù)乃嗔俊ij為產(chǎn)能之外每個水泥廠往各地運輸?shù)乃嗔?。W1,W2,W3,W表示2012年各個水泥廠的生產(chǎn)成本（萬元/噸） 設所需要的總費用為 M四個水泥廠按照題干要求的生產(chǎn)總成本為 Z,四個水泥 廠的運輸總費用為Y。則M=Z+Y目的是求使得M為最小值的生產(chǎn)運輸方案。四、模型的建立與求解4.1模型的建立這里統(tǒng)一

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

8、據(jù)計算出的2012年生產(chǎn)成本以及運費可以列出一下表格水泥廠2012年產(chǎn)能之內(nèi) 生產(chǎn)成本（萬元/噸）2012年產(chǎn)能之外每噸所需 額外投資（萬元/噸）2012年產(chǎn)能之外總成本（萬元/噸）AW11.4W1+1.4A2W20.8W2+0.8AW31.1W3+1.1AW41.5W4+1.5水泥廠運往地BB2B3B5A0.0080.0130.0120.0030.014A0.010.0060.0070.0030.009A0.0040.0090.0060.0050.004A0.0070.0050.0090.0120.01j 1每噸水泥的運輸費用X11（單位：萬元）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模型的求解接下來利用Microsoft Excel2003列出表格數(shù)據(jù)以及規(guī)劃求解，為方便求解，這 里的水泥量單位統(tǒng)一為噸，費用單位統(tǒng)一為萬元。圖（1）如圖1,陰影部分為這次建模主要求解的部分，即各個水泥廠的生產(chǎn)運輸方案圖（2）此表格中的數(shù)據(jù)為各個水泥廠運往各地的運費，以及產(chǎn)能之內(nèi)和產(chǎn)能之外的生產(chǎn)成本。為了方便使用SUMPRODU函數(shù)，我們將生產(chǎn)成本同樣設計成4X 5的表格（圖2）圖（3）圖3利用SUMPRODU函數(shù)求出上述模型中的 Z、Y、M的值。分別對應圖 3表格中的B100、C100、B101

12、單元格。其中SUMPRODU函數(shù)的功能是是具有相同維數(shù)的表格數(shù)據(jù)對應相乘， 返回值為這些乘積的和。圖（4）接下來就是規(guī)劃求解了，利用先從Excel2003工具中加載規(guī)劃求解宏，然后打開規(guī)劃求解工具，就能看到圖（4）的界面。我們先來設置規(guī)劃求解參數(shù)。目標單元格為總費用的B101單元格，目的是使其值最小，我們點選最小值選項。可變單元格為圖（1）中的陰影部分，即為各個工廠的生產(chǎn)運輸方案。添加的約束即為上述模型中的三組限制條件中的右邊部分。圖（5）如圖5,在選項中，我們勾選假定非負，這樣上述三組限制條件中的右邊部分也已將添加了 約束。然后勾選采用線性模型。圖（6）點擊確定后，規(guī)劃求得結果，找到一解，可滿足所有的約束及最優(yōu)狀況。圖（7）圖（8）表格7為這次所求的運費以及生產(chǎn)費用的最小值。 表格8即為滿足使得總費用最 小的生產(chǎn)運輸方案。由表格可以看出，四個水泥廠要生產(chǎn)的水泥分別為 100萬噸, 300萬噸，120萬噸，130萬噸。水泥廠1運往D城市100萬噸水泥。水泥廠2運往B城市120萬噸，C城市80萬噸，運往D城市100萬噸。水泥廠3運往A城市20萬噸，運往E城市100萬噸。水泥廠4運往A城市90萬噸，運往B城40萬噸，運往C城市40萬噸。表格為:水泥廠運往各城市的水泥（萬噸）ABCDE100010002012080100032000010049040000只有水泥廠