使用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.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规划求解工具进行优化1.线性规划—生产规划： 步骤一：建立模型：每天生产甲乙两种产品分别为X1和X2，数学模型为：目标函数：minf（X1，X2）=60*X1+120*X2 约束条件：9*X1+4*X2<=360 3*X1+4*X2<=300 4*X1+5*X2<=200 -X1<=0 -X2<=0 用EXCEL建立模型如下： 步骤二：规划求解参数确定： 步骤三：选项参数确定：
步骤四：求解： 由上面求解过程可知：X1=20，X2=24时，可使目标函数值最小，即f（X1，X2）=4080. 2.工程下料问题规划求解： 由题意可列出下列方案： 步骤一：设使用8种方案的次数分别为X1，X2，X3，X4，X5，X6，X7和X8，且均为正整数，建立数学模型如下： 目标函数：f（X）=（5*X1+10*X2+25*X3+5*X4+30*X5+10*X6+25*X7+5*X8）/（（X1+X2+X3+X4+X5+X6+X7+X8）*180） 约束条件：gX1=2*X1+X2+X3+X4=100 gX1=2*X2+X3 +3*X5+2*X6+X7 gX1=X1+X3+33*X4 +2*X6+3*X7+5*X8 用EXCEL建立模型如下：
步骤二：规划求解参数确定： 步骤三：选项参数确定： 步骤四：求解： 由上面求解过程可知：X1=23，X2=50，X3=0，X4=4，X5=0，X6=0，X7=0和X8=3时，可使目标函数值最小，即f（X）=0.045139. 3．规划求解—工时安排： 某厂生产A B C三种产品，净利润分别为90元，75元，50元；使用的机时数分别为3h，手工时数分别为4h，3h，2h，由于数量和品种受到制约，机工最多为400h，手工为280h，数量最多不能超过50件，C至少要生产32件。求：如何安排A B C的数量以获得最大利润？
使用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规划求解
□财会月刊· 全国优秀经济期刊□·110·2014.8下 在传统财务运营管理中，营运决策包括确定最佳现金持有量、最优订货批量，或者只是考虑单个市场的生产与销售决策。企业集团全球运营管理涉及生产、运输、销售等环节，需要在实现集团利润最大化的同时，解决生产什么产品、在哪里生产、生产多少、运到哪个市场等诸多问题。显然，采用传统的运营管理方法会比较棘手。而Ex?cel 提供的规划求解工具，不但能非常迅速地求出多种营运决策模型的最优解，还可以给出敏感性分析报告，满足财务全球化运营管理的需求，有效提高公司决策效率，同时也能促进财务人员更多地参与到公司管理决策中。 一、问题描述 某跨国集团在中国和其他地区设立了四个工厂，分别为A 、B 、C 、D 厂，产品主要面向国际市场销售，分别销往北京、香港、纽约、东京四个城市。各个工厂的单位产品成本、固定成本、产能，各个市场的销售价格和需求量，以及各个工厂到每个市场的运输成本见图1。 在每个工厂产能允许同时最大限度满足市场需求的情况下，集团管理层希望财务部给出能够实现集团利润 最大化目标的年生产和运输预算的决策方案。 二、建立线性数学模型 1.定义决策变量。下文中，i （i=1，2，3，4）表示工厂，j 表示市场（j=1，2，3，4）；决策问题可以用图2表示。所以定义决策变量为X ij ：即在i 工厂生产的产品投放到j 市场。 2.确定目标函数。最大利润=收入-产品变动成本-其他成本最大利润=55500（X 11+X 21+X 31+X 41）+61100（X 12+X 22+X 32+X 42）+57800（X 13+X 23+X 33+X 43）+62650（X 14+X 24+X 34+X 44）-34900（X 11+X 12+X 13+X 14）-32200（X 21+X 22+X 23+X 24）-38350（X 31+X 32+X 33+X 34）-23400（X 41+X 42+X 43+X 44）-（500X 11+12225X 12+9075X 13+21450X 14+4500X 21+……+15150X 43+5925X 44）。 3.列出约束条件。 （1）产能约束：X 11+X 12+X 13+X 14≤101；X 21+X 22+X 23+X 24≤201；X 31+X 32+X 33+X 34≤121；X 41+X 42+X 43+X 44≤250。 （2）需求约束：X 11+X 21+X 31+X 41≤150；X 12+X 22+X 32+X 42≤75；X 13+X 23+X 33+X 43≤200；X 14+X 24+X 34+X 44≤100。 （3）非负约束：X ij ≥0。4.最优解：最大利润时的X ij 。 三、数据及公式准备 1.数据输入：把图1集团公司的决策数据输入新建的Excel 表中，如图3所示。 耿海利 （江西财经大学会计学院南昌330013） 【摘要】随着全球经济一体化的深入，企业运营管理方式发生了很大变化。本文通过一个实例，来探讨企业集团拥有多个生产子公司、多个产品市场并且各个产品市场价格不同的情况下，企业如何使用Excel 规划求解工具进行产品生产、运输和分配决策，以实现集团利润最大化。 【关键词】规划求解 企业集团全球运营决策敏感性分析 Excel 规划求解： 企业全球运营管理工具 图1 集团基本运营决策数据 图2决策问题
EXCEL规划求解题
1、生产问题 某工厂生产I，II两种食品，现有80名熟练工人，己知一名熟练工人每小时可生产10千克食品I或8千克食品II。据合同预订，该两种食品每周的需求量将急剧上升，见下表。为此该厂决定到第8周末需培训出60名新的工人，两班生产。已知一名工人每周工作40小时，一名熟练工人用两周时间可培训出不多于三名新工人(培训期间熟练工人和培训人员均不参加生产)。熟练工人每周工资320元，新工人培训期间工资每周180元，培训结束参加工作后工资每周260元，生产效率同熟练工人。在培训的过渡期间，很多熟练工人愿加班工作，工厂决定安排部分工人每周工作80小时，工资每周480元。又若预订的食品不能按期交货，每推迟交货一周的赔偿费食品I为0.4元，食品II 为0.8元。在上述各种条件下，试建立该问题的线性规划模型，以便作出合理全面的安排，使各项费用的总和为最小。 建立该问题的电子表格模型，填写下列电子表格。
2、项目选择问题 某个制药公司需要开发四个新的研究项目，为了使所有项目的成功性最高，派了六位科学家来对这四个项目进行投标选择。每位科学家具有1000点可以投标，投标点数越大，表示科学家对该项目越感兴趣，成功的可能性就越高。投标具体情况如下表所 没有该领域的知识或其他原因而不能从事该项目的研究与开发。目标是使投标总点数最高，应该如何指派。建立该问题的电子表格模型，填写下列电子表格。
某物流公司希望以最小的成本完成一种物资的配送，其运出货物数量、分配量和各段线路单位运输成本如下表所示。另外，由于运输能力限制，从各个工厂到配送中心，以及由配送中心到各个仓库运输产品的数量均不超过60。 建立该问题的电子表格模型，填写下列电子表格。
EXCEL规划求解功能操作说明
E X C E L规划求解功能操 作说明 This model paper was revised by the Standardization Office on December 10, 2020
Excel规划求解功能操作说明以Microsoft Excel2003为例，说明使用Excel的求解线性规划问题功能的使用方法。 一、加载规划求解功能 1.点击【工具】按钮，在下拉菜单中选择【加载宏】功能。 2.在弹出的【可加载宏】选项卡中勾选【规划求解】，点击确定按钮。 此时，【工具】下拉菜单中增加规划求解功能，表示加载成功。 二、构造表格Excel表格并填入各项数据 以教材18页【例题2-8】为例，构造表格如下： 1.录入约束条件系数 约束条件（1）为5x 1+x 2 -x 3 +x 4 =3，则在约束系数的第一行的x 1 ,x 2 ,x 3 ,x 4 ,x 5 ，限制条 件，常数b列下分别录入5,1，-1,1,0，=，3如下图所示。 约束系数区的第二行录入约束条件（2）的系数、限制符号及常数b，即- 10,6,2,0,1，=，2； 约束系数区的第三行录入约束条件（3）（x1≥0）的系数、限制符号及常数b，即1,0,0,0,0，≥，0； 约束系数区的第四行录入约束条件（4）（x2≥0）的系数、限制符号及常数b，即0,1,0,0,0，≥，0；
约束系数区的第五行录入约束条件（5）（x3≥0）的系数、限制符号及常数b，即0,0,1,0,0，≥，0； 约束系数区的第六行录入约束条件（6）（x4≥0）的系数、限制符号及常数b，即0,0,0,1,0，≥，0； 约束系数区的第七行录入约束条件（7）（x5≥0）的系数、限制符号及常数b，即0,0,0,0,1，≥，0。如下图所示。 2.录入目标函数系数 目标函数为maxZ=4x 1-2x 2 -x 3 ，则在目标函数的x 1 ,x 2 ,x 3 ,x 4 ,x 5 列下分别录入4，-2，- 1,0,0，如下图所示。 3. 录入约束条件的计算公式 双击约束条件（1）行的“总和”单元格，录入以下内容： “=B3*B12+C3*C12+D3*D12+E3*E12+F3*F12” 说明：录入的内容即是约束条件（1）的计算公式，其中“B3*B12”代表5x 1 ; “C3*C12”代表1x 2；“D3*D12”代表-1x 3 ；“E3*E12”代表1x 4 ；“F3*F12”代表0x 5 。 整个计算公式即代表5x 1+1x 2 -1x 3 +1x 4 +0x 5 ，即约束条件（1）的计算公式。注意：单元格 B12，C12，D12，E12，F12分别代表x 1，x 2 ，x 3 ，x 4 ，x 5
利用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规划求解功能操作说明 以Microsoft Excel2003为例，说明使用Excel的求解线性规划问题功能的使用方法。 一、加载规划求解功能 1.点击【工具】按钮，在下拉菜单中选择【加载宏】功能。 2.在弹出的【可加载宏】选项卡中勾选【规划求解】，点击确定按钮。
此时，【工具】下拉菜单中增加规划求解功能，表示加载成功。 二、构造表格Excel表格并填入各项数据
以教材18页【例题2-8】为例，构造表格如下： 标题栏 约束条件区 目标函数区 计算结果显示区 1.录入约束条件系数 约束条件（1）为5x1+x2-x3+x4=3，则在约束系数的第一行的x1,x2,x3,x4,x5， 限制条件，常数b列下分别录入5,1，-1,1,0，=，3如下图所示。 约束系数区的第二行录入约束条件（2）的系数、限制符号及常数b，即 -10,6,2,0,1，=，2； 约束系数区的第三行录入约束条件（3）（x1≥0）的系数、限制符号及常数
b，即1,0,0,0,0，≥，0； 约束系数区的第四行录入约束条件（4）（x2≥0）的系数、限制符号及常数b，即0,1,0,0,0，≥，0； 约束系数区的第五行录入约束条件（5）（x3≥0）的系数、限制符号及常数b，即0,0,1,0,0，≥，0； 约束系数区的第六行录入约束条件（6）（x4≥0）的系数、限制符号及常数b，即0,0,0,1,0，≥，0； 约束系数区的第七行录入约束条件（7）（x5≥0）的系数、限制符号及常数b，即0,0,0,0,1，≥，0。如下图所示。 2.录入目标函数系数 目标函数为maxZ=4x1-2x2-x3，则在目标函数的x1,x2,x3,x4,x5列下分别录入4，-2，-1,0,0，如下图所示。
用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规划求解工具在多目标规划中的应用 摘要：多目标决策方法是从20世纪70年代中期发展起来的一种决策分析方法。该方法已广泛应用于人口、环境、教育、能源、交通、经济管理等多个领域。文章采用多目标决策方法中分层序列法的思想，应用excel的规划求解工具，对多目标规划问题进行应用研究，并以实例加以说明。 abstract： multi-objective decision method is a kind of decision analysis method from the mid 1970s. the method has been widely used in population， environment， education，energy， traffic， economic management， and other fields. this paper uses the lexicographic method of multi-objective decision method and makes some researches on the multi-objective problem using the excel solver tool and an example to illustrate. 关键词： excel规划求解；多目标规划；分层序列法 key words： excel solver；multi-objective programming；the lexicographic method 中图分类号：tp31 文献标识码：a 文章编号：1006-4311（2013）21-0204-02 0 引言 excel中的规划求解工具只能对单目标的问题进行求解。当遇到多目标问题时，可以把多目标问题先转化为单目标问题，然后求解。
实验五：运用Excel规划求解进行最优投资组合地求解
实验报告 证券投资 学院名称 专业班级 提交日期 评阅人____________ 评阅分数____________
实验五：运用Excel规划求解进行最优投资组合的求解 【实验目的】 1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序； 2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合； 【实验条件】 1、个人计算机一台，预装Windows操作系统和浏览器； 2、计算机通过局域网形式接入互联网； 3、matlab或者Excel软件。 【知识准备】 理论知识：课本第三章收益与风险，第四章投资组合模型，第五章CAPM 实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章，第四章，第五章 【实验项目容】 请打开参考《金融建模—使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验 A．打开“实验五组合优化.xls”，翻到“用规划求解计算最优组合”子数据表； B．调用规划求解功能进行求解。 点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才能使用，如何加载见实验补充文档“EXCEL规划求解功能的安装”。 C．
D．在规划求解选项卡里面选择“选项”，再选择“非负”再运行一次，比较两次返回的投资比例值的正负。在实验报告中记录两次得到的最优投资组合，并说明投资比例是负值说明什么？ E．（选做）借助连续调用规划求解的VBA过程生成有效组合以及资本市场线。 参考实验参考电子书《金融建模—使用EXCEL和VBA》电子书第四章P83 F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？ 【实验项目步骤与结果】 A.
《运筹学》使用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 就可以给出求解结果。对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab 了。不过，大多数规划问题Mathcad 即可完成所赋予的任务。利用Excel 求解规划问题有些“罗嗦”，但也不难掌握。下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。 【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。有关生产情况的各种数据资料见下表。 品种 工效（h ） 储藏量（m 3） 定点量(件)* 收益（元） 果汁杯 6 h/百件 10 m 3/百件 600件 600元/百件 鸡尾酒杯 5 h/百件 20 m 3/百件 0件 400元/百件 *注：定点量为每周生产的最大数量。 若每周工作不超过50小时，且拥有储藏量为140m3的仓库。问： ⑴ 该个体户如何安排工作时间才能使得每周的收益最大？ ⑵ 若每周多干1小时，收益增大多少？ ⑶ 通过加班加点达到的收益极限是多少？ 解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下： 21400600)(Max x x x f += ???????≥≥≤≤+≤+0 ,06 14020105056 s.t.2112121x x x x x x x 显然，约束条件中的第三个式子x 1≤6可以表作1*x 1+0*x 2≤6，从而有如下矩阵 ??????=400600c ，??????=21x x x ，??????????=01201056A ，???? ??????=614050b 容易看到，上述模型表为矩阵形式便是： 目标函数为 []?? ????==21400600)(Max x x x c x f T 约束条件为 ???? ?????≥??????=??????????=≤??????????=061405001201056 s.t.21x x x b Ax
如何在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规划求解并作灵敏度分析
题目 如何利用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%。问在满足上述要求的前提下投资者该如何选择投资组合使平均年收益率最高？（不同的投资方式的具体参数如下表。）