用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+500x2Max 300x1+500x2(3)约束条件混合部门的总工时的约束2x1+1x2<=5*40+2*15=230净化部门的总工时的约束x1+2x2<=6*40+1*10=250CS-02的销售数量的约束x2<=120变量的非负约束x1，x2>=0.数学模型Max 300x1+500x2St. 2x1+1x2<=230 blending1x1+2x2<=250 purificationX2<=120 CS-02x1,x2>=0 nonnegativeExcel规划求解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）建立电子表格模型；（2）使用Excel规划求解功能求解问题并生成“敏感性报告”；（3）结果分析：哪些问题可以直接利用“敏感性报告”中的信息求解，哪些问题需要重新规划求解，并对结果提出你的看法；（4）在Word文档中书写实验报告，包括线性规划模型、电子表格模型、敏感性报告和结果分析等。

案例1 市场调查问题某市场调查公司受某厂的委托，调查消费者对某种新产品的了解和反应情况。

该厂对市场调查公司提出了以下要求：（1）共对500个家庭进行调查；（2）在被调查家庭中，至少有200个是没有孩子的家庭，同时至少有200个是有孩子的家庭；（3）至少对300个被调查家庭采用问卷式书面调查，对其余家庭可采用口头调查；（4）在有孩子的被调查家庭中，至少对50%的家庭采用问卷式书面调查；（5）在没有孩子的被调查家庭中，至少对60%的家庭采用问卷式书面调查。

对不同家庭采用不同调查方式的费用如下表所示：市场调查费用表家庭类型调查费用（元）问卷式书面调查口头调查有孩子的家庭50 30没有孩子的家庭40 25问：市场调查公司应如何进行调查，使得在满足厂方要求的条件下，使得总调查费用最少？案例2 经理会议建议的分析某公司生产三种产品A1，A2，A3，它们在B1，B2两种设备上加工，并耗用C1，C2两种原材料，已知生产单位产品耗用的工时和原材料以及设备和原材料的每天最多可使用量如下表所示：生产三种产品的有关数据资源产品A1 产品A2 产品A3 每天最多可使用量设备B1(min) 1 2 1 430设备B2(min) 3 0 2 460原料C1(kg) 1 4 0 420原料C2(kg) 1 1 1 300每件利润(元) 30 20 50已知每天对产品A2的需求不低于70件，对A3不超过240件。

90. 如何在Excel中进行敏感性分析？90、如何在 Excel 中进行敏感性分析？在当今的数据驱动时代，Excel 作为一款强大的电子表格软件，被广泛应用于各种数据分析和决策支持场景。

敏感性分析作为一种重要的分析方法，可以帮助我们了解模型中输入变量的变化对输出结果的影响程度，从而为决策提供更可靠的依据。

接下来，让我们一起深入探讨如何在 Excel 中进行敏感性分析。

首先，我们需要明确敏感性分析的概念。

简单来说，敏感性分析就是研究当模型中的某个或某些输入变量发生变化时，输出结果会如何相应地改变。

这对于评估模型的稳定性和可靠性，以及识别关键的影响因素非常有帮助。

在 Excel 中进行敏感性分析，通常可以采用以下几种方法：一、数据表格法这是一种较为直观和简单的方法。

假设我们有一个销售预测模型，其中销售量、单价和成本是影响利润的主要因素。

我们可以在 Excel 中创建一个数据表，将这三个变量放在列标题上，然后在不同的行中输入它们可能的取值。

接着，通过公式计算出每个组合下的利润。

这样，我们就可以直观地看到不同变量取值对利润的影响。

例如，假设利润的计算公式为：利润＝（销售量单价）成本。

我们可以在 Excel 中输入如下公式：在 B2 单元格输入：＝B1C1 D1然后通过向下填充或复制公式，得到不同变量组合下的利润值。

通过观察这个数据表，我们可以快速了解每个变量对利润的影响程度，例如销售量增加 10%时利润的变化情况，或者单价降低 5%时利润的变化情况。

二、单变量求解当我们想要知道当输出结果达到某个特定值时，某个输入变量应该取什么值时，可以使用单变量求解功能。

比如，我们仍然以销售预测模型为例，已知当前的销售量、单价和成本，以及计算出的利润。

现在假设我们希望利润达到一个特定的目标值，比如 10000 元，然后想知道在这种情况下，单价应该调整为多少。

操作步骤如下：首先，在 Excel 中输入利润的计算公式，然后选择“数据”选项卡中的“假设分析”，再点击“单变量求解”。

实验报告课程名称：运筹学实验项目名称：应用Excel对线性规划进行灵敏度分析班级与班级代码：实验室名称（或课室）：809专业：信息管理与信息系统任课教师：学号：姓名：实验日期：2010 年10 月18 日广东商学院教务处制姓名实验报告成绩评语：指导教师（签名）年月日说明：指导教师评分后，实验报告交院（系）办公室保存。

