AVERAGE、RANK、COUNTIFS的使用方法

统计函数是指用于对数据区域进行统计分析的函数。常用的统计函数有AVERAGE、RANK、COUNTIFS等。

1.AVERAGE函数

AVERAGE（Number1，[Number2]，…）是平均值函数，其主要功能是求出所有参数的算术平均值。

参数说明：Number1：必需的参数，在该文本框中输入要计算平均值的第一个数字、单元格引用或单元格区域。

Number2：可选的参数，在该文本框中输入要计算平均值的其他数字、单元格引用或单元格区域，至少包含—个参数，最多可包含255个。

下面将介绍如何通过AVERAGE函数来计算个人的平均分，具体操作步骤如下：

步骤1：继续“成绩统计表-素材.xlsx”的操作，选择K3单元格，在“公式”选项卡“函数库”组中单击“其他函数”按钮，在弹出的下拉列表中选择“统计”|“AVERAGE”选项，如图6-54所示。

图6-54　选择“AVERAGE”选项

步骤2：在弹出的对话框中的第一个文本框中输入“D3：L3”公式，如图6-55所示。

图6-55　输入公式

步骤3：设置完成后，单击“确定”按钮，即可求出平均值，如图6-56所示。

图6-56　计算平均值

步骤4：使用前面所介绍的方法为其他单元格进行填充，效果如图6-57所示。

图6-57　为其他单元格填充后的效果

◆提示

参数可以是数字或者是包含数字的名称、单元格区域或单元格引用。

逻辑值和直接输入到参数列表中代表数字的文本被计算在内。如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。

2.RANK函数

RANK（Number，Ref，[Order]）是排位函数，其主要功能是返回一个数值在指定数值列表中的排位；如果多个值具有相同的排位，使用函数RANK.AVG将返回平均排位；使用函数RANK.EQ则返回实际排位。

参数说明：Number必需的参数，表示需要排位的数值。Ref必需的参数，表示要查找的数值列表所在的单元格区域。Order可选的参数，指定数值列表的排序方式。

下面将介绍如何为学生的成绩进行排名，具体操作步骤如下：

步骤1：继续“成绩统计表-素材.xlsx”的操作，在工作表中选择L3单元格，在“公式”选项卡“函数库”组中单击“其他函数”按钮，在弹出的下拉列表中选择“兼容性”|“RANK”选项，如图6-58所示。

图6-58　选择“RANK”选项

步骤2：在弹出的对话框中单击“Number”右侧的按钮，在工作表中选择J3单元格，如图6-59所示。

图6-59　选择J3单元格

步骤3：按Enter键确认，再在返回的对话框中单击“Ref”右侧的按钮，在工作表中选择J3：J71单元格区域，如图6-60所示。

图6-60　选择单元格区域

步骤4：按Enter键确认，在“函数参数”对话框中对公式进行修改，如图6-61所示。

图6-61　修改公式后的效果

◆提示

如果Order为0或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非0值，则按升浮排名，即数值越大，排名结果数值越大。

步骤5：设置完成后，单击“确定”按钮，执行该操作后，即可计算选中参数的排名，如图6-62所示。

图6-62　对选中的参数进行排名

步骤6：根据前面所介绍的方法填充其他单元格，填充后的效果如图6-63所示。

图6-63　填充其他单元格后的效果

◆提示

Rank赋予重复数相同的排位。但重复数的存在将影响后续数值的排位。例如，在按升序排序的整数列表中，如果数字10出现两次，且其排位为5，则11的排位为7（没有排位为6的数值）。

要达到某些目的，可能需要使用将关联考虑在内的排位定义。在上一示例中，可能需要将数字10的排位修改为5.5。这可以通过向RANK返回的值添加以下修正系数来实现。此修正系数适用于按降序排序（Order=0或省略）和按升序排序（Order=非零值）计算排位的情况。

3.COUNTIFS函数

COUNTIFS（Criteria_range1，Criteria1，[Criteria_range2，Criteria2]…）是多条件计数函数，其主要功能是统计指定单元格区域中符合多组条件的单元格的个数。

参数说明：Criteria_range1必需的参数，第1组条件中指定的区域。

Criteria1必需的参数，第1组条件中指定的条件，条件的形式可以为数字、表达式、单元格地址或文本。

Criteria_range2，Criteria2可选参数，第2组条件。附加的区域及其关联条件，最多允许127个区域／条件对。

下面将介绍如何统计班级人数，具体操作步骤如下：

步骤1：继续“成绩统计表—素材.xlsx”的操作，在工作簿中选择Sheet2工作表，在该工作表中选择B2单元格，在“公式”选项卡“函数库”组中单击“其他函数”按钮，在弹出的下拉列表中选择“统计”|“COUNTIFS”选项，如图6-64所示。

图6-64　选择“COUNTIFS”选项

