相册

相关系数与协方差一样是描述两个测量值变量之间的离散程度的指标。与协方差的不同之处在于，相关系数是成比例的，因此它的值与这两个测量值变量的表示单位无关。例如，如果两个测量值变量为重量和高度，当重量单位从磅换算成千克时，相关系数的值并不改变。任何相关系数的值都必须介于-1和+1之间（包括-1和+1）。

提示：可以使用相关系数分析工具来检验每对测量值变量，以便确定两个测量值变量是否趋向于同时变动，即，一个变量的较大值是否趋向于与另一个变量的较大值相关联（正相关）；或者一个变量的较小值是否趋向于与另一个变量的较大值相关联（负相关）；或者两个变量的值趋向于互不关联（相关系数近似于零）。

下面通过实例说明如何进行相关系数分析。

步骤1：将要处理的数据按图22-27所示输入到工作表中。

步骤2：单击“数据”选项卡，然后单击“分析”组中的“数据分析”命令，打开“数据分析”对话框。

步骤3：选中“分析工具”列表中的“相关系数”，如图22-28所示。

图22-27　输入将要处理的数据

步骤4：单击“确定”按钮，打开“相关系数”对话框。

步骤5：按如图22-29所示设置各选项。

图22-28　选中“分析工具”列表中的“相关系数”

图22-29　设置相关系数选项

步骤6：单击“确定”按钮，即可看到分析的结果，如图22-30所示。

图22-30　相关系数分析结果

提示：如果要指定输入区域中的数据是按行还是按列排列，则在“分组方式”右侧选择“逐行”或“逐列”单选按钮。

从相关系数分析结果可以看出，月销售额与销售成本之间的相关系数达到了0.990317，说明两者之间呈现良好的正相关性。

方差分析工具提供了以下3种不同类型的方差分析：单因素方差分析、包含重复的双因素方差分析和无重复的双因素方差分析。具体应该使用何种工具，需要根据因素的个数以及待检验样本总体中所含样本的个数而定。

单因素方差分析

单因素方差分析也叫作一维方差分析，此工具可对两个或更多样本的数据执行简单的方差分析。此分析可提供一种假设测试，该假设的内容是：每个样本都取自相同的基础概率分布，而不是对所有样本来说基础概率分布都不相同。如果只有两个样本，则可使用工作表函数TTEST。如果有两个以上的样本，则不能使用方便的TTEST归纳，可改为调用“单因素方差分析”模型。

下面通过实例说明如何进行单因素方差分析。

步骤1：将要处理的数据输入到工作表中，本例将5个地区一天当中发生交通事故的次数输入到工作表，如图22-15所示。下面将以α=0.01检验各地区平均每天交通事故的次数是否相等。

步骤2：单击“数据”选项卡，然后单击“分析”组中的“数据分析”命令，打开“数据分析”对话框。

步骤3：选中“分析工具”列表中的“方差分析：单因素方差分析”，如图22-16所示。

图22-15　输入要处理的数据

图22-16　选中“方差分析：单因素方差分析”

步骤4：单击“确定”按钮，此时将打开“方差分析：单因素方差分析”对话框。

步骤5：在“输入区域”框中输入源数据区域“$A$3：$E$6”，将α设置为0.01，在“输出区域”框中输入“$A$8”，如图22-17所示。对话框中各选项简要介绍如下。

图22-17　单因素方差分析选项设置

• 输入区域：输入待分析数据区域的单元格引用，该引用必须由两个或两个以上按列或行排列的相应数据区域组成。
• 分组方式：如果要指定输入区域中的数据是按行还是按列排列，则选择“行”或“列”单选按钮。
• 标志位于第一行/标志位于第一列：如果输入区域的第一行中包含标志项，则选中“标志位于第一行”复选框。如果输入区域的第一列中包含标志项，则选中“标志位于第一列”复选框。如果输入区域没有标志项，则清除该复选框，Excel将在输出表中生成合适的数据标志。
• α：输入要用来计算F统计的临界值的置信度。α置信度为与I型错误发生概率相关的显著性水平（拒绝真假设）。
• 输出区域：输入对输出表左上角单元格的引用，Excel只在输出表的半边填写结果，这是因为两个区域中数据的协方差与区域被处理的次序无关。在输出表的对角线上为每个区域的方差。
• 新工作表组：选择此项可以在当前工作簿中插入新工作表，并由新工作表的A1单元格开始粘贴计算结果。如果要为新工作表命名，则在右侧的框中输入名称。
• 新工作簿：选择此项可以创建一个新的工作簿，并在新工作簿的新工作表中粘贴计算结果。

