线性规划与Excel求解

刘连香陈鑫源

中图分类号：F224.31文献标识码：A文章编号：1003-2738（2012）02-00200-01

摘要：在生产管理和经营活动中，经常遇到如何合理地利用有限资源，以达到我们最理想的结果，即线性规划问题。随着数学的发展，线性规划模型可以有Matlab、LINDO等软件求解，但是这些软件都过于专业化，而且占用空间大，对一般的工作人员来说不太实用，下面以简单的实际问题为例介绍用Excel软件求解该模型的方法。

关键词：线性规划；数学模型；Excel

一般地，线性模型的可表述为如下形式：*

在优化模型中，如果目标函数和约束条件中的都是线性函数，则该模型称为线性规划。目标函数或者约束条件中至少有一个是非线性函数的最优化问题叫做非线性规划问题。下面分别举例介绍，并给出Excel求解过程。

一、线性规划模型求解

例1：需求最省运费方案

现要从两个仓库（发点）运送库存原棉来满足3个纺织厂的需要，数据如下表，试问在保证各纺织厂的需求都得到满足的条件下应采取哪个运输方案，才能使总运费最省？（运价（元/t）如下表）

工厂1工厂2工厂3库存量/t

仓库1号21350

仓库2号22430

需求量/t401525

模型建立于求解：依题意即要确定从i号仓库运到j号工厂的原棉数量，故设表示从i号仓库运到j号工厂的原棉数量（t），f表示总运费，则总运费为：

下面介绍用Excel来求解以上线性规划模型的解：

打开Excel的一个工作簿，把模型的约束系数矩阵置于A1至F5中，约束常数置于H1至H5中，而目标函数系数置于A6至F6中。选择A8至F8为“可变单元”（即相对于变量），并输入初值0，如左图所示。

在单元格G1处输入“=A1*A8+B1*B8+C1*C8+D1*D8+E1*E8+F1*F8”，即第一个约束不等式的左边；类似对G2，G3，G4，G5做处理，即完成约束条件的左边表达式的输入；以单元格G6为目标单元格，输入“=A6*A8+B6*B8+C6*C8+D6*D8+E6*E8+F6*F8”。

打开菜单的“工具”项，在下拉菜单中选择“规划求解”。在“设置目标单元格”中输入“G6”，再选“最小值”，再“可变单元格中”输入“A8：F8”,在“约束”中按一下“添加”按钮，把六个条件填好，再进入“选项”，选择“采用线性模型”，再按“求解”按钮即可获得结果。

这时即可从A1至F8中读出模型的最优解为：

即从1号仓库分别运10t、15t、25t到一、二、三号工厂；从2号仓库运30t到一号厂时总运费最省，为170元。

例2：汽车厂生产计划问题

一汽车厂生产小、中、大三种类型的汽车，已知各类型每辆车对钢材、劳动时间的需求，利润以及每月工厂钢材、劳动时间的现有量如下表所示。试制订月生产计划，使工厂的利润最大。

小型中型大型现有量

钢材（吨）1.535600

劳动时间（小时）28025040060000

利润（万元）234

模型建立与求解：

设每月生产小、中、大型汽车的数量分别为，，，工厂的月利润为，很容易得到可得线性规划模型为：

类似例1的步骤打开一个Excel工作簿，在A1至C3单元格内输入相关数据；在D1输入“=A1*A5+B1*B5+C1*C5”，类似对D2，D3做处理；选择“工具”中的“规划求解”，选D3为目标单元格，选“最大值”，再设定“可变单元格为”A5：C5，再按例1方法输入约束条件，即得到一组最优解：=64，=168，=0，最优值=632，即月生产计划为每月生产小型车64辆，中型车168辆，不生产大型车。

二、非线性规划模型求解

例3:设有一个三角形，三个顶点的坐标分别为A(1,1),B(2,5),C(3,1),试在平面上求一点M(x,y)，使其到三顶点的距离之和为最短。

模型建立与求解：有平面两点距离公司很容易得到以下模型：

现用Excel求解模型：

打开Excel的一个工作表

选择A1至B1范围作为可变单元，并输入初值1

选择C1作为目标单元，输入：

“=SQRT((A1-1)^2+(B1-1)^2)+SQRT((A1-2)^2+(B1-5)^2)+SQRT((A1-3)^2+(B1-1)^2)”

进入“规划求解”界面，在“设置目标单元格”中输入“C1”,然后选“最小值”，再在“可变单元格”中输入“A1：B1”，在“约束”中添加约束：“A1>=1”、“A1<=3”、“B1>=1”、“B1<=5”，最后，规划求解参数界面。

此时按“求解”即可获得结果如图7。

最后得出模型的最优解为

从以上例子可以看到，我们用常用的办公软件Excel就可以解决一般的生产管理中的线性规划模型，而且操作也是很简单的，易于一般工作人员掌握。

参考文献：

[1]边馥萍,侯文华，梁冯珍.数学模型方法与算法.高等教育出版社,2005。

[2]姜启源，谢金星，叶俊.数学模型（第三版）.高等教育出版社,2003。

[3]梁炼.数学建模.华南理工大学出版社，2005。