Excel FILTER関数の使い方｜複数条件・別シート・使えない原因まで解説

本記事には広告（PR）が含まれます。
くわしくは プライバシーポリシー をご確認ください。

Excelの一覧表から、

「営業部の人だけ取り出したい」
「30歳以上など、複数の条件で絞り込みたい」
「別シートに条件に合うデータだけ表示したい」

といったことはありませんか？

そんなときに便利なのがFILTER関数です。

FILTER関数を使うと、指定した条件に一致するデータだけを別の場所へ自動で抽出できます。

さらに、複数の条件を組み合わせたり、特定の文字を含むデータを探したり、SORT関数と組み合わせて抽出結果を並び替えたりすることもできます。

一方で、

「FILTER関数が使えない」
「#SPILL!と表示された」
「古いExcelなのでFILTER関数が見つからない」

と困ることもあるでしょう。

この記事では、FILTER関数の基本的な使い方から、複数条件・部分一致・日付・別シート・並び替え、使えない場合の原因や代用方法まで初心者向けにわかりやすく解説します。

FILTER関数の基本的な使い方

FILTER関数とは？

FILTER関数は、指定した条件に一致するデータだけを抽出する関数です。

たとえば、社員名簿から「営業部の人だけを一覧表示したい」とします。

通常のフィルター機能を使って絞り込むこともできますが、通常のフィルターでは元の表の不要な行を一時的に非表示にします。

一方、FILTER関数では、元の一覧表をそのまま残しながら、条件に一致したデータを別の場所へ表示できます。

元データを変更すると抽出結果も自動的に変わるため、

• 社員名簿
• 出欠表
• 商品一覧
• 予定表
• 地区・班ごとの名簿

など、内容が更新される表にも便利です。

FILTER関数の基本構文

FILTER関数の基本的な書き方は次のとおりです。

=FILTER(配列,含む,[空の場合])

それぞれの引数には、次の内容を指定します。

• 配列：抽出したい元データの範囲
• 含む：どのデータを抽出するかを決める条件
• 空の場合：条件に一致するデータがなかった場合に表示する内容

たとえば、A2:C11に社員データがあり、B列に部署名が入力されているとします。

「営業部」の人だけを抽出する場合は、次のように入力します。

=FILTER(A2:C11,B2:B11="営業部","該当なし")

この数式では、

• A2:C11からデータを取り出す
• B2:B11が「営業部」の行だけを対象にする
• 該当するデータがなければ「該当なし」と表示する

という意味になります。

FILTER関数では、条件に一致する人が複数いても、数式を1つ入力するだけで結果が自動的に下方向へ展開されます。

この仕組みをスピルと呼びます。

FILTER関数はいつから使える？

FILTER関数は比較的新しいExcelで利用できる関数です。

主に、

• Microsoft 365
• Excel 2021
• Excel 2024

などで利用できます。

Excel 2019以前ではFILTER関数を利用できません。

FILTER関数を入力しても認識されない場合は、使用しているExcelのバージョンを確認してみましょう。

古いExcelでFILTER関数が使えない場合の代用方法は、後ほど紹介します。

FILTER関数で条件を指定して抽出する方法

FILTER関数では、単純な1つの条件だけでなく、複数条件や部分一致などさまざまな条件を指定できます。

複数条件で抽出する方法

FILTER関数でよく使われるのが、AND条件とOR条件です。

AND条件｜「営業部」かつ「30歳以上」

複数の条件をすべて満たすデータを抽出したい場合は、条件同士を * でつなぎます。

たとえば、

• 部署が「営業部」
• 年齢が30歳以上

の両方を満たす人を抽出する場合は、次のように入力します。

=FILTER(A2:C11,(B2:B11="営業部")*(C2:C11>=30),"該当なし")

* は、FILTER関数の条件では**「かつ（AND）」**のような役割をします。

そのため、

営業部 かつ 30歳以上

のデータだけが抽出されます。

OR条件｜「営業部」または「経理部」

どちらか一方の条件を満たせばよい場合は、条件同士を + でつなぎます。

=FILTER(A2:C11,(B2:B11="営業部")+(B2:B11="経理部"),"該当なし")

この数式では、

