第8章Excel最优化模型

1、经济统计与分析软件应用基础第第8章章 最优化模型最优化模型学习要点学习要点l 了解最优化问题的概念l 掌握规划求解工具l 熟悉模型参数和报告l 掌握常见规划问题的解决第第8章章 最优化模型最优化模型目目 录录8.1 最优化问题概述8.2 线性规划8.3 常见规划问题应用举例 8.1 8.1 最优化问题概述最优化问题概述【经典问题】早饭前妈妈要干好多的事：烧开水要15分钟，擦桌椅要8分钟，准备暖瓶要1分钟，灌开水要2分钟，买油条要10分钟，煮牛奶要7分钟，如果灶具上只有一个火，那么全部做完这些工作最少需要多少时间？怎样安排？最优化思想和概念最优化思想和概念最优化问题： 就是指在给定条件下寻找最佳

2、规划方案的问题。最优化问题： 就是指在给定条件下寻找最佳规划方案的问题。【最佳】的含义包括两个方面：在资源给定时寻找最好的目标在目标确定下使用最少的资源8.1.18.1.1最优化问题基本最优化问题基本概念概念 经济管理中常见的最优化问题：经济管理中常见的最优化问题： 怎样安排运货使总运费最小？ 怎样组织生产使利润最大？ 怎样分配工作使总效率最高？ 怎样组织原材料使生产成本最低？8.1.18.1.1最优化问题基本概念最优化问题基本概念8.1.18.1.1最优化问题基本概念最优化问题基本概念【例8-1】从两家工厂分别运货到三家超市的单价运费、各工厂每月的供应量和各超市每月的需求量如表所示。 要求满

3、足原料可供量和最大销量的限制，应如何安排各工厂到各超市的运货量，使得每月的运货费最低？8.1.2 8.1.2 最优化最优化问题分类问题分类（1）有无约束条件（2）函数表达形式无约束条件的最优化问题有约束条件的最优化问题 线性规划问题 非线性规划问题8.1.2 8.1.2 最优化问题分类最优化问题分类【例8-1】从两家工厂分别运货到三家超市的单价运费、各工厂每月的供应量和各超市每月的需求量如表所示。 要求满足原料可供量和最大销量的限制，应如何安排各工厂到各超市的运货量，使得每月的运货费最低？8.1.3 8.1.3 最优化最优化问题问题的的基本要素基本要素目标变量目标变量约束条件约束条件决策变量决

4、策变量决策变量 最优化问题中寻找的求解结果，称作决策变量。目标变量 最优化问题中希望达到的一些极值目标，如最大利润、最低成本等。目标变量总是与决策变量有直接或间接的关系。约束条件 最优化问题中的限制条件，称作约束条件。8.1.3 8.1.3 最优化最优化问题的问题的基本要素基本要素l 求解最优化问题的首要问题是将实际问题数学化、模型化。l 即将实际问题通过以下三方面来表示： （1）一组决策变量 （2）一组用不等式或等式表示的约束条件 （3）目标函数l 这是求最优化问题的关键。8.1.3 8.1.3 最优化最优化问题的基本要素问题的基本要素Excel中可用以下方法进行最优化问题求解：1）公式法

5、转换成数学模型，利用数学方法求解。2）查表法 利用模拟运算表工具制作决策变量与目标变量的对照表。 8.1.4 8.1.4 最优化最优化问题的求解方法问题的求解方法3）利用规划求解工具 启动Excel中的规划求解工具进行求解。规划求解工具是最有效和最方便的求解工具 据统计，85%的全球500强企业都使用规划求解工具。8.1.4 8.1.4 最优化问题的求解方法最优化问题的求解方法 安装Excel时选择“完全安装”或“自定义安装”，不能选择“典型安装”。8.2 8.2 线性规划线性规划l 规划求解工具的加载： 【文件】/【选项】/【加载项】/ 【Excel加载项】 /【转到】/【加载宏】“规划求解

6、加载项”8.2.1 8.2.1 规划求解工具的加载规划求解工具的加载【例8-1】从两家工厂分别运货到三家超市的单价运费、各工厂每月的供应量和各超市每月的需求量如表所示。要求满足原料可供量和最大销量的限制,应如何安排各工厂到各超市的运货量，使得每月的运货费最低？8.2.2 8.2.2 线性规划问题举例线性规划问题举例l 首先看懂所给的数据：8.2.2 8.2.2 线性规划问题举例线性规划问题举例每件产品的单价运费各工厂每月的供应量各超市每月的需求量l 要求满足的约束条件有：总运货量=供应量总收货量=需求量8.2.2 8.2.2 线性规划问题举例线性规划问题举例约束条件约束条件目标函数目标函数决策

7、变量决策变量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 线性规划问题举例线性规划问题举例约束条件约束条件目标函数目标函数决策变量决策变量l 规划求解的结果：8.2.2 8.2.2 线性规划问题举例线性规划问题举例l 可用SUM函数和数组公式代替SUMPRODUCT函数：8.2.2 8.2.2 线性规划问题举例线性规划问题举例=SUMPRO

8、DUCT(C3:E4,C7:E8)=SUM(C3:E4,C7:E8)8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告l 将规划求解工具所求的模型保存起来，以方便以后调用。“规划求解参数”对话框 “装入/保存”1.规划求解参数保存与调用8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告2. 规划求解选项8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告3. 规划求解报告的生成与解读l Excel规划求解工具在求解时，能生成以下三种报告： 运算

9、结果报告 敏感性报告 极限值报告 运算结果报告 列出目标单元格和可变单元格以及它们的初始值、最终结果、约束条件和有关约束条件的信息。8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告 敏感性报告 “目标单元格”编辑框中所指定的公式的微小变化对求解结果都会有一定的影响。此报告提供关于求解结果对这些微小变化的敏感性的信息。含有整数约束条件的模型不能生成该报告8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规

