分类：统计学函数应用

Excel中的统计函数主要用于对数据区域进行各种分类统计与分析。统计函数包括了许多统计学领域的函数，具体包括平均值函数、Beta分布函数、概率函数、单元格数量计算函数、指数与对数函数、最大值与最小值函数、标准偏差函数、方差函数、正态累积分布函数、数据集相关函数、Pearson乘积矩函数、t分布函数等。本章将通过实例介绍相关统计函数的基本语法、参数用法及在实际中的应用。

Excel 计算调和平均值：HARMEAN函数

HARMEAN函数返回数据集合的调和平均值。调和平均值与倒数的算术平均值互为倒数。HARMEAN函数的语法如下：

HARMEAN(number1,number2,...)

其中，参数number1、number2……是用于计算平均值的1～255个参数，也可以不用这种用逗号分隔参数的形式，而用单个数组或对数组的引用的形式。下面通过实例详细讲解该函数的使用方法与技巧。

打开“HARMEAN函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-18所示。该工作表中记录了一组数据集，要求计算该组数据的调和平均值。具体操作步骤如下。

选中A10单元格，在编辑栏中输入公式“=HARMEAN(A2:A8)”，用于计算数据集的调和平均值，输入完成后按“Enter”键返回计算结果，如图18-19所示。

图18-18　目标数据

图18-19　计算调和平均值

调和平均值总小于几何平均值，而几何平均值总小于算术平均值。参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本被计算在内。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。如果任何数据点小于等于0，函数HARMEAN返回错误值“#NUM!”。调和平均值的计算公式如下：

Excel 计算几何平均值：GEOMEAN函数

GEOMEAN函数用于计算正数数组或区域的几何平均值。例如，可以使用函数GEOMEAN计算可变复利的平均增长率。GEOMEAN函数的语法如下：

GEOMEAN(number1,number2,...)

打开“GEOMEAN函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-16所示。该工作表中记录了一组数据集，要求根据工作表中的数据计算一个数据集的几何平均值。具体操作步骤如下。

选中A10单元格，在编辑栏中输入公式“=GEOMEAN(A2:A8)”，用于计算数据集的几何平均值，输入完成后按“Enter”键返回计算结果，如图18-17所示。

参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本被计算在内。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。如果任何数据点小于0，函数GEOMEAN返回错误值“#NUM!”。几何平均值的计算公式如下：

图18-16　原始数据

图18-17　计算几何平均值

Excel 计算置信区间：CONFIDENCE函数详解

CONFIDENCE函数返回一个值，可以使用该值构建总体平均值的置信区间。CONFIDENCE函数的语法如下：

CONFIDENCE(alpha,standard_dev,size)

其中，alpha参数是用于计算置信度的显著水平参数。置信度等于100*(1-alpha)%，也就是说，如果alpha参数为0.05，则置信度为95%。standard_dev参数为数据区域的总体标准偏差，假设为已知。size参数为样本容量。

置信区间是一个值区域。样本平均值x位于该区域的中间，区域范围为x±CONFIDENCE。例如，如果通过邮购的方式订购产品，其交付时间的样本平均值为x，则总体平均值的区域范围为x±CONFIDENCE。对于任何包含在本区域中的总体平均值μ₀，从μ₀到x，获取样本平均值的概率大于alpha；对于任何未包含在本区域中的总体平均值μ₀，从μ₀到x，获取样本平均值的概率小于alpha。换句话说，假设使用x、standard_dev和size构建一个双尾检验，假设的显著性水平为alpha，总体平均值为μ₀。如果μ₀包含在置信区间中，则不能拒绝该假设；如果μ₀未包含在置信区间中，则将拒绝该假设。置信区间不允许进行概率为1–alpha的推断，此时下一份邮购包裹的交付时间将肯定位于置信区间内。下面通过实例详细讲解该函数的使用方法与技巧。

打开“CONFIDENCE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-14所示。假设样本取自100名某生产车间的工人，他们平均每小时加工的零件数量为30个，总体标准偏差为3个，假设alpha=0.05。具体操作步骤如下。

选中A6单元格，在编辑栏中输入公式“=CONFIDENCE(0.05,3,100)”，用于计算总体平均值的置信区间，输入完成后按“Enter”键返回计算结果，如图18-15所示。

图18-14　原始数据

图18-15　计算置信区间

如果任意参数为非数值型，函数CONFIDENCE返回错误值“#VALUE!”。如果参数alpha≤0或alpha≥1，函数CONFIDENCE返回错误值“#NUM!”。如参数果standard_dev≤0，函数CONFIDENCE返回错误值“#NUM!”。如果size参数不是整数，将被截尾取整。如果参数size<1，函数CONFIDENCE返回错误值“#NUM!”。假设alpha参数等于0.05，则需要计算等于(1-alpha)或95%的标准正态分布曲线之下的面积。其面积值为±1.96。因此置信区间为：

Excel 计算协方差：COVAR函数实例图解

COVAR函数用来计算协方差，即每对数据点的偏差乘积的平均数，利用协方差可以决定两个数据集之间的关系。例如，可利用它来检验受教育程度与收入档次之间的关系。COVAR函数的语法如下：

COVAR(array1,array2)

其中，array1参数为第1个所含数据为整数的单元格区域，Array2参数为第2个所含数据为整数的单元格区域。

协方差计算公式为：

其中x和y是样本平均值AVERAGE(array1)和AVERAGE(array2)，且n是样本大小。下面通过实例详细讲解该函数的使用方法与技巧。

某工厂统计了不同加工条件（数据1）下设备的成品数量（数据2）,需要计算两组数据的协方差。打开“COVAR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-12所示。具体的操作步骤如下所示。