步骤2：在弹出的对话框中单击“Criteria_rangel”右侧的按钮，在Sheet1工作表中选择C3：C71单元格区域，如图6-65所示。

图6-65　选择单元格区域

步骤3：按Enter键确认，在返回的对话框中单击“Criterial”右侧的按钮，在Sheet2工作表中选择A2单元格，如图6-66所示。

图6-66　选择A2单元格

步骤4：按Enter键确认，在返回的对话框中修改公式参数，如图6-67所示。

图6-67　修改公式参数

◆提示

每一个附加的区域都必须与参数Cnteria_range1具有相同的行数和列数。这些区域无须彼此相邻。

步骤5：修改完成后，单击“确定”按钮，即可完成班级人数的统计，效果如图6-68所示。

图6-68　统计班级人数后的效果

步骤6：根据前面所介绍的方法为其他单元格填充公式，效果如图6-69所示。

图6-69　填充公式后的效果

◆提示

每个区域的条件一次应用于一个单元格。如果所有的第一个单元格都满足其关联条件，则计数增加1。如果所有的第二个单元格都满足其关联条件，则计数再增加1，依此类推，直到计算完所有单元格。

如果条件参数是对空单元格的引用，COUNTIFS会将该单元格的值视为0。

还可以在条件中使用通配符，即问号（?）和星号（*）。问号匹配任意单个字符，星号匹配任意字符串。如果要查找实际的问号或星号，请在字符前输入波形符（～）。

步骤7：选择C2单元格，在“公式”选项卡“函数库”组中单击“其他函数”按钮，在弹出的下拉列表中选择“统计”|“COUNTIFS”选项，在弹出的对话框中单击“Criteria_range1”右侧的按钮，在Sheet1工作表中选择B3：B71单元格区域，如图6-70所示。

图6-70　选择单元格区域

步骤8：按Enter键确认，在返回的对话框中的“Criteria1”文本框中输入“男”，如图6-71所示。

图6-71　设置Criteria1参数

步骤9：设置完成后，在该对话框中单击“Criteria_range12”右侧的按钮，在Sheet1工作表中选择C3：C71单元格区域，如图6-72所示。

图6-72　选择C3：C71单元格区域

步骤10：按Enter键确认，在返回的对话框中单击“Criteria2”右侧的按钮，在Sheet2工作表中选择A2单元格，如图6-73所示。

图6-73　选择A2单元格

步骤11：按Enter键确认，在返回的对话框中对公式进行修改，效果如图6-74所示。

图6-74　对公式进行修改

步骤12：设置完成后，单击“确定”按钮，使用同样的方法对其他单元格进行计算，效果如图6-75所示。

图6-75　对其他单元格计算后的效果

4.AVERACEIFS函数(www.chuimin.cn)

AVERAGEIFS（Average_range，Criteria_range1，Criteria1，[Criteria_range2，Criteria2]…）是多条件平均值函数，其主要功能是对指定单元格区域中符合多组条件的单元格求平均值。

参数说明：Average_range必需的参数，要计算平均值的实际单元格区域。

Criteria_range1必需的参数，第1组条件中指定的区域；

Criteria1必需的参数，第1组条件中指定的条件；

Criteria_range2，Criteria2可选参数，第2组条件。附加的区域及其关联条件，最多允许127个区域／条件对。

下面将介绍如何统计班级平均分，具体操作步骤如下：

步骤l：继续“成绩统计表-素材.xlsx”的操作，在工作表中选择E2单元格，在“公式”选项卡“函数库”组中单击“其他函数”按钮，在弹出的下拉列表中选择“统计”|“AVERACEIFS”选项，如图6-76所示。

图6-76　选择“AVERACEIFS”选项

步骤2：在弹出的对话框中单击“Average_range”右侧的按钮，在Sheet1工作表中选择J3：J71单元格区域，如图6-77所示。

图6-77　选择单元格区域

步骤3：按Enter键确认，在返回的对话框中单击“Criteria_range1”右侧的按钮，在Sheet1单元格区域中选择C3：C71单元格区域，如图6-78所示。

图6-78　选择C3：C71单元格区域

步骤4：按Enter键确认，在返回的对话框中单击“Criteria1”右侧的按钮，在Sheet2工作表中的A2单元格，如图6-79所示。

图6-79　选择单元格

步骤5：按Enter键确认，在返回的对话框中对公式进行修改，如图6-80所示。

图6-80　对公式进行修改

步骤6：设置完成后，单击“确定”按钮，使用同样的方法为其他单元格添加公式，如图6-81所示。

图6-81　为其他单元格添加公式后的效果

◆提示

如果Average_range为空值或文本值，则AVERAGEIFS返回错误值#DIV/0!。

如果条件区域中的单元格为空，AVERACEIFS将其视为0值。

区域中包含TRUE的单元格计算为1；区域中包含FALSE的单元格计算为0（零）。

仅当Average_range中的每个单元格满足为其指定的所有相应条件时，才对这些单元格进行平均值计算。

