第8章Excel最优化模型.pptx

经济统计与分析软件应用基础,第8章最优化模型,学习要点,了解最优化问题的概念掌握规划求解工具熟悉模型参数和报告掌握常见规划问题的解决,第8章最优化模型,目录,8.1最优化问题概述8.2线性规划8.3常见规划问题应用举例,8.1最优化问题概述,【经典问题】早饭前妈妈要干好多的事：烧开水要15分钟，擦桌椅要8分钟，准备暖瓶要1分钟，灌开水要2分钟，买油条要10分钟，煮牛奶要7分钟，如果灶具上只有一个火，那么全部做完这些工作最少需要多少时间？怎样安排？,最优化思想和概念,最优化问题：就是指在给定条件下寻找最佳规划方案的问题。,最优化问题：就是指在给定条件下寻找最佳规划方案的问题。,【最佳】的含义包括两个方面：在资源给定时寻找最好的目标在目标确定下使用最少的资源,8.1.1最优化问题基本概念,经济管理中常见的最优化问题：,怎样安排运货使总运费最小？怎样组织生产使利润最大？怎样分配工作使总效率最高？怎样组织原材料使生产成本最低？,8.1.1最优化问题基本概念,8.1.1最优化问题基本概念,【例8-1】从两家工厂分别运货到三家超市的单价运费、各工厂每月的供应量和各超市每月的需求量如表所示。要求满足原料可供量和最大销量的限制，应如何安排各工厂到各超市的运货量，使得每月的运货费最低？,8.1.2最优化问题分类,（1）有无约束条件,（2）函数表达形式,无约束条件的最优化问题,有约束条件的最优化问题,线性规划问题,非线性规划问题,8.1.2最优化问题分类,【例8-1】从两家工厂分别运货到三家超市的单价运费、各工厂每月的供应量和各超市每月的需求量如表所示。要求满足原料可供量和最大销量的限制，应如何安排各工厂到各超市的运货量，使得每月的运货费最低？,8.1.3最优化问题的基本要素,目标变量,约束条件,决策变量,决策变量最优化问题中寻找的求解结果，称作决策变量。目标变量最优化问题中希望达到的一些极值目标，如最大利润、最低成本等。目标变量总是与决策变量有直接或间接的关系。约束条件最优化问题中的限制条件，称作约束条件。,8.1.3最优化问题的基本要素,求解最优化问题的首要问题是将实际问题数学化、模型化。即将实际问题通过以下三方面来表示：（1）一组决策变量（2）一组用不等式或等式表示的约束条件（3）目标函数这是求最优化问题的关键。,8.1.3最优化问题的基本要素,Excel中可用以下方法进行最优化问题求解：1）公式法转换成数学模型，利用数学方法求解。2）查表法利用模拟运算表工具制作决策变量与目标变量的对照表。,8.1.4最优化问题的求解方法,3）利用规划求解工具启动Excel中的规划求解工具进行求解。,规划求解工具是最有效和最方便的求解工具,据统计，85%的全球500强企业都使用规划求解工具。,8.1.4最优化问题的求解方法,安装Excel时选择“完全安装”或“自定义安装”，不能选择“典型安装”。,8.2线性规划,规划求解工具的加载：【文件】/【选项】/【加载项】/【Excel加载项】/【转到】/【加载宏】,“规划求解加载项”,8.2.1规划求解工具的加载,【例8-1】从两家工厂分别运货到三家超市的单价运费、各工厂每月的供应量和各超市每月的需求量如表所示。要求满足原料可供量和最大销量的限制,应如何安排各工厂到各超市的运货量，使得每月的运货费最低？,8.2.2线性规划问题举例,首先看懂所给的数据：,8.2.2线性规划问题举例,每件产品的单价运费,各工厂每月的供应量,各超市每月的需求量,要求满足的约束条件有：,总运货量=供应量,总收货量=需求量,8.2.2线性规划问题举例,约束条件,目标函数,决策变量,8.2.2线性规划问题举例,8.2.2线性规划问题举例,=SUM(C7:E7),=SUM(C8:E8),=SUM(C7:C8),=SUM(D7:D8),=SUM(E7:E8),=SUMPRODUCT(C3:E4,C7:E8),8.2.2线性规划问题举例,约束条件,目标函数,决策变量,规划求解的结果：,8.2.2线性规划问题举例,可用SUM函数和数组公式代替SUMPRODUCT函数：,8.2.2线性规划问题举例,=SUMPRODUCT(C3:E4,C7:E8),=SUM(C3:E4,C7:E8),8.2.3规划求解参数和规划求解报告,将规划求解工具所求的模型保存起来，以方便以后调用。,“规划求解参数”对话框“装入/保存”,1.规划求解参数保存与调用,8.2.3规划求解参数和规划求解报告,8.2.3规划求解参数和规划求解报告,2.规划求解选项,8.2.3规划求解参数和规划求解报告,3.规划求解报告的生成与解读,Excel规划求解工具在求解时，能生成以下三种报告：,运算结果报告敏感性报告极限值报告,运算结果报告列出目标单元格和可变单元格以及它们的初始值、最终结果、约束条件和有关约束条件的信息。,8.2.3规划求解参数和规划求解报告,8.2.3规划求解参数和规划求解报告,敏感性报告“目标单元格”编辑框中所指定的公式的微小变化对求解结果都会有一定的影响。此报告提供关于求解结果对这些微小变化的敏感性的信息。,含有整数约束条件的模型不能生成该报告,8.2.3规划求解参数和规划求解报告,8.2.3规划求解参数和规划求解报告,极限值报告列出目标单元格和可变单元格以及它们的数值、上下限和目标值。,含有整数约束条件的模型不能生成该报告,8.2.3规划求解参数和规划求解报告,8.2.3规划求解参数和规划求解报告,8.3常见规划问题应用举例,8.3.1运输问题,运输问题要解决的是需要解决的问题是：在满足某些条件的情况下，应如何制订合理的运输方案，能够使运输成本最低？,8.3常见规划问题应用举例,【例8-2】从三家工厂分别运货到三家超市的单价运费（元/公斤）、各工厂每月的供应量和各超市每月的需求量如表所示。其中，由于路程相隔很远，工厂2不向超市C运送货物。作为该企业的调度员，应如何安排各工厂到各超市的运货量，使得每月的运货费最低？,8.3常见规划问题应用举例,8.3常见规划问题应用举例,约束条件,目标函数,决策变量,8.3常见规划问题应用举例,8.3常见规划问题应用举例,【例8-3】从两家工厂分别运货到三家超市的单价运费（元/公斤）、各工厂每月的供应量、各超市每月的最低需求量和最高需求量如表所示。作为该企业的调度员，应如何安排各工厂到各超市的运货量，使得每月的运货费最低？,8.3常见规划问题应用举例,8.3常见规划问题应用举例,供求关系决定的约束条件供=求时：运货量=供应量，需求量=收货量；供求时：运货量=收货量.,8.3常见规划问题应用举例,8.3.2选址问题,选址是在多个位置上选出最佳的地址，使得建设成本或经营成本最低，以获取最大利润。,选址问题属于典型的0-1规划问题，需要设置决策变量取值只能为0或1的约束条件。,8.3常见规划问题应用举例,【例8-4】某地区有三个居民小区，有四个建设报刊亭合适的地点，这四个地点对三个居民小区的覆盖范围不同，且每个报刊亭的建设成本也一样，如表所示。小区和地点交叉处的值为“1”表示在这个地点建报刊亭可以覆盖该小区，小区和地点交叉处的值为空白表示在这个地点建报刊亭不能够覆盖该小区。在这四个地点中选择哪几个地点建设报刊亭，既可以覆盖三个居民小区，又能使总建设费用最低？,8.3常见规划问题应用举例,=SUMPRODUCT(C3:F3,$C$7:$F$7),=SUMPRODUCT(C4:F4,$C$7:$F$7),=SUMPRODUCT(C5:F5,$C$7:$F$7),=SUMPRODUCT(C7:F7,C6:F6),8.3常见规划问题应用举例,8.3常见规划问题应用举例,8.3.3指派问题,指派问题是研究工作和人员之间的分配问题，不同人员处理不同工作时所需的时间或成本不同，如何将一组工作更合理地分配给一组人员，使得总的工作效率最高。,8.3常见规划问题应用举例,【例8-5】某公司同时收到三个项目（项目A、项目B和项目C）的开发任务，公司中有三位开发人员（小王、小孙和小李）可以承担这三个项目的开发工作，但不同开发人员承担不同项目所需的开发时间不同，如表所示。要求：每个项目只能由一位开发人员承担，每位开发人员只能承担一个项目。作为公司负责人，应如何将三个项目分配给三位开发人员，使得总的开发时间最短？,8.3常见规划问题应用举例,约束条件,目标函数,决策变量,8.3常见规划问题应用举例,8.3常见规划问题应用举例,规划求解结果：,8.3常见规划问题应用举例,8.3.4生产问题,生产问题是关于在组织生产过程中，如何合理安排生产计划，使生产成本最低或利润最大等，这也是在生产管理中最常见的问题。,8.3常见规划问题应用举例,【例8-6】某企业有下属三家工厂：工厂1、工厂2和工厂3，三家工厂能够生产四种产品：产品A、产品B、产品C和产品D。由于工厂2没有安装产品C的生产线，所以工厂2不能生产产品C。每家工厂生产各种产品的单位成本、每家工厂的生产能力和每种产品的需求量如表所示。问应如何安排各家工厂的生产计划，使既能够满足各产品的需求量，同时使总成本最低？,8.3常见规划问题应用举例,约束条件,目标函数,决策变量,8.3常见规划问题应用举例,8.3常见规划问题应用举例,规划求解结果：,8.3常见规划问题应用举例,8.3.5原料配比问题,原料配比问题是指在生产中多种产品使用到多种的原材料，但原材料的供应量有限，而不同产品的利润不同，在原料有