[文章]：线性规划与Excel 2000应用

qinhl96

关键词：运筹学•线性规划与电子表格
1 概述
线性规划是运筹学的一个重要分支，在实际应用中日益广泛与深入，特别是在电子计算机能处理千万个约束条件和决策变量的线性规划问题之后，线性规划的适用领域更为广泛。下面就解决最优化方案技术问题，应用办公软件中文Excel 2000电子表格进行数据分析。
Excel 2000线性规划数据分析可以广泛应用于财务、金融、经济、统计、审计、工程设计和施工等众多领域。Excel 2000作为一种电子表格，有这一特点：任意改变表格中某一数据，同时会对所有的数据产生影响。利用Excel 2000中“规划求解”命令寻找出多个约束条件下的最优解，可以给我们在解决线性规划问题上节省很多时间，带来快速解决问题的方法。
2 线性规划原理和基本方法
2.1 线性规划原理
线性规划是以线性方程和不等式描述多领域中的计划、任务、资源分配等的可行性方案和预期目标之间的关系，以求得用最少的资源消耗，求出最优经济技术指标的一种数学方法。
规划问题的解决应满足一定的条件和达到一定的目标,应用数学方式描述便形成线性规划中的线性约束条件方程和线性目标函数方程。
2.2 线性规划基本方法
2.1.1 建立问题线性代数模型
一般先确定要求的未知变量如Ⅰ、Ⅱ、Ⅲ、Ⅳ；然后找出所有的约束条件，并表示为线性方程或不等式，并且是未知量的线性函数。每一组变量的取值代表一个具体的方案；最后找出目标函数，把它表达为未知变量的线性函数，并对它求极大值或极小值。具体数学模型的建立如下：
设线性规划问题有四个方案变量，需要满足三个约束条件，则可得到一般线性规划数学模型，以求一组变量Ⅰ、Ⅱ、ⅢⅣ（非负值），使该问题满足约束条件：
A11Ⅰ+A12Ⅱ+A13Ⅲ+A14Ⅳ=B1
A21Ⅰ+A22Ⅱ+A23Ⅲ+A24Ⅳ=B2
A31Ⅰ+A32Ⅱ+A33Ⅲ+A34Ⅳ=B3
且使目标函数为:
Zmin=C1Ⅰ+C2Ⅱ+C3Ⅲ+ C4Ⅳ
式中Aij(i=1、2--m，j=1、2--m)为消耗系数；Bi(i=1、2--m)为限定系数或常数值；Ci(i=1、2--m)为利润系数或成本系数。且均为已知常数。
2.1.2 数学模型求解
一般变量数目和约束条件较少的情况下，可用手工计算；较多的情况下，则需运用电子计算机求解，以解决实际应用问题。
3 线性规划与Excel 2000应用
3.1.1 启动规划求解
Excel 2000规划求解是对直接或间接与目标单元格中公式相联系的一组单元格中的数值进行调整，最后在目标单元格中求出最优解。“规划求解”是在“工具”菜单中的一个“加载宏”命令，在“加载宏”对话框中选择“规划求解”复选框，再单击“确定”按钮，可启动“规划求解”。
3.1.2 应用线性规划方程求解
例：某桥梁钢筋规格Φ28需要加工制作长度分别为以下3种，6.2m长75根，2.9m长100根，1.45m长100根，钢筋原材料每根长12.0m,试选择最优配料方案。
解:简单的做法是,在每一根材料上截取6.2m长共75根,余料用于截取2.9m长共100根,余料截取1.45m长共100根。但用线性规划列方程式解答如下：
①约束条件方程：
Ⅰ+Ⅱ+Ⅳ=75
2Ⅰ+Ⅲ+Ⅳ=100
4Ⅱ+6Ⅲ+2Ⅳ=100
②目标函数方程:
Zmin=0.4Ⅲ
利用手工计算得出:
Ⅰ=30,Ⅱ=5,Ⅲ=0,Ⅳ=40, Zmin=0.
3.1.3 应用Excel2000规划求解
列电子表格如下表所示：
　 A B C D E F G
1 下料方案 Ⅰ Ⅱ Ⅲ Ⅴ 合计
2 下料组数 30 5 0 40 75
3 每组余料 0 0 0.4 0 0
4 下料
规格
(m) 6.20 1 1 0 1 75
5 2.90 2 0 1 1 100
6 1.45 0 4 6 2 100
具体步骤如下：
①合并单元格A1B1、A2B2、A3B3、A4A5A6，并相应输入“下料方案”、“下料组数”、“每组余料”、“下料规格（m）”，在C1、D1、E1、F1、G1单元格内分别输入Ⅰ、Ⅱ、Ⅲ、Ⅳ和合计,相应地在B4-F6区域内输入上表内容。在C3、D3、E3单元格内输入公式，如C3单元格公式=12-（B4×C4+B5×C5+B6×C6）,显示结果为0,以此类推,结果见上表。在G2单元格输入公式=SUM（C2：F2），在G3单元格输入公式=SUM（C3：F3），在G4单元格输入公式=SUM（C4：F4），在G5单元格输入公式=SUM（C5：F5），在G6单元格输入公式=SUM（C6：F6）。
②打开“工具”菜单中的“规划求解”命令，弹出“规划求解参数”对话框图，在“设置目标单元格”编辑框中，输入引用单元格G3，显示$G$3.
③目标单元格中的数值选择为“最小值”。
④在“可变单元格”编辑框中,输入C2、D2、E2、F2，并用逗号隔开。
⑤在“约束”列表框中输入相应的约束，如C2=整数、D2=整数、E2=整数、F2=整数，C2≥0、D2≥0、E2≥0、F2≥0，C2≤100、D2≤100、E2≤100、F2≤100，G4=75、G5=100、G6=100.
⑥单击“求解”按钮，Excel2000就可以根据约束条件求出所要的最优解。
本例题用手工计算速度应比用Excel2000快，但在实际应用中使用方案并不可能是四个方案，在大于四个方案、三个约束的情况下，如20个方案、15个线性约束，再用手工计算线性规划方程，几乎是不可能，但用Excel2000求解可轻而易举得出结果。
在实际工作中，可以把电子表格中大部份相关公式运用数组公式，以简化公式输入，节约解决问题的时间,并且可将已使用的规划求解方案建立“方案管理器”，以便多次使用，提高工作效率。

参考资料：
①《运筹学》•1990•北京•清华大学出版社
②《简明施工计算手册》•1999•北京•中国建筑工业出版社
③《中文Excel2000中级教程》•1999•北京•机械工业出版社