系统工程结课论文----线性规划问题的Excel建模及求解

合集下载

用EXCEL求解线性规划

用EXCEL求解线性规划
要用EXCEL求解线性规划问题，需要遵循以下步骤：
1. 给定问题中的约束条件和目标函数。

2. 打开EXCEL，建立一个新的工作表。

3. 在工作表中输入问题的约束条件和目标函数。

在输入目标函数时，需要将所有项移动到等号左侧，使它成为一个线性方程。

需要注意将不等式约束条件转化为等式约束条件，可以通过添加松弛变量来实现。

4. 使用EXCEL的“规划”工具，在工具栏中点击“数据”-“分
析”-“规划器”，打开“规划器”。

5. 在“规划器”中，选择需要优化的目标单元格，在“约束条件”
中输入所有约束条件所在的单元格，设置变量单元格的范围。

6. 可以在“选项”中添加其他约束条件。

例如，可以设定变量的整数或二元特性等。

7. 单击“求解”按钮，EXCEL将自动求解最优解，并输出最优
值和变量值。

需要注意的是，线性规划问题求解的结果是一个数值，而不是图形。

因此，需要谨慎分析问题以确保从数值结果中得到了正确的结论。

实验1用Excel求解线性规划模型

实验一、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。

Excel最多可解200个变量、600个约束条件的问题。

下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。

一、实验目的1、掌握如何建立线性规划模型。

2、掌握用Excel求解线性规划模型的方法。

3、掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。

4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。

二、实验内容1、[工具][规划求解]命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。

在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。

使用[规划求解]命令的一般步骤为：第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。

图1“规划求解参数”对话框选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。

如利润问题，选取“最大值”可变单元格决策变量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。

图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。

图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。

excel线性规划求解

excel线性规划求解Excel是一种功能强大的电子表格软件，除了可以进行基本的计算和数据分析外，还可以用于求解线性规划问题。

线性规划是一种数学优化方法，通过最大化或最小化线性目标函数，同时满足一系列线性约束条件，从而寻找最优解。

在Excel中，我们可以使用内置的线性规划求解器来解决这类问题。

下面将介绍如何使用Excel进行线性规划求解。

首先，我们需要将线性规划问题转化为Excel的表格形式。

假设我们有一个线性目标函数和一系列线性约束条件，我们可以将变量和常数分别放置在表格的不同单元格中。

然后，我们可以在Excel中的“数据”选项卡中找到“线性规划求解器”。

单击“线性规划求解器”并选择“确定”后，我们将进入求解器对话框。

在求解器对话框中，我们需要设置求解的目标、变量和约束条件。

首先，我们需要选择是求取最大值还是最小值。

然后，我们需要指定目标函数和约束条件中的变量单元格范围。

接下来，我们可以指定变量的约束条件。

例如，我们可以将某个变量约束为非负数，或者指定它的取值范围。

最后，我们可以选择求解方法和优化选项。

一般来说，我们可以选择线性规划求解器自动选择最佳求解方法。

如果需要更精确的结果，我们可以选择增加迭代次数和精度。

完成设置后，单击“确定”按钮，Excel将自动计算并求解线性规划问题。

求解结果将显示在工作表中，并且还可以显示最优解的目标函数值和各个变量的取值。

使用Excel进行线性规划求解的优点是，它提供了一个直观和易于使用的界面，并且能够快速计算出结果。

然而，它也有一些局限性，例如只能处理线性约束条件和目标函数、求解的精度有限等。

总的来说，使用Excel进行线性规划求解是一种简单而方便的方法。

通过将问题转化为Excel的表格形式，并使用内置的线性规划求解器，我们可以快速求解线性规划问题，并获得最优解。

以上为关于Excel线性规划求解的简要介绍，希望能帮助你了解如何在Excel中进行线性规划求解。

怎么利用EXCEL求解线性规划

