EXCEL规划求解选修指导书

1、EXCELL辅助决策实验选修指导书本章以举例的方式介绍用Spreadsheet方法解决各种管理问题。第一节线性规划问题建模和求解例雅致家具厂生产计划优化问题雅致家具厂生产4种小型家具，由于该四种家具具有不同的大小、形状、重量和风格,所以它们所需要的主要原料（木材和玻璃）、制作时间、最大销售量与利润均不相同。该厂每天可提供的木材、玻璃和工人劳动时间分别为600单位、1000单位与400小时，详细的数据资料见下表。问：（1）应如何安排这四种家具的日产量，使得该厂的日利润最大？（2）家具厂是否愿意出10元的加班费，让某工人加班1小时？（3）如果可提供的工人劳动时间变为398小时，该厂的日利润有何变化

2、？（4）该厂应优先考虑购买何种资源？（5）若因市场变化，第一种家具的单位利润从60元下降到55元，问该厂的生产计划及日利润将如何变化？表1雅致家具厂基本数据家具类型劳动时间（小时/件）木材（单位/件）玻璃（单位/件）单位产品利润（元/件）最大销售量（件）12466010021222020033114050422230100可提供量400小时600单位1000单位解：依题意，设置四种家具的日产量分别为决策变量X1,X2,X3,X4,目标要求是日利润最大化，约束条件为三种资源的供应量限制和产品销售量限制。据此，列出下面的线性规划模型：MaxZ4为2X2X32x4600（木材约束）6x12X2X32

3、x41000（玻璃约束）2为1X23x32x4400（劳动时间约束）60x120x240x330x4x1100（家具1需求量约束）x2200（家具2需求量约束）x350（家具3需求量约束）x4100（家具4需求量约束）x1,x2,x3,x40（非负约束）其中X1,X2,X3,X4分别为四种家具的日产量。下面介绍用Excel中的“规划求解”功能求此题。第一步在Excel中描述问题、建立模型，如下图所示。第二步在“工具”菜单中选择“规划求解”。第三步在“规划求解参数”对话框进行选择如下图。等于金宦犬佰牡i广憬小怕臥厂值为FT主闭可賀重元辭.KTwisKJ唯釧©约克妙mi5；mi5SB：1

4、T$EST预I$G胆:蜩8uEU&：$ItS14I更坟©1“删腔I第四步点击“选项”按钮，弹出“规划求解选项”对话框。星怪运直时闫.©.迭代软数:精度（1）:允许谍差:收斂虎（M：P采用线性複型辺P暇莖韭夏1声计i&正切画数®r二次方程1100抄100I.oooool|5|.0001r自动按比例缰敵厂显示選代结果&导数®简前差£迪中心羞分©e丰顿法（S）共9ais（a）第五步选择“采用线性模型”和“假定非负”，单击“确定”，返回下图。单击“求解”，即可解决此题。最后结果如下页图所示。与此结果对应的敏感性报告如下

5、表所示。叩5T怦应触折illEl®迪/.©柜亢电JLB.©数亮E.蛍赴I特片也口朮曰£|吾出也（&刁ey*SIm3号ABCDEF*1VRrjPTfl|呂1茲L"».0最够性就占;cr2R-wlslSliiOifit3q抿苜附堆立:n»-B-flHrUiDaE67粗內犀式Oft8单尤格値皓鱼縄竝9SEtlEIIJU站B0IE+302010ECSLRmL蛍件j30D20L02.511|U$l£日产豈C4）4UD40)20kO120-2*0it)2.011*20U15蛉枣允许的允许的16直*r«IHMt

