[excel2020怎样使用规划求解功能]2020excel规划求解

合集下载

EXCEL中的目标求解与规划求解应用

EXCEL中的目标求解与规划求解应用Excel是一款广泛应用于数据处理和分析的电子表格软件，不仅可以帮助用户处理数据，制作图表，还可以进行目标求解和规划求解。

本文将介绍在Excel中如何应用目标求解和规划求解，以帮助您更好地利用这些功能。

目标求解应用在Excel中，目标求解是指通过设定一些约束条件和目标函数，找到最优解或满足特定条件的解决方案。

这在实际工作中特别有用，比如在制造业中优化生产计划，或者在金融领域中优化投资组合。

通过Excel的目标求解功能，您可以快速设定目标、约束条件和变量范围，让Excel自动搜索最优解，并为您提供优化后的结果。

这大大简化了复杂问题的求解过程，提高了工作效率。

规划求解应用规划求解是指在Excel中通过设定约束条件和目标函数，找到一种最优的决策方案。

这种方法常用于资源分配、成本控制、排程安排等方面的问题。

在Excel中，您可以利用规划求解功能来解决各种决策问题。

通过设定变量、约束条件和目标函数，Excel可以帮助您找到最佳的决策方案，提高工作效率和决策质量。

应用案例举个例子，假设您是一个生产主管，需要确定每种产品的最佳生产量以最大化利润。

通过在Excel中设定产量、成本、销售价格等变量，以及考虑到生产能力和销售需求等约束条件，您可以利用目标求解和规划求解功能找到最佳的生产方案，从而实现利润最大化。

通过本文的介绍，您应该对在Excel中应用目标求解和规划求解有了更深入的了解。

这些功能可以帮助您解决复杂的决策问题，优化资源利用，提高工作效率。

利用Excel的强大功能，您可以更加轻松地应对各种挑战，实现工作目标的快速达成。

Excel作为一款功能强大的电子表格软件，其目标求解和规划求解功能为用户提供了便捷而高效的决策支持。

通过合理应用这些功能，用户可以在工作中更好地处理复杂问题，优化决策方案，提高工作效率，实现更好的业绩表现。

Excel 规划求解工具解决优化问题实例

Excel 规划求解工具解决优化问题实例
一、安装和运行规划求解
要安装规划求解，请单击“工具”菜单上的“加载宏”，然后选择“规划求解”加载宏复选框。

单击“确定”，Excel 将安装规划求解。

安装该加载宏后，您可以通过单击“工具”菜单上的“规划求解”来运行规划求解。

二、定义优化模型
优化模型包括三部分：目标单元格、可变单元格和约束。

目标单元格代表目的或目标。

我们需要最小化或最大化目标单元格。

可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格。

约束是您置于可变单元格中的限制条件。

三、实例运用
已知条件：
运费B1 B2 B3 B4
A1 3 11 3 12
A2 1 9 2 8
A3 7 4 10 5
根据上述条件，怎样调运使总运费最少？
答案如图1、图2所示。

解题步骤如下：
1、确定目标单元格，如图2所示，“总费用”所在列的最后一格
2、确定可变单元格，如图2所示，“运量”所在列为可变单元格。

3、确定约束，如图2所示，“产量”、“销量”所在列的单元格。

