Excel公式和函数 方差和标准差

Excel 公式和函数 方差和标准差

方差是一组数据中，各变量值与其均值离差平方和的平均数；而标准差是方差的平方根，两者均反映了数据中变量值的平均变异程度。在Excel 中，可以利用相应的统计函数，轻松、快捷的对这些值进行计算。

1．COVAR 函数

该函数用于返回协方差，即每对数据点的偏差乘积的平均数。利用协方差可以决定两个数据集之间的关系，例如，利用该函数检验教育程度与收入档次之间的关系。 语法：COVAR (array1, array2)

其中，参数Array1表示第一个所含数据为整数的单元格区域；参数Array2表示第二个所含数据为整数的单元格区域。

例如，假设未来经济可能有四种状态，每种状态发生的概率都是相同的，理财产品X 在四种状态下的收益率分别为14％、20％、35％和29％；而理财产品Y 在四种状态下的收益率分别为9％、16％、40％和28％。求这两种理财产品的收益率协方差为多少？

将已知的两种产品在各状态下的收益率输入到工作表中。然后，选择“协方差”所对应的单元格，即C8单元格，插入COVAR 函数，并在【函数参数】对话框中，设置参数Array1为C3:C6；参数Array2为D3:D6，即可计算出这两种理财产品收益率的协方差为0.0094875，如图7-45所示。

图7-45 两种产品收益率的协方差

2．DE VSQ

函数

该函数用于返回数据点与各自样本平均值偏差的平方和。

语法：DEVSQ (number1, number2,...)

其中，参数Number1, number2, ...为1到255个需要计算偏差平方和的参数，它们可以是用逗号分隔的数值，也可以是数组引用。

例如，某化学实验小组进行了5次实验，分别统计了3种化学反应的响应时间，求各化学反应响应时间的偏差平方和分别为多少？

选择D7单元格，插入DEVSQ 函数，在【函数参数】对话框中，设置参数Number1为B4:F4，即可计算化学反应1的偏差平方和为149，如图7-46所示。

然后，拖动该单元格右下角的填充柄，将公式填充至D9单元格，计算结果如图7-47所示。 提 示 在计算过程中，如果参数Array1和Array2所含数据点的个数不等，则COVAR 函数

返回错误值#N/A ；如果参数Array1和Array2当中有一个为空，则COVAR 函数返回

错误值#DIV/0!。 设置

计算结果

图7-46 化学反应1的偏差平方和 图7-47 化学反应2和3的偏差平方和

3．VAR 和VARA 函数

VAR 和VARA 函数都用于计算基于给定样本的方差。所不同的是，VARA 函数在计算时将包含引用中的逻辑值和代表数字的文本。

语法：VAR (number1, number2,...)

VARA (value1, value2,...)

其中，参数Number, number2,...和参数Value1, value2,...均为对应于总体样本的1到255个数值参数。

例

如，某学校随机抽取了10名学生的英语成绩进行成绩检验，要求分别使用VAR 和VARA 函数，计算这些成绩的样本方差。

选择D13单元格，插入VAR 函数，在【函数参数】对话框中，设置参数Number1为D3:D12，即可计算出参加考试的学生成绩的样本方差，如图7-48所示。

然后，选择D14单元格，直接在【编辑栏】中，输入“=VARA(D3:D12)”公式，即可计算出包括缺考学生在内的样本方差，如图7-49所示。

图7-48 样本方差1 图7-49 样本方差2

4．VARP 和VARPA 函数

VARP 和VARPA 函数均用于计算整个样本总体的方差。两者的区别在于：VARP 函数在计算时将忽略包含逻辑值和文本的单元格；而使用VARPA 函数计算时，逻辑值和文本也将参与计算。

语法：VARP (number1, number2,...)

VARPA (value1, value2,...)

其中，参数Number, number2,...和参数Value1, value2,...均为对应于样本总体的1到255个参数。

例如，假设随机抽取10名学生的英语成绩之后，要使用总体方差了解学生的学习情况， 提 示 VAR 函数假设其参数是样本总体中的一个样本。如果数据为整个样本总体，则应使

用VARP 函数来计算方差。 设置 计算结果

计算结果 设置

计算结果 计算结果

输入公式

应如何计算？

选择D15单元格，插入VARP函数，在【函数参数】对话框中，设置参数Number1为D3:D12，即可计算出参加考试的学生成绩的总体样本方差，如图7-50所示。

再选择D16单元格，在【编辑栏】中，输入“=VARPA(D3:D12)”公式，即可计算出包括缺考学生在内的总体样本方差，如图7-51所示。

设置

输入公式

计算结果

计算结果

图7-50 总体方差1 图7-51 总体方差2

在使用VARP或者VARPA函数进行计算时，需要注意以下几点：

●V ARP和V ARPA函数假设其参数为样本总体，如果数据只是代表总体中的一个样本，则应该使

用V AR和V ARA函数来计算方差。

●如果参数为数组或者引用，那么在计算时，将只计算其中的数值，数组或者引用中的空白单元

格和文本将被忽略；使用V ARP函数计算方差时，逻辑值也被忽略。

●使用V ARPA函数计算方差时，包含TRUE函数的参数作为1来计算，包含文本或者FALSE的

参数作为0来计算。

5．STDEV和STDEVA函数

STDEV和STDEVA函数均用于估算基于样本的标准偏差，标准偏差反映了数值相对于平均值（mean）的离散程度。

语法：STDEV (number1, number2,...)

STDEVA (value1, value2,...)

其中，参数Number1,number2,...和Value1,value2,...表示对应于总体样本的1到255个参数。各参数既可以使用逗号分隔参数的形式，也可以使用单个数组或对数组的引用。

例如，某厂要从一批钢丝绳中随机抽取若干进行试验，以便测试它们的抗拉强度。已知随机抽取的6条钢丝绳的强度分别为78、45、90、12、未检测和85，求这6条钢丝绳强度的标准偏差为多少？

将已知条件输入到Excel工作表中后，选择“标准偏差”所对应的单元格，即C10单元格，插入STDEV函数，在【函数参数】对话框中，设置参数Number1为C3:C8，即可计算出不包含“未检测”钢丝绳在内的标准偏差，如图7-52所示。

然后，选择“标准偏差2”所对应的单元格，即C11单元格，在【编辑栏】中输入“=STDEVA(C3:C8)”公式，计算包含“未检测”钢丝绳在内的标准偏差，如图7-53所示。

输入公式

设置

计算结果

图7-52 标准偏差1 图7-53 标准偏差2