如何在Excel中建立并求解线性规划模型_第1页
如何在Excel中建立并求解线性规划模型_第2页
如何在Excel中建立并求解线性规划模型_第3页
已阅读5页,还剩2页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、如何在Excel中建立并求解线性规划模型 刘桂莲摘要:数学中线性规划问题的求解一直是很繁琐的,功能强大的Excel软件为我们提供了一种很好的求解方法,但这种方法却很少被人了解。本文就如何在Excel中建立并求解线性规划模型作了较详尽的论述。关键词:线性规划 数学模型 电子表格模型 规划求解 Excel线性规划是运筹学的一个分支,它的应用已愈来愈深入到社会生产和经济活动的各个领域。描述线性规划问题的抽象的数学式子是线性规划问题的数学模型。建立数学模型后,求解满足约束条件的目标函数的最优解是解决线性规划问题的关键。数学中常用的方法是图解法和单纯形法,而图解法只适用于两个变量的目标函数,单纯形法则计

2、算量相当大,步骤烦琐,容易出错。在Excel中建立电子表格模型,并利用它提供的“规划求解”工具,能轻松快捷地求解模型的解。例如,某玻璃制品公司有三个工厂,公司目前决定停止不赢利产品的生产并撤出生产能力来生产两种新开发的产品:玻璃门和双把窗。估计三个工厂每周可用来生产新产品的时间分别为4小时、12小时、18小时,而每扇门需工厂1生产时间1个小时和工厂3生产时间3个小时,每扇窗需工厂2和工厂3生产时间各为2个小时,预测门的单位利润是300元,窗的单位利润是500元,问每周两种新产品数量的哪种组合能使总利润最大?这是一个典型的产品组合问题,现将问题中的有关数据列表如下:工厂生产每一个单位产品所需的时

3、间每周用得时间门窗11小时04小时202小时12小时33小时2小时18小时单位利润300元500元问题的决策变量有两个:每周门的生产数量和窗的生产数量,目标是总利润最大,需满足的条件是:三个工厂每周用于生产新产品的时间每周可得时间每周门、窗的生产数量均0。设每周门的生产数量为x,窗的生产数量为y,则该问题的数学模型即为:最大化利润P =300 x+500y,约束条件:x4,2y12,3x+2y18,x0和y0。将上表的有关数据输入到Excel中,建立如图1所示的电子表格模型。被输入已知数据的单元格是数据单元格,如单元格C5:D8,G5:G7。决策变量(即两种产品每周的生产量)放在单元格C9和D

4、9,正好定位在这些产品所在列的数据单元格下面,这种含有需要做出决策的单元格是可变单元格。单元格E5:E7是用来计算各个工厂每周的总生产时间,如单元格E5就是用C5:D5和C9:D9的对应数值各自相乘再总加得到。Excel中有一个叫SUMPRODUCT的函数能对相等行数和相等列数的两个变化范围的单元格中的值乘积后进行加和。被加和的每个值是对第一个变化范围的一些值和对应位置的第二个变化范围的一些值的积。如E5=SUMPRODUCT(C5:D5,C9:D9)是把C5:D5变化范围的每个值与C9:D9变化范围中对应的每个值相乘,然后各个积相加。同样E6=SUMPRODUCT(C6:D6,C9:D9),

5、E7=SUMPRODUCT(C7:D7,C9:D9),E5、E6、E7 这些单元格的数值是依赖于可变单元格的,它们是输出单元格。单元格F5、F6、F7中的“”符号表示它们左边的总值不允许超过列G中的对应数值,体现了函数的约束条件。目标函数值(利润)被放在E8单元格,正好在用来帮助计算总利润的数据单元格右边,与列E中的其中它的数据相像,它也是一些乘积的加和,E8=SUMPRODUCT(C8:D8,C9:D9),E8是特殊的输出单元格,是显示目标函数值的,是目标单元格。在没有计算之前,可变单元格和输出单元格的数值均显示为0。图1:ABCDEFG1玻璃制品公司的产品组合问题23生产单位产品的时间4门

6、窗时间总数可得时间5工厂1100=46工厂2020=127工厂3320在工具菜单中选择“规划求解”,会弹出一个对话框。在对话框中,将E8键入目标单元格,将C9:D9键入可变单元格,既然目标是要最大化目标单元格,还必须选中“最大值”(Max)2点击对话框中的“添加”按纽(Add),弹出添加约束对话框,将约束条件具体化。左端输入范围E5:E7,右端输入范围G5:G7,中间的符号可选择“点击“选项”按钮,在新弹出的对话框中,选中“采用线性模型”和“假定非负”选项,这就告诉了计算机要求解的问题是一个线性规划问题以及非负约束,点击“确定”再回到“规划求解”对话框。4点击 “求解”按钮,计算机会在后台开始