営業部 または 経理部

の人を抽出できます。

複数条件を指定するときは、

• AND条件 → *
• OR条件 → +

と覚えておくとわかりやすいでしょう。

「含まない」条件で抽出する方法

特定のデータだけを除外したい場合は、<> を使います。

たとえば、「営業部以外」の人を抽出する場合は次のように入力します。

=FILTER(A2:C11,B2:B11<>"営業部","該当なし")

<> は**「等しくない」**という意味です。

そのため、

B2:B11<>"営業部"

は、「B列が営業部ではない」という条件になります。

「○○を除外した一覧を作りたい」ときに便利です。

部分一致で「○○を含む」データを抽出する方法

「営業1課」「営業2課」のように、セルの一部に同じ文字が含まれている場合があります。

このようなデータをまとめて抽出したい場合は、SEARCH関数とISNUMBER関数を組み合わせます。

たとえば、部署名に「営業」という文字を含むデータを抽出する場合は次のように入力します。

=FILTER(A2:C11,ISNUMBER(SEARCH("営業",B2:B11)),"該当なし")

SEARCH関数で「営業」という文字がある位置を探し、ISNUMBER関数で検索結果が数値かどうかを判定しています。

これにより、

• 営業部
• 営業1課
• 営業企画

など、「営業」を含むデータをまとめて抽出できます。

完全一致ではなく、一部の文字を含んでいるかどうかで絞り込みたい場合に便利です。

FILTER関数で日付を条件に抽出する

FILTER関数では、日付も条件として指定できます。

たとえば、A列に予定日、B列に予定内容が入力されている表から、今日以降の予定だけを抽出したい場合は次のように入力します。

=FILTER(A2:B100,A2:A100>=TODAY(),"予定なし")

TODAY関数は今日の日付を返します。

そのため、

A2:A100>=TODAY()

は「今日以降の日付」という条件になります。

日付が変われば抽出結果も自動的に変わるため、予定表や期限管理にも便利です。

空白セルが0と表示される場合

FILTER関数で予定表などを抽出すると、元データの空白部分が抽出結果では「0」と表示されることがあります。

0を表示したくない場合は、IF関数を組み合わせる方法があります。

=FILTER(IF(A2:B100="","",A2:B100),A2:A100>=TODAY(),"予定なし")

この数式では、元データが空白の場合に "" を返し、それ以外は元の値を表示しています。

予定内容などに空白が含まれる表では、見た目を整えたい場合に利用できます。

テーブルとFILTER関数を組み合わせる

Excelの元データをテーブルにしておくと、FILTER関数との組み合わせが便利です。

通常のセル範囲では、

A2:C11

のように範囲を指定します。

しかし、データを追加して12行目、13行目と増えていく場合、その都度参照範囲を見直す必要が出てくることがあります。

テーブルなら、データを追加すると範囲も自動的に拡張されます。

たとえば「社員テーブル」という名前のテーブルから営業部だけを抽出する場合は、次のように指定できます。

=FILTER(社員テーブル,社員テーブル[部署]="営業部","該当なし")

列名を使って条件を指定できるため、何を参照している数式なのかもわかりやすくなります。

毎月データが追加される一覧表などでは便利な方法です。

※日付が「43191」のような数字で表示された場合
FILTER関数で抽出した日付がシリアル値で表示されることがあります。その場合は、抽出結果のセルの表示形式を「日付」に変更してください。Excelでは日付を内部的に連続した数値（シリアル値）として管理しています。

FILTER関数で別シートから抽出する方法

FILTER関数は、元データとは別のシートに抽出結果を表示することもできます。

たとえば、Sheet1に社員一覧があり、別シートに「営業部」の人だけを表示したい場合は次のように入力します。

=FILTER(Sheet1!A2:C11,Sheet1!B2:B11="営業部","該当なし")

Sheet1! は、「Sheet1にあるセルを参照する」という意味です。

この方法を利用すれば、元データを1つのシートで管理しながら、

• 営業部だけの一覧
• 経理部だけの一覧
• 出席者だけの一覧
• 地区別・班別の名簿

などを別シートに表示できます。

元データが変更されれば、FILTER関数で抽出した側も自動的に更新されます。

