【ExcelVBA】表の値が別の表に存在するかを確認するには

この記事では、表の値が別の表に存在するか確認する方法についてご説明します。

【動画】表の値が別の表に存在するか確認する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

WorksheetFunction.CountIfメソッドを使い、まず左の表の値が右の表に存在するか確認しています。

さらに、WorksheetFunction.Matchメソッドを使って左の表の値が右の表のどのセルに存在するのかを特定しています。

マクロ作成の流れ

コードの例

Excelのマクロのコード(例)

Option Explicit

Sub test()

    Dim cellPos     As Long         '行位置
    Dim cnt         As Long         'カウンタ用変数
    Dim fndCelPos   As Long         '見つかった行位置
    Dim bgnCellPos  As Long
    Dim outPutVal   As Long
    Dim lRng        As Range        'Rangeオブジェクト格納用変数
    Dim rRng        As Range        'Rangeオブジェクト格納用変数
    Dim cellVal     As Variant      'セルに関する情報格納用変数
    Dim fndCntVal   As Integer      '左のセルの値が右のセルに存在するデータ件数
    Dim ws          As Worksheet    'Worksheet用変数
    
    '右の表にデータが存在するかを確認する元のセルを取得する
    Set lRng = Worksheets("work").Range("B2:B11")
        
    '行位置用変数を2(行目)で初期化する
    cellPos = 2
    
    'シートを取得する
    Set ws = Worksheets("work")

    For Each cellVal In lRng
            
        '検索先の表のデータ範囲を設定する
        Set rRng = Worksheets("work").Range("H1:H11")
    
        '左の表の値が右の表に存在するか、件数を数える
        fndCntVal = WorksheetFunction.CountIf(rRng, cellVal)

        If fndCntVal >= 1 Then
        
            '1件以上存在している場合
        
            '検索位置を、検索先の表にあるデータの先頭行位置で初期化する
            bgnCellPos = 1
        
            For cnt = 0 To fndCntVal - 1
            
                '検索先の表のデータ範囲を改めて設定する
                Set rRng = ws.Range("H" & bgnCellPos & ":H11")
                
                '検索してデータが見つかった行位置
                fndCelPos = WorksheetFunction.Match(cellVal, rRng, 0)
                
                '検索した値が存在している行位置を取得する
                outPutVal = outPutVal + fndCelPos
                
                '有無判定の結果表示(ある：○、ない：×)
                ws.Range("D" & cellPos).Value = "○"
                
                '存在行の出力
                ws.Range("E" & cellPos).Value = ws.Range("E" & cellPos).Value & "H" & outPutVal & ","
                
                '行位置の値を増やす
                bgnCellPos = bgnCellPos + fndCelPos
                
                '次に検索する行の開始行
                fndCelPos = fndCelPos + 1
                
            Next cnt
            
            '右端の「,」を取り除いたセルの値を再設定する
            ws.Range("E" & cellPos).Value = Left(ws.Range("E" & cellPos).Value, Len(ws.Range("E" & cellPos).Value) - 1)
            
        Else
        
            'データが存在しない場合
            
            '有無判定の結果表示(ある：○、ない：×)
            ws.Range("D" & cellPos).Value = "×"
            
            '存在行の出力
            '→存在しないので「－」を出力
            ws.Range("E" & cellPos).Value = "－"
            
        End If
        
        cellPos = cellPos + 1
        
        'カウンタを初期化する
        bgnCellPos = 1
        outPutVal = 0
    
    Next cellVal

End Sub

注目すべきコード①

最初に見て頂きたいのは17行目と28行目です。

    '右の表にデータが存在するかを確認する元のセルを取得する
    Set lRng = Worksheets("work").Range("B2:B11")

        '検索先の表のデータ範囲を設定する
        Set rRng = Worksheets("work").Range("H1:H11")

以上のコードは、検索元の表と検索先の表の範囲を取得しているコードです。

検索元の表全ての値を、検索先の表に対して検索するのでそれぞれの表の範囲を取得します。

注目すべきコード②

