办公自动化高级应用案例教程（微课版）课件 任务6 优化与决策分析

任务6

优化与决策分析办公软件高级应用案例教程任务6优化与决策分析决策是为了解决某一问题，达到一定目标而做出的决定。决策分析是从问题开始到做出决策前分析和研究最优方案的过程。很多决策问题都会涉及到数学规划问题，如产品定价问题、生产计划问题等，即求解目标优化问题。Excel为数学规划问题提供了一个工具计算工具，即【规划求解】可以帮助用户解决线性规划、非线性规划和演化问题的求解。1-任务情境2-任务分析3-任务实施4-拓展实训5–综合实践任务情境小卓一次行业交流会议上了解到几家公司经营过程中的优化经验，感觉这些公司初始情况与自己公司目前的状态相似。由于有了前一阶段对于生产预测的研究，小卓对本公司的生产计划系统有了很深入的了解，为了改进公司的生产计划系统，决定借鉴同行优化经验采用Excel对现有生产计划安排系统进行优化，并使得： 所有生产系统投入最少； 公司销售收入达到最大。任务分析可以看到，小卓的目标实际上就是公司运营的全局目标，即获得最大的利润。这个目标不但涉及到如何安排生产过程，而且也涉及到产品定价问题。解决利润最大化问题方法首选数学规划方法，因此，小卓要达到任务目的，需要以下工作步骤： 学习并掌握基本的优化方法； 运用线性规划方法进行生产计划； 运用非线性规划方法进行生产计划； 改进现有预测模型。知识与能力目标知识目标 掌握基本数学规划工作步骤； 掌握【规划求解】工作步骤； 掌握【规划求解】之线性规划方法； 掌握【规划求解】之非线性规划方法； 掌握【规划求解】之演化规划方法。能力目标 通过任务学习能够对一些优化问题进行建模，并采用Excel【规划求解】工具进行求解，包括： 线性规划问题 非线性规划问题 非演化规划问题任务实施任务实施1—建立优化模型2—线性规划3—非线性规划4—非平滑规划6.1建立优化模型任务实施建立实际问题的优化模型通常包括四个基本步骤：1.识别决策变量2.确定目标函数3.识别所有适合的约束4.用数学形式表示目标函数和约束注：详见教材6.2线性规划任务实施线性规划6.3任务实施数学规划是数学中的一个分支，它主要研究的目标在给定的区域中寻找可以最小化或最大化某一函数的最优解，广泛应用于自然科学、社会科学和工程技术中。数学规划虽然包含很多分支，如线性规划、非线性规划、多目标规划、动态规划等，但都遵循一个基本的问题解决流程，即建立优化模型和求解模型两个基本阶段。建立优化模型是数学规划中最重要的一步，关系着对实际问题的抽象是否真实、是否能够反映实际问题根本特质。第二阶段则是利用数学方法进行求解，可以手工计算或利用计算机进行求解。6.3.1建立优化模型建立实际问题的优化模型通常包括四个基本步骤：识别决策变量确定目标函数识别所有适合的约束用数学形式表示目标函数和约束6.3.1建立优化模型决策变量决策变量代表实际问题中影响最终优化值，需要做出取值决策的变量，是优化模型想要确定的量。识别决策变量就是识别所有对问题最终优化值有显著性影响的变量。对于最终优化值没有显著影响的变量不予考虑。一个问题有一个或多个决策变量；不同的问题有不同的决策变量，如将要生产的不同产品的数量、用于研究和开发项目的投入资金金额、化工中各种材料的配比等。决策变量在数学上是目标函数的自变量，即决策变量的变化引起目标函数值的变化。6.3.1建立优化模型目标函数表示决策变量与问题中最优化值之间的变化关系。实际应用中，常常称代表最优值的变量为“目标函数”，如利润是公司期望取得最大化的变量，就是某些问题的目标函数。确定目标函数就是要找出代表优化值的变量和决策变量与这变量之间的对应关系。6.3.1建立优化模型识别所有适合的约束约束是实际问题中要取得最优化结果前提条件和所受的限制，即对决策变量和优化变量的约束条件。约束多种多样，可能是实际的或技术上的限制，可能是各种管理规定，也可能是法律法规的规范要求。约束不但增加了优化问题求解的难度，而且也限制了解得范围。必须识别所有适合的约束，否则就会得出错误的解。6.3.1建立优化模型用数学形式表示目标函数和约束6.3.2线性规划一般地，求线性目标函数在线性约束条件下的最大值或最小值的问题，统称为线性规划问题。主要研究两类问题：一类是用最少的资源满足确定目标，即当任务确定后，如何统筹安排，尽量做到以最少的人力、物力资源去完成任务；第二类则是用现有资源实现最佳输出，即如何安排使用已有的人力、物力资源，取得最佳输出结果。凡是有明确的线性优化目标，且能用线性约束方程（等式或者不等式）组描述其内部运行规则的问题，都能够用线性规划的方法求解。一些常见的应用领域有：企业营销策划、产品生产计划、采购与库存管理、工程设计优化、物流管理、人事管理、理财与投资、系统综合评价、宏观经济运行调控、城市管理、作战规划等。6.3.2线性规划线性规划模型6.3.2线性规划—小卓公司生产计划优化问题产品生产过程包括分装、总装和检验三个阶段，分别由三个车间完成。分装车间有50名熟练工人，分两个班次，每人每天工作8小时。总装车间有30名熟练工人，一个班次，每人每天工作8小时。检验车间有10名熟练工人，一个班次，每人每天工作8小时。公司每周工作6天。公司为了实现最大利润，可以只生产某一种利润最高的产品，但是这样不利于规避市场风险。公司在追求最大利润的同时，对每种产品的最低日产量进行了限制6.3.2线性规划—小卓公司生产计划优化问题6.3.2线性规划—小卓公司生产计划优化问题决策变量。豆浆机日产量；电饭煲日产量；热水壶日产量；压力锅日产量；果汁机日产量。目标函数。各产品日产量与该利润之间的确定关系就是目标函数。该目标函数是各产品日产量的线性函数。6.3.2线性规划—小卓公司生产计划优化问题面临的约束：分装车间：用于分装的总工时不能超过可以用的总工时数量。总装车间：用于总装的总工时不能超过可以用的总工时数量。检验车间：用于检验的总工时不能超过可以用的总工时数量。除此之外，公司还对每种产品的最低日产量进行了限制：豆浆机的日产量大于等于500台；电饭煲的日产量大于等于900台；热水壶的日产量大于等于900台；压力锅的日产量大于等于500台；果汁机的日产量大于等于650台；6.3.2线性规划—小卓公司生产计划优化问题线性规划模型表示豆浆机的日产量表示电饭煲的日产量表示热水壶的日产量表示压力锅的日产量表示果汁机的日产量

