如何在Excel中建立并求解线性规划模型

如何在Excel中建立并求解线性规划模型

刘桂莲

摘要：数学中线性规划问题的求解一直是很繁琐的，功能强大的Excel软件为我们提供了一种很好的求解方法，但这种方法却很少被人了解。本文就如何在Excel中建立并求解线性规划模型作了较详尽的论述。

关键词：线性规划数学模型电子表格模型规划求解Excel

线性规划是运筹学的一个分支，它的应用已愈来愈深入到社会生产和经济活动的各个领域。描述线性规划问题的抽象的数学式子是线性规划问题的数学模型。建立数学模型后，求解满足约束条件的目标函数的最优解是解决线性规划问题的关键。数学中常用的方法是图解法和单纯形法，而图解法只适用于两个变量的目标函数，单纯形法则计算量相当大，步骤烦琐，容易出错。在Excel中建立

电子表格模型，并利用它提供的“规划求解”工具，能轻松快捷地求解模型的解。

例如，某玻璃制品公司有三个工厂，公司目前决定停止不赢利产品的生产并撤出生产能力来生产两种新开发的产品：玻璃门和双把窗。估计三个工厂每周可用来生产新产品的时间分别为4小时、12小时、18小时，而每扇门需工厂1生产时间1个小时和工厂3生产时间3个小时，每扇窗需工厂2和工厂3生产时间各为2个小时，预测门的单位利润是300元，窗的单位利润是500元，问每周两种新产品数量的哪种组合能使总利润最大？

问题的决策变量有两个：每周门的生产数量和窗的生产数量，目标是总利润最大，需满足的条件是：⑴三个工厂每周用于生产新产品的时间w每周可得时间

⑵每周门、窗的生产数量均》0。设每周门的生产数量为X，窗的生产数量为y，则该问题的数学模型即为：最大化利润P =300x+500y,约束条件：xw4, 2y< 12，3x+2yw 18，x>0和y》0。

将上表的有关数据输入到Excel中，建立如图1所示的电子表格模型。被输入已知数据的单元格是数据单元格，如单元格C5：D8，G5：G7。决策变量(即两种产品每周的生产量)放在单元格C9和D9，正好定位在这些产品所在列的

数据单元格下面，这种含有需要做出决策的单元格是可变单元格。单元格E5：

E7是用来计算各个工厂每周的总生产时间，如单元格E5就是用C5：D5和C9:

D9的对应数值各自相乘再总加得到。Excel中有一个叫SUMPRODUCT的函数

能对相等行数和相等列数的两个变化范围的单元格中的值乘积后进行加和。被加

和的每个值是对第一个变化范围的一些值和对应位置的第二个变化范围的一些值的积。女口

E5=SUMPRODUCT(C5 : D5，C9：D9)是把C5：D5变化范围的每个值与C9 : D9变化范围中对应的每个值相乘，然后各个积相加。同样

E6=SUMPRODUCT(C6 : D6, C9：D9)，E7=SUMPRODUCT(C7 : D7, C9：D9)，

E5、E6、E7这些单元格的数值是依赖于可变单元格的，它们是输出单元格。单元格F5、

F6、F7中的“W”符号表示它们左边的总值不允许超过列G中的对应

数值，体现了函数的约束条件。目标函数值（利润）被放在E8单元格，正好在

用来帮助计算总利润的数据单元格右边，与列E中的其中它的数据相像，它也是一些乘积的加和，E8=SUMPRODUCT（C8：D89：D9），E8是特殊的输出单元格，是显示目标函数值的，是目标单元格。在没有计算之前，可变单元格和输出单元格的数值均显示为0。

图1：

Excel中有一个叫“规划求解”的工具，能快速求解线性规划问题，步骤如

下：

1＞在工具菜单中选择“规划求解”，会弹出一个对话框。在对话框中，将E8 键入目标单元格，将C9：D9键入可变单元格，既然目标是要最大化目标单元格，还必须选中“最大值” （Max）

2 ＞点击对话框中的“添加”按纽（Add），弹出添加约束对话框，将约束条件具体化。左端输入范围E5：E7,右端输入范围G5：G7,中间的符号可选择“＜=”。如果还要添加更多的函数约束就再点击“添加”按钮以弹出一个新的添加约束对话框，在这个例子中没有其它约束了。下一步只要点击“确定”按钮回到“规划求解”对话框。

3＞点击“选项”按钮，在新弹出的对话框中，选中“采用线性模型”和“假定非负”选项，这就告诉了计算机要求解的问题是一个线性规划问题以及非负约束，点击“确定”再回到“规划求解”对话框。

4＞点击“求解”按钮，计算机会在后台开始对问题进行求解。几秒钟之后会显示运行结果，一般而言，它会显示已经找到一个最优解。如果模型没有可行解或没有最优解，对话框会显示“规划求解找不到可行解”。

求解模型之后，最优值就代替了可变单元格中的初始值，最优解是每周2扇

门和6扇窗，目标单元格的对应数值（每周总利润）为3600元。

求解的电子表格模型（图2）如下：

线性规划问题的数学模型是描述实际问题的抽象的数学形式，它反映了客观事物数量间的本质规律。电子表格模型和数学模型两种形式是等价的。电子表格模型的建立不是唯一的，是非常灵活的，一个好的电子表格模型能直观，简便地反映线性规划问题的实质•这两种形式导致了不同但互补的分析问题的方法，但用“规划求解”工具会达到事半功倍的效果。

再如：某厂家拥有三个生产婴儿车的工厂，并运往四个配送中心，三个工厂每月的产量分别是12、17、11个运输单位，同时每个配送中心每月要接受10个运输单位的货物，从每一个工厂到每一个配送中心的单位运输成本如下所示：到各配送中心的单位运输成本（元）

确定每月从每一个工厂之中要运送多少运输单位的婴儿车给相应的配送中心的最佳方案，使总的运输成本最小。

用x j （i=1、2、3, j=1、2、3、4）表示从工厂运送到配送中心j的货物量, 那么各工厂运送到各配送中心的货物量如下表：

确定X j （i=1、2、3，j=1、2、3、4）的值，使目标函数C成本

=50X11+60X12+40X13+20X14+20X21 +90x22+10x23+30x24+30x31 +40x32+20x33+10x34 有最小值。

约束条件是：

X11+X12+X13+X14=12

X21+X22+X23+X24=17

X31+X32+X33+X34=1 1

X11+X21+X31=10

X12+X22+X32=10

X13+X23+X33=10

X14+X24+X34=10

x ij>0 (i=1、2、3, j=1、2、3、4)

电子表格模型如图3: