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求解线性规划
利用线性回归方法求解生产计划方法一：1、建立数学模型:①设变量：设生产拉盖式书桌x台，普通式书桌y台，可得最大利润②确定目标函数及约束条件目标函数：y=max+115P90x约束条件：200x .....................⑴+y10≤20x .....................⑵4≤+y16128x .....................⑶+y1015≤220yx ..........................⑷,≥2、在Excel中求解线性规划①首先，如图1所示，在Excel工作表格输入目标函数的系数、约束方程的系数和右端常数项：图1②将目标方程和约束条件的对应公式输入各单元格中F2=MMULT（B6:C6,F6:F7)；F3=MMULT（B3:C3,F6:F7)；F2=MMULT（B4:C4,F6:F7)；F2=MMULT（B5:C5,F6:F7)；出现图2样式：图2线性规划问题的电子表格模型建好后，即可利用“线性规划”功能进行求解。
选择“工具”→“规划求解”出现“规划求解参数”窗口，如图3所示:图3在该对话框中，目标单元格选择F2，问题类型选择“最大值”，可变单元格选择F6：F7，点击“添加”按钮，弹出“添加约束条件”窗口，如图4所示:图4根据所建模型，共有4个约束条件，针对约束（1）：2002010≤+y x ，左端“单元格所引用位置”选择F3，右端“约束值”选择D3，符号类 型选择“<=”，同理继续添加约束（2）（3）（4），完成后选择“确定”，回到“规划求解参数”对话框，如5图所示：图5④点击“选项”按钮，弹出“规划求解选项”对话框，选择“采用线性模型”和“假定非负”两项，如图6所示：图6⑤点击“确定”→“求解”，选择“运算结果报告”“敏感性报告”“极限值报告”三项，最后点击“确定”，输出结果： 运算结果报告：敏感性报告：极限报告：方法二：1、建立数学模型设生产拉盖式书桌x 台，普通式书桌y 台，总利润为Z 元 确定目标函数及约束条件 目标函数：y x Z 90115max += 约束条件：⎪⎪⎩⎪⎪⎨⎧≥≤+≤+≤+0,22010151281642002010..y x y x y x y x t s 2、在Excel 中规划求解在Excel 中建立线性规划模型，如图1所示：图11)在E2中输入“=B2*B6+C2*C6”如图2所示，同理 E3=B3*B6+C3*C6E4=B4*B6+C4*C6B7=B5*B6+C5*C6图22)单击“工具”菜单下的“规划求解”，在弹出的“规划求解参数”对话框输入各项参数：✓目标单元格选择B7✓问题类型选择“最大值”✓可变单元选择B6:C6✓约束条件选择B6:C6≥0；E2:E4≤D2:D4参数设置完毕，如图3：图33）点击“选项”，弹出“规划求解选项”对话框，选择“采用线性模型”、“假定非负”和“显示迭代结果”，说明要求求解的问题是线性模型且所求的变量必须为非负，如图4所示：图44）点击“确定”→“求解”，选择“运算结果报告”“敏感性报告”“极限值报告”三项，最后点击“确定”，输出结果：运算结果报告：敏感性报告：极限值报告：。
用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中的加载宏新加入的规划求解功能解决线性规划问题
利用Excel中的加载宏新加入的规划求解功能解决线性规划问题（郑来运PPT例1）
具体步骤如下：
1.打开Excel，单击“工具”弹出菜单，然后单击“加载宏”会出现如下画面：
选择“规划求解”点击确定，这样你的Excel就有了能解决线性规划问题的功能。
2.依次输入以下数据作为准备工作，如下图：图中用不同的色块表示约束条件和可变部分
3.在表中选中D2的位置然后点击函数，出现“插入函数”的弹出框后，选择”常用函数”中的”SUMPRODUCT”,
如下图所示。
点击确定后在弹出的对话框中array1选择B2：C2，在Array2中选择B6：C6，同时可以看到公式的生成。
用相同的方法让D3,D4,都相应填上公式
选中E6输入公式SUMPRODUCT(B5:C5，B6:C6)
4.单击“工具”选择“规划求解”设置目标单元格为E6，可变单元格为B6，C6，并添加约束条件，如下图
单击“求解”
选择保存规划求解结果，点击“确定”得到求解结果。
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求解LP(线性规划)问题
▪
x1 , x2 ≥ 0
整理ppt
3
2、输入系数：
输入系数
▪ 在单元格A2:D4中分
别输入两个不等式约 束的系数与常数项,在 单元格A5:B5中分别
输入目标函数的两个 系数,在单元格A1:B1
中任意输入两个数分 别作为决策变量x1,x2 的值(如右图，C列暂 空) 。
整理ppt
4
3、LP模型的EXCEL输入（两 种方法）
▪ 在C2单元格中输入“=A2*A$1+B2*B$1”，并复 制到C3、C4、C5中，使它们分别变为 “=A3*A$1+B3*B$1” 、“=A4*A$1+B4*B$1”和 “=A5*A$1+B5*B$1”。
整理ppt
5
利用EXCEL的SUMPRODUCT 函数进行计算
▪ sumproduct是求两个数组（矩阵）相乘的结果 ▪ 数组的输入： ▪ “选中所有的数组元
②在“可变单元格(B)”栏后的空白中 填入$A$1:$B$1；
整理ppt
8
③光标指向“约束”栏，按“添加”， 出现“添加约束”对话框（如下图），
对话框“改变约束”
▪ 依次填入约束关系，每输完一条，按“添加”， 输入所有约束条件后，按“确定”，
整理ppt
9
又退回到下图状态，在下图中可以选 “更改”、“删除”、“全部重设” 来编辑约束条件及其他设置。
3、用Excel求解LP(线 性规划)问题
整理ppt
1
1、检查是否加载了宏“规划求解”？ 即查看Excel窗口的“工具”菜单下 是否有“规划求解”菜单条？
▪ 找到与你的微机中已
在机房中，这装的Office版本一致
一步骤已经为的Office安装盘。单
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会根据给定的目标函数和约束条件，寻找使目标函数取得最优值的决策变量取值。
Excel求解线性规划问题实验教程要点
数学与信息科学学院Excel求解线性规划问题实验教程二零一三零八月目录1.关于“规划求解” (1)2.如何加载“规划求解” (2)3.“规划求解”各参数解释和设置 (3)4.“规划求解”的步骤 (6)5.Excel求解线性规划问题 (8)6.Excel求解运输问题 (14)7.Excel求解目标规划问题 (18)8.Excel求解整数规划问题 (22)1.关于“规划求解”“规划求解”是Excel中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式（公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。
公式总是以等号（=）开始）的最优值。
“规划求解”将对直接或间接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。
“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。
在创建模型过程中，可以对“规划求解”中的可变单元格数值应用约束条件（约束条件：“规划求解”中设置的限制条件。
可以将约束条件应用于可变单元格、目标单元格或其它与目标单元格直接或间接相关的单元格。
而且约束条件可以引用其它影响目标单元格公式的单元格。
使用“规划求解”可通过更改其它单元格来确定某个单元格的最大值或最小值。
）Microsoft Excel的“规划求解”工具取自德克萨斯大学奥斯汀分校的Leon Lasdon 和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码。
线性和整数规划问题取自Frontline Systems公司的John Watson 和Dan Fylstra提供的有界变量单纯形法和分支边界法。
2.如何加载“规划求解”安装office的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。
下面是加载“规划求解”宏的步骤：（1）在“工具”菜单上，单击“加载宏”。
【必须收藏】只用60秒就能解决的Excel线性规划，你却熬了整个通宵...
【必须收藏】只用60秒就能解决的Excel线性规划，你却熬
了整个通宵...
箭头处“蓝色字”，
每天学一个表格技能！
领导给小王同志12个金额，让他凑数据，凑成26005元和33459元。
左拼右凑这个金额，凑了一个通宵，还没凑对，十分着急，同事3分钟就给解决了
❶在Excel中调出线性规划我们在Excel选项里面，找到加载项然后勾选规划求解加载项，点击确定
❷我们在C1单元格输入公式：=SUMPRODUCT(A2:A13*B2:B13)
❸前面加载了加载项之后，在数据选项卡下，就有了规划求解进行相关设置后，运行得到最终的结果，操作动图如下所示：
其中的设置是，设置目标是C1单元格，目标值是26005，可变的单元格区域是B2:B13，遵守约束是B2:B13是二进制
最终B2:B13单元格中的数据为1的这些值累加起来，正好就能得到我们需要的26005了
剩下的数据正好就是33459元了。
模糊凑数据
如果给定的一个金额是系统也不可能准确的凑出来，Excel一直在计算的过程中的时候，可以随时按ESC退出
或者我们改变公式，使得进行模糊的凑数据接近这个值，我们现在要把这些数据最接近30000
我们可以在C1输入公式：=ABS(SUMPRODUCT(A2:A13,B2:B13)-30000)
然后在线性规划中的设置是：C1是最小值
然后运行，这个时候，会一直在那里转，这个时候，我们需要按ESC，然后
保留求解
得到了一组结果。
这个例子还是找到了正好等于30000的数据。
如果不等于的话，那么会得出一个最接近的结果。
当然，平时不用这个功能的时候，需要把这个功能给关闭了，否则每次打开Excel的速度会变慢一点。
线性规划的EXCEL求解
关于“规划求解选项”各可选项的说明 (３)
• 装入模型：输入对所要调入模型的引用 • 保存模型：将打开“保存模型”对话框， 输入模型的保存位置，只有当需要在工作 表上保存多个模型时，单击此命令，第一 个模型会自动保存。
一类特殊的线性规划问题：运输问 题
例1 某公司经销甲产品。它下设三个加工厂。 每日的产量分别是：A1为7吨，A2为4吨，A3为9吨。 该公司把这些产品分别运往四个销售点。各销售点 每日销量为：B1为3吨，B2为6吨，B3为5吨，B4为 6吨。已知从各工厂到各销售点的单位产品的运价如 下表所示。问该公司应如何调运产品，在满足各销 点的需要量的前提下，使总运费为最少。
( j 1, 2,3, 4) (i 1, 2,3)
这类问题，我们称之为运输问题。产量正好和销 量相等的运输问题称为产销平衡问题，产销平衡问题 有以下特征：
1. 平衡运输问题必有可行解，也必有最优解. 2. 平衡运输问题的约束方程系数矩阵 A 的所有各阶子 式只取 0，1 或 -1 三个值. 3. 如果平衡运输问题中的所有产量 ai 和销量 bj 4. 都是整数，那么，它的任一基可行解都是整数解.
线性规划问题的EXCEL求解
• 用EXCEL求解线性规 划问题前，需要在工 具菜单上选择加载宏： 弹出对话框
勾选规划工具，点击“确定”即可
• 若已加载过则无需再次加载。若安装不完全，也是无法加 载的，需要重新安装。 • 加载宏之后，工具菜单上即出现“规划求解”按钮，可以 用来求解许多规划问题，当然包含线性规划问题
例：某工厂生产三种产品，各种产品所需的原材料和设备 台时及能供给数量如下表所示，问如何安排生产利润最大？
甲
原材料 工时 单位利润 ３ ２ 4
乙
用excel进行线性规划求解
步骤1 单击[工具]菜单中的[规划求解]命令。
步骤2 弹出[规划求解参数]对话框，在其中输入参数。
置目标单元格文本框中输入目标单元格；[等于]框架中选中[最大值＼最小值〕单选按钮。
步骤3 设置可变单元格区域，按Ctrl键，用鼠标进行选取，或在每选一个连续区域后，在其后输入逗号“，”。
步骤4 单击[约束〕框架中的[添加]按钮。
步骤5 在弹出的[添加约束]对话框个输入约束条件．
步骤6 单击[添加]按钮、完成一个约束条件的添加。
重复第5步，直到添加完所有条件
步骤7 单击[确定]按钮，返回到[规划求解参数]对话框，完成条件输入的[规划求解参数]对话框。
步骤8 点击“求解器参数”窗口右边的“选项”按钮。
确信选择了“采用线性模型”旁边的选择框。
这是最重要的一步工作！如果“假设为线性模型”旁边的选择框没有被选择，那么请选择，并点击“确定”。
如果变量全部非负，而“假定变量非负”旁边的选择框没有被选择，那么请选择，并点击“确定”。
步骤9 单击[求解]按钮，弹出[规划求解结果]对话柜，同时求解结果显示在工作表中。
步骤10 若结果满足要求，单击[确定]按钮，完成操作；若结果不符要求，单击[取消]按钮，在工作表中修改单元格初值后重新运行规划求解过程。
利用Excel解线性规划问题
利用Excel中的加载宏新加入的规划求解功能可以方便的解决线性规划问题。
下面是详细步骤：
（1）打开Excel，单击“工具”弹出菜单，然后单击“加载宏”会出现如下画面：
选择“规划求解”点击确定，这样你的Excel就有了能解决线性规划问题的功能。
（2）依次输入以下数据作为准备工作，如下图：
（3）然后在表中选中“G3”位置如下图
输入以下公式“=$B$2*B3+$C$2*C3+$D$2*D3+$E$2*E3+$F$2*F3”（输入公式时必须在英文输入状态），然后回车即可。
（4）选中“G3”位置可以看到公式了，则用填充柄拖动让G4,G5,G6都相应填上公式
（5）再次选中“G3”点击“工具”----“规划求解”，出现下图：
根据题意选择目标单元格为“$G$3”，等于最小值，可变单元格为“$B$2:$C$2:$D$2:$E$2:$F$2”。
然后点击“添加”添加约束条件。
一共有八个约束条件要加入。
下图为其中之一：
（6）添加完约束条件后的图片是：
（7）如上图点击“求解”即可得到结果，如下图：。
EXCEL规划求解功能操作说明书
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”代表5x1;“C3*C12”代表1x2；“D3*D12”代表-1x3；“E3*E12”代表1x4；“F3*F12”代表0x5。
《运筹学》使用Excel求解线性规划问题
第三节使用Excel求解线性规划问题利用单纯形法手工计算线性规划问题是很麻烦的。
office软件是一目前常用的软件，我们可以利用office软件中的Excel工作表来求解本书中的所有线性规划问题。
对于大型线性规划问题，需要应用专业软件，如Matlab，Lindo，lingo等，这些软件的使用这里我们不作介绍，有需要的，自己阅读有关文献资料。
用Excel工作表求解线性规划问题，我们需要先设计一个工作表，将线性规划问题中的有关数据填入该工作表中。
所需的工作表可按下列步骤操作：步骤1 确定目标函数系数存放单元格，并在这些单元格中输入目标函数系数。
步骤2 确定决策变量存放单元格，并任意输入一组数据。
步骤3 确定约束条件中左端项系数存放单元格，并输入约束条件左端项系数。
步骤4 在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式，计算出约束条件左端项对应于目前决策变量的函数值。
步骤5 在步骤4的数据右边输入约束条件中右端项（即常数项）。
步骤6 确定目标函数值存放单元格，并在该单元格中输入目标函数值的计算公式。
例建立如下线性规划问题的Excell工作表：1212121212max1502102310034120..55150,0z x xx xx xs tx xx x=++≤⎧⎪+≤⎪⎨+≤⎪⎪≥⎩解：下表是按照上述步骤建立的线性规划问题的Excell工作表。
其中：D4=B2*B4+C2*C4, D5=B2*B5+C2*C5 , D6=B2*B6+C2*C6, C7= B2*B1+C2*C1 。
建立了Excel工作表后，就可以利用其中的规划求解功能求相应的线性规划问题的解。
求解步骤如下：步骤1单击[工具]菜单中的[规划求解]命令。
步骤2 弹出[规划求解参数]对话框，在其中输入参数。
置目标单元格文本框中输入目标单元格；[等于]框架中选中[最大值＼最小值］单选按钮。
步骤3 设置可变单元格区域，按Ctrl键，用鼠标进行选取，或在每选一个连续区域后，在其后输入逗号“，”。
利用excel求解线性规划问题
利用excel求解线性规划问题线性规划（Linear Programming，LP）是一种用于求解最优化问题的数学方法。
它在经济学，管理学，工程学等领域得到了广泛应用。
Excel是一种功能强大的电子表格软件，提供了一些内置的工具和函数，可以帮助我们求解线性规划问题。
在Excel中求解线性规划问题，通常需要使用“规划求解”工具，该工具位于“数据”选项卡的“分析”分组中。
下面将逐步介绍如何使用Excel求解线性规划问题。
步骤1：建立模型首先，我们需要建立线性规划模型。
模型通常包括目标函数和约束条件。
目标函数：我们需要定义一个目标函数，它表示我们希望最大化或最小化的目标。
在Excel中，可以使用单元格引用和各种数学运算符来定义目标函数。
约束条件：我们需要定义一系列约束条件，这些约束条件是对决策变量的限制。
在Excel中，可以使用不等式和等式来表示约束条件。
每个约束条件都可以转化为一个单元格引用和数学运算符的组合。
步骤2：输入数据在建立模型之后，我们需要输入相关数据。
这包括目标函数中的系数和约束条件中的系数和约束值。
在Excel中，我们可以使用单元格来输入这些数据。
步骤3：设置规划求解选择“数据”选项卡，在“分析”分组中找到“规划求解”工具。
如果没有找到该工具，可能需要先启用“加载项”中的“分析工具包”。
点击“规划求解”，将会打开一个对话框。
在这个对话框中，我们需要输入一些参数来设置求解过程。
目标单元格：这是包含目标函数结果的单元格。
调整变量单元格：这是包含决策变量的单元格范围。
约束条件：这是包含约束条件的单元格范围。
约束条件中的系数：这是一个选择项，用于指定约束条件中的系数是包含在单元格范围中还是直接输入。
约束条件的约束值：这是一个选择项，用于指定约束条件中的约束值是包含在单元格范围中还是直接输入。
约束条件的约束类型：这是一个选择项，用于指定约束条件的类型（大于等于，小于等于等）。
非负约束：这是一个复选框，用于指定决策变量是否具有非负约束。
用EXCLE求解线性规划问题
4.在约束条件左端项系数存放单元格右边的单元格中输入约束 条件左端项的计算公式，计算出约束条件左端项对应于目前决 策变量的函数值。 5.在步骤4的的数据右边输入约束条件中右端项（即常数项）
6.确定目标函数值存放单元格，并在该单元格中输入目标函数 值电容计算公式。
例.求下列线性规划问题
SUMPRODUCT(B3:C3,B10:C10)
例.求解如下的线性规划问题 某企业的产品生产数据如下
分共厂 门 生产时间 窗 0 2小时 2小时 500 4小时 12小时 18小时 每周可利用时间
s.t
第一步：选择决策变量单元格 决策变量的一般初始值赋0。
第二步：目标单元格，用函数公式表示。
用EXCLE求解线性规划问题
1.“线性规划求解”的安装(文件
选项)
加载项 规划求解加载项 选择在数据加载项
转到
加载数据规划求解选项以后,在“数据”菜单中就 会出现“规划求解”
线性规划求解的步骤：
1.确定目标函数系数存放单元格，并在这些单元格中输入目标 函数系数。 2.确定决策变量存放单元格，并任意输入一组数据； 3.确定约束条件中左端项系数存放单元格，并输入约束条件左 端项系数；
G 11 Total Proft 12 =sumproduct（C4:D4，C12:D12）
第三步：约束条件左边项用函数表示
5 6 7 8 9
E Hours Used SUMPRODUCT(C7:D7,$C$12:$D$12) SUMPRODUCT(C8:D8,$C$12:$D$12) SUMPRODUCT(C9:D9,$C$12:$D$12)
第四步：激活规划求解，确定可变单元格和目标 单元格
第五步：增加约束 条件
excel应用线性规划求解
建立規劃求解報表
• 產生報表 • 檢視報表
產生報表
• 請您開啟範例檔案 Ch19-05, 選取 C11 儲 存格並執行『工具/規劃求解』命令, 接著按 下規劃求解參數交談窗中的求解鈕後, 跟著 底下的步驟來建立報表：
產生報表
產生報表
檢視報表
• 若要檢視報表, 只需切換至該工作表中即可。 底下一一檢視剛才產生的 3 種規劃求解報 表：
變更規劃求解條件
• 修改規劃條件 • 修改求解限制式
修改規劃條件
• 假設此時高階主管修訂了部分生產計劃, 決 定投入更多的原料與時間來生產梅子烏龍 茶與無糖綠茶, 那麼這時候我們就要到工作 表中修改數據, 然後重新進行求解了。 • 請開啟範例檔案 Ch19-03：
修改規劃條件
修改規劃條件
• 由於應生產量和總收益是透過規劃求解功 能所計算出來的, 因此當我們更新工作表中 的資料後, 還必須選定目標儲存格 C11, 然 後重新執行『工具/規劃求解』命令, 才能得 到新的求解結果喔！
規劃求解的條件設定
• 最後還有時間配額的限制, 因此請再按 下新增鈕完成如下的設定：
規劃求解的條件設定
• 完成限制式的設定之後, 按下確定鈕, 即可回到規劃求解參數交談窗：
執行求解
• 現在, 請您按下求解鈕, 計算的結果即將呈 現在眼前：
執行求解
執行求解
• 原本感到複雜的生產問題, 經由規劃求解的 運算, 便可得知最佳的生產組合！尚泉公司 只要利用規劃的生產資源, 每個月生產梅子 烏龍茶 62.5 打、生產無糖綠茶 150 打, 就 可獲得最大利潤 25,750 元。
– 運算結果報表 – 敏感度報表 – 極限值報表
運算結果報表
• 運算結果報表會列出目標儲存格及變數儲 存格的初值、終值、及參數限制式的公式 內容：
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
调用 ‘规划求解’ 模块
• 选择工具下拉菜单 • 选择规划求解选项（事先 需用Office安装盘安装规 划求解的功能）
填写目标单元格和可变单元格
• 出现规划求解参数对话框
– 在目标单元格中输入B14 – 在等于选择最大 – 在可变单元格中输入B12:C12 – 选择添加
在上图显示的界面中，需要输入目标单元格、可变单元格， 添加约束条件，另外还可能需要进行选项设置。
• 在工作表的顶部输入数据 • 确定每个决策变量所对应 的单元格位置 • 选择单元格输入公式，找 到目标函数的值 • 选择一个单元格输入公式， 计算每个约束条件左边的 值 • 选择一个单元格输入公式， 计算每个约束条件右边的 值
图中，规定B12、C12 为可变单元格
可变单元格存放决策变量的取值，可变单元格数目等于决策变量个数
• 问：如何安排话务员才能保证服务人数， 又使总成本最低？
• 解：这个问题实际上是一个成本效益平衡 问题。管理层在向客户提供满意服务水平 的同时要控制成本，因此必须寻找成本与 效益的平衡。由于每节工作时间为3小时， 一天被分为8班，每人连续工作3节，各班 时间安排如下表：
• 为了建立数学模型，对应于一般成本效益 平衡问题，我们首先必须明确包含的活动 数目，活动一个单位是对应于分派一个话 务员到该班次收，效益的水平对应于时段。 收益水平就是该时段里上下班的话务员数 目，各活动的单位效益贡献就是在该时间 内增加的在岗位话务员数目。我们给出下 列成本效益平衡问题参数表：
• 在工作表的顶部输入数据 • 确定每个决策变量所对应 的单元格位置 • 选择单束条件左边的 值 • 确定约束单元格输入公式， 计算每个约束条件右边的 值
在约束单元格中，需要填入计算约束函数值的公式。
建立数学公式（步骤五）
• 在工作表的顶部输入数据 • 确定每个决策变量所对应 的单元格位置 • 选择单元格输入公式，找 到目标函数的值 • 确定约束单元格输入公式， 计算每个约束条件左边的 值 • 确定约束单元格输入公式， 计算每个约束条件右边的 值
解：（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%，
第三资源约束右端值减少15，占允许减少的15/50=30%。
改变的百分比和为56.7%，没有超过100%，因此影子价格仍然 有效。因此目标函数值改变量为
30 × 0 + 4 × 2.8 – 15 × 1.2=11.2 – 18= – 6.8
练习：话务员排班问题
• 某寻呼公司雇用了多名话务员工作，他们 每天工作3节，每节3小时，每节开始时间 为午夜、凌晨3点钟、凌晨6点钟，上午9点、 中午12点、下午3点、6点、9点，为方便话 务员上下班，管理层安排每位话务员每天 连续工作3节，根据调查，对于不同的时间， 由于业务量不同，需要的话务员的人数也 不相同，公司付的薪水也不相同，有关数 据见表。
保存求解结果
• 当求解结果对话框出现时，选 择保存规划求解结果。选择确 定。
运算结果报告
列出目标单元格和可变单 元格以及它们的初始值、 最终结果、约束条件和有 关约束条件的信息。 初值和终值分别指单元格 在本次求解前的数值和求 解后的数值。
敏感性分析报告（1）
可变单元格中 • “单元格”指决策变量所在单元格的地址 • “名字”是决策变量的名称 • “终值”是决策变量的终值,即最优值 • “递减成本”指最优解中等于0的变量，对应的目标函数中的系数 增加或减少多少，最优解不再为0 • “目标式系数”目标函数中的系数，为已知条件 • “允许的增量”与“允许的减量”表示目标函数中的系数在增量 与减量的变化范围内变化时，最优解保持不变（注：最优值发生 变化）
使用Excel进行求解
1.关于“规划求解”
2.如何加载“规划求解”
3. “规划求解”各参数设置
4. “规划求解”步骤
5. 利用“规划求解”解线性规划问题
2.如何加载“规划求解”
1) 在“工具”菜单上,单击“加载宏”
2) 在弹出的对话框中的“可用加载宏”列表框 中,选定待添加的加载宏“规划求解”选项旁 的复选框,然后单击“确定”.单击“确定” 后,“工具”菜单下就会出现一项“规划求解”
建立数学公式（步骤三）
• 在工作表的顶部输入数据 • 确定每个决策变量所对应 的单元格位置 • 选择单元格输入公式，找 到目标函数的值 • 确定约束单元格输入公式， 计算每个约束条件左边的 值 • 确定约束单元格输入公式， 计算每个约束条件右边的 值
在目标单元格中，需要填入计算目标函数值的公式。
建立数学公式（步骤四）
添加约束
• 在添加约束对话框中，在 单元格引用位置中输入 B17，选择<=，在约束值 中输入D17。选择添加 • 第三个条件添加完毕后， 选择确定 • 当规划求解参数对话框重 新出现时，选择选项
“选项”设置
• 当选项对话框出现时，选 择假设非负。选择确定
用Excel求解
• 出现规划求解参数对话框， 选择求解。
• 在工作表的顶部输入数据 • 确定每个决策变量所对应 的单元格位置 • 选择单元格输入公式，找 到目标函数的值 • 确定约束单元格输入公式， 计算每个约束条件左边的 值 • 确定约束单元格输入公式， 可采用 ‘复制粘贴’ 或 ‘直 计算每个约束条件右边的接输入’ 的方式导入数据。 值
建立数学公式（步骤二）
（2）约束右端值b同时变动的百分之百法则： 同时改变几个或所有函数约束的约束右端值，如果这些变动的幅 度不大，那么可以用影子价格预测变动产生的影响。为了判别这些 变动的幅度是否允许，计算每一变动占同方向可容许变动范围的百 分比，如果所有的百分比之和不超过百分之一百，那么影子价格还 是有效的；如果所有的百分比之和超过百分之一百，那就无法确定 影子价格是否有效。
敏感性分析报告（2）
约束单元格 • “单元格”指约束条件左边所在单元格的地址 • “名字”是约束条件左边的名称 • “终值”是约束条件左边的值 • “阴影价格”指约束条件右边增加或减少一个单位，目标函 数值增加或减少的数量 • “约束的限制值”指约束条件右边的值，为已知条件 • “允许的增量，减量”表示约束条件右边在允许的范围内变 化时，影子价格不变，即约束条件右边的值每增加1个单位， 目标函数值的增加仍然为影子价格的大小。因此，右端项在 一定范围内变化时，影子价格不变，目标函数值的变动等于 右端项变动值乘以影子价格
（2）命名工作表 或
（3）Format / sheet Rename 对话框
（4）键入工作表名
显示Rename sheet
2、工作表 Excel-2003 256列 65535 行
Excel-2007 16384列 1048576行 3、向单元格中输入数据 常数：不能改变的文字或其它数据 公式：输入到单元格中的数字值，或返回数字值的表达式。 4、进行计算
一个简单的例子
• 某工厂计划生产两种产品，利润分别为2和3，已知生 产单位产品所需的设备台时和A、B两种原材料的消耗， 如表
设备 原材料A 原材料B
产品1 1 4 0
产品2 2 0 4
8台时 16KG 12KG
• 目标是不超过资源限制的情况下，确定两产品产量， 得到最大利润。
建立数学公式（步骤一）
第五章 利用EXCEL求解线性规划问题 目的：
建立线性规划问题的模型
利用EXCEL求解线性规划问题
分析运算结果（敏感性分析）
一、EXCEL 基本知识
功能： 存储信息、进行计算、排序数据、用图或表的形 式显示数据、规划求解、财会分析、概率与统计分析等 等
1、命名工作表
（1）激活工作表1，单击sheet 1 标签
3. “规划求解”各参数设置
单击“规划求解”按钮,将会出现以下规划求 解参数设置对话框
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可 以加载或保存规划求解模型,并对规划求解过 程的高级属性进行控制
线性规划求解步骤：
1. 确定目标函数系数存放单元格，并在这些单元格中输入目标 函数系数。 2. 确定决策变量存放单元格，并任意输入一组数据； 3. 确定约束条件中左端项系数存放单元格，并输入约束条件左 端项系数； 4. 在约束条件左端项系数存放单元格右边的单元格中输入约束 条件左端项的计算公式，计算出约束条件左端项对应于目前决 策变量的函数值。 5. 在步骤4的的数据右边输入约束条件中右端项（即常数项） 6. 确定目标函数值存放单元格，并在该单元格中输入目标函数 值电容计算公式。
2012年12月管理创新实验班期末考试题
已知一个线性规划问题的灵敏度分析报告如下 变动单元格
单元格 $B$9 $C$9 $D$9 变量名 最终值 x1 x2 x3 0 12.4 9.5 减少成本 －2.8 0 0 目标系数 6 9 12 允许增加 值 2.8 7.8 2.6 允许减少 值 1E+30 3.4 1.5
极限值报告解释
列出目标单元格和可变单元格以及它们的数值、上下限和目标 值。含有整数约束条件的模型不能生成本报告。其中，下限是 在满足约束条件和保持其它可变单元格数值不变的情况下，某 个可变单元格可以取到的最小值。上限是在这种情况下可以取 到的最大值。
延伸
下面对目标系数同时变动以及约束右端值同时变动的情况分别作延伸。 （1）目标系数c同时变动的百分之百法则： 如果目标函数系数同时变动，计算出每一系数变动量占该系数同方向 可容许变动范围的百分比，而后将各个系数的变动百分比相加，如果 所得的和不超过百分之一百，最优解不会改变；如果超过百分之一百， 则不能确定最优解是否改变。