本ページには広告が含まれています。

Excel VBAでスピルする新関数「FILTER」を使う方法

Excel VBAでワークシート関数のFILTERを使う方法

ワークシート関数として新しく登場した「FILTER 関数」は、VBA関数に存在していた「Filter関数」をワークシートでも使えるようにしたものでしょう。ただ同じ動作ではなく「スピル機能」を有することでワークシート上ではVBAでのFilter関数のように配列を意識していなくても結果を展開してくれるのがすごいところです。

スポンサーリンク

はじめに

くるみこ
くるみこ

Evaluateメソッドを使う方法」の後半で、動的配列数式の例としてワークシート関数の「FILTER関数」を使って少しだけ解説しました。今回はその続きです。
以前、VBA関数の「Filter関数」で2次元配列を処理する例を紹介していますが、ワークシート関数の「FILTER関数」で処理する場合どのようになるのかを解説したい思います。

VBA関数の「Filter関数」とVBAで使う場合のワークシート「FILTER関数」の違いということですね。よろしくお願いします(^^)/

【この記事でわかることは】
・VBAでスピルするワークシート関数のFILTERを使う方法
その他、Excelがスピル対応しているかや書き込み先セル範囲を調べる方法など

FILTER(ワークシート関数)を使ったコードを作成

「セル範囲の2次元配列でFilter関数を使うコード」では、Join関数 と Split関数 を使いまわして無理やり感たっぷりのコードを使っていますが、新しいワークシート関数の「FILTER」を使えば2次元配列を意識しなくても簡単に作成できます。

VBA関数の「Filter関数」で2次元配列を処理する例で使用したものと同じデータを使って検証します。

今回の手順は、つぎのように設定していきます。

  1. ワークシート上でFILTER関数を使って同じデータを抽出する数式を作成します。
  2. 作成した数式をEvaluateメソッドを使う方法でそのままVBAに反映させるようにします。
  3. 結果を書き込む前に動的配列の書き込み先セル範囲を事前に調べる処理を追加します。
  4. 書き込み先の選択は、別ブックや別シートの選択にも対応できるようにします。
  5. VBAの実行に際しては、スピルに対応しているExcelバージョンなのかを確認して、処理を分けられるようにします。

まず、1番目のFILTER関数で作成した数式はこちらです

ワークシートでFILTER関数を作成する

この数式で同じ結果を取得できました。=FILTER(A2:F1200,IFERROR(SEARCH(“*“&H2&”*”,C2:C1200),FALSE),”該当なし”)

設定した数式を Evaluateメソッドを使ってVBA上で実行させるようにコードを組み立てていきます。

ワークシート関数の FILTER をVBA上で実行する

実行するExcel がスピルに対応している場合 ワークシート関数の FILTER を使って処理します。

'ワークシート関数のFILTERを使ってみる
Sub wsFuncFILTER_Sample()
    Dim r   '戻り値の動的配列は2次元配列
    Dim sr As Long      '開始行
    Dim er As Long      '最終行
    Dim rng As Range    '元データのセル選択用
    Dim exprng As Range   '書き込みセル選択用
    Dim tgcol As String   '検索列セル範囲アドレス
    Dim param As String   'Evaluateに渡す文字列
    
    'Application.InputBoxで対象表を選択させる
    On Error Resume Next
    Set rng = Application.InputBox( _
            Prompt:="表の先頭(左上)セルを1つ選択してください", _
            Title:="セル選択", Type:=8)
    If rng Is Nothing Then On Error GoTo 0: Exit Sub
    '結果を書き出すセル範囲の左上を選択させる
    Set exprng = Application.InputBox( _
            Prompt:="結果を書き出すセル範囲の左上を選択してください", _
            Title:="セル選択", Type:=8)
    On Error GoTo 0
    If exprng Is Nothing Then Exit Sub
    
Dim t As Single     '処理時間計測用
t = Timer   'ここから処理時間計測開始
    
    Application.ScreenUpdating = False
    rng.Worksheet.Activate
    sr = rng.Row    '開始行取得
    Set rng = rng.CurrentRegion     'データ範囲取得
    er = rng.Rows.Count + sr - 1    '最終行取得
    tgcol = "C1:C" & er             '検索対象列の範囲
    
    'Evaluate に渡すパラメーター作成
    param = "FILTER(" & rng.Address(False, False) & _
        ",IFERROR(SEARCH(""*""&H2&""*""," & tgcol & "),FALSE),""該当なし"")"
    'EvaluateメソッドでFILTER関数の戻り値を取得
    r = Evaluate(param)

    '書き込み範囲の状態を事前に調べる
    exprng.Worksheet.Activate
    Dim msg As String
    Call AddrCheckRange(r, exprng, msg)
    If msg <> "" Then Exit Sub
    
    '戻り値は2次元配列なのでUBoundで範囲をリサイズしてセルに書き込む
    exprng.Resize(UBound(r, 1), UBound(r, 2)) = r
    rng.Worksheet.Activate
    
    'FILTERで抽出した件数をセルに表示
    Range("H3").Value = "抽出件数= " _
        & exprng.Resize(UBound(r, 1), UBound(r, 2)).Rows.Count & " 件"
    Application.ScreenUpdating = True
    
