6-使用Excel求解线性规划问题

6-使用Excel求解线性规划问题
6-使用Excel求解线性规划问题

第三节 使用Excel 求解线性规划问题

利用单纯形法手工计算线性规划问题是很麻烦的。office 软件是一目前常用的软件,我们可以利用office 软件中的Excel 工作表来求解本书中的所有线性规划问题。对于大型线性规划问题,需要应用专业软件,如Matlab ,Lindo ,lingo 等,这些软件的使用这里我们不作介绍,有需要的,自己阅读有关文献资料。

用Excel 工作表求解线性规划问题,我们需要先设计一个工作表,将线性规划问题中的有关数据填入该工作表中。所需的工作表可按下列步骤操作:

步骤1 确定目标函数系数存放单元格,并在这些单元格中输入目标函数系数。 步骤2 确定决策变量存放单元格,并任意输入一组数据。

步骤3 确定约束条件中左端项系数存放单元格,并输入约束条件左端项系数。

步骤4 在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式,计算出约束条件左端项对应于目前决策变量的函数值。

步骤5 在步骤4的数据右边输入约束条件中右端项(即常数项)。

步骤6 确定目标函数值存放单元格,并在该单元格中输入目标函数值的计算公式。 例 建立如下线性规划问题的Excell 工作表:

12

121

21212max 1502102310034120..55150,0

z 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,0

z 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 求解表格。

使用Excel求解线性规划问题

1.7.使用Excel求解线性规划问题 例:Case Chemicals生产两种溶剂CS-01和CS-02。这些溶剂可以用来溶解某些有毒物质。Case Chemicals的生产工厂有两个部门—混合(blending)和净化(purification)。每个部门每周工作40个小时。混合部门有5个全职(full-time)的工人和2个兼职(part-time)的工人,这两个兼职的工人每人每周工作15个小时。这些工人操作7台机器来混合某些化学物质生产溶剂。每1000加仑的CS-01需要2个小时去混合,同样数量的CS-02只需要1个小时去混合。产品在混合部门混合后需要去净化部门净化。净化部门有7台净化机器,并且雇了6个全职的工人和1个兼职的工人,兼职的工人每周工作10个小时。60分钟可以净化1000加仑的CS-01或500加仑的CS-02。Case Chemicals原材料供应充足,市场对CS-01的需求是供不应求,但是市场对CS-02的需求每周最多120,000加仑。据估计,每加仑CS-01可以赚$0.30,每加仑的CS-02可以赚$0.50。生产经理想要决定最优的生产计划,即应该生产每种溶剂各多少才能最大化利润? 解:(1)决策变量 x1=每周生产CS-01的数量(千加仑) x2=每周生产CS-02的数量(千加仑) (2)目标函数 最大化每周生产CS-01和CS-02的利润 Maximize 利润=CS-01利润+CS-02的利润 =300x1+500x2 Max 300x1+500x2 (3)约束条件 混合部门的总工时的约束 2x1+1x2<=5*40+2*15=230 净化部门的总工时的约束 x1+2x2<=6*40+1*10=250

用excel规划求解并作灵敏度分析

题目 如何利用EXC E L求解线性规划 问题及其灵敏度分析 第 8 组 姓名学号 乐俊松 090960125 孙然 090960122 徐正超 090960121 崔凯 090960120王炜垚 090960118 蔡淼 090960117南京航空航天大学(贸易经济)系 2011年(5)月(3)日

摘要 线性规划是运筹学的重要组成部分,在工业、军事、经济计划等领域有着广泛的应用,但其手工求解方法的计算步骤繁琐复杂。本文以实际生产计划投资组合最优化问题为例详细介绍了Excel软件的”规划求解”和“solvertable”功能辅助求解线性规划模型的具体步骤,并对其进行了灵敏度分析。

目录 引言 (4) 软件的使用步骤 (4) 结果分析 (9) 结论与展望 (10) 参考文献 (11)

