excel规划求解实例

2021年高教社杯全国大学生数学速模竞赛

校内选拔赛

2021年12月2日

关于水泥厂生产及运输方案的最优化求解

摘要

摘要内容：木论文主要讨论四个水泥厂往五个城市提供水泥的生产运输最优

化问题。根据给出的条件，做出合理的分析，通过建立数学模型以及利用电脑软件

Microsoftexce!2003辅助，求出2021年的水泥生产本钱，并根据各地不同的生产本

钱以及超出需要额外投资的本钱，规划求解得出在资源限制范围内最优的生产运输

方案以及所需要的最低费用。

关键词：回归方程；目标函数；数学模型；线性规划求解。

一、问题重述

某水泥现有4个水泥厂，这4个厂生产的水泥都销往附近的ABCDE这5个城市，

而这5个城市今年的需求量分别为110万吨，160万吨，80万吨，200万吨和100万

吨。资源消耗系数为2.5,每吨产品的运输费用见表一，表二提供了一些其他供参

考的数据，表三提供了最近十年这4家水泥厂生产每吨水泥的生产本钱（万元）。

问题：请你根据给定的数据设计出最优的生产及运输方案，并给该水泥公司管理层

写一个____________报____________告o

A城市B城市C城市D城市E城市

水泥厂18013012030140

水泥厂210060703090

水泥厂34090605040

水泥厂4705090120100

（单位二元）

年生产能力平均每提高1吨

资源限制

（万产量所需额外投

（万吨）

吨）资（万元）

水泥厂11001.4500

水泥厂21500.8800

水泥厂31201.1600

水泥厂41301.5700

表二：一些其他供参考的数据

水泥厂1水泥厂2水泥厂3水泥厂4

2021年1.1231.1690.9001.244

2021年1.0960.9890.9501.050

2021年1.0370.9700.9351.065

2021年0.9950.9590.9201.024

2007年0.9900.9530.9001.022

2006年0.9600.9250.8570.980

2005年0.9360.7580.8150.899

2004年0.8820.7140.7050.892

2003年0.8770.6600.6880.764

2002年0.8430.6220.6840.721

表三：4家水泥厂的生产本钱（万元/吨）

注：资源限制是指产地资源的拥有量；资源消耗系数是指生产单位产品所需消耗的资源数。

二、问题分析

问题中给出最近儿年各个水泥厂生产本钱，由回归方程可得到每个水泥厂2021年的生产本钱。

设2021年每个水泥厂生产本钱分别为Wl,W2,W3,W4o

四个水泥厂运往五个城市，需要的运费各不一样。并且各个水泥厂的生产本钱各不一样。超出

年生产能力之后生产每吨水泥需要的额外本钱也不一样，所以此题需要设两个主要的函数，分别为

年生产能力之内每个水泥厂运往每个地方的水泥数量，以及年生产能力之外每个水泥厂运往每个地

方的水泥数量。

设四个水泥厂的代号为Al,A2,A3,A4,五个城市的代号为Bl,B2,B3,B4,B5,设产能之内各个水

泥厂运往每个城市的水泥吨位为Xij,产能之外各个水泥厂运往每个城市的水泥为Yijo那么总费用即

为运输量乘以运费与生产本钱的和。

由资源消耗系数以及资源限制量可求的四个水泥厂可生产的水泥最多分别为200万吨,320万

吨,240万吨,280万吨.各自减去产能可得到超出生产能力后每个水泥厂分别最多可以再生产100万

吨，170万吨，120万吨，150万吨水泥。

再根据每个水泥厂的产能限制以及运费和各地的需求量列出等式和不等式组，利用线性规划求

出总费用的最小值。

三、模型假设及符号说明3「模型假设

由每个水泥厂运往每个城市的水泥量，以及使得其满足费用最小化的约束条件，建立成一个模

型。

3.2符号说明

Al,A2,A3,A4代表四个水泥厂,Bl,B2,B3,B4,B5代表五个城市。Xij为产能之内每个工厂向各地

运输的水泥量。Yij为产能之外每个水泥厂往各地运输的水泥量。W1,W2,W3,W4表示2021年各个水泥厂

的生产本钱（万元/吨）。设所需要的总费用为M,四个水泥厂按照题干要求的生产总本钱为Z,四个水

