《EXCEL规划求解》选修指导书

《EXCEL规划求解》选修指导 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 《EXCELL辅助决策实验》选修指导书 本章以举例的方式介绍用Spreadsheet方法解决各种管理问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 。 第一节 线性规划问题建模和求解 例 雅致家具厂生产计划优化问题 雅致家具厂生产4种小型家具，由于该四种家具具有不同的大小、形状、重量和风格，所以它们所需要的主要原料(木材和玻璃)、制作时间、最大销售量与利润均不相同。该厂每天可提供的木材、玻璃和工人劳动时间分别为600单位、1000单位与400小时，详细的数据资料见下表。问: (1)应如何安排这四种家具的日产量，使得该厂的日利润最大, (2)家具厂是否愿意出10元的加班费，让某工人加班1小时, 3)如果可提供的工人劳动时间变为398小时，该厂的日利润有何变化, ( (4)该厂应优先考虑购买何种资源, (5)若因市场变化，第一种家具的单位利润从60元下降到55元，问该厂的生产计划及日利润将如何变化, 表1 雅致家具厂基本数据 家 具 类 型 劳 动 时 间 木 材 玻 璃 单位产品利润 最大销售量 (小时/件) (单位/件) (单位/件) (元/件) (件) 1 2 4 6 60 100 2 1 2 2 20 200 3 3 1 1 40 50 4 2 2 2 30 100 可提供量 400小时 600单位 1000单位 解:依题意，设置四种家具的日产量分别为决策变量x,x,x,x,目标要求是日利润最大1234 化，约束条件为三种资源的供应量限制和产品销售量限制。 据此，列出下面的线性规划模型: 1 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ MaxZxxxx,60，20，40，301234 ?xxxx4，2，，2,600(木材约束),1234 ,?xxxx6，2，，2,1000(玻璃约束)1234, ,?xxxx2，1，3，2,400(劳动时间约束)1234 ,?x,100(家具1需求量约束), 1st.., x,200(家具2需求量约束)?2, ,x,50(家具3需求量约束)?3, x,100(家具4需求量约束)?,4 ,xxxx,,,,0(非负约束)? 1234, 其中X1,X2,X3,X4分别为四种家具的日产量。 下面介绍用Excel中的“规划求解”功能求此题。 第一步 在Excel中描述问题、建立模型，如下图所示。 2 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 第二步 在“工具”菜单中选择“规划求解”。 第三步 在“规划求解参数”对话框进行选择如下图。 3 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 第四步 点击“选项”按钮，弹出“规划求解选项”对话框。 第五步 选择“采用线性模型”和“假定非负”，单击“确定”，返回下图。单击“求解”，即可解决此题。 最后结果如下页图所示。 4 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 与此结果对应的敏感性 报告 软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载 如下表所示。 5 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 说明:(1)可变单元格表中，终值对应决策变量的最优解;递减成本指目标函数中决策变量的系数必须改进多少才能得到该决策变量的正数解，改进对最大值为增加，对最小值为减少。(2)允许的增量(或减量)指在保证最优解不变的前提下，目标函数系数的允许变化值。(3)在约束表中，终值是指约束的实际用量;影子价格式指约束条件右边增加(或减少)一个单位，目标值增加(或减少)的数值;这里的允许的增量(或减量)是指在影子价格保持不变的前提下，终值的变化范围。 根据模型运行结果可作出如下分析: (1)由模型的解可知，雅致家具厂四种家具的最优日产量分别为100件、80件、40件和0件，这时该厂的日利润最大，为9200元。 本问题的敏感性报告如上页表所示。 由上述敏感性报告可进行灵敏度分析，并回答题目中的问题(2)一(5)。 (2)由敏感性报告可知，劳动时间的影子价格为12元，即在劳动时间的增量不超过25小时的条件下，每增加l小时劳动时间，该厂的利润(目标值)将增加12元。 因此，付给某工人10元以增加l小时劳动时间是值得的，可多获利为: 12—10,2(元)。 (3)当可提供的劳动时间从400小时减少为398小时时，该减少量在允许的减量(100小时)内，所以劳动时间的影子价格不变，仍为12元。 因此，该厂的利润变为: 9200+12X(398—400),9 176(元)。 (4)由敏感性报告可见，劳动时间与木材这两种资源的使用量等于可提供量，所以它们的约束条件为“紧”的，即无余量的;而玻璃的使用量为800，可提供量为1000，所以玻璃的约束条件是“非紧”的，即有余量的。 因此，应优先考虑购买劳动时间与木材这两种资源。 (5)由敏感性报告可知，家具1的目标系数(即单位利润)允许的减量为20，即当家具1的单位利润减少量不超过20元时，最优解不变。因此，若家具1的单位利润从60元下降到55元，下降量为5元，该下降量在允许的减量范围内，这时，最优解不变。 因此，四种家具的最优日产量仍分别为100件、80件、40件和0件。 最优值变为: 9200+(55,60)X100,8 700(元)。 6 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 第二节 非线性规划 例 北海玩具厂生产一种玩具，设该玩具下月产量为x(个)，而且所有的产品均可销售出去。已知玩具A的单位产品利润随着销量的增加而减少，其规律是单位产品利润为 ,0.5x(元)。该玩具每月单位产品的原材料消耗量为1单位，对人工的需求量为2单100 位。已知该厂下月可提供的原材料为200单位，可提供的人工为350单位。问该厂下月应如何安排生产，才能使总利润最大? 解:设下月玩具的产量为x(个)，因此有下月的利润为: (100—0.5x)x,100x一0.5x2 下月玩具消耗的原材料为:x 下月玩具需要的人工为: 2x 由此得到本问题的模型如下: O.b( max 100x一0(5x2 s(t( x?200 2x?350 x?0 上述问题中，目标函数(利润)为决策变量(产量x)的非线性函数，所以本规划问题为非线性规划问题。 用Spreadsheet求解此题的步骤如下: 第一步:输入已知数据 首先在Excel的工作表上输入已知数据。在单元格D4:E4中输入目标函数的系数，在单元格D6:D7中分别输入单位产品消耗的原材料和人工，在单元格G6:G7中分别输入原材料和人工的可提供量。 第二步:建立非线性规划模型 在Spreadsheet上描述规划的决策变量、目标函数与约束条件。 7 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 本问题的决策变量是下月的计划生产量，在Spreadsheet上用单元格D9表示该决 策变量。 本问题的目标函数是下月总利润最大。用单元格D11表示总利润。它等于单位产品的利润与产量的乘积，其中单位产品的利润等于100x一o.5x2，所以，在单元格D11中输入: ,D4*D9—E4*D9*D9 本问题共有三个约束条件。 第一个约束是原材料约束，即所消耗的原材料不得超过原材料的可提供量，用单元格E6表示该约束条件的左边，即所消耗的原材料，它应等于单位产品消耗的原材料与产量的乘积，所以在单元格E6中输入: ,D6*$D$9 第二个约束是人工约束，即所需要的人工数不得超过可提供的人工数，用单元格 E7表示该约束条件的左边，即所需要的人工，它应等于单位产品需要的人工数与产量 的乘积，所以在单元格E7中输入: ,D7*$D$9 第三个约束是非负约束，该约束将在下一步规划求解时输入。 第三步:利用“规划求解”功能求出非线性规划的最优解; 在规划求解参数框中输入目标单元格(目标函数地址)、可变单元格(决策变量地址) 8 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 和约束条件。其规划求解参数框如下图所示。 然后在规划求解选项参数框中选择“假定非负”(注意:本问题是非线性规划问 题，所以不选择“采用线性模型”)，最后在规划求解参数对话框中单击“求解”得到本问题的最优解,如下页图所示。 9 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 敏感性报告如下图所示。 10 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 第三节 线性整数规划 例 乐天保健仪器厂的生产优化问题 乐天保健仪器厂下月拟生产两种保健仪器A和B，生产该两种仪器的利润、消耗 的主要原材料和劳动力如表5(1(1所示。该厂下月可提供的原材料和劳动力分别为 2 000(千克)和140(千小时)。另根据市场调查，下月对仪器A的需求量不大于5台。 为获得最大的总利润，该厂应生产这两种仪器各多少台? 设备名称 仪器A 仪器B 可提供量 原材料(千克,台) 282 400 2 000 劳动力(千小时,台) 4 40 140 利润(千元,台) 10 15 乐天保健仪器厂生产利润与消耗资源表 解:据题意，本问题的决策变量是下月两种仪器的生产量，设下月仪器A与B的生产量分别为X(台)与y(台)。 本问题的目标函数是总利润最大，由于生产每台仪器A与仪器B的利润分别为10与15千元，所以总利润为:lOX+15Y 本问题的约束条件有四个。 第一个约束是原材料约束，即所消耗的原材料总量不得超过原材料的可提供量; 第二个约束是劳动力约束，即所需劳动力的总量不得超过劳动力的可提供量; 第三个约束是仪器A的生产量约束不得超过其最大需求量; 第四个约束是决策变量必须为非负整数。 由此得到整数规划模型如下: O(b( max 10X+15Y s(t( 282X+400Y?2 000 4X+40y?140 X ?5 X，y?0并且为整数 线性整数规划模型的Spreadsheet解法 11 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 用SPreadsheet方法求解整数规划的基本步骤与求解一般线性规划问题相同，只是在 约束条件中添加一个“整数”约束。在Excel的规划求解的参数对话框中，用“int”表示整数。因此，只要在该参数对话框中添加一个约束条件，在左边输入的是要求取整数的决策变量的单元格地址，然后选择“int”，见下图。 下面说明整数规划模型的Spreadsheet解法。 第一步:输入已知数据 与解一般线性规划问题相同，首先在Excel的工作表上输入已知数据:在单元格 B4:C5中分别输入两种仪器消耗的原材料和劳动力，在单元格G4:G6中分别输入可提供的原材料、劳动力和最大需求量，在单元格B7:C7中输入两种设备的利润，如下图所示。 第二步:建立整数规划模型 首先在Spreadsheet上描述规划的决策变量、目标函数与约束条件。 12 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 本问题的决策变量是两种仪器的产量，分别用单元格B17与C17表示。 本问题的目标函数是总利润最大，用单元格B13表示总利润，它应等于每种仪器的单位利润与其产量的乘积之和，即在单元格B13中输入下述 公式 小学单位换算公式大全免费下载公式下载行测公式大全下载excel公式下载逻辑回归公式下载 : sumproduct(B7:C7，B17:C17) , 本问题共有四个约束条件。第一个约束条件是原材料约束，即所消耗的原材料总量 不得超过其供应量。在约束条件左边是所消耗的原材料总量，用单元格F15表示，它应等于每种仪器的单位原材料消耗量与其产量的乘积之和，即在单元格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 用单元格H17表示仪器A的最大需求量，它用下述公式得到: ,G6 第四个约束条件是决策变量必须为非负整数。该约束条件在下一步规划求解时输入。 第三步:在Excel规划求解功能中输入整数约束并求解在规划求解参数框中输入目标单元格(目标函数地址)、可变单元格(决策变量地址)和四个约束条件，包括整数约束，其规划求解参数框，如下图所示。 13 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 然后在规划求解选项参数框中选择“采用线性模型”和“假定非负”，最后在规划求解参数对话框中单击“求解”得到本问题的最优解。本问题的最优解为:仪器A的产量为4台，仪器B的产量为2台。这时总利润最大，为70千元。 第四节 运输问题 例 海华设备厂均衡运输问题 海华设备厂下设三个位于不同地点的分厂A，B，C，该三个分厂生产同一种设备，设每月的生产能力分别为20台、30台和40台。海华设备厂有四个固定用户，该四个用户下月的设备需求量分别为20台、15台、23台和32台。设各分厂的生产成本相同，从各分厂至各用户的单位设备运输成本如下表所示，而且各分厂本月末的设备库存量为零。问该厂应如何安排下月的生产与运输，才能在满足四个用户需求的前提下使总运输成本最低。 海华设备厂运输成本表 14 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 解:本题可用下页图所示的网络图描述。 网络图左边的节点表示三个分厂，右边的节点表示四个用户，左、右节点间的连线表示从左边某分厂生产的设备运输到右边某用户，线段上的数字表示单位设备的运输成本。网络图最左边的数字分别为三个分厂的生产能力，最右边的四个数字分别为四个用户的需求量。 总供应量:20十30十40,90(台); 总需求量:20十15十23，32,90(台)。 即所有供应点的供应量之和等于所有需求点的需求量之和。所以本问题是供需均衡的运输问题。这时，所有供应点的供应量全部供应完毕，而所有需求点的需求量全部满足。 据题意，本问题的决策变量是下月各分厂为各用户生产与运输的设备数量。 可设分厂A下月为四个用户生产和运输的设备数量分别为: A1,A2,A3,A4(台); 运输成本(元,台) 分厂—名称 月生产能力 用户l 用户2 用户3 用户4 (吨) 分厂A 70 40 80 60 20 分厂B 80 100 110 50 30 分厂C 80 70 130 40 40 下月设备需求量(吨) 20 15 23 32 15 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 分厂B下月为四个用户生产和运输的设备数量分别为:B1，B2，B3，B4(台); 分厂C下月为四个用户生产和运输的设备数量分别为:C1，C2，C3，C4(台)。 本问题的目标函数是总运输成本最小化。 总运输成本的 计算公式 六西格玛计算公式下载结构力学静力计算公式下载重复性计算公式下载六西格玛计算公式下载年假计算公式 如下: 总运输成本,?(各分厂至各用户的设备运输成本)X(各分厂至各用户的运输量) 因此，该问题的目标函数为: o.b min 70Al+40A2+80A3+60A4+70Bl+100B2+110B3+50B4+80C1+70C2+130C3+40C4 本问题的约束条件有两个部分，第一部分是需求约束，即各用户从各分厂收到的设备总数不得少于它们的需求量: A1+Bl+C1,20 (用户1从三个分厂收到的设备总数应等于其需求量) A2+B2+C2,15 (用户2从三个分厂收到的设备总数应等于其需求量) A3+B3+C3,23 (用户3从三个分厂收到的设备总数应等于其需求量) A4+B4+C4,32 (用户4从三个分厂收到的设备总数应等于其需求量) 第二部分是生产能力约束，即各分厂生产和运输的设备总数不得超过其生产能力: +A2+A3+A4,20 (分厂A下月生产与运输的设备总数应等于其月生产能力) A1 B1+B2+B3+B4,30 (分厂B下月生产与运输的设备总数应等于其月生产能力) C1+C2+C3+C4,40 (分厂C下月生产与运输的设备总数应等于其月生产能力) 最后还有非负约束，即: A1，A2，A3，A4，B1，B2，B3，B4，C1，C2，C3，C4?0 (非负约束) 综上所述，本问题的线性规划模型如下: o.b min 70Al+40A2+80A3+60A4+70Bl+100B2+ 110B3+50B4+80C1+70C2+130C3+40C4 s.t A1+Bl+C1,20 A2+B2+C2,15 A3+B3+C3,23 A4+B4+C4,32 A1+A2+A3+A4,20 B1+B2+B3+B4,30 C1+C2+C3+C4,40 A1，A2，A3，A4，B1，B2，B3，B4，C1，C2，C3，C4?0 本问题的Spreadsheet描述及建模如下图所示。 16 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 用Excel中的规划求解功能求出本问题的结果。 最后的结果如下图所示。 17 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 第五节 最大流问题 例 供水网络问题。 某城市有7个供水加压站，分别用节点1，节点2，„„，节点7表示。见下图。其中节点l为水厂，各泵站间现有的管网用相应节点间的边表示。现规划在节点7处建一个开发区，经对现有管网调查，各段管网尚可增加的供水能力(万吨,日)如下图中各边上的数值所示。依照现有管网状况，从水厂(源点)到开发区(汇点)，每日最多可增加多少供水量? 18 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 解:本问题要解决的问题是在各管网可增加的供水能力为定值时，该网络可增加的从水厂至开发区的最大供水流量。这是一个网络最大流问题。这时可在网络图中添加一条从节点7(汇点)至节点1(源点)的“虚”边(由于实际上并不存在从节点7流向节点1的管道，所以称该边为“虚”的)。增加这条边的目的，是为了使网络中各节点的边形成回路，各节点的流出量与流入量的代数和(即净流出量)为零。 本问题可以看作在满足边容量约束条件下的网络流优化问题，目标函数是开发区(节点7)的总流人量(或虚拟的总流出量)最大化，这时节点7的总流人量(或虚拟的总流出量)就是网络最大流，即最大供水量。 本问题的Spreadsheet描述与求解如下页图所示。 (1)输入部分 首先输入已知数据。在单元格C22:I28中输入各节点间的边的容量增量。例如在单元格F22中输入3，表示从节点l至节点4的边可增加的供水能力为3(万吨,日)，等等。凡是节点间没有管道相连接的边，令其容量为零。从节点7至节点1的边为“虚”边，可设它的能力增量等于从源点出发的所有边的供水能力增量之和，即:3+4+3,10。此外，当网络中总流入量与总流出量达到平衡时，应满足以下条件: 各中间节点的流出量等于流入量，即它们的净流出量应等于零; 源点的流出量与从汇点经虚边的流入量的代数和应等于零; 汇点的流入量与从汇点经虚边的流出量的代数和应等于零。 因此，所有节点的净流出量均应等于零。在单元格C17:I17中输入各节点净流出量应取的值，它们均为零。 19 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ (2)决策变量 本问题的决策变量用C6:I12中的单元格表示，它们是从各节点到其他节点的流量，也是供水流量增量在网络中各条边上的分配量。例如单元格D6表示从节点1流入节点2的流量，也是连接节点1与节点2的边上的流量。 (3)目标函数 本问题的目标函数是流入节点7的总流人量最大(即开发区得到的供水流量增量最大)，或者从节点7流向节点1的流出量最大。在单元格L6中输入目标函数，它用下式计算: ,C12 (4)约束条件 本问题的约束条件有三个: 第一个约束是网络中边的容量约束。容量约束是指各节点间的边上的流量不得超过该边的容量。因此有: 单元格C6:I12中的数值(边流量)?单元格C22:I28中的数值(边容量) 第二个约束是节点总流人量与总流出量的平衡约束。其计算过程如下: ?计算各节点的总流人量 节点的总流人量等于所有流人该节点的流量之和。用单元格C13表示节点1的总流人量，其计算公式如下: 20 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ ,sum(C6:C12) 将上述公式复制到单元格D13:I13，得到其他节点的总流人量。 ?计算各节点的总流出量 节点的总流出量等于从该节点的所有流出量之和。用单元格J6表示节点1的总流出量，其计算公式如下: ,Sum(C6:I6) 将上述公式复制到单元格J7:J12，得到其他节点的总流出量。 ?计算各节点的净流出量 为便于计算节点的净流出量，需将单元格J6:J12的总流出量写入单元格C14:I14。可在单元格C14中输入: ,J6 然后，用同法逐个将单元格J6:J12的内容分别写入单元格D14:I14。也可以使用transpose(转置)命令完成这个工作。transpose是一个将行向量或列向量进行转置的命令，其步骤是:选择区域C14:I14，在单元格C14中输入: ,transpose(J6:J12) 按下Ctrld+Shift+Enter键，就将总流出量写入了单元格C14:I14。 节点的净流出量等于该节点的总流出量与总流入量之差即两者之代数和。在单元 格C17:I17中输入各节点的净流出量。单元格C15表示节点1的净流出量，它的计算公式如下: ,I14-C13 将上述公式复制到单元格D15:I15，得到其他节点的净流出量。 ?当网络中总流人量与总流出量达到平衡时，所有节点的净流出量均为零 (5)用Excel中的规划求解功能求出本问题的解 在规划求解参数框中输入目标单元格(目标函数地址)、可变单元格(决策变量地址)和两个约束条件，然后在规划求解选项参数框中选择“采用线性模型”和“假定非负”，最后求解得到本问题的最优解。规划求解参数框如下图所示。 21 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 模型运行结果见上上表。由表可知，本问题的最优解如下表所示。 这时，节点7的总流人量为9，达到最大值，即该供水网络最多可供给开发区的供 水流量增量为9(吨/日)。 上述结果可用如下的网络图表示。 22 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ 第六节 排队论 例 某高速路口收费站，汽车按泊松分布到达此高速路口，平均每小时90辆。每辆车通过收费口的平均时间为35秒，服从负指数分布。(排队系统中各项数量指标计算公式，请参考教材。) 用Spreadsheet求解M/M/1模型 M/M/1排队模型如下图所示。 1(在单元格B4、B5、B6输入参数。 B4:模型的时间单位，秒; B5:平均到达率，B5,90/3600(辆/秒); B6:平均服务率，B6,1,35(辆,秒)。 2(根据模型输入参数计算模型的输出参数，先求得直接计算的输出，见单元格 B10:B12。 ; B10:计算平均到达时间间隔，B10,1,B5(秒); : 23 (鬼谋哥Seo论坛)更多请多多支持，谢谢～ B11:计算每辆车的平均服务时间，B11,1,B6(秒); : B12:计算服务强度，B12,B5,H6。 3(计算系统数量指标，见单元格B15:B22。 ’ B15:if—算系统中没有顾客的概率，B15,1-B12; B16:输入顾客个数n; B17:计算系统中有n个顾客的概率,B17=B12^B16*B15; B18:计算等待队长，B18,B5xB5,(H6*(B6-B5)); B19:计算总队长，B19,B5,(B6一B5); B20:计算等待时间，B20,B5,((B6,B5)*B6); U21:计算逗留时间，B21,1,(B6-B5); B22:计算顾客到达必须等待的概率，B22,B5/B6. 其他符合M,M,1排队模型条件的排队模型都可用这个过程来汁舒:，计算时 只需要改变模则的输入参数即可。各公式如下图所示。 24 (鬼谋哥Seo论坛)更多请多多支持，谢谢～