128992698452508750Excel2000(第二讲)单变量与规划求解、模拟运算表
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
模型求出原因变量的值?EXCEL提供一些这样的方法,如单变量求解,规 划求解等.
2.模拟运算表:根据数学模型(公式)为一组假设数据产生预测结果,以供 分析决策.
3.相关性分析:判断两组数据集（可以使用不同的度量单位）之间的关 系。
4.回归分析:当一个结果与一个或多个参数之间存在联系时，可以进行 1 回归分析，通常可由一个或多个自变量来预测一个变量的值。
10
2.工作表函数 Excel 还提供了其他一些统计、财务和工程函数。某些统计函数是内置函数， 而其他一些函数只有在安装了“分析工具库”之后才能使用。
预备知识
要使用这些工具，用户还必须熟悉需要进行分析的统计学或工程学的特定
领域。
2
一、单变量求解
解方程式：4x4+3x3+2x2+x+1=45620
如果已知一个公式的预期结果，而用于确定此公式结果的变量 输入值未知，就可使用“单变量求解”功能。当进行单变量求 解时，Microsoft Excel 会不断改变特定单元格中的值，直到 依赖于此单元格的公式返回所需的结果为止。
注意： • 在一列或一行中，键入要替换工作表上的输入单元格的数值序列。 • 如果输入数值被排成一列，请在第一个数值的同一行且处于数值列右
侧的单元格中，键入所需的公式。 • 如果输入数值被排成一行，请在第一个数值的同一列且处于数值行下
方的单元格内，键入所需的公式。 • 选定包含公式和需要被替换的数值的单元格区域。 • 在“数据”菜单中，单击“模拟运算表”命令。
4
数学模型
根据以上资料，设四种原料的配比比例分别为X1、X2、X3、X4， 可列线性规划模型如下：
25%*X1＋8%*X2＋20%*X3＋7%*X4≥15%（蛋白质约束条件） 2%*X1＋1%*X2＋8%*X3＋5%*X4≥4.5%(脂肪约束条件) 10%*X1＋5%*X2＋1%*X3＋40%*X4≥30%（淀粉约束条件） 2%*X1＋40%*X2＋0.5%*X3＋6%*X4≤10%（纤维素约束条 件） X1≥0，X2≥0，X3≥0，X4≥0（非负条件） Z=500X1＋50X2＋350X3＋450X4（目标函数值最小）
数学模型
8X1+6X2<=360 5X1+5X2<=250 4X1+9X2<=350
X1>=0 X2>=0 Z=9X1+12X2取最大值
6
三、解方程式组
x1+x2+2x3+3x4=1 3x1-x2-x3-2x4=-4 2x1+3x2-x3-x4=-6 x1+2x2+3x3-x4=-4
X1=-1 பைடு நூலகம்2=-1 X3=0 X4=1
例.银行存款年利率为5%,存入10000元,存10年后本息合计多少元?
本息合计:10000*(1+0.05)10
如果存入金额从10000每次增加1000元变动到20000,存10年后本息合计 各是多少元?
8
1、单变量模拟运算表
单变量模拟运算表的结构特点是，其输入数值被排列在 一列中（列引用）或一行中（行引用）。单变量模拟 运算表中使用的公式必须引用输入单元格。
EXCEL操作演示
5
练习:规划求解
设某工厂生产甲乙两种产品，生产甲产品需要A、B、C三 种原料的量分别为8、5、4，可获利润为9。生产乙产品 需要A、B、C三种原料的量分别为6、5、9，可获利润 为12。现在工厂中A、B、C三种原料的量分别为360、 250、350，求生产甲、乙两种产品各多少才能使利润最 大？最大利润是多少？
7
四、模拟运算表
Excel作为一个电子表格其作用不仅仅是数据的电子化存储及排序和检 索，它还有另外一项很重要的功能，那就是数据分析功能，这里用 得最多的就是模拟运算表。
例如：要制作一个数据分析表，要求显示公式中某个值的变化将如何影 响公式的结果。模拟运算表提供了一种快捷手段，它可以通过一步操作 计算出多种情况下的值；同时它还是一种有效的方法，可以查看和比 较由工作表中不同变化所引起的各种结果。
例 1 ：某生产单位生产混合饲料，规定所含各种营养成份为：A（蛋白质）至少 有15％，B（脂肪）至少含有4.5％，C（淀粉）至少含有30％，D（纤维素）不得 超过10％。所能提供的原料有四种，甲（花生饼），每吨单价500元，含有各种 营养成份分别为A25％、B2％、C10％、D2％；乙原料（花生秧），每吨单价为50 元，四种营养成份的含量分别为A8％、B1％、C5％、D40％；丙原料（骨粉）， 每吨单价350元，含四种营养成份分别为A20％、B8％、C1％、D0.5％；丁原料 （玉米），每吨单价为450元，含四种营养成份分别为A7％、B5％、C40％、D6％。 求在符合饲料营养成份要求的前提下，如何配合这四种饲料，使饲料配合的费用 最低。
➢为单变量模拟运算表单改变公式，可以自动改变运算结果
➢将模拟运算表的计算结果转换为常量
9
2、双变量模拟运算表
例如：对于公式 z=3x+4y+1：要求当x等于从1到4间的所有整 数，而y为1到7间所有整数时所有z的值，可以使用模拟运算表 完成。
1、首先排好x与y的位置， 3、在单元格中建立一个公式， 4、在公式所在行的右边和下面分别输入两个变量的变化值 4、选中这个方形的区域， 5、选择“数据”菜单中的“模拟运算表”命令，
数据分析方法
1.分析工具库 Excel 提供了一组数据分析工具，称为“分析工具库”，在建立复杂统计或
工程分析时可节省步骤。只需为每一个分析工具提供必要的数据和参数，该工 具就会使用适宜的统计或工程函数，在输出表格中显示相应的结果。其中有些 工具在生成输出表格时还能同时生成图表。 安装“分析工具库”: 要使用查看可用的分析工具，请单击“工具”菜单中的“数据分析”命令。如 果“工具”菜单中没有“数据分析”命令，则需要安装“分析工具库”。
例如，算式z=4x4+3x3+2x2+x+1，我们要求当z=45620时x的值 演示：
3
二、规划求解
利用Excel2000提供的规划求解可以解解决诸如线性规划、运输问题、食谱问题、 饲料配方问题、人事安排等，只要与生产、制造、分配、投资、财务、工程等有 关的求最大利润，最小费用等问题均可使用规划求解法找到答案。
数据分析
数据分析的含义 : 数据分析就是根据电子表格中的数据以图形或数值的方式得出一些有意
义的结论. 比如:数据的排序,筛选,运算,统计,图表化等等都可以说是数据分析的
手段. 这里我们主要讲以下几个数据分析问题: 1.变量求解:有些数据之间存在因果关系,这些因果关系可以用数学模型
来描述,如方程式,不等式等,如果知道了结果数据,能否根据这些数学
2.模拟运算表:根据数学模型(公式)为一组假设数据产生预测结果,以供 分析决策.
3.相关性分析:判断两组数据集（可以使用不同的度量单位）之间的关 系。
4.回归分析:当一个结果与一个或多个参数之间存在联系时，可以进行 1 回归分析，通常可由一个或多个自变量来预测一个变量的值。
10
2.工作表函数 Excel 还提供了其他一些统计、财务和工程函数。某些统计函数是内置函数， 而其他一些函数只有在安装了“分析工具库”之后才能使用。
预备知识
要使用这些工具，用户还必须熟悉需要进行分析的统计学或工程学的特定
领域。
2
一、单变量求解
解方程式：4x4+3x3+2x2+x+1=45620
如果已知一个公式的预期结果，而用于确定此公式结果的变量 输入值未知，就可使用“单变量求解”功能。当进行单变量求 解时，Microsoft Excel 会不断改变特定单元格中的值，直到 依赖于此单元格的公式返回所需的结果为止。
注意： • 在一列或一行中，键入要替换工作表上的输入单元格的数值序列。 • 如果输入数值被排成一列，请在第一个数值的同一行且处于数值列右
侧的单元格中，键入所需的公式。 • 如果输入数值被排成一行，请在第一个数值的同一列且处于数值行下
方的单元格内，键入所需的公式。 • 选定包含公式和需要被替换的数值的单元格区域。 • 在“数据”菜单中，单击“模拟运算表”命令。
4
数学模型
根据以上资料，设四种原料的配比比例分别为X1、X2、X3、X4， 可列线性规划模型如下：
25%*X1＋8%*X2＋20%*X3＋7%*X4≥15%（蛋白质约束条件） 2%*X1＋1%*X2＋8%*X3＋5%*X4≥4.5%(脂肪约束条件) 10%*X1＋5%*X2＋1%*X3＋40%*X4≥30%（淀粉约束条件） 2%*X1＋40%*X2＋0.5%*X3＋6%*X4≤10%（纤维素约束条 件） X1≥0，X2≥0，X3≥0，X4≥0（非负条件） Z=500X1＋50X2＋350X3＋450X4（目标函数值最小）
数学模型
8X1+6X2<=360 5X1+5X2<=250 4X1+9X2<=350
X1>=0 X2>=0 Z=9X1+12X2取最大值
6
三、解方程式组
x1+x2+2x3+3x4=1 3x1-x2-x3-2x4=-4 2x1+3x2-x3-x4=-6 x1+2x2+3x3-x4=-4
X1=-1 பைடு நூலகம்2=-1 X3=0 X4=1
例.银行存款年利率为5%,存入10000元,存10年后本息合计多少元?
本息合计:10000*(1+0.05)10
如果存入金额从10000每次增加1000元变动到20000,存10年后本息合计 各是多少元?
8
1、单变量模拟运算表
单变量模拟运算表的结构特点是，其输入数值被排列在 一列中（列引用）或一行中（行引用）。单变量模拟 运算表中使用的公式必须引用输入单元格。
EXCEL操作演示
5
练习:规划求解
设某工厂生产甲乙两种产品，生产甲产品需要A、B、C三 种原料的量分别为8、5、4，可获利润为9。生产乙产品 需要A、B、C三种原料的量分别为6、5、9，可获利润 为12。现在工厂中A、B、C三种原料的量分别为360、 250、350，求生产甲、乙两种产品各多少才能使利润最 大？最大利润是多少？
7
四、模拟运算表
Excel作为一个电子表格其作用不仅仅是数据的电子化存储及排序和检 索，它还有另外一项很重要的功能，那就是数据分析功能，这里用 得最多的就是模拟运算表。
例如：要制作一个数据分析表，要求显示公式中某个值的变化将如何影 响公式的结果。模拟运算表提供了一种快捷手段，它可以通过一步操作 计算出多种情况下的值；同时它还是一种有效的方法，可以查看和比 较由工作表中不同变化所引起的各种结果。
例 1 ：某生产单位生产混合饲料，规定所含各种营养成份为：A（蛋白质）至少 有15％，B（脂肪）至少含有4.5％，C（淀粉）至少含有30％，D（纤维素）不得 超过10％。所能提供的原料有四种，甲（花生饼），每吨单价500元，含有各种 营养成份分别为A25％、B2％、C10％、D2％；乙原料（花生秧），每吨单价为50 元，四种营养成份的含量分别为A8％、B1％、C5％、D40％；丙原料（骨粉）， 每吨单价350元，含四种营养成份分别为A20％、B8％、C1％、D0.5％；丁原料 （玉米），每吨单价为450元，含四种营养成份分别为A7％、B5％、C40％、D6％。 求在符合饲料营养成份要求的前提下，如何配合这四种饲料，使饲料配合的费用 最低。
➢为单变量模拟运算表单改变公式，可以自动改变运算结果
➢将模拟运算表的计算结果转换为常量
9
2、双变量模拟运算表
例如：对于公式 z=3x+4y+1：要求当x等于从1到4间的所有整 数，而y为1到7间所有整数时所有z的值，可以使用模拟运算表 完成。
1、首先排好x与y的位置， 3、在单元格中建立一个公式， 4、在公式所在行的右边和下面分别输入两个变量的变化值 4、选中这个方形的区域， 5、选择“数据”菜单中的“模拟运算表”命令，
数据分析方法
1.分析工具库 Excel 提供了一组数据分析工具，称为“分析工具库”，在建立复杂统计或
工程分析时可节省步骤。只需为每一个分析工具提供必要的数据和参数，该工 具就会使用适宜的统计或工程函数，在输出表格中显示相应的结果。其中有些 工具在生成输出表格时还能同时生成图表。 安装“分析工具库”: 要使用查看可用的分析工具，请单击“工具”菜单中的“数据分析”命令。如 果“工具”菜单中没有“数据分析”命令，则需要安装“分析工具库”。
例如，算式z=4x4+3x3+2x2+x+1，我们要求当z=45620时x的值 演示：
3
二、规划求解
利用Excel2000提供的规划求解可以解解决诸如线性规划、运输问题、食谱问题、 饲料配方问题、人事安排等，只要与生产、制造、分配、投资、财务、工程等有 关的求最大利润，最小费用等问题均可使用规划求解法找到答案。
数据分析
数据分析的含义 : 数据分析就是根据电子表格中的数据以图形或数值的方式得出一些有意
义的结论. 比如:数据的排序,筛选,运算,统计,图表化等等都可以说是数据分析的
手段. 这里我们主要讲以下几个数据分析问题: 1.变量求解:有些数据之间存在因果关系,这些因果关系可以用数学模型
来描述,如方程式,不等式等,如果知道了结果数据,能否根据这些数学