'処理時間をセルに書き込む
Range("H18") = Timer - t
End Sub

コード内にコメントを入れていますが以下、その補足説明です

13~22行目:Application.InputBox(Type:=8)を2回出します。
      1回目で元データ表のセル番地を取得します(rng)
      2回目で書き込み先の先頭セルを指定します(exprng)
※別シート(別ブックを含む)の選択も可能です。

25行目:ここから処理時間計測のため Timer で開始タイムを取得します。

35~36行目:変数 paramに Evaluateメソッドに渡す文字列を代入しています。
※ セルに入力した FILTER関数の数式をもとに作成しています。

38行目:EvaluateメソッドをVBA上で実行し、結果を変数 (r) に入れています。

43行目:書き込み範囲の状態を事前に調べるプロシージャを呼び出しています。
    ※ この部分は次の項でもう少し詳しく説明します。

47行目:FILTER関数で取得した結果データをセルに書き込んでいます。
    ※ 基準セルからリサイズした範囲にデータを一気に書き込んでいます。

51行目:FILTERで抽出した件数をセルに書き込んで表示しています。

56行目:Timer で終了タイムを取得して開始タイムとの差で経過時間を出しています。

書き込み先を事前チェックするプロシージャ

この処理は前回記事「Excel VBA 動的配列の書き込み先セル範囲を事前に調べる」で紹介したものを少しだけ変えて使っています。

'書き込み先アドレスの状況を事前チェックする
Sub AddrCheckRange(ByVal r As Variant, _
                    ByVal exprng As Range, _
                    ByRef msg As String)
    Dim addr As String
    Dim rng As Range
    Dim res As Integer
    
    '書き込み先の範囲アドレスを取得
    addr = exprng.Resize(UBound(r, 1), UBound(r, 2)).Address
    '書き込み範囲のアドレスをRangeオブジェクトにセット
    Set rng = Range(addr)

    'セル範囲内に結合セルがあるかを調べる(TrueまたはNullなら存在有)
    If IsNull(rng.MergeCells) Or rng.MergeCells Then
        msg = "書き込み範囲内に結合セルがあります!" _
            & vbCrLf & "(正しく書き込めない可能性があります)" _
            & vbCrLf
        res = MsgBox(msg & "書き込みに支障があるため処理を中止します!" _
                , vbExclamation)
        Exit Sub
    End If
    
    '空白以外のセルが無いかをチェック
    If WorksheetFunction.CountA(rng) <> 0 Then
        msg = "書き込み範囲に空白以外のセルがあります!" _
            & vbCrLf & " (既存のデータは上書きされます)" _
            & vbCrLf & vbCrLf
    End If
    If msg <> "" Then
        res = MsgBox(msg & "このまま書き込みを実行しますか?" _
                & vbCrLf & "(「はい」=このまま実行、「いいえ」=中止)", _
                vbYesNo + vbExclamation)
        If res = vbYes Then msg = ""
    End If 
End Sub

【変更した部分】
3行目:ByVal exprng As Range で書き込み先セルRangeを引数に追加しました。

15~22行目:結合セルの存在を確認する処理をはじめに行うように変更しています。
      ※「結合セル」があった場合は必ず中止する設定にするためです。

24行目以降は、書き込む範囲内に入力されているデータが無いかをチェックしています。
この部分は、確認無しで上書きして良いのならコードは不要です。削除するかコメントアウトしても良いでしょう。

ワークシート関数の方が圧倒的に速い

それでは、動作確認を行っている動画をご覧ください。ワークシート関数のFILTERとVBA関数のFILTERを交互に実行しています。

初めに、書き出し先のセルが空白の状態からワークシート関数のFILTERを実行します。続いてVBA関数のFILTERを実行し、最後にもう一度ワークシート関数のFILTERを実行しています。その際は書き出し範囲にデータがあるので上書きするかどうか確認メッセージが出ているのがわかると思います。