1. 引言 对于整个运筹学来说,线性规划(Linear Programming)是形成最早、最成熟的一个分支,是优化理论最基础的部分,也是运筹学最核心的内容之一。它是应用分析、量化的方法,在一定的约束条件下,对管理系统中的有限资源进行统筹规划,为决策者提供最优方案,以便产生最大的经济和社会效益。因此,将线性规划方法用于企业的产、销、研等过程成为了现代科学管理的重要手段之一。[1] Excel中的线性规划求解和solvertable功能并不作为命令直接显示在菜单中,因此,使用前需首先加载该模块。具体操作过程为:在Excel的菜单栏中选择“工具/加载宏”,然后在弹出的对话框中选择“规划求解”和“solvertable”,并用鼠标左键单击“确定”。加载成功后,在菜单栏中选择“工具/规划求解”,便会弹出“规划求解参数”对话框。在开始求解之前,需先在对话框中设置好各种参数,包括目标单元格、问题类型(求最大值还是最小值)、可变单元格以及约束条件等。 2 软件的使用步骤 “规划求解”可以解决数学、财务、金融、经济、统计等诸多实 际问题,在此我们只举一个简单的应用实例,说明其具体的操作 方法。 某人有一笔资金可用于长期投资,可供选择的投资机会包括购买国库券、公司债券、投资房地产、购买股票或银行保值储蓄等。投资者希望投资组合的平均年限不超过5年,平均的期望收益率不低于13%,风险系数不超过4,收益的增长潜力不低于10%。问在满足上述要求的前提下投资者该如何选择投资组合使平均年收益率最高?(不同的投资方式的具体参数如下表。)

怎么利用EXCEL求解线性规划

利用线性回归方法求解生产计划 方法一: 1、建立数学模型: ①设变量:设生产拉盖式书桌x台,普通式书桌y台,可得最大利润 ②确定目标函数及约束条件 目标函数:y = max+ 115 P90 x 约束条件:200 x .....................⑴ +y 10≤ 20 x .....................⑵ 4≤ +y 16 128 x .....................⑶ +y 10 15≤ 220 y x ..........................⑷ ,≥ 2、在Excel中求解线性规划 ①首先,如图1所示,在Excel工作表格输入目标函数的系数、约束方程的系数和右端常数项: 图1 ②将目标方程和约束条件的对应公式输入各单元格中 F2=MMULT(B6:C6,F6:F7); F3=MMULT(B3:C3,F6:F7); F2=MMULT(B4:C4,F6:F7); F2=MMULT(B5:C5,F6:F7);

出现图2样式: 图2 线性规划问题的电子表格模型建好后,即可利用“线性规划”功能进行求解。 选择“工具”→“规划求解”出现“规划求解参数”窗口,如图3所示: 图3 在该对话框中,目标单元格选择F2,问题类型选择“最大值”,可变单元格选择F6:F7,点击“添加”按钮,弹出“添加约束条件”窗口,如图4所示: 图4

根据所建模型,共有4个约束条件,针对约束(1):20 x, +y 20 10≤ 左端“单元格所引用位置”选择F3,右端“约束值”选择D3,符号类型选择“<=”,同理继续添加约束(2)(3)(4),完成后选择“确定”,回到“规划求解参数”对话框,如5图所示: 图5 ④点击“选项”按钮,弹出“规划求解选项”对话框,选择“采用线性模型”和“假定非负”两项,如图6所示: 图6 ⑤点击“确定”→“求解”,选择“运算结果报告”“敏感性报告”“极限值报告”三项,最后点击“确定”,输出结果: 运算结果报告:

使用Excel规划求解解 线性规划问题

使用Excel规划求解解线性规划问题 引言 最近,开始学习运筹学,期望通过学习后能够解决许多困扰自已的难题。 刚开始时,选了很多教材,最后以Hamdy A.Taha著的《Operations Research:An Introduction》开始学习。(该书已由人民邮电出版社出版,书名《运筹学导论-初级篇(第8版)》,不知为什么,下载链接中只有该书配套的部分习题解答,而书中所说的光盘文件找不到下载的地方,因为中译本没有配光盘,因此也就错过了许多示例文件。不知道哪位有配套光盘文件,可否共享???) 线性规划求解的基本知识 线性规划模型由3个基本部分组成: ?决策变量(variable) ?目标函数(objective) ?约束条件(constraint) 示例:营养配方问题 (问题)某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成,含有以下成份: 特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤维。该农场希望确定每天最小成本的饲料配制。 (解答过程) 因为饲料由玉米和大豆粉配制而成,所以模型的决策变量定义为: x1=每天混合饲料中玉米的重量(磅) x2=每天混合饲料中大豆粉的重量(磅) 目标函数是使配制这种饲料的每天总成本最小,因此表示为: min z=0.3×x1+0.9×x2 模型的约束条件是饲料的日需求量和对营养成份的需求量,具体表示为: x1+x2≥800 0.09×1+0.6×2≥0.3(x1+x2) 0.02×1+0.06×2≤0.05(x1+x2) 将上述不等式化简后,完整的模型为:

