【Excel】FILTER関数で「条件に一致するデータなし」の表示!Excelの空結果エラーを制御する方法

FILTER関数で検索条件に一致するデータがない場合、「#CALC!」エラーが表示されることがあります。このエラーは、意図しない結果として表示されるため、見栄えが悪くなることも少なくありません。しかし、FILTER関数の引数を活用することで、このエラーを制御し、より分かりやすいメッセージを表示させることが可能です。この記事では、FILTER関数で「条件に一致するデータなし」というメッセージを返す方法を解説します。Excelの空結果エラーを効果的に制御し、見やすい表を作成できるようになりましょう。

FILTER関数は、指定した条件に一致する行を抽出する便利な関数です。しかし、条件に合うデータが一つも存在しない場合、デフォルトではエラー値が表示されます。この挙動を理解し、適切に対処することで、レポートやダッシュボードの質を高めることができます。

FILTER関数で#CALC!エラーが発生する仕組み

FILTER関数は、指定した配列(データ範囲)から、指定した条件に合致する行を抽出します。その構文は以下の通りです。

FILTER(配列, 含む, [if_empty])

ここで、各引数の意味は以下の通りです。

配列:抽出したいデータが含まれる範囲を指定します。

含む:配列からどの行を抽出するかを判定する条件を指定します。TRUE/FALSEの論理値で返される配列を指定します。

if_empty:省略可能な引数です。配列から条件に一致するデータが一つも見つからなかった場合に表示する値を指定します。この引数を指定しない場合、Excelでは#CALC!エラーが表示されます。

つまり、FILTER関数で#CALC!エラーが表示されるのは、`if_empty`引数を省略した状態で、条件に一致するデータが一つもない場合に限られます。この`if_empty`引数を活用することで、エラー表示を制御できるのです。

FILTER関数で「条件に一致するデータなし」と表示する手順

FILTER関数で条件に一致するデータがない場合に、エラーではなく分かりやすいメッセージを表示させるには、`if_empty`引数を使用します。ここでは、具体的な手順を解説します。

1. データ範囲と条件を設定する
   まず、FILTER関数で抽出したいデータ範囲と、抽出条件を設定します。例えば、A1:C10に商品リストがあり、B列(商品名)で特定のキーワードを検索するとします。
2. if_empty引数を追加する
   FILTER関数の構文の最後に、3番目の引数として`if_empty`を指定します。ここに、表示したいメッセージを文字列として入力します。メッセージはダブルクォーテーション(“”)で囲む必要があります。例えば、「”該当なし”」のように指定します。
3. 数式を完成させる
   例えば、A1:C10の範囲から、B列がD1セル(検索キーワード)と一致するデータを抽出し、一致するデータがない場合は「該当なし」と表示させたい場合、数式は以下のようになります。

=FILTER(A1:C10, B1:B10=D1, "該当なし")

この数式を入力することで、D1セルに指定した商品名がB列に存在しない場合、#CALC!エラーではなく「該当なし」という文字列が表示されます。これにより、ユーザーはデータがないことを明確に認識できます。

if_empty引数の応用例

`if_empty`引数は、「条件に一致するデータなし」というメッセージを表示させるだけでなく、様々な用途で活用できます。以下にいくつかの応用例を紹介します。

空文字列を表示して何も表示させない

場合によっては、データがない場合に何も表示させたくないことがあります。そのような場合は、`if_empty`引数に空文字列(“”)を指定します。

=FILTER(A1:C10, B1:B10=D1, "")

この数式では、条件に一致するデータがない場合、セルは空白になります。エラー値が表示されないため、見た目がすっきりします。

特定の数値や記号を表示する

データがない場合に、特定の数値(例えば0)や記号(例えば-)を表示させたい場合も、同様に`if_empty`引数に指定します。

=FILTER(A1:C10, B1:B10=D1, 0)

または

=FILTER(A1:C10, B1:B10=D1, "-")

これらの数式は、集計結果などで「該当データがない場合は0とする」といった処理を行いたい場合に便利です。

別の関数と組み合わせる

`if_empty`引数には、単なる文字列だけでなく、別の関数を指定することも可能です。例えば、データがない場合に、特定のセルの値を表示させたい場合などが考えられます。

=FILTER(A1:C10, B1:B10=D1, IFERROR(E1, "データなし"))