利用线性回归方法求解生产计划方法一：1、建立数学模型:①设变量：设生产拉盖式书桌x台，普通式书桌y台，可得最大利润②确定目标函数及约束条件目标函数：y=max+115P90x约束条件：200x .....................⑴+y10≤20x .....................⑵4≤+y16128x .....................⑶+y1015≤220yx ..........................⑷,≥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）：2002010≤+y x ，左端“单元格所引用位置”选择F3，右端“约束值”选择D3，符号类型选择“<=”，同理继续添加约束（2）（3）（4），完成后选择“确定”，回到“规划求解参数”对话框，如5图所示：图5④点击“选项”按钮，弹出“规划求解选项”对话框，选择“采用线性模型”和“假定非负”两项，如图6所示：图6⑤点击“确定”→“求解”，选择“运算结果报告”“敏感性报告”“极限值报告”三项，最后点击“确定”，输出结果：运算结果报告：敏感性报告：极限报告：方法二：1、建立数学模型设生产拉盖式书桌x 台，普通式书桌y 台，总利润为Z 元确定目标函数及约束条件目标函数：y x Z 90115max += 约束条件：⎪⎪⎩⎪⎪⎨⎧≥≤+≤+≤+0,22010151281642002010..y x y x y x y x t s 2、在Excel 中规划求解在Excel 中建立线性规划模型，如图1所示：图11)在E2中输入“=B2*B6+C2*C6”如图2所示，同理 E3=B3*B6+C3*C6E4=B4*B6+C4*C6B7=B5*B6+C5*C6图22)单击“工具”菜单下的“规划求解”，在弹出的“规划求解参数”对话框输入各项参数：✓目标单元格选择B7✓问题类型选择“最大值”✓可变单元选择B6:C6✓约束条件选择B6:C6≥0；E2:E4≤D2:D4参数设置完毕，如图3：图33）点击“选项”，弹出“规划求解选项”对话框，选择“采用线性模型”、“假定非负”和“显示迭代结果”，说明要求求解的问题是线性模型且所求的变量必须为非负，如图4所示：图44）点击“确定”→“求解”，选择“运算结果报告”“敏感性报告”“极限值报告”三项，最后点击“确定”，输出结果：运算结果报告：敏感性报告：极限值报告：。

Excel规划求解操作指南线性规划问题的建模与求解

Excel规划求解操作指南(一)——线性规划问题的建模与求解内容摘要：《Excel规划求解操作指南》旨在比较通俗地来说明规划求解的步骤和怎么利用它来解决问题，便于大家自学或查询。

本文主要介绍Excel规划求解的预备知识、线性规划问题的建模初步方法和利用Excel求解线性规划的步骤。

关键词：Excel 线性规划建模求解第二次世界大战以来，运筹学成功地解决了许多经济管理问题，作为一门现代科学得到了广泛应用，规划论是运筹学的最重要的分支。

计算机的应用为运筹学的发展提供了强大的支持，利用Excel可以解决通常情况下的规划求解问题。

但是，使用过Excel的朋友，很多可能都不了解什么是规划求解，而知道有此功能的朋友，也很少有利用此功能来完成实际问题，或者学习时懂了，学过就忘了。

《Excel规划求解操作指南》试图比较通俗地来说明规划求解的步骤和怎么利用它来解决问题，便于大家自学或查询。

本文是该操作指南的一部分，其他部分将陆续向大家介绍。

一、预备知识1、规划求解程序安装在OFFICE的重要组件EXCEL中，有一个规划求解的加载宏。

加载该宏之后，就可以利用EXCEl的规划求解功能进行规划求解。

在EXCEL2003版本中，通过点击菜单【工具】——【加载宏】，在加载宏对话框中选择【规划求解】项，便可以加载该宏。

如果计算机提示无法安装，那么需要插入OFFICE光盘，再进行安装。

2、规划求解的常用函数（1）SUM函数利用SUM函数，可以自动求出所选中的单元格数据的和。

