ExcelでデータベースからF条件に合致するデータを抽出する作業は、業務で頻繁に発生します。
FILTER関数は、Excel 2021やMicrosoft 365で利用できる強力な関数で、条件に合うデータをすべて自動的に抽出し、1つのセルに数式を入れるだけで複数行のデータを一度に表示できる画期的な機能です。
従来は、VLOOKUP関数やフィルター機能を使って手動でデータを抽出する必要がありましたが、FILTER関数を使えば数式だけで自動的に抽出結果が更新されるため、作業効率が大幅に向上します。
しかし、FILTER関数を入力しても「#NAME?」エラーが表示される、関数の候補に表示されない、入力しても認識されないといったトラブルに遭遇することがあります。
FILTER関数が使えない主な原因は、Excelのバージョンが2019以前である、Microsoft 365を使用していない、数式の入力ミス、スピル機能に対応していないなどです。
本記事では、ExcelでFILTER関数が使えない具体的な原因を詳しく解説し、バージョンごとの対処法、フィルター機能による代替方法、他の関数を組み合わせた代用方法を紹介していきます。
どのバージョンのExcelでも効率的にデータ抽出ができる方法を習得しましょう。
ポイントは
・FILTER関数はExcel 2021またはMicrosoft 365でのみ使用可能
・Excel 2019以前ではフィルター機能やVLOOKUP関数で代用する
・COUNTIF関数とINDEX/MATCH関数を組み合わせて同様の機能を実現できる
です。
それでは詳しく見ていきましょう。
FILTER関数とは何か、どんな時に便利なのか
まずは、FILTER関数の基本的な機能と、どのような場面で活用できるのかを理解していきましょう。
FILTER関数の基本的な機能と特徴
FILTER関数は、指定した条件に基づいてデータ範囲をフィルター処理し、条件に合致するすべての行を自動的に抽出する関数です。
従来のVLOOKUP関数が1つの値しか返せないのに対し、FILTER関数は条件に合致する複数の行をまとめて抽出できます。
また、スピル機能により、1つのセルに数式を入力するだけで、結果が自動的に隣接するセルに展開されます。
元データが更新されると、FILTER関数の結果も自動的に更新されるため、常に最新の状態を保つことができます。
引数の説明：
配列：フィルター処理するデータ範囲（必須）
含む：抽出条件を指定（必須）
空の場合：条件に合うデータがない時の表示（省略可）
使用例：
=FILTER(A2:C10, B2:B10=”東京”, “該当なし”)
→B列が「東京」の行をA～C列すべて抽出
→該当がなければ「該当なし」と表示
=FILTER(A2:C10, (B2:B10=”東京”)*(C2:C10>=1000), “”)
→B列が「東京」かつC列が1000以上の行を抽出
→該当がなければ空白を表示
例えば、売上データから特定の支店のデータだけを抽出したい場合、FILTER関数を使えば数式1つで完結します。
従来の方法とFILTER関数の違い
FILTER関数が登場する前は、フィルター機能で手動抽出、VLOOKUP関数で1件ずつ検索、ピボットテーブルで集計といった方法が主流でした。
フィルター機能は直感的で使いやすいですが、手動操作が必要で、データが更新されるたびに再度フィルターをかけ直す必要があります。
VLOOKUP関数は、検索キーが1つの場合しか使えず、複数の該当データがある場合は最初の1件しか取得できません。
FILTER関数なら、条件に合致するすべてのデータを一度に抽出でき、元データが変更されても自動的に結果が更新されます。
| 方法 | 操作 | 複数行抽出 | 自動更新 |
|---|---|---|---|
| フィルター機能 | 手動 | ○可能 | ×手動で再実行 |
| VLOOKUP関数 | 数式 | ×1件のみ | ○自動 |
| ピボットテーブル | 手動設定 | ○可能 | △更新ボタン必要 |
| FILTER関数 | 数式 | ○可能 | ○完全自動 |
FILTER関数が使えない主な原因と確認方法
それでは、FILTER関数が使えない具体的な原因と、それぞれの確認方法を見ていきます。
Excelのバージョンが2019以前の場合
FILTER関数が使えない最も一般的な原因は、Excelのバージョンが2019以前であることです。
FILTER関数は、Excel 2021またはMicrosoft 365（旧Office 365）でのみ使用できる新しい関数です。
Excel 2019、Excel 2016、Excel 2013、Excel 2010では、FILTER関数自体が存在しないため使用できません。
FILTER関数を入力しても「#NAME?」エラーが表示され、関数の候補リストにも表示されません。
2. 左側メニューの「アカウント」を選択
3. 右側の「製品情報」セクションを確認
4. 「Excelのバージョン情報」をクリック
表示内容で判断：
・「Microsoft 365」と表示される
→FILTER関数が使用可能
・「Excel 2021」と表示される
→FILTER関数が使用可能
・「Excel 2019」「Excel 2016」と表示される
→FILTER関数は使用不可（代替方法が必要）
・バージョン番号で確認
Excel 2021：ビルド16.0以降
Microsoft 365：常に最新版
Excel 2019以前のバージョンを使用している場合は、Microsoft 365にアップグレードするか、後述する代替方法を使用する必要があります。
FILTER関数の入力ミスやエラー
Excelのバージョンが正しくてもFILTER関数が動作しない場合、数式の入力ミスや引数の指定ミスが原因の可能性があります。
よくある入力ミスとして、関数名のスペルミス（「FILLTER」「FILTAR」など）、引数の区切りが間違っている（カンマとセミコロンの混在）、括弧の閉じ忘れなどがあります。
また、「含む」引数で指定する条件式が正しくない場合も、期待通りの結果が得られません。
文字列を検索する場合は「”文字列”」のようにダブルクォーテーションで囲む必要があり、これを忘れるとエラーになります。
| エラーの種類 | 原因 | 解決方法 |
|---|---|---|
| #NAME? | バージョンが古い・スペルミス | バージョン確認・スペル修正 |
| #CALC! | 条件に合うデータがない | 第3引数で代替値を指定 |
| #VALUE! | 引数の指定が不正 | 範囲と条件を確認 |
| #SPILL! | 結果の展開先に既存データ | 展開先のセルを空にする |
スピル範囲に既存のデータがある場合
FILTER関数はスピル機能を使用するため、結果を展開する範囲に既存のデータがあると「#SPILL!」エラーが発生します。
FILTER関数は、抽出結果を数式を入力したセルから下方向および右方向に自動的に展開します。
その展開先のセルに既に何らかのデータや数式が入っている場合、スピルできないためエラーになります。
この問題を解決するには、FILTER関数の結果が展開される範囲のセルをすべて空にする必要があります。
FILTER関数が使えるかどうかの簡単なチェック方法：
1. 任意のセルに「=FI」と入力してみる
2. 関数の候補リストが表示される
3. リストに「FILTER」が表示されるか確認
表示される場合：
→Excel 2021またはMicrosoft 365を使用している
→FILTER関数が使用可能
表示されない場合：
→Excel 2019以前のバージョンを使用している
→FILTER関数は使用不可
→代替方法を使用する必要がある
この方法なら、わざわざバージョン情報を確認しなくても、FILTER関数が使えるかどうかを瞬時に判断できます。
FILTER関数が使えない場合の代替方法
続いては、Excel 2019以前のバージョンでFILTER関数と同様の機能を実現する方法を確認していきます。
フィルター機能による手動抽出
最もシンプルで確実な代替方法は、Excelの標準機能であるフィルター機能を使用することです。
データ範囲の見出し行を選択して「ホーム」タブの「並べ替えとフィルター」から「フィルター」をクリックします。
各列の見出しにフィルターボタンが表示されるので、条件を指定してデータを絞り込みます。
フィルター機能は手動操作が必要ですが、FILTER関数と同じように複数の条件でデータを抽出できます。
2. 「ホーム」タブ→「並べ替えとフィルター」
3. 「フィルター」をクリック
4. 見出し行にフィルターボタンが表示される
5. 絞り込みたい列のフィルターボタンをクリック
6. 条件を指定してデータを抽出
複数条件での絞り込み：
・複数の列でフィルターを設定
・AND条件（すべての条件を満たす）で絞り込まれる
抽出結果のコピー：
・絞り込んだデータを選択してコピー
・別シートに貼り付けて利用
フィルター機能は手動操作が必要で、元データが更新されるたびに再度フィルターをかけ直す必要がありますが、すべてのExcelバージョンで使用できる確実な方法です。
COUNTIF関数とVLOOKUP関数を組み合わせる方法
関数を使ってFILTER関数と同様の機能を実現するには、COUNTIF関数で条件に合う行に連番を振り、VLOOKUP関数で該当データを抽出する方法があります。
まず、元データの隣に作業列を作成し、COUNTIF関数で条件に合致する行に連番（1、2、3…）を振ります。
次に、抽出結果を表示するエリアで、ROW関数を使って連番を作成し、VLOOKUP関数で該当する行のデータを取得します。
この方法は少し複雑ですが、Excel 2010以降のすべてのバージョンで使用できます。
| 手順 | 使用関数 | 目的 |
|---|---|---|
| 1. 作業列作成 | COUNTIF関数 | 条件に合う行に連番を振る |
| 2. 検索キー作成 | 条件値&連番 | 一意の検索キーを作成 |
| 3. 連番生成 | ROW関数 | 1、2、3…の連番を作成 |
| 4. データ抽出 | VLOOKUP関数 | 該当データを取得 |
| 5. エラー処理 | IFERROR関数 | #N/Aエラーを非表示 |
INDEX関数とMATCH関数を組み合わせる方法
より柔軟な抽出を実現するには、INDEX関数とMATCH関数を組み合わせる方法も有効です。
COUNTIF関数で条件に合う行を判定し、MATCH関数で該当行の位置を特定し、INDEX関数でデータを取得します。
この方法は、VLOOKUP関数よりも自由度が高く、左側の列からもデータを取得できます。
複数の条件を指定する場合は、COUNTIFS関数を使用して複雑な条件式を作成できます。
=COUNTIF($B$2:B2,”東京”)
→条件に合致する行に1、2、3…と連番
抽出番号列：
=IF(条件判定列=0,””,SUM($条件判定列$2:条件判定列2))
→条件に合う行の抽出順番を表示
データ取得（INDEX/MATCH）：
=INDEX($A:$A,MATCH(ROW()-開始行,$抽出番号列,0))
→抽出番号に対応する行のデータを取得
エラー処理：
=IFERROR(INDEX/MATCH式,””)
→該当データがない場合は空白表示
この方法は設定が複雑ですが、一度作成すれば、条件を変更するだけで自動的に抽出結果が更新されます。
テーブル機能と構造化参照を活用する方法
データをテーブル化しておくと、範囲指定が自動的に拡張され、数式のメンテナンスが楽になります。
データ範囲を選択して「挿入」タブの「テーブル」をクリックし、テーブルに変換します。
テーブルを使用すると、列名で範囲を参照できる構造化参照が使えるようになります。
例えば、「=COUNTIF(テーブル1[支店],”東京”)」のように、列名で指定できるため、数式が分かりやすくなります。
FILTER関数の代替方法の選び方：
手動操作でも問題ない場合：
→フィルター機能を使用
→最もシンプルで確実
更新頻度が高く自動化したい場合：
→COUNTIF + VLOOKUP方式
→比較的簡単に実装できる
複雑な条件で柔軟に抽出したい場合：
→INDEX + MATCH方式
→高度だが自由度が高い
データが頻繁に追加される場合：
→テーブル化 + 構造化参照
→範囲の自動拡張で管理が楽
複数のパソコンで共有する場合：
→代替方法を使用
→バージョンの違いを気にしなくて済む
それぞれの状況に応じて、最適な方法を選択することが重要です。
まとめ エクセルでFILTER関数の代用・代わりの関数や方法（バージョン：Excel 2019：代替方法：フィルター機能：VLOOKUP）
エクセルでFILTER関数が使えない場合の対処法をまとめると
・バージョンの確認と対応：Excel 2021またはMicrosoft 365でのみ使用可能、「ファイル」→「アカウント」でバージョン確認、Excel 2019以前では代替方法を使用、Microsoft 365へのアップグレードを検討
・エラーの種類と対処法：「#NAME?」エラーはバージョンが古いかスペルミス、「#CALC!」エラーは第3引数で代替値を指定、「#SPILL!」エラーは展開先のセルを空にする、文字列検索時は””で囲む
・フィルター機能による代替：「ホーム」→「並べ替えとフィルター」→「フィルター」、複数列で条件を設定してAND条件で絞り込み、絞り込んだデータをコピーして別シートに貼り付け、すべてのExcelバージョンで使用可能
・関数による代替方法：COUNTIF関数で条件に合う行に連番を振る、VLOOKUP関数で該当データを取得、INDEX/MATCH関数で柔軟な抽出を実現、IFERROR関数でエラー表示を非表示
・テーブル機能の活用：データをテーブル化して範囲を自動拡張、構造化参照で列名を使った数式作成、データ追加時の数式メンテナンスが不要
これらの方法を理解しておくことが重要です。
職場で複数のExcelバージョンが混在している場合は、FILTER関数ではなく代替方法を使用することで、どのパソコンでもエラーなく開けるファイルを作成できます。
最も重要なのは、自分の使用環境と共有先の環境を把握し、適切な方法を選択することです。
ExcelのFILTER関数と代替方法を適切に使い分けて、効率的なデータ抽出と分析作業を実現していきましょう！