泥厂的运输总费用为Y。那么切Z+Y.目的是求使得M为最小值的生产运输方案。

四、模型的建立与求解4.1模型的建立

这里统一单位为万元/吨

年产能之内的生产运临

水泥厂

B.B3B,B5年生产能力

A.XIIX12X13X14X15100C000

A2X21X22X23X24X25150C000

A3X31X32X33X34X35120C000

4X41X42X43X44X45130C000

年产能之外的生产运输量资源限制

A.Y11Y12Y13Y14Y15100C000

A2Y21Y22Y23Y24Y25170C000

A3¥31Y32Y33Y34Y35120C000

Y41Y42Y43Y44Y45150C000

需求量1100000160000080000020000001000000

（吨）

每个水泥厂运往每个城市的水泥本钱为运费加上生产本钱。根据计算出的2021年生产本钱以及运费可以列出一下表

格

2021年产能之内生产本钱2021年产能之外每吨所需额2021年产能之

水泥厂

（万元/吨）外投资（万元/吨）外总本钱（万元/吨）

A]W11.4町+1.4

A2W20.8W2+0.8

A3V'31.1W3+L1

AAW41.5W4+1.5

运往地

水泥厂

B2B3B4B5

A.0.0080.0130.0120.0030.014

A20.010.0060.0070.0030.009

A30.0040.0090.0060.0050.004

A.0.0070.0050.0090.0120.01

每吨水泥的运输费用（单位：万元）’X11+K11、

X12+K12

Z=(0.0080.0130.0120.0030.014)X13+K13+(0.010.0060.0070.003X14+H4

\X15+V,

*X21+X21、*X31+K31>

X22+Y22X32+Y32

0.009)X23+K23+(0.0040.0090.0060.0050.004)X33+K33+(0.0070.0050.009

X24+Y24X34+Y34

zX25+K25.\X35+Y35.

X41+K41X42+Y42

0.0120.01)X43+V43X44+Y44

X45+K45

冒(XI/+KI/)(5

支(X2i+Y2i)

Y=(W1W2W3W4)jT+(1.40.81.1J=1

£(X3/+W

/=/

文(X4/+K4J)

',=i>

M=Y+Z

限制条件1：文(Xi1+YU)=1100000,文(Xi2+Yi2)=1600000,文(Xi3+Yi3)=800000,i=ii=ii=i£(Xi4+

Y,4)=2000000,£(Xi5+Yi5)=1000000；

i=li=l555

限制条件2:0<-Xlj<1000000,0WW1500000,OW£X3jm200000,ow

<130()000；限制条件3：OWZYlj<1000000,0W£Y2jwi700000,、3j<1200000,0W>=•>=1>=!

Y4j<1500000；J=i

4.2模型的求解接下来利用MicrosoftExcel2()()3列出表格数据以及规划求解,为方便求解，这里的

水泥量单位统一为吨，费用单位统一为万元。

图(1)如图1,阴影局部为这次建模主要求解的局部，即各个水泥厂的生产

运输方案.

图(2)此表格中的数据为各个水泥厂运往各地的运费，以及产能之内和产

能之外的生产本钱。为了方便使用SUMPRODUCT函数，我们将生产本钱同

样设计成4X5的表格(图2)

图⑶图3利用SUMPRODUCT函数求出上述模型中的Z、Y、M的值。分别对

应图3表格中的B100、C100sB101单元格。其中SUMPRODUCT函数的功能

是是具有•样维数的表格数据对应相乘，返回值为这些乘积的和。

图(4)接下来就是规划求解了，利用先从Excel2003工具中加载规划求解

宏，然后翻开规划求解工具，就能看到图(4)的界面。我们先来设置规划

求解参数。目标单元格为总费用的B101单元格，目的是使其值最小，我

们点选最小值选项。可变单元格为图(D中的阴影局部，即为各个工厂的

生产运输方案。添加的约束即为上述模型中的三组限制条件中的右边局部

图(5)如图5,在选项中，我们勾选假定非负，这样上述三组限制条件中的

右边局部也己将添加了约束。然后勾选采用线性模型。

图⑹点击确定后，规划求得结果，找到一解，可满足所有的约束及最优

状况。

图⑺图(8)

表格7为这次所求的运费以及生产费用的最小值。表格8即为满足使得总费用最小的生产运输方案。

由表格可以看出，四