min z=0.3×1+0.9×2 s.t.x1+x2≥800 0.21×1-0.3×2≤0 0.03×1-0.01×2≥0 x1,x2≥0 可以使用图解法确定最优解。下面,我们介绍使用Excel的规划求解加载项求解该模型。使用Excel规划求解解线性规划问题 步骤1安装Excel规划求解加载项 单击“Office按钮——Excel选项——加载项——(Excel加载项)转到”,出现“加载宏”对话框,如下图所示。选择“规划求解加载项”,单击“确定”。 此时,在“数据”选项卡中出现带有“规划求解”按钮的“分析”组,如下图所示。 步骤2设计电子表格 使用Excel求解线性规划问题时,电子表格是输入和输出的载体,因此设计良好的电子表格,更加易于阅读。本例的电子表格设计如下图所示:

利用excel软件求解线性规划问题

下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。 例1 公司通常需要确定每月(或每周)生产计划,列出每种产品必须生产的数量。具体来说就是,产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束: ● 产品组合使用的资源不能超标。 ● 对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量,因为生产过剩就是浪费(例如,易变质的药品)。 下面,我们来考虑让某医药公司的最优产品组合问题。该公司有六种可以生产的药品,相关数据如下表所示。 设该公司生产药品1~6的产量分别为126,,,x x x (磅),则最优产品组合的线性规划模型为 123456 123456123456123456max 6 5.3 5.4 4.2 3.8 1.86543 2.5 1.545003.2 2.6 1.50.80.70.316009609281041..977108410550,16j z x x x x x x x x x x x x x x x x x x x x x s t x x x x j =++++++++++≤??+++++≤??≤?≤??≤??≤?≤??≤??≥≤≤? 下面用规划求解加载宏来求解这个问题: 首先,如下如所示,在Excel 工作表内输入目标函数的系数、约束方程的系数、右端常数项;

其次,选定目标函数单元、可变单元、约束函数单元,定义目标函数、约束函数 其中,劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”,原料约束函数的定义公式是“=MMULT(B4:G4,J5:J10)”,目标函数的定义公式是“MMULT(B5:G5, J5:J10)”。 注:函数MMULT(B3:G3, J5:J10)的意义是:单元区B3:G3表示的行向量与单元区J5:J10表示的列向量的内积。这一要特别注意的是,第一格单元区必须是行,第二格单元区必须是列,并且两个单元区所含的单元格个数必须相等。 最后,打开规划求解参数设定对话框设定模型 (1)(2)目标函数和可边单元的设定很简单,在此就不再赘述 (3)约束条件的设定 (3.1) 约束条件1234561234566543 2.5 1.545003.2 2.6 1.50.80.70.31600x x x x x x x x x x x x +++++≤??+++++≤? 的设定: 系数矩阵 目标函数的系数 系数矩阵右端常数 可变单元 约束函数单元 目标函数单元

用EXCEL求解线性规划

用EXCEL 求最值 华东师范大学03级教育硕士 江苏省溧阳市戴埠高级中学 潘晓春 〔摘要〕 介绍了用Excel 软件的规划求解功能解决一些常见的求最值问题的方法。主要从一元函数的最值、线性规划和二元函数的最值三个方面去进行探讨。 〔关键词〕 Excel 规划求解 最值 最值问题是生产、科学研究和日常生活中常遇到的一类特殊的数学问题,是高中数学的一个重点,它涉及到高中数学知识的各个方面,解决这类问题往往需要综合运用各种技能。Excel 软件中的规划求解功能将为这类问题的解决提供了一个很有效的方法,而且适用范围较广,具有很强的实用性。 用Excel 解线性规划,必须在Excel 系统中加载“规划求解”项目,如果没有,可以启动Excel 软件,进入Excel 用户界面,然后使用“工具”菜单下“加载宏”菜单项的“规划求解”子项,则可完成“规划求解”项的加载。 本文将从以下三个方面来介绍用Excel 中的规划求解功能进行最值的求解。 一、 一元函数的最值 求函数的最值是高中数学中的一类常见问题,也是高中数学中的一个重点和难点问题,运用Excel 中的规划求解功能能够很快捷地进行求解。 例1. 求函数y = 建立规划求解方案与求解的的步骤如下: (1)在Excel 工作中表选定B1单元中的数据作为自变量x ,在B2单元格中输入目标函数公式“=SQRT(B1*B1 -2*B1+2)+SQRT(B1*B1-10*B1+29)”; (2)选中2B ,然后进入菜单栏上的“工具”|“规划求解…”,在对话框中输入如下内容(如图1) :将“设置目标单元格”设置成“$B$2”,并设置成最小值;可变单元格设置成“$B$1”,单击求解; (3)得出如下内容(如图2):单元格$B$1的值为2.333333,单元格$B$2的值为5,所以当 2.333333x =时,()min 5f x = 运用这一方案,可以解决一元函数的的最值,也可以解决一元函数给定区间内的最值问题。 例2.求函数()12 3f x x x =+[]()1,8x ∈上的最值 建立规划求解方案与求解的的步骤如下: (1)在Excel 工作表中选定1B 单元中的数据作为自变量x ,在2B 单元格中输入目标函数 图 2 图 1

