エクセルのFILTER関数で条件に合うデータを抽出する

FILTER関数は、作成した表の範囲と条件を指定するだけで必要なデータが簡単に抽出できる便利な関数です。

FILTER関数は、2021以降のエクセルまたはOffice365しか使用することができませんが、複数条件のデータを抽出したいときに役立つ関数です。

本記事を参考に、使い方をマスターしてみてください。

FILTER関数以外のエクセルのフィルターに関する使い方を紹介した関連記事もおすすめです。

エクセルのFILTER関数の使い方！Excel2024以降の新機能を活用しよう

複数条件を設定したデータを抽出したい場合は、FILTER関数がおすすめです。

これは、FILTER関数の条件の部分にフィルターをかけたい文字を入力して、画面の右側に条件と合致するデータを抽出したものです。

FILTER関数の条件は、上記のように抽出した条件を文字を使って指定することができます。

または、上記のようにあらかじめ抽出したい条件を入力しておくと、セル番地で指定することも可能です。

抽出後の結果は同じですが、条件を入力してからセル番地で指定すると抽出した条件が視覚的に見やすくなるというメリットがあります。

どちらの方法を使っても必要なデータが抽出できるので、使いやすいほうを覚えてみてください。

FILTER関数の基本操作

FILTER関数の基本的な使い方を解説していきます。

FILTER関数の構文は、「=FILTER(配列,含む,[空の場合])」です。

[空の場合]の部分は省略することが可能です。

これは、FILTER関数の関数ボックスを表示した画面です。

「配列」は、赤枠で囲まれているフィルターをかけたいデータが入力されているデータ全体の範囲を指定します。

「含む」は、緑の枠で囲まれているフィルターをかけたいデータが入力されている範囲を選択したあとに「=」を入力し、条件が入力されているセルをクリックします。

[空の場合]は、指定した条件に該当するデータがなかった場合に表示する値です。

[空の場合]は省略することができますが、該当するデータがなかった場合はエラーが表示されてしまうので注意してください。

また、FILTER関数は、「条件の数」、「すべての条件を満たしているのか」、「いずれか1つでも条件を満たしているものを抽出したいのか」によって引数の入力方法が少し変わります。

これらの手順を1つずつ解説していきます。

指定したい条件が1つの場合

指定したいフィルター条件が1つの場合は、「含む」の引数にフィルターをかけるデータが入力されている範囲と、条件が入力されているセルを、「データ範囲=セル番地」のような形で入力します。

条件が複数あり、すべての条件を満たしているデータを抽出したい場合

複数の条件があり、すべての条件を満たしているデータを抽出したい場合は、条件と条件のかっこの間を「*(アスタリスク)」を使ってつなげます。

条件が複数あり、いずれか一方を満たしているデータを抽出したい場合

条件が複数あり、いずれか一方を満たしているデータを抽出したい場合は、「+」を使って条件を入力します。

FILTER関数では、「+」はOR関数の「OR」と同じ意味になります。

上記のデータを使って、東京支店または千葉支店の社員データを抽出してみましょう。

FILTER関数では、「OR」の条件を入力したいときは「+」を使うということを覚えておきましょう。

FILTER関数は、エクセルの「スピル」という機能を使ってデータを抽出しています。
関数を挿入したセル以外の場所に表示されているデータは、削除したり、修正したりすることはできません。
入力内容の修正または削除を行いたい場合は、FILTER関数を挿入したセルを選択してから操作を行ってください。

FILTER関数は他の関数と組み合わせることもできる

FILTER関数は単独で使用することもできますが、他の関数と組み合わせて使うこともできます。

今回は、「AVERAGE関数」と「IFERROR関数」を組み合わせて使う方法を紹介します。

AVERAGE関数と組み合わせて平均を求める

FILTER関数とAVERAGE関数を組み合わせると、FILTER関数で指定した条件の値の平均値を求めることができます。

上記はわかりやすいように簡単なデータ例で紹介していますが、AVERAGE関数とFILTER関数を組み合わせると、特定のデータを抽出して平均値を求めることができるので、データ分析をしたいときなどに活用してみてください。

IFERROR関数と組み合わせて値がない場合のエラー表示を回避する

FILTER関数とIFERROR関数を組み合わせておくと、フィルター条件に該当する値がなかった場合にエラー表示を回避することができます。

エラーを表示させたくない場合は、FILTER関数の空の場合の引数に「"データなし"」のように入力して表示する文字を指定したり、「""」を入力して空白にしたりすることもできます。

できるだけシンプルに関数を入力したい場合は、FILTER関数の[空の場合]に入力しても問題ありません。

ですが、IFERROR関数は他の関数にも使える便利な関数なので、今回はIFERROR関数を使った方法を紹介します。

FILTER関数で指定した条件に該当するデータがなかった場合、「#CALC!」というエラーが表示されます。

FILTER関数にIFERROR関数を組み合わせておくと、上記の画像のように指定した文字列で表示することが可能です。

エクセルのオートフィルターとFILTER関数の違いと使い分け

エクセルのオートフィルターとFILTER関数の違いは、大きく分けて以下の3つがあります。

オートフィルター	FILTER関数
フィルターボタンを挿入する必要がある	フィルターボタンを挿入する必要がない
元データの中から条件に合うデータのみが表示され、条件に合わないデータは非表示になる	元データはそのままの状態で、条件と合致するデータが別の場所に表示される
すべてのエクセルで使用可能	Excel2021以降のバージョンのエクセルまたはOffice365のエクセルでしか使用できない

オートフィルターは、エクセル初心者でも比較的簡単に必要なデータを抽出することが可能です。

