下载文档原格式
第2章 线性规划的计算机求解及应用举例§1线性规划模型在电子表格中的布局线性规划模型在电子表格中布局的好坏关系到问题可读性和求解方便性的高低。
本节以第一章中的例1（资源分配问题）为例来说明一下如何在电子表格中描述线性规划模型，让我们回顾一下第一章中例1的数学模型：Max 1243Z x x =+s.t. 1212126282318,0x x x x x x ≤⎧⎪≤⎪⎨+≤⎪⎪≥⎩ （2.1）一般来说，在与问题相关的表格的基础上稍加调整就可以在电子表格中形成一个十分清晰的模型描述。
我们以表1-1为基础在Excel 电子表格中将上述问题描述如图2-1。
§2用Excel规划求解工具求解线性规划模型Excel 中有一个工具叫规划求解，可以方便地求解线性规划模型。
“规划求解”加载宏是Excel 的一个可选加载模块，在安装Excel 时，只有在选择“定制安装”或完全安装时才可以选择装入这个模块。
如果你现在的Excel 窗口菜单栏的“工具”菜单中没“规划求解”选项，可以通过“工具”菜单的“加载宏”选项打开“加载宏”对话框来添加“规划求解”（见图2-2）。
在应用规划求解工具以前，要首先确认在Excel 电子表格中包括决策变量、目标函数、约束函数三种信息的单元格或单元格区域。
图2-1中的电子表格中就已经有了这部分内容：决策变图2-1 资源分配问题的模型在Excel 电子表格的布局及公式图2-2 加载宏对话框量在C9和D9单元格中；目标函数的系数在第8行；约束函数在第5、6和7行。
因为我们不知道决策变量的值是多少，所以就在决策变量所在的单元格中填上初始值“0”，当然也可以什么都不填，系统会默认它为0，在求解以后Excel会自动将它们替换成决策变量的最优解。
下面我们接着上节的内容用Excel规划求解将第一章例1的资源分配问题解一遍。
首先将要求解模型的所有相关信息和公式像图2-1那样填入电子表格中后，再选取[工具] | [规划求解]命令后，弹出图2-3所示的“规划求解参数”对话框。
实验一：规划求解操作（线性规划问题）一、实验目的在Excel 软件中加载规划求解工具，使用Excel 软件求解线性规划问题。
二、实验内容1. 在Excel 软件中，加载“规划求解”工具。
2. 在Excel 窗体上输入问题的数据及计算公式。
3. 使用规划求解进行分析，找出线性规划问题的最优解。
4. 对结果进行简单分析。
某营养师建议一位缺铁质与维生素B 的病人，应在一段时间内摄取至少2400mg 的铁质、2100mg 的维生素B1与1500mg 的维生素B2。
现在考虑A, B 两个牌子的维生素，A 牌的维生素每颗含40mg 铁质、10mg 维生素B1与5mg 维生素B2；B 牌的维生素每颗含10mg 铁质，以及各15mg 的维生素B1与B2。
已知A 牌维生素每颗6元，B 牌每颗为8元。
试问在满足营养师建议的情况下，A 与B 两种厂牌的维生素各应服用多少才能使花费的费用最少？1212121212min 684010240010152100 .5151500,0z x x x x x x s t x x x x =++≥⎧⎪+≥⎪⎨+≥⎪⎪≥⎩ 三、实验步骤1. 加载规划求解工具，如图1-1a~图1-1c 。
2. 在窗体上输入问题数据及模块，服用量可先输入任意数值，如图1-2。
3. 输入目标函数和约束的计算公式，如图1-3。
4. 打开规划求解工具，如图1-4。
5. 完成规划求解的参数设定，如图1-5a~图1-5d。
6. 找出线性规划问题的最优解，如图1-6a与图1-6b。
图1-1a 加载规划求解工具图1-1b 加载规划求解工具图1-1c 加载规划求解工具图1-2 输入问题数据与模块图1-3 输入公式图1-4 打开规划求解工具图1-5a 参数设定图1-5b 参数设定图1-5c 参数设定图1-5d 参数设定图1-6 找出线性规划问题的最优解图1-6b 线性规划问题的敏感性报告。
使用Excel进行线性规划求解功能，轻松找到问题的最优的解
决方案
在我们的工作中，规划求解是十分常见的应用场景，是一种研究线性约束条件下线性目标函数的极值问题的数学理论和方法。
比如在生产管理中，在人工、材料等等条件的约束下，如何安排才能使工厂利益的最大化问题就是典型的规划问题。
而对于此类问题的求解，如果使用手工求解的方式还是存在一定的困难，但是如果使用Excel这个工具的话，就能轻松的进行求解。
下面，我就通过一个工厂生产利润最大化的例子来给小伙伴们讲解下具体的使用方法。
题目：某家具生产厂可以生产A、B、C、D四种家具，四种家具所需要的人工、木材、玻璃等的量是不同的，同时由于市场
的限制，每种家具的最大销售量也是有限制的。
四种家具的所
需材料、市场限额、利润见下表：
根据上述要求，可以设该厂生产A、B、C、D四种家具的量分别为X1、X2、X3、X4，则利润为：maxZ=60X1+66X2+40X3+50X4。
约束条件如下：
根据以上条件，在Excel中做出以下求解模版：
根据以上分析，目标值单元格的公式如下：
=SUMPRODUCT(B13:E13,B6:E6)。
时间约束，木材约束，玻璃约束的使用量公式分别为：=SUMPRODUCT(B18:E18,$B$13:$E$13)
=SUMPRODUCT(B19:E19,$B$13:$E$13)
=SUMPRODUCT(B20:E20,$B$13:$E$13)
专栏
从进销存系统入门ExcelVBA编程。
《运筹学》课程实验报告
班别数学1410 姓
名杨欢
学号1101141020 实验室号28实验室
日期2015年12月4日组号计算机号52 实验
名称
Excel求解线性规划问题成绩评定
所用
软件Excel
老师签名
实
验
目的或要求能够熟练建立线性规划数学模型，熟练掌握Excel求解线性规划问题的应用。
通过实验进一步掌握运筹学有关方法原理、求解过程，提高分析问题和解决问题。
实
验过
程、心
得或体会实验过程：
1．建立线性规划数学模型。
Max z=2x1+3x2
s.t. 2x1+2x2<=12
4x1 <=16
5x2<=15
X1 , x2>=0
2．在Excel中建立线性规划问题。
3．应用Excel求解该规划问题。
（1）单击“工具”菜单“规划求解”，出现“规划求解参数”对话框：依次在“设置目标单元格”输入“目标值”、“可变单元格”中输入“变量”，选中“最大值”单选按钮；
（2）单击“规划求解参数”对话框中“添加”按钮，出现“添加约束”对话框，按对话框要求依次添加约束条件“资源一（二、三……）实际使用量<=资源一（二、三……）提供量”，单击“确定”按钮。
（3）单击“规划求解参数”对话框中“选项”按钮，出现“规划求解选项”对话框，选中“采用线性模型”和“假定非负”多选按钮后单击“确定”按钮。
（4）单击“规划求解参数”对话框中“求解”按钮，出现“规划求解结果”对话框，单击“确定”按钮后得到求解结果。
实验结论：
当x1=3,x2=3时，目标函数最大，为15。
第三节使用Excel求解线性规划问题利用单纯形法手工计算线性规划问题是很麻烦的。
office软件是一目前常用的软件，我们可以利用office软件中的Excel工作表来求解本书中的所有线性规划问题。
对于大型线性规划问题，需要应用专业软件，如Matlab，Lindo，lingo等，这些软件的使用这里我们不作介绍，有需要的，自己阅读有关文献资料。
用Excel工作表求解线性规划问题，我们需要先设计一个工作表，将线性规划问题中的有关数据填入该工作表中。
所需的工作表可按下列步骤操作：步骤1 确定目标函数系数存放单元格，并在这些单元格中输入目标函数系数。
步骤2 确定决策变量存放单元格，并任意输入一组数据。
步骤3 确定约束条件中左端项系数存放单元格，并输入约束条件左端项系数。
步骤4 在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式，计算出约束条件左端项对应于目前决策变量的函数值。
步骤5 在步骤4的数据右边输入约束条件中右端项（即常数项）。
步骤6 确定目标函数值存放单元格，并在该单元格中输入目标函数值的计算公式。
例建立如下线性规划问题的Excell工作表：1212121212max1502102310034120..55150,0z x xx xx xs tx xx x=++≤⎧⎪+≤⎪⎨+≤⎪⎪≥⎩解：下表是按照上述步骤建立的线性规划问题的Excell工作表。
其中：D4=B2*B4+C2*C4, D5=B2*B5+C2*C5 , D6=B2*B6+C2*C6, C7= B2*B1+C2*C1 。
建立了Excel工作表后，就可以利用其中的规划求解功能求相应的线性规划问题的解。
求解步骤如下：步骤1单击[工具]菜单中的[规划求解]命令。
步骤2 弹出[规划求解参数]对话框，在其中输入参数。
置目标单元格文本框中输入目标单元格；[等于]框架中选中[最大值＼最小值］单选按钮。
步骤3 设置可变单元格区域，按Ctrl键，用鼠标进行选取，或在每选一个连续区域后，在其后输入逗号“，”。
第三节 使用Excel 求解线性规划问题
利用单纯形法手工计算线性规划问题就是很麻烦的。
office 软件就是一目前常用的软件,我们可以利用office 软件中的Excel 工作表来求解本书中的所有线性规划问题。
对于大型线性规划问题,需要应用专业软件,如Matlab,Lindo,lingo 等,这些软件的使用这里我们不作介绍,有需要的,自己阅读有关文献资料。
用Excel 工作表求解线性规划问题,我们需要先设计一个工作表,将线性规划问题中的有关数据填入该工作表中。
所需的工作表可按下列步骤操作:
步骤1 确定目标函数系数存放单元格,并在这些单元格中输入目标函数系数。
步骤2 确定决策变量存放单元格,并任意输入一组数据。
步骤3 确定约束条件中左端项系数存放单元格,并输入约束条件左端项系数。
步骤4 在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式,计算出约束条件左端项对应于目前决策变量的函数值。
步骤5 在步骤4的数据右边输入约束条件中右端项(即常数项)。
步骤6 确定目标函数值存放单元格,并在该单元格中输入目标函数值的计算公式。
例 建立如下线性规划问题的Excell 工作表:
12
121
21212max 1502102310034120..55150
,0z x x x x x x s t x x x x =++≤⎧⎪+≤⎪⎨+≤⎪⎪≥⎩
解:下表就是按照上述步骤建立的线性规划问题的Excell 工作表。
其中:
D4=B2*B4+C2*C4, D5=B2*B5+C2*C5 , D6=B2*B6+C2*C6, C7= B2*B1+C2*C1 。
建立了Excel 工作表后,就可以利用其中的规划求解功能求相应的线性规划问题的解。
求解步骤如下:
步骤1 单击[工具]菜单中的[规划求解]命令。
步骤2 弹出[规划求解参数]对话框,在其中输入参数。
置目标单元格文本框中输入目标单元格;[等于]框架中选中[最大值＼最小值］单选按钮。
步骤3 设置可变单元格区域,按Ctrl 键,用鼠标进行选取,或在每选一个连续区域后,在其后输入逗号“,”。
步骤4 单击[约束］框架中的[添加]按钮。
步骤5 在弹出的[添加约束]对话框个输入约束条件.
步骤6 单击[添加]按钮、完成一个约束条件的添加。
重复第5步,直到添加完所有条件
步骤7 单击[确定]按钮,返回到[规划求解参数]对话框,完成条件输入的[规划求解参数]对话框。
步骤8 点击“求解器参数”窗口右边的“选项”按钮。
确信选择了“采用线性模型”旁边的选择框。
这就是最重要的一步工作！如果“假设为线性模型”旁边的选择框没有被选择,那么请选择,并点击“确定”。
如果变量全部非负,而“假定变量非负”旁边的选择框没有被选择,那么请选择,并点击“确定”。
步骤9 单击[求解]按钮,弹出[规划求解结果]对话柜,同时求解结果显示在工作表中。
步骤10 若结果满足要求,单击[确定]按钮,完成操作;若结果不符要求,单击[取消]按钮,在工作表中修改单元格初值后重新运行规划求解过程。
例 利用Excell 工作表求解线性规划问题
12
121
21212max 1502102310034120..55150
,0z x x x x x x s t x x x x =++≤⎧⎪+≤⎪⎨+≤⎪⎪≥⎩ 。
解:1 将光标方在目标函数值存放单元格(C7),点击“工具”,出现下图:
2 点击“规划求解”出现下图
如果就是求最小值问题,选择“最小值”。
3.在可变单元格中选择决策变量单元格B2,C2,出现下图。
4、点击“添加”,出现下图。
5、输入约束条件
6、输入约束条件,点击“确定”,出现下图。
7、点击“选项”,出现下图。
如果“采用线性模型”前没有√,点击“采用线性模型”;如果“假定非负”前没有√,点击“假定非负”。
8、 点击确定,回到规划求解对话框,出现下图。
9、点击“求解”,出现下图‘
计算机给出求解信息“规划问题找到一解,可满足所有约束条件及最优状况”,这说明,问题有最优解。
10、点击“确定”,回到Excell 工作表,出现下图。
在工作表中,给出了最优解情况:
120,30,max 6300x x z === 。
在工具栏中,如果没有“规划求解”项目,可通过“加载宏”添加规划求解功能。
提醒大家注意的就是,在计算机安装时,很多计算机的office软件就是典型安装的,这时,需要有office 软件的安装盘。
利用Excell工作表的规划求解功能,可得案例1、1 火电厂动力配煤问题的最优解为: 1号矿井生产的煤的使用量为0吨;
2号矿井生产的煤的使用量为313、07吨;
2号矿井生产的煤的使用量为649、72吨;
2号矿井生产的煤的使用量为37、2顿:
最小总成本就是699193、13元。
下表就是相应的Excell求解表格。
