FILTER関数の使い方｜Excelで条件抽出とスピルを理解する

Excelで大量の名簿や台帳から「営業部の社員だけ」「30歳以下のメンバーだけ」といった条件に合うデータだけを取り出したいとき、多くの人がまず思い浮かべるのはオートフィルタ（フィルターボタン）ではないでしょうか。ただし、オートフィルタは条件を変えるたびに手作業でボタンを操作し直す必要があり、抽出結果を別の場所に残しておきたい場合は、その都度コピー＆ペーストが必要になるなど、地味に手間がかかる作業です。

そこで活用したいのが、Excel 2021およびMicrosoft 365から使えるようになった「FILTER関数」です。FILTER関数は、条件に一致するデータだけを数式ひとつで別のセル範囲に自動的に取り出せる「動的配列関数」の一つで、元データを修正すれば抽出結果も自動的に更新される点が最大の特長です。オートフィルタのように手作業を繰り返す必要がなく、抽出条件を数式として残せるため、後から見返したときにも「何を条件に抽出したデータなのか」が一目でわかります。

本記事では、FILTER関数の基本構文から、実際のサンプルデータを使った抽出手順、OR条件・AND条件による複数条件抽出、該当データがない場合の表示方法まで、具体的な数式例とともに順を追って解説します。

FILTER関数とは

FILTER関数は、指定した条件に一致するデータだけを配列として取り出し、数式を入力したセルを起点に自動的に書き出す関数です。Excel 2021およびMicrosoft 365で利用できる「動的配列関数」の一つで、これより前のバージョン（Excel 2019・2016など）には搭載されていません。

従来のオートフィルタやフィルターオプションの機能と違い、FILTER関数は数式として動作するため、元データを更新すると抽出結果も自動的に再計算される点が大きな特長です。また、抽出条件を数式としてセルに残せるため、後から条件を確認・修正しやすいというメリットもあります。

FILTER関数の構文

構文: FILTER(配列, 含む, [空の場合])

• 配列：抽出したいデータ全体のセル範囲を指定します（例：A2:F7）
• 含む：抽出条件となる論理式を指定します（例：C2:C7="営業部"）
• 空の場合（省略可）：条件に一致するデータが1件もなかった場合に表示する値を指定します。省略するとエラー値が返されます

FILTER関数の使い方（基本の抽出）

次のような社員データ（A1:F7、見出し行はA1:F1）を例に、FILTER関数の基本的な使い方を確認します。

このデータの中から「営業部」の社員だけを抽出したい場合、結果を表示したいセル（例えばA10）に次の数式を入力します。

例：構文: =FILTER(A2:F7, C2:C7=”営業部”)
この数式をセルA10に入力すると、次のように営業部に所属する3名分のデータだけが自動的に抽出されます。

ここで入力した数式はA10のみですが、結果はA10からF12まで自動的に展開されています。
この「あふれるように複数セルへ結果が広がる」現象を「スピル」（動的配列）と呼びます。B10やC10など、A10以外のセルには何も入力していない点がポイントで、A2:F7の元データを書き換えると、抽出結果も自動的に更新されます。

注：スピル結果が表示される範囲（A10:F12など）に既に別のデータが入力されていると、「#SPILL!」または「#スピル!」とエラーが表示され、抽出結果を展開できません。数式を入力するセルの右側・下側には、あらかじめ十分な空きセルを確保しておく必要があります。

例：F12に「東京」と記入したまま、Ａ10に式を挿入

複数条件で抽出する（OR条件・AND条件）

実務では、「営業部または経理部」のように複数条件のどれか一つを満たすデータを抽出したい場合と、「営業部に所属し、かつ年齢が30歳以下」のようにすべての条件を同時に満たすデータだけを抽出したい場合があります。FILTER関数では、この2つのケースを演算子の使い分けによって表現します。

OR条件（+）で複数条件を指定する

まず、「営業部」または「経理部」のどちらかに該当する社員を抽出したい場合は、条件同士を「+」（プラス）でつなぎます。

例：構文: =FILTER(A2:F7, (C2:C7=”営業部”)+(C2:C7=”経理部”))
この数式を入力すると、営業部の佐藤健・鈴木大輔・伊藤直樹と、経理部の田中美咲・渡辺花子を合わせた5名分のデータが抽出されます。総務部の高橋陽子は、どちらの条件にも当てはまらないため抽出結果から除外されます。

