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

Excelの初心者向け入門解説
最終更新日:2020-05-01

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


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

2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。

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

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

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 配列またはセル範囲内の項目の相対的な位置を返します。


ワークシート関数一覧

Excelワークシート関数の一覧と解説です、詳細解説ページへのリンクもあります。Excel2007までの関数です。2010以降追加の関数一覧 ワークシート関数の一覧(2010以降) 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 文字列関数…27 日付と時刻の関数…21 情報関数…17 論理関数…7 検索/行列関数…1…

ワークシート関数の一覧(2010以降)
Excelワークシート関数の一覧と解説です。Excel2010以降に追加された関数一覧になります。2007までのワークシート関数一覧ワークシート関数の一覧 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 Excel2010で追加されたワークシート関数 Excel2013で追加されたワークシート関数 関数名 種類と説明 …



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

SWITCH関数(複数値での切替)
XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)
XMATCH関数(範囲から値を検索し一致する相対位置)
LET関数(数式で変数を使う)


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

VBA100本ノック 18本目:名前定義の削除|VBA練習問題100(11月6日)
VBA100本ノック 17本目:重複削除(ユニーク化)|VBA練習問題100(11月6日)
VBA100本ノック 16本目:無駄な改行を削除|VBA練習問題100(11月5日)
VBA100本ノック 15本目:シートの並べ替え|VBA練習問題100(11月4日)
VBA100本ノック 14本目:社外秘シート削除|VBA練習問題100(11月3日)
VBA100本ノック 13本目:文字列の部分フォント|VBA練習問題100(11月1日)
VBA100本ノック 12本目:セル結合を解除|VBA練習問題100(10月31日)
VBA100本ノック 11本目:セル結合の警告|VBA練習問題100(10月30日)
VBA100本ノック 10本目:行の削除|VBA練習問題100(10月29日)
VBA100本ノック 9本目:フィルターコピー|VBA練習問題100(10月28日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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