Excel规划求解操作指南线性规划问题的建模与求解

Excel规划求解操作指南(一)——线性规划问题的建模与求解内容摘要：《Excel规划求解操作指南》旨在比较通俗地来说明规划求解的步骤和怎么利用它来解决问题，便于大家自学或查询。

本文主要介绍Excel规划求解的预备知识、线性规划问题的建模初步方法和利用Excel求解线性规划的步骤。

关键词：Excel 线性规划建模求解第二次世界大战以来，运筹学成功地解决了许多经济管理问题，作为一门现代科学得到了广泛应用，规划论是运筹学的最重要的分支。

计算机的应用为运筹学的发展提供了强大的支持，利用Excel可以解决通常情况下的规划求解问题。

但是，使用过Excel的朋友，很多可能都不了解什么是规划求解，而知道有此功能的朋友，也很少有利用此功能来完成实际问题，或者学习时懂了，学过就忘了。

《Excel规划求解操作指南》试图比较通俗地来说明规划求解的步骤和怎么利用它来解决问题，便于大家自学或查询。

本文是该操作指南的一部分，其他部分将陆续向大家介绍。

一、预备知识1、规划求解程序安装在OFFICE的重要组件EXCEL中，有一个规划求解的加载宏。

加载该宏之后，就可以利用EXCEl的规划求解功能进行规划求解。

在EXCEL2003版本中，通过点击菜单【工具】——【加载宏】，在加载宏对话框中选择【规划求解】项，便可以加载该宏。

如果计算机提示无法安装，那么需要插入OFFICE光盘，再进行安装。

2、规划求解的常用函数（1）SUM函数利用SUM函数，可以自动求出所选中的单元格数据的和。

首先，选中输出结果的单元格，输入“=”，在下拉菜单中选择“SUM”，得到函数参数对话框然后，将光标放在Number框中，选中需要求和的单元格，确定。

在菜单栏中会出现输入内容以供检查。

例如，其中“=SUM(E2:G3)”表示对如下单元格的数据求和：（2）SUMPRODUCT函数，利用SUM函数，可以自动求出一组数据与另一组数据对应元素乘积的和。

例如，总利润等于各种产品的单位利润与产量乘积之和；总成本等于各种产品的单位成本与产量乘积之和。

首先，选中输出结果的单元格，输入“=”，在下拉菜单中选择“其他函数…..”，得到插入函数对话框其次，选择“SUMPRODUCT”函数，出现函数参数对话框再次，在函数参数对话框中，将光标放在Array1，选中第一组数据所在的单元格（例如，单位利润）；将光标放在Array2，选中第二组数据所在的单元格（例如，产量）。

确定。

在菜单栏中会出现输入内容以供检查。

例如，其中“=SUMPRODUCT(C2:E2,C4:E4)”表示以下二组单元格数据对应元素乘积的和而“=SUMPRODUCT(C2:E4,C6:E8)” 表示以下二组单元格数据对应元素乘积的和二、建立数学模型数据表格是人们对客观事物感性认识的归纳，数学模型是人们对客观事物的抽象认识，是一种理性认识。

如何让学生根据数据表格建立数学模型，这实际上是思维过程中由感性认识到理性认识的转变。

由直观的感性认识到抽象的数学表示，往往要经过复杂的思维过程，但有时一个简单的变化就能实现这一过程。

例 某厂生产甲、乙、丙三种产品，产品的单位利润分别为4、1、5单位。

每生产一个单位甲产品消耗的原料A 、B 分别为6、3单位，每生产一个单位乙产品消耗的原料A 、B 分别为3、4单位，每生产一个单位丙产品消耗的原料A 、B 分别为5、5单位。

原料A 、B 的拥有量分别为45、30单位。

问如何安排生产计划，使得利润最大？生产是资源投入转换为产品和服务的过程。

根据例题中实际问题的相关数据，原料(投入)和产品（产出）的关系可以用表1表示。

表1 数据表Ⅰ在数据表Ⅰ中添加一行表示产量，设123,,x x x 分别代表三种产品的产量，那么数据表格Ⅰ可以改写为数据表Ⅱ。

由于总利润等于各种产品的单位利润与产量乘积之和，即第四行数据与第五行数据的对应元素乘积之和，利润要求尽可能大；A 原料的使用量等于各种产品A 原料的单位使用量与产量乘积之和，即第二行数据与第五行数据的对应元素乘积之和；B 原料的使用量等于各种产品B 原料的单位使用量与产量乘积之和，即第三行数据与第五行数据的对应元素乘积之和；原料的使用量应该小于等于原料拥有量；产量应该是非负数。

