エクセル入門
FILTER関数(範囲をフィルター処理)

Excelの初心者向け入門解説
公開日:2020-02-11 最終更新日:2022-08-26

FILTER関数(範囲をフィルター処理)


FILTER関数は、定義した条件に基づいてデータ範囲をフィルター処理した結果を返します。
FILTER関数はスピルで登場した新しい関数です。

・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事

最後の方では、表示する列を選択する方法も掲載しています。

FILTER関数の書式

=FILTER(配列,含む,[空の場合])

配列
必須です。
フィルター処理する元データを指定します。
セル範囲または配列です。

含む
必須です。
フィルター条件を指定します。
元データの配列の縦または横の大きさと同じ真偽値(TRUE,FALSE)の1次元配列を指定します。

空の場合
省略可能。
フィルター結果が空の場合に表示する値を指定します。
フィルター結果が空の場合にこの引数を省略していると、#CALC!となります。

FILTER関数使用例のサンプルデータ

使い道の広い関数です。
配列を意識して使いこなすと、かなり便利なことができます。
以下のFILTER関数使用例で使う表は以下になります。

Excel エクセル FILTER関数
※なんちゃって個人情報です。50件用意しました。

以下では、上表を元にFILTER関数を使用した場合を例示しています。

FILTER関数の基本

数値でフィルター

30歳未満でフィルター

=FILTER(A2:E51,C2:C51<30)

Excel エクセル FILTER関数


文字でフィルター

"東京都"でフィルター

=FILTER(A2:E51,E2:E51="東京都")

Excel エクセル FILTER関数


フィルター結果が0件の場合

フィルター結果が0件の場合に、引数「空の場合」を省略していると、#CALC!のエラーとなります。

Excel エクセル FILTER関数

引数「空の場合」を適宜指定します。

Excel エクセル FILTER関数


空白セルを0ではなく空白にする場合

エクセル関数全般での問題ですが、空白セルを参照すると0になってしまいます。

=FILTER(A2:E51,B2:B51="女")

Excel エクセル FILTER関数

空白セルを空白にしたい場合、いろいろな方法がありますが、
&""を付け加える方法が最も簡単だと思います。

=FILTER(A2:E51,B2:B51="女")&""

Excel エクセル FILTER関数


複数条件のフィルター

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)の配列を指定するので、
個々の真偽値の足し算がOR条件になります。
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出力結果の日付の表示形式は自動では設定されません。
適宜表示形式を設定してください。

Excel エクセル FILTER関数


関数使用時の注意

引数「含む」の計算結果が1行でもエラーを含んでいる場合、FILTER関数全体がエラーとなります。

=FILTER(A2:E51,FIND("京",E2:E51)>0)

Excel エクセル FILTER関数

FIND関数は、検索値が無い場合はエラーとなるため、上記ではFILTER関数全体がエラーとなっています。
このような場合は、IFERROR関数でくるみます。

=FILTER(A2:E51,IFERROR(FIND("京",E2:E51),0)>0)

Excel エクセル FILTER関数

したがって、
見出し列を範囲に含めたり列全体で指定したりする場合は、IFERROR関数が必要になる場合が多くなります。

横(列)でフィルター

ここまで、縦のデータによってフィルターしましたが、
横(列)のデータによってフィルターすることもできます。

=FILTER(A1:E51,A1:E1="年齢")

Excel エクセル FILTER関数

ある特定の文字列を含む見出し列でフィルターするといった使い方ができます。
しかし実務的には、この機能だけを単発で使う事は少ないように思います。
むしろ、この下の布石になります。

表示する列を選択する

引数「含む」は真偽値(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})

Excel エクセル FILTER関数

「含む」に指定した配列の1(TRUE)の行だけが出力されました。
とはいえ、このような使い方をすることはまずないでしょう。
では、横の配列を指定してみましょう

=FILTER(A2:E6,{1,0,1,0,1})

Excel エクセル FILTER関数

見事に元範囲の1,3,5列だけが出力されました。
これは使えそうです。
FILTER関数の結果をFILTERすれば、条件で絞り込んだ後に必要な列だけに絞り込めます。

FILTER関数をネストして表示する列を選択

=FILTER(FILTER(A2:E51,C2:C51<30),{1,0,1,0,0})

Excel エクセル FILTER関数

外側の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))

Excel エクセル FILTER関数


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関数は、その中核となる関数と言えるでしよう。

スピルによって新しく追加された関数

関数名 説明
FILTER フィルターは定義した条件に基づいたデータ範囲です。
SORT 範囲または配列の内容を並べ替えます。
SORTBY 範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。
UNIQUE 一覧表または範囲内から重複データを削除した一覧を返します。
RANDARRAY 0から1までのランダムな数値の配列を返します。
SEQUENCE 1、2、3、4など、配列内の連続した数値の一覧を生成します。
XLOOKUP 範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。
一致が存在しない場合、XLOOKUP は最も近い (概算) 一致を返すことができます。
XMATCH 配列またはセル範囲内の項目の相対的な位置を返します。


ワークシート関数一覧

・文字列関数 ・日付と時刻の関数 ・情報関数 ・論理関数 ・検索/行列関数 ・数学/三角関数 ・統計関数 ・財務関数 ・エンジニアリング関数 ・データベース関数 ・アドイン/オートメーション ・キューブ ・Microsoft Office のサポート

ワークシート関数の一覧(2010以降)
・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021より後の365で追加されたワークシート関数 ・Microsoft 365 Insider ・Microsoft「Office のサポート」



同じテーマ「エクセル入門」の記事

SWITCH関数(複数値での切替)
XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(VLOOKUP関数を拡張した新関数)
XMATCH関数(MATCH関数を拡張した新関数)
LET関数(数式で変数を使う)


新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