如何用Excel公式或VBA提取A列中不存在于B列的数据至C列

方法一：Excel公式实现 #

在Sheet1的C1单元格输入以下公式，然后下拉填充到A列所有有数据的行：

=IF(COUNTIF(B:B,A1)=0,A1,"")

• 原理：COUNTIF(B:B,A1)统计A1值在B列出现的次数，次数为0说明A1不在B列，此时显示A1，否则显示空值。
• 优化：如果B列数据范围固定（比如B1:B50），可以把B:B改成B1:B50，提升计算速度。

若你的Excel版本支持XLOOKUP（Office 365/2021及以上），也可以用更直观的公式：

=IF(ISNA(XLOOKUP(A1,B:B,B:B)),A1,"")

方法二：修正后的VBA代码 #

你原来的VBA代码存在两个核心问题：

1. 直接将单个单元格与整个B列区域比较，逻辑错误，无法判断值是否存在于B列；
2. 每次都把结果写入C1，会覆盖之前的内容，应该写入C列的下一个空行。

修正后的代码如下：

Sub FindMissingInB()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRowA As Long
    Dim nextEmptyRowC As Long
    
    ' 指定目标工作表
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 获取A列最后一行有数据的行号
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For Each rng In ws.Range("A1:A" & lastRowA)
        ' 检查当前单元格值是否不在B列
        If Application.CountIf(ws.Range("B:B"), rng.Value) = 0 Then
            ' 找到C列下一个空行
            nextEmptyRowC = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
            ' 写入结果到C列
            ws.Cells(nextEmptyRowC, "C").Value = rng.Value
        End If
    Next rng
End Sub

• 使用步骤：按Alt+F11打开VBA编辑器，插入模块，粘贴上述代码，运行即可。
• 适用场景：数据量较大时，无需手动下拉公式，自动完成批量检测。

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