第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.

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

5、8.1.3 8.1.3 最优化最优化问题的基本要素问题的基本要素 Excel中可用以下方法进行最优化问题求解： 1）公式法 转换成数学模型，利用数学方法求解。 2）查表法 利用模拟运算表工具制作决策变量与目标 变量的对照表。 8.1.4 8.1.4 最优化最优化问题的求解方法问题的求解方法 3）利用规划求解工具 启动Excel中的规划求解工具进行求解。 规划求解工具是最有效和最方便的求解工具 据统计，85%的全球500强企业都使用规 划求解工具。 8.1.4 8.1.4 最优化问题的求解方法最优化问题的求解方法 安装Excel时选择“完全安装”或“自定 义安装”，不能选择“典型安装”。 8.2

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

7、各超市每月的需求量 l 要求满足的约束条件有： 总运货量=供应量 总收货量=需求量 8.2.2 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 线性规划问题举例线性规划问题举例 约束条件约束条件 目标函数目标函数 决策变量决策变量

8、l 规划求解的结果： 8.2.2 8.2.2 线性规划问题举例线性规划问题举例 l 可用SUM函数和数组公式代替 SUMPRODUCT函数： 8.2.2 8.2.2 线性规划问题举例线性规划问题举例 =SUMPRODUCT(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 规划求解

9、参数和规划求解报告规划求解参数和规划求解报告 2. 规划求解选项 8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告 3. 规划求解报告的生成与解读 l Excel规划求解工具在求解时，能生成以 下三种报告： 运算结果报告 敏感性报告 极限值报告 运算结果报告 列出目标单元格和可变单元格以及它 们的初始值、最终结果、约束条件和有关 约束条件的信息。 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.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告 8.2.3 8.2.3 规划求解参数和规划求解报告规划求解参数和规划求解报告 8.3 8.3 常见规划问题常见规划问题应用应用举例举例 8

11、.3.1 运输问题 l 运输问题要解决的是需要解决的问题是： 在满足某些条件的情况下，应如何制订 合理的运输方案，能够使运输成本最低？ 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 【例8-2】从三家工厂分别运货到三家超市的 单价运费（元/公斤）、各工厂每月的供应量 和各超市每月的需求量如表所示。其中，由 于路程相隔很远，工厂2不向超市C运送货物。 作为该企业的调度员，应如何安排各工 厂到各超市的运货量，使得每月的运货费最 低？ 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 运费运费 （元元/公斤）公斤） 超市超市A 超市超市B超市超市C 供应量供应量 （公斤）公斤）

12、工厂工厂1101512 100 工厂工厂21115- 110 工厂工厂3121413 90 需求量需求量 （公斤）公斤） 8010070 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 约束条件约束条件 目标函数目标函数 决策变量决策变量 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 【例8-3】从两家工厂分别运货到三家超市的 单价运费（元/公斤）、各工厂每月的供应量、 各超市每月的最低需求量和最高需求量如表 所示。 作为该企业的调度员，应如何安排各工 厂到各超市的运货量，使得每月的运货费最 低？ 8.3 8.3

13、常见规划问题应用举例常见规划问题应用举例 运费运费 （元元/公斤）公斤） 超市超市A 超市超市B 超市超市C 供应量供应量 （公斤）（公斤） 工厂工厂1121513120 工厂工厂2131412150 最低需求量最低需求量 （公斤）（公斤） 708060 最高需求量最高需求量 （公斤）（公斤） 90100不限 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 供求关系决定的约束条件供求关系决定的约束条件 供供= =求时求时：运货量：运货量 = =供应量供应量，需求量需求量 = =收货收货量量； 供供 求时求时：运货：运货量量=供应量，供应量，需求量需求量 = =收货量收货量; ; 供供

14、 =收货收货量量. . 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 8.3.2 选址问题 l 选址是在多个位置上选出最佳的地址， 使得建设成本或经营成本最低，以获取 最大利润。 l 选址问题属于典型的0-1规划问题，需要设 置决策变量取值只能为0或1的约束条件。 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 【例8-4】某地区有三个居民小区，有四个建 设报刊亭合适的地点，这四个地点对三个居民 小区的覆盖范围不同，且每个报刊亭的建设成 本也一样，如表所示。 小区和地点交叉处的值为“1”表示在这 个地点建报刊亭可以覆盖该小区，小区和地点 交叉处的值为空白表示在这个地点建报

15、刊亭不 能够覆盖该小区。 在这四个地点中选择哪几个地点建设报刊 亭，既可以覆盖三个居民小区，又能使总建设 费用最低？ 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 常见规划问题应用举例常见规划问题应用举例 8.3 8.3 常见规划问题应用举例常见规划问题应用举例 8.3.3 指派问题 l 指派问题是研究工作和人员之间的分配 问题，不同人员处理不同

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

17、数 决策变量决策变量 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，三家工厂能够生产四种产品：产 品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 原料配比问题是指在生产中多种产品使 用到多种的原材料，但原材料的