使用Excel求解线性规划问题
线性规划实验-Excel求解
用Excel求解线性规划问题实验（实验题目在最后）一、Excel函数使用Excel求解线性规划问题时，SUMPRODUCT函数可以大大降低资料录入工作量，提高工作效率。
计算数组或向量的乘积时，使用SUMPRODUCT 函数，格式如下：SUMPRODUCT(数组1,数组2,…,数组n)其中2≤n≤30，即最多可以使用30个数组参数，返回值为n个数组对应元素乘积之和。
以图1为例，在单元格D1中输入公式=SUMPRODUCT(A1:B1,A2:B2,A3:B3)得到111（相当于A1*A2*A3 + B1*B2*B3 = 1*2*3 + 3*5*7 = 111）。
在单元格D2中输入公式=SUMPRODUCT(A1:C1,A2:C2)得到53(相当于A1*A2 + B1*B2 + C1*C2 = 1*2 + 3*5 + 4*9 = 53)。
图1. 乘积和(SUMPRODUCT函数)计算结果11二、求解实例1. 问题描述与模型建立某玩具厂生产猫和龟两种玩具，制造一个玩具猫可获利30元，制造一个玩具龟可获利20元。
制造一个猫需要2小时机工和1小时手工；制造一个龟需要1小时机工和1小时手工。
在一周内，机工不能超过100h ，手工不能超过80h ，猫的产量不能超过45个。
求产品的最佳生产量和最大利润。
设1x 为一周内猫的生产量，2x 为一周内龟的生产量。
可建立如下线性规划模型：⎪⎩⎪⎨⎧≤≤+≤++=458010022030max 1212121x x x x x x x g2. 数据录入（1）启动Excel ，建立如图2所示的Excel 工作表，输入系数矩阵A 到区域C2：D4；输入约束常数b 到区域F2：F4；输入目标系数到区域C5：D5。
（2）指定单元格C6和D6存储变量1x 和2x 的值，称之为可变单元格。
在可变单元格中输入数字1表示给定初始值121==x x ，但并非一定这样；若这两个单元格不输入内容，Excel 将按0处理，不影响求解。
用EXCEL求解线性规划
用EXCEL求解线性规划
要用EXCEL求解线性规划问题，需要遵循以下步骤：
1. 给定问题中的约束条件和目标函数。
2. 打开EXCEL，建立一个新的工作表。
3. 在工作表中输入问题的约束条件和目标函数。
在输入目标函数时，需要将所有项移动到等号左侧，使它成为一个线性方程。
需要注意将不等式约束条件转化为等式约束条件，可以通过添加松弛变量来实现。
4. 使用EXCEL的“规划”工具，在工具栏中点击“数据”-“分
析”-“规划器”，打开“规划器”。
5. 在“规划器”中，选择需要优化的目标单元格，在“约束条件”
中输入所有约束条件所在的单元格，设置变量单元格的范围。
6. 可以在“选项”中添加其他约束条件。
例如，可以设定变量的整数或二元特性等。
7. 单击“求解”按钮，EXCEL将自动求解最优解，并输出最优
值和变量值。
需要注意的是，线性规划问题求解的结果是一个数值，而不是图形。
因此，需要谨慎分析问题以确保从数值结果中得到了正确的结论。
应用excel软件求解线性规划问题
3.设置可变单元格
4.约束的输入
Excel求解步骤-3
5.设置规划求解选项
Excel求解步骤-4
6. 运行规划求解
Excel结果分析-1
1. 将xij四舍五入为整数
Excel结果分析-2
2. 整数规划
按照与前面相同的步骤输入规划求解模型 增加整数约束 设置规划求解选项
例题
某公司生产两种型号的汽油，其性能指 标和销售价格见表1所示。该公司可供生 产汽油的原料性能指标和库存量见表2。 生产的汽油可在一周内成功售出，没有 用完的原料可以作为燃料油以每桶8美元 的价格出售。若汽油产品的蒸汽压力和 辛烷值可根据其调和组分的相应性质加 权平均计算，请给出使得该公司的销售 收入最大化的最佳生产方案。
油的销售单价（$/桶）
物料平衡约束
使用变量xij代表第i种原料用于生产第j种产品 的数量（桶）
i=1, 2, 3分别代表催化裂化汽油、异戊烷和直馏汽油 j=1, 2, 3分别代表80#、100#汽油和燃料油
物料平衡约束 q1 x11 x21 x31
q2 x12 x22 x32 q3 x13 x23 x33 x11 x12 x13 2500 x21 x22 x23 1200 x31 x32 x33 4000
应用软件求解线性规划问题
1.1 Excel的规划求解工具
Excel软件提供了求解一般规模数学规划 问题的“规划求解”工具
该工具具有界面友好、操作简单、与Excel 无缝集成等优点
可用于化学化工常见中、小规模线性规划、 非线性规划、整数规划问题的求解
Excel提供的规划求解工具对模型规模有一定限制：求解 模型的决策变量数不超过200个。当“规划求解选项”对 话框中的“采用线性模型”复选框处于选中状态时，对约 束条件的数量没有限制；而对于非线性问题，每个可变单 元格除了变量的范围和整数限制外，还可以有最多达100 个约束条件
用Excel求解运筹学问题
可变单元格 单元格 名字 $C$12 Units Produced Doors $D$12 Units Produced Windows 约束 单元格 名字 $E$7 Plant 1 Used $E$8 Plant 2 Used $E$9 Plant 3 Used 终 阴影 约束 允许的 允许的 值 价格 限制值 增量 减量 2 0 4 1E+30 2 12 150 12 6 6 18 100 18 6 6 终 递减 目标式 允许的 允许的 值 成本 系数 增量 减量 2 0 300 450 300 6 0 500 1E+30 300
C D Optimal Units Produced 16 17 Doors Windows 18 =DoorsProduced =WindowsProduced
E Total Prof it =TotalProf it
(1) 只有一个目标函数系数变动的影响
门的单位利润从$100变到$1000，产品组合的变化
５．Under the Tools menu, choose the "Add-Ins" command.
６．Click the Solver Table checkbox to have Solver Table load with Excel every time it is loaded.
excel线性规划求解
To Calculate Total LHS, 選擇SUMPRODUCT
1 2
選定第一列範圍
選定第二列範圍
SUMPRODUCT(F6:G6,G3:G3)=F6*F3 + G6*G3
Then repeat the same steps for constraint #2 and #3
輸入Slack 公式
求最小值
1.輸入變數x1, x2的值所在的儲存格 2.『新增』限制式
1.輸入限制式左邊及右邊的儲存格 2.選擇適當的符號
左邊
右邊
1.選擇後之結果 2.按『新增』
1.此限制式表示 constraint #1and #2 2.再新增 constraint #3
Constraint #3的左邊，右邊及符號
1.前三項限制式(constraints#1,#2,#3) 2.選擇『選項』
新增 constraint #4: x1 >=0 constraint #5: x2 >=0
按『求解』後的結果
想將此圖轉貼於 EXCEL工作表上 1.選此按鈕 2.回到EXCEL
1.按貼上的按鈕或 『編輯』下的貼上， 即顯示圖案。 2. 將之移至適當位置
完成
线性规划求解
Linear Programming Problem
輸入公式Βιβλιοθήκη 列出EXCEL的求解方式: Min:Minimum Objective Value=F4*F3+G4*G3 LHS: Left Hand Side RHS: Right Hand Side Slack: RHS - LHS for “<=“ LHS- RHS for “>=“
EXCEL求解线性规划问题
约束右端值降低15时，目旳函数值旳变化量。
解：（1）最优解为x1=0, x2=12.4, x3=9.5
（2） x1旳目旳系数降低5，占允许降低旳百分比=5/∞=0%，x2 旳目旳系数增长4,占允许增长旳百分比=4/7.8=51.2%。
变化旳百分比和为51.2%，没有超出100%，所以最优解不变。
（3）第一资源约束右端值增长30，占允许增长旳30 /∞=0%， 第二资源约束右端值增长4 ，占允许增长旳4/15=26.7%，
•初值和终值分别指 单元格在此次求解 前旳数值和求解后 旳数值。
敏感性分析报告（1）
可变单元格中 • “单元格”指决策变量所在单元格旳地址 • “名字”是决策变量旳名称 • “终值”是决策变量旳终值,即最优值 • “递减成本”指最优解中档于0旳变量，相应旳目旳函数中旳系数
增长或降低多少，最优解不再为0 • “目旳式系数”目旳函数中旳系数，为已知条件 • “允许旳增量”与“允许旳减量”表达目旳函数中旳系数在增量
（1）引用旳类型
三种类型 ：
相对引用、 绝对引用、混合引用
（2） 相对引用
格式： A3 、B6
使用相对引用后，系统将会记住建立公式旳单元格和被 引用旳单元格旳相对位置，在复制这个公式时，新旳公式单 元和被引用旳单元依然保持这种相对位置。
（3）绝对引用 格式：$a$3 $d$5
绝对引用是指被引用旳单元与引用旳公式单元旳位置 关系是绝正确，不论将这个公式复制到任何单元，公式所 引用旳还是原来单元格旳数据。
2) 在弹出旳对话框中旳“可用加载宏”列表框 中,选定待添加旳加载宏“规划求解”选项旁 旳复选框,然后单击“拟定”.单击“拟定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
利用Excel求解线性规划问题
利用Excel求解线性规划问题线性规划问题的求解有很多方法，也有很多工具。
比如常用的Matlab、Lingo，记得参加数学建模的时候就是用的Lingo解决线性规划问题的。
本文主要讲解如何使用Excel求解线性规划问题，Excel本身是没有计算线性规划问题能力的，因此我们首先要加载相应的宏定义。
一、加载宏定义（不同版本的加载方式有所不同）：Excel 2003：单击“工具”菜单，然后单击“加载宏”，选择“规划求解”点击确定。
Excel 2007：方法一：用快捷键。
先按Alt+T，再按I键，即可打开加载宏对话框。
方法二：单击“Office按钮→Excel 选项→加载项”，确保“管理”右侧下拉列表中的选项是“Excel 加载项”，单击“转到”按钮即可。
Excel 2010：直接在功能区中选择“开发工具”选项卡，在“加载项”组中单击“加载项”命令，选择“规划求解”点击确定。
注意：如果功能区中没有“开发工具”选项卡，可以通过自定义功能区来显示“开发工具”选项卡：单击“文件→选项→自定义功能区”，然后在右侧区域中勾选“开发工具”并单击“确定”。
二、初始化数据（以Excel 2010为例，其他版本大同小异）：比如我们要计算的线性规划问题如下：那么，我们可以构造如下的表格数据。
其中，B2:F2为待求的值Xi，B3:F3为目标函数的系数，B4:F4、B5:F5、B6:F6为约束条件的系数。
在G3单元格中输入公式=$B$2*B3+$C$2*C3+$D$2*D3+$E$2*E3+$F$2*F3，并将鼠标放到单元格的右下角会变成黑色十字架，向下拖拽复制单元格公式到G4、G5、G6单元格。
然后，单击“数据”选项卡，单击“规划求解”打开“规划求解参数”对话框。
∙修改“设置目标”为$G$3,即最优解下目标函数的值z所在的单元格。
∙选择是求最大值，还是最小值。
∙“可变单元格”指的是最优解取值变量所在的单元格。
∙“遵守约束”指的是约束条件中对各变量的约束情况。
excel线性规划
excel线性规划Excel线性规划是指利用Excel软件来解决线性规划问题。
线性规划问题是最经典的优化问题之一，主要是在一定约束条件下，找出使某个目标函数取得最优值的决策变量取值。
Excel提供了Solver插件，可以用于求解线性规划问题。
首先，我们需要建立起线性规划问题的模型。
假设我们有m个决策变量x1、x2、...、xm，需要找到这些决策变量的取值，使得目标函数Z(x1、x2、...、xm)取得最优值。
同时，还有n个约束条件，即使得一些函数关系式（一般为等式或不等式）满足。
线性规划模型可以表示为如下形式：目标函数：Z = c1x1 + c2x2 + ... + cmxm + d约束条件：A11x1 + A12x2 + ... + A1mxm <= b1A21x1 + A22x2 + ... + A2mxm <= b2...An1x1 + An2x2 + ... + Anmxm <= bn然后，我们可以通过Excel的Solver插件来求解线性规划问题。
具体步骤如下：1. 打开Excel软件，在工具栏中选择“数据”菜单，点击“求解器”按钮。
2. 在弹出的Solver对话框中，选择“线性规划”作为求解的方法。
3. 在“目标单元格”栏中输入目标函数的单元格地址。
若目标函数是在单元格C1中，则输入$C$1。
4. 在“变量单元格”栏中输入决策变量的单元格范围。
若决策变量是在范围B1:B5中，则输入$B$1:$B$5。
5. 在“约束条件”栏中，点击“添加”按钮，逐个输入约束条件。
每个约束条件包括“约束单元格”、“约束类型”和“约束值”三项。
若第一个约束条件是在单元格D1中，约束类型为“<=”，约束值为10，则输入$D$1<=10。
6. 在“求解方法”下拉菜单中，选择求解的方法。
常用的有“规划求解法”和“单纯形法”。
7. 点击“确定”按钮开始求解。
Solver会根据给定的目标函数和约束条件，寻找使目标函数取得最优值的决策变量取值。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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规划求解的选项可以用来解决线性规划问题。
可以设置决策变量为整数约束。
规划求解可以用来解决最多有200个变量，100个外在约束和400个简单约束（决策变量整数约束的上下边界）的问题。
安装线性规划加载项Excel 2003：菜单栏——工具——加载宏——规划求解调用规划求解，从主菜单中选择工具/规划求解。
Excel 2007：Office按钮——Excel选项——加载项——转到——加载宏——规划求解加载项在“数据”选项卡中出现带有“规划求解”按钮的“分析”组输入数据在Excel中输入问题数据，我们即可以输入原来的形式也可以输入标准的计算形式。
通常我们就输入原来的形式，让Excel自己把它变成标准形式。
因此，我们输入如下形式。
下图显示了输入这个问题后的屏幕，我们把变量1x称为x称为“CS-02”。
我们把三个约束也分别“CS-01”，变量2命名为BLENDHRS,PURIHRS和CS02LIM。
除了指约束外，这些名字也被Excel指为与这些约束相对应的剩余/松弛Slack/surplus 变量。
我们用单元格B2和C2来包含1x和2x的值。
约束中‘<=’的符号只是给我们自己看的，我们需要告诉规划求解（Solver）这些符号。
下面显示的是单元格所用的公式(formulae)。
注意在单元格D4中我们需要输入公式来计算目标函数（这个例子中，“=B4*B2+C4*C2”,其中*在Excel中指的是乘的意思），在D6到D8中输入公式来计算每个约束（如果你没有用过Excel，公式中的$符号，你可以不用管；它只是一个捷径）。
注意作为一个捷径，在单元格D6中我们也可以输入=SUMPRODUCT($B$2:$C$2,B6:C6)，然后拷贝和粘贴这个公式到单元格D7和D8中。
当问题比较大的时候，这个会比较有用。
一旦我们设置好问题之后，我们可以从工具菜单中选择‘规划求解’（Solver）,我们需要告诉规划求解参数对话框我们的问题。
这个例子中，单元格C4是目标单元格，需要最大化；这是我们输入目标函数的单元。
Excel会通过调整可变单元格B2和C2的值（我们的决策变量）来最大化目标。
（符号‘：’在$B$2:$C$2公式中，实际上指的是从B2到C2的单元格，这个例子中，只是单元格B2和C2,对于一个大点的问题，你可能输入$B$2:$F$2来告诉ExcelB2,C2,D2,E2和F2是决策变量。
）约束的输入是通过点击添加按钮，点击增加钮会弹出增加约束对话框，如下所示。
上面的第一个约束（又在下面的增加约束对话框中显示）是非负约束，这个例子中单元格B2和C2是正的（即CS-01>=0和CS-02>=0）。
在输入约束后，你可以用增加按钮增加一个约束。
如果你想改变约束，可以点击修改按钮，打开修改约束的对话框。
这个对话框和增加约束的对话框非常相似。
为了确保Excel产生我们期待的LP Solver的输出，我们需要点击选项按钮，选中采用线性模型和假定非负，假定非负也是另一种说1x,2x>=0的方式。
为了保证找到最优解，我们把允许误差设为0%。
一旦我们点击求解按钮，规划求解（Solver）会找到一个解（如果解存在），并显示下面的窗口，允许生成一定数量的报告；我们通常想要的是答案报告和灵敏度分析报告。
下面显示了Excel找到的最优解和答案报告（你可以看出Slacks 已经被加到答案报告中）。
如果你得到的报告和这个不同，你可能没选中“采用线性模型”。
最优解是生产70千加仑的CS-01和90千加仑的CS-02,这会给Case Chemicals带来每周$66000的利润。
1.8灵敏度分析引言一旦我们找到了最优解，我们经常想知道如果问题数据发生了变化，最优解会怎样改变。
换句话说，我们想知道最优值对模型中的某些值有多敏感。
例如，我们可能会问如果Case Chemicals模型中的某些值发生变化，会发生什么。
灵敏度分析主要是用来解决这样的问题。
术语上面的的式子中（1）目标函数中的300和500被称作成本（costs）（2）约束中的230，250和120被称作右边（right hand sides）计算机输出的灵敏度分析在问题求解后，Excel会产生一个灵敏度的表。
Case Chemicals 的灵敏度报告如下所示。
注意为了得到如下的内容，你必须告诉Excel“采用线性模型”。
Excel给出值的范围，用允许增加（如CS-01的700，也就是值的最大到300+700=1000）和减少（CS-01的50，也就是值最小到300-50=250）的数量来表示。
下面我们解释如何使用这个表。
成本灵敏度分析（Cost Sensitivity Analysis）我们考虑目标函数中成本系数的变化1.考虑最优解例如：对于Case Chemicals的问题，最优解是，目标函数值（利润）是660002.如果一个变量的成本发生变化，那么灵敏度分析会告诉我们原来的最优决定是否还是最优的。
例如：如果的成本$300增加到$400,解是否是最优的？也就是，是否我们还应该生产70千加仑的CS-01和90千加仑的CS-02?3.成本灵敏度分析的输出给出了新成本可以改变到的最小和最大值而不改变最优的决定。
例：对（CS-01）,成本可以变到250-1000之间的任何值，而不改变最优解。
4.如果新的成本在范围内，那么原来的最优解保持最优。
如果不是，那么问题需要重新求解。
例：新的成本=400 值的范围 250-1000. 在范围内？是/（否）所以原来的解还是/(不是)最优的。
5.如果原来的解还是最优的，我们可以计算新的目标函数值。
新的目标函数值=旧的目标函数值+决策变量值*成本系数变化=旧的目标函数值+决策变量值*（新的成本系数-旧的成本系数）例：Case Chemicals新的总利润可以计算为旧的利润=$66,000决策变量的值：旧的成本系数：的旧的成本系数是300新的成本系数：新的成本是400新的目标函数值=旧的目标函数值+决策变量值*成本系数变化=66000+70*（400-300）=73000注意：这个分析只能用于一个变量的成本发生变化。
成本分析-数值例子例1. 市场的变化允许Case Chemicals在CS-01上的利润上增加了$200/每1000加仑。
生产计划该如何改变，Case Chemicals现在最大的利润是多少？例2. 由于市场的变化，CS-02的利润下降了$400/每1000加仑，现在Case Chemicals可以获得的最大利润是多少？第一步：识别问题的变化，x2的成本系数由500变为100.第二步：检查新的成本系数是否在范围内新的成本是100，范围是150-600，在范围内？否第三步：如果在范围内，计算新的成本答:最优生产计划改变了，需要重新求解。
例3. CS-01和CS-02的利润分别增加了10%和15%，Case Chemicals可以获得的最多的利润是多少？两个变化，不能回答。
例4. CS-01和CS-02的利润现在都加倍了，Case Chemicals是否应该改变他们生产CS-01和CS-02的量。
由于成本的比例没变，目标函数的轮廓看起来和原来一样，所以最优解没有改变。
成本灵敏度分析的图形解释改变一个成本对应着目标函数轮廓的改变。
下面显示了我们改变CS-01的利润（Cost on X1）1.原来的目标函数（Original Objective Function）原来CS-01的利润是$300,目标函数是Max 300X1+500X2图中显示了目标函数的轮廓。
注意当我们向右上方移动时目标函数是增加的。
原来的最优解是x1=70,x2=90, 最优的目标函数值为$300*70+$500*90=$660002.新的CS-01的利润为$500目标函数是Max 500X1+500X2图中显示了目标函数的轮廓。
注意当我们向右上方移动时目标函数是增加的。
原来的最优解仍然是最优的。
新的最优目标函数值为$500*70+$500*90=$80000检查：新的成本是否在Excel范围内？答：是/否(是，所以最优解没有变化)3.新的CS-01的成本$1000目标函数是Max 1000X1+500X2图中显示了目标函数的轮廓。
注意当我们向右上方移动时目标函数是增加的。
原来的最优解仍然是最优的。
新的最优目标函数值为$1000*70+$500*90=$115000评论：原来的最优解是众多最优解中的一个。
检查：新的成本在Excel的范围内吗？答：是，但只是刚刚是4.新的CS-01的成本$1500目标函数是Max 1000X1+500X2图中显示了目标函数的轮廓。