首先，选中输出结果的单元格，输入“=”，在下拉菜单中选择“SUM”，得到函数参数对话框然后，将光标放在Number框中，选中需要求和的单元格，确定。

在菜单栏中会出现输入内容以供检查。

例如，其中“=SUM(E2:G3)”表示对如下单元格的数据求和：（2）SUMPRODUCT函数，利用SUM函数，可以自动求出一组数据与另一组数据对应元素乘积的和。

例如，总利润等于各种产品的单位利润与产量乘积之和；总成本等于各种产品的单位成本与产量乘积之和。

EXCEL求解线性规划问题

约束右端值降低15时，目旳函数值旳变化量。
解：（1）最优解为x1=0, x2=12.4, x3=9.5
（2） x1旳目旳系数降低5，占允许降低旳百分比=5/∞=0%，x2 旳目旳系数增长4,占允许增长旳百分比=4/7.8=51.2%。
变化旳百分比和为51.2%，没有超出100%，所以最优解不变。
（3）第一资源约束右端值增长30，占允许增长旳30 /∞=0%，第二资源约束右端值增长4 ，占允许增长旳4/15=26.7%，
•初值和终值分别指单元格在此次求解前旳数值和求解后旳数值。
敏感性分析报告（1）
可变单元格中 • “单元格”指决策变量所在单元格旳地址 • “名字”是决策变量旳名称 • “终值”是决策变量旳终值,即最优值 • “递减成本”指最优解中档于0旳变量，相应旳目旳函数中旳系数
增长或降低多少，最优解不再为0 • “目旳式系数”目旳函数中旳系数，为已知条件 • “允许旳增量”与“允许旳减量”表达目旳函数中旳系数在增量
（1）引用旳类型
三种类型：
相对引用、绝对引用、混合引用
（2）相对引用
格式： A3 、B6
使用相对引用后，系统将会记住建立公式旳单元格和被引用旳单元格旳相对位置，在复制这个公式时，新旳公式单元和被引用旳单元依然保持这种相对位置。
（3）绝对引用格式：$a$3 $d$5
绝对引用是指被引用旳单元与引用旳公式单元旳位置关系是绝正确，不论将这个公式复制到任何单元，公式所引用旳还是原来单元格旳数据。
2) 在弹出旳对话框中旳“可用加载宏”列表框中,选定待添加旳加载宏“规划求解”选项旁旳复选框,然后单击“拟定”.单击“拟定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置

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提供的有界变量单纯形法和分支边界法。

2.如何加载“规划求解”安装office的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。

下面是加载“规划求解”宏的步骤：（1）在“工具”菜单上，单击“加载宏”。

利用EXCEL进行线性规划

解：依题意，设置四种家具的日产量分别为决策变量
x1, x2 , x3, x4 ，目标要求是日利润最大化，
约束条件为三种资源的供应量限制和产品销售量限制。据此，列出下面的线性规划模型：
MaxZ 60x1 20x2 40x3 30x4
4x1 2x2 x3 2x4 600
x1, x2 , x3 , x4 0 （非负约束）
其中 x1, x2 , x3, x4 分别为四种家具的日产量。
下面介绍用Excel中的“规划求解”功能求此题。第一步在Excel中描述问题、建立模型，如下图所示。
=SUMPRODUCT(B6:E6,$B$15:$E$15)
第二步在“工具”菜单中选择“规划求解”。
第三步在“规划求解参数”对话框进行选择如下图。
第四步点击“选项”按钮，弹出“规划求解选项”对话框
第五步选择“采用线性模型”和“假定非负”，单击“确定”，返回下图。单击“求解”，即可解决此题。
最后结果如下页图所示。
应如何安排这四种家具的日产量，使得该厂的日利润最大？
表1 雅致家具厂基本数据
12
3
4
家具类型
劳动时间
21 3
2
（小时/件）
木材
42 1
2
（单位/件）
玻璃（单位/ 6 2
1
2
件）
单位利润
60 20 40
30
（元/件）
最大销量 100 200 50
100
（件）
可提供两 400小时 600单位 1000单位
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可以加载或保存规划求解模型,并对规划求解过程的高级属性进行控制

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

