第六章 数据高级分析工具的使用.ppt

Excel 2007 统计应用统计应用数据高数据高级分析工具的使用分析工具的使用Excel 2007 统计应用统计应用单变量求解是假定一个公式要取得某一个结果值，其中变量的引单变量求解是假定一个公式要取得某一个结果值，其中变量的引用单元格应取值为多少的问题。用单元格应取值为多少的问题。实验：实验：职工的年终奖金是全年销售额的职工的年终奖金是全年销售额的0.3，前三个季度的销售，前三个季度的销售额已知，该职工想知道第四季度的销售额为多少时，才能保证年额已知，该职工想知道第四季度的销售额为多少时，才能保证年终奖金为终奖金为1000元。元。（1）计算职工年终奖金。）计算职工年终奖金。（2）打开）打开“单变量求解单变量求解”对话框，设置单变量求解的变量。对话框，设置单变量求解的变量。（3）求解出年终奖金为）求解出年终奖金为1000元时，第四季度的销售额。元时，第四季度的销售额。注：注：“单变量求解单变量求解”命令在它执行命令在它执行100次求解与指定目标值的差次求解与指定目标值的差在在0.001之内停止运算。之内停止运算。单变量求解量求解Excel 2007 统计应用统计应用 数据表（模拟运算表）是一个单元格区域，它可显示一个或多个数据表（模拟运算表）是一个单元格区域，它可显示一个或多个公式中替换不同值的结果。有两种类型：单输入模拟运算表和双公式中替换不同值的结果。有两种类型：单输入模拟运算表和双输入模拟运算表。输入模拟运算表。单输入模拟运算表中，可以对一个变量输入不同的值，从而查看单输入模拟运算表中，可以对一个变量输入不同的值，从而查看它对一个或多个公式的影响。它对一个或多个公式的影响。双输入模拟运算表中，对两个变量输入不同的值，查看它对一双输入模拟运算表中，对两个变量输入不同的值，查看它对一 个公式的影响。个公式的影响。模模拟运算表运算表Excel 2007 统计应用统计应用实验：实验：某公司长期贷款某公司长期贷款500000元，贷款年利率为元，贷款年利率为8，贷款年限为，贷款年限为10年，每年还款一次。根据贷款的分期等额偿还金额，利用年，每年还款一次。根据贷款的分期等额偿还金额，利用“数数据表据表”快速求出当贷款金额发生变化时，贷款年限、贷款利率、快速求出当贷款金额发生变化时，贷款年限、贷款利率、还款期数均不变的情况下，每期应支付的还款金额。还款期数均不变的情况下，每期应支付的还款金额。（1）选中单元格区域，打开）选中单元格区域，打开“数据表数据表”对话框。对话框。（2）设置数据表运算列变量，在）设置数据表运算列变量，在“输入引用列的单元格输入引用列的单元格”右侧，右侧，单击单击“拾取器拾取器”按钮。按钮。（3）求出不同的贷款额，每期应偿还的金额。）求出不同的贷款额，每期应偿还的金额。1 单变量的数据表运算量的数据表运算Excel 2007 统计应用统计应用 当贷款当贷款年利率和借款期数发生变化时，也可以利用年利率和借款期数发生变化时，也可以利用“数据表数据表”快快速得到不同的贷款年利率和贷款期数下应付的还款金额。速得到不同的贷款年利率和贷款期数下应付的还款金额。（1）选中单元格区域，打开）选中单元格区域，打开“数据表数据表”对话框。对话框。（2）设值数据表运算得的行列变量。）设值数据表运算得的行列变量。（3）求出不同的贷款额，每期应偿还的金额。）求出不同的贷款额，每期应偿还的金额。2 多多变量的数据表运算量的数据表运算Excel 2007 统计应用统计应用方案管理器是基于多个变量的结果预算，模拟运算表和单变量求方案管理器是基于多个变量的结果预算，模拟运算表和单变量求解只适用于单变量或两个变量的情况。在数据假设分析中常常涉解只适用于单变量或两个变量的情况。在数据假设分析中常常涉及多个不确定的因素（即多变量），为此可以使用方案管理器来及多个不确定的因素（即多变量），为此可以使用方案管理器来进行多变量管理。进行多变量管理。方案管理器方案管理器Excel 2007 统计应用统计应用 实验：实验：创建创建“个人住房公积金贷款月还款额个人住房公积金贷款月还款额”方案、方案、“个人住房个人住房商业贷款月还款额商业贷款月还款额”方案、方案、“非个人住房商业贷款月还款额非个人住房商业贷款月还款额”方方案。案。（1）打开）打开“方案管理器方案管理器”对话框，单击对话框，单击“添加按钮添加按钮”。（2）设置方案名称为）设置方案名称为“个人住房公积金贷款月还款额个人住房公积金贷款月还款额”，选取可，选取可变单元格，单击变单元格，单击“确定确定”，显示方案变量值，单击，显示方案变量值，单击“确定确定”后可后可以看到建立的方案。以看到建立的方案。（3）分别切换到个人住房商业贷款月还款额和非个人住房商业贷）分别切换到个人住房商业贷款月还款额和非个人住房商业贷款月还款额的工作表中，利用相同的方法创建款月还款额的工作表中，利用相同的方法创建“个人住房商业贷个人住房商业贷款月还款额款月还款额”方案和方案和“非个人住房商业贷款月还款额非个人住房商业贷款月还款额”方案。方案。1 创建方案建方案Excel 2007 统计应用统计应用方案创建完成后，当数据发生变化时，还可以对其进行编辑操作，方案创建完成后，当数据发生变化时，还可以对其进行编辑操作，例如修改方案、剔除方案等。例如修改方案、剔除方案等。（1）选中要修改的方案，单击）选中要修改的方案，单击“编辑编辑”按钮，可更改方案名、可按钮，可更改方案名、可变单元格。变单元格。（2）选中要删除的方案，单击）选中要删除的方案，单击“删除删除”按钮即可删除。按钮即可删除。2 编辑方案方案Excel 2007 统计应用统计应用在当前工作表中建立方案后，打开在当前工作表中建立方案后，打开“方案管理器方案管理器”则只显示当前则只显示当前工作表中建立的方案，其他工作表中建立的方案则不显示。而在工作表中建立的方案，其他工作表中建立的方案则不显示。而在实际工作中，为了便于方案的管理与使用，则可以通过实际工作中，为了便于方案的管理与使用，则可以通过“合并方合并方案案”功能直接复制其他工作表中的方案，或复制其他工作蒲中的功能直接复制其他工作表中的方案，或复制其他工作蒲中的方案。方案的合并需要逐一进行，即每张工作表中的方案需要逐方案。方案的合并需要逐一进行，即每张工作表中的方案需要逐一合并。一合并。实验：实验：将将“个人住房公积金贷款月还款额个人住房公积金贷款月还款额”工作表和工作表和“非个人住非个人住房商业贷款月还款额房商业贷款月还款额”工作表合并到工作表合并到“个人住房商业贷款月还款个人住房商业贷款月还款额额”工作表中。工作表中。（1）合并）合并“个人住房公积金贷款月还款额个人住房公积金贷款月还款额”工作表方案到工作表方案到“个人个人住房商业贷款月还款额住房商业贷款月还款额”工作表中。打开方案管理器，单击工作表中。打开方案管理器，单击“合合并并”，选中要合并方案的工作表，选中要合并方案的工作表（2）合并）合并“非个人住房公积金贷款月还款额非个人住房公积金贷款月还款额”工作表方案到工作表方案到“个个人住房商业贷款月还款额人住房商业贷款月还款额”工作表中。工作表中。3 合并方案合并方案Excel 2007 统计应用统计应用 如果当前工作表中创建了多个方案，或是从其他工作蒲中（工作如果当前工作表中创建了多个方案，或是从其他工作蒲中（工作表）复制得到多个方案，为了更好的对多个方案进行比较分析，表）复制得到多个方案，为了更好的对多个方案进行比较分析，可以利用建立方案摘要的方法将所有方案集中在一个工作表中显可以利用建立方案摘要的方法将所有方案集中在一个工作表中显示出来。示出来。（1）单击）单击“摘要摘要”按钮，选择结果单元格。按钮，选择结果单元格。（2）单击）单击“确定确定”按钮自动建立按钮自动建立“方案摘要方案摘要”工作表。工作表。4 创建方案摘要建方案摘要Excel 2007 统计应用统计应用在使用规划求解前，首先要理清求解目标，看是否希望目标值越大越好在使用规划求解前，首先要理清求解目标，看是否希望目标值越大越好或是越小越好，或者等于某特定的数值，然后设定好变量存储格与条件或是越小越好，或者等于某特定的数值，然后设定好变量存储格与条件限制。接着再根据设定的目标，运用规划求解为目标单元格中的值寻求限制。接着再根据设定的目标，运用规划求解为目标单元格中的值寻求一个最符合求解目标的值。一个最符合求解目标的值。实验：实验：求求A产品和产品和B产品的总收益在指定的限制条件下可以获得的最大产品的总收益在指定的限制条件下可以获得的最大值。值。假设条件：假设甲类食品的生产量为假设条件：假设甲类食品的生产量为X1，乙类食品的生产量为，乙类食品的生产量为X2，总收，总收益为益为Y。主方程：主方程：Y=50 X1+65 X2约束条件：约束条件：（1）生产量要求大于或等于零且为整数）生产量要求大于或等于零且为整数 X1，X20（2）实际原料用量应小于原料配额）实际原料用量应小于原料配额 1.6X1+1.8X2800规划求解划求解(线形形规划模型）划模型）Excel 2007 统计应用统计应用（3）实际实际生产时间应小于或等于每月生产时间生产时间应小于或等于每月生产时间 X1+1.5X2580 线性规划模型线性规划模型Excel 2007 统计应用统计应用（1）选中选中B10单元格，打开单元格，打开“规划求解规划求解”对话框，选中对话框，选中“规划求规划求解参数解参数”对话框。对话框。（2）选择）选择E3：E4单元格用为可变单元格。单元格用为可变单元格。（3）添加）添加“生产量生产量”约束条件。这里的生产量要求大于或等于零约束条件。这里的生产量要求大于或等于零且为整数。在且为整数。在“规划求解参数规划求解参数”对话框中单击对话框中单击“添加添加”按钮打开按钮打开“添加约束添加约束”对话框。对话框。（4）添加）添加“实际原料用量实际原料用量”约束条件。这里的实际原料用量应小约束条件。这里的实际原料用量应小于原料配额。单击于原料配额。单击“添加添加”按钮打开按钮打开“添加约束添加约束”对话框。对话框。Excel 2007 统计应用统计应用（5）添加添加“实际的生产时间实际的生产时间”约束条件。这里的实际的生产时间约束条件。这里的实际的生产时间应小于每月生产限时。单击应小于每月生产限时。单击“添加添加”按钮打开按钮打开“添加约束添加约束”对话对话框。框。（6）单击）单击“求解求解”按钮，选中按钮，选中“保存规划求解结果保存规划求解结果”。规划求解划求解