FILTER 関数

  1. Home
  2. Excel
  3. 機能と数式
  4. ワークシート関数

ここで解説する FILTER関数 は、Office InsiderのProPlusに追加された関数です。執筆時点(2019年8月)で、製品版のExcelにはまだ実装されていません。ただし、Microsoftがこの関数を実装すると公にアナウンスしたのは、2018年の11月です。おそらく現在は、さまざまな動作検証をしたり、細かい修正などを行っているところでしょうけど、近い将来Excelに実装されるのは間違いありません。そうなったら、もうExcelの使い方が大きく変わります。そんな画期的な関数です。なお、Office Insiderに関しては、ご自身でググってください。また、この関数では「スピル」という機能が使われています。スピルに関しては「Excel 2016レビュー[Excelの使い方が激変する「スピル」]」をご覧ください。

FILTER関数

リストに対して条件を指定して絞り込んだ結果を返す関数です。できることは、オートフィルタ機能とは異なります。オートフィルタではできることがFILTER関数ではできなかったり、反対にオートフィルタではできないことがFILTER関数で可能だったりします。書式は下図のとおりです。

分かりにくいので、次のように考えるといいでしょう。

引数「範囲」には、フィルタをかける元データの範囲を指定します。SORT関数と同じように、タイトル行は含めません。元データをテーブル形式にしておくと、データの増減にも自動的に対応するので便利です。

引数「条件」には、どの列に、どんな条件を指定するかを記述します。たとえば「B2:B11="田中"」とか「C2:C11>100」などのように指定します。

もし「範囲」内で「条件」に該当するセルが1つも存在しなかったとき、FILTER関数は「#CALC!」エラーになります。そうではなく、該当するセルが1つも存在しないときに返す文字列を、引数「空の場合」に指定します。ここに「""」を指定すれば、該当するセルが1つもなかったとき「#CALC!」エラーではなく空欄が表示されます。なお、引数「空の場合」は省略可能です。

文字列で絞り込む

さっそく、やってみましょう。元データはテーブル形式にしています。名前は「テーブル1」です。

意外と簡単ですね。では、B列を「"田"で始まる」で絞り込んでみましょう。ワイルドカードを使います。

おやおや、ワイルドカードは使えないようです。では「"田"で始まる」や「"田"を含む」などの条件は指定できないのでしょうか。ここは発想を変えます。FILTER関数は、引数「条件」がTRUEになれば、条件に一致したとみなされます。ここで「スピル」や「動的配列数式」の知識が必要になります。「スピル」や「動的配列数式」に関しては「Excel 2016レビュー - Excelの使い方が激変する「スピル」」を、ご覧ください。ここでは、次のようなデータで考えてみましょう。

A列に入力されている文字列のうち、左1文字を抜き出すLEFT関数を、B列に入力してみます。

このLEFT関数の結果が"田"と等しい、ということなのですから、次のようになります。

したがって「"田"で始まる」ということは、セルに入力されている文字列の"左1文字"が"田"と等しい、ということですから、次のように考えられます。

もちろん「"田"で終わる」でしたら、RIGHT関数です。

ちょっと難しいのが「"田"を含む」です。VBAでしたらInStr関数で調べます。ここはワークシートですからFIND関数かSEARCH関数です。どちらでも同じですから、ここはFIND関数でいきましょう。FIND関数の結果が0より大きかったら、どこかに"田"が存在しているということです。しかし、困ったことにFIND関数は、見つからなかったときエラーになります。数式の一部がエラーになると、その数式全体がエラーになってしまいますので、FIND関数のエラーをIFERROR関数で回避します。

引数「条件」で、別の関数を使ってTRUEかFALSEを判定するという仕組みは、発想しだいでいろいろなことができます。たとえば、LEN関数を使えば、文字数で絞り込むことも可能です。

FILTER関数を使いこなすには、いかに多くの関数を知っているかにかかっていますね。

さて今度は、空欄("")を条件にしてみます。

上図のような状態で、B列が「空欄ではない」で絞り込みます。

これは予想どおりです。問題は「空欄である」の場合です。

