Excel数据管理与图表分析 双因素方差分析

Excel 数据管理与图表分析 双因素方差分析
在实际问题的研究中，有时需要考虑两个因素对实验结果的影响。

例如饮料销售，除了关心饮料颜色之外，还需要了解销售地区的不同是否影响销售量。

若把饮料的颜色看作影响销售量的因素A ，饮料的销售地区则是影响因素B 。

对因素A 和因素B 同时进行分析，就属于双因素方差分析的内容。

双因素方差分析的类型主要有两种，下面具体介绍其应用。

1．无重复双因素分析
无重复双因素分析是指在假设两个因素之间是相互独立、不存在任何关系的情况下，对其进行分析。

与单因素方差分析类似，在分析前需将试验数据按一定的格式输入工作表中。

例如，对
A 、
B 、
C 和
D 地区上半年和下半年的销售额进行统计，其数据信息如图13-6所示。

图13-6 创建表格 图13-7 设置无重复双因素参数
单击【分析】组中的【数据分析】按钮，在弹出的【数据分析】对话框中，选择【方差分析：无重复双因素分析】选项。

然后，在【方差分析：无重复双因素分析】对话框中，设置相关的参数，如图13-7所示。

其中，在【方差分析：无重复双因素分析】对话框中，各选项功能如下： ●
输入区域 输入无重复双因素分析的数据区域。

● 标志 启用该复选框，则生成的分析数据结果工作表中包含数据标志。

若禁用该复选框，则选择的分析数据中只能是数值类型，不能为文本类型，且生成的分析数据结果工作表中不包含数据标志。

●
α 显著性水平，一般输入0.05，即95%的置信度。

● 输出选项 输出无重复双因素分析数据的结果。

提 示 【方差分析：无重复双因素分析】对话框中的参数与【方差分析：单因素方差分
析】对话框中的参数相同。

单击【方差分析：无重复双因素分析】对话框中的【确定】按钮，即可得到如图13-8所示的方差分析结果。

创建
表格 选择
分析
结果
图13-8 无重复双因素方差分析
在生成的Sheet4无重复双因素方差分析工作表中，分为上下两部分。

其中，上部分为4个地区及上、下半年的计数、求和、平均和方差。

地区的方差是组内年份2个阶段销售额与其平均数之差的平方和除以自由度求得的，年份的方差是组内各地区销售额与其平均数之差的平方和除以自由度求得的。

例如：
A地区：[(B3-69.5)2+(C3-69.5)2]/(2-1)=180.5，以下3个地区均用此方法计算。

上半年：[(B3-120.75)2+(B4-120.75)2+(B5-120.75)2+(B6-120.75)2]/(4-1)，下半年也运用此方法进行介绍。

下半部分为“方差分析”，它将影响销售额变动的因素分解为行（地区）、列（年份）和误差（随机误差）3项，下面分别介绍这些数值的计算方法。

●平方和
计算行和列的平方和，需要先计算其总平均数，然后以组平均数与总平均数相比求得其离差平方和，再乘以数据个数。

各地区年度总平均销售额：(D4+D5+D6+D7+D9+D10)/6=110.38
行平方和：[(D4-D12)2+(D5-D12)2+(D6-D12)2+(D7-D12)2]×2=15699
列平方和：[(D9-D12)2+(D10-D12)2)×4=861.13
随机误差平方和通常是由平方和总计减去行和列的平方和求得的。

其中，平方和总计是将“销售额”表格中的8个数据逐一与总平均数相比求得其离差，然后计算离差平方并算出合计，即误差==B19-B15-B16=5667.4。

●自由度
在统计学里，自由度（degree of freedom,df）是指当以样本的统计量来估计总体的参数时，样本中独立或能自由变化的数据的个数称为该统计量的自由度。

行自由度n-1=4-1=3
列自由度n-1=2-1=1
误差自由度为行与列自由度的乘积3×1=3。

总计的自由度n-1=8-1=7
●方差
样本中各数据与样本平均数的差的平方和的平均数叫做样本方差，下面介绍有关方差的计算。

行的方差=行平方和/行自由度=15699/3=5233.1
列的方差=列平方和/列自由度=861.13/1=861.13
误差的方差=误差平方和/误差自由度=5667.4/3=1889.1
●F统计量
F值是根据实验数据进行方差分析求出来的统计量。

F统计量等于方差除以误差方差。

行的F统计量=行方差/误差方差=5233.1/1889.1=2.7701
列的F统计量=列方差/误差方差=861.13/1889.1=0.4558
●F临界值
行按自由度N1=3，N2=3查显著性水平0.05的F分布表，得F临界值9.276628。

列按自由度N1=1，N2=3显显著性水平0.05的F分布表，得F临界值10.12796。

从上述的分析可以得出：列的F统计量远小于F临界值，说明年份是影响销售额的主要因素，行的F统计最也小于F临界值，说明地区是影响销售额的次要因素。

2．可重复双因素方差分析
可重复双因素方差分析假定两种因素的结合会产生出一种新的效应，这种效应又称交互作用。

每个因素必须重复取样至少两次以上，才能分析出来是否存在交互作用，即要进行“可重复”双因素方差分析。

例如，某商场对各商品区中不同的商品种类进行了统计，4个季度的销售量数据记录如图13-9所示。

由于该实例是对各商品区和商品种类两个变量因素进行分析；且在同一商品区和商品种类中，又包含4个季度的不同商品销售量，故可以使用可重复双因素方差分析的方法。

图13-9 创建表格
选择【数据】选项卡，单击【分析】组中的【数据分析】按钮，在弹出的【数据分析】对话框中，选择【可重复双因素分析】对话框。

然后，在弹出的【方差分析：可重复双因素分析】对话框中，设置【输入区域】为“$A$3:$E$15”；【每一样本的行数】为4，如图13-10所示。

设置
图13-10 设置可重复双因素分析参数
其中，在【方差分析：可重复双因素分析】对话框中，各选项功能如下：
●输入区域选择可重复双因素分析的数据区域。

●每一样本的行数输入创建可重复双因素分析数据的行数。

本例中，每个商品区中包含4个季
度的销售量数据，故输入数字4。

●α 著性水平，一般输入0.05，即95%的置信度。

●输出选项输出可重复双因素分析数据的结果。

单击【方差分析：可重复双因素分析】对话框中的【确定】按钮，即可得到如图13-11所示的数据分析结果。

图13-11 数据分析结果
提 示
可重复双因素方差分析与无重复双因素方差分析数据输入的区别在于对重复试验
数据的处理，可重复方差分析就是将重复试验的数据叠加起来。

可重复双因素方差分析的过程与前面讲述的无重复双因素方差分析的方法类似，这里不再介绍。

根据前面所讲述的知识，从图13-11中，可以看出样本（行）F 统计量（F ）小于F 临界值（F crit ），且相差的值不大，说明商品区对销售量的影响不显著。

列的F 统计量小于F 临界值，且相差的值较大，说明商品种类对销售量有显著影响。

交互作用的F 统计量也小于F 临界值，且相差的值也较大，说明必须将商品区和商品种类两者很好的结合起来，才能吸引顾客消费。

分析
结果。