《运筹学》使用Excel求解线性规划问题

第三节 使用Excel 求解线性规划问题 利用单纯形法手工计算线性规划问题是很麻烦的。office 软件是一目前常用的软件,我们可以利用office 软件中的Excel 工作表来求解本书中的所有线性规划问题。对于大型线性规划问题,需要应用专业软件,如Matlab ,Lindo ,lingo 等,这些软件的使用这里我们不作介绍,有需要的,自己阅读有关文献资料。 用Excel 工作表求解线性规划问题,我们需要先设计一个工作表,将线性规划问题中的有关数据填入该工作表中。所需的工作表可按下列步骤操作: 步骤1 确定目标函数系数存放单元格,并在这些单元格中输入目标函数系数。 步骤2 确定决策变量存放单元格,并任意输入一组数据。 步骤3 确定约束条件中左端项系数存放单元格,并输入约束条件左端项系数。 步骤4 在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式,计算出约束条件左端项对应于目前决策变量的函数值。 步骤5 在步骤4的数据右边输入约束条件中右端项(即常数项)。 步骤6 确定目标函数值存放单元格,并在该单元格中输入目标函数值的计算公式。 例 建立如下线性规划问题的Excell 工作表: 12 121 21212max 1502102310034120..55150,0 z 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 单击[确定]按钮,返回到[规划求解参数]对话框,完成条件输入的[规划

利用excel求解线性规划问题

利用excel 求解线性规划问题 “规划求解”示例 例1 美佳公司计划制造Ⅰ、Ⅱ两种家电产品。已知各制造一件时分别占用的设备A ,B 的台时、调试工序时间及每天可用于这两种家电的能力、各售出一件时的获利情况,如下表所示。问该公司应制造两种家电各多少件,使获取的利润为最大。 1.建立数学模型 2. 打开excel ,输入下列数据。 3、如何在工作表中设置问题条件?先设置目标单元格,即最大利润,把它放在E1单元格上,可变单元格放置计划生产Ⅰ和Ⅱ产品的件数,这里把它放在C10:D10区域。F4:F6是约束单元格,要对它们的值进行约束。单击E1,在编辑框输入如图所示的公式。 注意,表示绝对引用的美元符号,可以单击F4功能键添加。 ???????>=<=+<=+<=+=0 ,5242615 5..2max 212121 221x x x x x x x t s x x z

4、单击E4单击格式,在编辑栏上输入公式:=$C$4*$C$10+$D$4*$D$10。绝对引用单元格有一个好处,显示的单元格位置变化时,引用的数据没改变。 5、单击E5单击格式,在编辑栏上输入公式:=$C$5*$C$10+$D$5*$D$10。 6、单击E6单击格式,在编辑栏上输入公式:=$C$6*$C$10+$D$6*$D$10。 7、如何使用规划求解功能?单击工具菜单,如果看不到规划求解选项不要慌,先选加载宏。然后勾选规划求解,确定 单击数据菜单——点击“模拟分析”——

8、单击“规划求解”:指定目标单元格。一种方法是先选中目标单元格E1,单击工具---规划求解。另一种先单击工具---规划求解,再输入目标单元格名称。 输入可变单元格区域。比较快的方法是,单击折叠框,用鼠标选中可变单元格区域:$C$11:$E$11。注意勾选最大值哦。 设置目标: $E$1;点选“最大值”;设置:可变单元: $C$10:$D$10 9.设置条件不等式。单击添加,单击折叠框,选择单元格和不等号,单击关闭窗口,接着添加另一个条件。 1).单击添加:输入约束不等式X1+X2≤0 ,即在E4输入:$E$4≤$F$4 2).单击添加:输入约束不等式X1+X2≤0 ,即在E5输入:$E$5≤$F$5 2).单击添加:输入约束不等式X1+X2≤0 ,即在E6输入:$E$6≤$F$6

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

