Excel数据分析工具及应用54.ppt

《Excel数据分析工具及应用54.ppt》由会员分享，可在线阅读，更多相关《Excel数据分析工具及应用54.ppt（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第5 5章章 ExcelExcel数据分析工具及应用数据分析工具及应用 数据分析工具的安装与卸载数据分析工具的安装与卸载 模拟运算表模拟运算表 方案分析方案分析 1234 规划求解规划求解 5.4 规划求解规划求解v在经济管理中，经常会遇到各种规划问题，在经济管理中，经常会遇到各种规划问题，例如：人力资源的调度、产品生产的安排、例如：人力资源的调度、产品生产的安排、运输线路的规划、生产材料的搭配、采购批运输线路的规划、生产材料的搭配、采购批次的确定等。这类问题有一个共同要求，那次的确定等。这类问题有一个共同要求，那就是：如何合理利用各种有限的资源实现最就是：如何合理利用各种有限的资源实现最佳

2、的经济效益，也就是达到利润最大、成本佳的经济效益，也就是达到利润最大、成本最低、费用最省等目标。最低、费用最省等目标。规划求解具有如下三个特点：规划求解具有如下三个特点：（1）所求问题都有单一的目标，如求生产的）所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等。目标函数的最佳值等。（2）总是有明确的不等式约束条件。比如库）总是有明确的不等式约束条件。比如库存不能低于一定的数量，否则造成原料短缺存不能低于一定的数量，否则造成原料短缺或产品缺货

3、；生产产品不能超过一定额度，或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等。否则会造成商品积压等。（3）问题都有直接或间接影响约束条件的一）问题都有直接或间接影响约束条件的一组输入值。组输入值。5.4.1 建立规划求解模型建立规划求解模型vExcel规划求解问题的基本构成规划求解问题的基本构成 （1）决策变量（）决策变量（variable）一个或一组可变单元格，可变单元格称为决策变一个或一组可变单元格，可变单元格称为决策变量，一组决策变量代表一个规划求解的方案量，一组决策变量代表一个规划求解的方案（2）目标函数）目标函数目标函数表示规划求解要达到的最终目标，是规目标函数表示规划求解

4、要达到的最终目标，是规划求解的关键。它是规划求解中可变量的函数划求解的关键。它是规划求解中可变量的函数 （3）约束条件）约束条件约束条件是实现目标的限制条件。约束条件是实现目标的限制条件。 建立规划求解模型：建立规划求解模型：第第1步步建立求解工作表建立求解工作表(输入原始数据及相应的各公式输入原始数据及相应的各公式)第第2步步设置求解参数设置求解参数选择选择“工具工具” “规划求解规划求解”菜单，设置以下求解的各项参数：菜单，设置以下求解的各项参数：v设置目标单元格：输入目标函数所在单元格设置目标单元格：输入目标函数所在单元格(为总余额单元为总余额单元格格)v设置目标：设置目标：最大值最大值

5、、最小值最小值或值的数值或值的数值(最大利润最大利润,即最大值即最大值)v设置可变单元格：它的确定决定结果（为生产数量）设置可变单元格：它的确定决定结果（为生产数量）v设置约束条件：单击设置约束条件：单击【添加添加】按钮按钮输入约束条件输入约束条件按添按添加加依次输入所有约束条件依次输入所有约束条件确定确定第第3步步保存求解结果保存求解结果在规划求解对话框中按在规划求解对话框中按“求解求解”在规划求解结果对话框中在规划求解结果对话框中按按“保存规划求解结果保存规划求解结果”5.4.2 求解优化问题求解优化问题【例例5-4】某农场每天至少使用某农场每天至少使用800磅特殊饲磅特殊饲料。这种特殊饲

6、料由玉米和大豆粉配制而成，料。这种特殊饲料由玉米和大豆粉配制而成，含有以下成份：含有以下成份：特殊饲料的营养要求是至少特殊饲料的营养要求是至少30%的蛋白质的蛋白质和至多和至多5%的纤维。该农场希望确定每天的纤维。该农场希望确定每天最小成本最小成本的饲料配制。的饲料配制。 v求解过程：求解过程：步骤步骤1：根据问题建立数学模型：根据问题建立数学模型因为饲料由玉米和大豆粉配制而成，所以模型的因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为：决策变量定义为： x=每天混合饲料中玉米的重量（磅）每天混合饲料中玉米的重量（磅）y=每天混合饲料中大豆粉的重量（磅）每天混合饲料中大豆粉的重量（磅）

7、目标函数是使配制这种饲料的每天总成本最小，目标函数是使配制这种饲料的每天总成本最小，因此表示为：因此表示为：z=0.3x+0.9y模型的约束条件是饲料的日需求量和对营养模型的约束条件是饲料的日需求量和对营养成份的需求量，具体表示为：成份的需求量，具体表示为：x+y800 /每天所需饲料每天所需饲料0.09x+0.6y0.3(x+y) /蛋白质蛋白质0.02x+0.06y0.05(x+y) /纤维纤维将上述不等式化简后，完整的模型为：将上述不等式化简后，完整的模型为：min z=0.31+0.92s.t. x+y8000.21x-0.3y00.03x-0.01y0 x,y0步骤步骤2 建立工作表

8、建立工作表工作表设计如图工作表设计如图5-23所示：所示：图5-23建立营养配方模型工作表 v其中，输入数据的单元格使用了阴影格式，其中，输入数据的单元格使用了阴影格式，即即B4:C7和和F5:F7；变量和目标函数单元格；变量和目标函数单元格为为B12:D12；D4:D7中输入了约束公式到中输入了约束公式到vD4=SUMPRODUCT(B4:C4,$B$12:$C$12)vD5=SUMPRODUCT(B5:C5,$B$12:$C$12)vD6= SUMPRODUCT(B6:C6,$B$12:$C$12)vD7= SUMPRODUCT(B7:C7,$B$12:$C$12)步骤步骤3 应用规划求解

9、工具应用规划求解工具 v单击单击【工具工具规划求解规划求解】，出现如图，出现如图5-24所示的所示的“规划求解参数规划求解参数”对话框，设计对话框，设计相应的参数。相应的参数。图5-24规划求解参数设置v并且单击并且单击【添加添加】按钮，添加相应的约束，按钮，添加相应的约束，如图如图5-25所示。所示。图5-25添加相应的约束条件v注意，上图所示的约束中，添加了非负注意，上图所示的约束中，添加了非负限制，即限制，即$B$12:$C$12=0。 步骤步骤4 求解求解 v设置好参数后，单击设置好参数后，单击【规划求解参数规划求解参数】对话对话框中的框中的【求解求解】按钮，结果如图按钮，结果如图5-27所示。所示。图5-27规划求解结果 本章小结v本章详细介绍了本章详细介绍了Excel数据分析工具在经济活数据分析工具在经济活动中常用的一些工具，包括模拟运算表、方动中常用的一些工具，包括模拟运算表、方案管理器和规划求解。由于案管理器和规划求解。由于“分析工具库分析工具库”中的分析工具具有很强的专业性，本章通过中的分析工具具有很强的专业性，本章通过实例介绍了这些工具的功能和使用方法。通实例介绍了这些工具的功能和使用方法。通过对本章的学习，希望读者能够对过对本章的学习，希望读者能够对Excel数据数据分析工具有深入的理解和掌握。分析工具有深入的理解和掌握。