Excel 条件に一致するデータ一覧を取得(FILTER関数)

2020年5月13日

FILTER関数は2019年に実装されたスピルの関連関数で、

特定の条件に一致するデータの一覧を取得します。

フィルターの関数版に近い働きをします。

この記事では、FILTER関数の使い方や

既存機能(フィルターDGET関数)との比較を紹介します。

仕様

=FILTER(配列,含む,空の場合)
配列省略時の値説明
配列省略不可表示するデータのセル範囲を指定
含む省略不可検索条件を指定
空の場合#CALC!エラー結果が0件の場合の表示を指定。
各引数の詳細

使用例

基本的な使い方

配列・含む

第1引数に元データの範囲を指定し、第2引数に検索条件を範囲で指定します。

下の例ではエリアが関東という抽出条件になります。

基本的なFILTER関数をセルに入力

一つのセルにFILTER関数を設定すると条件の結果の数に合わせて数式が自動拡大されます。

最初に関数を設定したオリジナルのセル以外は灰色のゴーストという状態で編集できません。

関数の入力結果とゴースト

ただし自動拡大先のセルに何らかの値があると#SPILLエラーになります。

拡充先にエラーがある例

これにより数式のコピー・ペーストの必要がなくなり、

数式壊したことに気付かずに使い続けるリスクがなくなります。

更に数式の本体は一つだけのため、料率などを計算に含める場合に絶対参照が不要です。

また類似の既存機能では最大検索結果数を想定して、

数式をセルに事前準備しておく必要がありましたが、(静的)

FILTER関数では一つのセルに設定すれば必要な数だけ

自動的に拡充されます(動的)。これが新機能のスピルです。

空の場合

第3引数にはエラー時の結果をします。

未指定の場合は#CALC!エラーになります。

第3引数が未指定の場合は#CALCエラー

指定した場合はその値が結果となります。

第3引数の指定あり

特定の列のみ取得したい場合

引数2の範囲が引数1に含まれている必要はないため、

引数1に表示したい列のみを指定すれば

特定列のみを取得することが可能です。

引数1にC列のみを指定し、B列の条件でFILTER関数を利用する例
引数1にC列のみを指定し、B列の条件でFILTER関数を利用する例

大小条件(比較演算子)

検索値でなく条件であるため○○以上・□□以下のような比較演算子による指定が可能です。

下の例では人口が500万以上のデータを抽出します。

人口が500万以上のデータを抽出する例

複数条件

○○以上かつ□□以下のような条件も指定可能です。

ANDやORではなく算術演算子を利用します。

条件演算子
かつ*
または+

+演算子でOR(または)

+(または)を利用し100万未満かまたは、1000千万より多いデータを抽出する例です。

100万未満、または1000千万より多いデータを抽出する例

*演算子でAND(かつ)

*(かつ)を利用し100万以上でかつ、1000千万以下のデータを抽出する例です。

100万以上でかつ、1000千万以下のデータを抽出する例

順序が狂うと条件でなく、足し算と掛け算として扱われるため

1つの条件ごとに()で囲む必要があります。

このパターンはFILTER関数を入れ子にしても実現できます。

文字列の部分一致条件

ワイルドカードは利用できませんが、他の関数との併用で部分一致検索を実現可能です。

部分一致:○○を含む(FIND関数)

FIND関数で部分一致検索が可能です。

IFERROR関数も併用しないと、一致しないデータの処理で

エラーしてしまい結果を取得できません。