6、i17SG翁芳咖E打311/JiV4(1012qoo25100ia翊木甘匕館件6M4aoo2005019即J3班鳴1单住/件）tuuD1000JE+3D2M112021蔦?H*卜胡睹件衆弓/x2yF1Jnr1rmif圏IF创卸戲也孚»|ijSUK-Ctf1|四仙匡异r-i_fl匀缶电：云04|15=文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.说明：（1）可变单元格表中，终值对应决策变量的最优解；递减成本指目标函数中决策变量的系数必须改进多少才能得到该决策变量的正数解，改进对最大值为增加，对最小值为减少。（2）允许的增量（或减量）指在保证最优解不变的前提下，目标函数系

7、数的允许变化值。（3）在约束表中，终值是指约束的实际用量；影子价格式指约束条件右边增加（或减少）一个单位，目标值增加（或减少）的数值；这里的允许的增量（或减量）是指在影子价格保持不变的前提下，终值的变化范围。根据模型运行结果可作出如下分析：（1）由模型的解可知，雅致家具厂四种家具的最优日产量分别为100件、80件、40件和0件，这时该厂的日利润最大，为9200元。本问题的敏感性报告如上页表所示。由上述敏感性报告可进行灵敏度分析，并回答题目中的问题（2）一（5）。（2）由敏感性报告可知，劳动时间的影子价格为12元，即在劳动时间的增量不超过25小时的条件下，每增加l小时劳动时间，该厂的利润（目标值

8、）将增加12元。因此，付给某工人10元以增加l小时劳动时间是值得的，可多获利为：1210=2（元）。（3）当可提供的劳动时间从400小时减少为398小时时，该减少量在允许的减量（100小时）内，所以劳动时间的影子价格不变，仍为12元。因此，该厂的利润变为：9200+12X（398400）=9176（元）。（4）由敏感性报告可见，劳动时间与木材这两种资源的使用量等于可提供量，所以它们的约束条件为“紧”的，即无余量的；而玻璃的使用量为800，可提供量为1000，所以玻璃的约束条件是“非紧”的，即有余量的。因此，应优先考虑购买劳动时间与木材这两种资源。（5）由敏感性报告可知，家具1的目标系数（即单位

9、利润）允许的减量为20，即当家具1的单位利润减少量不超过20元时，最优解不变。因此，若家具1的单位利润从60元下降到55元，下降量为5元，该下降量在允许的减量范围内，这时，最优解不变。因此，四种家具的最优日产量仍分别为100件、80件、40件和0件。最优值变为：9200+（5560）X100=8700（元）。文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.第二节非线性规划例北海玩具厂生产一种玩具，设该玩具下月产量为x（个），而且所有的产品均可销售出去。已知玩具A的单位产品利润随着销量的增加而减少，其规律是单位产品利润为1000.5x（元）。该玩具每月单位产品的原材料消耗量为1单位

10、，对人工的需求量为2单位。已知该厂下月可提供的原材料为200单位，可提供的人工为350单位。问该厂下月应如何安排生产，才能使总利润最大?解：设下月玩具的产量为x（个），因此有下月的利润为:（1000.5x）x=100x0.5x2下月玩具消耗的原材料为：x下月玩具需要的人工为：2x由此得到本问题的模型如下：O.bsmax100x一05x2.t.xw2002xw350x>0上述问题中，目标函数（利润）为决策变量（产量x）的非线性函数，所以本规划问题为非线性规划问题。用Spreadsheet求解此题的步骤如下：第一步：输入已知数据首先在Excel的工作表上输入已知数据。在单元格D4:E4中输入

11、目标函数的系数，在单元格D6:D7中分别输入单位产品消耗的原材料和人工，在单元格G6:G7中分别输入原材料和人工的可提供量。第二步：建立非线性规划模型在Spreadsheet上描述规划的决策变量、目标函数与约束条件。msgaiEngMmi”注'RZ11Bcne0R1例山尢1北沟玩尺厂生？计划234单妒册艙曲ft111000.515資源林呈6甲应产展洎耗原材柯=D&*5D(S200T甲逬产孔希整入工關2=37*D(3933S0S下耳计划生产逼1110,11下月翻祠1=D4tD：i2护出飞1213151617Id19JU21rtHl寸IHJI四匸匚lI町厲亠口口壬2酋|吾山盼（fe

12、<EAJ；D习?v淘斯illEl©MAH柜丈电_L0.lI?劉世lOiiJ聞砂Ie曲站学叫H血训第本问题的决策变量是下月的计划生产量，在Spreadsheet上用单元格D9表示该决策变量。本问题的目标函数是下月总利润最大。用单元格D11表示总利润。它等于单位产品的利润与产量的乘积，其中单位产品的利润等于100x0.5x2，所以，在单元格D11中输入：=D4*D9E4*D9*D9本问题共有三个约束条件。第一个约束是原材料约束，即所消耗的原材料不得超过原材料的可提供量，用单元格E6表示该约束条件的左边，即所消耗的原材料，它应等于单位产品消耗的原材料与产量的乘积，所以在单元格E6中输

13、入：=D6*$D$9第二个约束是人工约束，即所需要的人工数不得超过可提供的人工数，用单元格E7表示该约束条件的左边，即所需要的人工，它应等于单位产品需要的人工数与产量的乘积，所以在单元格E7中输入：=D7*$D$9第三个约束是非负约束，该约束将在下一步规划求解时输入。第三步：利用“规划求解”功能求出非线性规划的最优解；在规划求解参数框中输入目标单元格（目标函数地址）、可变单元格（决策变量地址）和约束条件。其规划求解参数框如下图所示。严触于当前鯛彌嘶盹帮助QP确是I朝肖'杲再方冥|题,然后在规划求解选项参数框中选择“假定非负”（注意：本问题是非线性规划问所以不选择“采用线性模型”），最后

14、在规划求解参数对话框中单击“求解”得到本问题的最优解，如下页图所示。敏感性报告如下图所示。第三节线性整数规划例乐天保健仪器厂的生产优化问题乐天保健仪器厂下月拟生产两种保健仪器A和B,生产该两种仪器的利润、消耗的主要原材料和劳动力如表5.1.1所示。该厂下月可提供的原材料和劳动力分别为2000（千克）和140（千小时）。另根据市场调查，下月对仪器A的需求量不大于5台。为获得最大的总利润，该厂应生产这两种仪器各多少台？设备名称仪器A仪器B可提供量原材料（千克/台）2824002000劳动力（千小时/台）440140利润（千兀/台）1015乐天保健仪器厂生产利润与消耗资源表解：据题意，本问题的决策变

15、量是下月两种仪器的生产量，设下月仪器A与B的生产量分别为X（台）与y（台）。本问题的目标函数是总利润最大，由于生产每台仪器A与仪器B的利润分别为10与15千元，所以总利润为：IOX+15Y本问题的约束条件有四个。第一个约束是原材料约束，即所消耗的原材料总量不得超过原材料的可提供量；第二个约束是劳动力约束，即所需劳动力的总量不得超过劳动力的可提供量；第三个约束是仪器A的生产量约束不得超过其最大需求量；第四个约束是决策变量必须为非负整数。由此得到整数规划模型如下：O.b.max10X+15Ys.t.282X+400Y<20004X+40yW140XW5X,y>0并且为整数线性整数规划模

16、型的Spreadsheet解法文档来源为：从网络收集整理.word版本可编辑.欢迎下载支持.用Spreadsheet方法求解整数规划的基本步骤与求解一般线性规划问题相同，只是在约束条件中添加一个“整数”约束。在Excel的规划求解的参数对话框中，用“int”表示整数。因此，只要在该参数对话框中添加一个约束条件，在左边输入的是要求取整数的决策变量的单元格地址，然后选择"int”，见下图。下面说明整数规划模型的Spreadsheet解法。第一步：输入已知数据与解一般线性规划问题相同，首先在Excel的工作表上输入已知数据：在单元格B4:C5中分别输入两种仪器消耗的原材料和劳动力，在单元格

17、G4:G6中分别输入可提供的原材料、劳动力和最大需求量，在单元格B7:C7中输入两种设备的利润，如下图所示。Hied-SI.厂笙产UUL>1主ABCEFHIJ二1Hs.l土产1£址1理23WB4民羽科i千A£ri可丘仆吧序和rk千克)440可干-|时,d古召尢需7足7单世产<4几怡)1U15S910L1211U14物宝JK加号酬Wi申2mn16苗！;加甬96A4Q17生产汁划4|43LE1.9302322142544卜八i皿hl1HI11"MS章刑i霹射迥勺*亠忆.|fijasi151射.1星G(色匚三如t41Q酉宜牌凶闽曲屯他兀誓式电_Lft(DCD