从而可以容易地写出数学模型：设123,,x x x 分别代表三种产品的产量，()f x 表示总利润，则123123123123()max ()4563545..345300,0,0f x x x x x x x s t x x x x x x =++++≤⎧⎪++≤⎨⎪≥≥≥⎩数据表格II 中第四行与第五行对应元素乘积之和（数据表格II 中第二行与第五行对应元素乘积之和）（数据表格II 中第三行与第五行对应元素乘积之和）三、线性规划问题的求解1、根据线性规划模型求解 求解线性规划问题123123123123()max ()4563545..345300,0,0f x x x x x x x s t x x x x x x =++++≤⎧⎪++≤⎨⎪≥≥≥⎩数据表格II 中第四行与第五行对应元素乘积之和（数据表格II 中第二行与第五行对应元素乘积之和）（数据表格II 中第三行与第五行对应元素乘积之和） 利用Excel 求解线性规划，实际是理性认识的进一步深化，即从数学模型到电子表格的转换。

第一步 在EXCEL 的电子表格中，根据数学模型将变量与系数分离如下：在电子表格中，B3表示1x ，C3表示2x ，D3表示3x 。

电子表格与数据表格Ⅱ在本质上是相同的，只是排列上有些差异。

第二步 在电子表格中输入目标函数表达式和约束条件左边表达式如下：这样，我们将目标函数的表达式输入到单元格B1中，将第一个约束条件左边的表达式输入到单元格E4中，将第二个约束条件左边的表达式输入到单元格E5中。

从而，建立了电子表格。

表3 电子表格Ⅰ第三步 点击菜单【工具】——【规划求解】，显示规划求解参数表。

采用选择单元格的方法填写规划求解参数表：目标单元格就是目标函数表达式所在的单元格B1，选中B1；目标函数要求达到最大值，故选择“最大值”；可变单元格就是变量所在的单元格B3、C3、D3，选中B3、C3、D3。

点击“添加”，显示添加约束对话框，填写添加约束对话框，单元格E4是第一个约束条件左边的表达式，F4是第一个约束条件右边数值，因此，“E4<=F4”表示第一个约束条件。

同理，“E5<=F5”表示第二个约束条件。

添加约束对话框表示变量都要大于0。

点击“确定”，规划求解参数表如下：第四步按求解按钮，马上可以求解得：求解结果表明，最优值为35，最优解为 1235,0,3x x x ===，即安排甲、乙、丙三种产品的产量分别为5，0，3个单位时利润最大，最大利润为35单位，二种原料都恰好全部用完。

小结 根据线性规划模型求解的步骤可以概括为：②根据数学模型填写规划求解参数表：规划求解参数表中的目标单元格和可变单元格按上表位置选择； 最大、最小按题目要求选择；约束条件按题目要求输入，即按约束条件左边和右边的关系输入。

③根据求解输出的电子表格给出线性规划问题的解。

这个求解过程是线性规划模型的Excel 规划求解的一般方法。

数学模型的特征在电子表格中体现为：目标函数系数、约束条件系数、目标函数表达式和约束条件左端表达式，以及在规划求解参数表中约束条件左端与右端的关系。

2、根据数据表格求解比较数据表格Ⅰ和电子表格Ⅰ可知，数据表格Ⅰ来自实际问题，具有较强的经济意义；电子表格Ⅰ来自数学模型，经济意义被淡化了，但是具有普遍意义，反映了线性规划模型Excel 求解的一般方法。

从形式上看，数据表格Ⅰ和电子表格Ⅰ都有反映技术经济条件和单位利润的系数行、资源约束列，但是电子表格Ⅰ增加了变量行、反映目标函数的单元格和反映资源使用情况的约束条件左边表达式列。

因此，在数据表格Ⅰ中直接增加变量行、反映目标函数的单元格和反映资源使用情况的约束条件左边表达式列，可以直接建立电子表格Ⅱ，如表4。

表4 电子表格Ⅱ规划求解参数表为从而，可以直接求解得到甲、乙、丙三种产品的产量分别为5，0，3单位时利润最大，最大利润为35单位。

电子表格Ⅱ与数据表格Ⅰ在形式上很相似，但是与数据表格Ⅰ又有本质的不同，在电子表格Ⅱ中加入了目标函数表达式（总利润）、约束条件左端表达式（原料使用量），在规划求解参数表中加入了约束条件左端与右端的关系（原料使用量小于等于原料拥有量）。

电子表格不同于数据表格，是数据表格的高级形式。

电子表格Ⅱ与电子表格Ⅰ具有相同的本质，但是电子表格Ⅱ具有明显的实际意义。

根据数据表格Ⅰ的高级形式电子表格Ⅱ直接求解线性规划问题，隐去了建立数学模型的过程，在认识上具有更强的直观性和经济意义，在使用上更为简洁，是一种创造性的思维方式。

对数据表格进行完善，进而直接求解线性规划问题是感性认识和理性认识相结合的完美过程。

线性规划问题的Excel规划求解过程可以看成是一个由实际问题→数据表格→数学模型→电子表格的思维过程。

在这个过程中，不仅要揭示客观事物的本质及内在联系，而且要在此基础上产生新颖的、前所未有的思维成果，它给人们带来新的、具有社会价值的产物，它是智力水平高度发展的表现。

参考文献[1] 叶佰英李艳敏，运筹学基础--建模与Excel求解，上海科技出版社，2005[2] 叶向，实用运筹学--运用Excel建模和求解，中国人民大学出版社，2007宝山业大李艳敏叶佰英。