如何在Excel中建立并求解线性规划模型 刘桂莲 摘要:数学中线性规划问题的求解一直是很繁琐的,功能强大的Excel软件为我们提供了一种很好的求解方法,但这种方法却很少被人了解。本文就如何在Excel中建立并求解线性规划模型作了较详尽的论述。 关键词:线性规划数学模型电子表格模型规划求解Excel 线性规划是运筹学的一个分支,它的应用已愈来愈深入到社会生产和经济活动的各个领域。描述线性规划问题的抽象的数学式子是线性规划问题的数学模型。建立数学模型后,求解满足约束条件的目标函数的最优解是解决线性规划问题的关键。数学中常用的方法是图解法和单纯形法,而图解法只适用于两个变量的目标函数,单纯形法则计算量相当大,步骤烦琐,容易出错。在Excel中建立 电子表格模型,并利用它提供的“规划求解”工具,能轻松快捷地求解模型的解。 例如,某玻璃制品公司有三个工厂,公司目前决定停止不赢利产品的生产并撤出生产能力来生产两种新开发的产品:玻璃门和双把窗。估计三个工厂每周可用来生产新产品的时间分别为4小时、12小时、18小时,而每扇门需工厂1生产时间1个小时和工厂3生产时间3个小时,每扇窗需工厂2和工厂3生产时间各为2个小时,预测门的单位利润是300元,窗的单位利润是500元,问每周两种新产品数量的哪种组合能使总利润最大? 问题的决策变量有两个:每周门的生产数量和窗的生产数量,目标是总利润最大,需满足的条件是:⑴三个工厂每周用于生产新产品的时间w每周可得时间 ⑵每周门、窗的生产数量均》0。设每周门的生产数量为X,窗的生产数量为y,则该问题的数学模型即为:最大化利润P =300x+500y,约束条件:xw4, 2y< 12,3x+2yw 18,x>0和y》0。 将上表的有关数据输入到Excel中,建立如图1所示的电子表格模型。被输入已知数据的单元格是数据单元格,如单元格C5:D8,G5:G7。决策变量(即两种产品每周的生产量)放在单元格C9和D9,正好定位在这些产品所在列的 数据单元格下面,这种含有需要做出决策的单元格是可变单元格。单元格E5: E7是用来计算各个工厂每周的总生产时间,如单元格E5就是用C5:D5和C9: D9的对应数值各自相乘再总加得到。Excel中有一个叫SUMPRODUCT的函数 能对相等行数和相等列数的两个变化范围的单元格中的值乘积后进行加和。被加 和的每个值是对第一个变化范围的一些值和对应位置的第二个变化范围的一些值的积。女口 E5=SUMPRODUCT(C5 : D5,C9:D9)是把C5:D5变化范围的每个值与C9 : D9变化范围中对应的每个值相乘,然后各个积相加。同样 E6=SUMPRODUCT(C6 : D6, C9:D9),E7=SUMPRODUCT(C7 : D7, C9:D9), E5、E6、E7这些单元格的数值是依赖于可变单元格的,它们是输出单元格。单元格F5、 F6、F7中的“W”符号表示它们左边的总值不允许超过列G中的对应

用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 —

使用Excel规划求解解线性规划问题

使用Excel规划求解解线性规划问题 本文转自:https://www.360docs.net/doc/9311516177.html,/2009/12/11/linearwithsolver1/ 引言 最近,开始学习运筹学,期望通过学习后能够解决许多困扰自已的难题。 刚开始时,选了很多教材,最后以Hamdy A. Taha著的《Operations Research:An Introduction》开始学习。(该书已由人民邮电出版社出版,书名《运筹学导论-初级篇(第8版)》,不知为什么,下载链接中只有该书配套的部分习题解答,而书中所说的光盘文件找不到下载的地方,因为中译本没有配光盘,因此也就错过了许多示例文件。不知道哪位有配套光盘文件,可否共享???) 线性规划求解的基本知识 线性规划模型由3个基本部分组成: ?决策变量(variable) ?目标函数(objective) ?约束条件(constraint) 示例:营养配方问题 (问题)某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成,含有以下成份: 特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤维。该农场希望确定每天最小成本的饲料配制。 (解答过程) 因为饲料由玉米和大豆粉配制而成,所以模型的决策变量定义为: x1=每天混合饲料中玉米的重量(磅) x2=每天混合饲料中大豆粉的重量(磅) 目标函数是使配制这种饲料的每天总成本最小,因此表示为: min z=0.3×1+0.9×2 模型的约束条件是饲料的日需求量和对营养成份的需求量,具体表示为: x1+x2≥800 0.09×1+0.6×2≥0.3(x1+x2) 0.02×1+0.06×2≤0.05(x1+x2) 将上述不等式化简后,完整的模型为: min z=0.3×1+0.9×2 s.t. x1+x2≥800 0.21×1-0.3×2≤0 0.03×1-0.01×2≥0 x1,x2≥0 可以使用图解法确定最优解。下面,我们介绍使用Excel的规划求解加载项求解该模型。 使用Excel规划求解解线性规划问题 步骤1安装Excel规划求解加载项 单击“Office按钮——Excel选项——加载项——(Excel加载项)转到”,出现“加载宏”对话框,如下图所示。选择“规划求解加载项”,单击“确定”。

