【Excel】FILTER関数で条件に合うデータだけを抽出する方法

Excelで大量のデータの中から、特定の条件に合う行だけを効率良く抽出したいと考えていませんか。手作業でフィルターを設定したり、VLOOKUP関数を繰り返し使ったりするのは時間がかかり、ミスも発生しがちです。FILTER関数を使えば、条件に合うデータを瞬時に自動抽出できます。この記事では、FILTER関数を使って必要なデータだけを取り出す詳細な手順と、利用時の注意点について解説します。

FILTER関数とは何か その機能と利用前提

FILTER関数はExcelの動的配列関数の一つで、指定した範囲から条件に合う行や列を抽出します。従来のVLOOKUP関数やINDEX+MATCH関数と異なり、抽出結果が自動的にスピルする特徴があります。この機能は特定のデータセットから必要な情報だけを瞬時に取り出したい場合に非常に役立ちます。

動的配列関数としてのFILTER関数の特徴

FILTER関数は、結果が単一のセルではなく、条件に合うすべてのデータ行を自動的に隣接セルに「スピル」する特性を持ちます。これにより、数式をコピーする必要がなく、一つの数式で複数の行を抽出できます。元のデータが変更された場合も、抽出結果は自動で更新されます。

利用できるExcelのバージョン

FILTER関数は、Excel for Microsoft 365のユーザーのみが利用できる関数です。Excel 2019やExcel 2021など、Microsoft 365以外の永続ライセンス版ExcelではFILTER関数は搭載されていません。そのため、使用するExcelのバージョンを確認することが重要です。

FILTER関数で条件に合うデータを抽出する手順

FILTER関数の基本的な構文は=FILTER(配列, 含む, [空の場合])です。「配列」は抽出元のデータ範囲、「含む」は抽出条件となる真偽値の配列、「空の場合」は条件に合うデータがない場合に表示する値です。ここでは具体的な抽出手順を解説します。

単一条件でデータを抽出する手順

特定の部署の社員データだけを抽出する例で、単一条件でのFILTER関数の使い方を説明します。

1. 抽出元のデータ範囲を確認する
   抽出したいデータが入力されている範囲を特定します。例えば、A1セルからD10セルにデータがある場合、この範囲が抽出元の「配列」です。
2. 抽出条件を設定する
   例えば、B列に部署名があり、「営業部」の社員を抽出したいとします。この場合、条件式は「B1:B10=”営業部”」となります。
3. 数式を入力する
   抽出結果を表示したいセルに、以下の数式を入力します。
   =FILTER(A1:D10, B1:B10="営業部")
   この数式は、A1:D10の範囲からB列が「営業部」であるすべての行を抽出します。
4. Enterキーを押して結果を確認する
   数式を入力後、Enterキーを押すと、条件に合うデータが自動的に指定したセルから下にスピル表示されます。

複数条件 AND でデータを抽出する手順

部署が「営業部」でかつ「性別が女性」の社員データを抽出する例で、AND条件でのFILTER関数の使い方を説明します。

1. 抽出元のデータ範囲を確認する
   A1セルからD10セルにデータがあるとして、この範囲を「配列」とします。
2. 抽出条件1を設定する
   B列に部署名があり、「営業部」を条件とします。条件式は「B1:B10=”営業部”」です。
3. 抽出条件2を設定する
   C列に性別があり、「女性」を条件とします。条件式は「C1:C10=”女性”」です。
4. AND条件を数式で結合する
   複数の条件をAND条件で結合するには、条件式同士を「*」で掛け合わせます。これにより、両方の条件が真の場合にのみ真となります。
   (B1:B10="営業部")*(C1:C10="女性")
5. 数式を入力して結果を確認する
   抽出結果を表示したいセルに、以下の数式を入力します。
   =FILTER(A1:D10, (B1:B10="営業部")*(C1:C10="女性"))
   この数式は、A1:D10の範囲からB列が「営業部」かつC列が「女性」であるすべての行を抽出します。

複数条件 OR でデータを抽出する手順

部署が「営業部」または「役職が部長」の社員データを抽出する例で、OR条件でのFILTER関数の使い方を説明します。

1. 抽出元のデータ範囲を確認する
   A1セルからD10セルにデータがあるとして、この範囲を「配列」とします。
2. 抽出条件1を設定する
   B列に部署名があり、「営業部」を条件とします。条件式は「B1:B10=”営業部”」です。
3. 抽出条件2を設定する
   D列に役職があり、「部長」を条件とします。条件式は「D1:D10=”部長”」です。
4. OR条件を数式で結合する
   複数の条件をOR条件で結合するには、条件式同士を「+」で足し合わせます。これにより、いずれかの条件が真の場合に真となります。
   (B1:B10="営業部")+(D1:D10="部長")
