Excel数据处理与分析 教学课件 作者 郑丽敏 第11章.pptVIP

Excel数据处理与分析 Excel数据处理与分析 Excel数据处理与分析 Excel数据处理与分析 第11章 规划求解及其应用 第11章 规划求解及其应用 本章内容：本章主要介绍了Excel 2007“规划求解”分析工具的使用及在管理决策工作中的应用 教学目标：通过本章的学习，不仅使读者学会规划求解”分析工具的使用，而且还使读者了解“规划求解”分析工具在实际工作中的应用 内容提要 11.1 数学规划概述 11.3 应用举例 11.2 利用“规划求解”工具求解规划问题 11.1 数学规划概述 问题的提出 “规划求解”分析工具的使用 求解规划问题的步骤 在计划管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同要求，那就是：如何合理利用各种约束资源实现最佳的经济效益，也就是达到利润最大、成本最低、费用最省等目标。这就是本章要解决的在约束条件下寻求目标函数最优的规划问题。 11.1 数学规划概述-问题提出 一般来讲，这类规划问题都具有如下三个特点： 所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等 总是有明确的不等式约束条件。比如库存不能低于一定的数量，否则造成原料短缺或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等 问题都有直接或间接影响约束条件的一组输入值 11.1 数学规划概述-问题提出 * 例如：某饲养场饲养动物，设每头动物每头至少需690g蛋白质、28g矿物质、80mg维生素。现有五种饲料可供选择，各种饲料每kg营养成分含量及单价如表： 饲养场要求确定即满足动物生长的营养需求，又使费用最少的选择饲料的方案 11.1 数学规划概述-问题提出 数学模型： 根据题意,用X1 , X2 , X3 , X4 , X5分别表示A、B、C、D 、E 5种饲料的数量(kg)，用COST表示总费用，则得如下线性规划数学模型： 目标函数： ? 约束条件： 11.1 数学规划概述-问题提出 * 11.1 数学规划概述-“规划求解”工具的使用 Excel 2007 “规划求解”分析工具可以用来解决线性规划与非线性规划优化问题。“规划求解”分析工具可以用来解决最多有200个决策变量，100个外在约束和400个简单约束（决策变量整数约束的上下边界）的问题 加载“规划求解”分析工具 “规划求解”工具中各参数的解释和设置 11.1 数学规划概述-“规划求解”工具的使用 加载“规划求解”分析工具 “规划求解”是Excel 2007中的一个加载宏，要使用“规划求解”分析工具，必须要先进行加载(见教材) “规划求解”工具中各参数的解释和设置 (见教材) 11.1 数学规划概述-求解规划问题的步骤 利用“规划求解”分析工具求解规划问题的大致步骤如下： 建立问题的数学模型 在Excel中建立Exce工作表规划模型 设置 “规划求解参数” 设置可变单元格：确定决策变量 设置目标单元格：建立目标函数 设置约束条件：约束条件可以用线性等式或不等式表示，还有非负约束(≥)和整数约束(int) 11.1 数学规划概述-求解规划问题的步骤 利用“规划求解”分析工具求解规划问题的大致步骤如下： 进行规划求解。可以求得决策变量值和目标函数的最优解 建立规划求解报告 11.2 利用“规划求解”工具求解规划问题 例如：见上例 步骤1：建立问题的数学模型 步骤2：建立Exce工作表规划模型 [见规划求解及应用.xlsx—饲料工作表规划模型] 11.2 利用“规划求解”工具求解规划问题 步骤3：设置 “规划求解参数” 设置可变单元格：选择B8:F8区域 设置目标单元格：B2，根据题意，选择“最小” 11.2 利用“规划求解”工具求解规划问题 步骤3：设置 “规划求解参数” 设置可变单元格：选择B8:F8区域 设置目标单元格：B2，根据题意，选择“最小” 设置约束条件：添加约束 11.2 利用“规划求解”工具求解规划问题 步骤4：进行规划求解 [见规划求解及应用.xlsx—饲料规划问题求解结果] 11.2 利用“规划求解”工具求解规划问题 步骤5：建立规划求解报告 11.3 应用举例 例： 某葡萄酒厂生产A、B两个品种的葡萄酒。在生产过程中受到3个方面的限制：一是最大的发酵设备能力为55000单位；二是装瓶能力为48000单位；三是净化能力为12000单位。根据产品的特点，每生产1瓶A需要4个单位的发酵能