用excel规划求解并作灵敏度分析报告

题目 如何利用EXC E L求解线性规划问题及其灵敏度分析 第 8 组 学号 乐俊松 090960125 然 090960122 徐正超 090960121 凯 090960120 王炜垚 090960118 蔡淼 090960117 航空航天大学(贸易经济)系 2011年(5)月(3)日

摘要 线性规划是运筹学的重要组成部分,在工业、军事、经济计划等领域有着广泛的应用,但其手工求解方法的计算步骤繁琐复杂。本文以实际生产计划投资组合最优化问题为例详细介绍了Excel软件的”规划求解”和“solvertable”功能辅助求解线性规划模型的具体步骤,并对其进行了灵敏度分析。

目录 引言 (4) 软件的使用步骤 (4) 结果分析 (9) 结论与展望 (10) 参考文献 (11) 1. 引言

对于整个运筹学来说,线性规划(Linear Programming)是形成最早、最成熟的一个分支,是优化理论最基础的部分,也是运筹学最核心的容之一。它是应用分析、量化的方法,在一定的约束条件下,对管理系统中的有限资源进行统筹规划,为决策者提供最优方案,以便产生最大的经济和社会效益。因此,将线性规划方法用于企业的产、销、研等过程成为了现代科学管理的重要手段之一。[1] Excel中的线性规划求解和solvertable功能并不作为命令直接显示在菜单中,因此,使用前需首先加载该模块。具体操作过程为:在Excel的菜单栏中选择“工具/加载宏”,然后在弹出的对话框中选择“规划求解”和“solvertable”,并用鼠标左键单击“确定”。加载成功后,在菜单栏中选择“工具/规划求解”,便会弹出“规划求解参数”对话框。在开始求解之前,需先在对话框中设置好各种参数,包括目标单元格、问题类型(求最大值还是最小值)、可变单元格以及约束条件等。 2 软件的使用步骤 “规划求解”可以解决数学、财务、金融、经济、统计等诸多实 际问题,在此我们只举一个简单的应用实例,说明其具体的操作 方法。 某人有一笔资金可用于长期投资,可供选择的投资机会包括购买国库券、公司债券、投资房地产、购买股票或银行保值储蓄等。投资者希望投资组合的平均年限不超过5年,平均的期望收益率不低于13%,风险系数不超过4,收益的增长潜力不低于10%。问在满足上述要求的前提下投资者该如何选择投资组合使平均年收益率最高?(不同的投资方式的具体参数如下表。)

利用Excel线性规划设计饲料配方模板

利用Excel线性规划设计饲料配方 线性规划, Excel, 配方, 饲料, 设计 利用Excel线性规划设计饲料配方 邓君明(中国海洋大学水产学院) 曾广厅(云南农业大学云南省动物营养与饲料重点实验室) 摘要:借助Excel 2000“线性规划求解”功能,结合饲料配方设计要求,以线性规划求解建立数学模型,根据动物的饲养标准,能快速、简单地求解饲料配方,在最低成本目标下实现对原料和营养成分的有效调控。 关键词:Excel;线性规划;饲料配方 所谓饲料配方设计,就是应用一定的计算方法,根据原料的营养成分和配方的规格、要求,产生配方中各原料比例的一种运算过程。线性规划法是目前应用最广泛的一种优化饲料配方技术。线性规划最低成本配方的优化结果是产生一个满足约束条件的最低成本配方,它受原料的营养成分、约束条件值(配方营养素水平)、原料价格等的影响。目前,大型专业饲料公司己使用计算机和专用的饲料配方软件进行配方。但专用的饲料配方软件价格不菲,因此,在小型饲料厂和一般养殖场的应用并不普及。现在有可能只要拥有安装了Excel 2000的电脑就可以通过计算机运用“线性规划求解”求解饲料配方。利用它的行和列的形式组成区域,在区域内记录数据信息,组成数据库,且可对数据进行统计和分析,获取有效信息。同时,运用线性规划实现日粮配方,方法简单、快捷、准确、方便。本文就利用Excel 2000线性规划设计饲料配方作一阐述。 1. 饲料配方设计要求 1.1优化配方设计目标 动物生产的经济效益主要取决于饲料。饲料品质的好坏,成本的高低直接影响动物生产的经济效益。而饲料配方是配合饲料生产的核心,要优化配方设计,必须同时解决以下三个问题:⑴营养需要问题,由营养学家研究修改制定,满足营养标准就是线性规划求解的主要约束条件之一,即营养性限制;⑵合理组合原料,不同原料的合理搭配,才能满足动物的营养需要,是约束条件之二,即原料性限制;⑶价格最低,在符合条件⑴、⑵的基础上,采用成本最低的原料配比就是求解的目标,即最低成本目标函数。