VBA関数のFILTERで書き込まれるデータは、書式がテキストで書き込まれているのも確認できます。また、時刻もシリアル値になっていることが確認できます。

一方、ワークシート関数のFILTERで書き込んだ場合は、書式は元のままで書き込まれています。

実行速度の比較

動画でも実行速度の差は確認いただけると思います。
動画に表示されている数値は、それぞれ複数回実行した平均値とほぼ同じ数値です。

1200行のデータから158件FILTERで抽出する処理速度です。

【ワークシート関数のFILTER】>>> 0.0625
【VBA関数のFILTER】>>>>>>> 0.59375

速度の差は約10倍です。【ワークシート関数のFILTER】が圧倒的に速しかも綺麗です。

【VBA関数のFILTER】の方が遅いのは、2次元配列ループ処理で書き込むため遅くなっています。

ExcelのBuild番号で処理を分岐する

使用しているExcelがスピルに対応しているかどうかは次のようにビルド番号で判断します。

'Excelのスピル対応状況を確認して処理方法を分岐
Sub FILTER_start()
    'ExcelのBuild番号でスピル対応しているかチェック
    If Application.Build >= 12228.20332 Then
        'スピル対応の場合ワークシート関数の処理へ
        Call wsFuncFILTER_Sample
    Else
        'スピル対応不可の場合のVBA関数の処理へ
        Call ArrayFilterSample
    End If
End Sub

4行目:Application.Build で実行しているExcelのビルド番号を取得します。
   スピルの適用が開始されたBuild番号は「12228.20332」です。
6行目:スピルに対応している場合は、ワークシート関数の処理を実行させます。
9行目:対応していない場合は、VBA関数のFILTERで2次元配列処理の実行です。

VBA関数のFILTERで2次元配列処理を行うVBAのサンプルコードがこちらです。

'セル範囲の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    'セル選択用
    Dim exprng As Range '書き込みセル選択用
    
    'Application.InputBoxで対象表を選択させる
    On Error Resume Next
    Set rng = Application.InputBox( _
            Prompt:="表の先頭(左上)セルを1つ選択してください", _
            Title:="セル選択", Type:=8)
    If rng Is Nothing Then On Error GoTo 0: Exit Sub
    '結果を書き出すセル範囲の左上を選択させる
    Set exprng = Application.InputBox( _
            Prompt:="結果を書き出すセル範囲の左上を選択してください", _
            Title:="セル選択", Type:=8)
    On Error GoTo 0
    If exprng Is Nothing Then Exit Sub
    
Dim t As Single     '処理タイム計測用
t = Timer   'ここから処理時間計測開始
    Application.ScreenUpdating = False
    rng.Worksheet.Activate
    sr = rng.Row        '開始行取得
    sc = rng.Column     '開始列取得
    
    '取得セル範囲を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   'セル選択用
    Dim tgr As Long     '書き込み行用
    Dim tgc As Long     '書き込み列用
    
    '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)
    exprng.Worksheet.Activate
    tgr = exprng.Row
    tgc = exprng.Column
    For i = 0 To UBound(ar)
        'フィルタリングしたデータをセルに書き出す
        Range(Cells(i + tgr, tgc), Cells(i + tgr, tgc + ec - 1)) = _
                                    Split(ar(i), "|")
    Next
    
    '抽出した件数をセルに表示
    rng.Worksheet.Activate
    Range("H3").Value = "抽出件数= " & i & " 件"
    
'処理時間をセルに書き込む
Range("H10") = Timer - t
    Application.ScreenUpdating = True
End Sub

コードの解説は省略します。
下の記事のコードを変更しているだけなので詳しくはリンク先の記事をご覧ください。

 

まとめ(おわりに)

以上、ワークシート関数のFILTERをVBAで使う方法について、VBA関数のFILTERと比較しながら解説してみました。

くるみこ
くるみこ

スピルに対応しているExcelでVBAを使う場合、新しいワークシート関数のFILTERを使ったほうが高速で綺麗にデータを表示できることがわかりましたね。

でも、すべてのPCでスピルに対応できていない現状では、Excelのビルド番号を調べて処理を分岐できるようにするようにしておく必要があるでしょう。

是非ともこの手法は使いこなせるようにしておきましょう(^^)/

今回二つの処理を比べてみて、実行速度や貼り付けられたデータの違いがはっきり判りました。やっぱり、新しいスピル機能は凄いですね(^^)
VBA上でスピルさせるには「Evaluateメソッド」は必須という感じですかね(^^;

★★★ ブログランキング参加中! クリックしてね(^^)/ ★★★

【記事作成にあたって】

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

スポンサーリンク

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

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