このようにFILTER関数は、元データの中に空欄セルがあると、そのセルに0を表示します。これは、VLOOKUP関数と同じですね。対処法も同じです。＆演算子で空欄("")を結合してやればいいのですが…

確かに0は消えますけど、ご覧のように全部文字列になっちゃいます。FILTER関数が結果を返す列単位で日付の書式設定をしたり、列単位で配置を指定しておけば、見た目的には何とかなるでしょう。ただ、ちょっと考え方が美しくありません。これは、そういう仕様だと諦めるしかないでしょうね。

数値で絞り込む

次は、C列の数値に条件を指定して絞り込んでみます。

VBAでオートフィルタを操作するとき、条件には文字列形式を指定するのが基本です。もし「200と等しい」で絞り込むのなら「"200"」とします。しかし、FILTER関数では、数値に条件を指定するときは数値を指定します。もし文字列形式で指定するとエラーになります。

では、表示形式が設定されていたらどうでしょう。

C列には「100」や「200」などの数値が入力されていますが、「0円」という表示形式を設定して"円"を表示しています。この状態でC列を「200円と等しい」で絞り込む場合、VBAだったら次のように"円"まで含めないと上手くいきません。

Range("A1").AutoFilter 3, "200円"

手動操作で行うオートフィルタ機能は、「～と等しい」で絞り込むとき、セルに入力されている値ではなく、セルに表示されている文字列で絞り込みます。そのへんの詳しい話は「VBA Tips - 数値で絞り込む」をご覧ください。では、FILTER関数ではどうでしょう。まずは、VBAと同じ考えで、"見えている文字"を指定してみます。

失敗します。VBAとは違い、FILTER関数はセルに"入力されている値"で絞り込んでいるようです。

実はこの"入力されている値で絞り込む"というのが、ポイントだったりします。

日付で絞り込む

次に、A列の日付で絞り込んでみます。セルA2に「2019/8/1」という日付があります。これで絞り込んでみましょう。

ダメです。一応、文字列で指定してみます。

こちらも失敗します。ここで先ほどの"セルに入力されている値"で絞り込むという原則を思い出しましょう。A列にはシリアル値が入力されています。したがって、日付に条件を指定して絞り込むときは、シリアル値を指定しなければなりません。

だけど、毎回いちいちシリアル値を調べていたら大変です。こんなときは、シリアル値を返す関数を使うといいでしょう。

とか

みたいに。これは、日付の期間を指定するときも同じです。「2019/8/31以降」というつもりで、次のように指定すると失敗します。

ここまでの解説を読んだ方なら、失敗した原因が分かるでしょうけど、いきなりこの結果になったら多くの方が混乱すると思います。だって、エラーにならず、全部が抽出されちゃうのですから。なぜ全部が抽出されたのか、お分かりですね。条件のシリアル値に「2019/8/31」を指定しました。これ、Excelは「2019÷8÷31」を計算して「8.141129032」というシリアル値だと判断したからです。A列に入力されているシリアル値は、すべて「8.141129032」より大きいです。だから全部です。こんなときも関数を使ってください。

では「8月」とか「2日」で絞り込むには、どうしたらいいでしょう。ここでも、文字列で絞り込むときに解説した「動的配列数式」を活用します。

最後に、日付の曜日で絞り込んでみましょう。これ、オートフィルタ機能では不可能です。でも、要するに引数「条件」でTRUEを判定できればいいのですから、たとえば次のようにします。

複数の条件で絞り込む

オートフィルタを複数の条件で絞り込むときは、

  1. 1つの列に複数の条件を指定する
  2. 複数の列に条件を指定する

の、2つの考え方があります。VBAでオートフィルタを操作するとき、これらは区別して行いますが、FILTER関数だったら考え方は同じです。最初は「2列目[名前]が"田中" または "広瀬"である」で絞り込みます。まずは、結果をご覧ください。

もうひとつ、今度は「2列目[名前]が"田中"であり かつ 3列目[数値]が200より大きい」です。

複数の条件を指定するときは、次のようにします。

