excel规划求解实例

1、2014 年高教社杯全国大学生数学建模竞赛校内选拔赛2013 年12月2日关于水泥厂生产及运输方案的最优化求解摘要摘要内容 ：本论文主要讨论四个水泥厂往五个城市提供水泥的生产运输最优化问题。根据给出的条件， 做出合理的分析， 通过建立数学模型以及利用电脑软件 Microsoft excel2003 辅助，求出 2012 年的水泥生产成本，并根据各地不同的生产成本以及超出需要额外投资的成本， 规划求解得出在资源限制范围内最优的生产运输方案以及所需要的最低费用。关键词：回归方程；目标函数；数学模型；线性规划求解。一、问题重述某水泥有限公司现有 4 个水泥厂，这 4 个厂生产的水泥都销往附近的 AB

2、CDE 这 5 个城市，而这 5 个城市今年的需求量分别为 110 万吨， 160 万吨， 80 万吨， 200 万吨和 100 万吨。已知资源消耗系数为 2.5 ，每吨产品的运输费用见表一，表二提供了一些其他供参考的数据， 表三提供了最近十年这 4 家水泥厂生产每吨水泥的生产成本 ( 万元 ) 。问题：请你根据给定的数据设计出最优的生产及运输方案， 并给该水泥公司管理层写一个报告。A 城市B城市C城市D城市E城市水泥厂 18013012030140水泥厂 210060703090水泥厂 34090605040水泥厂 4705090120100表一：每吨水泥的运输费用 ( 单位：元 )年生平均

3、每提高 1资源限制产能力(万吨产量所需额(万吨)吨)外投资(万元)水泥厂 11001.4500水泥厂 21500.8800水泥厂 31201.1600水泥厂 41301.5700表二：一些其他供参考的数据水泥厂 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 家水泥厂的生产成本 ( 万元 / 吨 )注：资源限制是指产地资源的拥有量；资源消耗系数是指生产单位产品所需消耗的资源数。二、问题分析问题中给出最近几年各个水泥厂生产成本，由回归方程可得到每个水泥厂2012 年的生产成本。设2012 年每个水泥厂生产成本分别为W1,W2,W3,W4。四个水泥厂运往五个城市， 需要的运费各不相同。 并且各个水泥厂的生产成本各不相同。超出年生产能力之后生产每吨水泥需要的额外成本也不一样， 所以

5、本题需要设两个主要的函数， 分别为年生产能力之内每个水泥厂运往每个地方的水泥数量，以及年生产能力之外每个水泥厂运往每个地方的水泥数量。设四个水泥厂的代号为 A1，A2， A3，A4，五个城市的代号为 B1，B2，B3，B4，B5，设产能之内各个水泥厂运往每个城市的水泥吨位为 Xij ，产能之外各个水泥厂运往每个城市的水泥为 Yij 。则总费用即为运输量乘以运费与生产成本的和。由资源消耗系数以及资源限制量可求的四个水泥厂可生产的水泥最多分别为 200 万吨 ,320 万吨 ,240 万吨 ,280 万吨 . 各自减去产能可得到超出生产能力后每个水泥厂分别最多可以再生产 100 万吨 ,170 万

6、吨 ,120 万吨 ,150 万吨水泥。再根据每个水泥厂的产能限制以及运费和各地的需求量列出等式和不等式组，利用线性规划求出总费用的最小值。三、模型假设及符号说明3.1 模型假设由每个水泥厂运往每个城市的水泥量， 以及使得其满足费用最小化的约束条件，建立成一个模型。3. 2 符号说明A1 ， A2，A3，A4代表四个水泥厂， B1， B2，B3， B4，B5 代表五个城市。 Xij 为产能之内每个工厂向各地运输的水泥量。 Yij 为产能之外每个水泥厂往各地运输的水泥量。 W1,W2,W3,W4表示 2012 年各个水泥厂的生产成本 （万元 / 吨）。设所需要的总费用为 M，四个水泥厂按照题干要

7、求的生产总成本为 Z，四个水泥厂的运输总费用为 Y。则 M=Z+Y目.的是求使得 M为最小值的生产运输方案。四、模型的建立与求解4.1 模型的建立这里统一单位为万元/ 吨年产能之内的生产运输量水泥厂B1B2B3B4B5年生产能力AX11X12X13X14X1510000001AX21X22X23X24X2515000002A3X31X32X33X34X351200000A4X41X42X43X44X451300000年产能之外的生产运输量资源限制A1Y11Y12Y13Y14Y151000000A2Y21Y22Y23Y24Y251700000A3Y31Y32Y33Y34Y351200000A4Y

8、41Y42Y43Y44Y451500000需求量1100000160000080000020000001000000（吨）每个水泥厂运往每个城市的水泥成本为运费加上生产成本。根据计算出的2012年生产成本以及运费可以列出一下表格水泥厂2012 年产能之内2012 年产能之外每吨所需2012 年产能之生产成本（万元吨）额外投资（万元吨）外总成本（万元吨）AW11.4W1+1.41AW20.8W2+0.82A3W31.1W3+1.1A4W41.5W4+1.5水泥厂运往地B1B2B3B4B5A10.0080.0130.0120.0030.014A20.010.0060.0070.0030.009A3

9、0.0040.0090.0060.0050.004A0.0070.0050.0090.0120.014每吨水泥的运输费用 ( 单位：万元 )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 模型的求解接下来利用 Microsoft Excel2003 列出表格数据以及规划求解，为方便求解，这里的水泥量单位统一为吨，费用单位统一为万元。图（ 1）如图 1，阴影部分为这次建模主要求解的部分，即各个水泥厂的生产运输方案.图（ 2）此表格中的数据为各个水泥厂运往

12、各地的运费，以及产能之内和产能之外的生产成本。为了方便使用SUMPRODUCT函数，我们将生产成本同样设计成4× 5 的表格（图2）图（ 3）图 3 利用 SUMPRODUCT函数求出上述模型中的Z、Y、 M的值。分别对应图3 表格中的 B100、C100、 B101 单元格。其中SUMPRODUCT函数的功能是是具有相同维数的表格数据对应相乘，返回值为这些乘积的和。图（ 4）接下来就是规划求解了，利用先从Excel2003 工具中加载规划求解宏，然后打开规划求解工具，就能看到图（4）的界面。我们先来设置规划求解参数。目标单元格为总费用的B101单元格，目的是使其值最小，我们点选最小

13、值选项。可变单元格为图（1）中的阴影部分，即为各个工厂的生产运输方案。添加的约束即为上述模型中的三组限制条件中的右边部分。图（ 5）如图 5，在选项中，我们勾选假定非负，这样上述三组限制条件中的右边部分也已将添加了约束。然后勾选采用线性模型。图（ 6）点击确定后，规划求得结果，找到一解，可满足所有的约束及最优状况。图（ 7）图（ 8）表格 7 为这次所求的运费以及生产费用的最小值。 表格 8 即为满足使得总费用最小的生产运输方案。由表格可以看出，四个水泥厂要生产的水泥分别为 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 万吨。表格为：水泥厂运往各城市的水泥（万吨）ABCDE100010002012080100032000010