分类：数据分析篇

“随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。可以通过概率分布来表示一组数据的总体特征。例如，可以使用正态分布来表示人体身高的总体特征，或者使用双值输出的伯努利分布来表示掷币实验结果的总体特征。

下面通过实例说明如何进行随机数发生器分析。

步骤1：打开例子工作表，如图22-56所示。

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

步骤3：选中“分析工具”列表中的“随机数发生器”，如图22-57所示。

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

图22-57　选中“分析工具”列表中的“随机数发生器”

步骤4：单击“确定”按钮，打开“随机数发生器”对话框。

步骤5：根据需要设置以下选项，具体设置如图22-58所示。其中一些选项简要介绍如下。

图22-58　设置随机数发生器选项

• 随机数个数：在此输入要查看的数据点个数。每一个数据点出现在输出表的一行中。如果没有输入数字，Excel会在指定的输出区域中填充所有的行。
• 分布：在此选择用于创建随机数的分布方法。
• 均匀：以下限和上限来表征。其变量是通过对区域中的所有数值进行等概率抽取而得到的。普通的应用是在范围0到1之间的均匀分布。Excel 2016共提供了7种随机数的分布方法，分别为均匀、正态、伯努利、二项式、泊松、模式和离散。
• 正态：以平均值和标准偏差来表征。普通的应用是平均值为0，标准偏差为1的标准正态分布。
• 伯努利：以给定的试验中成功的概率（p值）来表征。伯努利随机变量的值为0或1。例如，可以在范围0到1之间抽取均匀分布随机变量。如果变量小于或等于成功的概率，则伯努利随机变量的值为1，否则，随机变量的值为0。
• 二项式：以一系列试验中成功的概率（p值）来表征。例如，可以按照“试验次数”框中指定的个数生成一系列伯努利随机变量，这些变量之和为一个二项式随机变量。
• 泊松：以值λ来表征，λ等于平均值的倒数。泊松分布经常用于表示单位时间内事件发生的次数，例如，汽车到达收费停车场的平均速率。
• 模式：以上界和下界、步长、数值重复率以及序列重复率来表征。
• 离散：以数值及相应的概率区域来表征。在本对话框中给定的输入区域必须包含两列，左边一列包含数值，右边一列为与数值对应的发生概率。所有概率的和必须为1。
• 参数：在此输入用于表征选定分布的数值。
• 随机数基数：在此输入用来构造随机数的可选数值。可以在以后重新使用该数值来生成相同的随机数。

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

图22-59　随机数发生器分析结果

“移动平均”分析工具可以基于特定的过去某段时期中变量的平均值，对未来值进行预测。移动平均值提供了由所有历史数据的简单的平均值所代表的趋势信息。使用此工具可以预测销售量、库存或其他趋势。

下面通过实例说明如何进行移动平均分析。

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

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

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

步骤3：选中“分析工具”列表中的“移动平均”，如图22-53所示。

步骤4：单击“确定”按钮，打开“移动平均”对话框。

步骤5：根据需要设置以下选项，具体设置如图22-54所示。其中一些选项简要介绍如下。

输入区域：在此输入待分析数据区域的单元格引用。该区域必须由包含4个或4个以上的数据单元格的单列组成。

间隔：在此输入需要在移动平均计算中包含的数值个数。默认间隔为3。

输出区域：在此输入对输出表左上角单元格的引用。如果选中了“标准误差”复选框，Excel将生成一个两列的输出表，其中右边的一列为标准误差值。如果没有足够的历史数据来设计预测或计算标准误差值，Excel会返回错误值“#N/A”。输出区域必须与输入区域中使用的数据位于同一张工作表中，因此，“新工作表组”和“新工作簿”选项均不可用。

图表输出：选中此复选框可以在输出表中生成一个嵌入直方图。

标准误差：如果要在输出表的一列中包含标准误差值，则选中此复选框。如果只需要单列输出表而不包含标准误差值，则清除此复选框。

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

图22-53　选中“分析工具”列表中的“移动平均”

图22-54　设置移动平均选项

图22-55　移动平均分析结果

“直方图”分析工具可计算数据单元格区域和数据接收区间的单个和累积频率。此工具可用于统计数据集中某个数值出现的次数。

例如，在一个有20名学生的班里，可按字母评分的分类来确定成绩的分布情况。直方图表可给出字母评分的边界，以及在最低边界和当前边界之间分数出现的次数。出现频率最多的分数即为数据集中的众数。

下面通过实例说明如何进行直方图分析。

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

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

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

步骤3：选中“分析工具”列表中的“直方图”，如图22-49所示。

步骤4：单击“确定”按钮，打开“直方图”对话框。

图22-49　选中“分析工具”列表中的“直方图”

图22-50　设置直方图选项

步骤5：根据需要设置以下选项，具体设置如图22-50所示。其中一些不同于其他分析工具的选项简要介绍如下。

接收区域（可选）：在此输入接收区域的单元格引用，该区域包含一组可选的用来定义接收区域的边界值。这些值应当按升序排列。Excel将统计在当前边界值和相邻边界值之间的数据点个数（如果存在）。如果数值等于或小于边界值，则该值将被归到以该边界值为上限的区域中进行计数。所有小于第一个边界值的数值将一同计数，同样所有大于最后一个边界值的数值也将一同计数。

