如何用Excel实现基于指定列排除项的COUNTIF/COUNTIFS计数？

针对你60万条文件列表的统计需求，以下是几种可行的解决方案，支持动态调整C列的排除项：

1. SUMPRODUCT函数（兼容全版本Excel） #

使用数组运算实现OR逻辑排除，公式如下：

=SUMPRODUCT(--(COUNTIF(C:C,A:A)=0),--(A:A<>""))

• 逻辑说明：COUNTIF(C:C,A:A)会逐个检查A列单元格是否在C列排除项中，返回0表示不在排除项；--将布尔值转换为1/0；SUMPRODUCT对符合条件的单元格求和，同时--(A:A<>"")排除A列的空白行。
• 优化建议：避免整列引用（如A:A），改为精确范围（如A1:A600000），减少运算量提升速度。

2. 动态数组公式（Excel 365/2021专属，效率更高） #

利用Excel新函数实现高效筛选统计，公式如下：

=ROWS(FILTER(A:A,ISNA(XMATCH(A:A,C:C))*(A:A<>"")))

• 逻辑说明：XMATCH(A:A,C:C)查找A列值在C列的位置，ISNA标记未找到（即不在排除项）的行；*(A:A<>"")过滤空白行；FILTER筛选出符合条件的行，ROWS统计行数。
• 优势：动态数组引擎优化过运算，60万条数据的处理速度远快于SUMPRODUCT。

3. VBA宏方案（超大数据量最优选择） #

对于60万条数据，VBA结合字典的查找效率是最高的，且支持一键统计，无需手动修改公式：

Sub CountNonExcludedFiles()
    Dim ws As Worksheet
    Dim lastRowA As Long, lastRowC As Long
    Dim excludeDict As Object
    Dim i As Long, resultCount As Long
    
    ' 指定操作工作表（可根据实际修改）
    Set ws = ActiveSheet
    ' 创建字典存储排除项，实现快速查找
    Set excludeDict = CreateObject("Scripting.Dictionary")
    
    ' 加载C列排除项到字典
    lastRowC = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    For i = 1 To lastRowC
        If ws.Cells(i, "C").Value <> "" Then
            excludeDict(ws.Cells(i, "C").Value) = True
        End If
    Next i
    
    ' 统计A列不在排除项且非空白的数量
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    resultCount = 0
    For i = 1 To lastRowA
        If ws.Cells(i, "A").Value <> "" And Not excludeDict.Exists(ws.Cells(i, "A").Value) Then
            resultCount = resultCount + 1
        End If
    Next i
    
    ' 将结果输出到D1单元格（可自定义位置）
    ws.Range("D1").Value = "符合条件的文件数：" & resultCount
End Sub

• 使用说明：打开VBA编辑器（Alt+F11），插入模块粘贴代码，回到工作表添加按钮绑定该宏，点击即可完成统计。后续只需修改C列排除项，点击按钮就能更新结果。

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