プチIT化 PR

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

記事内に商品プロモーションを含む場合があります。
当サイトは、アフィリエイト広告を利用しています。

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

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

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

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

VBAマクロの概要・構成:Array配列でフィルター

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

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

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

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

4.Array配列でフィルター

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

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

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

表をテーブルに変換

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

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

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

テーブル名を付ける

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

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

デフォルトでは"テーブル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) & ","

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

「2.文字列に変換し、ワイルドカードで比較」でマッチした文字列をtmpStrにカンマ区切りで入れていたので、この文字列をArray配列に格納します。

Split関数を使って、filterArryという配列に一時的に格納した文字列をカンマ区切りでArray配列へ格納します。

filterArry = Split(tmpStr, ",")

4.Array配列でフィルター

あとは、文字列比較結果を格納したArray配列filterArryでフィルターをかけています。

Range("A1").AutoFilter Field:=1, Criteria1:=filterArry, Operator:=xlFilterValues

Field:=1の所の数字には、テーブルの何列目か?という数値を指定しています。2列目なら2を指定するなど、お使いの状況に合わせて変えてくださいね!

わたあめ
わたあめ
ということで、主要ななマクロ処理部分を説明したよ!

さいごに

いかがだったでしょうか?つたない説明でしたが、少しでもサンプルマクロがお役に立てればいいなと思っています。最後までお読みいただき、ありがとうございました。思い通りのマクロが実現できますように!それでは、また!