选中A8单元格，在编辑栏中输入公式“=COVAR(A2:A6,B2:B6)”，用于计算协方差，即上述每对数据点的偏差乘积的平均数，输入完成后按“Enter”键返回计算结果，如图18-13所示。

图18-12　原始数据

图18-13　计算协方差

参数必须是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。如果array1参数和array2参数所含数据点的个数不等，则函数COVAR返回错误值“#N/A”。如果array1参数和array2参数当中有一个为空，则函数COVAR返回错误值“#DIV/0!”。

Excel 计算满足条件平均值：AVERAGEIF函数

AVERAGEIF函数用于返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。其语法如下：

AVERAGEIF(range,criteria,average_range)

其中，range参数是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。criteria参数是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。average_range参数是要计算平均值的实际单元格集，如果忽略，则使用range。下面通过实例详细讲解该函数的使用方法与技巧。

某公司统计了不同区域的年度销售利润，需要计算几组给定条件（指定区域）的平均值。打开“AVERAGEIF函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-6所示。具体的操作步骤如下。

STEP01：选中A9单元格，在编辑栏中输入公式“=AVERAGEIF(A2:A7,”=*西部”,B2:B7)”，用于计算西部和中西部地区的所有利润的平均值，输入完成后按“Enter”键返回计算结果，如图18-7所示。

图18-6　原始数据

图18-7　计算西部和中西部地区平均值

STEP02：选中A10单元格，在编辑栏中输入公式“=AVERAGEIF(A2:A7,”<>*西部”,B2:B7)”，用于计算西部以外所有地区的所有利润的平均值，输入完成后按“Enter”键返回计算结果，如图18-8所示。

AVERAGEIFS函数用于返回满足多重条件的所有单元格的平均值（算术平均值）。其语法如下：

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

其中，average_range参数是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。参数criteria_range1、criteria_range2……是计算关联条件的1～127个区域。参数criteria1、criteria2……是数字、表达式、单元格引用或文本形式的1～127个条件，用于定义要对哪些单元格求平均值。下面通过实例详细讲解该函数的使用方法与技巧。

某房产公司统计了两个不同地区不同户型房屋的售价，需要计算满足多重条件的房屋售价的平均值。打开“AVERAGEIFS函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-9所示。具体的操作步骤如下。

STEP01：选中A9单元格，在编辑栏中输入公式“=AVERAGEIFS(B2:B7,C2:C7,”烟台”,D2:D7,”>2″,E2:E7,”是”)”，用于计算烟台市一个至少有3间卧室和一个车库的住房的平均价格，输入完成后按“Enter”键返回计算结果，如图18-10所示。

STEP02：选中A10单元格，在编辑栏中输入公式“=AVERAGEIFS(B2:B7,C2:C7,”威海”,D2:D7,”<=3″,E2:E7,”否”)”，用于计算在威海一个最多有3间卧室但没有车库的住宅的平均价格，输入完成后按“Enter”键返回计算结果，如图18-11所示。

图18-8　计算西部以外所有地区平均值

图18-9　原始数据

图18-10　计算烟台市住房平均价格

图18-11　计算威海住宅的平均价格

Excel 计算绝对偏差平均值：AVEDEV函数

AVEDEV函数用于返回一组数据与其均值的绝对偏差的平均值，AVEDEV用于评测这组数据的离散度。其语法如下：

AVEDEV(number1,number2,...)

其中，参数number1、number2……用于计算绝对偏差平均值的一组参数，参数的个数可以有1～255个，可以用单一数组（即对数组区域的引用）代替用逗号分隔的参数。下面通过实例详细讲解该函数的使用方法与技巧。

某公司的市场拓展部统计了最近7个月以来每个月所开拓新区域的数量，为了能够对未来的区域拓展数量进行预测，需要了解区域数量的离散度，因此需要计算一组区域数量与其均值的绝对偏差平均值。打开“AVEDEV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-4所示。具体的操作步骤如下。

选中A10单元格，在编辑栏中输入公式“=AVEDEV(A2:A8)”，用于计算上面提供的一组数据与其均值的绝对偏差的平均值，输入完成后按“Enter”键返回计算结果，如图18-5所示。

图18-4　原始数据

图18-5　绝对偏差平均值

输入数据所使用的计量单位会影响函数AVEDEV的计算结果。参数必须是数字或者包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本会被计算在内。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。

Excel 计算参数平均值：AVERAGE函数实例图解

AVERAGE函数用于返回参数的平均值（算术平均值）。其语法如下：

AVERAGE(number1,number2,...)

其中，参数number1、number2……是要计算其平均值的1～255个数字参数。下面通过实例详细讲解该函数的使用方法与技巧。

某机械厂车间统计了该车间装配5台大型设备各自所需要的时间，需要按不同的类型统计装配设备的平均时间，因此需要计算5组给定参数的平均值。此外，另有一台装备未列入表中，需要将所提供的数据与该装备所需时间单独计算平均时间。打开“AVERAGE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图18-1所示。具体的操作步骤如下。

图18-1　原始数据

STEP01：选中A8单元格，在编辑栏中输入公式“=AVERAGE(A2:A6)”，用于计算上面数字的平均值，输入完成后按“Enter”键返回计算结果，如图18-2所示。

STEP02：选中A9单元格，在编辑栏中输入公式“=AVERAGE(A2:A6,5)”，用于计算上面数字与5的平均值，输入完成后按“Enter”键返回计算结果，如图18-3所示。

图18-2　计算数据平均值

图18-3　计算上面数字与5　的平均值

参数可以是数字或者包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本被计算在内。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。如果要使计算包括引用中的逻辑值和代表数字的文本，则须使用AVERAGEA函数。