Excel中使用Countif函数统计关键词时如何避免单元格重复计数？

无宏原生公式方案 #

不需要编写宏，直接用现有函数组合即可实现「单单元格命中多个关键词仅计数1次」的统计需求，适配你使用;作为参数/数组元素分隔符的设置，旧版Excel公式如下：

=SUMPRODUCT(SIGN(MMULT(--ISNUMBER(SEARCH({"Red";"Green";"Blue"};A1:A7));{1;1;1})))

旧版Excel输入完成后按Ctrl+Shift+Enter三键确认数组公式即可得到正确结果。

公式逻辑拆解：

• SEARCH({"Red";"Green";"Blue"};A1:A7)：逐单元格匹配每个关键词，返回关键词在文本中的起始位置，未匹配则返回错误值
• --ISNUMBER(...)：将匹配成功的结果转换为1，匹配失败转换为0，生成与「单元格数×关键词数」对应的0/1矩阵
• MMULT(...,{1;1;1})：对每个单元格的所有关键词匹配结果求和，单单元格命中N个关键词就返回N，未命中返回0
• SIGN(...)：将所有大于0的数值统一转换为1，实现单单元格多命中仅保留1次计数
• SUMPRODUCT：对最终的0/1数组求和，得到符合要求的总单元格数

如果你使用Excel 365/2021及以上支持动态数组的版本，可以用更简洁的公式，输入后直接回车即可生效，不需要按三键：

=SUM(--(BYROW(--ISNUMBER(SEARCH({"Red";"Green";"Blue"};A1:A7));LAMBDA(x;OR(x)))))

自定义宏函数方案 #

如果需要简化日常输入，可以封装为自定义函数，后续直接调用短公式完成统计，操作步骤：

• 按Alt+F11打开VBA编辑器
• 在左侧工程面板右键点击当前工作簿名称，依次选择「插入」-「模块」
• 在弹出的代码编辑窗口粘贴以下VBA代码：

Function CountAnyMatch(rng As Range, ParamArray keywords() As Variant) As Long
    Dim cell As Range, kw As Variant
    Dim isHit As Boolean
    For Each cell In rng
        isHit = False
        For Each kw In keywords
            ' 匹配默认不区分大小写，需要区分大小写可把vbTextCompare改为vbBinaryCompare
            If InStr(1, cell.Text, kw, vbTextCompare) > 0 Then
                isHit = True
                Exit For
            End If
        Next
        If isHit Then CountAnyMatch = CountAnyMatch + 1
    Next
End Function

• 关闭VBA编辑器返回表格，即可直接调用自定义函数

自定义函数语法如下：

=CountAnyMatch(统计区域;关键词1;关键词2;关键词3;...)

针对你的示例场景，公式写为：

=CountAnyMatch(A1:A7;"Red";"Green";"Blue")

该函数无关键词数量限制，新增统计关键词直接在公式后追加参数即可，单单元格命中多个关键词时仅计数1次。

注意：使用宏函数时，文件需保存为*.xlsm（启用宏的工作簿）格式，否则宏代码会丢失，函数无法正常运行。

内容的提问来源于stack exchange，提问作者Enderluck