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

Excel规划求解操作指南(一) ——线性规划问题的建模与求解 内容摘要：《Excel规划求解操作指南》旨在比较通俗地来说明规划求解的步骤和怎么利用它来解决问题，便于大家自学或查询。本文主要介绍 Excel规划求解的预备知识、线性规划问题的建模初步方法和利用Excel求解线性规划的步骤。 关键词：Excel 线性规划 建模 求解 第二次世界大战以来，运筹学成功地解决了许多经济管理问题，作为一门现代科学得到了广泛应用，规划论是运筹学的最重要的分支。计算机的应用为运筹学的发展提供了强大的支持，利用Excel可以解决通常情况下的规划求解问题。但是，使用过Excel的朋友，很多可能都不了解什么是规划求解，而知道有此功能的朋友，也很少有利用此功能来完成实际问题，或者学习时懂了，学过就忘了。《Excel规划求解操作指南》试图比较通俗地来说明规划求解的步骤和怎么利用它来解决问题，便于大家自学或查询。本文是该操作指南的一部分，其他部分将陆续向大家介绍。 一、预备知识 1、规划求解在OFFICE的重要组件EXCEL中，有一个规划求解的加载宏。加载该宏之后，就可以利用EXCEl的规划求解功能进行规划求解。在EXCEL2003版本中，通过点击菜单【工具】——【加载宏】，加载宏【规划求解】项便可以加载该宏OFFICE光盘，再进行安装。 2、规划求解…..”，得到插入函数对话框 其次，选择“SUMPRODUCT”函数，出现函数参数对话框 再次，在函数参数对话框中，将光标放在Array1，选中第一组数据所在的单元格（例如，单位利润）；将光标放在Array2，选中第二组数据所在的单元格（例如，产量）。确定。 在菜单栏中会出现输入内容以供检查。例如， 其中“=SUMPRODUCT(C2:E2,C4:E4)”表示以下二组单元格数据对应元素乘积的和 而“=SUMPRODUCT(C2:E4,C6:E8)” 表示以下二组单元格数据对应元素乘积的和 二、建立数学模型 数据表格是人们对客观事物感性分别代表三种产品的产量，那么数据表格Ⅰ可以改写为数据表Ⅱ。 表2 数据表Ⅱ 产品 原料 甲 乙 丙 原料拥有量 A 6 3 5 45 B 3 4 5 30 单位利润 4 1 5 产量 由于总利润等于各种产品的单位利润与产量乘积之和，即第四行数据与第五行数据的对应元素乘积之和，利润要求尽可能大；A原料的使用量等于各种产品A原料的单位使用量与产量乘积之和，即第二行数据与第五行数据的对应元素乘积之和；B原料的使用量等于各种产品B原料的单位使用量与产量乘积之和，即第三行数据与第五行数据的对应元素乘积之和；原料的使用量应该小于等于原料拥有量；产量应该是非负数。从而可以容易地写出数学模型： 设分别代表三种产品的产量，表示总利润，则 三、线性规划问题的求解 1、根据线性规划模型求解 求解线性规划问题 利用Excel求解线性规划，实际是理性认识的进一步深化，即从数学模型到电子表格的转换。 第一步 在EXCEL的电子表格中，根据数学模型将变量与系数分离如下： 在电子表格中，B3表示，C3表示，D3表示。电子表格与数据表格Ⅱ在本质上是相同的，只是排列上有些差异。 第二步 在电子表格中输入目标函数表达式和约束条件左边表达式如下： 这样，我们将目标函数的表达式输入到单元格B1中，将第一个约束条件左边的表达式输入到单元格E4中，将第二个约束条件左边的表达式输入到单元格E5中。从而，建立了电子表格。 表3 电子表格Ⅰ 第三步 点击菜单【工具】——【】点击，即安排甲、乙、丙三种产品的产量分别为5，0，3个单位时利润最大，最大利润为35单位，二种原料都恰好全部用完。 小结 根据线性规划模型求解的步骤可以概括为： ①根据数学模型建立电子表格 目标函数 = SUMPRODUCT（★，●） 目标函数系数 ★ ★ ★ ★ 变量 ● ● ● ● 约束条件左边 约束条件右边 约束条件系数 ◆ ◆ ◆ ◆ = SUMPRODUCT（●，◆） ** ▲ ▲ ▲ ▲ = SUMPRODUCT（●，▲） *** ■ ■ ■ ■ = SUMPRODUCT（●，■） **** ②根据数学模型填写规划求解参数表： 规划求解参数表中的目标单元格和可变单元格按上表位置选择； 最大、最小按题目要求选择； 约束条件按题目要求输入，即按约束条件左边和右边的关系输入。 ③根据求解输出的电子表格给出线性规划问题的解。 这个求解过程是线性规划模型的Excel规划求