方差 – Excel22

典型案例

假定某工厂仅生产了10种产品，取样为随机样本进行抗断强度检验。基础数据如图16-101所示。

步骤1：打开例子工作簿“VARP.xlsx”。

步骤2：在单元格A13中输入公式“=VARP（A2：A11）”，用于计算全部工具抗断强度的方差（假定仅生产了10件工具）。计算结果如图16-102所示。

图16-101　基础数据

图16-102　计算结果

使用指南

函数VARP假设其参数为样本总体。如果数据只是代表样本总体中的一个样本，则使用函数VAR计算方差。参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。

如果参数是一个数组或引用，则只计算其中的数字，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或为不能转换为数字的文本，将会导致错误；如果要使计算包含引用中的逻辑值和代表数字的文本，则需要使用VARPA函数。函数VARP的计算公式如下：

其中x为样本平均值AVERAGE（number1，number2，…），n为样本大小。

Excel 基于整个样本总体计算方差：VARP函数

如果需要计算基于整个样本总体的方差，可通过VARP函数实现。VARP函数的语法为：=VARP(number1,number2, …)。

假设所有5件工具在制造过程中都是由同一台机器制造出来的，并取样进行抗断强度检验。

01　根据需要在指定单元格区域中输入需要计算的数据。

02　在需要显示结果的单元格中输入公式：=VARP (A2:A9)，然后按下“Enter”键确认即可。

alt

Excel 计算基于给定样本估算方差：VAR函数

VAR函数用于计算基于给定样本的方差。VAR函数的语法如下：

VAR(number1,number2,...)

其中，参数number1、number2……为对应于总体样本的1～255个参数。下面通过实例详细讲解该函数的使用方法与技巧。

假定某工厂仅生产了10种产品，取样为随机样本进行抗断强度方差的检验。打开“VAR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-53所示。具体的检验步骤如下。

选中A13单元格，在编辑栏中输入公式“=VAR(A2:A11)”，用于计算工具抗断强度的方差，输入完成后按“Enter”键返回计算结果，如图18-54所示。

图18-53　原始数据

图18-54　计算方差结果

函数VAR假设其参数是样本总体中的一个样本。如果数据为整个样本总体，则应使用函数VARP来计算方差。参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本被计算在内。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或为不能转换为数字的文本，将会导致错误。如果要使计算包含引用中的逻辑值和代表数字的文本，则需要使用VARA函数。函数VAR的计算公式如下：

其中，x为样本平均值AVERAGE(number1,number2,…)，n为样本大小。

Excel 计算基于整个样本总体方差：VARPA函数

VARPA函数用于计算基于整个样本总体的方差。VARPA函数的语法如下：

VARPA(value1,value2,...)

其中，参数value1、value2……为对应于样本总体的1～255个参数。下面通过实例详细讲解该函数的使用方法与技巧。

假定某工厂仅生产了10种产品，取样为随机样本进行抗断强度方差的检验。打开“VARPA函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-51所示。具体的检验步骤如下。

选中A13单元格，在编辑栏中输入公式“=STDEV(A2:A11)”，用于计算全部工具抗断强度的方差（假定仅生产了10件工具），输入完成后按“Enter”键返回计算结果，如图18-52所示。

图18-51　原始数据

函数VARPA假设其参数为样本总体。如果数据代表的是总体的一个样本，则必须使用函数VARA来计算方差。参数可以是下列形式：数值；包含数值的名称、数组或引用；数字的文本表示；或者引用中的逻辑值，例如TRUE和FALSE。逻辑值和直接键入参数列表中代表数字的文本被计算在内。包含TRUE的参数作为1来计算；包含文本或FALSE的参数作为0（零）来计算。如果参数为数组或引用，则只使用其中的数值。数组或引用中的空白单元格和文本值将被忽略。如果参数为错误值或为不能转换为数字的文本，将会导致错误。如果要使计算不包括引用中的逻辑值和代表数字的文本，则需要使用VAR函数。函数VARPA的计算公式如下：

图18-52　计算总体标准偏差

其中，x是样本平均值AVERAGE(value1,value2,…)且n是样本大小。

Excel 计算总体方差：DVARP函数

DVARP函数用于返回将列表或数据库中满足指定条件的列中数值作为样本总体，计算出样本的总体方差。其语法如下：