附：约束条件单元格运算公式：
B9=SUM(D9+D13+D17)=3 C12=SUM(D9+D10+D11+D12)=7 B10=SUM(D10+D14+D18)=6 C16=SUM(D13+D14+D15+D16)=4 B11=SUM(D11+D15+D19)=5 C20=SUM(D17+D18+D19+D20)=9 B12=SUM(D12+D16+D20=8
目标单元格运算公式：
总费用=运量*运输费F21=SUM(总费用)
图1
图2。

excel规划求解

excel规划求解Excel 是一个非常强大的电子表格软件，可以用于规划和求解各种问题。

下面我将介绍如何使用 Excel 进行规划求解。

首先，我们需要明确问题是什么，并确定目标是什么。

比如，我们想要规划一周的日程安排，目标是合理分配时间，使得能够高效完成工作和休闲。

接下来，我们需要建立一个 Excel 表格。

我们可以在第一列填写任务的名称，比如工作任务、家务任务、个人项目等。

在第二列填写任务的优先级，例如高、中、低。

在第三列填写任务的时间预估，即完成该任务所需的时间。

然后，在第四列中，我们可以使用 Excel 的条件格式功能，设置根据任务优先级分配不同的颜色。

例如，我们可以将高优先级的任务设置为红色，中优先级的任务设置为黄色，低优先级的任务设置为绿色。

接下来，我们可以建立一个总时间的单元格。

我们可以使用SUM 函数来计算任务时间的总和，并将结果显示在总时间单元格中。

这样，我们可以很方便地了解所有任务的总时间。

接着，我们可以利用 Excel 的筛选功能来过滤出合适的任务。

我们可以根据任务的优先级进行筛选，选择优先级高、中等的任务进行安排。

最后，我们可以使用 Excel 的排序功能，根据任务的优先级和时间预估进行排序。

这样，我们可以按照优先级和时间预估的先后顺序进行任务的安排。

通过以上步骤，我们可以使用 Excel 进行规划和求解问题。

Excel 的强大功能和灵活性可以帮助我们高效地完成任务安排，并提高工作效率。

当然，Excel 还有许多其他的功能可以用于规划和求解问题，比如图表分析、数据透视表等。

我们可以根据具体的需求和问题，探索和尝试这些功能，以求得更好的解决方案。

Excel的规划求解

− i + i − 3 + 3 − 2 + 2
− 1
+ 1

3、规划求解一般步骤 1.加载宏 1.加载宏 2.在excel中将模型表示出来 2.在excel中将模型表示出来 3.设置单元格及目标值的确定 3.设置单元格及目标值的确定 4.选定可变单元格 4.选定可变单元格 5.添加约束条件 5.添加约束条件 6.求解 6.求解

5、规划求解结果

2、利用“规划求解”解整数规划问题利用“规划求解”
仍然以上述线性规划问题为例，假定变量全为整数，则只需添加一个约束条件即可：

此时的参数设定

计算结果：计算结果：

整数规划问题的特例0 整数规划问题的特例0—1型问题不难发现，在刚才的例子中，假定变量的取值非不难发现，在刚才的例子中，假定变量的取值非0 型问题，即1，则整数规划问题就转化为，则整数规划问题就转化为0—1型问题，在型问题 excel规划求解中只需在约束条件中添加两个约束条规划求解中只需在约束条件中添加两个约束条件即可：件即可：
Excel的规划求解 Excel的规划求解
一、Excel规划求解预备知识 Excel规划求解预备知识
1.加载“规划求解” 1.加载“规划求解”宏加载
注：宏加载的越多，excel启动越慢，请根据实际情况酌情考虑宏加载的越多，启动越慢，启动越慢

2、“规划求解”各参数解释和设置、规划求解”

2、“规划求解”各参数解释和设置、规划求解”

二、Excel规划求解 Excel规划求解利用“规划求解” 利用“规划求解”解线性规划问题利用“规划求解” 利用“规划求解”解整数规划问题利用“规划求解”解运输问题利用“规划求解” 利用“规划求解” 利用“规划求解”解目标规划问题利用“规划求解” 利用“规划求解”解最短路问题利用“规划求解” 利用“规划求解”解最大流问题利用“规划求解”解数据包络分析(DEA)问利用“规划求解”解数据包络分析问题 • 利用“规划求解”解其他运筹学问题（精确重利用“规划求解” • • • • • • •

Excel规划求解功能的使用教程

本文整理于网络，仅供阅读参考
Excel规划求解功能的使用教程
excel规划求解功能的使用教程：
规划求解使用步骤1：安装规划求解：规划求解是excel的一个插件，需要安装。

打开新建文档左上角office按钮——excel 选项——自定义——从下列位置选择命令(所有命令)——加载宏——添加——确定。

点击“加载宏”工具，弹出【加载宏】对话框，勾选“分析工具库“和”规划求解加载项“，点击”确定“。

随即弹出microsoft office excel对话框，点击”是“。

开始安装。

规划求解使用步骤2：创建表格，如下。

单击“数据“工具栏，选择”规划求解“，随即弹出【规划求解参数】对话框，在【设置目标单元格】中输入“$b$12”;在【可变单元格】中输入“$c$3：$c$5”，单击“添加”按钮，弹出【添加约束】对话框，在【单元格引用位置】输入“$b$10”，在其右侧的下拉列表中选择【看了excel规划求解功能的使用教程。

Excel的规划求解

Excel的函数公式一、在EXCEL中如何从一列数据中找出某些数的和等于一个数字1、首先我们在D3单元格输入一个求和公式：=SUMPRODUCT(A2:A14*B2:B14)2、然后在D4单元格输入一个求差公式：=D1-D3。

3、然后，选择【数据】-【规划求解】。

4、选择【目标单元格】为D4，选择【值】处输入0。

5、点击选择按钮选择【可变单元格】6、区域为B2:B14（即A列数据对应B列区域）。

7、点击【添加】【约束条件】8、具体按下图设置。

9、点击【求解】按钮开始计算求解。

10、运算结束后弹出如下对话框，选择【保存规划求解结果】11、这时B列数值为1对应A列数据之和就等于14。

方法二：规划求解可以用规划求解，以下图中的A1:A20数据为例。

假设要在A1:A20中找出某些数的和等于200，操作步骤如下：步骤1：在C1单元格输入公式=SUMPRODUCT(A1:A20,B1:B20)如下图步骤2：选定C1单元格，数据>>>规划求解，“设置目标”会自动设置为C1单元格，到：选择“目标值”，并在右侧文本框中输入固定的数字200，鼠标放在”通过更改可变单元格“框中，并选择B1:B20，Excel将自动输入单元格地址，再单击“遵守约束”右侧的“添加”按钮，如下图：步骤3：在”单元格引用“用鼠标选择B1:B20单元格，中间的下拉框中选择”bin“，右侧框中将自动显示”十进制“，再单击”确定“按钮，如下图步骤4：通过上步操作后，”遵守约束“列表框中就增加了一个约束”$B$1:$B$20 = 二进制“，单击”求解“按钮，如下图步骤5：单击”确定“按钮，结果如下图所示，B列结果为1的表示对应A列的数字相加的和为C1的值200。

知识扩展：1、如果数据菜单没有”规划求解“命令，开发工具>>>加载项，勾选”规划求解加载项“，再单击”确定“按钮，如下图：2、如果连”开发工具“菜单都没有，操作如下：文件>>>Excel选项>>>自定义功能区>>>勾选”开发工具“，再单击”确定“按钮，如下图。

EXCEL-规划求解

EXCEL-规划求解加载规划求解规划求解加载宏是一个Excel 加载项（加载项：为Microsoft Office 提供自定义命令或自定义功能的补充程序。

）程序，安装Microsoft Office 或 Excel 后即可使用该程序。

但是，要在 Excel 中使用它，您需要先进行加载。

1.在“工具”菜单上，单击“加载宏”。

2.在“可用加载宏”框中，选中“规划求解”旁边的复选框，然后单击“确定”。

提示如果“规划求解”未列出，请单击“浏览”进行查找。

3.如果出现一条消息，指出您的计算机上当前没有安装规划求解，请单击“是”进行安装。

4.单击菜单栏上的“工具”。

加载规划求解后，“规划求解”命令会添加到“工具”菜单中。

更改“规划求解”的求解方法1.在“工具”菜单上，单击“规划求解”。

如果“规划求解”命令没有出现在“工具”菜单上，则需要安装“规划求解”加载宏（加载项：为Microsoft Office 提供自定义命令或自定义功能的补充程序。

）。

操作方法1.在“工具”菜单上，单击“加载宏”。

2.如果在“可用加载宏”框中没有所需的加载宏（加载项：为Microsoft Office 提供自定义命令或自定义功能的补充程序。

），请单击“浏览”，再找到该加载宏。

3.在“可用加载宏”框中，选中待装载的加载宏旁边的复选框，再单击“确定”。

4.如果必要，请按安装程序中的指示进行操作。

2.在“规划求解参数”对话框中，单击“选项”。

3.在“规划求解选项”对话框中，设置下列一个或多个选项:求解时间与迭代次数1.在“最长运算时间”框中，键入限定的最长求解时间（秒数）。

2.在“迭代次数”框中，键入限定的最大迭代次数。

注释如果求解过程在求出结果之前即达到最长求解时间或最大迭代次数，“规划求解”会出现“显示中间结果”对话框。

精度在“精度”框中，键入所要求的精度：该数值越小，精度越高。

允许误差在“允许误差”框中，键入在求解中限定的误差百分比。

excel2020使用solver求解的方法步骤详解

excel2020使用solver求解的方法步骤详解
步骤1：我们按照题目的限制条件在excel中输入如下的表格和公式
步骤2：A和B,的数量可以留空，但是为了让大家了解输入的公式，我们暂时分别填写50,50
步骤3：点击“数据”选项卡中的“规划求解”
步骤4：在弹出的对话框中填入相应的信息;
勾选“最大值”;
通过更改可变单元格：填入A，B产品数量的单元格(C5：C6)下面添加限制公式，点击“添加”
步骤5：输入第一条限制公式：A<=400;点击确定
步骤6：如图遵守约束中出现刚才填写的限制条件，我们再点击“添加”加入另外两条公式;
步骤7：点击“求解”
如图，自动求解出最优解：A=0，B=400;
步骤8：点解“确定”就okay了!。

怎么利用EXCEL求解线性规划

怎么利用EXCEL求解线性规划利用线性回归方法求解生产计划方法一：1、建立数学模型:①设变量：设生产拉盖式书桌x台，普通式书桌y台，可得最大利润②确定目标函数及约束条件目标函数：y=max+115P90x约束条件：200x .....................⑴+y10≤20x .....................⑵4≤+y16128x .....................⑶+y1015≤220,≥yx ..........................⑷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）：200+yx，10≤20左端“单元格所引用位置”选择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）点击“确定”→“求解”，选择“运算结果报告”“敏感性报告”“极限值报告”三项，最后点击“确定”，输出结果：运算结果报告：敏感性报告：极限值报告：- 11 -。

excel2020规划求解的方法

excel2020规划求解的方法
excel2007规划求解的方法：
规划求解步骤1：首先要在Excel中调出规划求解工具。

规划求解步骤2：点击开始——Excel选项
规划求解步骤3：在选项的【加载项】下，看到了【规划求解加
载项】，处于【非活动应用】状态。

规划求解步骤4：点击【转到】，开始激活它
规划求解步骤5：弹出的对话框中，勾选【规划求解加载项】，
点击【确定】
规划求解步骤6：发现，在【数据】选项卡下出现了【规划求解】工具。

点击【规划求解】
弹出一个对话框
规划求解步骤7：【目标单元格】设为E8，即合计的总价。

规划求解步骤8：将目标值设成10000
规划求解步骤9：【可变单元格】设成购买数量
规划求解步骤10：再点击【添加】，添加约束条件。

规划求解步骤11：【引用位置】选择购买量，
约束条件为>=1，表示最小购买量为1。

规划求解步骤12：再点击【添加】，添加另外的约束条件
规划求解步骤13：新的约束条件中，引用位置依旧是购买量，
但约束值选择为【int】【整数】。

表示购买量一定是整数。

规划求解步骤14：点击【确定】
规划求解参数就设置好了。

规划求解步骤15：点击【求解】
结果出来了，购买量都规划出来了，土豪的10000元，我帮他花个精光。

规划求解步骤17：如果还需要试试约束条件，就先【保存方案】，再调整约束条件。

这是运算结果报告。

利用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。

excelsolver（规划求解）的用法及例子

excelsolver（规划求解）的⽤法及例⼦Solve Linear Programming ProblemsCheck that Solver is installedOpen ExcelClick on the ‘tools’ menuIf Solver is listed, then go to Formulation.Otherwise, Solver needs to be installed, as follows:Again under ‘tools’ click ‘Add-ins..’.The window that appears lists the available add-ins,Click the box next to Solver so that it contains a tick, click ok.Solver should now appear under the ‘tools’ menuFormulationWhenever we formulate a worksheet model of a linear program, we perform the following steps (Par. problem as an example, see appendix):Step 1: Enter the data in the worksheetCells B7:C10 show the production requirements per unit for each product.Cells B5:C5 show the profit contributions per unit for the two products.Cells F7:F10 show the number of hours available in each department.Step 2: Specify cell locations for the decision variablesCells B4:C4.Step 3: Select a cell and enter a formulation for computing the objective value function.Cell D5: =B4*B5+C4*C5 or SUMPRODUCT($B$4:$C$4,$B5:$C5)Step 4: Select a cell and enter a formulation for computing the left-hand side of each constraint.Cell D7:=B4*B7+C4*C7 or SUMPRODUCT($B$4:$C$4,$B7:$C7) (copy from Cell D5)Cell D8:=B4*B8+C4*C8 or SUMPRODUCT($B$4:$C$4,$B8:$C8) (copy from Cell D5)Cell D9:=B4*B9+C4*C9 or SUMPRODUCT($B$4:$C$4,$B9:$C9) (copy from Cell D5)Cell D10:=B4*B10+C4*C10 or SUMPRODUCT($B$4:$C$4,$B10:$C10) (copy from Cell D5)Tips:(1)SUMPRODUCT function requires specifying two cell ranges of equal size, separated by a comma, such as SUMPRODUCT($B$4:$C$4,$B5:$C5). The SUMPRODUCT function computes the products of the first entries in each range, second entries in each range, and so on. It then sums these products.(2) The $ symbol in the cells keeps that cell reference fixed when we copy the formula. This is especially convenient since the formula for calculating the sum of the left-hand-side value for each constrain also follows the same structure as the objective function.Excel SolutionThe following steps show how Solver can be used to obtain the optimal solution to the Par, Inc., problem. Step 1: Select the Tools pull-down menu.Step 2: Select the Solver option.Step 3: When the Solver Parameters dialog box appears.Enter D5 into the Set Cell boxSelect the Equal to: Max optionEnter B4:C4 into the By Changing Variable Cells box.Select Add.Step 4: When the Add Constraint dialog box appears:Enter D7:D10 in the Cell Reference boxSelect <=Enter F7:F10 into the Constraint boxClick OKStep 5: When the Solver Parameters dialog box reappears:Choose Options.Step 6: When the Solver Options dialog box appears,Select Assume Linear Models and Assume Non-negativeClick OK.Step 7: When the Solver Parameters dialog box reappears:Choose Solve.Step 8: When the Solver Results dialog box appears:Select Keep Solver Solution, and choose Answer and Sensitivity from Reports box. The following table shows Excel layout for the Par. problem.The answer report for the Par. problem is:Answer the following questions:1.a.Which constraints are binding? Which are not binding?b.What is the range of optimality for the objective function coefficient associated with standardbags?c.What is the range of optimality for the objective function coefficient associated with deluxe bags?d.After the production, how many hours remain in finishing, and inspection and packagingdepartment?e.What would be the impact on the production plan and profit if the objective function coefficientassociated with standard bags were to change to 12?f.What would be the impact on the production plan and profit if the number of sewing departmentwere to decrease to 500?g.What would be the impact on the production plan and profit if the objective function coefficient associated with standard bags were to change to 9 while at the same time the objective function coefficient associated with deluxe bag were to change to 8?2. Solve M&D Problem. (Answer: Obj=800)3. Solve PM Problem. (Answer: Obj=216,300)4. Solve MSA Problem. (Answer: Obj=15,166)5. Solve Whole Wood Problem. (Answer: Obj=0.05)。

Excel规划求解的使用

§9.6 Excel软件“规划求解”的使用用Excel软件的“规划求解”功能可以方便地求解线性规划、整数规划和非线性规划问题。

但如果安装Office 97时采用的是典型安装方法，则【工具】菜单中是无“规划求解”功能项的。

可参照§2.8中介绍的方法将未安装的组件安装完整。

下面以第八章例8.1为例介绍用Excel求解线性规划的操作步骤和运行输出结果的分析。

一．求解线性规划的操作过程1．输入数据、公式和说明文字（1）在工作表中按图9.7所示格式输入必要的说明文字（图中粗体字部分）和LP模型的原始数据（图中虚线框所示单元格内，注意并不需要化为标准型）；图中F4是放置目标函数的单元格，B5:D5是放置决策变量X1、X2、X3（既“可变单元格”）的区域。

图9.7（2）在F4单元格内输入目标函数X0的计算公式：=B4*B5+C4*C5+D4*D5或=SUMPRODUCT（B4:D4,B5:D5）其中SUMPRODUCT（）函数返回两个或多个区域（即数组）中对应单元格乘积之和的值。

该函数可在Excel的“数学和三角函数”中找到。

（1）在E8单元格中输入第一个约束条件左端的计算公式：=B8*$B$5+ C8*$C$5+D8*$D$5或= SUMPRODUCT（B8:D8,$B$5:$D$5）然后拖曳E8的填充柄将公式复制到E9、E10单元格（注意公式中的B5、C5、D5或B5:D5要使用绝对引用）。

当模型中的变量数较多时，使用SUMPRODUCT（）函数可大大加快以上两个公式的输入速度。

说明：图中粗线框是表示要输入公式的单元格。

用Excel求解线性规划的数据输入格式可由用户自行设计，但以上介绍的格式不仅与我们所熟悉的LP模型相似，便于理解和使用；而且便于在对话框中输入约束条件。

按以上格式输入说明文字后，还可以使系统所输出的三个运行结果报告更具可读性。

2．选【工具】→“规划求解”，“打开规划求解参数”对话框，见图9.8。

如何在不同Excel版本中找到“规划求解”选项

如何在不同Excel版本中找到“规划求解”选项如果你的Excel是2003版得话，步骤如下：
1.打开Excel程序，选择“工具”→“加载宏”
2.在“Solver Add-in”的复选框中打上“√”，然后点击确定：
3.这时再打开“工具”菜单栏，就能找到“规划求解”了：
4.点击一下看看长什么样子的：
如果你使用的是Excel2007,那么找到规划求解的步骤如下：
1.首先找到“Exc el选项”
2.然后在“加载项”里面找到“转到”，点击“转到”
3.在“规划求解加载项”前面的复选框中打上“√”，然后点击确定：
4.最后在“数据”选项卡的“分析”组里面能找到它：
5.点击打开看看，貌似长的跟2003里面的没什么区别：最后看看Excel 2010的规划求解藏在哪里吧：
1.首先选择“文件”找到打开里面的“选项”
2.选中“自定义功能区”，在这两列的右列找到“开发工具”选项卡，并在它前
面的复选框中打上“√”，然后点击确定：
3.然后在“开发工具”选项卡中“加载项”组里面点击“加载项”
4.点击“规划求解加载项”前面的复选框，然后点击“确定”
5.然后像在2007中一样，去“数据”选项卡找到它：
6.点开2010中的“规划求解”，会发现界面有变化了（我指的不是英文菜单）：。

EXCEL规划求解宏包的使用简介

EXCEL规划求解宏包的使用简介王緌四川大学锦城学院工商管理系EXCEL规划求解宏包是EXCEL软件上附带的一款非常好用的解决线性系统优化问题的工具，也是国内外学习运筹学必备的工具之一。

下面，结合教材，将简单地为大家介绍其用法。

(1) 加载“规划求解”宏包①打开EXCEL, 选择菜单中“工具”—“加载宏”(图1)图1②选择“规划求解”宏包(图2), 点击“确定”, 即可完成加载.图2 图3加载后, 再打开工作簿窗口上方菜单中的“工具”选项, 则可见其中出现了“规划求解”项(见图3).如果一些同学安装的OFFICE系统,不能正确加载此宏包, 则可以在打开EXCEL后, 找到硬盘上OFFICE安装位置中的“SOLVER.XLA”程序, 双击打开它, 即可实现在EXCEL 中的加载. 此程序文件一般的路径为：C:\Program Files\Microsoft Office\OFFICE11\Library \SOLVER.(2) 利用“规划求解”宏包求解线性规划问题下面将结合例1说明. 例1 max z =3x 1＋2x 22x 1＋ 3x 2≤14 ① x 1＋0.5x 2 ≤4.5 ② x 1, x 2≥0 a 基本术语价值向量c: 变量在目标函数中的系数的总称. 本例中c=(3 2).资源向量b: 资源约束不等式右端的常数项的总称, 它反映了资源总量的限制. 本例中b=()144.5, 即第一、二种资源的总量限制分别为14和4.5. 系数矩阵A: 资源约束不等式左端的函数部分中变量系数的总称. 本例中A=()2310.5. 资源的实际耗用: 资源约束不等式左端的函数部分. 本例有两个资源约束, 资源约束①的左端函数部分为2x 1＋3x 2, 此即第一种资源的实际耗用; 资源约束②的左端函数部分为x 1＋0.5x 2, 此即第二种资源的实际耗用.关系: 指资源约束中不等式左端函数部分与不等式右端的常数项之间的关系, 即资源的实际耗用与该资源总量之间的关系, 可用关系符“≤”或“≥”或“=”来描述. 本例中资源约束①、②的左端函数部分, 即第一、二种资源的实际耗用, 均被要求小于等于其各自的资源总量, 故均用“≤”来表示.约束条件: 其资源约束形态一般有三种, “资源实际耗用(≤ , ≥, =)资源总量”, 每个约束包括三个组成部分, “资源实际耗用”、“关系”、资源总量. 本例两个资源约束条件均为类型“资源实际耗用≤资源总量”.单元格: EXCEL 表上的每个空格被称为单元格, 并且分别用A, B, C, D, …, Z, AA, AB, AC, …, AZ,…等字母和1,2,3,4,5,…等数字来标示单元格所在的行和列的位置, 如“C5”就表示该单元格在C 列5行上, 见图4.图4b 模板设计将系数矩阵A 、价值向量c 、资源实际耗用、资源实际耗用与资源总量限制间的关系、资源向量b 等栏目分别在EXCEL 表上选用不同列的单元格放置.除此, 还要选择一些单元格来盛放模型求解后变量与目标函数的值, 这些单元格分别被称为变量单元格和目标单元格.例1的求解模板设计如图5. 其中, 可看出系数矩阵A 、价值向量c 、资源实际耗用、资源实际耗用与资源总量限制间的关系、资源向量b 等栏目分别被置于EXCEL 表上的B4~C5, B6~C6, D4~D5, E4~E5, F4~F5等单元格.而单元格H4~H5被设计为变量单元格, 它们用来盛放例1中两个x 1, x 2的解. 单元格H6被设计为目标单元格.c 数据录入将例1的系数矩阵A 、价值向量c 、资源实际耗用与资源总量限制间的关系“≤”、资源向量b 按图5方式分别输入EXCEL 表中的B4~C5, B6~C6, E4~E5, F4~F5等单元格. 其中, A=()2310.5, c=(3 2), b=()144.5.图5 例1的模板设计d 函数输入将模型基础数据录入后, 就可以将每种资源的实际耗用, 即每个资源约束左端的函数部分以及模型的目标函数输入到预先规划好的单元格中.记住, 例1中两个变量x 1, x 2的单元格被预先设计在H4和H5, 所以在下面函数的输入中, 将分别用H4, H5代替函数中的变量符号x 1, x 2.目标函数的输入例1模型中目标函数为3x 1＋2x 2, 则在目标单元格H6上操作如下.鼠标点击单元格H6, 输入“=”, 此时, “=”自动出现在上面菜单中的“f x ”栏上, 在“=”后接着输入“3*H4+2*H5”, 点击旁边的“√”即可完成输入, 目标单元格H6出现了值“0”或者“#value ”.图6 例1目标函数的输入变量单元格目标单元格实际耗用的输入例1模型中两种资源的实际耗用分别为2x1＋3x2和x1＋0.5x2, 即资源约束①、②左端的函数部分. 下面, 只须将这两个函数赋予给它们在模板设计时被预先规划好的位置—单元格D4, D5中即可.方法与目标单元格的函数输入一致, 鼠标点击单元格D4, 输入“=”, 此时, “=”自动出现在上面菜单中的“f x”栏上, 在“=”后接着输入“2*H4+3*H5”, 点击旁边的“√”即可完成输入, 单元格D4出现了值“0”或者“#value”.将函数“H4+0.5*H5”输入单元格D5的过程同理，不再累述.实际耗用单元格和目标单元格完成相应的函数输入后的形态见图7.图7 实际耗用函数和目标函数输入完成后的形态e 模型求解先鼠标点击目标单元格H6调用规划求解宏包. 打开菜单“工具”——“规划求解”, 见图8.图8 调用规划求解宏包设置规划求解参数规划求解宏包选择后, 会出现规划求解参数设置菜单, 见图9.图9 规划求解参数菜单据模型, 逐一设置图9中的各项规划求解所需参数, 过程如下.(a) 目标单元格设置. 选项“设置目标单元格”中会自动出现例1的目标单元格的值“$H$6”, , 所以不必管它.(b) 目标类型设置. EXCEL默认为求“最大值”, .而例1正好是MAX问题, 所以不用改变此选项. 若模型是MIN问题, 则只需点击“最小值”选项, 即可调整.(c) 可变单元格设置. 其作用是告知EXCEL, 模型变量单元格的位置. 由于例1中变量x1,x2的单元格被规定在H4, H5上, 所以按此输入“$H$4:$H$5”即可. 此处“:”的意思为H4至H5, “$”的意思为相应单元格的值, 打开EXCEL的HELP可寻找到该符号的详细解释. 另一种常用的可变单元格设置方式为: 先将鼠标在该空白选项栏上点击一下,, 然后鼠标移动回工作簿上, 左键点击放置变量的第一个单元格H4, 然后按住不放, 拖动鼠标到放置变量的最后一个单元格H5, 松开鼠标, 此时规划求解参数菜单中可变单元格栏同样会出现“$H$4:$H$5”.(d) 约束条件设置. 此步骤作用为输入模型中的约束条件. 由前面知道, 约束条件有三个部分: “资源实际耗用”、关系“≤, ≥,=”和“资源总量”, 将它们分别输入到相应参数选项中, 即可完成约束条件的输入.做法为: 先将鼠标在“约束”选项下的空白栏处点击一下，然后点击旁边的“添加”项, 出现“添加约束”菜单，见图10.图10 调出“添加约束”菜单继而, 将例1中约束条件的三个组成部分“资源实际耗用”和“资源总量”所在单元格分别填入“添加约束”菜单中的“单元格引用位置”和“约束值”选项中, 同时点击该菜单中间的按钮, 选择资源耗用和资源总量间的关系类型即可. EXCEL一般默认关系为“<=”, 即“≤”.由于例1中资源实际耗用单元格为D4: D5, 资源总量单元格为F4:F5, 二者间关系均为“≤”, 故可按图11中左图所示填入. 图11中右图描述了关系类型的多种选择.除此, 也可采用点击工作簿相应位置的单元格来完成约束条件中资源实际耗用和资源总量的输入: 点击“添加约束”菜单中“单元格引用位置”选项, 然后移动鼠标回工作簿, 左键点击放置第一种资源实际耗用函数的第一个单元格D4, 然后按住不放, 拖动鼠标到放置第二种资源实际耗用函数的单元格D5, 松开鼠标, 此时“添加约束”菜单中“单元格引用位置”选项栏会出现“$D$4:$D$5”.同理, 点击“添加约束”菜单中“约束值”选项, 然后移动鼠标回工作簿, 左键点击放置第一种资源总量的第一个单元格F4, 然后按住不放, 拖动鼠标到放置第二种资源总量的单元格F5, 松开鼠标, 此时“添加约束”菜单中“单元格引用位置”选项栏会出现“$F$4:$F$5”.图11 添加约束当还有约束条件需输入时, 可点击“添加约束”菜单中的“添加”选项, 重复上面的操作.当所有约束均完成输入后, 点击“添加约束”菜单中的“确定”选项, 回到前面的“规划求解参数”菜单, 点击其上的“选项”, 见图12.图12 求解参数设置完成后的形态(e) 规划求解选项的设置点击选择“选项”菜单中的“采用线性模型”和“假定非负”, 点击“确定”回到“规划求解参数”菜单, 见图13.图13 规划求解选项的设置求解结果点击“规划求解参数”菜单上的“求解(S)”, 见图14; 出现“规划求解结果”菜单,图15, 根据需要点击选择“报告”栏下面的“运算结果报告”、“敏感性报告”和“极限值报告”, 最后点击“确定”, 获得求解结果.工作簿中, 两个变量的优解值、目标函数最优值、两种资源的实际耗用值被展示在原来设置的单元格H4: H5, H6, D4: D5中, 见图16.图 14 点击求解按钮图15 选择求解报告类型图16 工作簿中的求解结果同时, EXCEL上涌现了一些新工作簿放置运算结果、敏感性和极限值报告.(3) 报告阅读a 运算结果报告图17 运算结果报告最优目标函数值最优解两种资源的实际耗用值, 即资源约束左端函数b 敏感性报告图18 敏感性报告价值向量c 的灵敏度分析图18中方框显示,变量x 1在目标函数中系数3在[3-1.666666667, 3+1]范围内变化时, 即[4/3, 4], 现最优解x=(3.25, 2.5)T 不变.变量x 2在目标函数中系数2在[2-0.5, 2+2.5]范围内变化时, 即[1.5, 4.5], 现最优解x=(3.25, 2.5)T 不变.x 1, x 2的递减成本均为0.两种资源的影子价格分别为0.25, 2.5. 资源向量灵敏度分析图18中椭圆显示,第一种资源总量现有14, 它在[ 14-5, 14+13], 即[9, 27]范围内变化时, 现最优基不变, 现在的影子价格不变, 仍为0.25.第二种资源总量现有4.5, 它在[ 4.5-2.1666666667, 4.5+2.5], 即[7/3, 7]范围内变化时, 现最优基不变, 现在的影子价格不变, 仍为2.5.关于影子价格和递减成本的经济含义和应用, 可参见《LINDO 简介》.(4) 高级技巧EXCEL 规划求解宏包的使用过程中, 在实际耗用函数和目标函数的输入中还有一些更简便的方法, 不过需要用到EXCEL 本身自带的一些函数功能.a 目标函数的多种输入方式方式1. 见图19, .在目标单元格H6上直接输入 =3*H4+2*H5. 目标函数中变量系数3, 2是直接输入的.图19 目标函数中变量系数3, 2是直接输入的情况最优解影子价格递减成本方式2. 见图20. 由于目标函数中变量系数3,2已预先输入单元格B6, C6中, 故在目标单元格H6上可输入 =B6*H4+C6*H5. 这样, 在输入目标函数过程中, 可通过: 直接点击B6, 再输入*号, 再点击H4单元格, 再输入+号, 再点击C6单元格, 再输入*, 再点击H5, 最后完成输入, 即目标函数中变量系数可通过点击系数所在单元格完成系数的输入.图20 目标函数中变量系数可通过点击系数所在单元格完成系数的输入方式3. 见图21. EXCEL提供了大量函数可供更方便的计算. 由于例1目标函数3x1+2x2可写成向量矩阵乘积的形式 (3 2)()12x x, 故可用EXCEL中MMULT这个函数. 当使用MMULT(A, B)命令时, 就可完成矩阵乘积运算A n×m·B m×q.输入过程: 点击目标单元格H6, 输入“=”, 如图21, 点击打开函数菜单, 选择其中的“MMULT”. 出现函数参数菜单, 见图22, 鼠标点击“Array1”空白栏, 然后移动回工作簿价值向量所在单元格, 左键点击B6, 按住并拖动鼠标至C6, 松开鼠标, 就完成了价值向量c=(3 2)的输入; 鼠标点击“Array2”空白栏, 然后移动回工作簿变量所在单元格, 左键点击H4, 按住并拖动鼠标至H5, 松开鼠标, 就完成了变量向量()12x x的输入. 最后, 点击MMULT 的“函数参数”菜单中的“确定”项, 这样就完成了目标函数在单元格H6上的输入.图21 在函数库中寻找矩阵乘法函数MMULT图22 MMULT函数参数菜单b 资源耗用函数的多种输入方式方式1. 见图. 第一种资源实际耗用函数2x 1+3x 2中变量系数2, 3是直接输入的情况.图23第一种资源实际耗用函数中变量系数2, 3是直接输入的情况方式2. 见图24. 第一种资源实际耗用函数2x 1+3x 2在输入时利用了变量系数2,3所在的单元格B4, C4, 输入形式为 =B4*$H$4+C4*$H$5, 其中$H$4, $H$5分别表示H4, H5单元格中的数值, 它们不会因为鼠标的拖动而发生数值指针的位移. 单元格D4中的公式输入完成后, 就可以通过拖动鼠标到D5, 而轻松完成第二种资源实际耗用函数在D5单元格上的输入, 见图25.图24 第一种资源实际耗用函数中的系数可通过点击系数所在单元格完成系数的输入拖动D4完成D5单元格的输入过程为: 将鼠标移动到D4单元格的右下角, 此时鼠标自动转换为十字形“＋”, 见图25的左图，然后按住鼠标左键并拖动至D6单元格，即可将第二种资源实际耗用函数输入至D6单元格，其公式可在图25 的右图上方的f x 栏看到.图25 拖动鼠标完成其它单元格的函数输入方式3. 比方式2更简洁的输入方式是直接利用MMULT 函数, 将两种资源的实际耗用函数同时求出来, 并放在单元格D4:D5中. 因为两种资源的实际耗用()()()12112223230.510.5x x x Ax x x x +==+. 过程如下: 点击单元格D4, 并按住鼠标左键, 拖动至D5单元格, 松开鼠标, 输入“=”, 并在EXCEL 函数库菜单中点击MMULT, 见图26. 在出现的MMULT 函数参数菜单中的“Array1”和“Array2”中首先通过鼠标点击系数矩阵A 的单元格B4:C5, 完成其在“Array1”中的输入; 然后通过鼠标点击变量单元格H4:H5, 完成其在“Array2”中的输入; 接下来同时按动“ctrl+shift+enter”, 完成两种资源实际耗用函数的同时输入, 见图27.图26 两个单元格同时使用MMULT函数Ctrl+shift+enter图27 MMULT函数参数菜单此时, 工作簿上单元格D4:D5出现“#V ALUE!”, 菜单中“f x”为{=MMULT(B4:C5),H4:H5}.。