Excel单变量与规划求解模拟运算表.pptx

1数据分析方法数据分析方法1.1.分析工具库分析工具库 Excel Excel 提供了一组数据分析工具，称为提供了一组数据分析工具，称为“分析工具库分析工具库”，在建立复杂统计或，在建立复杂统计或工程分析时可节省步骤。只需为每一个分析工具提供必要的数据和参数，该工工程分析时可节省步骤。只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或工程函数，在输出表格中显示相应的结果。其中有些具就会使用适宜的统计或工程函数，在输出表格中显示相应的结果。其中有些工具在生成输出表格时还能同时生成图表。工具在生成输出表格时还能同时生成图表。安装安装“分析工具库分析工具库”:要使用查看可用的分析工具，请单击要使用查看可用的分析工具，请单击“工具工具”菜单中的菜单中的“数据分析数据分析”命令。如命令。如果果“工具工具”菜单中没有菜单中没有“数据分析数据分析”命令，则需要安装命令，则需要安装“分析工具库分析工具库”。2.2.工作表函数工作表函数Excel Excel 还提供了其他一些统计、财务和工程函数。某些统计函数是内置函数，还提供了其他一些统计、财务和工程函数。某些统计函数是内置函数，而其他一些函数只有在安装了而其他一些函数只有在安装了“分析工具库分析工具库”之后才能使用。之后才能使用。预备知识预备知识要使用这些工具，用户还必须熟悉需要进行分析的统计学或工程学的特定要使用这些工具，用户还必须熟悉需要进行分析的统计学或工程学的特定领域。领域。23二、规划求解利用利用Excel2000Excel2000提供的规划求解可以解解决诸如线性规划、运输问题、食谱问题、提供的规划求解可以解解决诸如线性规划、运输问题、食谱问题、饲料配方问题、人事安排等，只要与生产、制造、分配、投资、财务、工程等有饲料配方问题、人事安排等，只要与生产、制造、分配、投资、财务、工程等有关的求最大利润，最小费用等问题均可使用规划求解法找到答案。关的求最大利润，最小费用等问题均可使用规划求解法找到答案。例例 1 1：某生产单位生产混合饲料，规定所含各种营养成份为：某生产单位生产混合饲料，规定所含各种营养成份为：A A（蛋白质）至少（蛋白质）至少有有1515，B B（脂肪）至少含有（脂肪）至少含有4.54.5，C C（淀粉）至少含有（淀粉）至少含有3030，D D（纤维素）不得（纤维素）不得超过超过1010。所能提供的原料有四种，甲（花生饼），每吨单价。所能提供的原料有四种，甲（花生饼），每吨单价500500元，含有各种元，含有各种营养成份分别为营养成份分别为A25A25、B2B2、C10C10、D2D2；乙原料（花生秧），每吨单价为；乙原料（花生秧），每吨单价为5050元，四种营养成份的含量分别为元，四种营养成份的含量分别为A8A8、B1B1、C5C5、D40D40；丙原料（骨粉），；丙原料（骨粉），每吨单价每吨单价350350元，含四种营养成份分别为元，含四种营养成份分别为A20A20、B8B8、C1C1、D0.5D0.5；丁原料；丁原料（玉米），每吨单价为（玉米），每吨单价为450450元，含四种营养成份分别为元，含四种营养成份分别为A7A7、B5B5、C40C40、D6D6。求在符合饲料营养成份要求的前提下，如何配合这四种饲料，使饲料配合的费用求在符合饲料营养成份要求的前提下，如何配合这四种饲料，使饲料配合的费用最低。最低。4数学模型根据以上资料，设四种原料的配比比例分别为根据以上资料，设四种原料的配比比例分别为X1X1、X2X2、X3X3、X4X4，可列线性规划模型如下：，可列线性规划模型如下：25%*X125%*X18%*X28%*X220%*X320%*X37%*X415%7%*X415%（蛋白质约束条件）（蛋白质约束条件）2%*X12%*X11%*X21%*X28%*X38%*X35%*X44.5%(5%*X44.5%(脂肪约束条件脂肪约束条件)10%*X110%*X15%*X25%*X21%*X31%*X340%*X430%40%*X430%（淀粉约束条件）（淀粉约束条件）2%*X12%*X140%*X240%*X20.5%*X30.5%*X36%*X410%6%*X410%（纤维素约束条件）（纤维素约束条件）X10X10，X20X20，X30X30，X40X40（非负条件）（非负条件）Z=500X1Z=500X150X250X2350X3350X3450X4450X4（目标函数值最小）（目标函数值最小）EXCELEXCEL操作演示操作演示5练习练习:规划求解规划求解设某工厂生产甲乙两种产品，生产甲产品需要设某工厂生产甲乙两种产品，生产甲产品需要A、B、C三三种原料的量分别为种原料的量分别为8、5、4，可获利润为，可获利润为9。生产乙产品。生产乙产品需要需要A、B、C三种原料的量分别为三种原料的量分别为6、5、9，可获利润，可获利润为为12。现在工厂中。现在工厂中A、B、C三种原料的量分别为三种原料的量分别为360、250、350，求生产甲、乙两种产品各多少才能使利润最，求生产甲、乙两种产品各多少才能使利润最大？最大利润是多少？大？最大利润是多少？数学模型数学模型8X1+6X2=3605X1+5X2=2504X1+9X2=0X2=0Z=9X1+12X2取最大值6三三、解方程式组解方程式组x1+x2+2x3+3x4=13x1-x2-x3-2x4=-42x1+3x2-x3-x4=-6x1+2x2+3x3-x4=-4 X1=-1X2=-1X3=0X4=17四、模拟运算表四、模拟运算表ExcelExcel作为一个电子表格其作用不仅仅是数据的电子化存储及排序和检作为一个电子表格其作用不仅仅是数据的电子化存储及排序和检索，它还有另外一项很重要的功能，那就是数据分析功能，这里用索，它还有另外一项很重要的功能，那就是数据分析功能，这里用得最多的就是模拟运算表。得最多的就是模拟运算表。例如：要制作一个数据分析例如：要制作一个数据分析表，要求显示公式中某个值的变化将如何影表，要求显示公式中某个值的变化将如何影响公式的结果。响公式的结果。模拟运算表模拟运算表提供了一种快捷手段，它可以通过一步操作提供了一种快捷手段，它可以通过一步操作计算出多种情况下的值；同时它还是一种有效的方法，可以查看和比计算出多种情况下的值；同时它还是一种有效的方法，可以查看和比较由工作表中不同变化所引起的各种结果。较由工作表中不同变化所引起的各种结果。例例.银行存款年利率为银行存款年利率为5%,5%,存入存入1000010000元元,存存1010年后本息合计多少元年后本息合计多少元?本息合计本息合计:10000*(1+0.05):10000*(1+0.05)1010如果存入金额从如果存入金额从1000010000每次增加每次增加10001000元变动到元变动到20000,20000,存存1010年后本息合计年后本息合计各是多少元各是多少元?81 1、单变量模拟运算表、单变量模拟运算表单变量模拟运算表的结构特点是，其输入数值被排列在单变量模拟运算表的结构特点是，其输入数值被排列在一列中（列引用）或一行中（行引用）。单变量模拟一列中（列引用）或一行中（行引用）。单变量模拟运算表中使用的公式必须引用输入单元格。运算表中使用的公式必须引用输入单元格。注意：注意：在一列或一行中，键入要替换工作表上的输入单元格的数值序列。在一列或一行中，键入要替换工作表上的输入单元格的数值序列。如果输入数值被排成一列，请在第一个数值的同一行且处于数值列右侧如果输入数值被排成一列，请在第一个数值的同一行且处于数值列右侧的单元格中，键入所需的公式。的单元格中，键入所需的公式。如果输入数值被排成一行，请在第一个数值的同一列且处于数值行下方如果输入数值被排成一行，请在第一个数值的同一列且处于数值行下方的单元格内，键入所需的公式。的单元格内，键入所需的公式。选定包含公式和需要被替换的数值的单元格区域。选定包含公式和需要被替换的数值的单元格区域。在在“数据数据”菜单中，单击菜单中，单击“模拟运算表模拟运算表”命令。命令。为单变量模拟运算表单改变公式，可以自动改变运算结果为单变量模拟运算表单改变公式，可以自动改变运算结果将模拟运算表的计算结果转换为常量将模拟运算表的计算结果转换为常量92 2、双变量模拟运算表、双变量模拟运算表例如：对于公式例如：对于公式 z=3x+4y+1z=3x+4y+1：要求当：要求当x x等于从等于从1 1到到4 4间的所有整间的所有整数，而数，而y y为为1 1到到7 7间所有整数时所有间所有整数时所有z z的值，可以使用模拟运算表的值，可以使用模拟运算表完成。完成。1 1、首先排好、首先排好x x与与y y的位置，的位置，3 3、在单元格中建立一个公式，、在单元格中建立一个公式，4 4、在公式所在行的右边和下面分别输入两个变量的变化值、在公式所在行的右边和下面分别输入两个变量的变化值4 4、选中这个方形的区域，、选中这个方形的区域，5 5、选择、选择“数据数据”菜单中的菜单中的“模拟运算表模拟运算表”命令，命令，