FILTER関数の結果を並び替える方法

FILTER関数は、条件に一致するデータを抽出するための関数です。

そのため、FILTER関数だけでは抽出結果の並び順を指定できません。

抽出した結果を並び替えたい場合は、SORT関数と組み合わせます。

たとえば、営業部の人だけを抽出し、第3列の「年齢」が高い順に並べたい場合は次のように入力します。

=SORT(FILTER(A2:C11,B2:B11="営業部","該当なし"),3,-1)

まず、

FILTER(A2:C11,B2:B11="営業部","該当なし")

で営業部のデータを抽出し、その結果を外側のSORT関数で並び替えています。

3 は抽出結果の3列目、-1 は降順を表します。

FILTER関数とSORT関数を組み合わせることで、必要なデータだけを抽出し、さらに見やすい順番へ自動で並び替えることができます。

FILTER関数が使えないときの原因と対処法

FILTER関数を入力してもうまく表示されない場合は、いくつかの原因が考えられます。

FILTER関数がExcelにない

まず確認したいのが、使用しているExcelのバージョンです。

FILTER関数はExcel 2019以前では利用できません。

数式を入力してもFILTER関数が候補に表示されない場合は、Excelのバージョンを確認してみましょう。

古いExcelを利用している場合は、後ほど紹介するINDEX関数などを使った代用方法や、通常のフィルター機能を利用できます。

抽出範囲と条件範囲の大きさが違う

FILTER関数では、抽出する範囲と条件として指定する範囲の行数を合わせる必要があります。

たとえば、次の数式では範囲の大きさが違います。

=FILTER(A2:C11,B2:B10="営業部")

A2:C11は10行ありますが、B2:B10は9行しかありません。

次のように範囲をそろえます。

=FILTER(A2:C11,B2:B11="営業部","該当なし")

FILTER関数がうまく動かない場合は、抽出範囲と条件範囲の開始行・終了行がそろっているか確認してみましょう。

#SPILL!（#スピル!）エラーが表示される

FILTER関数は、条件に一致する結果が複数ある場合、周囲のセルへ自動的に展開します。

しかし、結果を表示する予定の範囲に、

• 文字や数値
• 数式
• スペースなどの見えにくい文字
• 結合セル

などがあると、結果を展開できず**#SPILL!（#スピル!）エラー**になることがあります。

なお、本当に何も入力されていない空白セルであれば問題ありません。

FILTER関数を入力したセルの周囲を確認し、抽出結果を表示するための十分なスペースを確保しましょう。

条件を指定する列が間違っている

条件として参照している列が正しいかも確認しましょう。

たとえば、B列に「営業部」「経理部」などの部署名が入っているのに、

B2:B11=28

のように指定しても、B列には「28」という値がないため一致しません。

年齢が28歳の人を抽出したい場合は、年齢が入力されているC列を条件範囲にして、

=FILTER(A2:C11,C2:C11=28,"該当なし")

のように指定します。

FILTER関数では、

「どの範囲から取り出すか」だけでなく、「どの列を条件として調べているか」

を確認することが大切です。

FILTER関数が使えないExcelで代用する方法

Excel 2019以前など、FILTER関数を利用できない環境でも、ほかの関数やExcelの機能を使って条件に一致するデータを抽出できます。

INDEX・SMALL・IF関数を組み合わせる

INDEX関数・SMALL関数・IF関数などを組み合わせると、条件に一致するデータを順番に取り出すことができます。

たとえば、B列が「営業部」の人の氏名をA列から取り出す場合は次のような数式を使用できます。

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$11="営業部",ROW($B$2:$B$11)),ROW(A1))),"")

この数式では、

1. IF関数で「営業部」に一致する行を調べる
2. ROW関数で該当する行番号を取得する
3. SMALL関数で行番号を小さい順に取り出す
4. INDEX関数で該当する行の氏名を表示する

という処理をしています。

FILTER関数との大きな違いは、数式を1つ入力しただけでは結果が自動的にスピルしないことです。

1件目、2件目、3件目……と順番に表示するため、数式を下方向へオートフィルして使用します。

また、古いExcelでは配列数式として、Ctrl + Shift + Enterで確定する必要がある場合があります。

