Excel約5分で読めます

Excelで条件に合う行を別シートに抜き出す|FILTER関数

Excelで条件に合う行を別シートに抜き出すを表したイメージ
記事の内容を表したイメージ(AI生成)

名簿や申込一覧から「特定の部署だけ」「未対応のものだけ」を別シートに表示したいとき、コピーと貼り付けを繰り返すと元データが変わるたびに作業が発生します。FILTER関数を使うと、条件に合う行を別シートに自動で表示でき、元データを更新するだけで結果側も変わります。

この記事では、FILTER関数の基本的な書き方から、複数条件の組み合わせ、結果が0件のときの表示制御、別シートへの参照方法まで順に説明します。FILTER関数はExcel 2021以降およびMicrosoft 365のExcelで使用できます。Excel 2019以前では使えないため、その場合はオートフィルタや別の方法を検討してください。画面の表示は版や設定により異なります。

作業を始める前に、元のファイルを必ずコピーして保存しておいてください。数式の入力ミスで元データを上書きするリスクを避けるためです。


FILTER関数の基本的な書き方

FILTER関数の構文は次のとおりです。

=FILTER(配列, 含む, [空の場合])
  • 配列:抜き出したいデータ範囲(見出し行は含めない)
  • 含む:条件式。TRUEになった行だけが返されます
  • 空の場合:条件に合う行がゼロ件のときに表示する値(省略可)

架空の例で確認します(以下のデータは架空の例です)。

表は横にスクロールできます

A列:氏名B列:部署C列:ステータス
山田 太郎営業完了
鈴木 花子総務未対応
田中 一郎営業未対応
佐藤 明美総務完了

このデータが「データ」シートのA2:C5にあるとします。別シートに「営業」部署の行だけを抜き出す場合、抽出先シートのセル(たとえばA2)に次のように入力します。

=FILTER(データ!A2:C5, データ!B2:B5="営業", "該当なし")

結果として「山田 太郎/営業/完了」と「田中 一郎/営業/未対応」の2行が表示されます。FILTER関数はスピル(自動展開)する関数なので、A2に入力するだけで必要な列数・行数が自動的に広がります。隣のセルや下のセルに値が入っているとエラーになるため、結果が展開される範囲を空けておいてください。


複数条件を組み合わせる

AND条件(すべてを満たす行)

「営業」かつ「未対応」の行だけ抽出するには、条件式を *(アスタリスク)でつなぎます。

=FILTER(データ!A2:C5, (データ!B2:B5="営業")*(データ!C2:C5="未対応"), "該当なし")

* は論理積(AND)として機能します。両方の条件がTRUEの行だけが残ります。上の架空データでは「田中 一郎/営業/未対応」の1行だけが返ります。

OR条件(どちらかを満たす行)

「営業」または「総務」を抽出するには、+(プラス)でつなぎます。

=FILTER(データ!A2:C5, (データ!B2:B5="営業")+(データ!B2:B5="総務"), "該当なし")

+ は論理和(OR)として機能します。どちらか一方でもTRUEであれば行が返ります。

数値の比較

数値列(例:D列に点数)で「80以上」の行を抽出するなら条件式は データ!D2:D5>=80 のように書きます。文字列との混在には注意が必要で、数値列に文字が混入しているとエラーになる場合があります。事前にデータ型を確認してください。


別シートへの参照と見出し行の扱い

FILTER関数の配列には見出し行を含めません。見出しは抽出先シートに手動で入力しておくか、別セルに =データ!A1、=データ!B1 などの参照式を並べる方法が簡単です。

元のデータ範囲を後から行追加することがわかっている場合は、範囲を列全体で指定する方法があります。

=FILTER(データ!A:C, データ!B:B="営業", "該当なし")

ただし、列全体を指定すると見出し行まで含まれるため、見出し行もデータとして返ってきます。実用的には、データが確実に収まる行数(例:A2:C1000)で範囲を広めに指定するほうが扱いやすいケースが多いです。


結果がゼロ件のときの制御

第3引数「空の場合」を省略すると、条件に合う行がないときに #CALC! エラーが表示されます。他の担当者がファイルを使う場合にエラー表示が混乱を招くことがあるため、第3引数に "該当なし" や "" を設定しておくことをお勧めします。

=FILTER(データ!A2:C5, データ!B2:B5="経理", "該当なし")

上の架空データには「経理」が存在しないため、"該当なし" と表示されます。空文字 "" を指定するとセルが空白になります。どちらが適切かは、見る人の状況に合わせて判断してください。


よくある失敗と確認ポイント

「#SPILL!」エラーが出る 結果が展開される予定の範囲に別のデータや数式が入っています。FILTER関数を入力したセルの下・右を空にしてから再入力してください。

条件が一致しているはずなのに行が返らない 半角・全角の違い、末尾の余分なスペース、セルの表示形式と実際の値のずれが原因であることがよくあります。元データのセルを選択して数式バーの値を直接確認するか、TRIM 関数や CLEAN 関数で余分な文字を除去してから試してください。

数式を変えるたびに結果範囲の位置がずれる FILTER関数の入力セルを変えると展開位置も変わります。抽出先シートの見出し行とFILTER関数の入力セルの位置関係を固定するルールを決めておくと管理しやすくなります。

元データの行が増えたのに反映されない 配列に固定範囲(例:A2:C50)を指定していて、新しいデータがその範囲を超えた場合です。範囲を広げるか、テーブル機能(Ctrl+T で変換)を使うと追加行が自動的に範囲に含まれます。テーブルを使う場合の参照式は データ!テーブル名[列名] の形になります。詳細は公式のヘルプで確認してください。


次の行動

まず元のファイルをコピーして保存してから、抽出先シートの空いたセルに =FILTER( と入力して試してください。条件が1列1条件の単純なケースから始め、正しく抽出できることを確認したうえで複数条件に進むと、どこで問題が起きているかを切り分けやすくなります。個人情報を含む名簿をFILTER関数で扱う場合でも、ファイル自体を外部のサービスや生成AIに渡す前に、情報の取り扱いルールを確認してください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