实验二应用Excel对线性规划的灵敏度分析一、实验目的与要求1.了解线性规划模型中各参数的变化对最优解的影响。

2.会用Excel中提供的敏感性报告对目标函数系数进行灵敏度分析。

3.会用Excel中提供的敏感性报告对约束条件右端值的灵敏度分析。

二、实验步骤与方法1.可以在电子表格中采取试验的方法，不断增加或减少的c值，直到最优j 解发生改变，以找到最优解发生变化时对应的c值.但是，这样计算太j麻烦了。

2.在Excel求得最优解之后，在其右边列出了它可以提供的三个报告。

选择第二项敏感性报告的选项，就可以得到灵敏度的分析报告，它显示在模型的工作表之前。

3.当几个价值系数同时变动时，注意使用百分之百法则。

4.对约束条件限定数的灵敏度分析同上：选择第二项“敏感性报告”的选项，就可以得到灵敏度的分析报告，其中“约束”表即是。

5.若几个约束限定数同时变动，也要注意使用百分之百法则。

三、实验内容第1题．医院放射科目前可以开展X线平片检查和CT检查业务，现拟购买磁共振仪，以增A设磁共振检查业务。

为此A医院收集了有关信息，从医院获取最大利润角度出发，问是否应购买磁共振仪？经过资料收集，A医院估计今后放射科如果开展此3项业务，在现有放射科医务人员力量和病人需求的情况下，每月此3项业务的最多提供量为1800人次。

平均每人次检查时间、每月机器实际可使用时间、平均每人次检查利润如下表放射科业务项目X线平片检查CT检查磁共振检查平均每人次检查时间（小时/次）0.1 0.25 0.5每月机器实际可使用时间（小时）300 120 120平均每人次检查利润（元/次）20 60 101、建立模型设123,,x x x 分别表示进行X 线平片检查，CT 检查，磁共振检查的人次，z 表示总利润，建立模型为：123123123123max 2060100.1 300 0.25 120.. 0.5120 1800,,0z x x x x x s t x x x x x x x =++≤⎧⎪≤⎪⎪≤⎨⎪++≤⎪⎪≥⎩（1）Excel 规划求解过程得到规划求解结果及敏感性报告表如下: 规划求解结果敏感性报告表（2）灵敏度分析1)、目标函数系数变动分析①单个目标函数系数变动情况：由以上得到的灵敏度报告表中可以看到：c1 的现值： 20c1 允许的增量：40c1 允许的减量：10c1 的允许变化范围：10≤c1≤60所以在目标函数系数c2、c3不变时，c1在10≤c1≤60范围内变化，问题最优解不变；同理，目标函数系数c1、c3不变时，c2在20≤c2范围内变化，问题的最优解不变；由灵敏度报告表可看出，核共振项目的终值为0，即不增设这个项目的检查，系数c3在c3≤20 的范围内变化都不影响最优解。

用excel进行线性规划的灵敏度分析学习资料线性规划是一种数学优化方法，它提供了一种有效的方法来解决最优化问题。

灵敏度分析是线性规划中的一个非常重要的概念，它是用来研究一些关键参数的变化对于最优解的影响。

在Excel中进行线性规划和灵敏度分析非常方便，本文将介绍如何在Excel中进行线性规划的灵敏度分析。

首先，我们需要先了解Excel中进行线性规划的基本步骤。

以最简单的线性规划模型为例，我们可以用以下模型来说明：目标函数：Maximize f(x,y)=4x+3y约束条件：2x+y <= 8x,y >= 0要在Excel中求解这个问题，我们需要遵循以下步骤：1. 打开Excel，输入目标函数和约束条件。

公式应放在单元格中，约束条件应按行排列，用每行的最后一个单元格来设置限制。

还应设置变量的初始值，并将目标单元格格式设置为“最大值”或“最小值”。

2. 选择“数据”选项卡，在“分析”组内选择“规划问题”选项。

在弹出的窗口中，选择“线性规划”选项，并单击“确定”按钮。

3. 在线性规划窗口中，选择“目标单元格”和变量单元格，然后选择要优化的运算符（如“大于等于”或“小于等于”）。

选择“添加”按钮向模型添加约束条件，直到所有限制都添加完毕。

单击“求解”按钮，Excel将显示变量的最优解、目标函数的最优解以及约束条件的最佳值。

在完成线性规划模型的求解后，我们可以进行灵敏度分析来研究模型中不同参数的变化对最终解的影响。

在Excel中进行灵敏度分析有以下步骤：1. 求出每个决策变量的最优值和目标函数的最优值。

2. 使用Excel的数据表功能，建立一个数据表，将要变化的参数输入到数据表中。

可以一次性变化多个参数。

3. 将数据表的单元格链接到原始模型中的输入参数单元格。

4. 使用Excel的数据表的“展示数据表”功能，查看各参数的最优解或其他解所对应的目标函数的值。

5. 根据结果进行分析，确定哪些参数对最终结果有最大的影响。

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