5. 数式を入力して結果を確認する
   抽出結果を表示したいセルに、以下の数式を入力します。
   =FILTER(A1:D10, (B1:B10="営業部")+(D1:D10="部長"))
   この数式は、A1:D10の範囲からB列が「営業部」であるか、またはD列が「部長」であるすべての行を抽出します。

FILTER関数利用時の注意点とよくあるトラブル

FILTER関数は強力な機能ですが、いくつかの注意点やトラブルが発生する場合があります。それぞれの対処法を把握しておきましょう。

#SPILL!エラーが発生してしまう

#SPILL!エラーは、FILTER関数の抽出結果をスピル表示するための十分なスペースが、参照先のセル範囲にない場合に発生します。

原因: 抽出結果を表示する予定の範囲に、すでにデータや結合されたセルが存在していることです。動的配列関数は、結果をスピルするために、数式を入力したセルから右下方向に連続した空のセル範囲を必要とします。

対処: #SPILL!エラーが表示された場合は、抽出結果がスピルする可能性のある範囲のセルを空にします。不要なデータを削除するか、数式を入力するセルを移動して、スピルに必要な空白領域を確保してください。

#CALC!エラーが表示される

#CALC!エラーは、FILTER関数で指定した条件に合うデータが一つも存在しない場合に発生します。

原因: FILTER関数の第二引数「含む」で指定した条件に合致するデータが、第一引数「配列」の範囲内に見つからないことが主な原因です。また、条件式に誤りがある場合もこのエラーが表示されることがあります。

対処: このエラーを回避するためには、FILTER関数の第三引数「空の場合」を設定します。例えば、=FILTER(A1:D10, B1:B10="経理部", "該当データなし")とすると、条件に合うデータがない場合に「該当データなし」と表示されます。また、条件式が正しいか再確認することも重要です。

Excel 2019/2021でFILTER関数が使えない

Excel 2019やExcel 2021を使用している場合、FILTER関数を利用しようとすると、#NAME?エラーや「この関数は無効です」といったメッセージが表示されます。

原因: FILTER関数はExcel for Microsoft 365のみで利用できる動的配列関数です。Excel 2019やExcel 2021などの永続ライセンス版Excelには、この関数は搭載されていません。

対処: Excel for Microsoft 365の契約にアップグレードすることを検討してください。すぐにアップグレードが難しい場合は、代わりにオートフィルター機能や、データタブにある「詳細設定」フィルター機能を利用することで、同様の条件抽出が可能です。VBAによる抽出機能を開発する方法もあります。

FILTER関数と従来のデータ抽出方法の比較

FILTER関数と、従来のExcelにおけるデータ抽出方法の主な違いを比較します。それぞれの機能の特性を理解し、業務内容に合わせた使い分けの参考にしてください。

項目	FILTER関数	オートフィルター	詳細設定フィルター	VLOOKUP関数
自動更新	元のデータ変更時に自動で結果を更新	手動でフィルターの再適用が必要	手動でフィルターの再適用が必要	元のデータ変更時に自動で結果を更新
複数条件	数式でAND/OR条件を柔軟に設定	AND条件は設定可能、OR条件は制限あり	条件範囲を使ってAND/OR条件を細かく設定	基本は1つの検索値、複数条件は工夫が必要
抽出結果の表示	新しいセル範囲にスピル表示	元のデータ行を非表示にして表示	元のデータ行を非表示にするか、別の場所に抽出	指定したセルに1つの検索結果を表示
操作の簡易性	数式入力の学習が必要だが、一度設定すれば容易	直感的な操作で簡単に設定	条件範囲の作成が必要でやや複雑	比較的簡単だが、複数条件は工夫が必要
利用バージョン	Excel for Microsoft 365のみ	すべてのExcelバージョンで利用可能	すべてのExcelバージョンで利用可能	すべてのExcelバージョンで利用可能
応用性	他の動的配列関数と組み合わせ可能、複雑な分析にも対応	簡単な絞り込みに適している	特定の抽出条件を保存し再利用可能	個別データの検索、参照に適している

FILTER関数は、特にデータが頻繁に更新され、常に最新の抽出結果が必要な場合に非常に有効です。手動フィルターのように再適用する手間がありません。一方、一時的な絞り込みであればオートフィルターが手軽です。詳細設定フィルターは、条件を保存して繰り返し使う場合に便利です。VLOOKUP関数は、一つの検索値に基づいて単一のデータを参照する場面に適しています。

まとめ

FILTER関数を使えば、Excelデータからの条件抽出作業が格段に効率化できます。単一条件から複雑な複数条件まで対応し、自動で抽出結果が更新されるため、常に最新のデータを手元に置くことが可能です。今回解説した手順と注意点を参考に、日々の業務にFILTER関数を取り入れてみてください。今後は、SORT関数やUNIQUE関数といった他の動的配列関数と組み合わせて、さらに高度なデータ加工や分析にも挑戦できます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】