これ、「または は＋」「かつ は＊」と丸暗記してもいいのですけど、一応仕組みを解説します。よく分からない方は読み飛ばしてください。

Excelでは(というか本当はコンピュータ全般の話ですが)、FALSEは「0」、TRUEは「FALSEでない値」と定義されています(厳密に言うと、"定義されている"のではありませんが、ここでは便宜上そう表現します)。FALSEは0です。しかしTRUEは、100でも－123でも、とにかくFALSE(0)でなかったら、それはTRUEです。しかし、TRUEが「FALSEでない値」だと、TRUEやFALSEを計算するときに困りますから、ワークシート上でTRUEやFALSEを計算するときは、便宜的に「FALSE = 0」「TRUE = 1」として計算を行います。ちなみに、VBAなどプログラミングの世界では、一般的に「False = 0」「True = -1」として計算されます。

まずは「または(＋)」からいきましょう。2つの条件を指定するとき、それぞれの条件がTRUEまたはFALSEになるのですから、考えられる組み合わせは4通りです。

計算の結果がTRUEになるのは、条件1と条件2のうち、少なくともどちらか一方がTRUEのときです。もちろん、両方TRUEであっても結果はTRUEです。これは「条件1がTRUE または 条件2がTRUE」ということですから OR です。

次に「かつ(＊)」を考えてみます。同じように組み合わせは4通りです。

今度は、計算の結果がTRUEになるのは、条件1と条件2の両方がTRUEのときだけです。これは「条件1がTRUE かつ 条件2がTRUE」ですから AND です。

この考え方を使うと、オートフィルタ機能では不可能な、複数の列を「または(OR)」で絞り込むことができます。

VBAからは使えない

新しく追加されたSORT関数やUNIQUE関数などは、VBAからWorksheetFunctionで呼び出せます。これはメチャクチャ助かります。たとえば、次のように。

Sub Macro1()
    Dim A, i As Long
    A = WorksheetFunction.Sort(Range("テーブル1"))
    For i = 1 To UBound(A)
        Debug.Print A(i, 1)
    Next i
End Sub


Sub Macro2()
    Dim A, i As Long
    A = WorksheetFunction.Unique(Range("テーブル1[名前]"))
    For i = 1 To UBound(A)
        Debug.Print A(i, 1)
    Next i
End Sub

しかし、FILTER関数は、VBAのWorksheetFunctionでは使えません。呼び出すことはできるのですが、いくらやってもエラーになります。

もちろん、まったく同じ式で、ワークシート上でしたら成功します。

「なぜだろう？何か間違ってる？」と、しばらく悩んでいたのですが、本稿を書いていて気づきました。VBAで指定した「Range("テーブル1[名前]") = "田中"」部分って、要するに"動的配列数式"です。

この数式がスピらなかったら、条件の判定はできません。ワークシート上にはスピル機能が追加されましたけど、VBAにはスピル機能がありません。スピれないんです。だから条件を判定できません。だからエラーです。まぁ、どぉーーーーしてもっ！って言うのでしたら

Sub Macro4()
    Dim A, B(2, 0) As Boolean
    B(0, 0) = True
    B(1, 0) = False
    B(2, 0) = True
    A = WorksheetFunction.Filter(Range("テーブル1"), B)
End Sub

みたくすれば使えます。実際に試してみました。でも、各セルで1つずつTrueとFalseを判定して配列に入れるんだったら、そんなもん、FILTER関数を使うまでもなく、VBAだけで処理しちゃえばいいです。意味ないです。ちなみに、条件に指定できるのは2次元配列です。1次元配列だと、そもそもエラーになります。なので、条件を自分で作るのは、かなり難しいですし現実的ではありません。ここは「VBAからは使えない」と考えるのが得策です。

(追記 2019/09/05)

FILTER関数をWorksheetFunctionから使う方法を思いつきました。ただ、このやり方、紹介してもいいんだろうか…。裏技とは言わないけど、ほとんどの人が知らない方法です。詳しくはVBAの解説になりますので、ここではなく、VBA Tipsの方に(そのうち)書きます。