当前位置：文档之家 › 如何在Excel中建立并求解线性规划模型

如何在Excel中建立并求解线性规划模型

  • 格式：doc
  • 大小：147.00 KB
  • 文档页数：5

1 如何在Excel中建立并求解线性规划模型 刘桂莲

摘要：数学中线性规划问题的求解一直是很繁琐的，功能强大的Excel软件为我们提供了一种很好的求解方法，但这种方法却很少被人了解。本文就如何在Excel中建立并求解线性规划模型作了较详尽的论述。 关键词：线性规划 数学模型 电子表格模型 规划求解 Excel

线性规划是运筹学的一个分支，它的应用已愈来愈深入到社会生产和经济活动的各个领域。描述线性规划问题的抽象的数学式子是线性规划问题的数学模型。建立数学模型后，求解满足约束条件的目标函数的最优解是解决线性规划问题的关键。数学中常用的方法是图解法和单纯形法，而图解法只适用于两个变量的目标函数，单纯形法则计算量相当大，步骤烦琐，容易出错。在Excel中建立电子表格模型，并利用它提供的“规划求解”工具，能轻松快捷地求解模型的解。 例如，某玻璃制品公司有三个工厂，公司目前决定停止不赢利产品的生产并撤出生产能力来生产两种新开发的产品：玻璃门和双把窗。估计三个工厂每周可用来生产新产品的时间分别为4小时、12小时、18小时，而每扇门需工厂1生产时间1个小时和工厂3生产时间3个小时，每扇窗需工厂2和工厂3生产时间各为2个小时，预测门的单位利润是300元，窗的单位利润是500元，问每周两种新产品数量的哪种组合能使总利润最大？ 这是一个典型的产品组合问题，现将问题中的有关数据列表如下：

工厂 生产每一个单位产品所需的时间 每周用得时间 门 窗

1 1小时 0 4小时 2 0 2小时 12小时 3 3小时 2小时 18小时 单位利润 300元 500元 问题的决策变量有两个：每周门的生产数量和窗的生产数量，目标是总利润最大，需满足的条件是：⑴三个工厂每周用于生产新产品的时间≤每周可得时间⑵每周门、窗的生产数量均≥0。设每周门的生产数量为x，窗的生产数量为y，则该问题的数学模型即为：最大化利润P =300x+500y，约束条件：x≤4，2y≤12，3x+2y≤18，x≥0和y≥0。 将上表的有关数据输入到Excel中，建立如图1所示的电子表格模型。被输入已知数据的单元格是数据单元格，如单元格C5：D8，G5：G7。决策变量（即两种产品每周的生产量）放在单元格C9和D9，正好定位在这些产品所在列的数据单元格下面，这种含有需要做出决策的单元格是可变单元格。单元格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)，E7=SUMPRODUCT(C7：D7，C9：D9)， 2

E5、E6、E7 这些单元格的数值是依赖于可变单元格的，它们是输出单元格。单元格F5、F6、F7中的“≤”符号表示它们左边的总值不允许超过列G中的对应数值，体现了函数的约束条件。目标函数值（利润）被放在E8单元格，正好在用来帮助计算总利润的数据单元格右边，与列E中的其中它的数据相像，它也是一些乘积的加和，E8=SUMPRODUCT(C8:D8，C9:D9)，E8是特殊的输出单元格，是显示目标函数值的，是目标单元格。在没有计算之前，可变单元格和输出单元格的数值均显示为0。 图1： A B C D E F G 1 玻璃制品公司的产品组合问题 2 3 生产单位产品的时间 4 门 窗 时间总数 可得时间 5 工厂1 1 0 0 <= 4 6 工厂2 0 2 0 <= 12 7 工厂3 3 2 0 <= 18 8 单位利润 300 500 0 9 结论 0 0

Excel中有一个叫 “规划求解”的工具，能快速求解线性规划问题，步骤如下： 1>在工具菜单中选择“规划求解”，会弹出一个对话框。在对话框中，将E8键入目标单元格，将C9：D9键入可变单元格，既然目标是要最大化目标单元格，还必须选中“最大值”（Max） 2 >点击对话框中的“添加”按纽（Add），弹出添加约束对话框，将约束条件具体化。左端输入范围E5：E7，右端输入范围G5：G7，中间的符号可选择“<=”。如果还要添加更多的函数约束就再点击“添加”按钮以弹出一个新的添加约束对话框，在这个例子中没有其它约束了。下一步只要点击“确定”按钮回到“规划求解”对话框。 3>点击“选项”按钮，在新弹出的对话框中，选中“采用线性模型”和“假定非负”选项，这就告诉了计算机要求解的问题是一个线性规划问题以及非负约束，点击“确定”再回到“规划求解”对话框。 4>点击 “求解”按钮，计算机会在后台开始对问题进行求解。几秒钟之后会显示运行结果，一般而言，它会显示已经找到一个最优解。如果模型没有可行解或没有最优解，对话框会显示“规划求解找不到可行解”。 求解模型之后，最优值就代替了可变单元格中的初始值，最优解是每周2扇门和6扇窗，目标单元格的对应数值（每周总利润）为3600元。 求解的电子表格模型（图2）如下：