18、J會口嘗邮動0U-旧1昱第二步：建立整数规划模型首先在Spreadsheet上描述规划的决策变量、目标函数与约束条件。文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持.本问题的决策变量是两种仪器的产量，分别用单元格B17与C17表示。本问题的目标函数是总利润最大，用单元格B13表示总利润，它应等于每种仪器的单位利润与其产量的乘积之和，即在单元格B13中输入下述公式：=sumproduct(B7:C7,B17：C17)本问题共有四个约束条件。第一个约束条件是原材料约束，即所消耗的原材料总量不得超过其供应量。在约束条件左边是所消耗的原材料总量，用单元格F15表示，它应等于每种仪器的单位

19、原材料消耗量与其产量的乘积之和，即在单元格F15中输入下述公式：=sumproduct(B4:C4,B17：C17)在约束条件右边输入原材料供应量。用单元格H15表示原材料供应量，并输入以下公式：=G4同理可得第二个约束条件(劳动力约束)的公式，用单元格F16表示所需要的劳动力总量，在单元格F16中输入：=sumproduct(B5:C5,B17：C17)用单元格H16表示劳动力供应量，在单元格H16中输入：=G5第三个约束是仪器A的需求约束。仪器A的产量不得超过其最大需求量。用单元格F17表示仪器A的产量，它应等于表示仪器A产量的那个决策变量，因此，在单元格F17中输入：=B17用单元格H1

