Excel公式和函数方差和差.docx

《Excel公式和函数方差和差.docx》由会员分享，可在线阅读，更多相关《Excel公式和函数方差和差.docx（8页珍藏版）》请在三一办公上搜索。

1、Excel公式和函数 方差和差Excel公式和函数 方差和标准差 方差是一组数据中，各变量值与其均值离差平方和的平均数；而标准差是方差的平方根，两者均反映了数据中变量值的平均变异程度。在Excel中，可以利用相应的统计函数，轻松、快捷的对这些值进行计算。 1COVAR函数 该函数用于返回协方差，即每对数据点的偏差乘积的平均数。利用协方差可以决定两个数据集之间的关系，例如，利用该函数检验教育程度与收入档次之间的关系。 语法：COVAR (array1, array2) 其中，参数Array1表示第一个所含数据为整数的单元格区域；参数Array2表示第二个所含数据为整数的单元格区域。 例如，假设未

2、来经济可能有四种状态，每种状态发生的概率都是相同的，理财产品X在四种状态下的收益率分别为14、20、35和29；而理财产品Y在四种状态下的收益率分别为9、16、40和28。求这两种理财产品的收益率协方差为多少？ 将已知的两种产品在各状态下的收益率输入到工作表中。然后，选择“协方差”所对应的单元格，即C8单元格，插入COVAR函数，并在对话框中，设置参数Array1为C3:C6；参数Array2为D3:D6，即可计算出这两种理财产品收益率的协方差为0.0094875，如图7-45所示。 设置 计算结果 图7-45 两种产品收益率的协方差 在计算过程中，如果参数Array1和Array2所含数据点

3、的个数不等，则COVAR函数 返回错误值#N/A；如果参数Array1和Array2当中有一个为空，则COVAR函数返回提 示 错误值#DIV/0!。 2DEVSQ函数 该函数用于返回数据点与各自样本平均值偏差的平方和。 语法：DEVSQ (number1, number2,.) 其中，参数Number1, number2, .为1到255个需要计算偏差平方和的参数，它们可以是用逗号分隔的数值，也可以是数组引用。 例如，某化学实验小组进行了5次实验，分别统计了3种化学反应的响应时间，求各化学反应响应时间的偏差平方和分别为多少？ 选择D7单元格，插入DEVSQ函数，在对话框中，设置参数Numbe

4、r1为B4:F4，即可计算化学反应1的偏差平方和为149，如图7-46所示。 然后，拖动该单元格右下角的填充柄，将公式填充至D9单元格，计算结果如图7-47所示。 设置 计算结果 计算结果 图7-46 化学反应1的偏差平方和 图7-47 化学反应2和3的偏差平方和 3VAR和VARA函数 VAR和VARA函数都用于计算基于给定样本的方差。所不同的是，VARA函数在计算时将包含引用中的逻辑值和代表数字的文本。 语法：VAR (number1, number2,.) VARA (value1, value2,.) 其中，参数Number, number2,.和参数Value1, value2,.均

5、为对应于总体样本的1到255个数值参数。 例 用VARP函数来计算方差。 如，某提 示 学校随机抽取了10名学生的英语成绩进行成绩检验，要求分别使用VAR和VARA函数，计算这些成绩的样本方差。 选择D13单元格，插入VAR函数，在对话框中，设置参数Number1为D3:D12，即可计算出参加考试的学生成绩的样本方差，如图7-48所示。 然后，选择D14单元格，直接在中，输入“=VARA(D3:D12)”公式，即可计算出包括缺考学生在内的样本方差，如图7-49所示。 VAR函数假设其参数是样本总体中的一个样本。如果数据为整个样本总体，则应使输入公式 设置 计算结果 计算结果 图7-48 样本方

6、差1 图7-49 样本方差2 4VARP和VARPA函数 VARP和VARPA函数均用于计算整个样本总体的方差。两者的区别在于：VARP函数在计算时将忽略包含逻辑值和文本的单元格；而使用VARPA函数计算时，逻辑值和文本也将参与计算。 语法：VARP (number1, number2,.) VARPA (value1, value2,.) 其中，参数Number, number2,.和参数Value1, value2,.均为对应于样本总体的1到255个参数。 例如，假设随机抽取10名学生的英语成绩之后，要使用总体方差了解学生的学习情况，应如何计算？ 选择D15单元格，插入VARP函数，在对话

7、框中，设置参数Number1为D3:D12，即可计算出参加考试的学生成绩的总体样本方差，如图7-50所示。 再选择D16单元格，在中，输入“=VARPA(D3:D12)”公式，即可计算出包括缺考学生在内的总体样本方差，如图7-51所示。 设置 输入公式 计算结果 计算结果 图7-50 总体方差1 图7-51 总体方差2 在使用VARP或者VARPA函数进行计算时，需要注意以下几点： l l 用VAR和VARA函数来计算方差。 VARP和VARPA函数假设其参数为样本总体，如果数据只是代表总体中的一个样本，则应该使如果参数为数组或者引用，那么在计算时，将只计算其中的数值，数组或者引用中的空白单元

