用Excel求解线性规划及线性方程组的方法

第23卷总第44期 西北民族学院学报(自然科学版)Vol.23,No.2 2002年6月 Journal of N orthw est Minorities U niversity(Natural Science)J une,2002

用Excel求解线性规划及线性方程组的方法

王培麟

(番禺职业技术学院,广东番禺511483)

[摘　要]对利用美国微软公司开发的Office组件中的电子表格软件Excel求解线性规划的方法给予了介绍,并将该功能给予扩充,给出了用该软件求解线性方程组的方法1

[关键词]Excel;线性规划;求解方法

[中图分类号]TP271+.7 [文献标识码]A [文章编号]1009-2102(2002)02-0037-03

Excel是美国微软公司开发的Office组件中的电子表格软件,它具有强大的电子表格处理功能,使用户能够轻松地制作表格,并具有对数据进行检索、分类、筛选、排序、计算、分析与统计等功能1对大多数用户而言,也许更注重于Excel的表格功能,而对于它的计算功能,特别是数学计算功能可能就不是十分熟悉1本文将介绍用Excel解线性规划及线性方程组的方法与技巧1

1　用Excel解线性规划

用Excel解线性规划,必须在Excel系统中加载“规划求解”项目1如果没有,可以启动Excel软件,进入Excel用户界面,然后使用“工具”菜单下“加载宏”菜单项之“规划求解”子项,则可完成“规划求解”项的加载1

下面通过例1的求解来说明使用Excel解线性规划问题的方法1

例1　线性规划模型为:

min s=2x1+7x2+4x3+9x4+5x51

S.t　3x1+2x2+x3+6x4+18x5≥700

x1+0.5x2+0.2x3+2x4+0.5x5≥30

0.5x1+x2+0.2x3+2x4+0.5x5=200

x1≤50;x2≤60;x3≤50;x4≤70;x5≤40; x1,x2,x3,x4,x5≥0

1

求解的具体方法为:首先要建立电子表格模型,输入如图1所示的工作表1

工作表的格式不是固定不变的,可根据具体的需要进行调整1建立工作表的步骤为:

1)确定一些单元格来代表决策变量,本例中x1,x2,…,x5为决策变量,需要将它们放到一些单元格中,称为可变单元格1一般地,可变单元格使用Excel的某行一块连续的区域,如

[收稿日期]2002-04-01

[作者简介]王培麟(1963—),男,副教授,硕士,主要从事数学和计算机方面的教学与研究1

—

7

3

—

本例中有5个决策变量,则可用区域B2:F2作为可变单元格(注意:B1:F1放的是说明,其下放的是可变单元格)1求解前,可变单元格放的是决策变量的初值,一般我们使用0作为初始值(此时,这些单元格不做任何输入,表示它们的初值为0)1

2)输入目标函数系数,本例中将它们输入在B3:F3单元格1

图1　规划求解电子表格模型

3)确定目标单元格,并在其中输入目标函数表达式1本例中取G3作为目标单元格1在G3中输入公式:=SUMPRODUCT(＄B＄2:＄F＄2,B3:F3),它的含义是目标函数表达式:

s=2x1,+7x2+4x3+9x4+5x51

注意公式中决策变量单元格使用的是绝对地址,这样做是为了方便公式的复制1

4)输入约束条件左右两端的数据,并将目标函数单元格中的公式复制到目标函数下面相应的单元格中,本例中需将公式复制到G4∶G111

通过以上步骤就建立好了电子表格模型,然后就可以进入规划的求解阶段1具体步骤为:

1)选取菜单栏中“工具”菜单下的“规划求解”菜单项,弹出如图2所示的“规划求解参数”对话框1

2)在“设置目标单元格”文本框中输入目标单元格地址,本例中为G31

3)在“等于”项目上选定“最小值”选项1

4)在“可变单元格”文本框中输入可变单元格区域地址,本例中为B2:F21

5)单击“添加”按扭,弹出如图3所示的“添加约束”对话框1

图2　规划求解对话框 图3　添加约束对话框

—

—

8

3

在其中依次输入所有约束条件,每输入一个约束条件后,单击“添加”按扭,进入下一个约束条件的输入1本例中约束条件如下:

G4:G5>=H4:H5;G6=H6;G7:G11<=H7:H11;B2:F2>=0

然后按“确定”按扭,返回“规划求解参数”对话框1

6)单击“选项”按扭,弹出“规划求解”选项对话框,在该对话框中,可以输入“最长运算时间”、“迭代次数”、“精度”、“收敛度”等参数1如果无特殊需要,建议使用默认值1选取“采用线性模型”后按“确定”按扭返回,“规划求解”对话框1

7)单击“求解”按扭,规划求解软件开始运行,运算结束后,弹出“规划求解结果”对话框,通过该对话框可以保存求解结果,并给出运算结果报告,同时可变单元格和目标单元格分别显示最优解和最优值1

2　用Excel 解线性方程组

将线性方程组看作目标函数永远都为0,而约束条件均为等式的线性规划,则可以利用Excel 的规划求解功能求解线性方程组1

下面通过例2的求解来说明使用Excel 解线性方程组的方法1

例2　设所要求解的线性方程组为:x

1-x 2+x 3-2x 4=2

2x 1-x 3+4x 4=4

3x 1+2x 2+x 3=-1-x 1+2x 2-x 3+2x 4=-4

1

求解的具体方法为:首先要建立电子表格模型,输入如图4所示的工作表1

图4　线性方程组求解电子表格模型

注意到该表格中目标函数系数单元格中的系数全部为01

在目标单元格中输入公式:=SUMPRODUCT (＄B ＄2:＄E ＄2,B3:E3),并将该公式复制到目标单元格下面相应的单元格中,本例中为F4:F7区域1然后选取菜单栏中“工具”菜单下的“规划求解”菜单项,弹出如图2所示的“规划求解参数”对话框1在该对话框中,设置目标单元格和可变单元格的方法与解线性规划是一样的,本例中,目标单元格为F3,可变单元格为

(转第49页)