使用Excel求解线性规划问题

使用Excel 求解线性规划问题 利用单纯形法手工计算线性规划问题是很麻烦的。office 软件是一目前常用的软件,我们可以利用office 软件中的Excel 工作表来求解本书中的所有线性规划问题。对于大型线性规划问题,需要应用专业软件,如Matlab ,Lindo ,lingo 等,这些软件的使用这里我们不作介绍,有需要的,自己阅读有关文献资料。 用Excel 工作表求解线性规划问题,我们需要先设计一个工作表,将线性规划问题中的有关数据填入该工作表中。所需的工作表可按下列步骤操作: 步骤 1 确定目标函数系数存放单元格,并在这些单元格中输入目标函数系数。 步骤 2 确定决策变量存放单元格,并任意输入一组数据。 步骤 3 确定约束条件中左端项系数存放单元格,并输入约束条件左端项系数。 步骤 4 在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式,计算出约束条件左端项对应于目前决策变量的函数值。 步骤 5 在步骤4的数据右边输入约束条件中右端项(即常数项)。 步骤 6 确定目标函数值存放单元格,并在该单元格中输入目标函数值的计算公式。 例 建立如下线性规划问题的Excell 工作表: 12 12121212m ax 150******** 34120 ..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步,直到添加完所有条件

用Excel规划求解解线性规划问题

用Excel规划求解解线性规划问题 本文转自:https://www.360docs.net/doc/9311516177.html,/2009/12/11/linearwithsolver1/ 引言 最近,开始学习运筹学,期望通过学习后能够解决许多困扰自已的难题。 刚开始时,选了很多教材,最后以Hamdy A. Taha著的《Operations Research:An Introduction》开始学习。(该书已由人民邮电出版社出版,书名《运筹学导论-初级篇(第8版)》,不知为什么,下载链接中只有该书配套的部分习题解答,而书中所说的光盘文件找不到下载的地方,因为中译本没有配光盘,因此也就错过了许多示例文件。不知道哪位有配套光盘文件,可否共享???) 线性规划求解的基本知识 线性规划模型由3个基本部分组成: ?决策变量(variable) ?目标函数(objective) ?约束条件(constraint) 示例:营养配方问题 (问题)某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成,含有以下成份: 特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤维。该农场希望确定每天最小成本的饲料配制。 (解答过程) 因为饲料由玉米和大豆粉配制而成,所以模型的决策变量定义为: x1=每天混合饲料中玉米的重量(磅) x2=每天混合饲料中大豆粉的重量(磅) 目标函数是使配制这种饲料的每天总成本最小,因此表示为: min z=0.3×1+0.9×2 模型的约束条件是饲料的日需求量和对营养成份的需求量,具体表示为: x1+x2≥800 0.09×1+0.6×2≥0.3(x1+x2) 0.02×1+0.06×2≤0.05(x1+x2) 将上述不等式化简后,完整的模型为: min z=0.3×1+0.9×2 s.t. x1+x2≥800 0.21×1-0.3×2≤0 0.03×1-0.01×2≥0 x1,x2≥0

使用Excel求解线性规划问习题

欢迎阅读 页脚内容 使用Excel 求解线性规划问题 利用单纯形法手工计算线性规划问题是很麻烦的。office 软件是一目前常用的软件,我们可以利用office 软件中的Excel 工作表来求解本书中的所有线性规划问题。对于大型线性规划问题,需要应用专业软件,如Matlab ,Lindo ,lingo 等,这些软件的使用这里我们不作介绍,有需要的,自己阅读有关文献资料。 用Excel 工作表求解线性规划问题,我们需要先设计一个工作表,将线性规划问题中的有关数据填入该工作表中。所需的工作表可按下列步骤操作: 步骤 1 确定目标函数系数存放单元格,并在这些单元格中输入目标函数系数。 步骤 2 确定决策变量存放单元格,并任意输入一组数据。 步骤步骤步骤步骤例 其中: 步骤1 步骤2 [等于]步骤步骤4 步骤5 步骤6 步骤7 步骤8 框。并步骤9 步骤10 若结果满足要求,单击[确定]按钮,完成操作;若结果不符要求,单击[取消]按钮,在工作表中修改单元格初值后重新运行规划求解过程。 例 利用Excell 工作表求解线性规划问题 12 121 21212max 150******** 34120..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. 软件 1222