与AVERACEIF函数中的区域和条件参数不同，AVERAGEIFS中每个Criteria_range的大小和形状必须与sum_range相同。

如果Average_range中的单元格无法转换为数字，则AVERAGEIFS返回错误值#DIV/0!。

如果没有满足所有条件的单元格，则AVERAGEIFS返回错误值#DIV/0!。

5.MAX和MIN函数

如果需要计算较大单元格区域的汇总，可以使用数组公式。数组就是一行、一列或行列组合中的一系列数据。数组公式对一个或多个数组中的数据执行计算，返回单个或多个结果。下面我们来讲一下如何使用数组公式：

步骤1：继续“成绩统计表-素材.xlsx”的操作，选择F2单元格，在该单元格中输入“=MAX（（Sheet1!$C $3：$C$71=$A2）*Sheet1!$J$3：$J$71）”，如图6-82所示。

图6-82　输入公式

◆知识链接

MAX函数可以返回一组值中的最大值。

MAX函数语法具有下列参数：

number1，number2，…：number1是必须要填写的，后续数字是可选的，最多可包含255个。在MAX函数中参数可以是数字或者是包含数字的名称、数组或引用。

逻辑值和直接输入到参数列表中代表数字的文本被计算在内。

如果参数是一个数组或引用，则只使用其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。

如果参数不包含任何数字，则MAX返回0（零）。

如果参数为错误值或为不能转换为数字的文本，将会导致错误。

步骤2：输入完成后，按Ctrl+Shift+Enter组合键完成输入，即可将输入的公式以数组的形式进行计算，效果如图6-83所示。

图6-83　数组计算

◆提示

数组公式通常称作CSE（Ctrl+Shift+Enter）公式，因为不是只按Enter键，而是要按Ctrl+Shift+Enter完成公式。

步骤3：使用同样的方法在F3、F4单元格中输入公式，如图6-84所示。

图6-84　为其他单元格填充公式后的效果

步骤4：在工作表中选择G4单元格，输入公式“=MIN（IF（（Sheet！$C$3：$C$71：$A2），Sheet1！$J$3：$J$71））”，如图6-85所示。

图6-85　在C4单元格中输入公式

◆提示

MIN函数可以返回一组值中的最小值。

MIN函数语法具有下列参数：

number1，number2，…：number1是必须要填写的，后续数字是可选的，最多可包含255个。

步骤5：输入完成后，按Ctrl+Shift+Enter组合键完成输入，即可将输入的公式以数组的形式进行计算，效果如图6-86所示。

图6-86　计算班级最低分

步骤6：使用同样的方法在G3、G4单元格中输入公式，如图6-87所示。

图6-87　输入其他公式后的效果

6.AVERAGEIF函数

AVERAGEIF（Range，Criteria，[Average_range]）是条件平均值函数，其主要功能是对指定单元格区域中符合一组条件的单元格求平均值。

参数说明：Range必需的参数，进行条件对比的单元格区域。

Criteria必需的参数，求平均值的条件，其形式可以为数字、表达式、单元格引用、文本或函数。

Average_range可选的参数，要求平均值的实际单元格区域。如果Average_range参数被省略，Excel会对在Range参数中指定的单元格求平均值。

下面将介绍如何利用AVERAGEIF函数计算各科的平均分，具体操作步骤如下：

步骤1：继续“成绩统计表-素材.xlsx”的操作，在工作表中选择H2单元格，在“公式”选项卡“函数库”组中单击“其他函数”按钮，在弹出的下拉列表中选择“统计”|“AVERAGEIF”选项，如图6-88所示。

图6-88　选择“AVERAGEIF”选项

步骤2：在弹出的对话框中单击“Range”右侧的按钮，在Sheet1工作表中选择C3：C71单元格区域，如图6-89所示。

图6-89　选择单元格区域

步骤3：按Enter键确认，再在返回的对话框中单击“Criteria”右侧的按钮，在Sheet2工作表中选择A2单元格，如图6-90所示。

图6-90　选择A2单元格

步骤4：按Enter键确认，在返回的对话“Average_range”右侧的按钮，在框中单击“Average_range Sheet1”工作表中选择D3：D71单元格区域，如图6-91所示。

图6-91　选择D3：D71单元格区域

步骤5：选择完成后，按Enter键确认，在返回的对话框中对公式进行修改，如图6-92所示。

图6-92　修改公式

步骤6：设置完成后，单击“确定”按钮，使用同样的方法为其他单元格添加公式，效果如图6-93所示。

图6-93　为其他单元格填充公式后的效果

◆提示

如果Average_range中的单元格为空单元格，AVERAGEIF将忽略它。

如果range为空值或文本值，AVERAGEIF将返回错误值#DIV0！。

如果条件中的单元格为空单元格，AVERACEIF就会将其视为0值。

如果区域中没有满足条件的单元格，AVERACEIF将返回错误值#DIV/0！。