=FILTER(元データの範囲,IFERROR(FIND(含む文字列,検索条件の範囲)>0,0))
FIND関数で○○を含む条件(岡を含む)
FIND関数で○○を含む条件(“岡"を含む)

前方一致:○○から始まる(LEFT関数)

LEFT関数で前方一致条件が可能です。LEN関数も併用します。

=FILTER(元データの範囲,LEFT(検索条件の範囲,LEN(始まる文字列))=始まる文字列)
LEFT関数で○○から始まる条件("山"から始まる)
LEFT関数で○○から始まる条件(“山"から始まる)

後方一致:○○で終わる(RIGHT関数)

RIGHT関数で後方一致条件が可能です。LEN関数も併用します。

=FILTER(元データの範囲,RIGHT(検索条件の範囲,LEN(終わる文字列))=終わる文字列)
LEFT関数で○○から終わる条件("府"で終わる)
LEFT関数で○○から終わる条件(“府"で終わる)

日時の条件(日付/時刻関数)

シリアル値そのものを条件にすることもできますが、

扱いづらく数式を読んでも意味不明になります。

よってセルに入力した日付を条件にするか日付/時刻関数を併用しましょう。

例えばDATE関数を使用すればシリアル値を使わずに日付比較を行うことが可能です。

(セル指定でもよいですが)

DATE関数で日付比較をすう例

DAY関数を併用すれば毎月の特定日の指定が可能です。

DAY関数を併用すれば毎月の特定日の指定する例

併用する日付関数の一覧と想定される利用ケースは以下の通りです。

関数利用ケース
DATE日付を指定(年月日の数値からシリアル値を取得)
DATEVALUE日付を指定(年月日の文字列からシリアル値を取得)
TIME時刻を指定(時分秒の数値からシリアル値を取得)
TIMEVALUE時刻を指定(時分秒の文字列からシリアル値を取得)
TODAY現在日付を指定
Now現在日時を指定
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
特定の年、月、日、時、分、秒を指定
WEEKDAY特定の曜日を指定
WORKDAY営業日・稼働日を指定
EOMONTH月末日付を指定

結果を他の関数に利用

SORT関数・SORTBY関数で並び替え

FILTER関数の結果はセル範囲であるため

セル範囲を引数とする他の関数に結果を受け渡すことが可能です。

例えばFILTER関数の結果をSORT関数で並び替えるといったことが可能です。

例ではSORT関数ですが、SORTBY関数でも同じことが可能です。

FILTER関数の結果をSORT関数で人口の昇順に並び替える例
FILTER関数の結果をSORT関数で人口の昇順に並び替える例

ROWS関数で結果の件数を取得

FILTER関数の結果をROWS関数に指定すると条件に一致する件数になります。

ROWS関数でFILTER関数の結果の件数を取得する例
ROWS関数でFILTER関数の結果の件数を取得する例

COUNT系の関数では列まで数えてしまうので結果件数とは一致しない場合があります。

COUNT関数では数値のみなため、たまたま3件と一致するがCOUNTA関数だと全セルを数えて9件)

既存機能との比較

フィルタリングや検索を行う類似機能と比べて、どのような違いがあるかを紹介します。

フィルター

従来、データのフィルタリングを行う場合は

フィルターが選択肢でしたが、そこにFILTER関数が追加されました。

フィルターはデータを更新した場合は毎回、条件を設定する必要があるため

日次などのルーチンワークには煩わしさがありましたが、

FILTER関数によって解消が見込めます。

逆に非定型作業ではこれまで通り、フィルターを使う方が取り回しが良いでしょう。

ただしOffice365や2019以降でしかFILTER関数は使えません。

項目フィルターFILTER関数
使いやすさ〇 画面操作が優秀で日付も扱える。× 数式が複雑で日付もシリアル値を意識する必要がある。
柔軟性× フィルター条件が画面操作の範囲内に限られる。〇 数式次第でどのような条件も設定可能。
ルーチンワーク× 毎回、手作業で設定が必要。〇 数式を作成すれば流用可能。
非定型業務〇 調査業務など様々な条件でフィルタリングを繰り返す場合に向く。× 数式を作り変える必要があるため、様々な条件でフィルタリングしたい場合には劣る。
Excelのバージョン〇 古いバージョンでも可能。× Office365や2019以降の新しいバージョンのみ。

DGET関数

DGET関数は似たような機能の関数ですが、こちらは検索結果が一つのみです。

FILTER関数の方が理解しやすい仕様になっているので

バージョンなどの理由がなければFILTER関数の方が優秀です。

関連記事

Excel 関数の仕様と使い方の記事一覧