【Excel VBA】ワイルドカードを使って数値フィルターをかける方法

こんにちは、わたあめです！皆さん、数値フィルターでワイルドカードが使えないってご存知でした？この前の記事で、数値に対してはワイルドカードが使えないという事が（自分の中で）発覚しました。

【Excel】数値フィルターがかからない？！ワイルドカードの使い方こんにちは、わたあめです。Excelの数値フィルターを使って、フィルタをかけてもかからない時ってないですか？具体的には、数値フィルタでワ...

数値フィルターでかけなくてもテキストフィルターでかければ…という案もあります。が、計算などを行うので数値は数値のまま扱うとして、ワイルドカードを使ってフィルターをかける方法を模索しました。

今回の記事ではVBAマクロを使って、数値に対してワイルドカードを使ったフィルターをかける方法を紹介していきます。それでは、いってみましょう！

VBAマクロの概要・構成：Ａrray配列でフィルター

今回作成したVBAマクロは、ワイルドカードの文字比較を行いArray配列でフィルターをかけるということを行っています。フィルターでワイルドカードで直接条件を指定するのではなくて、大きくは以下の流れで実現しています。

1.フィルターを掛けたいテーブルデータの取得

2.文字列に変換し、ワイルドカードで比較

3.Array配列に比較結果を入れる

4.Array配列でフィルター

それでは、各手順を細かく説明していきますね。

事前準備：テーブルに変換

VBAマクロを作成する前に、列のデータ一覧を取得するために、取得したいデータの範囲をテーブル化しておきましょう。手順は以下です。

表をテーブルに変換

表をテーブルに変換します。変換することで、列の項目名にフィルター用ボタンが現れます。

テーブルの範囲を選択する
[ホーム]タブ>[テーブルとして書式設定]をクリック

このときデザインを選択しますが、ここは見た目の話なのでどれでもOKです！

テーブル名を付ける

VBAマクロを作成する時に、扱いやすいようにテーブルに名前を付けます。

テーブルのどこかのセルを選択
[デザイン]タブ>[テーブル名:]にテーブル名を指定する

デフォルトでは"テーブル1"や"テーブル2"などとExcel側が付けた名前が入っていますよ。そのままでも良いですが、VBAマクロの中で指定するテーブル名になるので分かりやすい名前が良いかと思います。

VBAサンプル：テーブルの値を使ってフィルタをかける

それでは、本題のVBAを作っていきましょう！テーブルのデータを取得し、ワイルドカードを使った文字比較、比較結果をArray配列にいれフィルターをかけています。まずは、マクロサンプルをどうぞ！

Sub filterSample()

' 変数の定義
Dim colData As ListObject
Dim filterArry() As String
Dim tempStr As String
Dim i, j As Integer

' 1.フィルターを掛けたい列のテーブルデータの取得
Set colData = Sheets("Sheet1").Range("番号一覧").ListObject

' 2.文字列に変換し、ワイルドカードで比較
j = 0
tmpStr = ""
For i = 1 To colData.ListColumns("番号").DataBodyRange.Count
　　If colData.ListColumns("番号").DataBodyRange(i) Like "?1*" Then
　　　　tmpStr = tmpStr & colData.ListColumns("番号").DataBodyRange(i) & ","
　　　　j = j + 1
　　End If
Next

' 3.Array配列に比較結果を入れる
ReDim filterArry(j)
filterArry = Split(tmpStr, ",")

' 4.Array配列でフィルター
Range("A1").AutoFilter Field:=1, Criteria1:=filterArry, Operator:=xlFilterValues

End Sub

各処理について、詳しく説明を補足していきますね。

1.フィルターを掛けたいテーブルデータの取得

まずはフィルターをかけたい列があるテーブルデータを取得します。指定の仕方は以下です。ListObjectでテーブルのデータを取得します。

Set colData = Sheets("シート名").Range("テーブル名").ListObject

マクロサンプルでは"Sheet1"というシートの中にある"番号一覧"というテーブル名のテーブルのデータを取得しているということになります。

2.文字列に変換し、ワイルドカードで比較

文字列比較し、ワイルドカードに一致した場合、Array格納に格納しています。

繰り返し処理Forを使って、1行目から"列名"（サンプルでは"番号"という列）の最終行まで1行ずつ処理しています。iはi=1から2,3,4…列名の最終行までカウントアップしていきます。

For i = 1 To colData.ListColumns("列名").DataBodyRange.Count

列名に対してワイルドカードを使った条件を指定します。Like演算子を使って、ワイルドで指定した条件と列のi行目のデータを比較します。

If colData.ListColumns("列名").DataBodyRange(i) Like "ワイルドカードを使った条件指定" Then

サンプルマクロの中では、"番号"列のデータを「Like "?1*"」と指定しています。?は任意の一文字、*は任意の文字列なので、2文字目が1の文字列であれば条件に一致ということになります。例えば、110,213,41000などの文字列が該当しますね。ここで指定する条件＝フィルターにかけたい条件になります。

一致した条件については、tmpStrという変数に一時的にカンマ区切りで格納しています。（後に、Array配列へ格納するためです。）

tmpStr = tmpStr & colData.ListColumns("列名").DataBodyRange(i) & ","