ExcelでFILTER関数の使い方をお探しですね。
広告
Excelで複数データを一括抽出！FILTER関数の使い方を分かりやすく解説
Excelで売上表や顧客リスト、在庫表などを扱っていると、「東京のデータだけ見たい」「売上が10万円以上でAさん担当のもの」「複数の条件のどれかに当てはまる行」だけをサッと取り出したい場面ってありますよね。
もちろん従来のフィルター機能でも絞り込みはできるんですが、条件を変えるたびに操作が必要で、レポート作成や定期集計ではちょっと面倒です。
そこで便利なのが**FILTER関数**。
この記事では、Excelで複数データを一括抽出できるFILTER関数の基本から、複数条件の指定方法、実務でつまずきやすいポイントまで、分かりやすく解説していきます。
FILTER関数って何？複数データを一括抽出できる便利な仕組み
FILTER関数は、指定した範囲の中から条件に合うデータだけを抽出するExcel関数です。
普通のオートフィルターは画面上で表示行を絞り込む機能ですが、FILTER関数は**数式として抽出結果を別の場所に表示**できるのが特徴です。
つまり、元のデータはそのまま残しておいて、条件に合う一覧表を自動で作れるんですね。
たとえば、A列に商品名、B列に地域、C列に売上金額がある表から「地域が東京の行だけ」を別の場所に取り出す、といった使い方ができます。
条件を設定しておけば、元データが更新されたときも抽出結果が自動で変わってくれるので、毎月の報告書や営業リスト作成にもピッタリです。
FILTER関数の基本の書き方
FILTER関数の基本構文は次のとおりです。
“`
=FILTER(配列, 含む, [空の場合])
“`
– **配列**：抽出したい元データ全体の範囲
– **含む**：抽出条件
– **空の場合**：条件に一致するデータがなかったときに表示する文字（省略可）
たとえば、A2:C100の表からB列が「東京」の行を抽出する場合はこう書きます。
“`
=FILTER(A2:C100, B2:B100=”東京”, “該当なし”)
“`
この数式は、A2:C100の範囲から、B列が「東京」である行だけを表示し、該当データがなければ「該当なし」と表示するという意味です。
「スピル」って何？
FILTER関数を使ううえで知っておきたいのが**「スピル」**という仕組みです。
スピルとは、1つのセルに入力した数式の結果が、必要な行数・列数に自動で広がって表示される機能のこと。
FILTER関数では抽出結果が複数行になることが多いので、数式を入れたセルの下や右に十分な空白が必要です。
結果を表示する範囲に別の値が入っていると「#SPILL!」エラーが出てしまうので注意しましょう。
また、FILTER関数は主に**Microsoft 365やExcel 2021以降**で使える関数です。
古いバージョンのExcelでは使えない場合があるので、共有ファイルで使うときは相手の環境も確認しておくと安心です。
FILTER関数の基本的な使い方と条件の指定方法
FILTER関数で最初に押さえておきたいのは、**抽出する範囲と条件範囲の行数をそろえる**ことです。
たとえば、抽出対象をA2:D100にした場合、条件範囲もB2:B100やC2:C100のように同じ行数で指定します。
ここがずれていると、正しく抽出できなかったりエラーになったりします。
実務では見出し行を含めるかどうかで範囲がずれやすいので、数式を作る前に「データ本体は何行目から始まるか」を確認しましょう。
基本形として、部署が「営業部」の行だけを抽出するならこう書きます。
“`
=FILTER(A2:D100, B2:B100=”営業部”, “該当なし”)
“`
数値や日付での条件指定もできる
条件には、文字列の一致だけでなく、**数値や日付の比較**も指定できます。
売上が10万円以上のデータを抽出するならこんな感じです。
“`
=FILTER(A2:D100, D2:D100>=100000, “該当なし”)
“`
日付の場合も、対象列が正しい日付データとして入力されていれば比較できます。
たとえば、受注日が2024年1月1日以降の行を取り出すならこう書けます。
“`
=FILTER(A2:D100, A2:A100>=DATE(2024,1,1), “該当なし”)
“`
DATE関数を使うと、日付の解釈違いを避けやすくなります。
文字列として入力された日付では期待どおりに比較できないことがあるので、日付形式の統一も大切です。
条件をセル参照にすると便利
条件をセル参照にすると、抽出表がもっと使いやすくなります。
たとえば、F1セルに地域名を入力し、そこに入力された地域のデータを抽出したい場合はこう書きます。
“`
=FILTER(A2:D100, B2:B100=F1, “該当なし”)
“`
F1を「東京」から「大阪」に変更するだけで、抽出結果も自動で切り替わります。
さらに、F1セルをプルダウンリストにしておけば、関数に詳しくない人でも条件を選ぶだけで必要なデータを表示できます。
FILTER関数は単にデータを取り出すだけでなく、入力規則やテーブル機能と組み合わせることで、操作しやすい簡易検索画面のように使える点が実務向きなんです。
複数条件を指定する方法：AND条件は「*」、OR条件は「+」
FILTER関数で複数条件を指定するときは、**AND条件とOR条件の違い**を理解することが大切です。
AND条件：すべての条件を満たすデータを抽出
AND条件は「すべての条件を満たすデータ」を抽出する考え方で、FILTER関数では条件式同士を**「*」でつなぎ**ます。
たとえば、「地域が東京」**かつ**「売上が10万円以上」の行を抽出する場合はこう書きます。
“`
=FILTER(A2:D100, (B2:B100=”東京”)*(D2:D100>=100000), “該当なし”)
“`
各条件式はTRUEまたはFALSEを返します。
Excel内部ではTRUEが1、FALSEが0のように扱われるので、掛け算では両方が1のときだけ1になります。
つまり、すべての条件を満たす行だけが抽出される仕組みです。
OR条件：いずれかの条件を満たすデータを抽出
OR条件は「いずれかの条件を満たすデータ」を抽出する考え方で、FILTER関数では条件式同士を**「+」でつなぎ**ます。
たとえば、「地域が東京」**または**「売上が10万円以上」の行を抽出したい場合はこう書きます。
“`
=FILTER(A2:D100, (B2:B100=”東京”)+(D2:D100>=100000), “該当なし”)
“`
プラスでつなぐと、どちらか一方がTRUEであれば結果が1以上になるため、片方だけ条件を満たす行も抽出されます。
普通のExcel関数ではAND関数やOR関数を思い浮かべる人も多いと思いますが、FILTER関数の条件式では「*」と「+」を使った方が直感的で分かりやすいんです。
AND条件とOR条件を組み合わせるときは括弧に注意
AND条件とOR条件を組み合わせる場合は、**括弧の使い方**がとても重要です。
たとえば、「地域が東京または大阪」**かつ**「売上が10万円以上」のデータを抽出するならこう書きます。
“`
=FILTER(A2:D100, ((B2:B100=”東京”)+(B2:B100=”大阪”))*(D2:D100>=100000), “該当なし”)
“`
先に「東京または大阪」を判定し、その結果に売上条件を掛け合わせることで、意図した条件になります。
括弧が不足していると、Excelが想定と異なる順番で計算してしまい、抽出結果がずれることがあります。
複数条件では、条件ごとに括弧で囲み、ORのまとまり、ANDのまとまりを明確にするのが失敗を防ぐコツです。
実務で役立つ応用テクニックとエラーを防ぐポイント
部分一致で抽出する方法
FILTER関数は、完全一致だけでなく**部分一致の抽出**にも応用できます。
たとえば、商品名に「ノート」という文字を含む行を抽出したい場合、SEARCH関数とISNUMBER関数を組み合わせます。
“`
=FILTER(A2:D100, ISNUMBER(SEARCH(“ノート”,A2:A100)), “該当なし”)
“`
SEARCH関数は指定した文字が見つかると位置番号を返し、見つからない場合はエラーを返します。
ISNUMBER関数で数値かどうかを判定することで、「指定文字を含むかどうか」をTRUE/FALSEで扱えるようになります。
商品名、顧客名、備考欄などからキーワード検索したいときに便利です。
条件に一致するデータがないときの対処法
条件に一致するデータがない場合、第3引数の「空の場合」を省略すると「#CALC!」エラーが表示されることがあります。
エラーのままでも数式としては間違いではないんですが、レポートや共有資料では見た目が悪く、利用者が不安に感じる原因になります。
そのため、実務では「該当なし」「データなし」または空白を意味する「””」を指定しておくとよいでしょう。
“`
=FILTER(A2:D100, B2:B100=F1, “該当なし”)
“`
こうしておけば、条件に合う行がないときも分かりやすく表示できます。
大量データを扱うときの注意点
大量データでFILTER関数を使う場合は、**列全体参照を避ける**ことも大切です。
「A:D」や「B:B」のように列全体を指定すると、データ量によっては計算が重くなることがあります。
実務では、必要な範囲だけを指定するか、Excelのテーブル機能を使ってデータ範囲を管理すると扱いやすくなります。
テーブルにしておけば、行を追加したときに参照範囲が自動で広がり、数式のメンテナンスもしやすくなります。
他の関数と組み合わせてもっと便利に
FILTER関数の抽出結果をSORT関数で並べ替えたり、UNIQUE関数で重複を除いたりすれば、集計前の一覧作成も効率化できます。
FILTER関数は単体でも便利ですが、条件セル、プルダウン、テーブル、並べ替え関数と組み合わせることで、日々のデータ抽出作業を大きく短縮できる関数なんです。
まとめ
この記事を参考に、ぜひFILTER関数を使いこなして、日々の作業を効率化してみてくださいね！
広告