数学规划模型实验指导手册Excel的规划求解加载宏求解数学规划问题一、什么是规划求解加载宏？规划求解加载宏（简称规划求解）是Excel的一个加载项1，可以用来解决线性规划与非线性规划优化问题。

规划求解可以用来解决最多有200个变量，100个外在约束和400个简单约束（决策变量整数约束的上下边界）的问题。

可以设置决策变量为整型变量。

规划求解加载宏的开发商是Fronline System公司。

用户通过自定义安装MS-Office所使用的是标准版本规划求解加载宏，Fronline System公司同时提供增强的Premium Solver工具。

规划求解工具在Office典型安装状态下不会安装，可以通过自定义安装选择该项或通过添加/删除程序增加规划求解加载宏。

二、怎样加载规划求解加载宏？加载规划求解加载宏的方法如下：（1）打开“工具”下拉列菜单，然后单击“加载宏”，打开“加载宏”对话框。

（2）在“可用加载宏”框中，选中“规划求解”旁边的复选框2，然后单击“确定”按钮。

1加载项的功能是为Microsoft Office 提供自定义命令或自定义功能的补充程序2如果“规划求解”未列出，请单击“浏览”进行查找。

（3）如果出现一条消息，指出您的计算机上当前没有安装规划求解，请单击“是”用原Office安装盘进行安装。

（4）单击菜单栏上的“工具”。

加载规划求解后，“规划求解”命令会添加到“工具”菜单中。

三、怎样使用规划求解加载宏求解数学规划？规划求解加载宏是一组命令构成的一个子程序，这些命令有时也称作假设分析3工具，其功能是可以求出线性和非线性数学规划问题的最优解和最优值。

使用规划求解加载宏求解数学规划的步骤首先，在Excel工作表中输入目标函数的系数向量、约束条件的系数矩阵和右端常数项（每一个单元格输入一个数据）；其次，选定一个单元格存储目标函数（称为目标单元格），用定义公式的方式在这个目标单元格内定义目标函数；再次，选定与决策变量个数相同的单元格（称为可变单元格），用以存储决策变量；再选择与约束条件个数相同的单元格，用定义公式的方式在每一个单元格内定义一个约束函数（称为约束函数单元格）；最后，点击下拉列菜单中的规划求解按钮，打开规划求解参数设定对话框（如图4所示），完成规划模型的设定模型设定方法如下：（1）设定目标函数和优化方向：光标指向规划求解参数设定对话框中的“设置目标单元格”提示后的域，点击鼠标左键，然后选中Excel工作表中的目标单元格。

运用EXCEL求解线性规划模型

EXCEL求解线性规划模型
线性规划问题解的讨论线性规划问题解的种类？唯一解的表现是……？无穷解的表现是……？无可行域无解的表现是……？可行域无界的表现是……？上述结果用EXCEL建模求解的最后对话框提示不同。
01
图解法解得分析：
02
解的结果
03
有可行域
04
无可行域
05
可行域有界
06
可行域无界
▍单击“粘贴列表”，在电子表格中的相应位置得出结果。
将单元格名称粘贴到电子表格中
EXCEL求解线性规划模型
EXCEL求解线性规划模型
EXCEL求解线性规划模型
对结果进行修饰利用“替换”功能中的“全部替换”去掉“=Sheet1!”和“$”，得出区域名称和引用结果。
EXCEL求解线性规划模型
规划求解过程
删除：选择欲删除单元格名称，单击“删除”。
3
1
2
4
路径：“插入”——“名称”——“定义”，进入“定义名称” 界面。
单击某个名称，可查看其引用位置。
更改：先添加新名称，再删除原名称。也可修改原名称的引用位置。
查看、更改、删除
EXCEL求解线性规划模型
查看、更改、删除操作界面
EXCEL求解线性规划模型
07
唯一解
08
无穷解
09
唯一解
10
无穷解
11
无解
12
一定无解
EXCEL求解线性规划模型
线性规划问题的灵敏度分析是在求出最优解的基础上，进一步讨论当cj、bi、aij发生变化时，对最优解的影响。
判断某一参数发生变化，原最优解是否发生变化？
02
怎样得出使原最优解不变的参数变化范围

