下载文档原格式
用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最多可解200个变量、600个约束条件的问题。
下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。
一、实验目的1、掌握如何建立线性规划模型。
2、掌握用Excel求解线性规划模型的方法。
3、掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。
4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。
二、实验内容1、[工具][规划求解]命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。
在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。
使用[规划求解]命令的一般步骤为：第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。
图1“规划求解参数”对话框选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。
如利润问题，选取“最大值”可变单元格决策变量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。
图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。
图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。
EXCEL规划求解功能操作说明Excel规划求解功能是Excel内置的解决最优化问题的工具，可用于线性规划、整数规划、非线性规划等诸多领域。
该功能十分便捷灵活，可以帮助用户快速找到问题的最优解。
一、添加求解功能1.打开Excel表格，点击“文件”>“选项”>“加载项”。
2.在弹出的窗口中选择“Excel加载项”>“转到”>“excel加载项”>“管理”。
在“可用的加载项”中勾选“求解器”并关闭窗口。
3.返回Excel表格，在数据选项卡中选择“分析”>“求解”，弹出求解对话框。
二、建立规划模型1.确定目标：需要确定最终要达到的目标或绩效指标，例如最大化利润、最小化成本等。
2.确定决策变量：需要确定影响目标的变量，例如销售量、成本等。
3.建立约束：需要确定影响决策变量的条件，例如材料成本、生产时间等。
注意约束需要用等式、不等式等数学形式表示。
例如，在一个玩具生产厂家的例子中，有以下规划问题：在有限的资源下，最大化玩具的利润。
目标：最大化利润。
决策变量：生产每种玩具的数量。
三、设置求解参数1.目标单元格：选择Excel表格中目标单元格，该单元格包含要优化的方程式。
4.变量单元格必须满足约束：勾选此项，保证变量单元格满足约束条件。
5.求解方法：选择要使用的求解算法，包括线性规划、非线性规划和整数规划等。
1.点击“求解”按钮，系统会自动寻找目标单元格、变量单元格和约束单元格区域。
2.系统执行计算，找到最优解并将其展示在新的单元格区域中。
3.若求解成功，单击“继续”将结果保存在Excel表中。
总之，利用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 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。
具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。
产品组合通常必须满足以下约束：
● 产品组合使用的资源不能超标。
● 对每种产品的需求都是有限的。
我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。
下面，我们来考虑让某医药公司的最优产品组合问题。
该公司有六种可以生产的药品，相关数据如下表所示。
设该公司生产药品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 +++++≤⎧⎨+++++≤⎩
的设定： 系数矩阵 目标函数的系数 系数矩阵右端常数
可变单元
约束函数单元 目标函数单元
（3.2）约束条件12
34
56960928104197710841055
x x x x x x ≤⎧⎪≤⎪⎪≤⎪⎨≤⎪⎪≤⎪≤⎪⎩的设定
（3.3）约束条件1260,0,,0x x x ≥≥≥ 的设定
这里值得注意的是：
●我们采用向量的形式设定同向不等式，并且不等式两边可以一个时行向量，
另一个是列向量；
●对所有分量都是0的向量，我们可以用一个0来代替。
（4）求解：我们选择保存三个报告
得到的三张份告如下
补充说明：
a)如果数学规划模型中包含整型变量或0－1型变量，只需要在设定约束条件一
步中设定相应的变量是整型变量或0－1型变量即可。
例如，假定在上例中变,x x是整型变量，只需要增加如下图所示的整型约束设定即可。
不过要注量
12
意的是，含整型变量或0－1型变量的问题是不能进行敏感性分析的。
b)求解报告中把输出目标函数单元、约束函数单元、和可边单元上方第一个输
有文字单元及左边第一个输有文字单元内全部文字都作为对解释目标函数单元、约束函数单元、和可边单元的解释，例如，把Excel表改为
则求解报告输出变为
c)另外，非线性规划模型的求解方法类似，在此不再赘述。
五、“规划求解”疑难解答
1、尚未找到满足要求的结果，“规划求解”即停止了运行
由于下列任意一个原因，“规划求解”在找到答案前，可能停止运行：
中断了求解过程。
在单击“求解”之前，选中了“规划求解选项”对话框中的“显示迭代结果”选项。
在单步迭代过程中，或达到最长运算时间或最大迭代次数时，单击了“停止”按钮。
选中了“规划求解选项”对话框中的“采用线性模型”复选框，但问题是非线性的。
在“规划求解参数”对话框的“设置目标单元格”框中指定的数值不收敛地增加或减少。
需要让“规划求解”运行更长的时间以求得结果。
请调整“规划求解选项”对话框中的“最长运算时间”或“迭代次数”的设置。
对于具有整数约束条件的问题，应该减小“规划求解选项”对话框中的“允许误差”的设置，使“规划求解”找到更好的整数解。
对于非线性问题，应该减小“规划求解选项”对话框中的“收敛度”的设置，使目标单元格数值变化缓慢时，“规划求解”仍可以运行，最终找到较好的结果。
应该选中“规划求解选项”对话框中的“自动按比例缩放”复选框，可能一些输入数值相差几个数量级，或输入和输出数值相差几个数量级。
当“规划求解”停止运行时，在“规划求解结果”对话框中显示出完成信息。
单击“保存规划求解结果”或“恢复为原值”，进行所需的更改，然后再运行一次。
2、可变单元格与约束条件或目标单元格中的数值差别很大
当可变单元格的典型数值与约束单元格或目标单元格中的数值相差几个数量级时，请选中“规划求解选项”对话框中的“自动按比例缩放”复选框。
对于非线性问题，在单击“规划求解参数”对话框中的“求解”之前，请确认可变单元格的初始数值与期望的最终数值的数量级相同。
3、未得到预期的结果
对于非线性问题，在可变单元格中尝试不同的初始值可能会有帮助，特别是在“规划求解”结果与期望的数值差别很大时。
预先将可变单元格的数值设置为预期的最优值，可以减少求解时间。
对于线性模型（也就是当“规划求解选项”对话框的“采用线性模型”复选框被选中时），改变可变单元格的初始值不会影响最终数值或求解时间。
4、“规划求解”得到的结果与以前的结果不同
“规划求解”显示如下消息：“规划求解已收敛到当前结果。
满足所有约束条件”。
这表明目标单元格中的数值在最近五次求解过程中的变化量小于“规划求解选项”对话框中“收敛度”设置的值。
“收敛度”中设置的值越小，“规划求解”在计算时就会越精细，但求解过程将花费更多的时间。
5、“规划求解”不能达到最优解
下面列出“规划求解”显示的完整消息。
●“规划求解”不能改进当前解。
所有约束条件都得到了满足
这表明仅得到近似值，迭代过程无法得到比显示结果更精确的数值；或是无法进一步提高精度，或是精度值设置得太小，请在“规划求解选项”对话框中试着设置较大的精度值，然后再运行一次。
●求解达到最长运算时间后停止
这表明在达到最长运算时间限制时，没有得到满意的结果。
若要保存当前结果并节省下次计算的时间，请单击“保存规划求解”或“保存方案”选项。
●求解达到最大迭代次数后停止
这表明在达到最大迭代次数时，没有得到满意的结果。
增加迭代次数也许有用，但是应该先检查结果数值来确定问题的原因。
若要保存当前值并节省下次计算的时间，请单击“保存规划求解”或“保存方案”选项。
●目标单元格中的数值不收敛
这表明即使满足全部约束条件，目标单元格数值也只是有增或有减但不收敛。
这可能是在设置问题时忽略了一项或多项约束条件。
请检查工作表中的当前值，确定数值发散的原因，并且检查约束条件，然后再次求解。
●“规划求解”未找到合适结果
这表明在满足约束条件和精度要求的条件下，“规划求解”无法得到合理的结果，这可能是约束条件不一致所致。
请检查约束条件公式或类型选择是否有误。
●“规划求解”应用户要求而中止
这表明在暂停求解过程之后，或在单步执行规划求解时，单击了“显示中间结果”对话框中的“停止”。
●无法满足设定的“采用线性模型”条件
这表明求解时选中了“采用线性模型”复选框，但是“规划求解”最后计算结果并不满足线性模型。
计算结果对工作表中的公式无效。
若要验证问题是否为非线性的，请选中“自动按比例缩放”复选框，然后再运行一次。
如果又一次出现同样信息，请清除“采用线性模型”复选框，然后再运行一次。
●“规划求解”在目标或约束条件单元格中发现错误值
这表明在最近的一次运算中，一个或多个公式的运算结果有误。
请找到包含错误值的目标单元格或约束条件单元格，更改其中的公式或内容，以得到合理的运算结果。
还有可能是在“添加约束”或“改变约束”对话框中键入了无效的名称或公式，或者在“约束”框中直接键入了“integer”或“binary”。
若要将数值约束为整数，请在比较运算符列表中单击“Int”。
若要将数值约束为二进制数，请单击“Bin”。
●内存不足以求解问题
Microsoft Excel 无法获得“规划求解”所需的内存。
请关闭一些文件或应用程序，再试一次。
●其他的 Microsoft Excel 实例正在使用 SOLVER.DLL
这表明有多个 Microsoft Excel 会话正在运行，其中一个会话正在使用 SOLVER.DLL。
SOLVER.DLL 同时只能供一个会话使用。
