Excel 有许多功能,用户需要指定一个或多个条件才能获得结果。例如,如果要根据多个条件对单元格进行计数,可以使用Excel 中的COUNTIF 或COUNTIFS 函数。
本教程介绍了在Excel 的COUNTIF 和COUNTIFS 函数中使用单个或多个条件的各种方法。
虽然我将在本教程中主要关注COUNTIF 和COUNTIFS 函数,但所有这些示例也可用于其他将多个条件作为输入的Excel 函数(例如SUMIF、SUMIFS、AVERAGEIF 和AVERAGEIFS)。
内容
Excel COUNTIF 和COUNTIFS 函数简介
让我们首先了解如何在Excel 中使用COUNTIF 和COUNTIFS 函数。
Excel COUNTIF 函数(采用单一条件)
Excel COUNTIF 函数最适合您希望基于单个条件对单元格进行计数的情况。如果要基于多个条件进行计数,请使用COUNTIFS 函数。
句法
=COUNTIF(范围,条件)
输入参数
- 范围 – 您要计算的单元格范围。
- 条件 –必须针对要计数的单元格范围评估的标准。
Excel COUNTIFS 函数(采用多个条件)
Excel COUNTIFS 函数最适合您要根据多个条件对单元格进行计数的情况。
句法
=COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)
输入参数
- 条件范围1 – 您要根据标准1 评估的单元格范围。
- 条件1 – 您要评估的标准为criteria_range1 确定要计算的单元格。
- [criteria_range2] – 您要根据标准2 评估的单元格范围。
- [criteria2] – 您要评估的条件为criteria_range2 以确定要计数的单元格。
现在让我们看一些在Excel 的COUNTIF 函数中使用多个条件的示例。
在Excel COUNTIF 函数中使用NUMBER 条件
#1 当标准等于一个值时计数单元格
要获取条件参数等于指定值的单元格计数,您可以直接输入条件或使用包含条件的单元格引用。
下面是一个示例,我们计算包含数字9 的单元格(这意味着条件参数等于9)。这是公式:
=COUNTIF($B$2:$B$11,D3)
在上面的示例中(在图片中),标准位于单元格D3 中。您也可以将条件直接输入到公式中。例如,您还可以使用:
=COUNTIF($B$2:$B$11,9)
#2 当条件大于值时计数单元格
要获取值大于指定值的单元格计数,我们使用大于运算符(“>”)。我们可以直接在公式中使用它,也可以使用具有条件的单元格引用。
每当我们在Excel 的条件中使用运算符时,我们都需要将它放在双引号内。例如,如果条件大于10,那么我们需要输入“>10”作为条件(见下图):
这是公式:
=COUNTIF($B$2:$B$11,”>10″)
您还可以在单元格中包含条件并将单元格引用用作条件。在这种情况下,您不需要将条件放在双引号中:
=COUNTIF($B$2:$B$11,D3)
当您希望条件位于单元格中但不希望与操作员一起使用时,也可能存在这种情况。例如,您可能希望单元格D3 的数字为10 而不是>10。
在这种情况下,您需要创建一个条件参数,它是运算符和单元格引用的组合(见下图):
=COUNTIF($B$2:$B$11,”>”&D3)
注意:当您组合运算符和单元格引用时,运算符总是用双引号引起来。运算符和单元格引用由与号(&) 连接。
#3 当标准小于一个值时计数单元格
要获取值小于指定值的单元格计数,我们使用小于运算符(“<”)。我们可以直接在公式中使用它,也可以使用具有条件的单元格引用。
每当我们在Excel 的条件中使用运算符时,我们都需要将它放在双引号内。例如,如果标准是数量应该小于5,那么我们需要输入“<5”作为标准(见下图):
=COUNTIF($B$2:$B$11,”<5”)
您还可以在单元格中包含条件并将单元格引用用作条件。在这种情况下,您不需要将标准放在双引号中(见下图):
=COUNTIF($B$2:$B$11,D3)
此外,您可能希望条件位于单元格中,但不希望与操作员一起使用。例如,您可能希望单元格D3 的数字为5 而不是<5。
在这种情况下,您需要创建一个条件参数,它是运算符和单元格引用的组合:
=COUNTIF($B$2:$B$11,”<“&D3)
注意:当您组合运算符和单元格引用时,运算符总是用双引号引起来。运算符和单元格引用由与号(&) 连接。
#4 计算具有多个标准的单元格– 两个值之间
要获得两个值之间的值的计数,我们需要在COUNTIF 函数中使用多个条件。
以下是执行此操作的两种方法:
方法一:使用COUNTIFS 函数
COUNTIFS 函数可以将多个条件作为参数处理,并且仅当所有条件都为TRUE 时才对单元格进行计数。要计算两个指定值之间的值(例如5 和10)的单元格,我们可以使用以下COUNTIFS 函数:
=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)
注意:上述公式不计算包含5 或10 的单元格。如果要包括这些单元格,请使用大于等于(>=) 和小于等于(<=) 运算符。这是公式:
=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″)
您还可以在单元格中包含这些条件,并将单元格引用用作条件。在这种情况下,您不需要将标准放在双引号中(见下图):
您还可以使用单元格引用和运算符的组合(在公式中直接输入运算符)。将运算符和单元格引用组合在一起时,运算符总是用双引号引起来。运算符和单元格引用由与号(&) 连接。
方法2:使用两个COUNTIF 函数
如果您有多个条件,则可以使用COUNTIFS 或创建COUNTIF 函数的组合。下面的公式也会做同样的事情:
=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)
在上面的公式中,我们首先找到值大于5 的单元格数,然后减去值大于10 的单元格数。这将使我们得到结果为5(即具有值大于5 且小于等于10)。
如果您希望公式同时包含5 和10,请改用以下公式:
=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)
如果您希望公式从计数中排除“5”和“10”,请使用以下公式:
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)
您可以在单元格中包含这些条件并使用单元格引用,也可以使用运算符和单元格引用的组合。
在Excel 函数中使用TEXT 条件
#1 当条件等于指定文本时计数单元格
要计算包含与指定文本完全匹配的单元格,我们可以简单地使用该文本作为条件。例如,在数据集中(如下图所示),如果我想计算所有名称为Joe 的单元格,我可以使用以下公式:
=COUNTIF($B$2:$B$11,”Joe”)
由于这是一个文本字符串,我需要将文本条件放在双引号中。
您还可以在单元格中包含条件,然后使用该单元格引用(如下所示):
=COUNTIF($B$2:$B$11,E3)
注意:如果条件或条件范围中有前导/尾随空格,您可能会得到错误的结果。确保在使用这些公式之前清理数据。
#2 当条件不等于指定文本时计数单元格
与我们在上面的示例中看到的类似,您还可以计算不包含指定文本的单元格。为此,我们需要使用不等于运算符(<>)。
假设您要计算所有不包含名称JOE 的单元格,以下是可以执行此操作的公式:
=COUNTIF($B$2:$B$11,”<>Joe”)
您还可以在单元格中包含条件并将单元格引用用作条件。在这种情况下,您不需要将标准放在双引号中(见下图):
=COUNTIF($B$2:$B$11,E3)
当您希望条件位于单元格中但不希望与操作员一起使用时,也可能存在这种情况。例如,您可能希望单元格D3 具有名称Joe 而不是<>Joe。
在这种情况下,您需要创建一个条件参数,它是运算符和单元格引用的组合(见下图):
=COUNTIF($B$2:$B$11,”<>”&E3)
将运算符和单元格引用组合在一起时,运算符总是用双引号引起来。运算符和单元格引用由与号(&) 连接。
在Excel COUNTIF 和COUNTIFS 函数中使用DATE 条件
Excel 将日期和时间存储为数字。所以我们可以像使用数字一样使用它。
#1 当条件等于指定日期时计数单元格
要获取包含指定日期的单元格的计数,我们将使用等于运算符(=) 以及日期。
要使用日期,我建议使用DATE 函数,因为它消除了日期值出错的任何可能性。因此,例如,如果我想使用2015 年9 月1 日的日期,我可以使用如下所示的DATE 函数:
=日期(2015,9,1)
尽管存在地区差异,但此公式将返回相同的日期。例如,根据美国日期语法,01-09-2015 将是2015 年9 月1 日,根据英国日期语法,将是2015 年1 月9 日。但是,此公式将始终返回2105 年9 月1 日。
这是计算包含日期02-09-2015 的单元格数量的公式:
=COUNTIF($A$2:$A$11,DATE(2015,9,2))
#2 当条件在指定日期之前或之后时计数单元格
要计算包含指定日期之前或之后日期的单元格,我们可以使用小于/大于运算符。
例如,如果我想计算所有包含2015 年9 月2 日之后日期的单元格,我可以使用以下公式:
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))
同样,您还可以计算指定日期之前的单元格数。如果要在计数中包含日期,请使用“等于”运算符以及“大于/小于”运算符。
您还可以使用包含日期的单元格引用。在这种情况下,您需要使用与号(&) 将运算符(在双引号内)与日期结合起来。
请参见下面的示例:
=COUNTIF($A$2:$A$11,”>”&F3)
#3 计算具有多个条件的单元格– 两个日期之间
要获得两个值之间的值的计数,我们需要在COUNTIF 函数中使用多个条件。
我们可以使用两种方法来做到这一点——一个单独的COUNTIFS 函数或两个COUNTIF 函数。
方法一:使用COUNTIFS 函数
COUNTIFS 函数可以将多个条件作为参数,并且仅当所有条件都为TRUE 时才对单元格进行计数。要计算两个指定日期(比如9 月2 日和9 月7 日)之间值的单元格,我们可以使用以下COUNTIFS 函数:
=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))
上面的公式不计算包含指定日期的单元格。如果您还想包括这些日期,请使用大于等于(>=) 和小于等于(<=) 运算符。这是公式:
=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))
您还可以在单元格中包含日期并使用单元格引用作为条件。在这种情况下,您不能让操作员在单元格中包含日期。您需要在公式中手动添加运算符(用双引号括起来)并使用与号(&) 添加单元格引用。见下图:
=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)
方法2:使用COUNTIF 函数
如果您有多个条件,您可以使用一个COUNTIFS 函数或创建两个COUNTIF 函数的组合。下面的公式也可以解决问题:
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
在上面的公式中,我们首先找到日期在9 月2 日之后的单元格数,然后减去日期在9 月7 日之后的单元格数。这将得出结果为7(即具有日期的单元格数9 月2 日之后和9 月7 日当天或之前)。
如果您不希望公式同时计算9 月2 日和9 月7 日,请改用以下公式:
=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))
如果要从计数中排除两个日期,请使用以下公式:
=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))
此外,您可以在单元格中设置标准日期并使用单元格引用(以及使用& 符号连接的双引号中的运算符)。
在COUNTIF 和COUNTIFS 函数的条件中使用通配符
Excel中有三个通配符:
- *(星号) ——它代表任意数量的字符。例如,ex* 可能表示excel、excels、example、expert 等。
- ? (问号) ——它代表一个单一的字符。例如,Tr?mp 可能意味着Trump 或Tramp。
- ~(波浪号) ——用于识别文本中的通配符(~、*、?)。
当其他内置计数功能失败时,您可以使用带有通配符的COUNTIF 函数对单元格进行计数。例如,假设您有一个如下所示的数据集:
现在让我们举几个例子:
#1 计算包含文本的单元格
要计算其中包含文本的单元格,我们可以使用通配符*(星号)。由于星号表示任意数量的字符,因此它将计算所有包含任何文本的单元格。这是公式:
=COUNTIFS($C$2:$C$11,”*”)
注意:上面的公式会忽略包含数字、空白单元格和逻辑值的单元格,但会计算包含撇号(因此显示为空白)的单元格或包含可能已作为公式的一部分。
这是有关处理有空字符串或撇号的情况的详细教程。
#2 计数非空白单元格
如果您正在考虑使用COUNTA 函数,请三思。
试试看,它可能会让你失望。COUNTA 还将计算包含空字符串的单元格(通常由公式返回为=””,或者当人们在单元格中仅输入撇号时)。包含空字符串的单元格看起来是空白但不是,因此由COUNTA 函数计数。
COUNTA 还将计算包含空字符串的单元格(通常由公式返回为=””,或者当人们在单元格中仅输入撇号时)。包含空字符串的单元格看起来是空白但不是,因此由COUNTA 函数计数。
因此,如果您使用公式 = COUNTA (A1:A11),它会返回11,而应该返回10。
这是修复:
=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))
让我们通过分解来理解这个公式:
#3 计算包含特定文本的单元格
假设我们要计算销售代表名称以J 开头的所有单元格。这可以通过在COUNTIF 函数中使用通配符轻松实现。这是公式:
=COUNTIFS($C$2:$C$11,”J*”)
条件J* 指定单元格中的文本应以J 开头,并且可以包含任意数量的字符。
如果要计算文本中任何位置包含字母的单元格,请在其两侧加一个星号。例如,如果要计算其中包含字母“a”的单元格,请使用*a* 作为条件。
与我的其他文章相比,这篇文章异常长。希望你喜欢它。通过发表评论让我知道您的想法。