Excelで、こんなお悩みを持ったことはないでしょうか？
・特定の条件に合うデータだけを自動で抽出したい
・毎回フィルターを手動で設定するのが面倒。。。
そんなときに便利なのが、Excelの「FILTER関数」です。
FILTER関数を使えば、条件に合うデータをリアルタイムで抽出でき、作業の効率がグッと上がります。
この記事では、FILTER関数の基本的な使い方や、他関数と組み合わせた便利な使い方を画像付きで分かりやすく解説します。
- FILTER関数とは？オートフィルターとの違いを解説
- 単一/複数条件でも簡単抽出！FILTER関数の基本的な使い方
- エラー対策もバッチリ！条件に合うデータがない場合のエラーメッセージ設定方法
- FINDやSORT関数、BYROW関数など、他関数との組み合わせでもっとFILTER関数を活用する方法
FILTER関数って何？
FILTER関数は、指定した範囲の中から特定の条件に一致するデータを抽出します。
従来のフィルター操作より柔軟で、リアルタイムにデータが更新されるのが特長です。
オートーフィルターのように手動で操作する必要がなく、関数だけで動的に抽出されるため、様々な場面で活躍する関数となります。
ではさっそく、FILTER関数の中身を見ていきましょう。
FILTER関数の書式と基本構文
FILTER関数は、以下のように記述します。
=FILTER(array,include,[if_empty]それぞれの説明は以下の通りです。
| 引数 | 説明 |
|---|---|
| array | 抽出したいデータの範囲 |
| include | 抽出条件を指定する論理式 |
| if_empty | 条件に一致するデータがなかった場合に表示する値 ※省略可能 |
「array」はそのまま抽出したい範囲を選択、「include」はフィルターをかけたい条件を指定することで、自動的に対象データを抽出することができます。
オートフィルターとの違い（自動更新が強み）
Excelには、「オートフィルター」という便利な機能がついています。
下の画像にある三角マークのようなやつですね。Excelを使っている方にとってはおなじみかと思います。
オートフィルターでもいいんじゃない？と思われる方もいらっしゃると思いますが、比較してみると多くの違いがあります。
オートフィルターとの違いを比較表にまとめてみました。
| 項目 | FILTER関数 | オートフィルター |
|---|---|---|
| 自動更新 | ○（動的配列） | ×（手動で更新） |
| 複数条件 | ○（AND/OR可能） | △（複雑条件は不便） |
| 表示形式 | 別セルに表示 | 元データを非表示 |
| 他シート反映 | ○（関数でOK） | △（コピー必要） |
| バージョン | オフィス365/2021以降 | 全バージョン |
大きく違うのは、FILTER関数で表示するデータは大元のデータと異なること、条件を設定しておけば手動でフィルタリングしなくても自動更新されるという点ですね。
この表で見ると、FILTER関数を使うメリットは様々あることが分かると思います。
では次に、FILTER関数の使い方を見ていきましょう。
FILTER関数の基本的な使い方
こちらでは、FILTER関数の基本的な使い方を紹介していきます。
今回以下のようなサンプルデータを用意しました。
このデータを元に、FILTER関数で条件を絞っていきたいと思います。
■今回使用する表はこちら↓↓
一つの条件でフィルターをかける場合
まずは、一つの条件でフィルターをかけるケースを説明します。
今回は、以下データより「部署」が「営業部」であるデータを抽出していきます。
この場合、FILTER関数を以下のように入力します。
=FILTER(FILTER_データ!A2:E13,FILTER_データ!C2:C13="営業部")これで、「営業部」のデータだけ抽出することができます。
複数の条件でフィルターをかける場合（AND条件）
つぎに、複数条件でフィルターをかけるケースを説明します。AND条件なので、すべての条件を満たすデータのみ抽出することができます。
以下データより「部署」が「総務部」、且つ「勤続年数」が「5年以上」であるデータを抽出していきます。
複数条件を全て満たすデータを抽出する場合、乗算演算子「*」を使用します。（掛け算で使用する記号ですね）
=FILTER(FILTER_データ!A2:E13,(FILTER_データ!C2:C13="総務部")*(FILTER_データ!D2:D13>=5))これで、「総務部」且つ勤続年数が「5年」以上のデータを抽出することができます。
複数の条件でフィルターをかける場合（OR条件）
同じく複数条件でフィルターをかけるケースですが、今回はOR条件で抽出するパターンです。いずれかの条件を満たすデータのみ抽出することができます。
以下データより「部署」が「総務部」、または「勤続年数」が「5年以上」であるデータを抽出していきます。
いずれかの条件を満たすデータを抽出する場合、加算演算子「+」を使用します。
=FILTER(FILTER_データ!A2:E13,(FILTER_データ!C2:C13="総務部")+(FILTER_データ!D2:D13>=5))これで、「総務部」または勤続年数が「5年」以上のデータを抽出することができます。
該当するデータがない場合のメッセージ設定（第3引数）
条件に合致するデータがないとエラーが発生しますが、その場合にエラーメッセージを表示することもできます。
以下データより、「勤続年数」が「11年以上」であるデータを抽出できるか確認してみます。
データの中には、勤続年数が「11年以上」のデータはないため、エラーが発生します。
そこで、エラーが発生した場合に「対象データがありません」とエラーメッセージを表示するように設定します。
=FILTER(FILTER_データ!A2:E13,FILTER_データ!D2:D13>=11,"対象データがありません")これで、対象データがない場合に、「対象データがありません」とエラーメッセージを表示させることができます。
- [IF_EMPTY]欄に記述がないと、「#CALC!」というエラーが表示されます。
FILTER関数の応用技（実務で差がつく使い方）
ここまでFILTER関数の基本的な使い方を見てきましたが、便利な関数であることがご理解いただけたと思います。
しかし、FILTER関数は他の関数と組み合わせることで、さらに便利に活用することができます。
こちらでは、
・FIND関数との組み合わせで、あいまい検索を実行する方法
・SORT関数との組み合わせで、データを並び替えて抽出する方法
・BYROW関数との組み合わせで、空白行を除外する方法
を紹介します。
あいまい検索・部分一致でフィルターをかける場合（FIND関数）
この単語が入っているデータを抽出したいというとき、結構あると思います。
しかしFILTER関数の場合、どうやらワイルドカード（*）が使えないようです。
しかしFIND関数を使うことで、あいまい条件でフィルターをかけることが可能です。
以下データより、「部署」に「務」があるデータを抽出できるか確認してみます。
抽出条件を入力する箇所に、FIND関数で条件を指定します。
=FILTER(FILTER_データ!A2:E13,IFERROR(FIND("務",FILTER_データ!C2:C13),0))これで、部署に「務」を含むデータを抽出することができます。
※FIND関数って何？
→対象の範囲から、特定の文字列があるかどうかを調べることができる関数です。
（対象の文字列がある場合、対象文字列の開始位置を返します）
=FIND(検索文字列,対象,[開始位置])それぞれの説明は以下の通りです。
| 引数 | 説明 |
|---|---|
| 検索文字列 | 検索したい文字列を指定 |
| 対象 | 検索したい文字列がある範囲を指定 |
| 開始位置 | 「対象」の文字列で、何文字目から検索するかを決めたい場合に指定 ※省略可能 |
例）「営業部」という文字列で「業」は2番目にある文字列のため、「2」が結果として返ってきます。
SORT関数と組み合わせ、並び替えて抽出することも可能
SORT関数と組み合わせることで、抽出したデータを並び替えることも可能です。
以下データより、「雇用形態」が「正社員」であるデータを抽出し、「勤続年数」が多い順に並び替えできるか確認してみます。
正社員でフィルターをかけた数式を、SORT関数で囲うことで順番を並び替えることができます。
=SORT(FILTER(FILTER_データ!A2:E13,FILTER_データ!E2:E13="正社員"),4,-1)SORT関数については補足で詳細を記載していますが、今回は
・「4」→4列目（=勤続年数）
・「-1」→降順（上から数が大きい順番に並び替える）
※昇順（上から数が小さい順番に並び替える）の場合、「1」もしくは引数自体を省略
で並び替えをしています。
ちゃんと「勤続年数」が多い順に並び替えできていることが確認できます。
※SORT関数って何？
→SORT関数とは、指定した範囲または配列の順番を並び替えします。
=SORT(array,[sort_index],[sort_order],[by_col])それぞれの説明は以下の通りです。
| 引数 | 説明 |
|---|---|
| array | 並び替えしたいデータの範囲 |
| sort_index | 並び替えの基準となる行または列を示す数値 ※省略可能 |
| sort_order | 目的の並び替え順序を示す数値 ※省略可能 ・昇順の場合は1（規定値） ・降順の場合は-1 |
| by_col | 目的の並び替え方向を示す論理値 ※省略可能 ・FALSEの場合は「行」で並び替え（規定値） ・TRUEの場合は「列」で並び替え |
BYROW関数で空白行を除外する方法【新機能】
BYROW関数と組み合わせることで、空白がある行を除外することが可能です。
BYROW関数は2022年9月ごろに登場した比較的新しい関数で、Microsoft365で活用できます。
FILTER関数のみでは、1つの列だけであれば空白行を除外して表示することができます。
しかし、以下のように空白が複数列にまたがる場合、FILTER関数だけではすべての空白行を除外することができません。
では、このように複数列に空白がまたがっている場合、どうしたら空白を含む全ての行を除外できるか？
そのときに使用するのがBYROW関数です。
BYROW関数を使うことで、以下のように空白が複数列にあっても、空白を含むすべての行を除外することができます。
今回はそれを実現するために、以下の計算式を入力しています。
=FILTER(A2:E5,BYROW(A2:E5,LAMBDA(r,COUNTBLANK(r)=0)))見慣れない関数もあるので、この計算式を解説していきます。
まず、BYROW関数の構文は以下の通りです。
=BYROW(array,lambda(row))arrayは文字通り「配列」を意味しますので、対象範囲を指定してあげればOKです。
しかしそのあとにある謎の単語「lambda」。。あまり聞きなれないという人も多いと思いますが、BYROW関数は、この「LAMBDA」を各行に適用することで、必要な結果の配列を返す関数です。
ではこの「LAMBDA」関数はいったい何なのか？
一言でいうと、Excel上で自作の関数を作れる関数（仕組み）です。
LAMBDA関数の基本構文と説明は以下の通りです。
=LAMBDA([parameter1, parameter2, …,], calculation)| 引数 | 説明 |
|---|---|
| パラメーター | セル参照、文字列、数値などの関数に渡す値 最大253まで設定可能 |
| 計算 | 関数の結果を実行して返す数式 |
ちょっと分かりにくいですが、ここでいうパラメーターは仮の変数みたいなものです。
（変数とは、値を入れておける「名前付きの箱」みたいなものです）
つまり、
＜計算の流れ＞
①COUNTBLANK関数で空白があるか確認し、空白なし（=0）であればTRUE、そうでなければFALSEを引数「r」に渡す
②BYROW関数で、各行の引数「r」に渡された値を配列で表示
③FILTER関数で、TRUE（=空白なし）行のみ抽出
という流れで計算しているということです。
BYROW部分を表示してみると、確かに空白行はTRUEを返しています。
ちなみに今回は引数「r」としましたが、あくまで変数のため、「x」や「row」など別のワードでも使用できます。
ややとっつきにくい関数ですが、使い方が分かると便利な関数で、他にも色々応用が効きます。
ちなみにBYROW関数は、Microsoft 365で使用できる関数です。
Microsoft 365であれば、BYROW関数以外にもExcelの最新機能を活用することができます。
さらなる業務効率化も期待できるので、まだ導入されていない場合は検討してみてください。
👉Microsoft 365の詳細はこちら
まとめ：FILTER関数で、Excel業務をもっとスマートに！
FILTER関数は、Excel業務を効率化する強力な関数です。
オートフィルターとの違いは、
・元データには影響しないこと（新しい範囲に数式で表示）
・条件を数式などで設定しておくと、自動的に条件が反映される
など様々あります。
FIND関数やSORT関数、BYROW関数との連携までマスターすればさらに便利に！
使いこなして、作業効率をグングン上げていきましょう！
最後まで読んでいただき、ありがとうございました！
コメント