DVARP(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

性别为女生的英语成绩的样本总体方差。
总分>=240分的男生成绩的样本总体方差。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-36所示。

图15-36　计算表格和条件区域

STEP02：选中E22单元格，在编辑栏中输入公式“=DVARP(A1:G16,E1,A25:A26)”，然后按“Enter”键返回，即可计算出女生的英语成绩的样本总体方差，结果如图15-37所示。

STEP03：选中E23单元格，在编辑栏中输入公式“=DVARP(A1:G16,F1,B25:C26)”，然后按“Enter”键返回，即可计算出总分>=240分的男生成绩的样本总体方差，结果如图15-38所示。

图15-37　计算性别为女生的英语成绩的样本总体方差

图15-38　计算总分>=240分的男生成绩的样本总体方差

Excel 计算样本方差：DVAR函数

DVAR函数用于返回将列表或数据库中满足指定条件的列中数值作为一个样本，估算样本的总体方差。其语法如下：

DVAR(database,fi eld,criteria)

下面通过实例详细讲解该函数的使用方法与技巧。根据如图15-1所示的基础数据清单，班主任想要了解：

性别为女生的英语成绩的样本方差。
总分>=240分的男生成绩的样本方差。

具体操作步骤如下。

STEP01：根据上面提出的查询条件设置计算表格和条件区域，结果如图15-33所示。

图15-33　计算表格和条件区域

STEP02：选中E22单元格，在编辑栏中输入公式“=DVAR(A1:G16,E1,A25:A26)”，然后按“Enter”键返回，即可计算出性别为女生的英语成绩的样本方差，结果如图15-34所示。

STEP03：选中E23单元格，在编辑栏中输入公式“=DVAR(A1:G16,F1,B25:C26)”，然后按“Enter”键返回，即可计算出总分>=240分的男生成绩的样本方差，结果如图15-35所示。

图15-34　计算性别为女生的英语成绩的样本方差

图15-35　计算总分>=240分的男生成绩的样本方差

Excel 2019方差分析图解

方差分析通常分为两种情况，一种是单因素方差分析，另一种是双因素方差分析。而双因素方差分析又可分为“无重复双因素方差分析”和“可重复双因素方差分析”两种。下面以“方差分析.xlsx”工作簿为例，逐一对这几种分析工具进行介绍。

单因素方差分析

STEP01：打开“方差分析.xlsx”工作簿，切换至“单因素方差分析”工作表。在主页将功能区切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-85所示的“数据分析”对话框。在“分析工具”列表框中选择“方差分析：单因素方差分析”选项，然后单击“确定”按钮。

图7-85　选择单因素方差分析工具

STEP02：随后会打开“方差分析：单因素方差分析”对话框，在“输入”列表区域设置输入区域为“$B$2:$D$7”，在“分组方式”列表中单击选中“列”单选按钮，并勾选“标志位于第一行”复选框，设置α的值为“0.05”。然后在“输出选项”列表中单击选中“输出区域”单选按钮，设置输出区域为“$E$2”单元格，最后单击“确定”按钮，如图7-86所示。

图7-86　“方差分析：单因素方差分析”对话框

对话框中各个设置项的作用简要介绍如下。

1）输入区域：在此输入待分析数据区域的单元格引用。该引用必须由两个或两个以上按列或行组织的相邻数据区域组成。例如这里选择单元格区域为“$B$2:$D$7”。

2）分组方式：指出输入区域中的数据是按行还是按列排列。例如这里选择分组方式为“列”。

3）标志位于第一行/列：如果输入区域的第1行中包含标志项，则可以选中“标志位于第一行”复选框；如果输入区域的第1列中包含标志项，则可以选中“标志位于第一列”复选框；如果输入区域没有标志项，则该复选框不会被选中，Excel将在输出表中生成数据标志。

4）α：根据需要指定显著性水平，例如这里输入“0.05”。

5）输出区域：如果用户选择“输出区域”单选按钮，则在右侧的文本框中输入或选择一个单元格地址。如果用户希望输出至新的工作表组中，则选择“新工作表组”单选按钮；如果用户希望输入至新的工作簿中，则选中“新工作簿”单选按钮。例如这里选择“输出区域”，在右侧的文本框中输入“$E$2”。

STEP03：此时，工作表中会显示“方差分析：单因素方差分析”的分析结果，如图7-87所示。

无重复双因素分析

切换至“双因素方差分析–无重复性”工作表，如图7-88所示的数据表中说明的是某一种产品在5种不同的地点区域的销售情况。不在同区域、不同产品的情况下对销售的影响，即考察的区域不同、销售的产品不同对销售量是否有其显著影响。这两种因素组合只会产生一个销售数据，所以这是一个无重复双因素方差分析。因此，应该使用“无重复双因素分析”工具进行分析，其具体操作步骤如下。

图7-87　单因素方差分析结果

图7-88　“无重复性”数据表

STEP01：切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-89所示的“数据分析”对话框。在“分析工具”列表框中选择“方差分析：无重复双因素分析”选项，然后单击“确定”按钮。

STEP02：随后会打开“方差分析：无重复双因素分析”对话框，在“输入”列表区域设置输入区域为“$B$2:$F$5”，设置α的值为“0.05”，然后在“输出选项”列表中单击选中“输出区域”单选按钮，设置输出区域为“$A$6”单元格，最后单击“确定”按钮，如图7-90所示。

图7-89　选择无重复双因素方差分析工具

图7-90　“无重复双因素分析”对话框

STEP03：此时，工作表中会显示“方差分析：无重复双因素分析”的分析结果，如图7-91所示。

可重复双因素分析

切换至“双因素方差分析–可重复性”工作表，如图7-92所示的工作表是一份农作物增收数据，其中化肥和复合肥是两个因素。目的是要考察化肥和复合肥的不同组合方案对农作物的收成是否有显著差异。从表中可以看到，每两个因素的组合都有4个数据，所以这是一个可重复性双因素方差分析，因此应该使用“可重复双因素分析”工具进行分析。

STEP01：切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-93所示的“数据分析”对话框。在“分析工具”列表框中选择“方差分析：可重复双因素分析”选项，然后单击“确定”按钮。

STEP02：随后会打开“方差分析：可重复双因素分析”对话框，在“输入”列表区域设置输入区域为“$A$1:$C$9”，在“每一样本的行数”文本框中输入“4”，并设置α的值为“0.05”，然后在“输出选项”列表中单击选中“输出区域”单选按钮，设置输出区域为“$A$11”单元格，最后单击“确定”按钮，如图7-94所示。

图7-91　无重复双因素方差分析结果

图7-92　农作物增收数据

图7-93　选择可重复双因素分析选项

图7-94　“可重复双因素分析”对话框

对话框中“每一样本的行数”项指的是每一样本的行数，因为在本实例中每一种方案都有4行数据，所以这里输入4。

STEP03：此时，工作表中会显示“方差分析：可重复双因素分析”的分析结果，如图7-95所示。

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

标签：方差

Excel 应用VARP函数计算基于样本总体的方差