このサイトはCocoonを使っています。現在「ミックスブルー [作者: y.hiroaki氏]」スキンを適用中です(^^)/

Excel VBA Filter関数で二次元配列を処理してみた

スポンサーリンク

Filter 関数は、文字列型の一次元配列の中から指定された文字列を含む(もしくは含まない)要素を抽出(フィルタリング)して文字列型の一次元配列として返します

くるみこ
くるみこ

というわけで、今回は一次配列のデータ(要素)をフィルタ出来る Filter関数 について解説します。基本的な解説とあわせて、Join関数Split関数 を使って配列の操作を勉強していきましょう

なんか盛り沢山ですね!
頭を整理しながら取り組まないとわからなくなりそうです(^^;
是非よろしくお願いしますm(_ _)m

前回のおさらいはこちらの記事です(^^)/ 下のカードをクリックすれば開きます

くるみこ
くるみこ

前回記事でわかったことは

・セルの書式設定を取得して配列に組み込む方法
・Application.InputBoxを利用して設定セルの指定と目的範囲を設定する方法
・セルに罫線などの書式を取得・設定する方法

【この記事でわかること
・Filter関数の使い方について
Filter関数が使えない2次元配列をJoin関数とSplit関数を使って処理する方法

スポンサーリンク

Filter 関数 とは

冒頭にも書きましたが、Filter 関数は、文字列の配列から指定した文字列でフィルタリングしてヒットした要素だけを含む配列で返します。ただし、対応する配列は一次元配列だけです。必要なデータが配列の中にいくつあるのか、などを調べる場合に利用できます

構文

 Filter(SourceArray, Match, Include, Compare)

引数 / 内容

・SourceArray / 検索する文字列型の一次元配列を指定します
・Match    / 検索する文字列を指定します
・Include    / (省略可)True :検索文字列を含む(既定値)
               False:検索文字列を含まない
・Compare   / (省略可)比較するモードを指定します(省略するとバイナリモード)
 【Compareに使用する(数値)定数/ 内容】
  (0) vbBinaryCompare (既定) ー バイナリモード、大文字と小文字を区別します
  (1) vbTextCompare ー テキストモード、大文字と小文字の区別をしません

★戻り値の型 文字列型 (String) の一次元配列
★Filter関数は要素は返しますが、配列の位置は返しません

Filter 関数 のサンプルコード

Sub Sample()
    Dim myDada(4) As String
    Dim Answer1 As Variant, Answer2 As Variant
    myDada(0) = "あかいりんご"
    myDada(1) = "あおいりんご"
    myDada(2) = "あかいだんご"
    myDada(3) = "しろいだんご"
    
    Answer1 = Filter(myDada, "あか", True)
    MsgBox Join(Answer1, vbCrLf)
    
    Answer2 = Filter(myDada, "ご", True)
    MsgBox Join(Answer2, vbCrLf)
End Sub

・指定文字が含まれている要素を配列に書き出しています
Split関数で要素を区切り文字「vbCrLf」で改行して文字列に書き出しメッセージしています

・膨大な配列の中に検索文字列を含む要素がどのくらいあるかを見るには有効ですね
・でも、一次元配列だけに使えるのがどうも物足りない感じです(あくまで私感(^^;)

2次元配列に何とか適用させてみたいと思います

・まずは、どう処理するのか考えてみます。こんなのはどうでしょうか?
・設定は、Excelの表データ(大きさも少しあるもので)
・指定文字列でフィルタリングして、ヒット要素があった行を配列にしてセルに代入する

フロー図でロジックを検討

セル範囲の2次元配列でFilter関数を使うコード

Join関数Split関数 を使いまわして無理やり感たっぷりですが(^^;

'セル範囲の2次元配列でFilter関数を使ってみる
Sub ArrayFilterSample()
    Dim db As Variant   'セルデ-タ取得用
    Dim sr As Long      '開始行
    Dim sc As Long      '開始列
    Dim er As Long      '最終行
    Dim ec As Long      '最終列
    Dim i As Long, j As Long 'ループ用
    Dim rng As Range   'セル選択用
    
    'Application.InputBoxで対象表を選択させる
    On Error Resume Next
    Set rng = Application.InputBox( _
            Prompt:="表の先頭(左上)セルを1つ選択してください", _
            Title:="セル選択", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    rng.Worksheet.Activate
    sr = rng.Row        '開始行取得
    sc = rng.Column     '開始列取得
    Set rng = Nothing
    '取得セル範囲を2次元配列に一括代入する
    db = Cells(sr, sc).CurrentRegion.Value
    er = UBound(db, 1)   '1次元の最大値(最終行)
    ec = UBound(db, 2)   '2次元の最大値(最終列)
    
    ReDim c(sc To ec) As String
    ReDim r(sr To er) As String
    Dim str As String
    Dim ar As Variant
    Dim rn As Variant   'セル選択用
    '2次元配列をJoin関数で行ごとの1次元配列にする
    For i = sr To er
        For j = sc To ec
            c(j) = db(i, j) '1次←2次元
        Next
        r(i) = Join(c, "|") '行ごとに列のセルデータを連結
    Next
    str = Join(r, ",")      '各行毎のデータをを全て連結(文字列)
    rn = Split(str, ",")    '文字列を一次元配列へ
    'ここでフィルタリングする。指定文字はRange("H2")の値
    ar = Filter(rn, Range("H2"), True, vbTextCompare)
    For i = 0 To UBound(ar)
        'フィルタリングしたデータをセルに書き出す
        Range(Cells(i + 1, 10), Cells(i + 1, 10 + ec - 1)) = _
                                    Split(ar(i), "|")
    Next
    '抽出した件数をセルに表示
    Range("H3").Value = "抽出件数= " & i - 1 & " 件"
    
End Sub

・Range(“H2”)セルを使ってフィルタする文字列を設定しています
・フロー図とコード内のコメントで大体お分かりいただけると思います
・実行させた動作状況は次の画像とおりです
黄色いセルの表データ数は「11486件」ありました。結構動作速いでしょ(^^ゞ

・今回は、分かりやすいようにセル範囲データを使っています
見本では「2020/10」のデータ抽出です。同じようにして違う月データを抽出できます

スポンサーリンク

まとめ(おわりに)

・いかがでしたでしょうか?
「いつも汎用でだれでも使えて活用できるように考えてvbaを使う」というポリシーを念頭に今回の記事も書いたつもりです
実行例のイメージが掴みづらいので「GIF画像」を配置しました
Filter 関数は便利ですが、一次元配列にしか使えないので活用範囲は限定的だと思っていました
・こんな風に使ってみたら使えるかな、という軽い気持ちでやってみました
サンプルファイルを用意していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

何回も「Join」と「Split」などで変換を繰り返しているので、分かりにくいですが勉強にはなったと思います(^^)
今回の解説はいかがでしたか?

はい! すごく勉強になりました(^^)
気になったのは、データが大きいと文字列のString型は大丈夫なんでしょうか? 

くるみこ
くるみこ

String型はVBAのリファレンスで確認してみると、「最大で約 20 億文字(半角)」を格納でるそうですよ(^^
次回は何にするかまだ検討中です。少し待っていてね!

【今回分かったことは】
・Filter関数の使い方がわかりました。また、一次元配列限定ということがわかりました
・2次元配列をフィルタリングしたい場合、データ型に注意しながらJoin関数Split関数を駆使すれば処理できることがわかりました

【お決まりの注意事項】
マクロ(VBA)を実行する際は必ずバックアップを取ってから行ってください!
・マクロ(VBA)は実行後にファイルを保存すると元に戻すことはできません!
・実行後にファイルを保存せず終了すれば、実行前に戻すことができます!


ブログランキングに参加しています(^^) 応援よろしくお願いしますm(_ _)m

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてvbaを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう現在検討中です
・その他雑記的に「小ネタなどいろいろ」・・・・・
・今後の記事にもご期待ください(^^)/

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください

スポンサーリンク

スポンサーリンク