20、7表示仪器A的最大需求量，它用下述公式得到：=G6第四个约束条件是决策变量必须为非负整数。该约束条件在下一步规划求解时输入。第三步：在Excel规划求解功能中输入整数约束并求解在规划求解参数框中输入目标单元格(目标函数地址)、可变单元格(决策变量地址)和四个约束条件，包括整数约束，其规划求解参数框，如下图所示。然后在规划求解选项参数框中选择“采用线性模型”和“假定非负”，最后在规划求解参数对话框中单击“求解”得到本问题的最优解。本问题的最优解为：仪器A的产量为4台，仪器B的产量为2台。这时总利润最大，为70千元。第四节运输问题例海华设备厂均衡运输问题海华设备厂下设三个位于不同地点的分厂A,B,

21、C,该三个分厂生产同一种设备，设每月的生产能力分别为20台、30台和40台。海华设备厂有四个固定用户，该四个用户下月的设备需求量分别为20台、15台、23台和32台。设各分厂的生产成本相同，从各分厂至各用户的单位设备运输成本如下表所示，而且各分厂本月末的设备库存量为零。问该厂应如何安排下月的生产与运输，才能在满足四个用户需求的前提下使总运输成本最低。海华设备厂运输成本表文档来源为：从网络收集整理.word版本可编辑.欢迎下载支持解：本题可用下页图所示的网络图描述。网络图左边的节点表示三个分厂，右边的节点表示四个用户，左、右节点间的连线表示从左边某分厂生产的设备运输到右边某用户，线段上的数字表示

22、单位设备的运输成本。网络图最左边的数字分别为三个分厂的生产能力，最右边的四个数字分别为四个用户的需求量。总供应量：20十30十40=90（台）；总需求量：20十15十23+32=90（台）。即所有供应点的供应量之和等于所有需求点的需求量之和。所以本问题是供需均衡的运输问题。这时，所有供应点的供应量全部供应完毕，而所有需求点的需求量全部满足。据题意，本问题的决策变量是下月各分厂为各用户生产与运输的设备数量。可设分厂A下月为四个用户生产和运输的设备数量分别为：A1,A2,A3,A4（台）；分厂-胛分厂-胛供应节点用户运输成本用户-2.'（元/台）-用户3分厂分厂分厂下月设备需求量）（吨）4

