Excel VBAであいまい検索する方法！Match/Like/Filter/Find使用！

Excel VBAであいまい検索をしてデータを取得したいときはないでしょうか。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

Excel VBAであいまい検索する方法

あいまい検索の対象となる表を用意

まずは、あいまい検索の対象となる表をExcelに用意しましょう。

サンプルはA列に名前、B列に番号を入れています。

方法その1；MATCH関数を使う

MATCH関数とは

MATCH関数とは対象列を上から順に検索し、一致したらその位置番号を返すワークシート関数です。

3番目のパラメーター”0”の場合は完全一致で検索されます。今回はアスタリスクを追加し部分一致検索をしています。

サンプルコード

サンプルコードは以下の通りです。
サンプル表のA列を対象に「いも」が含まれる初めの行番号を求めます。

Sub あいまい検索1()
    Dim varResult As Variant

    varResult = WorksheetFunction.Match("*いも", Range("A:A"), 0)
    
    Debug.Print varResult
End Sub

結果

結果です。

タカヒロ

3 番目のパラメーター”1”の場合でも同じ結果を得ることができます。

方法その2；VLookup関数を使う

VLookup関数とは

VLookup関数とは対象範囲を左上から順に検索し、一致したら合致した行番号の指定列位置にある値を返すワークシート関数です。

サンプルコード

サンプルコードは以下の通りです。
サンプル表のA列を対象に「いも」が含まれる隣のセルの値を求めます。

Sub あいまい検索2()
    Dim varResult As Variant

    varResult = Application.VLookup("*いも", Range("A:B"), 2, False)
    
    Debug.Print varResult
End Sub

結果

結果です。

タカヒロ

Vlookupであいまい検索をする場合は第四引数をTrueにするのですが、うまくいかない場合があるので、完全一致のFalseにしてアスタリスクを付加して部分一致検索をさせています。

方法その3；Like演算子を使う

Like演算子とは

Like演算子は文字列をパターンで突き合わせ、その結果をTrue/Falseで返します。パターンはアスタリスク「*」、疑問符「?」、 チルダ「~」などのワイルドカード文字を含めることができます。

サンプルコード

サンプルコードは以下の通りです。
サンプル表のA列を対象に「いも」が含まれるセルの行番号を求めます。

Sub あいまい検索3()
    Dim varResult As Variant
    
    For Each cell In Range("A:A")
        If cell.Value Like "*いも" Then
            varResult = cell.row
            Exit For
        End If
Next

結果

結果です。

方法その4；Findメソッドを使う

Findメソッドとは

Findメソッドはセル範囲の中で検索した文字列を含むセルを返します。

Findメソッドのオプションで LookAt:=xlPartとすると部分一致検索となり、ワイルドカードを使用することができます。

サンプルコード

サンプルコードは以下の通りです。
サンプル表のA列を対象に「いも」が含まれるセルの行番号を求めます。

Sub あいまい検索4()
     Dim varResult As Variant
Dim objFindRange As Object

    Set objFindRange = Range("A:A").Find(What:="いも", LookIn:=xlValues, LookAt:=xlPart)
    varResult = objFindRange.row
    Debug.Print varResult
End Sub

結果

結果です。

方法その5；Filter関数を使う

Filter関数とは

Filter関数は一次元配列から検索文字列を含む要素に絞り込んだ配列を返します。

サンプルコード

サンプルコードは以下の通りです。
サンプル表のA列を対象に「いも」が含まれる隣のセルの値を求めます。

Sub あいまい検索5()
    Dim varResult As Variant
    Dim arrTemp(5) As String
    Dim arrTemp1 As Variant
    
    For i = 0 To UBound(arrTemp)
        arrTemp(i) = Cells(i + 1, 1)
    Next

    arrTemp1 = Filter(arrTemp, "いも")

    For Each varResult In arrTemp1
        Debug.Print varResult
    Next

End Sub

結果

結果です。

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。