如何利用Excel 软件的规划求解功能求解线性规划问题Word格式文档下载.docx
《如何利用Excel 软件的规划求解功能求解线性规划问题Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《如何利用Excel 软件的规划求解功能求解线性规划问题Word格式文档下载.docx(12页珍藏版)》请在冰豆网上搜索。
约束条件、目标函数是线性规划的三要素。
求解线性规划问题的基本方法是单纯形法。
对于只有两个决策变量的简单的线性规划问
题,也可采用图解法求解,这种方法仅适用于只有两个决策变量的线性规划问题。
当决策变量比较多时,通常需由计算机完成求解。
LINDO和LINGO都是求解线性规划问题的优秀专业工具软件。
作为MicrosoftOffice软件组件之一的Excel,其“规划求解”功能强大,可以轻松实现对多个决策变量的线性规划问题的求解,回避了使用线性规划专业求解软件求解时对操作者的专业要求,同时也克服了笔算的缺点,其操作简单、方便、快捷,可大大提高计算的效率和准确性。
软件操作环境:
Windows10家庭版,Excel2016
线性规划问题如下:
某建筑公司用沙、石料、水泥、钢材4种材料进行甲、乙两个工程项目的施工,按照施
工要求,甲项目施工时所用沙、石料、水泥、钢材4种材料的比例为8:
6:
3:
2,可获利润为
100元/m2。
乙项目施工时所用沙、石料、水泥、钢材4种材料的比例为2:
5:
4,可获利润
为120元/m2。
现有库存沙、石料、水泥、钢材4种材料的量分别为3000吨、2500吨、2000吨、1500吨。
求:
(1)按现有的原料库存,甲、乙两个项目各施工多少平方米时能获得的利润最大?
此时的最大利润为多少?
(2)要获得利润50000元时,甲、乙两个项目应各施工多少平方米?
分析过程:
问题
(1)的求解,可以使用线性规划中的单纯型方法来求解,但其计算是比较复杂的。
使用Excel规划求解功能来求解,问题就简单得多了。
首先,构建问题的数学模型。
设甲、乙工程项目施工平方米数各为x1、x2,最大利润为Smax,则可得到如下的线性规
划数学模型。
Smax=100x1+120x2
按Excel规划求解功能的要求,把决策变量、约束条件和目标函数分别输入Excel工作表
的适当位置。
然后打开Excel“规划求解参数”对话框,设置目标单元格、可变单元格、约束条件,最后单击“求解”按钮求解。
操作过程如下:
1.打开工作表,在C2、D2单元格分别输入“甲项目”、“乙项目”。
2.在B3单元格输入“单位利润”,在C3、D3单元格分别输入100、120。
3.在C5:
E5、G5单元格分别输入“甲项目比例”、“乙项目比例”、“实际使用”、“库存
材料”在B6:
B9单元格分别输入“沙”、“石料”、“水泥”、“钢筋”;
在C6:
C9单元格分别
输入8、6、3、2,在D6:
D9单元格分别输入2、3、5、4。
4.在E6单元格输入“=C6*C$12+D6*D$12”,选定E6,利用填充句柄向下填充至E9单
元格。
5.在F6:
F9四个单元格均输入“<
=”。
6.在G6:
G9单元格分别输入3000、2500、2000、1500。
7.在C11、D11、G11单元格分别输入“甲项目”、“乙项目”、“总利润”。
8.在B12输入“施工面积”;
在G12输入“=C3*C12+D3*D12”。
各项输入后如图所
示。
9.将以上制作的文件以“线性规划的求解.xlsx”保存。
如果是Excel2010以下版本:
1.在“数据”选项卡菜单中,选择“规划求解”选项,打开“规划求解参数”对话框,设置目标单元格、可变单元格和约束条件:
说明:
如果是Excel2016中,在实施以上线性规划问题求解时,可按以下实施步骤进行。
打开线性规划的求解.xlsx文档
在文件菜单,选中选项菜单,选择自定义功能区选项,开发工具,如下图所示:
10点击“开发工具”功能,点击其中的“Excel加载项”功能,找到“规划求解加载项”,在前边打对勾,点击“确定”。
点击后,找到“数据”功能,可以看到新增了“规划求解”功能
点击后,找到“数据”功能,可以看到新增了“规划求解”功能
1.单击“规划求解”功能选项,打开“规划求解参数”对话框,
设置目标单元格、可变单元格和约束条件:
在“设置目标单元格”栏中输入表示目标函数值的单元格地址$G$12(也可直接单击G12
单元格),并在“等于”一栏中选择“最大值”单选项。
在“可变单元格”一栏中输入决策变量的单元格地址“$C$12:
$D$12”如下图所示:
在“约束”中,单击“添加”按钮,在弹出的“添加约束”对话框中添加约束条件,
在“单元格引用位置”输入决策变量的单元格地址“$C$12:
$D$12”,将其右
的关系运算符选为“>
=”,在“约束值”栏中输入0,单击“添加”按钮完成第一个约束条件的设置,如下图所示:
并进入第二个约束条件的设置;
在“单元格引用位置”输入表示各种材料实际使用量的
单元格地址“$E$6:
$E$9”,将其右的关系运算符选为“<
=”,在“约束值”栏中输入表示各种材料库存量的单元格地址“$G$6:
$G$9”,单击“确定”按钮完成约束条件的设置,如下图所示:
选择求解方法:
单纯线性规划,,单击”求解”按钮,在弹出的选项中选中保留规划求解的解,可以看到在文件中生成了规划求解的解,如下图所示:
2.如果是Excel2010以下版本,则需要单击“选项”按钮,在弹出的“规划求解选项”对话框中,选定“采用线性模型”复选项,其他参数不变,单击“确定”按钮返回“规划求解参数”对话框。
3.单击“规划求解参数”对话框右上角的“求解”按钮,系统开始进行规划求解。
4.在弹出的“规划求解结果”对话框中,选中“保存规划求解结果”单选
项,单击“确定”按钮,在该模型有可行解的情况下,即可求得该线性规划的解。
以上线性规划求解文件,在模型有可行解的情况下,即可求得该线性规划的解;
若模型
无解,则在弹出的“规划求解结果”对话框中显示的是“规划求解找不到有用的解”。
若要利用规划求解功能求解问题
(2),即求解“要获得利润50000元时,甲、乙两个项目应各施工多少平方米”的问题,则只需在“设置目标单元格”及其值时,在“等于”一栏中选择“值为”单选项,并在其右的文本栏中输入50000,而其他步骤不变,选择求解方法:
单纯线性规划,,单击”求解”按钮,在弹出的选项中选中保留规划求解的解,可以看到在文件中生成了规划求解的解,要注意的是,求解结果只显示一个解,而满足条件的解往往不止一个。
如下图所示:
单击”求解”按钮,选中保留规划求解的解,可以看到在文件中生成了规划求解的解,如下图所示:
Excel2007的结果如下图所示: