【VBA】フィルターでの完全一致・部分一致

大量の情報を絞り込むのに、エクセルのフィルター機能は大変役に立ちます。
手作業で絞り込みをすることもできますが、いつも使う絞り込みルールがある場合、VBAで実装した方が作業が格段に効率化されるでしょう。

今回は下記のテーブルにフィルターを掛ける例を幾つか提示します。

※国土交通省建設コンサルタント登録事業者一覧より抜粋・改変
http://www.mlit.go.jp/totikensangyo/const/totikensangyo_const_tk2_000059.html

1.フィルターをかける

'フィルターをかける
Private Sub filterData()
    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter
End Sub

Sheets("filterTest").Range("B2").CurrentRegionにてB2セルの属するテーブル領域であるB2:G10の範囲を取得し、この範囲に対してフィルターをかけています。

ちなみに、このコードを再実行するとフィルターが解除されます。

2. 完全一致で絞り込む
2−1. 1項目での絞り込み

上図で「商号又は名称」を「株式会社　東横エルメス」で絞り込む際、下記のように書きます。

'フィルター1項目完全一致

Private Sub filterData0()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社　東横エルメス"

End Sub

この時field:=6の6はフィルター範囲左端から数えて絞り込み対象列である「商号又は名称」が6列目に当たることを示します。

2−2. 2項目以上での絞り込み

同様に2項目で絞り込む際、下記のように書きます。

'フィルター2項目完全一致

Private Sub filterData1()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社　東横エルメス", _

            Operator:=xlOr, _

            Criteria2:="沖昌エンジニアリング　株式会社"

End Sub

2項目以上はCriteria1に配列を指定することで絞り込むことができます。

'フィルター2項目以上完全一致

Private Sub filterData2()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("株式会社　一測設計", "星和電機　株式会社", "株式会社　長大"), _

            Operator:=xlFilterValues

End Sub

3. 部分一致で絞り込む
3-1. 1項目での絞り込み

基本的に完全一致と書き方が同じで、Criteriaに指定する文字列を変えることで絞り込むことができます。

'フィルター1項目部分一致

Private Sub filterData3()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="株式会社*"

End Sub

この場合、Criteria1にワイルドカード(*)を用いて「株式会社*」と指定しているので、「株式会社」が前方一致するように絞られます。

3−2. 2項目以上での絞り込み

これも完全一致と書き方は同じとなります。

'フィルター2項目部分一致

Private Sub filterData4()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:="*　株式会社", _

            Operator:=xlOr, _

            Criteria2:="*和*"

End Sub

また、配列を指定して下記のように書くこともできます。

'フィルター2項目部分一致

Private Sub filterData5()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("*　株式会社", "*和*"), _

            Operator:=xlFilterValues

End Sub

ただし、残念ながら以下のように3項目以上の指定はできないようです。

'フィルター3項目以上部分一致NGパターン

Private Sub filterData6()

    Sheets("filterTest").Range("B2").CurrentRegion.AutoFilter _

            field:=6, _

            Criteria1:=Array("*　株式会社*", "*和*", "*エンジニア*"), _

            Operator:=xlFilterValues

End Sub

完全一致で複数指定できたのだから普通できるだろうと思うのですが、実際実装してみ
ると何も表示されない絞り込みとなり、VBAエラーともなりません。
エクセルのこの感じなんとも言えません。

そのため、3項目を部分一致で絞りたい場合は、他の方法を模索しなければなりません。

【エクセル関数】文字列の加工① MID関数とFIND関数

外部のデータを利用する上で、一部のデータを加工したい場合があると思います。
たとえば官公庁のデータだったり、お客様のデータや外部システムからの出力データだったりと実際そういう場面は多々あるでしょう。

そういう時活躍する関数がMID関数です。

◼︎MID(文字列,開始位置,文字数)
引数「文字列」の文字列から、引数「開始位置」に指定した文字数目から数えて引数「文字数」分を取得します。

たとえば、文字列「WindowsVista」の1文字目から7文字目まで、つまり「Windows」を取得しようと考えた場合、

=MID("WindowsVista",1,7)

と記述します。

またこの文字列から「Vista」を取得しようと考えた場合、8文字目から5文字分を取得することとなるため、
=MID("WindowsVista",8,5)
と記述します。
この場合、指定する「文字数」はオーバーしても良いため、このように書くこともできます。
=MID("WindowsVista",8,100)

◼︎MID関数の用法
下図に日本郵便の配布するCSVから取得した郵便番号と住所の一部を記載しました。ただし、CSV上では郵便番号がハイフン「-」抜きで書かれているため、MID関数を用いてハイフン「-」ありに文字列を加工することにします。

こんな風に書きます。

=MID($C3,1,3)&"-"&MID($C3,4,4)

「MID($C3,1,3)」で1文字目から3文字の「604」を取得して、「MID($C3,4,4)」で4文字目から4文字の「0991」を取得、最後にハイフン「-」でつなげて郵便番号の形にしています。

このようにMID関数を使えば、抜き出す文字列の始まりと始まりから取得する文字数がわかる場合に限り、文字列を抽出し、加工することができます。
今回は郵便番号が必ず4文字目にハイフン「-」がくる8文字の文字列となるという規則性に着目してMID関数を利用しましたが、文字数が決まっていない場合にもMID関数の利用場面があります。

たとえば、メールアドレスから「@」以降を省いたユーザーIDを取得する場合、こんな風に取得できます。

=MID($C3,1,FIND("@",$C3)-1)

ユーザーIDを取得するにはMID関数を利用するには「開始位置」が1文字目であるかわかっているので、「@」の手前までの「文字数」を取得する必要が有ります。
そのためこの関数式ではFIND関数で「＠」の文字が何文字目に来るかを取得し、その手前までの文字数を計算してMID関数の第3引数にしています。

◼︎FIND(検索文字列,対象,[開始位置])
「対象」に指定した文字列から「検索文字列」を検索し最初の文字列から何文字目であるかを取得する。オプショナルな引数である「開始位置」を指定すると指定した文字数目から「検索文字列」を探すようになります。

◼︎FIND関数の用法
上述の通り。


◼︎より応用的な使い方として
FIND関数のオプショナルな引数である「開始位置」を活用すると、以下のように「検索文字列」が複数ある場合でも対応できます。

例えばC4セルにある「osama-no-mimi@lobanomimi.com」から「-」に囲まれた「no」をMID関数を用いて取得する場合を考えます。
この時FIND関数で初めて「-」がくる位置と、次に「-」がくる位置を求められれば「開始位置」と開始位置からの「文字数」を取得でき、そうすればMID関数を使うことができます。

計算してみると、 この場合最初の「-」は

=FIND("-",C4)
で6文字目に来ることがわかり、

次の「-」は「開始位置」を以下のように設定することで、

=FIND("-",C4,FIND("-",C4)+1)

9文字目に来ることがわかります。

よってMID関数で指定すべき「開始位置」は、
=FIND("-",C4) + 1
(=7文字目)
「文字数」は、

=FIND("-",C4,FIND("-",C4)+1) - FIND("-",C4) - 1
(=2文字)

となります。

以上から「no」を取得するには

=MID(C4,FIND("-",C4)+1,FIND("-",C4,FIND("-",C4)+1) - FIND("-",C4) - 1)

と記述すれば良いこととなります。


さすがにここまで来るとちょっと読みにくいですね。