次に見て頂きたいのは25行目です。

    For Each cellVal In lRng

以上のコードは、検索元の表のデータ件数分だけ処理を繰り返すためのコードです。

注目すべきコード③

次に見て頂きたいのは25行目です。

        '左の表の値が右の表に存在するか、件数を数える
        fndCntVal = WorksheetFunction.CountIf(rRng, cellVal)

以上のコードは、WorksheetFunction.CountIfメソッドを実行し、検索元の表全ての値を検索先の表に対して検索する処理のコードです。

検索した結果何件なのかを変数「fndCntVal」に格納しています。

注目すべきコード④

次に見て頂きたいのは33行目から40行目です。

        If fndCntVal >= 1 Then
        
            '1件以上存在している場合
        
            '検索位置を、検索先の表にあるデータの先頭行位置で初期化する
            bgnCellPos = 1
        
            For cnt = 0 To fndCntVal - 1

以上のコードは、検索元の表全ての値を検索先の表に対して検索した結果1件以上存在している場合の判定およびその条件合致の場合と、件数分処理を繰り返すfor文のコードです。

33行目では、検索元の表全ての値を検索先の表に対して検索した結果1件以上存在しているかを判定します。

もし1件以上存在している場合はカウンタbgnCellPosを1に初期化し、40行目で件数分処理を繰り返すfor文を呼び出しています。

検索先の表に検索元の表の値が複数ある場合は、検索先の表のどの行に存在するのかを特定するために、1件1件このfor文で確認するため処理を繰り返すのにfor文を呼び出します。

注目すべきコード⑥

次に見て頂きたいのは43行目から46行目です。

                '検索先の表のデータ範囲を改めて設定する
                Set rRng = ws.Range("H" & bgnCellPos & ":H11")
                
                '検索してデータが見つかった行位置
                fndCelPos = WorksheetFunction.Match(cellVal, rRng, 0)

以上のコードは、検索先の表のデータ範囲を改めて設定し、そのデータ範囲に対してWorksheetFunction.Matchメソッドを実行して検索データを検索する処理のコードです。

43行目では検索先の表のデータ範囲を改めて設定しているのですが、なぜ改めて設定するのかというと、WorksheetFunction.Matchメソッドを実行して2件目以降のデータを特定するために1件目のデータが存在する次の行位置から再度検索したいからです。

例えば2行目と4行目に検索データが存在している場合は、まずは2行目から検索しますが、次の4行目のデータを特定する場合は、2行目の次の行(3行目から)から4行目を特定する必要があります。

あるデータが特定出来たあとに次のデータを検索する際は、表のデータ範囲を改めて設定し直してWorksheetFunction.Matchメソッドを実行します。(46行目)

注目すべきコード⑦

次に見て頂きたいのは52行目から55行目です。

                '有無判定の結果表示(ある：○、ない：×)
                ws.Range("D" & cellPos).Value = "○"
                
                '存在行の出力
                ws.Range("E" & cellPos).Value = ws.Range("E" & cellPos).Value & "H" & outPutVal & ","

以上のコードは、検索データの行の位置が特定できたので、出力結果用のセルに出力しているコードです。

注目すべきコード⑤

次に見て頂きたいのは52行目から55行目です。

        Else
        
            'データが存在しない場合
            
            '有無判定の結果表示(ある：○、ない：×)
            ws.Range("D" & cellPos).Value = "×"
            
            '存在行の出力
            '→存在しないので「－」を出力
            ws.Range("E" & cellPos).Value = "－"

以上のコードは、検索元の表の値を検索先の表に対して検索した結果、0件の場合の判定およびデータが無いことを出力結果用のセルに出力しているコードです。

動作確認

マクロ実行前

今回は以下の表を用意しました。

左の表の商品名が、右の表に存在するかを特定し、その特定した結果をD列とE列に出力します。

マクロ実行後

マクロを実行した結果、特定した結果がD列とE列に出力されました。

最後に

本記事では、表の値が別の表に存在するか確認する方法についてご説明しました。

表の値が別の表に存在するか手軽に確認したい時は本記事を参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら