FILTER関数を使う

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

VBAからWorksheetFunctionを使って「FILTER関数」を活用するときのポイントを解説します。なお、FILTER関数の詳細な仕様や、動的配列数式などに関しては、下記ページをご覧ください。

Excel 2016レビュー - [Excelの使い方が激変する「スピル」]
VBA Tips - [動的配列数式の操作]
この関数はこう使え - [FILTER関数]

注意！

はじめにお断りしておきます。VBAからWorksheetFunctionでFILTER関数を使うのは超絶難しいです。"難しい"というか、ほとんどの人が知らない技術を使います。これ、ごくたま～にネットで使っているコードを散見しますが、おそらくご本人も分かっていないだろうと思います。とりあえず下記で解説しますけど、くれぐれも知ったからといって「おお、これは便利！さっそく他のマクロでも活用しよう」などとは考えないでください。できれば他では使わないでください。理由は明白です。ほとんどの人は、この使い方を知らないからです。ちゃんと解説している書籍やWebは見たことがありません。第三者が理解できないようなコードは、絶対に書いてはいけません。だから他で使っちゃダメですよ。これ、熱湯風呂の「押すなよ、絶対に押すなよ」というフリじゃないですからね。頼みますよ、マジで。

絞り込むときの問題点

まずは、下図のような表を例にします。

テーブル形式にしています。テーブルの名前は「Data」です。

ワークシート上でFILTER関数を使うのなら、次のような感じです。

これを、WorksheetFunctionから呼び出すのですから、次のような考え方になります。

Sub Macro1()
    Dim A
    A = WorksheetFunction.Filter(Range("Data"), Range("Data[名前]") = "田中")
End Sub

考え方に間違いはありませんが、実行するとエラーになります。

考え方は正しいけどエラーになる原因は、FILTER関数の条件に指定している「Range("Data[名前]") = "田中"」部分です。これ、ワークシート上でしたら、動的配列数式とみなされてスピります。

上図で選択している「Data[名前]="田中"」部分は、下図のような2次元配列として評価されています。

動的配列数式というのは、セル内だけの動作です。VBAのコードではスピりません。まったく事情が異なるのですから。マクロの中で、A列の文字列が"田中"かどうかを判定し、自分でTrueとFalseの2次元配列を作れば、まぁ動くっちゃ動きますけど、そんなことをするのなら、何もFILTER関数を使う必要がありません。そのままデータを編集なり加工なりしちゃえばいいんです。

さあ困りました。もちろん「Data[名前]="田中"」という数式を、どこかのセルに代入し、その結果を使うという手もありますけど、ちょっと美しくないです。セルの中でしか動作しないものを、何とかVBA内で動作させたいです。何か方法はないだろうか…と考えていて、ふと閃きました。いや、"思い出した"という方が正しいです。これ、私が最後に使ったのは、もう20年くらい前じゃないかな。それは、Evaluateメソッドです。実務で使う機会は、まずないでしょう。

Evaluateメソッドとは

Evaluateは「～を評価する」とか「～の計算結果を求める」という意味の英単語です。Applicationオブジェクト、Worksheetオブジェクト、Chartオブジェクトに用意されています。Evaluateメソッドは、通常セルの中で使用する"式"を評価(計算)して、その結果を返すメソッドです。ちょっと、やってみましょう。

セルC1に「=A1」という参照式が入力されています。この参照式の結果、セルA1が参照されて、セル内の値が表示されています。これを、Evaluateメソッドでやると次のようになります。

Sub Macro2()
    MsgBox Evaluate("A1")
End Sub

もちろん、指定できるのは単純な参照式だけではありません。次のような数式も機能します。

Sub Macro3()
    MsgBox Evaluate("SUM(A1:A3)")
End Sub

SUM関数だけじゃありません。セル内で使用できる数式であれば(すべてじゃありませんが)、ほとんどいけます。

Sub Macro4()
    MsgBox Evaluate("SUMIF(A1:A5,""田中"",C1:C5)")
End Sub

変数も使えます。

Sub Macro5()
    Dim str As String
    str = "C"
    MsgBox Evaluate("INDEX(A1:A5,MATCH(""" & str & """,B1:B5,0))")
End Sub

珍しいところでは、共有範囲の参照演算子(スペース)も使えます。

Sub Macro6()
    MsgBox Evaluate("A3:C3 B1:B5")
End Sub

そして、今回の話題である動的配列数式も、いけます。

Sub Macro7()
    Dim A, i As Long, B As String
    A = Evaluate("A2:A10=""田中""")
    For i = 1 To UBound(A)
        B = B & A(i, 1) & vbCrLf
    Next i
    MsgBox B
End Sub

さて、このEvaluateメソッドには簡便な記述が用意されています。それは、評価する式を角括弧([])で囲む書き方です。したがって、下記の両者は同じ意味になります。

Evaluate("A1")
[A1]

Evaluate("SUM(B1:B4)")
[SUM(B1:B4)]

