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

1、如何在Excel中建立并求解线性规划模型刘桂莲摘要：数学中线性规划问题的求解一直是很繁琐的，功能强大的 Excel软件 为我们提供了一种很好的求解方法，但这种方法却很少被人了解。本文就如何在 Excel中建立并求解线性规划模型作了较详尽的论述。关键词：线性规划数学模型电子表格模型规划求解Excel线性规划是运筹学的一个分支，它的应用已愈来愈深入到社会生产和经济活 动的各个领域。描述线性规划问题的抽象的数学式子是线性规划问题的数学模 型。建立数学模型后，求解满足约束条件的目标函数的最优解是解决线性规划问 题的关键。数学中常用的方法是图解法和单纯形法，而图解法只适用于两个变量 的目标函数，单纯形法

2、则计算量相当大，步骤烦琐，容易出错。在Excel中建立电子表格模型，并利用它提供的“规划求解”工具，能轻松快捷地求解模型的解。例如，某玻璃制品公司有三个工厂，公司目前决定停止不赢利产品的生产并 撤出生产能力来生产两种新开发的产品：玻璃门和双把窗。估计三个工厂每周可 用来生产新产品的时间分别为 4小时、12小时、18小时，而每扇门需工厂1生 产时间1个小时和工厂3生产时间3个小时，每扇窗需工厂2和工厂3生产时间 各为2个小时，预测门的单位利润是300元，窗的单位利润是500元，问每周两 种新产品数量的哪种组合能使总利润最大？这是一个典型的产品组合问题，现将问题中的有关数据列表如下:工厂生产每一个

3、单位产品所需的时间每周用得时间门窗11小时04小时202小时12小时33小时2小时18小时单位利润300元500元问题的决策变量有两个：每周门的生产数量和窗的生产数量，目标是总利润 最大，需满足的条件是：三个工厂每周用于生产新产品的时间w每周可得时间每周门、窗的生产数量均0。设每周门的生产数量为X，窗的生产数量为y， 则该问题的数学模型即为：最大化利润 P =300x+500y,约束条件：xw4, 2y0和 y0。将上表的有关数据输入到 Excel中，建立如图1所示的电子表格模型。被输 入已知数据的单元格是数据单元格，如单元格 C5： D8，G5： G7。决策变量(即 两种产品每周的生产量)放

4、在单元格C9和D9，正好定位在这些产品所在列的数据单元格下面，这种含有需要做出决策的单元格是可变单元格。单元格E5：E7是用来计算各个工厂每周的总生产时间，如单元格E5就是用C5： D5和C9:D9的对应数值各自相乘再总加得到。Excel中有一个叫SUMPRODUCT的函数能对相等行数和相等列数的两个变化范围的单元格中的值乘积后进行加和。被加和的每个值是对第一个变化范围的一些值和对应位置的第二个变化范围的一些 值的积。女口 E5=SUMPRODUCT(C5 : D5，C9： D9)是把C5： D5变化范围的每个 值与C9 : D9变化范围中对应的每个值相乘，然后各个积相加。同样E6=SUMPR

5、ODUCT(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：ABCD

6、EFG1玻璃制品公司的产品组合问题23生产单位产品的时间4门窗时间总数可得时间5工厂1100=46工厂2020=127工厂3320=188单位利润30050009结论00Excel中有一个叫“规划求解”的工具，能快速求解线性规划问题，步骤如下：1在工具菜单中选择“规划求解”，会弹出一个对话框。在对话框中，将 E8 键入目标单元格，将C9： D9键入可变单元格，既然目标是要最大化目标单元格， 还必须选中“最大值” （Max）2 点击对话框中的“添加”按纽（Add），弹出添加约束对话框，将约束条 件具体化。左端输入范围 E5： E7,右端输入范围G5： G7,中间的符号可选择 “=”。如果还要添加

7、更多的函数约束就再点击“添加”按钮以弹出一个新的添 加约束对话框，在这个例子中没有其它约束了。下一步只要点击“确定”按钮回 到“规划求解”对话框。3点击“选项”按钮，在新弹出的对话框中，选中“采用线性模型”和“假 定非负”选项，这就告诉了计算机要求解的问题是一个线性规划问题以及非负约 束，点击“确定”再回到“规划求解”对话框。4点击“求解”按钮，计算机会在后台开始对问题进行求解。几秒钟之后 会显示运行结果，一般而言，它会显示已经找到一个最优解。如果模型没有可行 解或没有最优解，对话框会显示“规划求解找不到可行解”。求解模型之后，最优值就代替了可变单元格中的初始值，最优解是每周2扇门和6扇窗，目

8、标单元格的对应数值（每周总利润）为 3600元。求解的电子表格模型（图2）如下：ABCDEFG1玻璃制品公司的产品组合问题23生产单位产品的时间4门窗时间总数可得时间5工厂1102=46工厂20212=127工厂332180 (i=1、2、3, j=1、2、3、4)电子表格模型如图3:ABCDEFGHI1婴儿车的配送问题2到各配送中心的单位运输成本312344工厂1506040205工厂2209010306工厂33040201078运送到各配送中心的单位数91234总量工厂产量10工厂100000=1211工厂200000=1712工厂300000=1113总量0000014=15中心接受量1

9、0101010模型中的单元格C4: F6, C15: F15,以及110: 112分别输入了单位运输成 本，中心接受量及工厂产量的数值，它们是数据单元格。 12个决策变量(各工 厂送到各分配中心的单位数)放在单元格 C10： F12中，它们是可变单元格，起 始值设置为0单元格 C13： F13 及 G10： G12 是输出单元格 C13=SUMPRODUCT(C10 : C12),D13= SUMPRODUCT(D10 : D12),E13= SUMPRODUCT(E10 : E12),F13= SUMPRODUCT(E10 :E12),G1O=SUMPRODUCT(C1O :F10),G11

10、=SUMPRODUCT(C11: F11), G12=SUMPRODUCT(C12: F12), G13是目标单 元格(总成本)，它正好是数据单元格C4: F6和可变单元格C10: F12对应数值 相乘再加和得到即G13=SUMPRODUCT( C4: F6, C10: F12)，模型中的等号 体现了函数的约束。以上模型通过点击“规划求解”工具，因为是最小化目标单元格 G13,所以 选“最小值”(Min)；在“添加”约束里，添加“ G10: G12=I10: I12 ”和“ C13: F13=C15: F15”，仍然选“采用线性模型”和“假定非负”，得出如下结论：(图 4)ABCDEFGHI1

11、婴儿车的配送问题2到各配送中心的单位运输成本312344工厂1506040205工厂2209010306工厂33040201078运送到各配送中心的单位数91234总量工厂产量10工厂10201012=1211工厂21007017=1712工厂3083011=1113总量1010101097014=15中心接受量10101010即工厂1运送到配送中心2和配送中心4分别为2和10个单位；工厂2运送到 配送中心1和配送中心3分别为10和7个单位；工厂3运送到配送中心2和配 送中心3分别8和3个单位，这样总的运输成本最低，最低成本是 970元。需要注意的是：用电子表格模型表述函数约束条件时，往往用到输出单元格， 而输出单元格必须使用 SUMPROD