图2 3

A B C D E F G 1 玻璃制品公司的产品组合问题 2 3 生产单位产品的时间 4 门 窗 时间总数 可得时间 5 工厂1 1 0 2 <= 4 6 工厂2 0 2 12 <= 12 7 工厂3 3 2 18 <= 18 8 单位利润 300 500 3600 9 结论 2 6 线性规划问题的数学模型是描述实际问题的抽象的数学形式，它反映了客观事物数量间的本质规律。电子表格模型和数学模型两种形式是等价的。电子表格模型的建立不是唯一的，是非常灵活的，一个好的电子表格模型能直观，简便地反映线性规划问题的实质.这两种形式导致了不同但互补的分析问题的方法，但用“规划求解”工具会达到事半功倍的效果。 再如：某厂家拥有三个生产婴儿车的工厂，并运往四个配送中心，三个工厂每月的产量分别是12、17、11个运输单位，同时每个配送中心每月要接受10个运输单位的货物，从每一个工厂到每一个配送中心的单位运输成本如下所示： 到各配送中心的单位运输成本（元） 工厂 1 2 3 4 1 50 60 40 20 2 20 90 10 30 3 30 40 20 10 确定每月从每一个工厂之中要运送多少运输单位的婴儿车给相应的配送中心的最佳方案，使总的运输成本最小。 用xij（i=1、2、3，j=1、2、3、4）表示从工厂运送到配送中心j的货物量，那么各工厂运送到各配送中心的货物量如下表： 工厂 1 2 3 4 工厂产量 1 X11 X12 X13 X14 12 2 X21 X22 X23 X24 17 3 X31 X32 X33 X34 11 配送中心接受量 10 10 10 10 该线性规划的数学模型是： 确定xij（i=1、2、3，j=1、2、3、4）的值，使目标函数C成本

=50x11+60x12+40x13+20x14+20x21+90x22+10x23+30x24+30x31+40x32+20x33+10x34有

最小值。 约束条件是： x11+x12+x13+x14=12 x21+x22+x23+x24=17 x31+x32+x33+x34=11 x11+x21+x31=10 x12+x22+x32=10 x13+x23+x33=10 x14+x24+x34=10 4

xij≥0（i=1、2、3，j=1、2、3、4） 电子表格模型如图3： 图3 A B C D E F G H I 1 婴儿车的配送问题 2 到各配送中心的单位运输成本 3 1 2 3 4 4 工厂1 50 60 40 20 5 工厂2 20 90 10 30 6 工厂3 30 40 20 10 7 8 运送到各配送中心的单位数 9 1 2 3 4 总量 工厂产量 10 工厂1 0 0 0 0 0 = 12 11 工厂2 0 0 0 0 0 = 17 12 工厂3 0 0 0 0 0 = 11 13 总量 0 0 0 0 0 14 = = = = 15 中心接受量 10 10 10 10

模型中的单元格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=SUMPRODUCT(C12：F12), G13是目标单元格（总成本），它正好是数据单元格C4：F6和可变单元格C10：F12对应数值相乘再加和得到,即G13=SUMPRODUCT（C4：F6，C10：F12），模型中的等号体现了函数的约束。 以上模型通过点击“规划求解”工具，因为是最小化目标单元格G13，所以选“最小值”（Min）；在“添加”约束里，添加“G10：G12=I10：I12”和“C13：F13=C15：F15”，仍然选“采用线性模型”和“假定非负”，得出如下结论：（图4）

图4

合集下载

用EXCEL解线性规划的步骤

用EXCEL解线性规划的步骤

