Microsoft Excel 提供了多种用于计算不同类型单元格的函数,例如空白或非空白,带有数字、日期或文本值,包含特定单词或字符等。
在本文中,我们将重点介绍Excel COUNTIF函数,该函数用于根据您指定的条件对单元格进行计数。首先,我们将简要介绍语法和一般用法,然后我提供一些示例,并警告在将此函数与多个条件和特定类型的单元格一起使用时可能出现的怪癖。
本质上,COUNTIF 公式在所有 Excel 版本中都是相同的,因此您可以在 Excel 2016、2013、2010 和 2007 中使用本教程中的示例。
- Excel COUNTIF 函数 - 语法和用法
- 如何在 Excel 中使用 COUNTIF 的示例
- 用于文本和数字的 COUNTIF 公式(完全匹配)
- 带有通配符的 COUNTIF(部分匹配)
- 计数是否为空或不为空
- 如果大于、小于或等于,则计数
- COUNTIF 日期公式
- 具有多个条件的 Excel COUNTIF
- 计算重复值和唯一值
- Excel COUNTIF - 常见问题和问题
Excel 中的 COUNTIF 函数 - 语法和用法
Excel COUNTIF 函数用于对指定范围内满足特定条件或条件的单元格进行计数。
例如,您可以编写一个 COUNTIF 公式来找出工作表中有多少单元格包含大于或小于您指定的数字的数字。Excel 中 COUNTIF 的另一个典型用途是对具有特定单词或以特定字母开头的单元格进行计数。
COUNTIF 函数的语法非常简单:
COUNTIF(range, criteria)
如您所见,只有 2 个参数,这两个参数都是必需的:
- range - 定义一个或多个要计数的单元格。您可以像通常在 Excel 中那样将范围放入公式中,例如 A1:A20。
- criteria- 定义告诉函数要计数哪些单元格的条件。它可以是数字、文本字符串、单元格引用或表达式。例如,您可以使用以下标准:“10”、A2、“>=10”、“一些文本”。
这是 Excel COUNTIF 函数的最简单示例。您在下图中看到的是过去 14 年最佳网球运动员的名单。公式=COUNTIF(C2:C15,"Roger Federer")
计算罗杰·费德勒的名字在名单上的次数:
笔记。条件不区分大小写,这意味着如果您在上述公式中键入“roger federer”作为条件,将产生相同的结果。
Excel COUNTIF 函数示例
正如您刚刚看到的,COUNTIF 函数的语法非常简单。但是,它允许条件的许多可能变化,包括通配符、其他单元格的值,甚至其他 Excel 函数。这种多样性使得 COUNTIF 函数非常强大并且适合许多任务,您将在下面的示例中看到。
用于文本和数字的 COUNTIF 公式(完全匹配)
事实上,我们刚刚讨论了 COUNTIF 函数,该函数计算与指定条件匹配的文本值。让我提醒您,包含精确文本字符串的单元格公式:=COUNTIF(C2:C15,"Roger Federer")
. 所以,你输入:
- 一个范围作为第一个参数;
- 逗号作为分隔符;
- 用引号括起来的一个词或几个词作为标准。
您可以使用对包含该单词或多个单词的任何单元格的引用,而不是键入文本,并获得完全相同的结果,例如=COUNTIF(C1:C9,C7)
。
同样, COUNTIF 公式适用于数字。如下面的屏幕截图所示,该公式=COUNTIF(D2:D9,5)
完美地计算了 D 列中数量为 5 的单元格。
带有通配符的 COUNTIF 公式(部分匹配)
如果您的 Excel 数据包含您要计算的关键字的多个变体,那么您可以使用通配符计算包含特定单词、短语或字母的所有单元格作为单元格内容的一部分。
假设您有一个分配给不同人员的任务列表,并且您想知道分配给 Danny Brown 的任务数。因为 Danny 的名字有几种不同的书写方式,所以我们输入“*Brown*”作为搜索条件=COUNTIF(D2:D10, "*Brown*")
。
一个星号(*)被用来找到的前导和结尾字符的任何序列的细胞,如在上面的例子中示出。如果您需要匹配任何单个字符,请输入问号(?),如下所示。
小费。在连接运算符 (&) 的帮助下,还可以在单元格引用中使用通配符。例如,不是直接在公式中提供“*Brown*”,您可以在某个单元格中键入它,比如 F1,并使用以下公式计算包含“Brown”的单元格:=COUNTIF(D2:D10, "*" &F1&"*")
计算以某些字符开头或结尾的单元格
您可以使用通配符、星号 (*) 或问号 (?),标准取决于您想要达到的确切结果。
如果您想知道以特定文本开头或结尾的单元格数量,无论单元格包含多少其他字符,请使用以下公式:
=COUNTIF(C2:C10,"Mr*")
- 计算以“先生”开头的单元格。
=COUNTIF(C2:C10,"*ed")
- 计算以字母“ ed”结尾的单元格。
下图演示了第二个公式:
如果您要查找以某些字母开头或结尾并包含确切字符数的单元格计数,请使用 Excel COUNTIF 函数和条件中的问号字符 (?):
=COUNTIF(D2:D9,"??own")
- 计算单元格 D2 到 D9 中以字母“own”结尾且恰好有 5 个字符(包括空格)的单元格数量。
=COUNTIF(D2:D9,"Mr??????")
- 计算以字母“Mr”开头并且在单元格 D2 到 D9 中恰好有 8 个字符(包括空格)的单元格数量。
小费。要查找包含实际问号或星号的单元格数量,请在 ? 之前键入波浪号 (~)。或公式中的 * 字符。例如,=COUNTIF(D2:D9,"*~?*")
将计算 D2:D9 范围内包含问号的所有单元格。
Excel COUNTIF 用于空白和非空白单元格
这些公式示例演示了如何使用 Excel 中的 COUNTIF 函数来计算指定范围内的空单元格或非空单元格的数量。
COUNTIF 不为空
在其他一些 Excel COUNTIF 教程中,您可能会遇到与此类似的计算 Excel 中非空白单元格的公式:
COUNTIF(range,"*")
但事实是,上面的公式只计算包含任何文本值的单元格,这意味着带有日期和数字的单元格将被视为空白单元格而不包括在计数中!
如果您需要一个通用的 COUNTIF 公式来计算指定范围内的所有非空白单元格,请使用:
COUNTIF(range"<>")
或者
COUNTIF(range,"<>"&"")
此公式适用于所有值类型 -文本、日期和数字- 如您在下面的屏幕截图中所见。
COUNTIF 空白
如果您想要相反的情况,即计算某个范围内的空白单元格,您应该遵循相同的方法 - 使用带有通配符的公式作为文本值,并使用 "" 条件来计算所有空单元格。
计算不包含任何文本的单元格的公式:
COUNTIF(range,"<>"&"*")
由于星号 (*) 匹配任何文本字符序列,因此公式计算不等于 * 的单元格,即不包含指定范围内的任何文本。
空格的通用 COUNTIF 公式(所有值类型):
COUNTIF(range,"")
上述公式正确处理数字、日期和文本值。例如,以下是获取范围 C2:C11 中空单元格数量的方法:
=COUNTIF(C2:C11,"")
请注意,Microsoft Excel 有另一个计算空白单元格的功能,即 COUNTBLANK。例如,以下公式将产生与您在上面屏幕截图中看到的 COUNTIF 公式完全相同的结果:
计算空白: =COUNTBLANK(C2:C11)
计算非空白: =ROWS(C2:C11)*COLUMNS(C2:C11)-COUNTBLANK(C2:C11)
另外,请记住,COUNTIF 和 COUNTBLANK 都计算具有仅看起来空白的空字符串的单元格。如果您不想将此类单元格视为空白,请改用以下公式:
=ROWS(C2:С11)*COLUMNS(C2:С11)-COUNTIF(C2:С11,"<>"&"")
有关在 Excel 中计算空白而不是空白的更多信息,请参阅:
- 在 Excel 中计算空单元格的 3 种方法
- 如何计算Excel中的非空单元格
COUNTIF 大于、小于或等于
要计算值大于、小于或等于您指定的数字的单元格,您只需将相应的运算符添加到条件中,如下表所示。
请注意,在 COUNTIF 公式中,带有数字的运算符始终用引号括起来。
标准 | 公式示例 | 描述 |
如果大于则计数 | =COUNTIF(A2:A10,">5") | 计算值大于 5 的单元格。 |
如果小于则计数 | =COUNTIF(A2:A10,"<5") | 计数值小于 5 的单元格。 |
计数如果等于 | =COUNTIF(A2:A10,"=5") | 计数值等于 5 的单元格。 |
如果不等于则计数 | =COUNTIF(A2:A10,"<>5") | 计算值不等于 5 的单元格。 |
如果大于或等于,则计数 | =COUNTIF(C2:C8,">=5") | 计数值大于或等于 5 的单元格。 |
如果小于或等于,则计数 | =COUNTIF(C2:C8,"<=5") | 计数值小于或等于 5 的单元格。 |
您还可以使用上述所有公式根据另一个单元格值对单元格进行计数,您只需要将条件中的数字替换为单元格引用。
笔记。在单元格引用的情况下,您必须将运算符括在引号中并在单元格引用之前添加一个与号 (&)。例如,要计算区域 D2:D9 中值大于单元格 D3 中值的单元格,请使用以下公式=COUNTIF(D2:D9,">"&D3)
:
如果要计算包含实际运算符作为单元格内容一部分的单元格,即字符“>”、“<”或“=”,则在条件中使用带有运算符的通配符。此类标准将被视为文本字符串而不是数字表达式。例如,该公式=COUNTIF(D2:D9,"*>5*")
将计算 D2:D9 范围内的所有单元格,内容类似于“交付 >5 天”或“>5 可用”。
使用带有日期的 Excel COUNTIF 函数
如果您想计算日期大于、小于或等于您指定的日期或另一个单元格中的日期的单元格,您可以使用与我们刚才讨论的公式类似的公式以已经熟悉的方式进行。以上所有公式都适用于日期和数字。让我给你举几个例子:
标准 | 公式示例 | 描述 |
计算等于指定日期的日期。 | =COUNTIF(B2:B10,"6/1/2014") | 计算日期为 2014 年 6 月 1 日的范围 B2:B10 中的单元格数。 |
计算大于或等于另一个日期的日期。 | =COUNTIF(B2:B10,">=6/1/2014") | 计算区域 B2:B10 中日期大于或等于 6/1/2014 的单元格数量。 |
计算大于或等于另一个单元格中的日期减去 x 天的日期。 | =COUNTIF(B2:B10,">="&B2-"7") | 计算区域 B2:B10 中日期大于或等于 B2 减去 7 天的日期的单元格数。 |
除了这些常见用法之外,您还可以将 COUNTIF 函数与特定的 Excel 日期和时间函数(例如 TODAY())结合使用,以根据当前日期对单元格进行计数。
标准 | 公式示例 |
计算与当前日期相等的日期。 | =COUNTIF(A2:A10,今天()) |
计算当前日期之前的日期,即小于今天。 | =COUNTIF(A2:A10,"<"&TODAY()) |
计算当前日期之后的日期,即大于今天。 | =COUNTIF(A2:A10,">"&TODAY()) |
计算一周内到期的日期。 | =COUNTIF(A2:A10,"="&TODAY()+7) |
计算特定日期范围内的日期。 | =COUNTIF(B2:B10, ">=6/1/2014")-COUNTIF(B2:B10,">6/7/2014") |
以下是在真实数据上使用此类公式的示例(撰写本文时为 2014 年 6 月 25 日):
具有多个条件的 Excel COUNTIF
事实上,Excel 的 COUNTIF 函数并不是专门为使用多个条件计算单元格而设计的。在大多数情况下,您会使用复数形式的COUNTIFS 函数来计算匹配两个或多个条件(AND 逻辑)的单元格。但是,某些任务可以通过在一个公式中组合两个或多个 COUNTIF 函数来解决。
COUNTIF - COUNTIF 计算范围内的数字
具有 2 个条件的 Excel COUNTIF 函数最常见的应用之一是计算特定范围内的数字,即小于 X 但大于 Y。例如,您可以使用以下公式计算 B2:B9 范围内的单元格,其中 a值大于 5 且小于 15。
=COUNTIF(B2:B9,">5")-COUNTIF(B2:B9,">=15")
COUNTIF + COUNTIF 对具有多个 OR 条件的单元格进行计数
如果您想要获取某个范围内的多个不同项目,请将 2 个或更多 COUNTIF 函数添加在一起。假设您有一个购物清单,并且想知道包含多少软饮料。要完成它,请使用类似于此的公式:
=COUNTIF(B2:B13,"Lemonade")+COUNTIF(B2:B13,"*juice")
请注意,我们在第二个条件中包含了通配符 (*),它用于计算列表中的各种果汁。
以同样的方式,您可以编写具有多个条件的 COUNTIF 公式。以下是具有多个 OR 条件的 COUNTIF 公式示例,用于计算柠檬水、果汁和冰淇淋的数量:
=COUNTIF(B2:B13,"Lemonade") + COUNTIF(B2:B13,"*juice") + COUNTIF(B2:B13,"Ice cream")
有关使用 OR 逻辑计算单元格的其他方法,请参阅本教程:Excel COUNTIF 和 COUNTIFS with OR conditions。
使用 COUNTIF 函数查找重复值和唯一值
Excel 中 COUNTIF 函数的另一种可能用法是查找一列、两列之间或一行中的重复项。
示例 1. 查找并计算 1 列中的重复项
例如,这个简单的公式 =COUNTIF(B2:B10,B2)>1 将发现 B2:B10 范围内的所有重复条目,而另一个函数 =COUNTIF(B2:B10,TRUE) 将告诉您有多少重复项:
示例 2. 计算两列之间的重复项
如果您有两个单独的列表,例如 B 列和 C 列中的名称列表,并且您想知道两列中出现了多少个名称,则可以将 Excel COUNTIF 与 SUMPRODUCT 函数结合使用来计算重复项:
=SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)>0)*(C2:C1000<>""))
我们甚至可以更进一步,计算C 列中有多少个唯一名称,即没有出现在 B 列中的名称:
=SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)=0)*(C2:C1000<>""))
小费。如果要突出显示重复的单元格或包含重复条目的整行,您可以根据 COUNTIF 公式创建条件格式规则,如本教程所示 - Excel 条件格式公式以突出显示重复项。
示例 3. 计算一行中的重复值和唯一值
如果要计算特定行而不是列中的重复值或唯一值,请使用以下公式之一。例如,这些公式可能有助于分析彩票抽奖历史。
计算一行中的重复项:
=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)>1)*(A2:I2<>""))
计算一行中的唯一值:
=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)=1)*(A2:I2<>""))
Excel COUNTIF - 常见问题和问题
我希望这些示例能帮助您了解 Excel COUNTIF 函数。如果您在数据上尝试了上述任何公式,但无法使它们起作用,或者您创建的公式有问题,请查看以下 5 个最常见的问题。您很有可能会在那里找到答案或有用的提示。
1. COUNTIF 在不连续的单元格范围内
问题:如何在 Excel 中对非连续区域或选定的单元格使用 COUNTIF?
答案: Excel COUNTIF 不适用于非相邻区域,其语法也不允许将多个单独的单元格指定为第一个参数。相反,您可以使用多个 COUNTIF 函数的组合:
错误的: =COUNTIF(A2,B3,C4,">0")
正确的: =COUNTIF(A2,">0") + COUNTIF(B3,">0") + COUNTIF(C4,">0")
另一种方法是使用 INDIRECT 函数创建范围数组。例如,以下两个公式产生的结果与您在屏幕截图中看到的结果相同:
=SUM(COUNTIF(INDIRECT({"B2:B8","D2:C8"}),"=0"))
=COUNTIF($B2:$B8,0) + COUNTIF($C2:$C8,0)
2. COUNTIF 公式中的&符号和引号
问题:何时需要在 COUNTIF 公式中使用与号?
答:这可能是 COUNTIF 函数中最棘手的部分,我个人觉得这很令人困惑。但是,如果您仔细考虑一下,您会看到其背后的推理 - 需要一个与号和引号来构造参数的文本字符串。因此,您可以遵守以下规则:
如果在精确匹配条件中使用数字或单元格引用,则既不需要与号也不需要引号。例如:
=COUNTIF(A1:A10,10)
或者=COUNTIF(A1:A10,C1)
如果您的条件包括文本、通配符或带有数字的逻辑运算符,请将其括在引号中。例如:
=COUNTIF(A2:A10,"lemons")
或=COUNTIF(A2:A10,"*")
或=COUNTIF(A2:A10,">5")
如果您的条件是带有单元格引用或其他 Excel函数的表达式,则必须使用引号 ("") 来开始文本字符串,并使用与号 (&) 来连接并结束字符串。例如:
=COUNTIF(A2:A10,">"&D2)
或者=COUNTIF(A2:A10,"<="&TODAY())
如果您不确定是否需要与号,请尝试两种方法。在大多数情况下,& 符号工作得很好,例如,以下两个公式同样有效。
=COUNTIF(C2:C8,"<=5")和=COUNTIF(C2:C8,"<="&5)
3. COUNTIF 用于格式化(颜色编码)单元格
问题:如何按填充或字体颜色而不是按值计算单元格?
答:很遗憾,Excel COUNTIF 函数的语法不允许使用格式作为条件。根据颜色对单元格进行计数或求和的唯一可能方法是使用宏,或者更准确地说是 Excel 用户定义函数。您可以在本文中找到适用于手动着色单元格以及条件格式单元格的代码 -如何在 Excel 中按颜色对单元格进行计数、求和和过滤。
4.#NAME?COUNTIF 公式中的错误
问题:我的 COUNTIF 公式抛出 #NAME?错误。我怎样才能把它修好?
回答:很可能,您为公式提供了不正确的范围。请查看上面的第 1点。
5. Excel COUNTIF 公式不起作用
问题:我的 COUNTIF 公式不起作用!我做错了什么?
答:如果您编写的公式看似正确,但它不起作用或产生错误结果,请首先检查最明显的内容,例如范围、条件、单元格引用、与号和引号的使用。
在 COUNTIF 公式中 使用空格时要非常小心。在为这篇文章创建一个公式时,我几乎要拔毛了,因为正确的公式(我确信它是正确的!)行不通。事实证明,问题出在介于两者之间的一个微不足道的空间中,啊……例如,看看这个公式:
=COUNTIF(B2:B13," Lemonade")
.
乍一看,没有什么错,只是在左引号后面多了一个空格。Microsoft Excel 将很好地吞下公式,而不会出现错误消息、警告或任何其他指示,假设您确实想计算包含单词“Lemonade”和前导空格的单元格。
如果您使用具有多个条件的 COUNTIF 函数,请将公式拆分为多个部分并分别验证每个函数。
这就是今天的全部内容。在下一篇文章中,我们将探讨几种在 Excel 中多条件计数单元格的方法。