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,...)

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

打开“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。对于任何包含在本区域中的总体平均值μ0,从μ0到x,获取样本平均值的概率大于alpha;对于任何未包含在本区域中的总体平均值μ0,从μ0到x,获取样本平均值的概率小于alpha。换句话说,假设使用x、standard_dev和size构建一个双尾检验,假设的显著性水平为alpha,总体平均值为μ0。如果μ0包含在置信区间中,则不能拒绝该假设;如果μ0未包含在置信区间中,则将拒绝该假设。置信区间不允许进行概率为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函数。