FILTER関数は、定義した条件に基づいてデータ範囲を
フィルター処理した結果を返します。
FILTER関数はスピルで登場した新しい関数です。
FILTER関数の書式
=FILTER(配列,含む,[空の場合])
配列
必須です。
フィルター処理する元データを指定します。
セル範囲または配列です。
含む
必須です。
フィルター条件を指定します。
元データの配列の縦または横の大きさと同じ
真偽値(TRUE,FALSE)の1次元配列を指定します。
空の場合
省略可能。
フィルター結果が空の場合に表示する値を指定します。
フィルター結果が空の場合にこの引数を
省略していると、#CALC!となります。
FILTER関数使用例のサンプルデータ
使い道の広い関数です。
配列を意識して使いこなすと、かなり便利なことができます。
以下のFILTER関数使用例で使う表は以下になります。
例題にした個人情報です。50件用意しました。
以下では、上表を元にFILTER関数を使用した場合を例示しています。
FILTER関数の基本
数値でフィルター
30歳未満でフィルター
=FILTER(A2:E51,C2:C51<30)
文字でフィルター
"東京都"でフィルター
=FILTER(A2:E51,E2:E51="東京都")
フィルター結果が0件の場合
フィルター結果が0件の場合に、引数「空の場合」を
省略していると、#CALC!のエラーとなります。
引数「空の場合」を適宜指定します。
空白セルを0ではなく空白にする場合
エクセル関数全般での問題ですが、
空白セルを参照すると0になってしまいます。
=FILTER(A2:E51,B2:B51="女")
空白セルを空白にしたい場合、いろいろな方法がありますが、
&""を付け加える方法が最も簡単だと思います。
=FILTER(A2:E51,B2:B51="女")&""
複数条件のフィルター
AND条件
"女" AND "東京都"でフィルター
=FILTER(A2:E51,(B2:B51="女")*(E2:E51="東京都"))
AND条件は、*演算子を使います。
引数「含む」には、真偽値(TRUE,FALSE)の配列を指定するので、
個々の真偽値の掛け算がAND条件になります。
AND関数は使えません。
OR条件
"女" OR "東京都"でフィルター
=FILTER(A2:E51,(B2:B51="女")+(E2:E51="東京都"))
OR条件は、+演算子を使います。
引数「含む」には、真偽値(TRUE,FALSE)の配列を指定するので、
個々の真偽値の足し算がAND条件になります。
OR関数は使えません。
関数を使ってフィルター
四則演算
ここだけ少し違うデータ例になります。
C列とD列の足し算の結果でフィルターする場合。
=FILTER(A2:E51,C2:C51+D2:D51>100)
論理式として評価できる式であればどんな
四則演算でも構いません。
文字列関数
LEFT,MID,RIGHT等の文字列関数が使えます。
"京都府"と"大阪府"でフィルターしています。
=FILTER(A2:E51,RIGHT(E2:E51)="府")
日付・時刻関数
YEAR,MONTH,DAY等の日付・時刻関数が使えます。
10月生まれでフィルターしています。
=FILTER(A2:E51,MONTH(D2:D51)=10)
ただし、FILTER出力結果の日付の表示形式は
自動では設定されません。
適宜表示形式を設定してください。
関数使用時の注意
引数「含む」の計算結果が1行でもエラーを
含んでいる場合、FILTER関数全体がエラーとなります。
=FILTER(A2:E51,FIND("京",E2:E51)>0)
FIND関数は、検索値が無い場合はエラーとなるため、
上記ではFILTER関数全体がエラーとなっています。
このような場合は、IFERROR関数でくるみます。
=FILTER(A2:E51,IFERROR(FIND("京",E2:E51),0)>0)
したがって、
見出し列を範囲に含めたり列全体で
指定したりする場合は、IFERROR関数が必要になる場合が多くなります。
横（列）でフィルター
ここまで、縦のデータによってフィルターしましたが、
横（列）のデータによってフィルターすることもできます。
=FILTER(A1:E51,A1:E1="年齢")
ある特定の文字列を含む見出し列で
フィルターするといった使い方ができます。
しかし実務的には、この機能だけを単発で
使う事は少ないように思います。
表示する列を選択する
引数「含む」は真偽値(TRUE,FALSE)の配列を指定するものです。
1次元の配列であれば、縦・横どちらでも受け付けてくれます。
配列リテラルの書き方
縦の配列
{}の中に;セミコロンで区切って各要素を入れます。
{1;2;3}
| 1 |
| 2 |
| 3 |
横の配列
{}の中に,カンマで区切って各要素を入れます。
{1,2,3}
| 1 | 2 | 3 |
そこで、ここまでの例で示した「含む」のほとんどは
縦の配列であることを理解してください。
E2:E51="東京都"
これは、
{FALSE;FALSE;…;TRUE,FALSE;…}
行数分の縦の配列になります、
条件に合致した行はTRUE、それ以外はFALSEです。
そして、TRUEは1、FALSEは0として代用できます。
そこで、「含む」に直接この配列を指定してみましょう。
「含む」の行数は元配列と同じ行数に
しなければならないので、ここでは5行だけにしました。
=FILTER(A2:E6,{1;0;1;0;1})
「含む」に指定した配列の1(TRUE)の行だけが出力されました。
とはいえ、このような使い方をすることはまずないでしょう。
では、横の配列を指定してみましょう
=FILTER(A2:E6,{1,0,1,0,1})
見事に元範囲の1,3,5列だけが出力されました。
これは使えそうです。
FILTER関数の結果をFILTERすれば、
条件で絞り込んだ後に必要な列だけに絞り込めます。
FILTER関数をネストして表示する列を選択
=FILTER(FILTER(A2:E51,C2:C51<30),{1,0,1,0,0})
外側のFILTER関数に指定した配列通りの列のみ出力されました。
これを使えば、元表から欲しい列だけにすることができます。
注意点としては、「含む」に指定する
配列の大きさは、元表の列数に必ず合わせることです。
表示する列の選択を自動化する
配列{1,0,1,0,0}を自動的に作成すれば良いだけです。
FILTER関数を入れる上の行に事前に見出し文字列を入れておくことで、
配列{1,0,1,0,0}を見出し文字列から自動生成します。
=COUNTIF(G1:H1,A1:E1)
この数式はスピルして、
| 1 | 0 | 1 | 0 | 0 |
このようになりますので、これをそのま引数に指定できます。
=FILTER(FILTER(A2:E51,C2:C51<30),COUNTIF(G1:H1,A1:E1))
FILTER関数の結果を他の関数で使う
FILTER関数が返すものは配列です。
したがって、配列を受け入れる関数の引数として使う事が出来ます。
"東京都"の"男"の平均年齢を出して見ましょう。
もちろん、AVERAGEIFSで簡単に求められます。
=AVERAGEIFS(C2:C51,B2:B51,"男",E2:E51,"東京都")
あくまで、ここまでの総復習としてやってみましょう。
=AVERAGE(FILTER(FILTER(A2:E51,(B2:B51="男")*
(E2:E51="東京都")),COUNTIF(C1,A1:E1)))
この数式は解読してみてください。
ここまでに説明してきたことを組み合わせて
使っているだけです。
この数式が読めれば、FILTER関数はほぼ確実に
理解できたと言えるでしょう。
スピル導入によって、エクセルの使い方が
大きく変わろうとしています。
FILTER関数は、その中核となる関数と言えると思います。
