试算表 COUNTIF 函数用法教学

COUNTIF 函数是什么？

在试算表中，COUNTIF 函数用于计算某一范围内符合特定条件的储存格数量，可以计算包含数字、日期、文字、符号或空白的储存格。

COUNTIF函数公式：=COUNTIF(范围, 标准)

• 范围 (range)：要计数的储存格范围。

• 标准 (criteria)：决定哪些储存格将被计算的条件。它可以是数字、符号、字串、储存格参照、或数学式。

请注意：COUNTIF 仅适用于单一标准。如果您需要设定多个标准，请使用 COUNTIFS 函数。

试算表 COUNTIF 函数用法范例

情况一：COUNTIF 大于、等于或小于

假设在 C2～C12 储存格中有一份销售数据列表，您想要计算有多少笔订单的销售额超过10,000元。

请输入公式 =COUNTIF(B2:B12,">10,000")。答案为7。

小技巧：与储存格参照进行比较

当与储存格参照进行比较时，将运算符号放在两个引号中间，并在储存格参照之前加上一个「&」符号。例如，您想计算有多少笔订单有超过B15储存格里的目标销售额，请使用此公式：=COUNTIF(B2:B12,">"&B15)。答案为4。

情况二：COUNTIF 完全相符的字串

承上题，我们想要计算由 William 的成交订单数。在 A2～A12 储存格中有一份名单，我们想要计算此名单中 William 出现的次数。

使用公式：=COUNTIF(A2:A12,"william")，计算范围 A2:A12 中完全符合字串"william"的储存格数量。答案为3。

请注意：COUNTIF 函数不在意字母大小写，也就是说 "william" 和 "WILLIAM" 被视为相同。

情况三：COUNTIF 使用万用字元的字串

万用字元可以让您在设定文字相关的标准时更加灵活。您可以在标准中使用万用字元来计算包含特定单字、词组或字母的储存格数量。

请注意：万用字元仅能套用于文字，不适用于数字。

星号（*）

只要储存格中有提到某个单字或词组，星号（*）都会计算在内，不管它在储存格内的位置或排序。

范例：假设您有一份顾客评论表单，您想要计算有提到「不满意（dissatisfied）」这个词的评论数量。您可以使用公式：

=COUNTIF(A2:A7,"*dissatisfied*")

此公式计算范围 A2:A7 中有提到"dissatisfied"这个词的所有储存格。星号允许这个词的前面或后面存在其他字元。得出答案为2。

小技巧：计算以特定字词开始或结束的储存格

如果您想要计算以某个字词开头或结尾的储存格，而不管这个字词有几个字母，可以使用以下公式：
=COUNTIF(范围,"Pro*") 来计算以"Pro"开头的储存格，=COUNTIF(范围,"*ing") 来计算以"ing"结尾的储存格。

问号（?）

问号（?）代表着某一个字元，包括空格也算，适用于计算具有明确字元数量的储存格。

范例：在零售库存管理的情境，假设您正在寻找有七个字元的产品编号，并且最后两个字元是BL（代表蓝色）。使用公式：=COUNTIF(A2:A11, "?????BL")。得出答案为4。这样做可以帮助您有效率地根据颜色管理库存。

小技巧：要计算「字面上」包含问号（？）或星号（*）这个符号的储存格，请在公式中的万用字元前使用波浪号（~）躲避。例如，=COUNTIF(范围,"~?") 将计算范围内包含问号符号的所有储存格。

情况四：COUNTIF 空白和非空白储存格

您可以在试算表中使用 COUNTIF 函数来计算指定范围内空白或非空白储存格的数量。

COUNTIF 计算非空白储存格

使用公式：=COUNTIF(范围,"<>")

在试算表中，「<>」这个运算符号代表「不等于」，因此这个公式代表计算「不等于空白」的储存格，也就是计算指定范围内包含任何值的所有非空白储存格，而不管这个值是数字、文字还是日期。

小技巧：如果您想计算只含有文字（包括空白字元）的非空白储存格，请使用 =COUNTIF(范围,"*")。这样做可以确保排除掉含有数字或日期等值的储存格。

COUNTIF 计算空白储存格

相反地，要计算范围内的所有空白储存格，您可以使用公式：=COUNTIF(范围,"")

请注意：COUNTBLANK 函数也可以达到相同目的。只需使用 =COUNTBLANK(范围) 就可以获得与 =COUNTIF(范围,"") 相同的结果。

然而，COUNTIF 和 COUNTBLANK 函数都会把套公式后得到的空白字元（""）视为非空白，这在计算公式结果时可能会出现问题。如果要排除空白字元，请使用 =COUNTIF(范围,"<>") - COUNTIF(范围,"")。或者可以根据您的需求考虑使用其他函数，像是 COUNTA 或 SUMPRODUCT 函数。

情况五：COUNTIF 日期

您也可以使用 COUNTIF 来分析日期资料，帮助您有效率地追踪与管理日期相关资料。

以下是一些常用公式：

• 等于指定日期：=COUNTIF(范围,"3/1/2024")

• 大于或等于指定日期：=COUNTIF(范围,">=3/1/2024")

• 今天：=COUNTIF(范围,TODAY())

• 早于今天：=COUNTIF(范围,"<"&TODAY())

• 今天起一周内：=COUNTIF(范围,"="&TODAY()+7)

• 特定日期范围内：=COUNTIF(范围,">=3/1/2024")-COUNTIF(范围,">6/30/2024")

COUNTIF 包含两个标准

COUNTIF 函数只能指定一个标准。如果您要指定两个或以上标准，请使用 COUNTIFS 函数。然而，您仍然可以透过将多个 COUNTIF 函数组合在一个公式中简单地达到目的。

例如，要計算介於兩個數字之間的值的數量，例如大於10但小於50的值，您可以使用公式 =COUNTIF(範圍,">10")-COUNTIF(範圍,">=50")。此外，要計算多個「或」標準，例如在旅遊目的地列表中計算"London"或"Manchester"的出現次數，您可以使用 =COUNTIF(範圍,"London")+COUNTIF(範圍,"Manchester")。

COUNTIF 最佳做法与建议

1. 使用命名范围：与其每次都手动选取范围，不如使用命名范围。不仅可以让公式更简单好读，也能减少错误。命名范围可以来自当前的工作表、同一个活页簿中的另一个工作表、或来自另一个活页簿。请确保您所引用的活页簿是打开的。

小技巧：如何命名范围

• 方法一（最快）：

(1) 选择储存格。
(2) 在名称栏位中编辑名称。
(3) 按下“Enter”键。

• 方法二：

(1) 选择储存格。
(2) 到功能区「公式」分页，点选「名称」>「定义名称」。
(3) 在对话框中，输入范围名称、范围和储存格参照，并可写下注解。
(4) 点击「确定」。

2. 确保绝对参照：在将 COUNTIF 公式复制到其他储存格时，请确保范围参照是绝对位置（例如，$A$1:$A$10），以避免公式跑掉而导致错误。

3. 确保标准的格式正确：将标准参数放入两个引号中间，并使用适当的万用字元，以避免 COUNTIF 错误。

4. 使用样本数据进行测试：在将 COUNTIF 应用于大型资料集之前，先使用样本数据进行测试，以确保它能有效产生所需的结果，这样做有助于及早发现潜在问题。

5. 记录您的公式：尤其是在设定复杂的标准或处理大型资料集时，纪录公式有助于清楚理解 COUNTIF 函数背后的逻辑。这样做也能帮助排除错误，也让自己和他人更容易维护和修正试算表。