6.3.2.2求解线性规划模型【规划求解】是Excel中的一个加载宏，可求得工作表上目标单元格中公式的最优值，对与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。规划求解有三种引擎：单纯线性规划引擎。用于求解线性最优化问题。非线性CRG引擎。用于求解非线性规划问题。演化引擎。用于包含非平滑函数的优化问题。6.3.2.2求解线性规划模型—小卓公司生产计划优化问题1.建立电子表格模型。G15=B15+C15+D15+E15+F15B15=B14*B6C15=C14*C6D15=D14*D6E15=E14*E6F15=F14*F6G16=B16+C16+D16+E16+F16B16=B14*B7C16=C14*C7D16=D14*D7E16=E14*E7F16=F14*F7G17=B17+C17+D17+E17+F17B17=B14*B8C17=C14*C8D17=D14*D8E17=E14*E8F17=F14*F8G21=B21+C21+D21+E21+F21B21=B10*B14C21=C10*C14D21=D10*D14E21=E10*E14

F21=F10*F146.3.2.2求解线性规划模型—小卓公司生产计划优化问题2.求解模型【数据】>【分析】>【规划求解】在“设置目标”字段中输入$G$21或用鼠标选择单元格G21，作为优化目标。在“到”选择“最大值”单选按钮。在“通过改变可变单元”中输入$B$14:$F$14或用鼠标选择B14至F14的单元格，作为决策变量。6.3.2.2求解线性规划模型—小卓公司生产计划优化问题6.3.2.2求解线性规划模型—小卓公司生产计划优化问题运算结果报告目标函数的初值和终值在“目标单元格”区域。决策变量在“可变单元格”区域。“约束”区域中“单元格值”表示使目标函数取得最优值的约束条件的取值；“状态”列表示约束是否达到了限制值；“松弛值”表示取得最优解时，约束条件左边与右边之间的差值。6.3.2.2求解线性规划模型—小卓公司生产计划优化问题敏感性报告6.3.2.2求解线性规划模型—小卓公司生产计划优化问题极限值报告6.3.3非线性规划现实中，大部分优化问题都属于非线性问题，即优化问题中的变量之间不是简单的线性关系。非线性在优化模型中表现为目标函数和/或约束条件的非线性。非线性模型和线性模型最大的不同是，非线性模型没有一个通用的结构。非线性优化问题建模必须注意以下两点：模型仅是真实问题的有效表达，“完美模型”不可能存在。模型现实性越高，模型的复杂度越高。6.3.3.1建立非线性优化模型上节，小卓用线性规划方法对生产计划进行了优化。然而，这种优化结论是在假定产品单位利润固定，公司总利润与产品销量呈正比关系下得出的。现实中，利润和销量并不存在简单的线性关系。因为销量受到包括价格在内许多因素的影响。小卓为了更加精准的进行生产计划优化，必须考虑产品的利润与产品销量和价格变化关系。数6.3.3.1建立非线性优化模型产品成本

