Excel VBAでB列にあってC列にない値を正しく抽出する方法｜FILTER関数の注意点

ExcelでB列にある値のうち、C列に存在しない値を抽出する際に、VBAでFILTER関数を使う場合、範囲の指定やCOUNTIFの扱いによって正しく抽出されないことがあります。今回は、正しい抽出方法とFILTER関数を使う際の注意点について解説します。

1. FILTER関数での典型的な問題

質問のコードでは以下のように書かれています。

result = ActiveSheet.Evaluate("FILTER(B3:B13, NOT(COUNTIF(C3:C9, B3:B13)))")

この場合、COUNTIFの範囲がC3:C9と固定されており、B3:B13の各値に正しく対応していないため、一部の値が抜けてしまうことがあります。特に、配列のサイズや1行のみの返り値に注意が必要です。

2. 解決策：配列処理を用いたVBA

FILTER関数ではなく、VBAで配列をループ処理する方法が確実です。

Sub ExtractMissingValues()
    Dim lastB As Long, lastC As Long
    Dim i As Long
    Dim outputRowD As Long, outputRowF As Long

    lastB = Cells(Rows.Count, "B").End(xlUp).Row
    lastC = Cells(Rows.Count, "C").End(xlUp).Row
    outputRowD = 3
    outputRowF = 3

    ' B列にあってC列にないものをD列へ
    For i = 3 To lastB
        If Application.CountIf(Range("C3:C" & lastC), Cells(i, "B")) = 0 Then
            Cells(outputRowD, "D") = Cells(i, "B")
            outputRowD = outputRowD + 1
        End If
    Next i

    ' C列にあってB列にないものをF列へ
    For i = 3 To lastC
        If Application.CountIf(Range("B3:B" & lastB), Cells(i, "C")) = 0 Then
            Cells(outputRowF, "F") = Cells(i, "C")
            outputRowF = outputRowF + 1
        End If
    Next i
End Sub

3. ポイント

• COUNTIFで範囲を正確に指定すること
• 配列やセル範囲をループ処理して、抜けている値を順に出力する
• FILTER関数は1対1対応での配列処理が難しいため、VBAで明示的にループする方が安定

4. まとめ

FILTER関数をVBAで使う場合、配列のサイズや範囲の設定に注意しないと、一部の値が抜けることがあります。B列とC列の差分を正確に抽出したい場合は、配列をループしてCOUNTIFで存在確認を行い、D列やF列に順に出力する方法が確実です。