AND条件（*）で複数条件を指定する

次に、「営業部に所属し、かつ年齢が30歳以下」のように、すべての条件を同時に満たすデータだけを抽出したい場合は、条件同士を「*」（アスタリスク）でつなぎます。

例：構文: =FILTER(A2:F7, (C2:C7="営業部")*(D2:D7<=30))
この数式では、まず営業部に所属する佐藤健（28歳）・鈴木大輔（41歳）・伊藤直樹（30歳）の3名が条件の候補になりますが、次に「年齢30歳以下」という条件も同時に満たす必要があるため、41歳の鈴木大輔は対象から外れ、佐藤健と伊藤直樹の2名だけが抽出されます。

該当データがない場合の表示（空の場合の引数）

抽出条件に一致するデータが1件も存在しない場合、FILTER関数の第3引数「空の場合」を省略していると、「#CALC!」というエラーが返されます。例えば、サンプルデータに存在しない「人事部」を条件に指定すると、次のようになります。

例：構文: =FILTER(A2:F7, C2:C7="人事部")
結果：#CALC!（一致するデータがないため、エラーが返されます）

このエラー表示を避けたい場合は、第3引数の「空の場合」に、該当データがないときに表示したい文字列を指定します。条件に一致するデータが存在する場合は通常どおり抽出結果が表示され、1件も一致しない場合には、第3引数で指定した文字列が代わりに表示されます。

例：構文: =FILTER(A2:F7, C2:C7="人事部", "該当なし")
結果：該当なし（エラー値の代わりに、指定した文字列が表示されます）

現場で直面するFILTER関数・Excel運用の限界

FILTER関数はスピルを活用した非常に便利な関数ですが、実際の業務でExcelによるデータ抽出・共有を続けていくと、関数単体では解決できない運用上の限界に直面する場面が少なくありません。

まず、FILTER関数はExcel 2021およびMicrosoft 365でのみ使用できる関数であり、Excel 2019やExcel 2016など、それ以前のバージョンでは数式そのものが認識されずエラーになります。社内に古いバージョンのExcelを使っている担当者が一人でもいると、同じファイルを共有した際に「数式が動かない」「開いたら数式がそのまま文字列として表示されている」といったトラブルが起こりがちです。

次に、FILTER関数によるデータ抽出は、あくまで一つのブックの中で完結する処理です。そのため、複数のメンバーが同時に同じデータを見ながら、それぞれ異なる条件で絞り込んで確認したい、という使い方にはあまり向いていません。ファイルをメールやチャットでその都度共有していると、「誰が最新版を持っているのか分からない」「他の人が編集中でロックされて開けない」といった、Excelでのファイル共有につきものの混乱が発生しやすくなります。

さらに、FILTER関数はあくまで一つの表・一つのブックの中でのデータ抽出を得意とする関数であり、複数のプロジェクトや複数の担当者がそれぞれ個別に管理しているExcelファイルを横断して集計・分析するといった用途には限界があります。管理するファイルが増えるほど、集計のためにコピー＆ペーストを繰り返す作業が発生し、転記ミスや集計漏れといったヒューマンエラーのリスクも比例して大きくなっていきます。

まとめ

FILTER関数は「=FILTER(配列, 含む, [空の場合])」という3つの引数を組み合わせるだけで、条件に一致するデータを別の場所へ自動的に抽出できる、Excel 2021・Microsoft 365ならではの強力な関数です。オートフィルタのように条件を変えるたびに手動で操作し直す必要がなく、スピルによって抽出結果が自動的に展開されるため、名簿の絞り込みや日々の集計作業を大幅に効率化できます。

OR条件（+）とAND条件（*）を使い分ければ、一つの表の中でもかなり柔軟なデータ抽出が可能になります。一方で、Excelのバージョン差異や、複数人・複数ファイルにまたがるデータ集計といった場面では、FILTER関数だけでは解決できない運用上の限界があることも事実です。まずは本記事で紹介した基本の構文から試していただき、必要に応じて業務全体のデータ管理の方法も見直してみることをおすすめします。