10、划求解报告 极限值报告 列出目标单元格和可变单元格以及它们的数值、上下限和目标值。含有整数约束条件的模型不能生成该报告8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告8.3 8.3 常见规划问题常见规划问题应用应用举例举例8.3.1 运输问题l 运输问题要解决的是需要解决的问题是：在满足某些条件的情况下，应如何制订合理的运输方案，能够使运输成本最低？8.3 8.3 常见规划问题应用举例常见规划问题应用举例【例8-2】从三家工厂分别运货到三家超市的单价运费（元/公斤）、各工厂每月的供应量和各超

11、市每月的需求量如表所示。其中，由于路程相隔很远，工厂2不向超市C运送货物。 作为该企业的调度员，应如何安排各工厂到各超市的运货量，使得每月的运货费最低？8.3 8.3 常见规划问题应用举例常见规划问题应用举例运费运费（元元/公斤）公斤）超市超市A 超市超市B超市超市C供应量供应量（公斤）公斤）工厂工厂1101512100工厂工厂21115-110工厂工厂312141390需求量需求量（公斤）公斤）8010070 8.3 8.3 常见规划问题应用举例常见规划问题应用举例约束条件约束条件目标函数目标函数决策变量决策变量8.3 8.3 常见规划问题应用举例常见规划问题应用举例8.3 8.3 常见规划

12、问题应用举例常见规划问题应用举例【例8-3】从两家工厂分别运货到三家超市的单价运费（元/公斤）、各工厂每月的供应量、各超市每月的最低需求量和最高需求量如表所示。 作为该企业的调度员，应如何安排各工厂到各超市的运货量，使得每月的运货费最低？8.3 8.3 常见规划问题应用举例常见规划问题应用举例运费运费（元元/公斤）公斤）超市超市A 超市超市B 超市超市C供应量供应量（公斤）（公斤）工厂工厂1121513120工厂工厂2131412150最低需求量最低需求量（公斤）（公斤）708060 最高需求量最高需求量（公斤）（公斤）90100不限 8.3 8.3 常见规划问题应用举例常见规划问题应用举例供

13、求关系决定的约束条件供求关系决定的约束条件供供= =求时求时：运货量：运货量 = =供应量供应量，需求量需求量 = =收货收货量量；供供 求时求时：运货：运货量量=供应量，供应量，需求量需求量 = =收货量收货量; ;供供 =收货收货量量. .8.3 8.3 常见规划问题应用举例常见规划问题应用举例8.3.2 选址问题l 选址是在多个位置上选出最佳的地址，使得建设成本或经营成本最低，以获取最大利润。l 选址问题属于典型的0-1规划问题，需要设置决策变量取值只能为0或1的约束条件。8.3 8.3 常见规划问题应用举例常见规划问题应用举例【例8-4】某地区有三个居民小区，有四个建设报刊亭合适的地点

14、，这四个地点对三个居民小区的覆盖范围不同，且每个报刊亭的建设成本也一样，如表所示。 小区和地点交叉处的值为“1”表示在这个地点建报刊亭可以覆盖该小区，小区和地点交叉处的值为空白表示在这个地点建报刊亭不能够覆盖该小区。 在这四个地点中选择哪几个地点建设报刊亭，既可以覆盖三个居民小区，又能使总建设费用最低？8.3 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 常见规划

15、问题应用举例常见规划问题应用举例8.3 8.3 常见规划问题应用举例常见规划问题应用举例8.3.3 指派问题l 指派问题是研究工作和人员之间的分配问题，不同人员处理不同工作时所需的时间或成本不同，如何将一组工作更合理地分配给一组人员，使得总的工作效率最高。8.3 8.3 常见规划问题应用举例常见规划问题应用举例【例8-5】某公司同时收到三个项目（项目A、项目B和项目C）的开发任务，公司中有三位开发人员（小王、小孙和小李）可以承担这三个项目的开发工作，但不同开发人员承担不同项目所需的开发时间不同，如表所示。 要求：每个项目只能由一位开发人员承担，每位开发人员只能承担一个项目。 作为公司负责人，应

16、如何将三个项目分配给三位开发人员，使得总的开发时间最短？8.3 8.3 常见规划问题应用举例常见规划问题应用举例约束条件约束条件目标函数目标函数决策变量决策变量8.3 8.3 常见规划问题应用举例常见规划问题应用举例8.3 8.3 常见规划问题应用举例常见规划问题应用举例l 规划求解结果：8.3 8.3 常见规划问题应用举例常见规划问题应用举例8.3.4 生产问题l 生产问题是关于在组织生产过程中，如何合理安排生产计划，使生产成本最低或利润最大等，这也是在生产管理中最常见的问题。8.3 8.3 常见规划问题应用举例常见规划问题应用举例【例8-6】某企业有下属三家工厂：工厂1、工厂2和工厂3，三

17、家工厂能够生产四种产品：产品A、产品B、产品C和产品D。 由于工厂2没有安装产品C的生产线，所以工厂2不能生产产品C。每家工厂生产各种产品的单位成本、每家工厂的生产能力和每种产品的需求量如表所示。 问应如何安排各家工厂的生产计划，使既能够满足各产品的需求量，同时使总成本最低？8.3 8.3 常见规划问题应用举例常见规划问题应用举例约束条件约束条件目标函数目标函数决策变量决策变量8.3 8.3 常见规划问题应用举例常见规划问题应用举例8.3 8.3 常见规划问题应用举例常见规划问题应用举例l 规划求解结果：8.3 8.3 常见规划问题应用举例常见规划问题应用举例8.3.5 原料配比问题l 原料配比问题是指在生产中多种产品使用到多种的原材料，但原材料的