INDEX関数について詳しく知りたい方は、こちらの記事も参考にしてください。

▶ INDEX・MATCH関数の使い方とVLOOKUPとの違い｜柔軟な検索をやさしく解説！

検索に利用できる関数にはVLOOKUP関数もあります。

ただし、VLOOKUP関数は「条件に一致する複数行を一覧として抽出する」というFILTER関数とは用途が異なります。

▶ 初めてのVLOOKUP関数｜よくあるエラー・複数条件・部分一致の対処法をやさしく解説！

通常のフィルター機能で代用する

一度だけ条件に一致するデータを確認したい場合は、無理に複雑な関数を組み合わせなくても、Excel標準のフィルター機能で対応できます。

たとえば社員一覧から「営業部」だけを確認したい場合は、部署列のフィルターボタンから「営業部」を選ぶだけです。

ただし、通常のフィルター機能では元の表を絞り込んで表示します。

別の場所へ抽出結果を自動表示したい場合はFILTER関数のほうが便利です。

用途に合わせて使い分けましょう。

Power Queryを使う方法もある

データ量が多く、毎回同じ条件で取り込み・整理・抽出を行う場合は、Power Queryを利用する方法もあります。

Power Queryでは、データを取り込んだあとに条件を指定して必要な行だけを残すことができます。

関数を使った抽出とは仕組みが異なりますが、大量データを繰り返し加工する場合には便利な選択肢です。

Power Queryについてはこちらの記事にて紹介しています。
▶Power Queryとは？初心者向けに「できること・使い方・マクロとの違い」をやさしく解説

FILTER関数をドロップダウンと組み合わせる

FILTER関数は、単に一覧表からデータを抽出するだけでなく、ほかのExcel機能と組み合わせることでさらに活用できます。

たとえば、

「都道府県を選択すると、その都道府県に該当する市区町村だけを表示する」

といった連動ドロップダウンにも応用できます。

FILTER関数とドロップダウンを組み合わせた方法については、以下の記事で詳しく紹介しています。

▶ Excel FILTER関数×ドロップダウン連動の使い方｜VBAで入力規則を自動切り替え！

FILTER関数についてよくある質問

FILTER関数で複数条件を指定できますか？

=FILTER(A2:C11,(B2:B11="営業部")*(C2:C11>=30),"該当なし")

FILTER関数で別シートから抽出できますか？

=FILTER(Sheet1!A2:C11,Sheet1!B2:B11="営業部","該当なし")

FILTER関数で部分一致できますか？

=FILTER(A2:C11,ISNUMBER(SEARCH("営業",B2:B11)),"該当なし")

FILTER関数で#SPILL!と表示されるのはなぜですか？

FILTER関数はExcel 2019でも使えますか？

まとめ｜FILTER関数なら条件に合うデータを自動で抽出できる

FILTER関数を使うと、一覧表から条件に一致するデータだけを別の場所へ自動的に抽出できます。

基本となる数式は次の形です。

=FILTER(抽出範囲,条件,"該当なし")

さらに、

• *を使ったAND条件
• +を使ったOR条件
• <>を使った除外
• SEARCH関数を使った部分一致
• TODAY関数を使った日付による抽出
• 別シートからの抽出
• SORT関数を使った並び替え

などにも応用できます。

一度だけデータを絞り込むのであれば、通常のフィルター機能でも十分です。

一方、元データが変更されるたびに抽出結果も自動で更新したい場合は、FILTER関数が便利です。

まずは「営業部だけを抽出する」といった簡単な例から試してみて、慣れてきたら複数条件や別シートへの抽出にも挑戦してみてください。

【関連記事】

• Excel FILTER関数×ドロップダウン連動の使い方｜VBAで入力規則を自動切り替え！
• INDEX・MATCH関数の使い方とVLOOKUPとの違い｜柔軟な検索をやさしく解説！
• XLOOKUP関数の使い方まとめ｜対応バージョン・エラー対策・複数条件検索もやさしく解説！

※外部リンク※

• FILTER 関数 |Microsoft サポート
• VLOOKUP、INDEX、または MATCH を使って値を検索する |Microsoft サポート