步骤6：单击“确定”按钮，即可从G1开始的单元格看到单因素方差分析的结果，如图22-18所示。

图22-18　单因素方差分析的结果

提示：由于F＝0.124087591而F_α=5.952544683，因此F<F_α，这说明各地区每天的交通事故次数差异不显著。

包含重复的双因素方差分析

双因素方差分析用于观察两个因素的不同水平对所研究对象的影响是否存在明显的不同，根据是否考虑两个因素的交互作用，又可以分为“包含重复的双因素方差分析”和“无重复的双因素方差分析”。本节首先介绍“包含重复的双因素方差分析”。

例如，在测量植物生长高度的实验中，共施用了5种不同品牌的化肥（A、B、C、D、E），同时植物处于不同温度（20℃、25℃、30℃）的环境中。对于每种化肥与每种温度的组合各统计两次，测定结果如图22-19所示。

图22-19　统计数据

使用“包含重复的双因素方差分析”可以检验：

• 施用不同化肥的植物高度是否取自相同的基础样本总体，此分析忽略温度。
• 处于不同温度级别环境中的植物高度是否取自相同的基础样本总体，此分析忽略所使用的化肥品牌。

无论是否考虑上述不同品牌化肥之间的差异的影响以及不同温度之间差异的影响，代表所有{化肥，温度}值对的样本都取自相同的样本总体。另一种假设是除了基于化肥或温度单个因素的差异带来的影响之外，特定的{化肥，温度}值对也会有影响。

下面通过实例介绍进行包含重复的双因素方差分析的具体操作步骤。

步骤1：将要处理的数据按图22-19所示输入到工作表中。

步骤2：单击“数据”选项卡，然后单击“分析”组中的“数据分析”命令，打开“数据分析”对话框。

步骤3：选中“分析工具”列表中的“方差分析：可重复双因素分析”，如图22-20所示。

图22-20　选中“方差分析：可重复双因素分析”

图22-21　“方差分析：可重复双因素分析”对话框

步骤4：单击“确定”按钮，打开“方差分析：可重复双因素分析”对话框。

步骤5：在“输入区域”框中输入源数据区域，在“每一样本的行数”框中输入每一样本的重复次数（本例中为2），将α值设置为0.05，将设置“输出区域”，如图22-21所示。

提示：在“每一样本的行数”框中输入包含在每个样本中的行数。每个样本必须包含同样的行数，因为每一行代表数据的一个副本。

步骤6：单击“确定”按钮，即可看到分析的结果，如图22-22所示。

图22-22　可重复双因素方差分析结果

无重复的双因素方差分析

此分析工具可用于当数据像可重复双因素那样按照两个不同维度进行分类时的情况，只是此工具假设每一对值只有一个观察值，如在上面的示例中的每个{化肥，温度}值对。下面通过实例说明如何进行无重复的双因素方差分析。

步骤1：将要处理的数据按图22-23所示输入到工作表中。

图22-23　将要处理的数据输入到工作表中

图22-24　选中“方差分析：无重复双因素分析”

步骤2：单击“数据”选项卡，然后单击“分析”组中的“数据分析”命令，打开“数据分析”对话框。

步骤3：选中“分析工具”列表中的“方差分析：无重复双因素分析”，如图22-24所示。

步骤4：单击“确定”按钮，打开“方差分析：无重复双因素分析”对话框。

步骤5：在“输入区域”框中输入源数据区域，将α值设置为0.05，并设置“输出区域”，如图22-25所示。

图22-25　“方差分析：无重复双因素分析”对话框

步骤6：单击“确定”按钮，即可看到分析的结果，如图22-26所示。

图22-26　无重复双因素方差分析结果

要使用分析工具库，需要首先确保Excel中加载了分析工具库。如果在“数据”选项卡“分析”组中可以看到“数据分析”按钮，则已经加载了分析工具库，否则，必须先按以下步骤进行操作将其加载到Excel中。

步骤1：单击“文件”选项卡，然后单击“选项”按钮，打开“Excel选项”对话框。

步骤2：单击左边类别列表中的“加载项”，然后在“管理”右侧列表中选择“Excel加载项”，如图22-13所示。

步骤3：单击“转到”按钮，打开“加载项”对话框。

步骤4：选中“可用加载宏”列表中的“分析工具库”复选框，如图22-14所示。