用EXCEL解线性规划的步骤
1. 2. 3. 4. 5. 6.
– – – – – –
将目标函数系数放入一行 将每个约束条件系数及常数项放入一行，所有约束系数及常数 项成一矩阵 将决策变量的初始值（全0或1）放入一列 用函数MMULT（A1，A2）将目标函数值放入一格：目标函数 系数行与决策变量列的乘积（或者用SUMPRODUCT(A1,A2,…) 用MMULT将每个约束条件系数行与决策变量列乘积放入对应 的常数项旁边格 在工具栏选规划求解
• 约束条件右端常数项
– 阴影价格：约束右端常数项增加一个单位， 使得目标函数最格 最大最小选择 可变单元格：决策变量列 添加约束条件：每个约束条件方程的值(>,=,<)对应的常数项 选项：线性模型 求解（保存敏感分析等报告）
1. 将目标函数系数放入一行
2. 将每个约束条件系数及常数项放入一行，所有约束系数及常数项成一矩阵
3. 将决策变量的初始值（全0或1）放入一列
4. 用函数SUMPRODUCT或MMULT（A1，A2）将目标 函数值放入一格：目标函数系数行与决策变量列的乘积
5. 用MMULT将每个约束条件系数行与决策变量列乘积放 入对应的常数项旁边格
6. 在工具栏选规划求解
7. 填好目标值所在格、决策变量（可变单元格）、约束条件
选项底下勾选采用线性模型
保存规划求解结果，包括运算结果、敏感性报告、极限值报告
最后结果
灵敏度分析
• 目标函数的系数
– 允许增加或减少的量：此范围内最优解不变 – 递减成本：最优解中等于0的变量，对应的 目标函数中的系数增加或减少多少，最优解 不再为0
用Excel求解线性规划及线性方程组的方法

用Excel求解线性规划及线性方程组的方法

