用excel进行线性规划的灵敏度分析
使用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规划求解的选项可以用来解决线性规划问题。
实验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软件进行灵敏度分析的操作方法。
（二）实验内容和要求：用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中进行敏感性分析？90、如何在 Excel 中进行敏感性分析？在当今的数据驱动时代，Excel 作为一款强大的电子表格软件，被广泛应用于各种数据分析和决策支持场景。
敏感性分析作为一种重要的分析方法，可以帮助我们了解模型中输入变量的变化对输出结果的影响程度，从而为决策提供更可靠的依据。
接下来，让我们一起深入探讨如何在 Excel 中进行敏感性分析。
首先，我们需要明确敏感性分析的概念。
简单来说，敏感性分析就是研究当模型中的某个或某些输入变量发生变化时，输出结果会如何相应地改变。
这对于评估模型的稳定性和可靠性，以及识别关键的影响因素非常有帮助。
在 Excel 中进行敏感性分析，通常可以采用以下几种方法：一、数据表格法这是一种较为直观和简单的方法。
假设我们有一个销售预测模型，其中销售量、单价和成本是影响利润的主要因素。
我们可以在 Excel 中创建一个数据表，将这三个变量放在列标题上，然后在不同的行中输入它们可能的取值。
接着，通过公式计算出每个组合下的利润。
这样，我们就可以直观地看到不同变量取值对利润的影响。
例如，假设利润的计算公式为：利润＝（销售量单价）成本。
我们可以在 Excel 中输入如下公式：在 B2 单元格输入：＝B1C1 D1然后通过向下填充或复制公式，得到不同变量组合下的利润值。
通过观察这个数据表，我们可以快速了解每个变量对利润的影响程度，例如销售量增加 10%时利润的变化情况，或者单价降低 5%时利润的变化情况。
二、单变量求解当我们想要知道当输出结果达到某个特定值时，某个输入变量应该取什么值时，可以使用单变量求解功能。
比如，我们仍然以销售预测模型为例，已知当前的销售量、单价和成本，以及计算出的利润。
现在假设我们希望利润达到一个特定的目标值，比如 10000 元，然后想知道在这种情况下，单价应该调整为多少。
操作步骤如下：首先，在 Excel 中输入利润的计算公式，然后选择“数据”选项卡中的“假设分析”，再点击“单变量求解”。
灵敏度分析的EXCEL求解(N12)
练习：《Sytech 国际公司》问题
案例概述：
Sytech 国际公司是一家在同行业中处于领先地位的计 算机和外围设备的制造商。公司的主导产品分类如下：大型 计算机（MFRAMES）、小型计算机（MINIS）、个人计算机（ PCS）、和打印机（PRINTERS）。公司的两个主要市场是北 美和欧洲。
公司一直按季度作出公司最初的重要决策。公司必须按 照营销部门的需求预测来对分布在全球的三个工厂调整产量 ，公司下一季度需求预测如下：
资源
价格（美元/吨） 联合/非联合 卡车/铁路 可挥发性（%） 生产容量（千吨）
阿什利
49.5 联合 铁路
15 300
贝德福德 50
联合 卡车
16 600
康索 61
非联合 铁路 18 510
邓比 63.5 联合 卡车 20 655
厄勒姆 66.5
非联合 卡车 21 575
弗洛伦斯 71
联合 卡车
22 680
1
0
0
0
1
1
506 <=
18
20
21
22
23
25
19
>=
决策变量
阿什利A贝德福德B 康索C 邓比D 厄勒姆E 弗洛伦斯F加斯顿G 霍普特H 合计
购买数量(千吨)
56
600
0
16
104
0
450
0
1226 =
<=
<=
<=
<=
<=
<=
<=
<=
生产容量（千吨）
300
600
510
655
575
用EXCEL解线性规划的步骤
1. 将目标函数系数放入一行
2. 将每个约束条件系数及常数项放入一行，所有约束系数及常数项成一矩阵
3. 将决策变量的初始值（全0或1）放入一列
4. 用函数SUMPRODUCT或MMULT（A1，A2）将目标 函数值放入一格：目标函数系数行与决策变量列的乘积
5. 用MMULT将每个约束条件系数行与决策变量列乘积放 入对应的常数项旁边格
6. 在工具栏选规划求解
7. 填好目标值所在格、决策变量（可变单元格）、约束条件
选项底下勾选采用线性模型
保存规划求解结果，包括运算结果、敏感性报告、极限值报告
最后结果
灵敏度分析
• 目标函数的系数
– 允许增加或减少的量：此范围内最优解不变 – 递减成本：最优解中等于0的变量，对应的 目标函数中的系数增加或减少多少，最优解 不再为0
• 约束条件右端常数项
– 阴影价格：约束右端常数项增加一个单位， 使得目标函数最优值增加的量 – 允许增加或减少的量：此范围内对应的阴影 价格不变
用EXCEL解线性规划的步骤
1. 2. 3.Biblioteka 4. 5. 6.– – – – – –
将目标函数系数放入一行 将每个约束条件系数及常数项放入一行，所有约束系数及常数 项成一矩阵 将决策变量的初始值（全0或1）放入一列 用函数MMULT（A1，A2）将目标函数值放入一格：目标函数 系数行与决策变量列的乘积（或者用SUMPRODUCT(A1,A2,…) 用MMULT将每个约束条件系数行与决策变量列乘积放入对应 的常数项旁边格 在工具栏选规划求解
用excel进行线性规划的灵敏度分析
求解线性规划问题
01
点击“规划求解”对话框中的“求解”按钮，Excel将开始求 解线性规划问题。
02
Excel将显示求解结果，包括最优解、目标函数的值、可变单 元格的值等。
03
可以根据需要调整参数或约束条件，重新进行求解，以获得 更优的解或更全面的灵敏度分析。
03 灵敏度分析
灵敏度分析的定义
01
灵敏度分析是评估线性规划模型中参数变化对最优解
的影响程度的过程。
02
它有助于理解模型的最优解对各个参数的敏感程度，
从而更好地理解模型的行为。
03
通过灵敏度分析，可以确定哪些参数对模型的影响最
大，从而在实际情况中更好地调整这些参数。
灵敏度分析的步骤
2. 运行模型
案例二：运输问题优化
约束条件
车辆载重、运输时间、运输路线等。
目标函数
最小化运输成本，同时满足各分区的需求。
灵敏度分析
分析需求量、运输成本、运输时间等参数变 化对最优解的影响。
案例三：资源分配问题优化
01
目标函数
最大化资源利用效率，同时满足 生产需求。
约束条件
02
03
灵敏度分析
资源总量、生产能力、产品质量 等。
THANKS FOR WATCHING
感谢您的观看
分析资源价格、生产能力、产品 质量等参数变化对最优解的影响。
05 结论与展望
线性规划与灵敏度分析的意义
线性规划是一种数学优化技术，用于 在有限资源约束下实现特定目标。灵 敏度分析是线性规划的一个重要组成 部分，用于评估模型参数变化对最优 解的影响。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
敏感性报告
1、用excel得到敏感性报告
是指
决策 变量 所在 单元 格的 地址 即最优值
它的绝对值表 示目标函数中 决策变量的系 数必须改进多 少，才能得到 该决策变量的 正数解（非零 解）
它们表示目标函数中的 系数在允许的增量和减 量范围内变化时，最优 解不变。（注意，这里 给出的决策变量的“允 许变化范围”是指其他 条件不变，仅该决策变 量变化时的允许变化范 围）
用excel进行线性规划 的灵敏度分析
以上海电器厂的线性规划模型为例：
max Z 3 X 8Y 6 X 2Y 1800 Y 350 s.t. 2 X 4Y 1600 X ,Y 0 （原材料 约束） 1 （原材料2约束） （劳动时间约束） (非负约束)
练习：
以上次的最小化问题---贵州金属厂成本优化问 题为例：
1、若由于市场调节作用，矿石A的价格下降为35， 请问已求得的最优解和最优值会变化么？若变 化,请说明理由及变化后的值. 2、目标函数的系数在什么范围内变换，才不会 影响最优解？ 3、如果矿石Ⅱ的供应量增加50，最大利润将会 变化多少？30千克，最大利润将为多少？
由表所示的敏感性报告的下部的表格可知，当原材料 2的约束条件右边允许范围[350-50，350+50]，即[300， 400]区间变化时，原材料2的影子价格不变。现在，原 材料2的供应量增加30千克，变成380千克，是在允许 范围内，所以，其影子价格不变，仍然等于2。这就是 说，原材料2的供应量每增加1千克，将使最大利润增 加2元。当原材料2的供应量增加30千克时，最大利润 将增加2*30=60（元），最大利润=3100+60=3160（元）
yunchouxuezuoye@
位于下部的表格，该表格反映约束条件右边变化对目标 值的影响。
“单元格”是指约束条件左边所在单元格的地址 “名字”是指约束条件左边的名称。 “终值”是约束条件左边的终值。 “影子价格”。 “约束条件限制值”，指约束条件右边的值。 “允许的增量”和“允许的减量”，表示约束条件右边在允许的增 量和减量范围内变化时，影子价格不变。（注意，这里给出的约 束条件右边的“允许变化范围”是指其他条件不变，仅该约束条 件右边变化时的允许变化范围）
影子价格
影子价格是指约束条件右边增加（或减少）一个 单位，使目标值增加（或减少）的值。 例如，第一个约束条件（原材料1供应额约束） 的影子价格为0，说明再增加或减少一个单位的 原材料供应额，最大利润不变；第二个约束条 件（原材料2供应额约束）的影子价格为2，说 明在允许范围[300，400]内，再增加或减少一 个单位的原材料2供应额，最大利润将增加2元。
用Excel解上述问题，得到上述问题最优解如下：
X=100
Y=350
这时，利润达到最大，即得到最优目标值3100元
问题：
现在假定市场状况和生产工艺发生了变化，使得目标
函数中的系数发生了变化。例如，产品A的利润系数从 3（元/单位产品）增至3.5，那么，以求得的最优解、 最优目标值会变化吗？ 目标函数的系数在什么范围内变化，才不会影响最优 解？ 如果原材料2的供应额增加30千克，最大利润将会变化 多少？ 这些问题在实际生产管理中是十分重要的，他们也是 灵敏度分析所要回答的问题。
使用敏感性报告进行灵敏度分析
产品A的利润系数从3增至3.5 从敏感性报告上部的表格可知，产品A的系数在 允许的变化范围[3-3，3+1]，即[0，4]区间变化时， 不会影响最优解。现在，产品的利润增至3.5，在 允许的变化范围内，所以最优解不变。
应注意的是。这时最优目标值（即最大利润）将发 生变化，原已求出的最大利润 =3x+8y=3*100+8*350=3100(元) 变化后的最大利润=3100+(3.5-3)*100=3150
•是约束条件左边的终值
•指约束条
•表示约束条件右边在允许的
件右边的 值
增量和减量范围内变化时， 影子价格不变。
2、敏感性报告中各项指标的含义
位于上部的表格反映目标函数中系数变化对最优值的影响。 “单元格”是指决策变量所在单元格的地址 “名字”是指这些决策变量的名称。 “终值”是决策变量的终值，即最优值。 “递减成本”，它的绝对值表示目标函数中决策变量的系数必须改 进多少，才能得到该决策变量的正数解（非零解）。 “目标式系数”是指目标函数中的系数。 “允许的增量”和“允许的减量”，它们表示目标函数中的系数在 允许的增量和减量范围内变化时，最优解不变。（注意，这里给 出的决策变量的“允许变化范围”是指其他条件不变，仅该决策 变量变化时的允许变化范围）