8、格和文本将被忽略；使用VARP函数计算方差时，逻辑值也被忽略。 l 使用VARPA函数计算方差时，包含TRUE函数的参数作为1来计算，包含文本或者FALSE的参数作为0来计算。 5STDEV和STDEVA函数 STDEV和STDEVA函数均用于估算基于样本的标准偏差，标准偏差反映了数值相对于平均值的离散程度。 语法：STDEV (number1, number2,.) STDEVA (value1, value2,.) 其中，参数Number1,number2,.和Value1,value2,.表示对应于总体样本的1到255个参数。各参数既可以使用逗号分隔参数的形式，也可以使用单个数组或对数组

9、的引用。 例如，某厂要从一批钢丝绳中随机抽取若干进行试验，以便测试它们的抗拉强度。已知随机抽取的6条钢丝绳的强度分别为78、45、90、12、未检测和85，求这6条钢丝绳强度的标准偏差为多少？ 将已知条件输入到Excel工作表中后，选择“标准偏差”所对应的单元格，即C10单元格，插入STDEV函数，在对话框中，设置参数Number1为C3:C8，即可计算出不包含“未检测”钢丝绳在内的标准偏差，如图7-52所示。 然后，选择“标准偏差2”所对应的单元格，即C11单元格，在中输入“=STDEVA(C3:C8)”公式，计算包含“未检测”钢丝绳在内的标准偏差，如图7-53所示。 输入公式 设置 图7-

10、52 标准偏差1 图7-53 标准偏差2 计算结果 根据STDEV函数的计算结果即可判断钢丝绳的抗拉强度是否分散，如果计算的标准偏差比较小，说明抗拉强度的一致性好，质量比较稳定。反之说明钢丝绳的质量波动较大，抗拉强度不够一致。由本例的计算结果可以看出，这批钢丝绳抗拉强度的一致性较好。 在使用STDEV和STDEVA函数计算样本的标准偏差时，应该注意以下几点： l l l l l l STDEV和STDEVA函数假设其参数是总体中的样本，如果数据代表全部样本总体，则应该使用STDEVP和STDEVPA函数来计算标准偏差。 此处标准偏差的计算需要使用“n1”的方法。 两种函数的参数都可以是数值、包

11、含数值的名称、数组或引用；另外，STDEVA函数的参数还可以是数字的文本表示；或者引用中的逻辑值，TRUE和FALSE。 如果参数是一个数组或者引用，则只计算其中的数字，数组或引用中的空白单元格、文本值将被忽略；STDEV函数还将忽略逻辑值和错误值。 如果参数为错误值或为不能转换成数字的文本，将会导致错误。 使用STDEVA函数时，包含TRUE的参数作为1来计算；包含文本或FALSE的参数作为0来计算。 l 若要使引用中的逻辑值和代表数字的文本不参与计算，可以使用STDEV函数；若要使其参与计算，则应该使用STDEVA函数。 6STDEVP和STDEVPA函数 STDEVP和STDEVPA函数

12、均是用于返回以参数形式给出的整个样本总体的标准偏差。两者的区别在于，使用STDEVPA函数进行计算时，文本和逻辑值将参与计算。 语法：STDEVP (number1, number2,.) STDEVPA (value1, value2,.) 其中，参数Number1,number2,.和Value1,value2,.均表示作为样本总体的1到255个参数，它们既可以是逗号分隔参数的形式，也可以是单个数组或对数组的引用。 例如，仍然以上述测算钢丝绳抗拉强度为例，要求分别使用STDEVP和STDEVPA函数，来计算其总体样本的标准偏差。 选择C12单元格，插入STDEVP函数，在对话框中，设置参数

13、Number1为C3:C8，即可计算出不包含“未检测”钢丝绳在内的总体样本的标准偏差，如图7-54所示。 然后，选择C13单元格，在中输入“=STDEVPA(C3:C8)”公式，计算包含“未检测”钢丝绳在内的总体样本的标准偏差，如图7-55所示。 设置 输入公式 图7-54 总体样本的标准偏差1 图7-55 总体样本的标准偏差2 在利用STDEVP和STDEVPA函数计算样本总体的标准偏差时，需要注意以下几点： l l l STDEVP和STDEVPA函数均是假设其参数为整个样本总体，如果数据代表样本总体中的样本，则应该使用STDEV或者STDEVA来计算标准偏差。 对于大样本容量，STDEV

14、和STDEVP函数的计算结果大致相等；STDEVA和STDEVPA函数的计算结果大致相等。 此处标准偏差采用n的方法。 计算结果 l l l l l l STDEVP函数的参数可以是数字、包含数字的名称、数组或者引用，而STDEVPA函数除此之外，还可以是文本表示的数字或者引用中的逻辑值。 直接键入到参数列表中代表数字的文本将被计算在内。 如果参数是一个数组或引用，则只计算其中的数字，数组或者引用中的空白单元格和文本值将被忽略；另外，STDEVP还将忽略逻辑值和错误值。 在使用STDEVPA函数计算标准偏差时，包含TRUE的参数将作为1来计算；包含文本或者FALSE的参数将作为0来计算。 如果参数为错误值或者不能转换成数字的文本，将会出现错误。 若要使引用中的逻辑值和代表数字的文本参与计算，可以使用STDEVPA函数；若要使其不参与计算，可以使用STDEVP函数。