第23卷总第44期 西北民族学院学报(自然科学版)Vol.23,No.2 2002年6月 Journal of N orthw est Minorities U niversity(Natural Science)J une,2002用Excel求解线性规划及线性方程组的方法王培麟(番禺职业技术学院,广东番禺511483)[摘　要]对利用美国微软公司开发的Office组件中的电子表格软件Excel求解线性规划的方法给予了介绍,并将该功能给予扩充,给出了用该软件求解线性方程组的方法1[关键词]Excel;线性规划;求解方法[中图分类号]TP271+.7 [文献标识码]A [文章编号]1009-2102(2002)02-0037-03Excel是美国微软公司开发的Office组件中的电子表格软件,它具有强大的电子表格处理功能,使用户能够轻松地制作表格,并具有对数据进行检索、分类、筛选、排序、计算、分析与统计等功能1对大多数用户而言,也许更注重于Excel的表格功能,而对于它的计算功能,特别是数学计算功能可能就不是十分熟悉1本文将介绍用Excel解线性规划及线性方程组的方法与技巧11　用Excel解线性规划 用Excel解线性规划,必须在Excel系统中加载“规划求解”项目1如果没有,可以启动Excel软件,进入Excel用户界面,然后使用“工具”菜单下“加载宏”菜单项之“规划求解”子项,则可完成“规划求解”项的加载1下面通过例1的求解来说明使用Excel解线性规划问题的方法1例1　线性规划模型为:min s=2x1+7x2+4x3+9x4+5x51S.t　3x1+2x2+x3+6x4+18x5≥700x1+0.5x2+0.2x3+2x4+0.5x5≥300.5x1+x2+0.2x3+2x4+0.5x5=200x1≤50;x2≤60;x3≤50;x4≤70;x5≤40; x1,x2,x3,x4,x5≥01求解的具体方法为:首先要建立电子表格模型,输入如图1所示的工作表1工作表的格式不是固定不变的,可根据具体的需要进行调整1建立工作表的步骤为:1)确定一些单元格来代表决策变量,本例中x1,x2,…,x5为决策变量,需要将它们放到一些单元格中,称为可变单元格1一般地,可变单元格使用Excel的某行一块连续的区域,如[收稿日期]2002-04-01[作者简介]王培麟(1963—),男,副教授,硕士,主要从事数学和计算机方面的教学与研究1—73—本例中有5个决策变量,则可用区域B2:F2作为可变单元格(注意:B1:F1放的是说明,其下放的是可变单元格)1求解前,可变单元格放的是决策变量的初值,一般我们使用0作为初始值(此时,这些单元格不做任何输入,表示它们的初值为0)12)输入目标函数系数,本例中将它们输入在B3:F3单元格1图1　规划求解电子表格模型3)确定目标单元格,并在其中输入目标函数表达式1本例中取G3作为目标单元格1在G3中输入公式:=SUMPRODUCT(＄B＄2:＄F＄2,B3:F3),它的含义是目标函数表达式:s=2x1,+7x2+4x3+9x4+5x51注意公式中决策变量单元格使用的是绝对地址,这样做是为了方便公式的复制14)输入约束条件左右两端的数据,并将目标函数单元格中的公式复制到目标函数下面相应的单元格中,本例中需将公式复制到G4∶G111通过以上步骤就建立好了电子表格模型,然后就可以进入规划的求解阶段1具体步骤为:1)选取菜单栏中“工具”菜单下的“规划求解”菜单项,弹出如图2所示的“规划求解参数”对话框12)在“设置目标单元格”文本框中输入目标单元格地址,本例中为G313)在“等于”项目上选定“最小值”选项14)在“可变单元格”文本框中输入可变单元格区域地址,本例中为B2:F215)单击“添加”按扭,弹出如图3所示的“添加约束”对话框1图2　规划求解对话框 图3　添加约束对话框——83在其中依次输入所有约束条件,每输入一个约束条件后,单击“添加”按扭,进入下一个约束条件的输入1本例中约束条件如下:G4:G5>=H4:H5;G6=H6;G7:G11<=H7:H11;B2:F2>=0然后按“确定”按扭,返回“规划求解参数”对话框16)单击“选项”按扭,弹出“规划求解”选项对话框,在该对话框中,可以输入“最长运算时间”、“迭代次数”、“精度”、“收敛度”等参数1如果无特殊需要,建议使用默认值1选取“采用线性模型”后按“确定”按扭返回,“规划求解”对话框17)单击“求解”按扭,规划求解软件开始运行,运算结束后,弹出“规划求解结果”对话框,通过该对话框可以保存求解结果,并给出运算结果报告,同时可变单元格和目标单元格分别显示最优解和最优值12　用Excel 解线性方程组 将线性方程组看作目标函数永远都为0,而约束条件均为等式的线性规划,则可以利用Excel 的规划求解功能求解线性方程组1下面通过例2的求解来说明使用Excel 解线性方程组的方法1例2　设所要求解的线性方程组为:x1-x 2+x 3-2x 4=22x 1-x 3+4x 4=43x 1+2x 2+x 3=-1-x 1+2x 2-x 3+2x 4=-41求解的具体方法为:首先要建立电子表格模型,输入如图4所示的工作表1图4　线性方程组求解电子表格模型注意到该表格中目标函数系数单元格中的系数全部为01在目标单元格中输入公式:=SUMPRODUCT (＄B ＄2:＄E ＄2,B3:E3),并将该公式复制到目标单元格下面相应的单元格中,本例中为F4:F7区域1然后选取菜单栏中“工具”菜单下的“规划求解”菜单项,弹出如图2所示的“规划求解参数”对话框1在该对话框中,设置目标单元格和可变单元格的方法与解线性规划是一样的,本例中,目标单元格为F3,可变单元格为(转第49页)311　与传统方法相比缺乏整体性　由于计算机屏幕内容的变换,与传统的板书相比,讲完课后回顾总结时,知识的整体性不够1因此,在教学设计过程中不可完全用计算机代替板书,应另外分几幅屏幕进行总结,以保证内容的完整性1312　教学中与学生眼神的交流相对减少　课堂上使用计算机需要教师操纵演示进度,教师被局限于电脑前控制鼠标,过多时间注视显示器,故与学生眼神交流相对减少1这样,一方面不易及时了解学生的反应;另一方面学生的注意力因缺少与教师的交流,降低了学习兴趣1因此,教师应适时离开电脑,适当采用教鞭指示,或穿插使用其他合适媒体,以充分发挥教师这一主要媒体的作用1对教学内容与教学手段的选择要严格把关,要注重教学效果,防止一味追求使用多媒体技术而忽视学生这一学习主体1313　学生长时间地注视容易产生视疲劳　由于放映条件要求室内光线不宜过亮,导致学生记录不便,再则长时间注视屏幕,学生容易疲劳1因此,对教学内容的选择要求针对性强,软件的制作要求精,其设计、编辑既要有知识,又要有趣味性,同时还应将色彩、动画、图片、声音与艺术有机地结合起来,以提高学生的学习兴趣1314　CA I课件的制作最好具有课堂教学与自学的双重性　用于教学要求交互性好,充分运用其超文本(无固定顺序的信息组织结构称为非线性,其中文字信息称超文本,如果还有其他媒体信息则称超媒体)功能,调用内容方便,以减少中间环节造成的信息丢失和失真,最好有学生自学的部分1综上所述,随着计算机的普及和应用1多媒体教学不仅是一种教学手段和方法,而且是一种独特的教学过程,充分体现了现代化的教学思想和教学理论的形成1我们相信开展多媒体教学将是实现药理教学中现代化教学的途径,将有着广阔的前景1当然,随着观念的发展、知识的更新和技术的进步,多媒体药理教学还会有许多问题存在,如教学软件的设计,教具的配套问题,如何恰当使用多媒体等,有待于进一步探索,力求在教学实践中获得最大的教学效果,真正体现教学现代化1(接第39页)B2:E2,但是“等于”项要设置为0,即线性方程组被看作是目标函数为0的线性规划,同时在填入约束条件时,所有的约束条件均是等式,该例中填入的约束条件为F4:F7=G4:G71最后按下“求解”按扭,运行完成后,在可变单元格中放的就是所求线性方程组的解13　结束语 本文给出了用Excel求解线性规划和线性方程组的一般方法及技巧,对于有最优解的线性规划和有解的线性方程组都可以进行求解,方法简捷、精度较高,适合于求解大型线性规划和高阶线性方程组1但是,对于有无穷多最优解的线性规划问题和有无穷多解的线性方程组,该方法只能给出其中的一个解,这一点在使用时应给予注意1参考文献:[1]李铁军.学用Excel2000[M].北京:清华大学出版社,19991[2]谢胜智,陈戈止1运筹学[M].成都:西南财经大学出版社,19991。