しかし、条件に合致していないデータは非表示になってしまうため、フィルターをかける前のデータとフィルターをかけたあとのデータが比較できないというデメリットがあります。

FILTER関数の場合は、フィルターで抽出したデータは元データとは別の場所に抽出されるので、元データと比較しやすいというメリットがあります。

オートフィルターはフィルター条件の設定方法が比較的わかりやすいので初心者の方にはおすすめですが、FILTER関数も便利なのでぜひ活用してみてください。

FILTER関数で抽出したデータに連番を振りたいときは抽出後のデータにROW関数を挿入する

オートフィルターの場合は、フィルターをかけると条件と合致していないデータが非表示になります。

そのため、フィルターをかけたデータにナンバリングしてからフィルターを解除すると、番号の間が空いてしまいます。

しかし、FILTER関数の場合は元データとは別の場所にデータが抽出されるので、抽出後のデータに番号を振るだけで連番の挿入が可能です。

今回は、ROW関数を活用して連番を振る方法を紹介します。

ROW関数は行番号を返す関数ですが、関数を挿入した位置の上にある行数分をマイナスすることで連番が挿入できるので、試してみてください。

補足

連番はオートフィルでコピーして入力することもできます。

しかし、ROW関数を使ってナンバリングしておくと、抽出条件を変えると抽出されたデータの数に合わせて番号が振り替わるので、試してみてください。

FILTER関数が使えないときはエクセルのバージョンを確認する

FILTER関数が使えないときは、現在使用しているエクセルのバージョンを確認してみましょう。

Excel2019以前のバージョンが表示された場合は、FILTER関数を使うことができません。

オートフィルターやフィルターオプションを活用するか、次で紹介するFILTER関数の代用として使える別の関数を活用する方法を試してみてください。

エクセルのFILTER関数の代用になる関数の使用例

Excel2019以前ではFILTER関数を使うことはできませんが、VLOOKUP関数と他の関数を組み合わせることでFILTER関数と同じような形でデータを抽出することが可能です。

ただし、FILTER関数とは異なり、入力内容が複雑になります。

使い方がわからない場合や関数の意味が分からない場合は、オートフィルターやフィルターオプションなどの機能をうまく活用してみてください。

今回は、「VLOOKUP関数」、「ROW関数」、「SMALL関数」、「COLUMN（カラム）関数」を組み合わせ、元データはそのままの状態で別の場所に必要なデータを抽出する方法を紹介します。

これは、STEP5で入力した関数に説明を加えたものです。

先頭に入力したIFERROR関数は、指定した条件が見つからなかったときにエラーを表示させないために入力している部分です。

関数の先頭に「=IFERROR(」、関数の終わりに「,""）」を入力します。

VLOOKUP関数の検索値には、SMALL関数とROW関数を組み合わせたものを入力しています。

これは、挿入したVLOOKUP関数をコピーしたときにA列に表示されている番号を上から順番に変動させて、検索値を指定するためです。

SMALL関数とROW関数を組み合わせることで、VLOOKUP関数の検索値にA列に挿入したROW関数の数字が小さい順に挿入することができます。

ROW関数の引数は、ROW関数が挿入されている先頭の「A1」を入力してください。

VLOOKUP関数の列番号の部分にはCOLUMN関数を挿入しています。

列方向に変動させたいときはROW関数を使いますが、行方向に数字を変動させたい場合はCOLUMN関数を使います。

VLOOKUP関数の列番号をCOLUMN関数を使って入力し、横方向のコピーで挿入するVLOOKUP関数の列番号を変動させましょう。

COLUMN関数の引数は、「単価」が挿入されているC列の先頭の「C1」を指定します。

今回紹介した方法を使うとFILTER関数と同じようにデータを抽出できます。

しかし、複数の関数を組み合わせて使う必要があり、数式の表記も複雑になるため、それぞれの関数の使い方をマスターしてから使用してみてください。

エクセルのFILTER関数に関するQ＆A

エクセルで条件に合うデータを抽出する関数はありますか？

Excel2021以降、またはOffice365を使用している場合は、FILTER関数が便利です。

詳しい操作方法は、FILTER関数の基本操作をご覧ください。

FILTER関数で該当するデータがない場合、エラーになるのはなぜですか？

FILTER関数の関数構文は、「=FILTER(配列,含む,[空の場合])」です。

引数の「空の場合」の部分は、該当する条件がなかった場合に表示したい文字などを指定しますが、省略することも可能です。

「空の場合」の引数を省略すると、条件に該当するデータがなかった場合、「#CALC!」というエラーが表示されます。エラーを表示させたくない場合は、FILTER関数の「空の場合」の引数で「データなし」のような文字列を指定するか、IFERROR関数と組み合わせて、「データなし」のように指定しておきましょう。

FILTER関数とIFERROR関数を組み合わせて使う方法は、FILTER関数は他の関数と組み合わせることもできるで解説しています。

ExcelのFILTER関数はどのバージョンから使えますか？

Excel2021以降またはOffice365のエクセルで使用できます。

新機能のFILTER関数を活用してみよう！

Excel2021以降から使用できるようになったFILTER関数は、「元データの範囲」と「フィルターをかける条件が入力されている範囲と条件」を入力するだけで、必要なデータを簡単に抽出できます。

元データはそのままの状態で、必要なデータを別の場所に抽出できるのでデータを比較したい場合にも便利です。

最後にFILTER関数を使って、必要なデータを抽出する際のポイントをおさらいしておきましょう。

FILTER関数は使い方さえ覚えてしまえば、必要なデータをすばやく抽出できる便利な関数です。

必要なデータの抽出やデータ解析にFILTER関数を活用してみてください。

FILTER関数以外のエクセルのフィルターに関する使い方を紹介した関連記事もおすすめです。