用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 软件的使用步骤
“规划求解”可以解决数学、财务、金融、经济、统计等诸多实
际问题，在此我们只举一个简单的应用实例，说明其具体的操作
方法。
某人有一笔资金可用于长期投资,可供选择的投资机会包括购买国库券、公司债券、投资房地产、购买股票或银行保值储蓄等。投资者希望投资组合的平均年限不超过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．
利用规划求解在EXCEL中解方程
利用规划求解在EXCEL中解方程 工具/原料：EXCEL 2007/2010(如果是EXCEL 2003，这些操作都是一样的，只是相对应的设置地方会不一样)，规划求解插件 步骤/方法 1首先我们来讲一下EXCEL里面内置的单变量求解。 2为了方便操作，一般我们会对单元格进行名称定义，点击公式——定义名称。 3我们设置了C3为变量x，那么在其它单元格上就可以直接输入带x的方程式了，并且EXCEL会自动调用此单元格内的数据。比如在B3内输入=x^3+27。 4点击数据——模拟分析——单变量求解。 5目标单元格为带有x变量的单元格，即要解方程的单元格。而目标值就设置为0了，其实我们在把方程变成f(x)=0的形式后可以节省很多设置时间。可变单元格，即为输出结果的单元格，这里我们设置成我们设置名称的单元格。然后点击确定即可计算出我们想要的结果。6有些朋友在问如果让输出结果随着我们方程的改变而自动进行计算呢？这里我们就要用到一个宏，首先我们进行录制宏，直接录制这个单变量求解的过程，不需要修改任何数据。点击视图——宏——录制宏，输入宏名(宏1)后直接进行单变量求解的过程录制。 7录制完成后，我们停止录制，再查看宏，对此宏进行编辑，此时EXCEL会打开宏编辑器。8双击你所在编辑的工作表，并输入如下代码： 9Private Sub Worksheet_Change(ByVal Target As Range) 宏1 End Sub 10 11保存后即可得到我们想要的结果了，随意更改公式就可以自动计算结果了。
12这样在EXCEL上完成解一元多次方程还是相当有效和好用的，不过它有一个缺点就是计算结果只是一个近似值，并且在方程中每两个数值或变量之间都必须用符号连接起来(如10*x是不能写成10x的形式的)。 13接下来就是利用规划求解插件进行多元方程组的解方程操作了，规划求解这个插件很好用，但似乎不能达成自动更改单元格之后自动计算的功能，当我们录制了宏之后，在宏中的代码都是红色的，表示错误的，因此我们先只来学习如何进行规划求解操作了，而不执行自动计算。 14将你下载好的规划求解插件解压到office相对应版本内的\Library文件夹里，并执行一下里面的SOLVER.XLA文件。同时打开EXCEL选项的加载项里面的——管理EXCEL加载项，并勾选规划求解。 15此时再打开EXCEL就会多出一个加载项，里面就有我们需要的规划求解插件了。 16点击规划求解，选择目标单元格为包含有所有变量的其中一个方程式，这里不能直接选择方程式，而需要选择等于此方程式的单元格，如图： 17 18可变单元格就是我们要计算结果的变量单元格，这里可以推测，而约束条件也就是约束计算结果或方程式的结果范围了，这里多用于不定式的求解，并且求解结果可选择为最大值，最小值，还是约定值，选项菜单可以设置一些计算精度或计算方式。 19利用此两个工具我们几乎可以求解出所有方程的近似解了，这样对于我们日后的学习和
lingo灵敏度分析实例
一个实例理解Lingo的灵敏性分析 线性规划问题的三个重要概念： 最优解就是反应取得最优值的决策变量所对应的向量。 最优基就是最优单纯形表的基本变量所对应的系数矩阵如果其行列式是非奇异的，则该系数矩阵为最优基。 最优值就是最优的目标函数值。 Lingo的灵敏性分析是研究当目标函数的系数和约束右端项在什么范围（此时假定其它系数不变）时，最优基保持不变。灵敏性分析给出的只是最优基保持不变的充分条件，而不一定是必要条件。下面是一道典型的例题。 一奶制品加工厂用牛奶生产A1,A2两种奶制品，1桶牛奶可以在甲车间用12小时加工成3公斤A1，或者在乙车间用8小时加工成4公斤A2。根据市场需求，生产的A1,A2全部能售出，且每公斤A1获利24元，每公斤A2获利16元。现在加工厂每天能得到50桶牛奶的供应，每天正式工人总的劳动时间480小时，并且甲车间每天至多能加工100公斤A1，乙车间的加工能力没有限制。试为该厂制订一个生产计划，使每天获利最大，并进一步讨论以下3个附加问题： 1）若用35元可以买到1桶牛奶，应否作这项投资？若投资，每天最多购买多少桶牛奶？2）若可以聘用临时工人以增加劳动时间，付给临时工人的工资最多是每小时几元？ 3）由于市场需求变化，每公斤A1的获利增加到30元，应否改变生产计划？ 模型代码： max=72*x1+64*x2; x1+x2<=50; 12*x1+8*x2<=480; 3*x1<=100; 运行求解结果： Objective value: 3360.000 Variable Value Reduced Cost X1 20.00000 0.000000 X2 30.00000 0.000000 Row Slack or Surplus Dual Price 1 3360.000 1.000000 2 0.000000 48.00000 3 0.000000 2.000000 4 40.00000 0.000000 这个线性规划的最优解为x1=20，x2=30，最优值为z=3360，即用20桶牛奶生产A1, 30桶牛奶生产A2，可获最大利润3360元。输出中除了告诉我们问题的最优解和最优值以外，还有许多对分析结果有用的信息。 其中，“Reduced Cost”列出最优单纯形表中判别数所在行的变量的系数，表示当变量有微小变动时, 目标函数的变化率。其中基变量的reduced cost值应为0，对于非基变量Xj, 相应的reduced cost值表示当某个变量Xj 增加一个单位时目标函数减少的量( max型问题)。本例中X1，X2均为基变量。 “Slack or Surplus”给出松驰变量的值，模型第一行表示目标函数，所以第二行对应第一个约束。3个约束条件的右端不妨看作3种“资源”：原料、劳动时间、车间甲的加工能力。输出中Slack or Surplus给出这3种资源在最优解下是否有剩余：原料、劳动时间的剩余均为
用excel解决整数规划问题
实验二Ｅｘcel解决整数规划问题 一、问题的提出 某公司拟用集装箱托运甲、乙两种货物,这两种货物每件的体积、重量、获得利润以及托运所受限制如下表所示: 二模型得出 分析：这个问题是一个整数规划问题, 故应该确定决策变量、目标函数及约束条件。 设X1,X2分别为甲乙两种货物托运的件数,显然,X1,X2是非负的整数,这是一个纯整数规划问题，根据问题的要求可知 对于货物总体积的托运限制最大不得超过1365立方英尺,故应有约束条件: 195 Ｘ１+２7３Ｘ２≦1365 对于货物总重量的托运限制为最大不得超过140千克,故应有约束条件为: 4X1+40Ｘ２≦１40 同时有：Xｉ≥0，ｉ=1,2 希望货物托运的配置,使得可获得利润最大,即求W=2Ｘ1+3X2 的最大值 由分析可得如下模型: ＭaxＷ=2X1+3X2 (所获利润最大）约束条件如下 195 X1+2７3 X２≦1365 4X1+40X2≦１40 X i≥0, ｉ＝1,2 Ｘ1≦4 三、模型求解 1．建立规划求解工作表（如下图所示） ⑴．在可变单元格（B４:C４)中输入初始值（１，1） ⑵．在上图有关单元格输入如下公式 单元格地址公式 Ｃ６ =B２*B4+Ｃ2*C４
Ｃ７ =B3＊B4+C3＊C４ C8 =B5*Ｂ4+C5*C4 ⑶.求最佳组合解： ①．选取[工具]→［规划求解…]出现如下对话窗： ②．在“设置目标单元格”窗口，输入C8。 ③．选定“最大值”选项。 ④．在可变单元格中输入B4：C4。 ⑤．选取“添加”，出现“添加约束”窗口,在“添加约束”窗口输入: 单元格引用位置运算符号约束值 B4：Ｃ4 int 单击“添加”,再输入以下约束条件: B4：C４ >= 0 单击“添加”,再输入以下约束条件： B４ >＝ 4 单击“添加”，再输入以下约束条件： C6 <= １365 单击“添加”,再输入以下约束条件： C7 ＜= １40,单击“确定” ⑥在“规划求解参数”窗口，选择“求解。” ⑦选择“确定”,（计算结果如下表所示） ⑧在“规划求解结果”对话框中选定保存“规划求解结果”，单击“确定”。 于是我们就得到如下运算结果报告 四、报告分析 表1 Microｓｏｆt Excel ９.0 运算结果报告 目标函数的初值：当变量X＝(１，1）时目标函数的值。 目标函数的终值：经过运算后的目标函数的最优值。 此表说明函数的最优值为14。 表2可变单元格式 从此表看出我们的最优解（终值）为(4，2）。 --
实验三灵敏度分析的应用
实验三灵敏度分析的应用 「、实验目的 (1) 掌握数学建模和用软件求解数学模型。 (2) 掌握在软件上分析问题和改进数学模型的方法。二、实验内容 1、(工作安排问题)人员在时段开始上班，连续工作8小时问该公交线路至少需要多少人。 问：要求在第5,6时段不能有多余人员上班，如何排班
在第i时段开始上班的人数为X i 。 模型: min Z 6 X i i 1 X6X160 ; X i X 70 ； X2X3 60 ; X3X4 50 ; X4X5 20 ； X5X6 30 ; X i0
min x1+x2+x3+x4+x5+x6 subject to x6+x1>60 x1+x2>70 x2+x3>60 x3+x4>50 x4+x5=20 x5+x6=30 end gin x1 gin x2 gin x3 gin x4 gin x5 gin x6 问：要求在第5,6时段不能有多余人员上班，如何排班。
保本点 盈亏平衡点又称零利润点、保本点、盈亏临界点、损益分歧点、收益转折点。通常是指全部销售收入等于全部成本时（销售收入线与总成本线的交点）的产量。 以盈亏平衡点的界限，当销售收入高于盈亏平衡点时企业盈利，反之，企业就亏损。盈亏平衡点可以用销售量来表示，即盈亏平衡点的销售量；也可以用销售额来表示，即盈亏平衡点的销售额。 单位售价-单位销售成本=单位毛利 可变成本=0时，保本点=每月固定成本/单位毛利（每月销售量）（不亏不赚） 可变成本0时， 估计的单位可变成本=每月可变成本/每月销售量 保本点=每月固定成本/ （单位毛利-估计的单位可变成本） 产品1销量50；每月固定成本=1000;计算保本点 产品利润贡献率的计算 对产品1的利润贡献率的计算：1，求解模型A的最优解X1，及最优解值Z1 2,增加约束X 0，得到模型B o 3，求解模型B的最优解X2，及最优解值Z24,设X1中分量X1的值为X*，贝V产品1的利润贡献率: Z1 Z2 * X 例如，(4280-3600) /20=34 2、(2)计算产品利润贡献率
应用excel规划求解实例
应用EXCEL规划求解工具进行优化1.线性规划—生产规划： 步骤一：建立模型：每天生产甲乙两种产品分别为X1和X2，数学模型为：目标函数：minf（X1，X2）=60*X1+120*X2 约束条件：9*X1+4*X2<=360 3*X1+4*X2<=300 4*X1+5*X2<=200 -X1<=0 -X2<=0 用EXCEL建立模型如下： 步骤二：规划求解参数确定： 步骤三：选项参数确定：
步骤四：求解： 由上面求解过程可知：X1=20，X2=24时，可使目标函数值最小，即f（X1，X2）=4080. 2.工程下料问题规划求解： 由题意可列出下列方案： 步骤一：设使用8种方案的次数分别为X1，X2，X3，X4，X5，X6，X7和X8，且均为正整数，建立数学模型如下： 目标函数：f（X）=（5*X1+10*X2+25*X3+5*X4+30*X5+10*X6+25*X7+5*X8）/（（X1+X2+X3+X4+X5+X6+X7+X8）*180） 约束条件：gX1=2*X1+X2+X3+X4=100 gX1=2*X2+X3 +3*X5+2*X6+X7 gX1=X1+X3+33*X4 +2*X6+3*X7+5*X8 用EXCEL建立模型如下：
步骤二：规划求解参数确定： 步骤三：选项参数确定： 步骤四：求解： 由上面求解过程可知：X1=23，X2=50，X3=0，X4=4，X5=0，X6=0，X7=0和X8=3时，可使目标函数值最小，即f（X）=0.045139. 3．规划求解—工时安排： 某厂生产A B C三种产品，净利润分别为90元，75元，50元；使用的机时数分别为3h，手工时数分别为4h，3h，2h，由于数量和品种受到制约，机工最多为400h，手工为280h，数量最多不能超过50件，C至少要生产32件。求：如何安排A B C的数量以获得最大利润？
使用Excel规划求解解 线性规划问题
使用Excel规划求解解线性规划问题 引言 最近，开始学习运筹学，期望通过学习后能够解决许多困扰自已的难题。 刚开始时，选了很多教材，最后以Hamdy A.Taha著的《Operations Research:An Introduction》开始学习。（该书已由人民邮电出版社出版，书名《运筹学导论-初级篇（第8版）》，不知为什么，下载链接中只有该书配套的部分习题解答，而书中所说的光盘文件找不到下载的地方，因为中译本没有配光盘，因此也就错过了许多示例文件。不知道哪位有配套光盘文件，可否共享？？？） 线性规划求解的基本知识 线性规划模型由3个基本部分组成： ?决策变量（variable） ?目标函数（objective） ?约束条件（constraint） 示例：营养配方问题 （问题）某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成，含有以下成份： 特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤维。该农场希望确定每天最小成本的饲料配制。 （解答过程） 因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为： x1=每天混合饲料中玉米的重量（磅） x2=每天混合饲料中大豆粉的重量（磅） 目标函数是使配制这种饲料的每天总成本最小，因此表示为： min z=0.3×x1+0.9×x2 模型的约束条件是饲料的日需求量和对营养成份的需求量，具体表示为： x1+x2≥800 0.09×1+0.6×2≥0.3(x1+x2) 0.02×1+0.06×2≤0.05(x1+x2) 将上述不等式化简后，完整的模型为：
min z=0.3×1+0.9×2 s.t.x1+x2≥800 0.21×1-0.3×2≤0 0.03×1-0.01×2≥0 x1,x2≥0 可以使用图解法确定最优解。下面，我们介绍使用Excel的规划求解加载项求解该模型。使用Excel规划求解解线性规划问题 步骤1安装Excel规划求解加载项 单击“Office按钮——Excel选项——加载项——（Excel加载项）转到”，出现“加载宏”对话框，如下图所示。选择“规划求解加载项”，单击“确定”。 此时，在“数据”选项卡中出现带有“规划求解”按钮的“分析”组，如下图所示。 步骤2设计电子表格 使用Excel求解线性规划问题时，电子表格是输入和输出的载体，因此设计良好的电子表格，更加易于阅读。本例的电子表格设计如下图所示：
Excel规划求解
□财会月刊· 全国优秀经济期刊□·110·2014.8下 在传统财务运营管理中，营运决策包括确定最佳现金持有量、最优订货批量，或者只是考虑单个市场的生产与销售决策。企业集团全球运营管理涉及生产、运输、销售等环节，需要在实现集团利润最大化的同时，解决生产什么产品、在哪里生产、生产多少、运到哪个市场等诸多问题。显然，采用传统的运营管理方法会比较棘手。而Ex?cel 提供的规划求解工具，不但能非常迅速地求出多种营运决策模型的最优解，还可以给出敏感性分析报告，满足财务全球化运营管理的需求，有效提高公司决策效率，同时也能促进财务人员更多地参与到公司管理决策中。 一、问题描述 某跨国集团在中国和其他地区设立了四个工厂，分别为A 、B 、C 、D 厂，产品主要面向国际市场销售，分别销往北京、香港、纽约、东京四个城市。各个工厂的单位产品成本、固定成本、产能，各个市场的销售价格和需求量，以及各个工厂到每个市场的运输成本见图1。 在每个工厂产能允许同时最大限度满足市场需求的情况下，集团管理层希望财务部给出能够实现集团利润 最大化目标的年生产和运输预算的决策方案。 二、建立线性数学模型 1.定义决策变量。下文中，i （i=1，2，3，4）表示工厂，j 表示市场（j=1，2，3，4）；决策问题可以用图2表示。所以定义决策变量为X ij ：即在i 工厂生产的产品投放到j 市场。 2.确定目标函数。最大利润=收入-产品变动成本-其他成本最大利润=55500（X 11+X 21+X 31+X 41）+61100（X 12+X 22+X 32+X 42）+57800（X 13+X 23+X 33+X 43）+62650（X 14+X 24+X 34+X 44）-34900（X 11+X 12+X 13+X 14）-32200（X 21+X 22+X 23+X 24）-38350（X 31+X 32+X 33+X 34）-23400（X 41+X 42+X 43+X 44）-（500X 11+12225X 12+9075X 13+21450X 14+4500X 21+……+15150X 43+5925X 44）。 3.列出约束条件。 （1）产能约束：X 11+X 12+X 13+X 14≤101；X 21+X 22+X 23+X 24≤201；X 31+X 32+X 33+X 34≤121；X 41+X 42+X 43+X 44≤250。 （2）需求约束：X 11+X 21+X 31+X 41≤150；X 12+X 22+X 32+X 42≤75；X 13+X 23+X 33+X 43≤200；X 14+X 24+X 34+X 44≤100。 （3）非负约束：X ij ≥0。4.最优解：最大利润时的X ij 。 三、数据及公式准备 1.数据输入：把图1集团公司的决策数据输入新建的Excel 表中，如图3所示。 耿海利 （江西财经大学会计学院南昌330013） 【摘要】随着全球经济一体化的深入，企业运营管理方式发生了很大变化。本文通过一个实例，来探讨企业集团拥有多个生产子公司、多个产品市场并且各个产品市场价格不同的情况下，企业如何使用Excel 规划求解工具进行产品生产、运输和分配决策，以实现集团利润最大化。 【关键词】规划求解 企业集团全球运营决策敏感性分析 Excel 规划求解： 企业全球运营管理工具 图1 集团基本运营决策数据 图2决策问题
LINGO软件灵敏度分析灵敏度分析实验报告
. . . .. . . . 2011——2012学年第二学期 合肥学院数理系 实验报告 课程名称：运筹学 实验项目：线性规划的灵敏度分析 实验类别：综合性□设计性□验证性□√ 专业班级： 09级数学与应用数学（1）班 姓名：王秀秀学号： 0907021006 实验地点： 9#503 实验时间： 2012-4-25 指导教师：管梅成绩：
一.实验目的 熟悉LINDO软件的灵敏度分析功能； 二.实验内容 1、求解线性规划 。 12 12 12 12 max z x2x 2x5x12 s.t.x2x8 x,x0 =+ +≥ ? ? +≤ ? ?≥ ? 并对价值系数、右端常量进行灵敏度分析 2、已知某工厂计划生产I，II，III三种产品，各产品需要在A、B、C设备上加工，有关数据如下： 试问答: (1)如何发挥生产能力，使生产盈利最大？ (2)若为了增加产量，可租用别工厂设备B，每月可租用60台时，租金1.8万元，租用B设备是否合算？
(3)若另有二种新产品IV 、V ，其中新产品IV 需用设备A 为12台时、B 为5台时、C 为10台时，单位产品盈利2.1千元；新产品V 需用设备A 为4台时、B 为4台时、C 为12台时，单位产品盈利1.87千元。如A 、B 、C 的设备台时不增加，这两种新产品投产在经济上是否划算？ (4)对产品工艺重新进行设计，改进结构。改进后生产每件产品I 需用设备A 为9台时、设备B 为12台时、设备C 为4台时，单位产品盈利4.5千元，这时对原计划有何影响？ 三. 模型建立 1、数学模型为 12121212 max z x 2x 2x 5x 12 s.t.x 2x 8x ,x 0=++≥?? +≤??≥? 2、设分别生产I ，II ，III 三种产品1x ，2x ，3x 件， （1）数学模型为： 123122123123123 123max z 3x 2x 2.9x 8x 2x 10x 30010x 5x 8x 400s.t.2x 13x 10x 420x x x 0 x ,x x =++++≤?? ++≤?? ++≤??≥???，，，，为整数 （2）数学模型为： 123122123123123123max z 3x 2x 2.9x 188x 2x 10x 30010x 5x 8x 460s.t.2x 13x 10x 420x x x 0x ,x x =++-++≤?? ++≤?? ++≤??≥???，，，，为整数
EXCEL规划求解题
1、生产问题 某工厂生产I，II两种食品，现有80名熟练工人，己知一名熟练工人每小时可生产10千克食品I或8千克食品II。据合同预订，该两种食品每周的需求量将急剧上升，见下表。为此该厂决定到第8周末需培训出60名新的工人，两班生产。已知一名工人每周工作40小时，一名熟练工人用两周时间可培训出不多于三名新工人(培训期间熟练工人和培训人员均不参加生产)。熟练工人每周工资320元，新工人培训期间工资每周180元，培训结束参加工作后工资每周260元，生产效率同熟练工人。在培训的过渡期间，很多熟练工人愿加班工作，工厂决定安排部分工人每周工作80小时，工资每周480元。又若预订的食品不能按期交货，每推迟交货一周的赔偿费食品I为0.4元，食品II 为0.8元。在上述各种条件下，试建立该问题的线性规划模型，以便作出合理全面的安排，使各项费用的总和为最小。 建立该问题的电子表格模型，填写下列电子表格。
2、项目选择问题 某个制药公司需要开发四个新的研究项目，为了使所有项目的成功性最高，派了六位科学家来对这四个项目进行投标选择。每位科学家具有1000点可以投标，投标点数越大，表示科学家对该项目越感兴趣，成功的可能性就越高。投标具体情况如下表所 没有该领域的知识或其他原因而不能从事该项目的研究与开发。目标是使投标总点数最高，应该如何指派。建立该问题的电子表格模型，填写下列电子表格。
某物流公司希望以最小的成本完成一种物资的配送，其运出货物数量、分配量和各段线路单位运输成本如下表所示。另外，由于运输能力限制，从各个工厂到配送中心，以及由配送中心到各个仓库运输产品的数量均不超过60。 建立该问题的电子表格模型，填写下列电子表格。
EXCEL规划求解功能操作说明
E X C E L规划求解功能操 作说明 This model paper was revised by the Standardization Office on December 10, 2020
Excel规划求解功能操作说明以Microsoft Excel2003为例，说明使用Excel的求解线性规划问题功能的使用方法。 一、加载规划求解功能 1.点击【工具】按钮，在下拉菜单中选择【加载宏】功能。 2.在弹出的【可加载宏】选项卡中勾选【规划求解】，点击确定按钮。 此时，【工具】下拉菜单中增加规划求解功能，表示加载成功。 二、构造表格Excel表格并填入各项数据 以教材18页【例题2-8】为例，构造表格如下： 1.录入约束条件系数 约束条件（1）为5x 1+x 2 -x 3 +x 4 =3，则在约束系数的第一行的x 1 ,x 2 ,x 3 ,x 4 ,x 5 ，限制条 件，常数b列下分别录入5,1，-1,1,0，=，3如下图所示。 约束系数区的第二行录入约束条件（2）的系数、限制符号及常数b，即- 10,6,2,0,1，=，2； 约束系数区的第三行录入约束条件（3）（x1≥0）的系数、限制符号及常数b，即1,0,0,0,0，≥，0； 约束系数区的第四行录入约束条件（4）（x2≥0）的系数、限制符号及常数b，即0,1,0,0,0，≥，0；
约束系数区的第五行录入约束条件（5）（x3≥0）的系数、限制符号及常数b，即0,0,1,0,0，≥，0； 约束系数区的第六行录入约束条件（6）（x4≥0）的系数、限制符号及常数b，即0,0,0,1,0，≥，0； 约束系数区的第七行录入约束条件（7）（x5≥0）的系数、限制符号及常数b，即0,0,0,0,1，≥，0。如下图所示。 2.录入目标函数系数 目标函数为maxZ=4x 1-2x 2 -x 3 ，则在目标函数的x 1 ,x 2 ,x 3 ,x 4 ,x 5 列下分别录入4，-2，- 1,0,0，如下图所示。 3. 录入约束条件的计算公式 双击约束条件（1）行的“总和”单元格，录入以下内容： “=B3*B12+C3*C12+D3*D12+E3*E12+F3*F12” 说明：录入的内容即是约束条件（1）的计算公式，其中“B3*B12”代表5x 1 ; “C3*C12”代表1x 2；“D3*D12”代表-1x 3 ；“E3*E12”代表1x 4 ；“F3*F12”代表0x 5 。 整个计算公式即代表5x 1+1x 2 -1x 3 +1x 4 +0x 5 ，即约束条件（1）的计算公式。注意：单元格 B12，C12，D12，E12，F12分别代表x 1，x 2 ，x 3 ，x 4 ，x 5
灵敏度分析5种实例
Max 123234z x x x =++ S.t 123412351523234,,0x x x x x x x x x x +++=?? -+-=??≥? 基变量x1=2，x2=3；非基变量x3=x4= x5=0； 由约束条件得基变量用非基变量表示为71112 1345 2121 23455555x x x x x x x x =--+??=+--? 目标函数中基变量用非基变量代入后981 345 14z x x x =---。 （1）当目标函数中系数i c 变化时（只要考虑最优性条件）： 设目标函数变为Max 123'34z cx x x =++ 目标函数中基变量用非基变量代入672361111234555555555()()()z c c x c x c x =+---+-- 所以如果72355c -，6155c +，1 2 55c -0≥，则符合最优解判别条件，所以目标函数最优性不变611'z c =+，由723c -，6155c +，1 2 55c -0≥解得最优性不变的c 的范围。 否则，即如果超出该范围，则重新用单纯形法求解。 （2）当约束条件右边常数i b 变化时（先考虑可行性条件看最优基是否变化，再考虑）： 设约束条件变为12341235152234,,0x x x x b x x x x x x +++=?? -+-=??≥? 先假设基没有变，所以令非基变量x3=x4= x5=0代入约束条件解得为8 15 8 2 24b b x x ++=??=-? 根据可行性条件，必须12,0x x ≥，解得b 的范围，即在此范围内最优基不变（最优解可能变化，要另外去求）。 否则，即如果超出该范围，则重新用单纯形法求解。 （3）当约束条件中价值系数ij a 变化时（先看可行性条件看最优基是否变化，再考虑最优值）： 设约束条件变为11123412351523 234,,0a x x x x x x x x x x +++=?? -+-=??≥?
利用excel软件求解线性规划问题
下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。 例1 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束： ● 产品组合使用的资源不能超标。 ● 对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。 下面，我们来考虑让某医药公司的最优产品组合问题。该公司有六种可以生产的药品，相关数据如下表所示。 设该公司生产药品1～6的产量分别为126,,,x x x （磅），则最优产品组合的线性规划模型为 123456 123456123456123456max 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表示的列向量的内积。这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。 最后，打开规划求解参数设定对话框设定模型 （1）（2）目标函数和可边单元的设定很简单，在此就不再赘述 （3）约束条件的设定 (3.1) 约束条件1234561234566543 2.5 1.545003.2 2.6 1.50.80.70.31600x x x x x x x x x x x x +++++≤??+++++≤? 的设定： 系数矩阵 目标函数的系数 系数矩阵右端常数 可变单元 约束函数单元 目标函数单元
数学建模五步法与灵敏度分析
灵敏度分析 简介： 研究与分析一个系统（或模型）的状态或输出变化对系统参数或周围条件变化的敏感程度的方法。在最优化方法中经常利用灵敏度分析来研究原始数据不准确或发生变化时最优解的稳定性。通过灵敏度分析还可以决定哪些参数对系统或模型有较大的影响。因此，灵敏度分析几乎在所有的运筹学方法中以及在对各种方案进行评价时都是很重要的。 用途： 主要用于模型检验和推广。简单来说就是改变模型原有的假设条件之后，所得到的结果会发生多大的变化。 举例（建模五步法）： 一头猪重200磅，每天增重5磅，饲养每天需花费45美分。猪的市场价格为每磅65美分，但每天下降1美分，求出售猪的最佳时间。 建立数学模型的五个步骤： 1.提出问题 2.选择建模方法 3.推到模型的数学表达式 4.求解模型 5.回答问题 第一步：提出问题 将问题用数学语言表达。例子中包含以下变量：猪的重量w（磅），从现在到出售猪期间经历的时间t（天），t天内饲养猪的花费C（美元），猪的市场价格p（美元/磅），出售生猪所获得的收益R（美元），我们最终要获得的净收益P（美元）。还有一些其他量，如猪的初始重量200磅。 （建议先写显而易见的部分） 猪从200磅按每天5磅增加 （w磅）=（200磅）+（5磅/天）*（t天） 饲养每天花费45美分 （C美元）=（0.45美元/天）*（t天） 价格65美分按每天1美分下降 （p美元/磅）=（0.65美元/磅）-（0.01美元/磅）*（t天） 生猪收益 （R美元）=（p美元/磅）*（w磅） 净利润 （P美元）=（R美元）-（C美元） 用数学语言总结和表达如下： 参数设定： t=时间（天）
w=猪的重量（磅） p=猪的价格（美元/磅） C=饲养t天的花费（美元） R=出售猪的收益（美元） P=净收益（美元） 假设： w=200+5t C=0.45t p=0.65-0.01t R=p*w P=R-C t>=0 目标：求P的最大值 第二步：选择建模方法 本例采用单变量最优化问题或极大—极小化问题 第三步：推导模型的数学表达式子 P=R-C （1） R=p*w （2） C=0.45t （3） 得到R=p*w-0.45t p=0.65-0.01t （4） w=200+5t （5） 得到P=（0.65-0.01t)(200+5t)-0.45t 令y=P是需最大化的目标变量，x=t是自变量，现在我们将问题转化为集合S={x:x>=0}上求函数的最大值： y=f（x）=（0.65-0.01x)(200+5x)-0.45x （1-1） 第四步：求解模型 用第二步中确定的数学方法解出步骤三。例子中，要求（1-1）式中定义的y=f （x）在区间x>=0上求最大值。下图给出了（1-1）的图像和导数（应用几何画板绘制）。在x=8为全局极大值点，此时f（8）=133.20。因此（8，133.20）为f在整个实轴上的全局极大值点，同时也是区间x>=0上的最大值点。 第五步：回答问题 根据第四步，8天后出售生猪的净收益最大，可以获得净收益133.20美元。只要第一步中的假设成立，这一结果正确。
lingo灵敏度分析实例
一个实例理解Lingo 的灵敏性分析 线性规划问题的三个重要概念： 最优解就是反应取得最优值的决策变量所对应的向量。最优基就是最优单纯形表的基本变量所对应的系数矩阵如果其行列式是非奇异的，则该系数矩阵为最优基。 最优值就是最优的目标函数值。 Lingo 的灵敏性分析是研究当目标函数的系数和约束右端项在什么范围（此时假定其它系数不变）时，最优基保持不变。灵敏性分析给出的只是最优基保持不变的充分条件，而不一定是必要条件。下面是一道典型的例题。 一奶制品加工厂用牛奶生产A1,A2 两种奶制品，1 桶牛奶可以在甲车间用12小时加工成3 公斤A1，或者在乙车间用8小时加工成4公斤A2。根据市场需求，生产的A1,A2全部能售出，且每公斤A1 获利24 元，每公斤A2 获利16 元。现在加工厂每天能得到50 桶牛奶的供应，每天正式工人总的劳动时间480小时，并且甲车间每天至多能加工100公斤A1，乙 车间的加工能力没有限制。试为该厂制订一个生产计划，使每天获利最大，并进一步讨论以下3 个附加问题： 1 ）若用35 元可以买到1 桶牛奶，应否作这项投资？若投资，每天最多购买多少桶牛奶？ 2）若可以聘用临时工人以增加劳动时间，付给临时工人的工资最多是每小时几元？ 3）由于市场需求变化，每公斤A1 的获利增加到30 元，应否改变生产计划？模型代码： max=72*x1+64*x2; x1+x2<=50; 12*x1+8*x2<=480; 3*x1<=100; 运行求解结果： Objective value: 3360.000 Variable Value Reduced Cost X120.000000.000000 X230.000000.000000 Row Slack or Surplus Dual Price 13360.000 1.000000 0.00000048.00000 2 30.000000 2.000000 440.000000.000000 这个线性规划的最优解为x1=20，x2=30,最优值为z=3360，即用20桶牛奶生产A1, 30 桶牛奶生产A2 ，可获最大利润3360 元。输出中除了告诉我们问题的最优解和最优值以外，还有许多对分析结果有用的信息。 其中，“ Reduced Cost列'出最优单纯形表中判别数所在行的变量的系数，表示当变量有微 小变动时，目标函数的变化率。其中基变量的reduced cost值应为0,对于非基变量Xj,相应的reduced cost值表示当某个变量Xj增加一个单位时目标函数减少的量（max型问题）。本例中X1 , X2 均为基变量。 “ Slack or Surplus给出松驰变量的值，模型第一行表示目标函数，所以第二行对应第一个约束。3个约束条件的右端不妨看作3种“资源”：原料、劳动时间、车间甲的加工能力。输出中Slack or Surplus 给出这3 种资源在最优解下是否有剩余：原料、劳动时间的剩余均为零，车间甲尚余40（公斤）加工能力。 “DUAL PRICE”（对偶价格）表示当对应约束有微小变动时，目标函数的变化率。输出结 果中对应于每一个约束有一个对偶价格。若其数值为p，表示对应约束中不等式右端项若 增加1个单位，目标函数将增加p个单位（max型问题）。显然，如果在最优解处约束正好取等号（也就是“紧约束”，也称为有效约束或起作用约束），对偶价格值才可能不是0。上 例中，第一、二个约束是紧约束”。当“x1+x2<=50'改为“x1+x2<=51"时，目标函数的值为
EXCEL规划求解功能操作说明
Excel规划求解功能操作说明 以Microsoft Excel2003为例，说明使用Excel的求解线性规划问题功能的使用方法。 一、加载规划求解功能 1.点击【工具】按钮，在下拉菜单中选择【加载宏】功能。 2.在弹出的【可加载宏】选项卡中勾选【规划求解】，点击确定按钮。
此时，【工具】下拉菜单中增加规划求解功能，表示加载成功。 二、构造表格Excel表格并填入各项数据
以教材18页【例题2-8】为例，构造表格如下： 标题栏 约束条件区 目标函数区 计算结果显示区 1.录入约束条件系数 约束条件（1）为5x1+x2-x3+x4=3，则在约束系数的第一行的x1,x2,x3,x4,x5， 限制条件，常数b列下分别录入5,1，-1,1,0，=，3如下图所示。 约束系数区的第二行录入约束条件（2）的系数、限制符号及常数b，即 -10,6,2,0,1，=，2； 约束系数区的第三行录入约束条件（3）（x1≥0）的系数、限制符号及常数
b，即1,0,0,0,0，≥，0； 约束系数区的第四行录入约束条件（4）（x2≥0）的系数、限制符号及常数b，即0,1,0,0,0，≥，0； 约束系数区的第五行录入约束条件（5）（x3≥0）的系数、限制符号及常数b，即0,0,1,0,0，≥，0； 约束系数区的第六行录入约束条件（6）（x4≥0）的系数、限制符号及常数b，即0,0,0,1,0，≥，0； 约束系数区的第七行录入约束条件（7）（x5≥0）的系数、限制符号及常数b，即0,0,0,0,1，≥，0。如下图所示。 2.录入目标函数系数 目标函数为maxZ=4x1-2x2-x3，则在目标函数的x1,x2,x3,x4,x5列下分别录入4，-2，-1,0,0，如下图所示。
LINGO软件灵敏度分析灵敏度分析实验报告
2011——2012学年第二学期 合肥学院数理系 实验报告 课程名称：运筹学 实验项目：线性规划的灵敏度分析 实验类别：综合性□设计性□验证性□√ 专业班级： 09级数学与应用数学（1）班 姓名：王秀秀学号： 0907021006 实验地点： 9#503 实验时间： 2012-4-25 指导教师：管梅成绩：
一.实验目的 熟悉LINDO软件的灵敏度分析功能； 二.实验内容 1、求解线性规划 。 12 12 12 12 max z x2x 2x5x12 s.t.x2x8 x,x0 =+ +≥ ? ? +≤ ? ?≥ ? 并对价值系数、右端常量进行灵敏度分析 2、已知某工厂计划生产I，II，III三种产品，各产品需要在A、B、C设备上加工，有关数据如下： 试问答: (1)如何发挥生产能力，使生产盈利最大？ (2)若为了增加产量，可租用别工厂设备B，每月可租用60台时，租金1.8万元，租用B设备是否合算？ (3)若另有二种新产品IV、V，其中新产品IV需用设备A为12台时、B为5台时、C为10台时，单位产品盈利2.1千元；新产品V需用设
备A 为4台时、B 为4台时、C 为12台时，单位产品盈利1.87千元。如A 、B 、C 的设备台时不增加，这两种新产品投产在经济上是否划算？ (4)对产品工艺重新进行设计，改进结构。改进后生产每件产品I 需用设备A 为9台时、设备B 为12台时、设备C 为4台时，单位产品盈利4.5千元，这时对原计划有何影响？ 三. 模型建立 1、数学模型为 12121212 max z x 2x 2x 5x 12 s.t.x 2x 8x ,x 0=++≥?? +≤??≥? 2、设分别生产I ，II ，III 三种产品1x ，2x ，3x 件， （1）数学模型为： 123122123123123 123max z 3x 2x 2.9x 8x 2x 10x 30010x 5x 8x 400s.t.2x 13x 10x 420x x x 0 x ,x x =++++≤?? ++≤?? ++≤??≥???，，，，为整数 （2）数学模型为： 123122123123123123max z 3x 2x 2.9x 188x 2x 10x 30010x 5x 8x 460s.t.2x 13x 10x 420x x x 0x ,x x =++-++≤?? ++≤?? ++≤??≥???，，，，为整数 （3）设分别生产I ，II ，III 、IV 、V 的件数为1x ，2x ，3x ，4x ，5x 数学模型为：
Excel规划求解工具在多目标规划中的应用
Excel规划求解工具在多目标规划中的应用 摘要：多目标决策方法是从20世纪70年代中期发展起来的一种决策分析方法。该方法已广泛应用于人口、环境、教育、能源、交通、经济管理等多个领域。文章采用多目标决策方法中分层序列法的思想，应用excel的规划求解工具，对多目标规划问题进行应用研究，并以实例加以说明。 abstract： multi-objective decision method is a kind of decision analysis method from the mid 1970s. the method has been widely used in population， environment， education，energy， traffic， economic management， and other fields. this paper uses the lexicographic method of multi-objective decision method and makes some researches on the multi-objective problem using the excel solver tool and an example to illustrate. 关键词： excel规划求解；多目标规划；分层序列法 key words： excel solver；multi-objective programming；the lexicographic method 中图分类号：tp31 文献标识码：a 文章编号：1006-4311（2013）21-0204-02 0 引言 excel中的规划求解工具只能对单目标的问题进行求解。当遇到多目标问题时，可以把多目标问题先转化为单目标问题，然后求解。