excel线性规划求解

excel线性规划求解


To Calculate Total LHS, 選擇SUMPRODUCT
1 2
選定第一列範圍
選定第二列範圍
SUMPRODUCT(F6:G6,G3:G3)=F6*F3 + G6*G3
Then repeat the same steps for constraint #2 and #3
輸入Slack 公式
求最小值
1.輸入變數x1, x2的值所在的儲存格 2.『新增』限制式
1.輸入限制式左邊及右邊的儲存格 2.選擇適當的符號
左邊
右邊
1.選擇後之結果 2.按『新增』
1.此限制式表示 constraint #1and #2 2.再新增 constraint #3
Constraint #3的左邊，右邊及符號
1.前三項限制式(constraints#1,#2,#3) 2.選擇『選項』
新增 constraint #4: x1 >=0 constraint #5: x2 >=0
按『求解』後的結果
想將此圖轉貼於 EXCEL工作表上 1.選此按鈕 2.回到EXCEL
1.按貼上的按鈕或 『編輯』下的貼上， 即顯示圖案。 2. 將之移至適當位置
完成
线性规划求解
Linear Programming Problem
輸入公式Βιβλιοθήκη 列出EXCEL的求解方式: Min:Minimum Objective Value=F4*F3+G4*G3 LHS: Left Hand Side RHS: Right Hand Side Slack: RHS - LHS for “<=“ LHS- RHS for “>=“
EXCEL规划求解案例分析

EXCEL规划求解案例分析

应如何安排这四种家具的日产量，使得该厂的日利 润最大？
表1 雅致家具厂基本数据
家具类型
劳动时间（小时/件） 木材（单位/件）
玻璃（单位/件） 单位利润（元/件） 最大销售量（件）
12 3
213 421 621 60 20 40 100 200 50
4
可提供两
2 400小时 2 600单位 2 1000单位 30 100
例1. 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具，由于该四种家具具 有不同的大小、形状、重量和风格，所以它们所需 要的主要原料（木材和玻璃）、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时，详细的数据资料见下表。
雅致家具厂生产计划优化问题雅致家具厂生产4种小型家具由于该四种家具具有不同的大小形状重量和风格所以它们所需要的主要原料木材和玻璃制作时间最大销售量与利润均不相同
运用EXCEL求解线性规划问题
广东商学院 数学与计算科学学院
outline
1.关于“规划求解” 2.如何加载“规划求解” 3. “规划求解”各参数设置 4. “规划求解”步骤 5. 利用“规划求解”解线性规划问题
解：依题意，设置四种家具的日产量分别为决策变量
x1, x2 , x3, x4 ，目标要求是日利润最大化，
约束条件为三种资源的供应量限制和产品销售量限制。 据此，列出下面的线性规划模型：
MaxZ 60x1 20x2 40x3 30x4
4x1 2x2 x3 2x4 600
2.如何加载“规划求解”
1) 在“工具”菜单上,单击“加载宏”
2) 在弹出的对话框中的“可用加载宏”列表框 中,选定待添加的加载宏“规划求解”选项旁 的复选框,然后单击“确定”.单击“确定”后 ,“工具”菜单下就会出现一项“规划求解”