例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表示的列向量的内积。

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

使用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中的灵敏度分析及整数规划。

1、城市规划部门对扩建城区的工业区和生活区的比例进行规划，每公顷工业区和生活区所耗费的资源及其对本市的贡献如下表所示：
（1）试确定对本市贡献最大的规划方案。

（2）若将电力约束改为工业区50，生活区40，验证规划方案如何变化。

（3）若将电力约束改为工业区65，生活区45，验证规划方案如何变化。

（4）若为配合电网负载分布，扩建城区电力消耗必须不低于8000千度，验证规划方案如何变化。

（5）若去掉水电约束，验证规划方案如何变化。

2、一企业计划生产甲、乙两种产品，这两种产品都要分别在A、B、C、D四种设备上加工。

已知生产每种产品占用设备的时间、每种设备可安排的最大加工时间、以及销售每件产品可获利润如下表所示，现在要求使总利润最大的生产方案，试用整数规划求解此问题。

3、已知五名运动员各种姿势的游泳成绩（各为50米，单位：秒）如下表所示，试分别用分配问题模块和0-1型变量的整数规划从中选拔一个参加200米混合泳的接力队，使预期比赛成绩为最好。

4、需制造2000件的一种产品，这种产品可利用A、B、C设备的任意一种加工。

已知每种设备的生产准备结束费用，生产该产品时的单件成本，以及每种设备的最大加工数量如下表所示，试求解此问题。

5、一公司生产三种产品需三种原料，产品的价格，生产每种产品所需原料量，库存原料量，原料的市场价如下表所示，现在可以生产三种产品也可以直接将原料出售，如何制订经营方案使公司获利最大？试求解此问题。

excel 线性规划Excel是一种非常强大的电子表格软件，可以用来进行各种类型的数学和统计分析，包括线性规划。

线性规划是一种最常见的优化方法，广泛应用于工程、经济和管理等领域。

它通过线性数学模型来找到最佳解决方案，以满足一组约束条件。

在Excel中进行线性规划，我们可以使用Excel的Solver插件。

Solver插件可以帮助我们找到目标函数的最大值或最小值，同时满足约束条件。

下面是一个示例，通过Excel进行线性规划的步骤：1.首先，我们需要创建一个Excel表格，其中包括如下内容：目标函数、约束条件、可调整的单元格等。

2.输入目标函数。

在一个单元格中输入目标函数，例如“=A1*10+B2*15+C3*20”，其中A1、B2、C3是可调整的单元格的引用，而10、15和20是目标函数中每个单元格对应的系数。

3.输入约束条件。

在另一行中，输入约束条件。

例如，“A1>=10”、“B2<=20”等等。

4.确保所需的单元格被定义为可调整单元格，并设置目标单元格为需要最小或最大化的目标函数单元格。

5.打开Solver插件。

在“数据”选项卡中，点击“Solver”。

6.在Solver对话框中，选择“最小化”或“最大化”的目标设置，输入目标函数单元格的引用，并设置约束条件。

7.点击“确定”按钮，Solver会自动计算并找到最佳解决方案。

需要注意的是，Excel的Solver插件对于较大的线性规划问题可能需要更复杂的方法。

解决大规模问题时，可能需要使用更专业的线性规划软件。

总之，Excel是一个非常方便和灵活的工具，可以用来进行线性规划分析。

通过使用Excel的Solver插件，我们可以快速而准确地找到最佳解决方案，并满足所有的约束条件。

无论是对于学生、专业人士还是研究人员，使用Excel进行线性规划都是一种方便和高效的方法。

在Excel上轻松实现线性规划及其对偶问题的求解以及灵敏
度分析
崔秋珍;王淑玉
【期刊名称】《洛阳师范学院学报》
【年(卷),期】2005(24)2
【摘要】利用Excel工具箱中的规划求解虽然可以解一些线性规划问题,但是直接求解在Excel工作表上表达的步骤较多,求解过程繁琐.本文将Excel工具箱中的规划求解和矩阵乘积函数MMULT结合起来,并通过命名单元格名称使得线性规划的求解变得非常简便.轻松实现线性规划对偶问题的求解以及灵敏度分析.
【总页数】3页(P89-91)
【作者】崔秋珍;王淑玉
【作者单位】洛阳工业高等专科学校,河南,洛阳,471003;洛阳师范学院数学系,河南,洛阳,471022
【正文语种】中文
【中图分类】N39;O221.1
【相关文献】
1.求解一类线性规划问题的对偶问题 [J], 王莲花;王继顺
2.线性规划对偶问题的灵敏度分析 [J], 周叶;黄荣欢
3.用Excel软件中规划求解命令求解线性规划问题 [J], 张景川
4.线性规划对偶问题及其灵敏度分析 [J], 袁放
5.如何利用EXCEL求解线性规划问题及其灵敏度分析 [J], 孙爱萍;王瑞梅
因版权原因，仅展示原文概要，查看原文内容请购买。