23、070407070；10080-311()350C43C4060504032用户32分厂B下月为四个用户生产和运输的设备数量分别为:B1,B2,B3,B4（台）;分厂C下月为四个用户生产和运输的设备数量分别为:C1，C2,C3,C4（台）。本问题的目标函数是总运输成本最小化。总运输成本的计算公式如下：总运输成本=刀（各分厂至各用户的设备运输成本）X（各分厂至各用户的运输量）因此，该问题的目标函数为：o.bmin70Al+40A2+80A3+60A4+70Bl+100B2+110B3+50B4+80C1+70C2+130C3+40C4本问题的约束条件有两个部分，第一部分是需求约束，即各用户从各分

24、厂收到的设备总数不得少于它们的需求量：A1+Bl+C1A2+B2+C2A3+B3+C3A4+B4+C4=20（用户1从三个分厂收到的设备总数应等于其需求量=15（用户2从三个分厂收到的设备总数应等于其需求量=23（用户3从三个分厂收到的设备总数应等于其需求量=32（用户4从三个分厂收到的设备总数应等于其需求量第二部分是生产能力约束，即各分厂生产和运输的设备总数不得超过其生产能力：A1+A2+A3+A4=20（分厂A下月生产与运输的设备总数应等于其月生产能力）B1+B2+B3+B4=30（分厂B下月生产与运输的设备总数应等于其月生产能力）C1+C2+C3+C牟40（分厂C下月生产与运输的设备总数

25、应等于其月生产能力）最后还有非负约束，即：A1,A2,A3,A4,B1,B2,B3,B4,C1,C2,C3,C4>0（非负约束）综上所述，本问题的线性规划模型如下：o.bmin70Al+40A2+80A3+60A4+70Bl+100B2+110B3+50B4+80C1+70C2+130C3+40C4s.tA1+B1+C1=20A2+B2+C2=15A3+B3+C3=23A4+B4+C4=32A1+A2+A3+A4=20B1+B2+B3+B4=30C1+C2+C3+C牟40A1,A2,A3,A4,B1,B2,B3,B4,C1,C2,C3,C4>0本问题的Spreadsheet描述及建

26、模如下图所示。设査目标单元格;等于；广最大值a最小值通值为的o最后的结果如下图所示。第五节最大流问题例供水网络问题。某城市有7个供水加压站，分别用节点1，节点2,，节点7表示。见下图。其中节点I为水厂，各泵站间现有的管网用相应节点间的边表示。现规划在节点7处建一个开发区，经对现有管网调查，各段管网尚可增加的供水能力（万吨/日）如下图中各边上的数值所示。依照现有管网状况，从水厂（源点）到开发区（汇点），每日最多可增加多少供水量？解：本问题要解决的问题是在各管网可增加的供水能力为定值时，该网络可增加的从水厂至开发区的最大供水流量。这是一个网络最大流问题。这时可在网络图中添加一条从节点7（汇点）至节

27、点1（源点）的"虚”边（由于实际上并不存在从节点7流向节点1的管道，所以称该边为“虚”的）。增加这条边的目的，是为了使网络中各节点的边形成回路，各节点的流出量与流入量的代数和（即净流出量）为零。本问题可以看作在满足边容量约束条件下的网络流优化问题，目标函数是开发区（节点7）的总流人量（或虚拟的总流出量）最大化，这时节点7的总流人量（或虚拟的总流出量）就是网络最大流，即最大供水量。本问题的Spreadsheet描述与求解如下页图所示。（1）输入部分首先输入已知数据。在单元格C22:128中输入各节点间的边的容量增量。例如在单元格F22中输入3，表示从节点I至节点4的边可增加的供水能力为

28、3（万吨/日），等等。凡是节点间没有管道相连接的边，令其容量为零。从节点7至节点1的边为“虚”边,可设它的能力增量等于从源点出发的所有边的供水能力增量之和，即:3+4+3=10。此外，当网络中总流入量与总流出量达到平衡时，应满足以下条件：各中间节点的流出量等于流入量，即它们的净流出量应等于零；源点的流出量与从汇点经虚边的流入量的代数和应等于零；汇点的流入量与从汇点经虚边的流出量的代数和应等于零。因此，所有节点的净流出量均应等于零。在单元格C17:117中输入各节点净流出量应取的值，它们均为零。总“仇民V.44返TtgRau卜卜£匸0-Gh1J1LIf14o-1£J46仆1屮