7、对问题进行求解。几秒钟之后会显示运行结果,一般而言,它会显示已经找到一个最优解。如果模型没有可行解或没有最优解,对话框会显示“规划求解找不到可行解”。求解模型之后,最优值就代替了可变单元格中的初始值,最优解是每周2扇门和6扇窗,目标单元格的对应数值(每周总利润)为3600元。求解的电子表格模型(图2)如下:图2ABCDEFG1玻璃制品公司的产品组合问题23生产单位产品的时间4门窗时间总数可得时间5工厂1102=46工厂20212=127工厂33218=188单位利润30050036009结论26线性规划问题的数学模型是描述实际问题的抽象的数学形式,它反映了客观事物数量间的本质规律。电子表格模型

8、和数学模型两种形式是等价的。电子表格模型的建立不是唯一的,是非常灵活的,一个好的电子表格模型能直观,简便地反映线性规划问题的实质.这两种形式导致了不同但互补的分析问题的方法,但用“规划求解”工具会达到事半功倍的效果。再如:某厂家拥有三个生产婴儿车的工厂,并运往四个配送中心,三个工厂每月的产量分别是12、17、11个运输单位,同时每个配送中心每月要接受10个运输单位的货物,从每一个工厂到每一个配送中心的单位运输成本如下所示:到各配送中心的单位运输成本(元)工厂1234150604020220901030330402010确定每月从每一个工厂之中要运送多少运输单位的婴儿车给相应的配送中心的最佳方案

9、,使总的运输成本最小。用xij(i=1、2、3,j=1、2、3、4)表示从工厂运送到配送中心j的货物量,那么各工厂运送到各配送中心的货物量如下表:工厂1234工厂产量1X11X12X13X14122X21X22X23X24173X31X32X33X3411配送中心接受量10101010该线性规划的数学模型是: 确定xij(i=1、2、3,j=1、2、3、4)的值,使目标函数C成本=50 x11+60 x12+40 x13+20 x14+20 x21+90 x22+10 x23+30 x24+30 x31+40 x32+20 x33+10 x34有最小值。约束条件是:x11+x12+x13+x1

10、4=12x21+x22+x23+x24=17x31+x32+x33+x34=11x11+x21+x31=10 x12+x22+x32=10 x13+x23+x33=10 x14+x24+x34=10 xij0(i=1、2、3,j=1、2、3、4)电子表格模型如图3:图3ABCDEFGHI1婴儿车的配送问题2到各配送中心的单位运输成本312344工厂1506040205工厂2209010306工厂33040201078运送到各配送中心的单位数91234总量工厂产量10工厂100000=1211工厂200000=1712工厂300000=1113总量0000014=15中心接受量10101010模

11、型中的单元格C4:F6,C15:F15,以及I10:I12分别输入了单位运输成本,中心接受量及工厂产量的数值,它们是数据单元格。12个决策变量(各工厂送到各分配中心的单位数)放在单元格C10:F12中,它们是可变单元格,起始值设置为0单元格C13:F13及G10:G12是输出单元格C13=SUMPRODUCT(C10:C12),D13= SUMPRODUCT(D10:D12),E13= SUMPRODUCT(E10:E12),F13= SUMPRODUCT(E10:E12),G10=SUMPRODUCT(C10:F10), G11=SUMPRODUCT(C11:F11), G12=SUMPRO

12、DUCT(C12:F12), G13是目标单元格(总成本),它正好是数据单元格C4:F6和可变单元格C10:F12对应数值相乘再加和得到,即G13=SUMPRODUCT(C4:F6,C10:F12),模型中的等号体现了函数的约束。以上模型通过点击“规划求解”工具,因为是最小化目标单元格G13,所以选“最小值”(Min);在“添加”约束里,添加“G10:G12=I10:I12”和“C13:F13=C15:F15”,仍然选“采用线性模型”和“假定非负”,得出如下结论:(图4) 图4ABCDEFGHI1婴儿车的配送问题2到各配送中心的单位运输成本312344工厂1506040205工厂2209010306工厂33040201078运送到各配送中心的单位数91234总量工厂产量10工厂10201012=1211工厂21007017=1712工厂3083011=1113总量1010101097014=15中心接受量10101010即工厂1运送到配送中心2和配送中心4分别为2和10个单位;工厂2运送到配送中心1和配送中心3分别为10和7个单位;工厂3运送到配送中心2和配送中心3分别8和3个单位,这

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论