图22-13　在“管理”右侧列表中选择“Excel加载项”

图22-14　选中“分析工具库”复选框

提示：如果“可用加载宏”列表中没有“分析工具库”，则单击“浏览”按钮进行查找。如果出现消息指出计算机上当前没有安装分析工具库，则单击“是”按钮进行安装。

在Excel中，可以使用“方案管理器”创建不同的假设分析方案，来预测使用不同组合的输入值计算出的不同结果。创建方案后可以在“方案管理器”中方便地查看不同方案所对应的数据表的数值变化，还可以生成方案总结报表以供预测分析。

定义方案

下面通过一个具体案例来说明如何定义一个方案。如图22-7所示，这是一个简单的图书销售利润统计表，其中顾客折扣（单元格B2）、运费（单元格B3）和数量（单元格B4）是输入单元格。

图22-7　图书销售利润统计表

在本例中，单价、进货折扣为固定值，单本书售价、单本书的利润、每种书的总利润、总利润需使用简单的公式计算得出，其公式分别如下：

• 单本书售价：从左至右依次为“=B7*B2”、“=C7*B2”、“=D7*B2”、“=E7*B2”。
• 单本书的利润：从左至右依次为“=B9-B7*（B2-B8-B3）”、“=C9-C7*（B2-C8-B3）”、“=D9-D7*（B2-D8-B3）”、“=E9-E7*（B2-E8-B3）”。
• 每种书的总利润：从左至右依次为“=B4*B10”、“=B4*C10”、“=B4*D10”、“=B4*E10”。
• 总利润：“=B11+C11+C11+D11”。

如果希望分析不同的顾客折扣、运费和数量下书籍销售的利润情况，则可以确定不同的方案，如可以分为“促销期”、“滞销期”、“常销期”三个方案，如表22-1所示。

表22-1　三个不同的方案

创建方案

下面通过实例介绍如何创建假设分析的方案。

步骤1：单击“数据”|“预测”|“模拟分析”|“方案管理器”命令，打开“方案管理器”对话框，如图22-8所示。因为是第一次打开该对话框，此时会出现“未定义方案，若要增加方案，请选定‘添加’按钮”的提示。

步骤2：单击“添加”按钮，打开“添加方案”对话框。

步骤3：在“方案名”框中输入方案的名字，例如此处输入“促销期”。

步骤4：在“可变单元格”框中输入可变单元格的地址，此处按住Ctrl键的同时单击B2、B3、B4单元格，这样可以在“可变单元格”框中输入“$B$2，$B$3，$B$4”，如图22-9所示。

图22-8　“方案管理器”对话框

图22-9　输入可变单元格

“添加方案”对话框各选项简介如下。

• 方案名：假设分析方案的名字，可以使用任意的名称，但最好能有助于识别方案的内容。
• 可变单元格：在此输入引用单元格的地址，允许输入多个单元格，而且输入单元格不必是相邻的。也可以按住Ctrl键的同时单击要输入的单元格，Excel会自动完成输入。
• 备注：默认会显示创建者的名字以及创建的日期，也可以根据实际情况输入其他内容或修改与删除内容。
• 保护：当工作簿被保护且“保护工作簿”中的“结构”选项被选中时，这两个选项即生效。保护方案可以防止其他人更改此方案。如果选择隐藏方案，则被隐藏的方案不会在“方案管理器”中出现。

步骤5：单击“确定”按钮，打开“方案变量值”对话框，输入每个可变单元格的值，如图22-10所示。

图22-10　输入方案变量值

图22-11　创建的3个方案出现在“方案”下方的列表中

步骤6：单击“添加”按钮，返回“添加方案”对话框，继续添加其他方案（滞销期、常销期），并为每个方案输入可变单元格的值。

步骤7：输入第三个方案的变量值后，单击“方案变量值”对话框中的“确定”按钮，返回“方案管理器”对话框，可以看到刚才创建的3个方案出现在“方案”下方的列表中，如图22-11所示。

显示方案

当假设分析方案创建完毕，即可在“方案管理器”中查看与管理方案，本节介绍如何在工作表中显示各方案所对应的可变单元格的信息。

步骤1：单击“数据”|“预测”|“模拟分析”|“方案管理器”命令，打开“方案管理器”对话框。

步骤2：选中“方案”列表下方的一个方案，然后单击“显示”按钮，即可在工作表中显示该方案的结果，如图22-12所示。

图22-12　从“方案”列表中选择要显示的方案