ちょっと話は横道にそれますが。この[A1]という書き方です。たま～にネットで(特に掲示板の回答で)見かけるのは。VBAでセルを指定するとき、一般的にはRangeやCellsを使います。たとえばRange("A1")とかCells(i, 3)みたいに。セルA1に数値を代入するのでしたら、Range("A1") = 100と書けばいいです。それを、[A1] = 100のように書いて「こんな書き方もできるんですよぉ～」みたいな、"プチ自慢"的な雰囲気が行間から漂うコードを、たま～に見かけます。その方は、この[A1]というのはEvaluateメソッドの簡略記述だと分かっているのでしょうかね。ヒドイのは次のようなコードです。これ、実際に見たことがあります。

[A1] = WorksheetFunction.Sum([B1:B4])

みたいなw お前は、いったい何をやっているんだと。思わずウーロン茶吹きました。セルを指定するときにEvaluateメソッドを使って書いたのなら、なぜ、そもそもSUM関数をEvaluateメソッドにしないのかと。まったく一貫性に欠けます。千歩譲って、これだったら次のように書いた方が、まだポリシーを感じます。

[A1] = [SUM(B1:B4)]

おそらく、[SUM(B1:B4)]ができるということを知らないんでしょうね。なぜなら、[A1]がEvaluateメソッドだと理解していないから。「なんか知らないけど[A1]って書くとセルを指定できるみたい」程度にしか考えていないんでしょうね。そんな「何だかよく分からないけど、動けばいいや」というマクロは最低です。自分でも意味が分からないマクロを後任者に引き継げますか？ちゃんと説明できますか？実務の現場で使うマクロは、作者だけの物ではありません。みんなで使う道具です。みんなで使う共有物です。だったら、何よりも、第三者が理解できるように書かなかったら、道具としての意義がありません。

FILTER関数の続き

すみません、ちょっと熱くなってしまいました。話をFILTER関数に戻しましょう。今回のFILTER関数で、条件のところには、このEvaluateメソッドを使ってやります。

Sub Macro8()
    Dim A
    A = WorksheetFunction.Filter(Range("Data"), Evaluate("Data[名前]=""田中"""))
    Range("E7").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub

このEvaluateメソッドでは文字列結合をしていませんから、次のようにも書けます。

Sub Macro9()
    Dim A
    A = WorksheetFunction.Filter(Range("Data"), [Data[名前]="田中"])
    Range("E7").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub

複数の条件を指定するときは書き方が難しいです。複数条件を指定する件については「FILTER関数」をご覧ください。FILTER関数で複数の条件を指定するとき、それぞれの条件を括弧()で囲まなければなりません。

Sub Macro10()
    Dim A
    A = WorksheetFunction.Filter(Range("Data"), Evaluate("(Data[名前]=""田中"") * (Data[記号]=""A"")"))
    Range("E7").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub

Sub Macro11()
    Dim A
    A = WorksheetFunction.Filter(Range("Data"), [(Data[名前]="田中") * (Data[記号]="A")])
    Range("E7").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub

さて、最後に。FILTER関数で絞り込んだ結果の、特定列だけを操作するには、どうしたらいいでしょう。もちろん、2次元配列内をループして、ひとつずつ値を取り出せばできますが、ちょっとその発想は美しくないです。ここは、何とか"列単位"で一気に取得してみましょう。ちなみに、ワークシート上でしたらXLOOKUP関数と組み合わせることで、簡単に実現できます。

ただ、残念ながら、少なくとも本稿執筆時点(2019年9月)では、WorksheetFunctionでXLOOKUP関数を呼び出せません。これ、将来はできるようになるのかな？なんか、無理っぽい気がしますね～個人的には。特に根拠はありませんけど。すでにInsiderで公開しているこのタイミングで"できない"ってことは、何か"できない"事情があるのではないかと。もし"できる"のなら、最初からそうなっている状態で公開すればいいし。後になって「やっとできるようになりました～お待たせしました～テヘッ」っていうのは、ちょっと恥ずかしいでしょ。天下のMicrosoftとしては。まぁ、将来呼び出せるようになったら、それはそれで嬉しいですけど(2019/11/11 追記▼)。さてさて、XLOOKUP関数をWorksheetFunctionで呼び出せないのですから、これはもう、全部をEvaluateメソッドでやっちゃうしかありませんね。

Sub Macro13()
    Dim A, i As Long, B As String
    A = [XLOOKUP("数値",Data[#Headers],FILTER(Data,Data[名前]="田中"))]
    For i = 1 To UBound(A)
        B = B & A(i, 1) & vbCrLf
    Next i
    MsgBox B
End Sub

注意しなければいけないのは、テーブルの特殊項目指定子です。これ、ワークシート上でしたら[#すべて]とか[#見出し]のように日本語を使えますが、VBAから指定するときは[#All]とか[#Headers]などのような英語表記にしなければ動作しません。このへんに関しては「VBAでテーブルの操作」をご覧ください。

(2019/11/11 追記)

朗報です！WorksheetFunctionからXLOOKUP関数を呼び出せるようになりました。なんか、ディスっちゃって、すみません＞Microsoft

ちなみに、バージョン1912(ビルド 12231.20000)で確認しました。

いやぁ～WorksheetFunctionでXLOOKUP関数を呼び出せるってことは、VBAで"列単位の取得"が、超簡単になるってことです。これは嬉しい！今夜は祝杯をあげます。