（元）价格系数

需求弹性系数

豆浆机335998460002.0电饭煲25254553001.5热水壶315511699001.8压力锅3988492001.2果汁机3623125226002.1表7-2产品需求模型参数及产品成本产品最低价（元）最高价（元）豆浆机350520电饭煲275400热水壶335480压力锅415600果汁机380500表7-3产品定价范围6.3.3.1建立非线性优化模型6.3.3.1建立非线性优化模型6.3.3.2求解非线性优化模型G27=B27+C27+D27+E27+F27B27=B18*(B17-B13)C27=C18*(C17-C13)D27=D18*(D17-D13)E27=E18*(E17-E13)

F27=F18*(F17-F13)6.3.3.2求解非线性优化模型1）在“设置目标”字段中输入$G$27引用单元格G27或用鼠标选择单元格G27，作为优化目标。2）在“到”选择“最大值”单选按钮。3）在“通过改变可变单元”中输入$B$17:$F$17或用鼠标选择B17至F17的单元格，作为优化的决策变量。5）点选“使用无约束变量为非负数”保证未约束的决策变量为非负数。6）选择“选择求解方法”为“非性GRG规划”。约束列表如下：$B$18>=$B$23$C$18>=$C$23$D$18>=$D$23$E$18>=$E$23$F$18>=$F$23$B$17>=$B$24$B$17<=$B$25$C$17>=$C$24$C$17<=$C$25$D$17>=$D$24$D$17<=$D$25$E$17>=$E$24$E$17<=$E$25$F$17>=$F$24$F$17<=$F$256.3.3.2求解非线性优化模型从结果中可以看出除果汁机外其余四种产品的日产量都是约束的日产量，且与线性优化的结果相同。不同的是，果汁机的日产量为671台比线性规划1500台小了很多，但是最大利润却比线性规划提高了5.2%。并且，公司各车间使用工时都未成为实现最大利润的瓶颈。小卓认识到在有限资源下综合考虑各个因素可有效降低企业投入，并能够实现最大利润。还有实现更高利润的其它方法吗？6.3.3.2求解非线性优化模型—无日产量约束小卓认识到在有限资源下综合考虑各个因素可有效降低企业投入，并能够实现最大利润。为此，小卓决定抛弃日产量约束，重新求解模型。五种产品都达到了约束的最大价格，除果汁机外其它产品的日产量都低于日产量约束，但是最大利润额却是有日产量约束情况下的1.235倍达到了46.7万元。6.3.3.2求解非线性优化模型—无日产量约束计算结果报告：三个车间可用工时的松弛值都比较大，使用工时仅占可用工时的48.4%，这说明在已经实现最大利润情况下，公司资源严重过剩。如果需求曲线绝对可信的情况下，公司需要开发新产品以充分利用公司资源，而不是一味的追求销量。6.3.3.2求解非线性优化模型—无日产量约束敏感性报告：“递减梯度”类似于线性规划报告中的“递减成本”，但是由于每个决策变量系数由许多参数决定，所以无法按照线性规划的思维去运用“递减梯度”。“拉格朗日乘数”类似于“阴影价格”。不过，拉格朗日乘数仅提供了当达到限制值的约束的右边增加1个单位时，目标函数中变化的大概数值，并不是准确的数值。本次优化，车间使用工时均为达到限制值，所以拉格朗日乘数均为零。6.3.3.2求解非线性优化模型—无日产量约束极限值报告：最大利润额以及每一个产品单位价格的可以接受的、满足所有约束条件的变化范围。可以看出热水壶单位价格取最小值时，公司实现的最大利润额最低。结合运算结果报告和敏感性报告，小卓公司目前要实现利润最大化不能盲目增加产量，而是如何研究定价和促销策略，或者是努力开发新产品，因为目前公司的制造能力已经远远超过了四种产品的实现利润的能力最大制造能力。6.3.4非平滑规划在实际的优化问题中，既存在非线性，又存在非连续取值（非平滑）变量，这样的问题很难用常规的方法来求解，如上一节的规划求解，如果限定产品的日产量为整数时，非线性GRG求解无法得到可行解。为了克服这种限制，科研人员开发了许多启发式算法来求解这种非平滑规划，这些算法有遗传算法、神经网络和禁忌搜索等6.3.4.1建立演化求解模型由于小卓要优化的产品日产量属于整数型变量，所以需要对产品日产量增加整数约束。另外在给产品定价时，按照惯例也不应该有小数，所以产品的价格也要限定为整数约束。6.3.4.2演化规划求解Excel【规划求解】中“演化”求解采用的算法是遗传算法。遗传算法是由美国密歇根大学计算机科学教授JohnHolland发现的，他借鉴生物进化理论中遗传和变异方法来寻求可行解。遗传算法首先选择50~200个可行解，通过变异和遗传产生新的可行解用于计算目标函数，并根据目标函数来选择最优的可行解用于下一次迭代计算和选择，直到目标函数值变化小于指定阈值，那些选择的满足目标函数的可行解即为演化规划的最优集合。使用演化求解需要遵循以下规则：为决策变量设置上下边界值。因为这种边界设置有利于遗传算法选择可行解，减少搜索范围。若优化问题没有对决策变量进行约束时，当求解达到某一个决策变量边界时，需要放宽边界；否则约束值即为决策变量的边界。由于遗传算法属于慢速收敛算法，所以应当将执行时间尽量设置大一些，或者不进行任何设置。6.3.4.2演化规划求解1.修改电子表格模型。为每一种产品单位价格单元增加整数约束。在添加约束对话中输入需要约束的单元格，在中间符合下列列表中选择“int”选项，右边约束文本框自动填充“整数”6.3.4.2演化规划求解由于【规划求解】不能为非决策变量单元格添加整数约束，所以手动为产品日产量添加整数约束。B18=ROUND(B11*B17^(-B12),0)C18=ROUND(C11*C17^(-C12),0)D18=ROUND(D11*D17^(-D12),0)E18=ROUND(E11*E17^(-E12),0)F18=ROUND(F11*F17^(-F12),0)6.3.4.2演化规划求解2.求解模型。首先设置演化求解的参数。

