利用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处理，不影响求解。

利用线性回归方法求解生产计划方法一：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求解线性规划问题线性规划问题的求解有很多方法，也有很多工具。

比如常用的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求解线性规划问题实验教程二零一三零八月目录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）在“工具”菜单上，单击“加载宏”。

实验三、用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）。

【必须收藏】只用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的速度会变慢一点。

步骤1 单击[工具]菜单中的[规划求解]命令。

步骤2 弹出[规划求解参数]对话框，在其中输入参数。

置目标单元格文本框中输入目标单元格；[等于]框架中选中[最大值＼最小值〕单选按钮。

步骤3 设置可变单元格区域，按Ctrl键，用鼠标进行选取，或在每选一个连续区域后，在其后输入逗号“，”。

步骤4 单击[约束〕框架中的[添加]按钮。

步骤5 在弹出的[添加约束]对话框个输入约束条件．
步骤6 单击[添加]按钮、完成一个约束条件的添加。

重复第5步，直到添加完所有条件
步骤7 单击[确定]按钮，返回到[规划求解参数]对话框，完成条件输入的[规划求解参数]对话框。

步骤8 点击“求解器参数”窗口右边的“选项”按钮。

确信选择了“采用线性模型”旁边的选择框。

这是最重要的一步工作！如果“假设为线性模型”旁边的选择框没有被选择，那么请选择，并点击“确定”。

如果变量全部非负，而“假定变量非负”旁边的选择框没有被选择，那么请选择，并点击“确定”。

步骤9 单击[求解]按钮，弹出[规划求解结果]对话柜，同时求解结果显示在工作表中。

步骤10 若结果满足要求，单击[确定]按钮，完成操作；若结果不符要求，单击[取消]按钮，在工作表中修改单元格初值后重新运行规划求解过程。

利用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规划求解功能操作说明以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求解线性规划问题线性规划（Linear Programming，LP）是一种用于求解最优化问题的数学方法。

它在经济学，管理学，工程学等领域得到了广泛应用。

Excel是一种功能强大的电子表格软件，提供了一些内置的工具和函数，可以帮助我们求解线性规划问题。

在Excel中求解线性规划问题，通常需要使用“规划求解”工具，该工具位于“数据”选项卡的“分析”分组中。

下面将逐步介绍如何使用Excel求解线性规划问题。

步骤1：建立模型首先，我们需要建立线性规划模型。

模型通常包括目标函数和约束条件。

目标函数：我们需要定义一个目标函数，它表示我们希望最大化或最小化的目标。

在Excel中，可以使用单元格引用和各种数学运算符来定义目标函数。

约束条件：我们需要定义一系列约束条件，这些约束条件是对决策变量的限制。

在Excel中，可以使用不等式和等式来表示约束条件。

每个约束条件都可以转化为一个单元格引用和数学运算符的组合。

步骤2：输入数据在建立模型之后，我们需要输入相关数据。

这包括目标函数中的系数和约束条件中的系数和约束值。

在Excel中，我们可以使用单元格来输入这些数据。

步骤3：设置规划求解选择“数据”选项卡，在“分析”分组中找到“规划求解”工具。

如果没有找到该工具，可能需要先启用“加载项”中的“分析工具包”。

点击“规划求解”，将会打开一个对话框。

在这个对话框中，我们需要输入一些参数来设置求解过程。

目标单元格：这是包含目标函数结果的单元格。

调整变量单元格：这是包含决策变量的单元格范围。

约束条件：这是包含约束条件的单元格范围。

约束条件中的系数：这是一个选择项，用于指定约束条件中的系数是包含在单元格范围中还是直接输入。

约束条件的约束值：这是一个选择项，用于指定约束条件中的约束值是包含在单元格范围中还是直接输入。

约束条件的约束类型：这是一个选择项，用于指定约束条件的类型（大于等于，小于等于等）。

非负约束：这是一个复选框，用于指定决策变量是否具有非负约束。

下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。

例1 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。

具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。

产品组合通常必须满足以下约束：● 产品组合使用的资源不能超标。

● 对每种产品的需求都是有限的。

我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。

下面，我们来考虑让某医药公司的最优产品组合问题。

该公司有六种可以生产的药品，相关数据如下表所示。

设该公司生产药品1～6的产量分别为126,,,x x x （磅），则最优产品组合的线性规划模型为123456123456123456123456max 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表示的列向量的内积。

这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。