数据表指的是一个单元格区域，可用于显示一个或多个公式中某些值的更改对公式结果的影响。数据表实际是一组命令的组成部分，有时也称这些命令为“假设分析”。用户可以通过更改单元格中的值，以查看这些更改对工作表中公式结果有何影响。使用数据表可以快捷地通过一步操作计算出多种情况下的值，可以有效查看和比较由工作表中不同的变化所引起的各种结果。

数据表有哪些类型

数据表有两种类型：单变量数据表和双变量数据表。在具体使用时，需要根据待测试的变量数来决定创建单变量数据表还是双变量数据表。下面以计算购房贷款月还款额为例，介绍这两种类型的区别（实例将在后面详细介绍）。

• 单变量数据表：如果需要查看不同年限对购房贷款月还款额的影响，则可以使用单变量数据表。
• 双变量数据表：双变量数据表可用于显示不同利率和贷款年限对购房贷款月还款额的影响。

使用单变量数据表

下面通过实例详细说明如何使用单变量数据表进行假设分析。

步骤1：先计算年限为1年的每月还款额。在单元格B8中输入用于计算每月还款额的公式“=PMT（B3/12，A8*12，B2-B5）”，输入完毕按Enter键，如图22-1所示。

提示：有关PMT函数的具体使用方法参见函数部分的相关知识。

步骤2：选中“A8：B19”单元格，然后依次单击“数据”|“预测”|“模拟分析”|“模拟运算表”命令，打开“模拟运算表”对话框。

步骤3：在“输入引用列的单元格”框中选择列引用单元格为$A$8（如果数据表为行方向，则需要在“输入引用行的单元格”框中选择行引用单元格），如图22-2所示。

图22-1　输入公式

图22-2　“数据表”对话框

提示：单变量数据表的输入数值应当排列在一列中（列方向）或一行中（行方向），而且单变量数据表中使用的公式必须引用输入单元格。所谓的输入单元格指的是，该单元格中源于数据表的输入值将被替换。可以将工作表中的任何单元格作为输入单元格，而不必是数据表的一部分。

步骤4：单击“确定”按钮，查看模拟运算表的结果，如图22-3所示。

图22-3　查看数据表的结果

如果单击B9至B19中任一单元格，或者选中“B9：B19”单元格区域，可以在编辑栏中看到数据表的区域数组形式为“{=TABLE（，A8）}”，其中圆括号中的单元格地址即为所引用的单元格。由于是单变量数据表，所以其中只有一个单元格地址，而且又因为是列引用，所以是“（，A8）”的形式；如果是行引用，则为“（A8，）”。

提示：用户无法对区域数组中的数据进行单元编辑，因为区域数组是以整体的形式存在的，而不是以单独的形式存在。如果用户试图编辑其中的一个数值，则会出现警告对话框，提示不能更改数据表的一部分。

使用双变量数据表

下面通过实例详细说明如何使用双变量数据表进行假设分析。

步骤1：在单元格B8中输入用于计算每月还款额的公式“=PMT（B3/12，B4*12，B2-B5）”，输入完毕按Enter键，如图22-4所示。

图22-4　输入公式

提示：在双变量数据表中，输入公式必须位于两组输入值的行与列相交的单元格，否则无法进行双变量假设分析。本例中的单元格B8即为相交的单元格。

步骤2：选中“A7：G19”单元格，然后依次单击“数据”|“预测”|“模拟分析”|“模拟运算表”命令，打开“模拟运算表”对话框。

步骤3：在“输入引用行的单元格”框中选择行引用单元格为$B$3，在“输入引用列的单元格”框中选择列引用单元格为$B$4，如图22-5所示。

图22-5　输入引用行与引用列的单元格

步骤4：单击“确定”按钮，查看模拟运算表的结果，如图22-6所示。

如果单击B8至B19中任一单元格，或者选中“B8：B19”单元格区域，可以在编辑栏中看到数据表的区域数组形式为“{=TABLE（B3，B4）}”，其中圆括号中的单元格地址即为所引用的单元格。由于是双变量数据表，所以其中有两个单元格地址，一个为行引用（即B3），一个为列引用（即B4）。

图22-6　查看数据表的结果

清除数据表

如果要清除数据表，可以按照以下步骤进行操作。

步骤1：选择整个数据表（包括所有的公式、输入值、计算结果、格式和批注）。

步骤2：单击“开始”|“编辑”|“清除”|“全部清除”命令。