“收敛”表示目标函数变化的最小阈值，即当目标函数变化小于此值时，认为目标函数不再变化。“收敛”值的设定主要参考目标函数最小区别精度。“突变速率”表示可行解的变异速度，变异速度越快，产生最优解的可能性越大，但并不能报告产生最优解的速度越大，该值的设定通常参考同类问题求解速度进行调整。“总体大小”即种群大小表示参与求解的可行解的个数，该值在一定增大会提高求解速度。“随机种子”表示为算法产生伪随机数的初始化值，该值是为提高随机数真实性而设置的，一般用户不需要进行设置。“无改进的最大时间”表示目标函数达到收敛阈值后保持不变的时间长度，该值越大，求解所得到可行解越接近优化问题的最优解，也就是说，此值越长，每次执行的结果越接近，当时间长度达到一定时，则求解得到优化问题的最优解。6.3.4.2演化规划求解2.求解模型。演化求解结果。6.3.4.2演化规划求解2.求解模型。演化求解计算结果报告。6.3.4.2演化规划求解2.求解模型。演化求解总体报告。6.3.4.2演化规划求解2.求解模型。演化求解总体报告。“总体报告”即“种群报告”，向用户提供演化求解结束后，整个种群的基本信息；让用户可以洞察演化算法的性能和所建模型的特点，决定是否再次运行求解过程以获得更加理想的解。“种群报告”给出了遗传算法在整个求解过程中所发现的每一个决策变量和约束的最优值、均值、标准差、最大值和最小值。对“种群报告”合理的解释很大程度依赖于对问题的理解和过去求解经验。若果多次求解最优值非常相似，且标准差比较小，就有理由相信可行解接近全局最优。但是，如果多次求解最优值相差很大，小标准差则表示种群缺少多样性，应当提高突变速率并再次进行求解。6.3.4.2演化规划求解2.求解模型。展限分析。6.3.4.2演化规划求解2.求解模型。展限分析。在仅参考需求曲线的情况下，可以实现最大利润614948元利润，是价格有限制的1.31倍。但是，这个结果仅是从求解角度做出的，因为它已经突破了价格限制。实际中价格的限制除了考虑需求曲线的适用范围，而且也考虑了长期市场占有率。因此，在实际优化中不能取消价格的限制。拓展决策变量限制进行求解的目的不是求解一个新的优化解，而是通过范围扩展而确定先前所求优化结果是否合理。拓展实训实训1：求解最优生产计划实训2：为便民超市寻找最佳地址实训3：为便民超市寻找最佳地址实训求解最优生产计划某汽车零部件制造公司的以薄钢板为原材料，为两种型号汽车生产引擎盖。每种型号引擎盖的生产都包括五个步骤：冲压、钻孔、组装、喷漆，以及最后的包装发货，将外壳发到其最终的组装厂。每个步骤由独立车间执行，每个车间中单位的生产率（以小时计）以及可用的工时数量如表所示。实训1：求解最优生产计划车间A引擎盖B引擎盖可用工时（小时）冲压0.030.07200钻孔0.090.06300组装0.050.10300喷漆0.040.06220包装0.020.04100实训求解最优生产计划除此之外，制造1个A引擎盖，需要3.2平方米的薄钢板，制造1个B引擎盖，需要3.5平方米的薄钢板，而总共有5000平方米的原材料可供使用。公司想在下一个生产计划期间实现引擎盖生产总数最大化。使用规划求解来构建并求解一个