学生用-实验指导书-excel线性规划实验

实验指导书 《管理决策模型与方法》 学院(部)管理学院 指导教师金玉兰

实验1 EXCEL 线性规划实验 一、实验目的 1、掌握应用Excel软件求解线性规划问题; 2、掌握应用Excel软件对线性规划问题进行灵敏度分析; 3、掌握应用Excel软件求解整数规划问题; 4、掌握应用Excel软件求解0-1整数规划问题。 二、实验设备、仪器及所需材料 配置在Pentium Ⅲ,内存128M以上的电脑;装有Microsoft Windows操作系统及Microsoft Office 2003工作软件。 三、实验原理 “规划求解”是Microsoft Excel 中的一个加载宏,借助它可以求解许多运筹学中的数学规划问题。 安装Office 2003 的时候,系统默认的安装方式不会安装该宏程序,需要用户自己选择安装。安装方法为:从Excel 菜单中选择“工具”→“加载宏”,打开如下对话框: 选择其中的“规划求解”后单击“确定”按钮,会出现提示:“这项功能目前尚未安装,是否现在安装?”,选择“是”,系统要你插入Office 的安装光盘,准备好后单击确定,很快就会安装完毕。于是,你会发现在“工具”菜单下多出一个名为“规划求解”的子菜单,说明“规划求解”功能已经成功安装。 在EXCEl2007版本中,通过点击“office按钮”,“EXCEL选项”→“加载项”→转到“EXCEL

加载项”,然后加载【规划求解加载项】便可以加载规划求解的宏。 在EXCEl2010版本中,通过点击“文件”选项卡打开“Excel选项”对话框,单击左侧 “加载项”标签,在右侧单击“转到”按钮,打开“加载宏”对话框,勾选“规划求解加载项”复选框,单击“确定”按钮,即可在工具栏的“数据”选项卡中出现 “分析”选项组,上面就有了“规划求解”按钮。 利用“规划求解”功能,就可以进行线性规划问题的求解。 例如:用EXCEL 求解数学规划问题 12121 212maxZ 2328416..4120, 0 x x x x x s t x x x =++≤??≤??≤??≥≥? 步骤: 1. 将模型中的目标函数和约束条件的系数输入到单元格中;为了使我们在操作过程中看得 更清楚,可以附带输入相应的标识符,并给表格加上边框。如下图所示:

Excel求解线性规划问题实验教程.

数学与信息科学学院 Excel求解线性规划问题实 验教程 二零一三零八月

目录 1.关于“规划求解” (1) 2.如何加载“规划求解” (2) 3.“规划求解”各参数解释和设置 (3) 4.“规划求解”的步骤 (6) 5.Excel求解线性规划问题 (8) 6.Excel求解运输问题 (14) 7.Excel求解目标规划问题 (18) 8.Excel求解整数规划问题 (22)

1.关于“规划求解” “规划求解”是Excel中的一个加载宏,借助“规划求解”,可求得工作表上某个单元格(被称为目标单元格)中公式(公式:单元格中的一系列值、单元格引用、名称或运算符的组合,可生成新的值。公式总是以等号(=)开始)的最优值。“规划求解”将对直接或间接目标单元格中公式相关联的一组单元格中的数值进行调整,最终在目标单元格公式中求得期望的结果。“规划求解”通过调整所指定的可更改的单元格(可变单元格)中的值,从目标单元格公式中求得所需的结果。在创建模型过程中,可以对“规划求解”中的可变单元格数值应用约束条件(约束条件:“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其它与目标单元格直接或间接相关的单元格。而且约束条件可以引用其它影响目标单元格公式的单元格。使用“规划求解”可通过更改其它单元格来确定某个单元格的最大值或最小值。) Microsoft Excel的“规划求解”工具取自德克萨斯大学奥斯汀分校的Leon Lasdon 和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码。线性和整数规划问题取自Frontline Systems公司的John Watson 和Dan Fylstra提供的有界变量单纯形法和分支边界法。

相关文档
最新文档