柏拉图：选中此复选框可以在输出表中按降序来显示数据。如果此复选框被清除，Excel将只按升序来显示数据并省略最右边包含排序数据的三列数据。

累积百分率：选中此复选框可以在输出表中生成一列累积百分比值，并在直方图中包含一条累积百分比线。如果清除此选项，则会省略累积百分比。

图表输出：选中此复选框可以在输出表中生成一个嵌入直方图。

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

图22-51　直方图分析结果

“傅里叶分析”分析工具可以解决线性系统问题，并能通过快速傅里叶变换（FFT）进行数据变换来分析周期性的数据。此工具也支持逆变换，即通过对变换后的数据的逆变换返回初始数据。

下面通过实例说明如何进行傅里叶分析。

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

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

步骤3：选中“分析工具”列表中的“傅里叶分析”，如图22-44所示。

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

图22-44　选中“分析工具”列表中的“傅里叶分析”

步骤4：单击“确定”按钮，打开“傅里叶分析”对话框。

步骤5：根据需要设置以下选项，具体设置如图22-45所示。其中一些不同于其他分析工具的选项简要介绍如下。

输入区域：在此输入对需要进行变换的实数或复数单元格区域的引用。得数必须表示为“x+yi”或“x+yj”的格式。输入区域中数值的个数必须为2的偶数次幂。如果x为负数，则在前面加上一个撇号（′）。数值的最大个数为4096。

逆变换：如果选中此复选框，则输入区域中的数据将会被认为是经过变换后的数据，并对其进行逆变换，返回初始输入值；如果清除此复选框，则输入区域中的数据在输出表中将进行变换。

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

注意：输入区域中数值的个数必须为2的乘幂，比如2、4、6、8、16、32、64、128等，否则会出现错误提示框，如图22-47所示。

图22-45　设置傅里叶分析选项

图22-46　傅里叶分析结果

图22-47　输入区域数值个数错误时的提示框

“指数平滑”分析工具基于前期预测值导出相应的新预测值，并修正前期预测值的误差。此工具将使用平滑常数a，其大小决定了本次预测对前期预测误差的修正程度。

提示：介于0.2到0.3的值是合理的平滑常数。这些值表明应将当前预测调整20%到30%以修正前期预测误差。常数越大响应越快，但是预测变得不稳定。常数较小将导致预测值的滞后。

下面通过实例说明如何进行指数平滑分析。

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

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

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

步骤3：选中“分析工具”列表中的“指数平滑”，如图22-36所示。

步骤4：单击“确定”按钮，打开“指数平滑”对话框。

步骤5：根据需要设置以下选项，具体设置如图22-37所示。其中一些不同于其他分析工具的选项简要介绍如下。

图22-36　选中“分析工具”列表中的“指数平滑”

图22-37　设置指数平滑选项

阻尼系数：输入需要用作指数平滑常数的阻尼系数。阻尼系数是用来将总体中数据的不稳定性最小化的修正因子，默认阻尼系数为0.3。介于0.2到0.3的值都是合理的平滑常数。这些值表明应将当前预测调整20%到30%以修正以前的预测。常数越大反应越快，但是预测变得不稳定。常数较小将导致预测值的滞后。

图表输出：选中此项可以在输出表中生成实际值与预测值的嵌入图表。

标准误差：如果希望在输出表的一列中包含标准误差，则选中此复选框。如果只需要单列输出表而不包含标准误差，则清除此复选框。

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

图22-38　指数平滑分析结果

“描述统计”分析工具用于生成数据源区域中数据的单变量统计分析报表，提供有关数据趋中性和易变性的信息。

下面通过实例说明如何进行描述统计分析。

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

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

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

步骤3：选中“分析工具”列表中的“描述统计”，如图22-32所示。

步骤4：单击“确定”按钮，打开“描述统计”对话框，并按如图22-33所示设置各选项。其中一些不同于其他分析工具的选项简要介绍如下。

图22-32　选中“描述统计”

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

·汇总统计：选中此项可以为结果输出表中每个统计结果生成一个字段，包括平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏斜度、极差、最小值、最大值、总和、计数、最大值（#）、最小值（#）和置信度。

·平均数置信度：如果需要在输出表的某一行中包含平均数置信度，则选中“平均数置信度”复选框，并在右侧的框中，输入所要使用的置信度。例如，数值95%用来计算在显著性水平为5%时的平均值置信度。此处使用默认值95%。

·第K大值：如果需要在输出表的某一行中包含每个数据区域中的第K个最大值，则选中“第K最大值”复选框。在右侧的框中，输入K的数字。如果输入1，则该行将包含数据集中的最大值。此处使用默认值1。

·第K小值：如果需要在输出表的某一行中包含每个数据区域中的第K个最小值，则选中“第K最小值”复选框。在右侧的框中，输入K的数字。如果输入1，则该行将包含数据集中的最小值。此处使用默认值1。

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

图22-34　分析结果

相关系数与协方差一样是描述两个测量值变量之间的离散程度的指标。与协方差的不同之处在于，相关系数是成比例的，因此它的值与这两个测量值变量的表示单位无关。例如，如果两个测量值变量为重量和高度，当重量单位从磅换算成千克时，相关系数的值并不改变。任何相关系数的值都必须介于-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　无重复双因素方差分析结果