29、处！24wF左6JiXBS?0'rttf145OfBrQaQ2D3&=P5Di210冲0D40D41(1书戊旺D0nn5n侶WED0oo33惟ItiiTgD0a000*：gwAgu2B7IJ14p*us日24*6JQ150D000010-X7QDaaafirl前3*1im*-TJTjtjo22343曲2It*3AE104fds6fljJB537RTea?1030-*u»i.jLf*V淘斯illEl©MAH柜丈电_L0.il?劉世lOiiJ口立2酋|吾山台(&d=S!Ki?zQ刁冇丨渤H伽.厶“|町&(2) 决策变量本问题的决策变量用C6：12

30、中的单元格表示，它们是从各节点到其他节点的流量，也是供水流量增量在网络中各条边上的分配量。例如单元格D6表示从节点1流入节点2的流量，也是连接节点1与节点2的边上的流量。(3) 目标函数本问题的目标函数是流入节点7的总流人量最大(即开发区得到的供水流量增量最大)，或者从节点7流向节点1的流出量最大。在单元格L6中输入目标函数，它用下式计算：=C12约束条件本问题的约束条件有三个：第一个约束是网络中边的容量约束。容量约束是指各节点间的边上的流量不得超过该边的容量。因此有：单元格C6:112中的数值(边流量)w单元格C22:I28中的数值(边容量)第二个约束是节点总流人量与总流出量的平衡约束。其计

31、算过程如下：计算各节点的总流人量节点的总流人量等于所有流人该节点的流量之和。用单元格C13表示节点1的总流人量，其计算公式如下:文档来源为:从网络收集整理.word版本可编辑.欢迎下载支持=sum(C6:C12)将上述公式复制到单元格D13：I13，得到其他节点的总流人量。计算各节点的总流出量节点的总流出量等于从该节点的所有流出量之和。用单元格J6表示节点1的总流出量，其计算公式如下:=Sum(C6:16)将上述公式复制到单元格J7:J12,得到其他节点的总流出量。计算各节点的净流出量为便于计算节点的净流出量，需将单元格J6:J12的总流出量写入单元格C14:I14。可在单元格C14中输入：=

32、J6然后，用同法逐个将单元格J6:J12的内容分别写入单元格D14:114。也可以使用transpose(转置)命令完成这个工作。transpose是一个将行向量或列向量进行转置的命令，其步骤是：选择区域C14:I14，在单元格C14中输入：=transpose(J6:J12)按下Ctrld+Shift+Enter键，就将总流出量写入了单元格C14I14。节点的净流出量等于该节点的总流出量与总流入量之差即两者之代数和。在单元格C17：117中输入各节点的净流出量。单元格C15表示节点1的净流出量，它的计算公式如下=I14-C13将上述公式复制到单元格D15:I15，得到其他节点的净流出量。当网

33、络中总流人量与总流出量达到平衡时，所有节点的净流出量均为零(4) 用Excel中的规划求解功能求出本问题的解在规划求解参数框中输入目标单元格(目标函数地址)、可变单元格(决策变量地址)和两个约束条件，然后在规划求解选项参数框中选择“采用线性模型”和“假定非负”，最后求解得到本问题的最优解。规划求解参数框如下图所示。模型运行结果见上上表。由表可知，本问题的最优解如下表所示。这时，节点7的总流人量为9，达到最大值，即该供水网络最多可供给开发区的供水流量增量为9（吨/日）。城市供水问邇优代姑果上述结果可用如下的网络图表示。节点M水厂）节点节点3节点4卄点5节点&节点九幵发区）节戌1243加22R点3121节虎44节点56节点&3文档来源为：从网络收集整理.word版本可编辑.欢迎下载支持.第六节排队论例某高速路口收费站，汽车按泊松分布到达此高速路口，平均每小时90辆。每辆车通过收费口的平均时间为35秒，服从负指数分布。（排队系统中