用excel规划求解并作灵敏度分析
EXCEL控的数据敏感性分析技巧
EXCEL控的数据敏感性分析技巧Excel控制的数据敏感性分析技巧数据敏感性分析是一种对数据的变化和不确定性进行评估的方法，能够帮助我们了解数据对结果的影响程度。
在Excel中，我们可以利用各种功能和技巧进行数据敏感性分析，以提高我们对数据的理解和决策的准确性。
本文将介绍几种常用的Excel数据敏感性分析技巧。
1. 数据表数据表是Excel中一个非常有用的功能。
使用数据表，我们可以将数据分组并进行快速分析。
在进行数据敏感性分析时，我们可以将不同的变量作为数据表的行或列，然后通过改变变量的值，观察结果的变化。
数据表还可以用于对大量数据进行排序、筛选和统计。
2. 条件格式条件格式是Excel中用于根据某些条件自动给单元格添加样式的功能。
在数据敏感性分析中，我们可以使用条件格式来突出显示对结果影响较大的数据。
例如，我们可以设定一个条件，如果某个单元格的数值超过或低于某个阈值，那么该单元格就会显示为红色或绿色，以便我们更容易地发现数据的敏感性。
3. 数据透视表数据透视表是Excel中用于汇总和分析大量数据的功能。
通过数据透视表，我们可以将数据按照不同的维度进行分组，并对这些组进行聚合和计算。
在进行数据敏感性分析时，我们可以通过改变数据透视表中的字段和筛选条件，来观察结果的变化。
数据透视表还可以用于对数据进行图表化展示，以便我们更好地理解数据的特点和趋势。
4. 目标搜索目标搜索是Excel中一个用于求解某个特定目标值的功能。
在数据敏感性分析中，我们可以使用目标搜索来找到使得某个结果达到预期的变量值。
通过设定目标值和相关约束条件，Excel会自动计算出满足这些条件的变量值，并将其显示在指定单元格中。
5. 条件求和条件求和是Excel中一个用于根据条件对数据进行汇总运算的功能。
在数据敏感性分析中，我们可以使用条件求和来提取满足某些条件的数据，并进行聚合运算。
通过改变条件，我们可以观察结果的变化，并进一步对数据的敏感性进行评估。
excel求解线性规划和灵敏度分析实训过程记录及学习收获
excel求解线性规划和灵敏度分析实训过程记录及学习收获线性规划是一种数学优化模型，用于对一组线性限制条件下的线性目标函数进行优化。
Excel 能够进行线性规划问题的求解和灵敏度分析，以下是实习过程的记录和收获总结：1. 实训任务我们的实训任务是一个有饲料限制的生产计划问题，其中需要决定生产哪些种类的产品、购买何种原材料、以及在何时生产这些产品，以使得利润最大化。
任务中给定了各种产品需要的原材料数量，各种原材料的数量与价格，及一些限制条件，例如生产时间，最小生产量等。
2. Excel求解线性规划问题Excel中求解线性规划问题的函数是“Solver”，首先需要打开Excel中的“数据”选项卡，然后在“分析”工具中找到“Solver”。
进入“Solver参数”对话框后，需要输入目标函数和限制条件，并且设置决策变量的可变性、约束条件的类型和数量。
最后根据需要设置求解的约束条件和目标函数的目标方向，点击“求解”即可。
在我们的实训任务中，我们首先需要设置约束条件，限制了各种产品需要的原材料数量，并且确保生产时间在规定范围内。
然后我们需要设置各个决策变量的可变性，例如选择生产哪些产品，购买何种原材料以及在何时生产这些产品等。
最后将目标函数设置为生产的利润最大化，并且设置约束条件为“>=0”，以确保决策变量的可行性。
点击“求解”即可得出最优解。
3. Excel灵敏度分析Excel的灵敏度分析功能可以帮助我们了解线性规划问题的各个变量对于目标函数的影响程度。
Excel中灵敏度分析的函数是“规划求解器的报告”，在对话框中选择“接受解决方案”，然后勾选“制作规划求解器报告”选项，即可生成报告。
在报告中，我们可以看到各个决策变量的最优解以及目标函数的最优值。
同时，报告中还包括影响目标函数的变量的“系数范围”和“变化量”，我们可以通过调整这些参数来预测目标函数的变化情况。
4. 学习收获通过这次实训，我学会了如何使用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 软件的使用步骤“规划求解”可以解决数学、财务、金融、经济、统计等诸多实际问题，在此我们只举一个简单的应用实例，说明其具体的操作方法。
90. 如何在Excel中进行敏感性分析？
90. 如何在Excel中进行敏感性分析？90、如何在 Excel 中进行敏感性分析？在当今的数据驱动时代，Excel 作为一款强大的电子表格软件，被广泛应用于各种数据分析和决策支持场景。
敏感性分析作为一种重要的分析方法，可以帮助我们了解模型中输入变量的变化对输出结果的影响程度，从而为决策提供更可靠的依据。
接下来，让我们一起深入探讨如何在 Excel 中进行敏感性分析。
首先，我们需要明确敏感性分析的概念。
简单来说，敏感性分析就是研究当模型中的某个或某些输入变量发生变化时，输出结果会如何相应地改变。
这对于评估模型的稳定性和可靠性，以及识别关键的影响因素非常有帮助。
在 Excel 中进行敏感性分析，通常可以采用以下几种方法：一、数据表格法这是一种较为直观和简单的方法。
假设我们有一个销售预测模型，其中销售量、单价和成本是影响利润的主要因素。
我们可以在 Excel 中创建一个数据表，将这三个变量放在列标题上，然后在不同的行中输入它们可能的取值。
接着，通过公式计算出每个组合下的利润。
这样，我们就可以直观地看到不同变量取值对利润的影响。
例如，假设利润的计算公式为：利润＝（销售量单价）成本。
我们可以在 Excel 中输入如下公式：在 B2 单元格输入：＝B1C1 D1然后通过向下填充或复制公式，得到不同变量组合下的利润值。
通过观察这个数据表，我们可以快速了解每个变量对利润的影响程度，例如销售量增加 10%时利润的变化情况，或者单价降低 5%时利润的变化情况。
二、单变量求解当我们想要知道当输出结果达到某个特定值时，某个输入变量应该取什么值时，可以使用单变量求解功能。
比如，我们仍然以销售预测模型为例，已知当前的销售量、单价和成本，以及计算出的利润。
现在假设我们希望利润达到一个特定的目标值，比如 10000 元，然后想知道在这种情况下，单价应该调整为多少。
操作步骤如下：首先，在 Excel 中输入利润的计算公式，然后选择“数据”选项卡中的“假设分析”，再点击“单变量求解”。
灵敏度分析实验例子
灵敏度分析实验例子实验报告课程名称：运筹学实验项目名称：应用Excel对线性规划进行灵敏度分析班级与班级代码：实验室名称（或课室）：专业：任课教师：学号：姓名：实验日期：2010 年10 月18 日广东商学院教务处制姓名实验报告成绩评语：指导教师（签名）年月日说明：指导教师评分后，实验报告交院（系）办公室保存。
实验二应用Excel对线性规划的灵敏度分析一、实验目的与要求1.了解线性规划模型中各参数的变化对最优解的影响。
2.会用Excel中提供的敏感性报告对目标函数系数进行灵敏度分析。
3.会用Excel中提供的敏感性报告对约束条件右端值的灵敏度分析。
二、实验步骤与方法1.可以在电子表格中采取试验的方法，不断增加或减少的jc值，直到最优解发生改变，以找到最优解发生变化时对应的jc值.但是，这样计算太麻烦了。
2.在Excel求得最优解之后，在其右边列出了它可以提供的三个报告。
选择第二项敏感性报告的选项，就可以得到灵敏度的分析报告，它显示在模型的工作表之前。
3.当几个价值系数同时变动时，注意使用百分之百法则。
4.对约束条件限定数的灵敏度分析同上：选择第二项“敏感性报告”的选项，就可以得到灵敏度的分析报告，其中“约束”表即是。
5.若几个约束限定数同时变动，也要注意使用百分之百法则。
三、实验内容第1题．A医院放射科目前可以开展X 线平片检查和CT检查业务，现拟购买磁共振仪，以增设磁共振检查业务。
为此A 医院收集了有关信息，从医院获取最大利润角度出发，问是否应购买磁共振仪？经过资料收集，A 医院估计今后放射科如果开展此3项业务，在现有放射科医务人员力量和病人需求的情况下，每月此3项业务的最多提供量为1800人次。
平均每人次检查时间、每月机器实际可使用时间、平均每人次检查利润如下表放射科业务项 目 X线平片检查 CT检查 磁共振检查 平均每人次检查时间（小时/次） 0.10.250.5每月机器实际可使用时间（小时） 300 120 120 平均每人次检查利润（元/次）2060101、建立模型设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求解(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进行线性规划的灵敏度分析
求解线性规划问题
01
点击“规划求解”对话框中的“求解”按钮，Excel将开始求 解线性规划问题。
02
Excel将显示求解结果，包括最优解、目标函数的值、可变单 元格的值等。
03
可以根据需要调整参数或约束条件，重新进行求解，以获得 更优的解或更全面的灵敏度分析。
03 灵敏度分析
灵敏度分析的定义
01
灵敏度分析是评估线性规划模型中参数变化对最优解
的影响程度的过程。
02
它有助于理解模型的最优解对各个参数的敏感程度，
从而更好地理解模型的行为。
03
通过灵敏度分析，可以确定哪些参数对模型的影响最
大，从而在实际情况中更好地调整这些参数。
灵敏度分析的步骤
2. 运行模型
案例二：运输问题优化
约束条件
车辆载重、运输时间、运输路线等。
目标函数
最小化运输成本，同时满足各分区的需求。
灵敏度分析
分析需求量、运输成本、运输时间等参数变 化对最优解的影响。
案例三：资源分配问题优化
01
目标函数
最大化资源利用效率，同时满足 生产需求。
约束条件
02
03
灵敏度分析
资源总量、生产能力、产品质量 等。
THANKS FOR WATCHING
感谢您的观看
分析资源价格、生产能力、产品 质量等参数变化对最优解的影响。
05 结论与展望
线性规划与灵敏度分析的意义
线性规划是一种数学优化技术，用于 在有限资源约束下实现特定目标。灵 敏度分析是线性规划的一个重要组成 部分，用于评估模型参数变化对最优 解的影响。
用excel进行线性规划的灵敏度分析
以上海电器厂的线性规划模型为例：
max Z 3X 8Y
6 X 2Y 1800
s.t.
2 X
Y 4Y
350பைடு நூலகம் 1600
X ,Y 0
（原材料1约束） （原材料2约束）
（劳动时间约束） (非负约束)
用Excel解上述问题，得到上述问题最优解如下：
练习：
以上次的最小化问题---贵州金属厂 成本优化问题为例：
1、若由于市场调节作用，矿石A的价 格下降为35，请问已求得的最优解 和最优值会变化么？若变化,请说 明理由及变化后的值.
2、目标函数的系数在什么范围内变 换，才不会影响最优解？
3、如果矿石Ⅱ的供应量增加50，最 大利润将会变化多少？
• 练习题及课堂讲解例子做一遍，发到邮箱 yunchouxuezuoye@
• 如果原材料2的供应额增加30千克，最大 利润将会变化多少？
这些问题在实际生产管理中是十分重要
敏感性报告
1、用excel得到敏感性报告
是指
决策 变量 所在 单元 格的 地址
即最优值
它的绝对值表 示目标函数中 决策变量的系 数必须改进多 少，才能得到 该决策变量的 正数解（非零
解）
它们表示目标函数中的 系数在允许的增量和减 量范围内变化时，最优 解不变。（注意，这里 给出的决策变量的“允 许变化范围”是指其他 条件不变，仅该决策变 量变化时的允许变化范 围）
影子价格
影子价格是指约束条件右边增加（或减少）一个 单位，使目标值增加（或减少）的值。
例如，第一个约束条件（原材料1供应额约束）的 影子价格为0，说明再增加或减少一个单位的原 材料供应额，最大利润不变；第二个约束条件 （原材料2供应额约束）的影子价格为2，说明 在允许范围[300，400]内，再增加或减少一个 单位的原材料2供应额，最大利润将增加2元。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
题目
如何利用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%。
问在满足上述要求的前提下投资者该如何选择投资组合使平均年收益率最高？（不同的投资方式的具体参数如下表。
）
解：设xi为第I种投资方式在总投资额中的比例，则模型如下：Max S=11x1+15x2 +25x3+20x4+10x5+12x6+3x7
s.t.
3x1+10x2 + 6x3+ 2x4+ x5+ 5x6 £ 5
11x1+15x2+25x3+20x4+10x5+12x6+3x7 ³ 13
x1+ 3x2 + 8x3 + 6x4+ x5+ 2x6 £ 4
15x2 +30x3 +20x4+5x5 +10x6 ³10
x1+ x2 + x3 + x4 + x5 + x6+ x7 = 1
x1,x2,x3,x4,x5,x6,x7 ³0
在EXCEL表格中，建立线性规划模型可以通过以下几步
完成：
(1)首先将题目中所给数据输入工作表中，包括基础数据、
约束条件等已知信息，如图1所示，其中单元格B8、H8是可变
单元格，不需要输入任何数据或公式，最后的计算结果将显示
其中。
基础数据
决策变量
目标方程
约束条件
(2)将目标方程和约束条件的对应公式输入各单元格中，回
车后以下四个单元格均显示数字“0”。
B11=SUMPR0DUCT(B3：H3，B8：H8）
B14=SUMPR0DUCT(B2：H2，B8：H8）
B15=SUMPR0DUCT(B3：H3，B8：H8）
B16=SUMPR0DUCT(B4：H4，B8：H8）
B17=SUMPR0DUCT(B5：H5，B8：H8）
B18=SUM(B8：H8)
线性规划问题的电子表格模型建好后，即可利用“规划求
解”功能进行求解。
针对图1的电子表格模型，在工具菜单中选择
“规划求解”命令，弹出“规划求解参数”窗口。
在该对话框中，目标单元格选择B11，问题类型选择“最大值”，可变单元格选择B8：
H8，点击“添加”按钮，弹出“添加约束”对话框，根据所建模
型，共有三个约束条件，针对约束一：
3x1+10x2 + 6x3+ 2x4+ x5+ 5x6 £ 5，左端“单元格引用位置”应选择输入B14，右端输入C14，符号类型选择“<=”。
继续添加约束二、三，点击“添加”，分别选择：B15³C15，B16£C16，
B17³C17，B18=C18完成后选择“确定”，回到“规划求解参数“。
求解参数右侧有一个“选项”按钮，利用它可以在求解之前
对求解过程做一些特定的设置。
本例中的线性规划模型对x1和
x2有非负约束的要求，点击“选项”按钮，弹出“规划求解选项”对话框，该对话框中是关于求解问题的一些更细致的选项，其中
最重要的是“采用线性模型”和“假定非负”，确定选择这两项如图5所示，这就告诉Excel求解的是一个线性规划问题，并且为
非负约束，这样它将拒绝可变单元格产生负值。
其他选项对于小
型计算通常是比较合适的，所以无需进行修改。
点击“确定”回到“规划求解参数”对话框。
以上都做好之后点击求解。
规划求解之后点击solvertable功能，选择一维如图
跳出新界面后，第一行空格选定要想测定哪个系数的灵敏度设a34所在单元格。
第2行空格设定a34从0.1变换到10，精度为0.1。
第3行空格设定输出X1到X7和目标函数所对应的值。
第4行空格设定从D24单元格开始输出结果，然后求解。
如图
3 结果分析
规划求解后问题答案自动显示在表格中，如图所示
得最优解：X1=0.57143，X3=0.42857
平均年收益率=17%
即将57.1%的资金投入到国债，42.9%的资金投入到房地产，可以实现最大收益。
然后进行灵敏度分析，刚才求解中假设求a34的灵敏度（即股票系数的灵敏度），solvertable求解后显示如图。
由图可知，当a34>5.4时，问题的最优解还是X1和X3，由此可知，a34的灵敏度,为a34>5.4。
因此，若想测定其他系数的灵敏度，只需将solvertable的第一行空格选定相应的单元格便是。
4 结论与展望
通过上述步骤可看出，利用Excel进行线性规划模型的求解简便、快捷，表中数值可根据用户要求自行设置，除了在合理安排产品的生产
决策可使用外，对于研究如何合理使用企业各项经济资源，以及研究如何统筹安排，对人、财、物等现有资源进行优化组合、实现最大效能等均可参照使用，能有效地提高组织决策的速度及准确性，而
Excel办公软件的普遍性优点使之更适合于促进科学决策的信息化水平。
[2]
5 参考文献
1.《如何利用EXC E L求解线性规划问题及其灵敏度分析》孙爱萍王瑞梅
2. 张纯义．Excel用于生产决策的线性规划法【J】．会计之友，2005．1O．
（注：范文素材和资料部分来自网络，供参考。
请预览后才下载，期待你的好评与关注。
）。