Excel2003中解决线性规划求解问题.docVIP

Excel2003中解决线性规划求解问题 　　摘要：在工作和教学中通常我们会遇到线性规划的问题，利用Excel2003中的加载宏“规划求解”工具可以方便解决诸如：线性规划、非线性规划、整数规划等多种数学规划问题。而本文通过演示一个线性规划实例来讲解利用Excel2003中“规划求解”解决线性规划求解问题的具体方法和步骤。 　　关键词：线性规划；规划求解；Excel；优化 　　中图分类号：F224.31文献标识码：A文章编号：1671—1580（2013）02—0147—02 　　1.引言 　　在工作和教学中通常我们会遇到线性规划的问题，其实利用常用的Microsoft Office办公应用软件中的Excel2003就可以很容易地解决线性规划的求解问题。 　　2.线性规划和规划求解 　　2.1线性规划 　　线性规划是运筹学中被广泛应用的一个重要分支，是辅助人们进行科学管理的一种数学方法。线性规划应用分析、量化的方法，对管理系统中的有限资源进行统筹规划，为决策者提供最优化方案，以实现科学管理。一般地，求线性目标函数在线性约束条件下的最大值或最小值的问题，统称为线性规划问题。更具体的说线性规划就是在一系列约束条件下，求解某一经济目标最优（最大或最小）值的一种数学方法。 　　2.1.1线性规划的模型建立 　　线性规划的模型建立需要从实际问题中找到三个要素：决策变量、目标函数、约束条件，然后建立问题的数学模型，建立数学模型分以下三个步骤： 　　（1）根据影响所要达到目的的因素找到决策变量；（2）由决策变量和所要达到目的之间的函数关系确定目标函数； （3）由决策变量所受的限制条件确定决策变量所要满足的约束条件。 　　2.1.2线性规划的一般形式如下： 　　Max（Min） Z=c1x1+c2x2+……+cnxn 　　a11x1+a12x2+……+a1nxn=）b1 　　a21x1+a22x2+……+a2nxn=）b2 　　s.t. … 　　an1x1+an2x2+……+annxn=）bn 　　x1，x2，……，xn=0 　　2.2规划求解 　　Microsoft Excel的“规划求解”是Office 2003的一个加载宏，也可以称作假设分析。规划求解的一般方法可以分为以下三个步骤： 　　（1）设置可变单元格，根据已有数据确定用来存放数学模型中的决策变量的单元格。 　　（2）设置目标单元格，在目标单元格中设置取得最优化值的求解公式。 　　（3）规划求解工具中设置相关参数，添加约束条件。 “规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。 　　3.规划求解应用实例 　　某公司生产A、B两种产品，假设生产1台A产品，要消耗90公斤钢材、40公斤铜线、30公斤油，获利0.7万元；生产1台B产品，要消耗40公斤钢材、50公斤铜线、100公斤油，获利1.2万元。假设该公司当前可供利用的各种资源额度是：钢材3600公斤、铜线2000公斤、油3000公斤。请利用规划求解，计算该公司应生产多少台A产品、多少台B产品，才能够获得最大利润？ 　　3.1分析：在此题目中对最终获得最大利润产生影响的决策变量为A、B两种产品产量，故设A、B两种产品产量分别为X1、X2台时，产品利润达到最大化，由决策变量X1、X2和所在达到目的之间的关系确定目标函数，表达为Max Z=1.2 X1+0.7 X2，由决策变量所受的限制条件确定决策变量所要满足的约束条件： 钢材：90X1+40X2=3600；铜线：40 X1+50 X2=2000；油：30 X1+100 X2=3000 　　3.2解：设A、B两种产品产量分别为X1、X2台时，产品利润达到最大化，则该问题的数学模型为： 　　Max Z=1.2 X1+0.7 X2 　　s.t . 90 X1+40 X2=3600 　　40 X1+50 X2=2000 　　30 X1+100 X2=3000 　　X1=0，X2=0 　　解题步骤： 　　第一步，在Excel中描述问题，建立模型 　　假设A产品、B产品目标台数X1、X2分别设置为可变单元格C7和D7，则数学模型在Excel中约束条件的对应表达式为： 　　Max Z=1.2 X1+0.7 X2 　　s.t 90 X1+40 X2=3600 　　40 X1+50 X2=2000 　　30 X1+100 X2=3000 　　X1=0，X2=0=C6*$C$7+D6*$D$7 　　=C3*$C$7+D3*$D$7 　　=C4*$C$7+D4*$D$7 　　=C5*$C$7+D5*$D$7 　　第二步，在“工具”中利用“规划求解”功能，设置规划求解参数，目