使用Excel进行线性规划求解功能，轻松找到问题的最优的解决方案

使用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编程。

(二)线型规划问题的Excel建模求解

（一）线型规划问题的Excel 建模求解
实验目的:掌握在Excel 中建立线性规划模型和求解的方法
实验内容:
利用Excel “规划求解”求解各种线性规划问题。

（如果“工具”菜单没有显示“规划求解”子菜单，可到“宏”下加载。

）
实验步骤
案例1利用EXCEL 求解线性规划
⎪⎪⎩⎪⎪⎨⎧≥≤≤≤++=0
,1551641222..32max 2121212
1x x x x x x t s x x z 第一步建模依次在相应的单元格内输入数据和公式，建模如图1 Sumproduct()函数：
在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

图1线型规划的Excel 模型
第二步设置规划求解参数如图2和图
3，其中，“选项”中选取“假定非负”和“采用线性模型”，其它采用默认选项，如图
图2 规划求解参数设置
图3 选项设置
第三步求解设置完毕后，单击图2中“求解”按钮，出现如图4规划求解结果对话框
图4 规划求解结果对话框
如图4所示，共提供3类报告，选择你想要的报告，单击确定按钮，完成运算，最后计算结果如图5
图5 计算结果。

使用Excel求解线性规划问题

使用Excel 求解线性规划问题利用单纯形法手工计算线性规划问题是很麻烦的。

office 软件是一目前常用的软件，我们可以利用office 软件中的Excel 工作表来求解本书中的所有线性规划问题。

对于大型线性规划问题，需要应用专业软件，如Matlab ，Lindo ，lingo 等，这些软件的使用这里我们不作介绍，有需要的，自己阅读有关文献资料。

用Excel 工作表求解线性规划问题，我们需要先设计一个工作表，将线性规划问题中的有关数据填入该工作表中。

所需的工作表可按下列步骤操作：步骤 1 确定目标函数系数存放单元格，并在这些单元格中输入目标函数系数。

步骤 2 确定决策变量存放单元格，并任意输入一组数据。

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

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

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

步骤 6 确定目标函数值存放单元格，并在该单元格中输入目标函数值的计算公式。