この例では、もしE1セルに何らかのエラーがある場合でも、「データなし」と表示されます。`if_empty`引数と他の関数を組み合わせることで、より複雑な条件分岐や表示制御が可能になります。

FILTER関数における注意点とよくある誤解

FILTER関数は非常に便利ですが、いくつか注意すべき点や、よくある誤解があります。これらを理解しておくことで、より効果的に関数を使用できます。

if_empty引数に数式を入力する場合の注意

`if_empty`引数に文字列以外の値を指定する場合、その値がExcelの数式として解釈されることがあります。例えば、`if_empty`に「=SUM(F1:F5)」と入力すると、Excelはその文字列を数式として実行しようとします。

意図した通りに文字列を表示させたい場合は、必ずダブルクォーテーション(“”)で囲む必要があります。数値やTRUE/FALSEなどの論理値は、ダブルクォーテーションなしで直接指定できます。

#CALC!エラーと#N/Aエラーの違い

FILTER関数で条件に一致するデータがない場合に表示されるのは#CALC!エラーです。一方、VLOOKUP関数などで検索値が見つからなかった場合に表示されるのは#N/Aエラーです。これらは異なるエラーであり、原因も異なります。

FILTER関数の#CALC!エラーは、`if_empty`引数を指定することで回避できます。#N/Aエラーは、検索対象のデータが存在しない場合に発生するため、検索値や検索範囲を見直す必要があります。

配列数式としての挙動

FILTER関数は動的配列関数です。そのため、結果が複数のセルに展開されます(スピル)。このスピル範囲に別のデータが存在する場合、#SPILL!エラーが発生します。`if_empty`引数は、この#SPILL!エラーとは直接関係ありません。

`if_empty`引数は、あくまで「抽出されるべきデータが一つもない」という状況に対して、どのような値を返すかを制御するものです。スピルエラーは、結果を表示する先のセルにデータがある場合に発生します。

Excelのバージョンによる違い

FILTER関数は、Microsoft 365のExcelで利用できる比較的新しい関数です。Excel 2019以前のバージョンでは利用できません。

もし、Excel 2019以前のバージョンで同様の処理を行いたい場合は、配列数式(Ctrl+Shift+Enterで確定する数式)や、IFERROR関数と組み合わせたVLOOKUP関数、あるいはPower Queryなどの機能を利用する必要があります。ただし、これらの代替手段はFILTER関数ほどシンプルではない場合があります。

FILTER関数と他の検索関数との比較

FILTER関数は、条件に一致するデータを抽出する点で、VLOOKUP関数やXLOOKUP関数と似ている部分もあります。しかし、その目的と機能には明確な違いがあります。

項目	FILTER関数	VLOOKUP関数	XLOOKUP関数
主な目的	条件に一致する複数の行を抽出	指定した値に対応する最初の行の値を検索	指定した値に対応する行の値を検索(複数列、前後検索も可能)
返り値	条件に一致する行の配列(動的配列)	条件に一致する最初の行の値(単一の値)	条件に一致する行の値(単一または配列)
複数一致の扱い	すべて抽出	最初の1件のみ	すべて抽出(範囲指定による)
「データなし」の制御	if_empty引数で指定可能	IFERROR関数で制御	if_not_found引数で指定可能
利用可能バージョン	Microsoft 365	全バージョン	Microsoft 365

FILTER関数は、特定の条件を満たすデータを一覧で取得したい場合に最適です。一方、VLOOKUP関数は、特定のキーに対応する値だけを取得したい場合に、XLOOKUP関数はより柔軟な検索を行いたい場合に適しています。

「データなし」の場合の表示制御についても、FILTER関数には`if_empty`引数、XLOOKUP関数には`if_not_found`引数があり、直接的に指定できる点が共通しています。VLOOKUP関数では、IFERROR関数などを別途組み合わせる必要があります。

まとめ

FILTER関数で条件に一致するデータがない場合に表示される#CALC!エラーは、`if_empty`引数を使うことで簡単に制御できます。この引数に「条件に一致するデータなし」といったメッセージや、空文字列などを指定することで、より見やすく、分かりやすい結果を表示させることが可能です。

本記事では、FILTER関数の`if_empty`引数の使い方、応用例、そして注意点について解説しました。この機能を活用することで、レポートやダッシュボードの質を向上させることができます。

FILTER関数はMicrosoft 365で利用できる強力な関数です。ぜひ、`if_empty`引数を活用して、Excelでのデータ表示をより洗練させてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】