&&&&如何利用EXCEL求解线性规划问题及其灵敏度分析
&如何利用EXCEL求解线性规划问题及其灵敏度分析
如何利用EXCEL求解线性规划问题及其灵敏度分析
若举报审核通过，可奖励20下载分
被举报人：
举报的资源分：
请选择类型
资源无法下载
资源无法使用
标题与实际内容不符
含有危害国家安全内容
含有反动色情等内容
含广告内容
版权问题，侵犯个人或公司的版权
*详细原因：
VIP下载&&免积分60元/年（1200次）
您可能还需要
Q.为什么我点的下载下不了，但积分却被扣了
A. 由于下载人数众多，下载服务器做了并发的限制。若发现下载不了，请稍后再试，多次下载是不会重复扣分的。
Q.我的积分不多了，如何获取积分？
A. 获得积分，详细见。
完成任务获取积分。
论坛可用分兑换下载积分。
第一次绑定手机，将获得5个C币，C币可。
关注并绑定CSDNID，送10个下载分
下载资源意味着您已经同意遵守以下协议
资源的所有权益归上传用户所有
未经权益所有人同意，不得将资源中的内容挪作商业或盈利用途
CSDN下载频道仅提供交流平台，并不能对任何下载资源负责
下载资源中如有侵权或不适当内容，
本站不保证本站提供的资源的准确性，安全性和完整性，同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
数据库下载排行
您当前C币：0&&&可兑换 0 下载积分
兑换下载分:&
消耗C币:0&
立即兑换&&
兑换成功你当前的下载分为 。前去下载资源
你下载资源过于频繁，请输入验证码
如何快速获得积分？
你已经下载过该资源，再次下载不需要扣除积分
如何利用EXCEL求解线性规划问题及其灵敏度分析
所需积分：1
剩余积分：0
扫描微信二维码精彩活动、课程更新抢先知
VIP会员，免积分下载
会员到期时间：日
剩余下载次数：1000
如何利用EXCEL求解线性规划问题及其灵敏度分析
剩余次数：&&&&有效期截止到：
你还不是VIP会员VIP会员享免积分 . 专属通道极速下载
VIP下载次数已满VIP会员享免积分 . 专属通道极速下载，请继续开通VIP会员
你的VIP会员已过期VIP会员享免积分 . 专属通道极速下载，请继续开通VIP会员2009年11月；Application；Experiences；总第168期；应用经验；如何利用EXCEL求解线性规划；问题及其灵敏度分析；孙爱萍；王瑞梅；赣州；341000）；（江西理工大学经济管理学院；要线性规划是运筹学的重要组成部分，在工业、军事、；复杂；关键词线性规划Excel规划求解灵敏度分析中图分；文献标识码A；文章编号5；
2009年11月
Application
Experiences
如何利用EXCEL求解线性规划
问题及其灵敏度分析
（江西理工大学经济管理学院
要线性规划是运筹学的重要组成部分，在工业、军事、经济计划等领域有着广泛的应用，但其手工求解方法的计算步骤繁琐、
复杂。本文以实际生产计划问题为例详细介绍了Excel软件中的&规划求解&功能辅助求解线性规划模型的具体步骤，并对其进行了灵敏度分析。
关键词线性规划Excel规划求解灵敏度分析中图分类号O211.1
文献标识码A
SolutionofLinearProgrammingProblemandSensitivityAnalysisbyExcel
SunAipingWangRuimei
（FacultyofEconomicsandManagement,
）JiangxiUniversityofScienceandTechnologyGanzhou341000
AbstractLinearprogrammingisanimportantpartofOperationResearchwithextensiveapplicationinindustry,
militaryaffairsandeconomicplansetc.However,itsmanualcalculatingprocessesarecomplicated,Thispapertakingthecaseofanactualproductionplaningproblem,introduceconcretestepsonhowtosolvethelinearprogrammingmodelbythetoolofprogrammingsolutionbyExcel,andalsoanalysisonsensitivity.KeywordsLinearprogrammingExcelProgrammingsolutionSensitivityanalysis
对于整个运筹学来说，线性规划（LinearProgramming）是形最成熟的一个分支，是优化理论最基础的部分，也是运成最早、
筹学最核心的内容之一。它是应用分析、量化的方法，在一定的约束条件下，对管理系统中的有限资源进行统筹规划，为决策者提供最优方案，以便产生最大的经济和社会效益。因此，将线性规划方法用于企业的产、销、研等过程成为了现代科学管理的重要手段之一。
1947年丹捷格（G.B.Dantzig）提出的单纯形方法是求解一般线性规划问题的通用方法，对于不太复杂的问题用这种方法手工求解还是可行的，但对于较大型的规划问题手工求解就变得十分困难，计算量大且容易出错，因此运用计算机相关软件求解线性规划问题便成为首选的方式。目前，已有多种软件可提供线SPSS、Lindo、Matlab、Ex-性规划问题的计算机求解，如WinQSB、cel等，通过操作这些软件可以便捷地利用单纯形法求解线性规划问题。相比之下，微软Office办公软件Excel中内含的“规划求解”功能操作较为简便，且应用广泛。从实用和方便的角度出发，本文主要介绍如何利用Excel内含的“规划求解”功能求解线性规划问题及其灵敏度分析。[1]
Excel中的线性规划求解功能并不作为命令直接显示在菜
单中，因此，使用前需首先加载该模块。具体操作过程为：在Excel的菜单栏中选择“工具/加载宏”，然后在弹出的对话框中“规划求解”，并用鼠标左键单击“确定”。加载成功后，在菜选择
单栏中选择“工具/规划求解”，便会弹出“规划求解参数”对话框。在开始求解之前，需先在对话框中设置好各种参数，包括目标单元格、问题类型（求最大值还是最小值）、可变单元格以及约“规划求解”工具，系统会提示插入束条件等。如果是首次加载
MicrosoftOffice（相应版本）安装系统的光盘。[2]
表1A工厂的实际情况
2009年11月
Application
Experiences
二、具体实例及其数学模型
“规划求解”可以解决数学、财务、金融、经济、统计等诸多实际问题，在此我们只举一个简单的应用实例，说明其具体的操作方法。
例如，A工厂生产甲、乙两种产品，这两种产品都要分别在A、B、C设备上加工。按工艺要求，产品甲、乙在设备A、B、C上所已知设备A、B、C计划期内用于生产需的加工台时如表1所示。
这两种产品的能力分别为12h、16h、15h，又知该厂每生产一件乙可分别获得利润2元、3元。问该企业应安排生产两产品甲、
种产品各多少件，使总的利润收入为最大？
此例的问题用数学语言描述为：假设x1、x2分别表示在计乙的产量，该问题的线性规划模型如下：划期内产品甲、
（2）将目标方程和约束条件的对应公式输入各单元格中，回车后以下四个单元格均显示数字“0”。
B10=SUMPRODUCT（B8：C8，B6：C6）B13=SUMPRODUCT（B8：C8，B3：C3）B14=SUMPRODUCT（B8：C8，B4：C4）B15=SUMPRODUCT（B8：C8，B5：C5）
四、线性规划问题的电子表格模型的求解
线性规划问题的电子表格模型建好后，即可利用“规划求功能进行求解。针对图1的电子表格模型，在工具菜单中选解”
择“规划求解”命令，弹出“规划求解参数”窗口，如图2所示。在该对话框中，目标单元格选择B10，问题类型选择“最大值”，可C8，点击“添加”按钮，弹出“添加约束”对话变单元格选择B8：框，如图3所示。
根据所建模型，共有三个约束条件，针对约束一：2x1+2x2≤12，左端“单元格引用位置”应选择输入B13，右端输入D13，符号类型选择“&=”。继续添加约束二、三，点击“添加”，分别选择：B14&=D14，B15&=D15，完成后选择“确定”，回到“规划求解参
三、建立线性规划问题的电子表格模型
把已经建立的线性规划模型转化为Excel电子表格文件形式具有一定的随意性，其表现形式可以多种多样，但应保持模型表现形式上的组织性、逻辑性、直观性、易操作性。为此，通常把模型放在电子表格中分成四个部分：基础数据、决策变量、目标方程、约束条件，如图1所示。其中决策变量为占位格，计算结果会显示其中；目标方程和各个约束条件的左端项位置输入“符号”部分只是一个说明符号，不相应的公式；约束条件中的参与运算。
此外，本例中将用到SUMPRODUCT函数，该函数的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和，具体解释可参见MicrosoftExcel自带的“帮助”功能。
在EXCEL表格中，建立线性规划模型可以通过以下几步完成：
（1）首先将题目中所给数据输入工作表中，包括基础数据、约束条件等已知信息，如图1所示，其中单元格B8、C8是可变单元格，不需要输入任何数据或公式，最后的计算结果将显示其中。
约束对话框
规划求解参数对话框
图1A工厂问题的电子表格模型图4设置好所有参数的情形
2009年11月
Application
Experiences
数”对话框，出现如图4所示的界面。（小窍门：相同约束符号且集中放置的约束可以一次输入多个，如本例中单元格引用位置处可直接在Excel表中用鼠标选择B13：B15，约束值处选择D13：D15，中间选择“&=”即可。）
右侧有一个“选项”按钮，利用它可以在求解之前在图4中，
对求解过程做一些特定的设置。本例中的线性规划模型对x1和x2有非负约束的要求，点击“选项”按钮，弹出“规划求解选项”对话框，该对话框中是关于求解问题的一些更细致的选项，其中最重要的是“采用线性模型”和“假定非负”，确定选择这两项如这就告诉Excel求解的是一个线性规划问题，并且为图5所示，
非负约束，这样它将拒绝可变单元格产生负值。其他选项对于小点击“确定”回到型计算通常是比较合适的，所以无需进行修改。“规划求解参数”对话框。
图7本例的最终求解结果
图5规划求解选项对话框
图8本例的敏感性报告及其相关参数说明
使付出时间和精力，也只能得到模型参数粗略的估计或预测值。因此，管理者需要了解当模型参数值发生变化时最优决策方案是否会发生变化，这就是灵敏度分析问题。
图6的“规划求解结果”对话框除了告知得到问题的一个最
规划求解结果对话框
优解之外，还有一个报告框，其中有一项为敏感性报告，用于报告灵敏度分析的结果，选中此项并点击“确定”按钮就会出现敏感性报告，如图8所示。该报告由可变单元格和约束两部分组成，分别对应于目标函数Ci和资源限量bj变化的分析。
可变单元格部分共提供五栏数据，“终值”栏表明了问题的“目标式系数”栏表示C最优解，第二栏给出了降低了的成本。的现值，后面两栏指的是Ci在保持最优解不变的前提下，允许增加或减少的量。其中“1E+30”是1×1030的科学计数法，表示无穷大，这是因计算机的精度限制而产生的。本例中，△C1的变化范围为-2≤△C1≤1，则-2+2≤C1≤1+2，即产品甲的单件利润C1的取值范围为[0，3]。同理可得到产品乙的单件利润C2的取值范围为[2，+∞]。
同样，约束部分亦提供了五栏数据，“终值”栏指的是当前方案下使用的资源数量，第三栏是现有的资源量，最后两栏指的是
（下转第51页）
点击图4右上侧的“求解”按钮，计算机后台便开始对问题进行求解，对于小型问题整个求解过程只需几秒钟，求解结束后出现“规划求解结果”对话框，如图6所示，找到一个最优解，选定“保存规划求解结果”，点击“确定”，则在Excel表的既定位置会出现求解的结果，如图7所示。若模型没有最优解，对话框将“规划求解找不到有用的解”或者“‘设置目标单元格’的值显示未收敛”。
由图7可看出，A工厂应生产甲、乙各3件，可获得利润最大，且总收入为15元。
五、利用Excel进行线性规划的灵敏度分析
在讨论线性规划问题时，建立线性规划模型所需的数据都是已知的，这些数据被称为模型的参数。实际中获取这些模型参数并不简单，需要经验和实践，需要付出时间和精力，但有时即
2009年11月
Application
Experiences
EndIfNextjNexti
（&b3&）.SelectRange
Application.ScreenUpdating=TrueEndSub
注：单引号后的文字为代码注释，可以不输入。
代码说明：以上代码是通过循环查找两工作表中的序号，如果两序号相同，就把工资发放表中此序号的工资明细复制到统计表中，实现高级筛选，如果不相符的，则不复制。
三、创建按钮，实现筛选
1、源代码编辑无误后保存并退出，然后选择［视图］→［工具栏］→［窗体］命令，出现［窗体］控件，选择［窗体］→［按钮］命令，移开鼠标，光标变成小十字形，然后将小十字形移到适当位置，按下左键拖动鼠标，直到出现的方框大小合适后，释放鼠标左键，形成一个按钮，点击按钮输入“筛选”文字，然后在此按钮
“宏”对话框，选定宏上单击右键，选择［指定宏］命令，弹出
“CZTJ”，单击对话框右上方的［确定］按钮。
2、以后筛选人员工资明细时，只要在“统计表”的序号列中输入要统计的序号，并按“筛选”按钮，即可筛选出人员的工资明细，完成后的结果如图2所示。
[1]伍云辉.完全手册EXCELVBA办公应用开发详解[M].北2008.京：电子工业出版社，
许国（1970~）诸暨市教育局核算中心统管会计、中国管理科学研究院学术委员会特约研究员。浙江省诸暨市人。中共党
本员。2000年2月被评为绍兴市级教育系统先进财会工作者，
科学历，会计师职称。在诸暨市教育局核算中心任统管会计，兼任系统管理员。业余时间开发了《XG人事工资管理系统V1.0》，在全市推广使用，取得了较好的效果，近40篇论文在国家级、省级刊物上发表。斯泉均（1970~）男诸暨市会计核算中心会计。浙江省诸暨市人，中共党员，本科学历，会计师职称。
在诸暨市会计核算中心任统管会计，兼任系统管理员。在计算机方面有一定的造诣，在国家级、省级刊物上发表多篇论文。
四、一点体会
EXCEL电子表格功能相当强大，上面的一个高级筛选功能
只用了短短的几句语句，筛选起来方便灵活，成倍提高了工作效率和工作质量；另外，此宏只要稍加修改即可应用到其它的统计工作中，有兴趣的朋友不妨一试。
（上接第46页）
在保证最优基不变的前提下，bj允许增加或减少的量。本例中由所得数据可快速求出bj的变化范围：6≤b1≤14,b2≥12，10≤b3≤30。
此敏感性报告中还有一个很重要的信息―――第二栏的阴影价格，通常称为影子价格。影子价格是线性规划模型中某个约束的右端常数项增加（或减少）一个单位而导致的目标函数值的增量（或减量）。影子价格的大小客观地反映了资源在系统内的稀缺程度，影子价格越高，这种资源越稀缺，而影子价格为零的约束资源为富余资源。[1]图8的敏感性报告中，第三个约束设备C的影子价格是0.2，说明设备C每增加1个台时目标函数值会增加0.2个单位。影子价格同时也是一种机会成本，对资源的购买决策具有重要的参考价值，当资源的实际市场价格低于影子价格时，可以适当购进该种资源以增加收益，当该资源的市场价格高于影子价格时可以适当售出资源。企业可以根据影子价格对有限的资源进行合理的配置，自主节约使用某些稀缺资源，使有限资源发挥更大的经济效益。
解简便、快捷，表中数值可根据用户要求自行设置，除了在合理安排产品的生产决策可使用外，对于研究如何合理使用企业各财、物等现有资源进项经济资源，以及研究如何统筹安排，对人、行优化组合、实现最大效能等均可参照使用，能有效地提高组织决策的速度及准确性，而Excel办公软件的普遍性优点使之更适合于促进科学决策的信息化水平。[3]
[1]孔造杰.运筹学[M].机械工业出版社，]王文平.运筹学[M].科学出版社，2007.2.
[3]张纯义.Excel用于生产决策的线性规划法[J].会计之友，2005.10.
孙爱萍（1981~）女汉族，山东海阳人，讲师，研究方向：电子商务，经济管理优化。
王瑞梅（1981~）男汉族，湖南株洲人，讲师，研究方向：管理过程优化，人力资源管理。
通过上述步骤可看出，利用Excel进行线性规划模型的求
包含各类专业文献、文学作品欣赏、行业资料、幼儿教育、小学教育、高等教育、中学教育、如何利用EXCEL求解线性规划问题及其灵敏度分析[1]21等内容。　
　实验一 线性规划问题及灵敏度分析实验目的: 实验目的...1 2 3 4 s.t. 解: 应用 WinQSB 软件求解线性...(13) 将计算表格转换成 Excel 表格。 在计算结果... 　由于许多问题仅仅利用线性规划的方法还不足以解决,因此用到了对 偶理论,也因此...1 1. 线性规划及灵敏度分析简介??? 1 2. 线性规划模型应用的发展???1 3... 　第三章线性规划对偶理论与灵敏度分析习题一,思考题 思考题 1.对偶问题和对偶...5.利用对偶单纯形法计算时,如何判断原问题有最优解或无可行解? 6.在线性规划... 　计算、图表等, 实 验结果及分析) 基础实验 1.求解下述线性规划问题 min s.t...如何利用EXCEL求解线性规... 4页 免费 线性规划问题及灵敏度分... 9页 免费... 　基于线性规划的灵敏度分析问题的研究 摘要:本文主要研究的是线性规划的灵敏度分析问题。讨论线性规划价值系 数和资源系数中单个系数在什么区间变化时能保证最优解或... 　3 掌握线性规划问题的对偶理论和灵敏度分析。 二 实验内容 1 建立线性规划问题的数学模型。 2 利用 Excel“规划求解”功能对线性规划问题进行建模与求解。 3 根据... 　用excel规划求解并作灵敏度分析_文学_高等教育_教育专区。用excel规划求解并作灵敏度分析 题目如何利用 EXC E L 求解线性规划 问题及其灵敏度分析 第 姓名 乐俊松... 　在“可用加载宏”框中,选中“规划求解”旁边的复选框 实验二应用 EXCEL 求解线性规划问题及灵敏度分析一、实验目的与要求 1.能应用 EXCEL 规划求解工具来求解线性... 　线性规划的灵敏度分析研究的问题是:研究线性规划模型中 aij、bi、cj 等参数中的一个或几个发生变化时, 问题最优解会发生什么变化;研究这些参数在一个多大范围内...实验三、用Excel求解线性规划模型
线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。Excel最多可解200个变量、600个约束条件的问题。下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。
一、实验目的
1、 掌握如何建立线性规划模型。
2、 掌握用Excel求解线性规划模型的方法。
3、 掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变
化对最优方案产生的影响。
4、 读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。
二、 实验内容
1、 [工具][规划求解]命令
规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。
使用[规划求解]命令的一般步骤为：
第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。
图1“规划求解参数”对话框
第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1
第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。
第四步：在图5中单击“确定”按钮，则只将优化计算结果显示在表格设置中的可变单元格（决策变量）和目标单元格（目标函数）内。
在图5的“报告”框中有3个选项，每个选项对应着一个报告，各报告以单一工作表记载，它们不仅能给出优化结果，甚至还给出更重要信息，例如影子价格等。
2、 产品生产品种结构优化问题 数学模型 示例：一家制药厂生产两种产品：药品Ⅰ和药品Ⅱ。每个产品要用到一种相同的原料A，并要经过一道相同的工序，在机器B上包装 。因为这两种产品可以使用同样的机器，所以它们可以轮换使用设备，从而使其生产设施得到较充分的利用。
问该制药厂应该如何安排生产计划才能使企业的利润最大。
我们知道，如果分别设药品Ⅰ和药品Ⅱ的生产数量为x1和x2，那么该问题的线性规划模型如下：
maxZ?50x1?100x2
?2x1?x2?400(原料A)?
?x1?x2?300(机器B)?
s.t.?x2?250(原料C)
?x?0(药品I的最低产量)?1??x2?0(药品II的最低产量)
表格设置与公式说明
根据本问题的规模和条件，拟设置如表1中A1UE8所示形式：
⑴区域B3UC6和E3UE5为原始数据区，输入如表1中所示的原始数据。
⑵在单元格B8内输入数学模型中目标函数的计算公式，并求最大值。 ⑶单元格B7UC7分别作为药品Ⅰ和药品Ⅱ的产量（即决策变量x1、x2），即可变单元格。其初始值设为0，求解过程中计算机会自动输入各组试验值。
⑷区域D3UD5内的各单元格依次输入三个约束条件对应式的左侧部分。
第一步：选择[工具][规划求解]命令，弹出图1所示对话框。根据本问题的性质，在“设置目标单元格”文本框内填入$B$8,在“等于”选项后选取“最大值”，在“可变单元格”文本框内填入$B$7U$C$7。
第二步：单击“添加”按钮，弹出图2所示对话框。该步骤的任务是要把前面数学模型中的全部约束条件一个一个地填入图1所示的“约束”列表框内。图2所示就是填入三个资源约束条件的情形：在左边“单元格引用位置”文本框内填入$D$3U$D$5（可直接录入、或用鼠标拖入）、单击中间向下小箭头并选取符号“&=”、在右方“约束值”文本框内录入$E$3U$E$5，也可以录入数字400，300，250，最后单击“确定” 按钮或回车键，回到图1。这样就完成了约束条件$D$3U$D$5&=$E$3U$E$5的录入。
第三步：重复第二步，录入$B$7U$C$7&=0，即两决策变量的值必须大于0，最后如图1所示。
第四步：在图1中单击“选项”按钮，弹出图4对话框。因本例题属于线性规划问题，选取“采用线性模型”按钮，再单击“确定”按钮，回到图1。
第五步：在图1中选取“求解”按钮或击回车键，Excel进入规划求解运行过程，屏幕左下角状态条上逐次显示运行过程报告。一旦计算结束，弹出图5的对话框。在图5内可以有四种选择：
⑴若单击“确定”按钮或击回车键，则显示如表2的结果。可变单元格$B$7U$C$7内显示最优生产计划，即药品Ⅰ生产50件和药品Ⅱ生产100件，可获得最大利润27500元；单元格$D$3U$D$5分别给出了各种资源的用量，只有原料A有50千克的剩余。
⑵若选择“运算结果报告”，Excel显示“运算结果报告&n&”，其中&n&表示求解本问题中已经连续第几次选择该选项，我们这里给出的是“运算结果报告1”（如表6），即在同一文件内首次选择该选项。表6比较全面地报告了优化结果信息，包括目标单元格的位置、名称、初值和终值，可变单元格的位置、名称、初值和终值，约束单元格位置、名称、单元格内计算结果、单元格相应约束式、运算结果达到的状态（型数值为0表示到达限制值、否则未到达限制值）。
⑶若选择“敏感性报告”，Excel显示“敏感性报告&n&”（如表7）。敏感性报告表的限制式中“阴影价格”（经济学中称影子价格、Shadow Price）是一个有特别意义的经济指标。如何利用EXCEL求解线性规划问题及其灵敏度分析
Solution of Linear Programming Problem and Sensitivity Analysis by Excel
线性规划是运筹学的重要组成部分,在工业、军事、经济计划等领域有着广泛的应用,但其手工求解方法的计算步骤繁琐、复杂.本文以实际生产计划问题为例详细介绍了Excel软件中的"规划求解"功能辅助求解线性规划模型的具体步骤,并对其进行了灵敏度分析.
Sun Aiping
Wang Ruimei
江西理工大学,经济管理学院,赣州,341000
年，卷(期)
机标分类号
本文读者也读过
互动百科相关词
加载中，请稍候
．客服电话
&&8:00-11:30,13:00-17:00(工作日)