例建立如下线性规划问题的Excell 工作表：解：下表是按照上述步骤建立的线性规划问题的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 单击[约束］框架中的[添加]按钮。

利用excel求解线性规划问题

利用excel求解线性规划问题线性规划（Linear Programming，LP）是一种用于求解最优化问题的数学方法。

它在经济学，管理学，工程学等领域得到了广泛应用。

Excel是一种功能强大的电子表格软件，提供了一些内置的工具和函数，可以帮助我们求解线性规划问题。

在Excel中求解线性规划问题，通常需要使用“规划求解”工具，该工具位于“数据”选项卡的“分析”分组中。

下面将逐步介绍如何使用Excel求解线性规划问题。

步骤1：建立模型首先，我们需要建立线性规划模型。

模型通常包括目标函数和约束条件。

目标函数：我们需要定义一个目标函数，它表示我们希望最大化或最小化的目标。

在Excel中，可以使用单元格引用和各种数学运算符来定义目标函数。

约束条件：我们需要定义一系列约束条件，这些约束条件是对决策变量的限制。

在Excel中，可以使用不等式和等式来表示约束条件。

每个约束条件都可以转化为一个单元格引用和数学运算符的组合。

步骤2：输入数据在建立模型之后，我们需要输入相关数据。

这包括目标函数中的系数和约束条件中的系数和约束值。

在Excel中，我们可以使用单元格来输入这些数据。

步骤3：设置规划求解选择“数据”选项卡，在“分析”分组中找到“规划求解”工具。

如果没有找到该工具，可能需要先启用“加载项”中的“分析工具包”。

点击“规划求解”，将会打开一个对话框。

在这个对话框中，我们需要输入一些参数来设置求解过程。

目标单元格：这是包含目标函数结果的单元格。

调整变量单元格：这是包含决策变量的单元格范围。

约束条件：这是包含约束条件的单元格范围。

约束条件中的系数：这是一个选择项，用于指定约束条件中的系数是包含在单元格范围中还是直接输入。

约束条件的约束值：这是一个选择项，用于指定约束条件中的约束值是包含在单元格范围中还是直接输入。

约束条件的约束类型：这是一个选择项，用于指定约束条件的类型（大于等于，小于等于等）。

非负约束：这是一个复选框，用于指定决策变量是否具有非负约束。

系统工程结课论文----线性规划问题的Excel建模及求解

《系统工程》结课论文线性规划问题的Excel建模及求解最优化就是从所有可能的方案中选择最合理的一种以达到最优目标的学科。

运筹学作为一种新型的管理方法，在解决系统工程优化问题上有着广泛的应用。

建立线性规划模型问题使得许多动态决策管理问题优化并得到解决。

对实际规划问题作定量分析，必须建立数学模型。

建立数学模型首先要选定适当的目标变量和决策变量，并建立起目标变量与决策变量之间的函数关系,称之为目标函数。

然后将各种限制条件加以抽象,得出决策变量应满足的一些等式或不等式，称之为约束条件。

在解决线性规划问题上本文我介绍采用Excel如何建模并解决问题。

非线性规划问题的一般数学模型可表述为求未知量x1，x2，…，x n，使满足约束条件：gi(x1，…，x n)≥0i＝1,…,mhj(x1，…，x n)＝0 j＝1,…,p并使目标函数f(x1，…,x n)达到最小值(或最大值)。

其中f，诸g i和诸h j都是定义在n维向量空间Rn的某子集D(定义域)上的实值函数,且至少有一个是非线性函数。

上述模型可简记为：min f(x)s.t. g i(x)≥0i＝1,…,mh j(x)＝0 j＝1,…,p其中x＝(x1，…,x n)属于定义域D，符号min表示“求最小值”，符号s.t.表示“受约束于”。

定义域D中满足约束条件的点称为问题的可行解。

全体可行解所成的集合称为问题的可行集。

对于一个可行解x*,如果存在x*的一个邻域，使目标函数在x*处的值f(x*)优于 (指不大于或不小于)该邻域中任何其他可行解处的函数值，则称x*为问题的局部最优解（简称局部解）。

如果f(x*)优于一切可行解处的目标函数值,则称x*为问题的整体最优解（简称整体解）。

实用非线性规划问题要求整体解，而现有解法大多只是求出局部解。

虽然运用表上作业法已使人们可以方便地给出一般线性规划的最优解（或满意解），并且也可给出某些参数的灵敏度分析。

但随着科学、经济的发展，竞争的加剧，手工操作的局限性逐渐暴露出来。

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

如何在Excel中建立并求解线性规划模型刘桂莲摘要：数学中线性规划问题的求解一直是很繁琐的，功能强大的Excel软件为我们提供了一种很好的求解方法，但这种方法却很少被人了解。

本文就如何在Excel中建立并求解线性规划模型作了较详尽的论述。

关键词：线性规划数学模型电子表格模型规划求解Excel线性规划是运筹学的一个分支，它的应用已愈来愈深入到社会生产和经济活动的各个领域。

描述线性规划问题的抽象的数学式子是线性规划问题的数学模型。

建立数学模型后，求解满足约束条件的目标函数的最优解是解决线性规划问题的关键。

数学中常用的方法是图解法和单纯形法，而图解法只适用于两个变量的目标函数，单纯形法则计算量相当大，步骤烦琐，容易出错。

在Excel中建立电子表格模型，并利用它提供的“规划求解”工具，能轻松快捷地求解模型的解。

例如，某玻璃制品公司有三个工厂，公司目前决定停止不赢利产品的生产并撤出生产能力来生产两种新开发的产品：玻璃门和双把窗。

估计三个工厂每周可用来生产新产品的时间分别为4小时、12小时、18小时，而每扇门需工厂1生产时间1个小时和工厂3生产时间3个小时，每扇窗需工厂2和工厂3生产时间各为2个小时，预测门的单位利润是300元，窗的单位利润是500元，问每周两种新产品数量的哪种组合能使总利润最大？最大，需满足的条件是：⑴三个工厂每周用于生产新产品的时间≤每周可得时间⑵每周门、窗的生产数量均≥0。

设每周门的生产数量为x，窗的生产数量为y，则该问题的数学模型即为：最大化利润P =300x+500y，约束条件：x≤4，2y≤12，3x+2y≤18，x≥0和y≥0。

将上表的有关数据输入到Excel中，建立如图1所示的电子表格模型。

被输入已知数据的单元格是数据单元格，如单元格C5：D8，G5：G7。

决策变量（即两种产品每周的生产量）放在单元格C9和D9，正好定位在这些产品所在列的数据单元格下面，这种含有需要做出决策的单元格是可变单元格。

excel求解线性规划

模型参数设置
在开始求解之前，需先在对话框中设置好各种参数，包括目标单元格、问题类型（求最大值还是最小值）、可变单元格以及约束条件等。
规划求解选项
在设置完模型参数之后，需要设置计算参数，点击“选项”按钮，选择运算参数。
求解
在设置各项参数后，点击“求解” 按钮。弹出如下窗体：
选择运算结果报告(可根据需要选择敏感性报告、极限值报告)，并确定，则计算结果保存在名为“运算结果报告”中。
求解结果
x1和x2。
(3) 确定用于表示原约束方程的左边的单元格，称为输出单元格。这里分别用E4，E5表示第一和第二个约束的左边，由于约束左边决定于变量的取值，即决定于可变单元格C7和D7的值，所以E4，E5取值决定于C7，D7。
线性规划的excel求解模型
在单元格E4和E5中分别输入： E4 =C4*C7+D4*D7 E5 =C5*C7+D5*D7
建立线性规划模型
决策变量
x1为电视广告投放量
x2为印刷媒体上投放量
目标函数
Max z 150x1 200x2
约束条件
去污喷剂新型液洗剂非负约束
2%x2 4%
3%x1 2%x2 18%
x1, x2 0，整数
线性规划的excel求解模型
(1) 在excel电子表格中输入如下内容：
(2) 确定用于表示变量的单元格，称为可变单元格，这里分别用C7，D7表示
(4) 确定用于表示目标函数值的单元格，称为目标单元格，这里用E6表示。在E6输入：E6 =C6*C7+D6*D7
注意：特别关注C7，D7和E6，所以将其背景刷灰
线性规划的excel求解模型
注意: (1) 特别关注决策变量的取值以及目标函数值，所以C7，D7和E6，所以将

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

下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。

例1 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。

具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。

产品组合通常必须满足以下约束：● 产品组合使用的资源不能超标。

● 对每种产品的需求都是有限的。

我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。

下面，我们来考虑让某医药公司的最优产品组合问题。

该公司有六种可以生产的药品，相关数据如下表所示。

设该公司生产药品1～6的产量分别为126,,,x x x （磅），则最优产品组合的线性规划模型为123456123456123456123456max 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表示的列向量的内积。

这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。