ExcelのFILTER関数で、データがあるときは正常に一覧が表示されるのに、条件に合う行が1件もないときだけ「#CALC!」になることがあります。
この場合は、FILTER関数の第3引数[if_empty]に「0件だったときに表示する内容」を指定すれば解決できます。説明文を出すなら"該当なし"、何も表示したくないなら""を指定します。Microsoftも、空の配列を返す可能性がある場合は第3引数を使うよう案内しています。
たとえば、A2:C20から「B列がE2と一致する行」を抽出し、0件なら「該当なし」と表示する式は次のとおりです。
=FILTER(A2:C20,B2:B20=E2,"該当なし")重要なのは、#CALC!を含むすべてのエラーを一律に隠すのではなく、FILTER関数の第3引数で「該当データが0件」のケースだけを処理することです。
ExcelのFILTERで該当なしが#CALC!になる理由
FILTER関数の基本構文は次のとおりです。
=FILTER(array,include,[if_empty])各引数の役割は次のようになります。
| 引数 | 役割 | 例 |
|---|---|---|
array | 抽出対象となる範囲 | A2:C20 |
include | どの行・列を抽出するかを判定する条件 | B2:B20=E2 |
[if_empty] | 条件に一致するデータがない場合に返す値 | "該当なし" |
第3引数の[if_empty]は省略できます。ただし、条件に一致するデータが1件もなく、FILTERが空の配列を返そうとした場合、Excelでは空の配列をそのまま扱えないため#CALC!になります。
MicrosoftのFILTER関数の説明でも、空の結果になる可能性がある場合は第3引数を指定し、省略すると#CALC!になるとされています。
つまり、
=FILTER(A2:C20,B2:B20=E2)では0件のときに#CALC!になる可能性がありますが、
=FILTER(A2:C20,B2:B20=E2,"該当なし")としておけば、0件だった場合だけ「該当なし」と表示できます。
0件のときに「該当なし」と表示する方法
たとえば、次のようなデータがA列からC列にあり、E2セルに検索条件を入力するとします。
| A列 | B列 | C列 |
|---|---|---|
| 商品001 | 東京 | 120 |
| 商品002 | 大阪 | 85 |
| 商品003 | 東京 | 140 |
B列がE2セルと一致する行を抽出する場合は、次の式を使用します。
=FILTER(A2:C20,B2:B20=E2,"該当なし")E2が「東京」であれば、B列が東京の行が抽出されます。
一方、E2に対象データが存在しない条件を入力した場合は、
該当なしと表示されます。
帳票や検索画面のように、利用者へ「検索は正常に行われたが、結果が0件だった」と伝えたい場合は、この方法が分かりやすいでしょう。
表示する文言は用途に合わせて変更できます。
=FILTER(A2:C20,B2:B20=E2,"該当するデータはありません")=FILTER(A2:C20,B2:B20=E2,"対象なし")=FILTER(A2:C20,B2:B20=E2,"0件")社内資料や一覧表であれば「該当なし」、一般利用者向けの検索画面であれば「該当するデータはありません」のように、用途に応じて選ぶと分かりやすくなります。
FILTERで該当なしのとき何も表示しない方法
0件であることを知らせる必要がなく、セル上では何も表示したくない場合は、第3引数に空文字列""を指定します。
=FILTER(A2:C20,B2:B20=E2,"")条件に一致する行があれば通常どおり一覧が表示され、一致する行がなければ空文字列が返ります。
Microsoftが示しているFILTER関数の例でも、第3引数に""を指定して、一致するデータがない場合に空文字列を返す方法が使われています。
空欄と説明文は用途で使い分ける
どちらを選ぶべきか迷った場合は、次のように考えると分かりやすくなります。
| 用途 | 第3引数の例 | 向いている理由 |
|---|---|---|
| 検索結果一覧 | "該当なし" | 正常に検索できたことが分かる |
| 帳票 | "対象データなし" | 0件であることを明示できる |
| 補助計算用の一覧 | "" | 不要な文字を表示しない |
| ダッシュボード | ""または説明文 | レイアウトや利用者に合わせられる |
特に他の人が操作するExcelでは、完全な空欄より「該当なし」と表示した方が、数式の故障と誤解されにくくなります。
IFERRORで全部隠すよりFILTERの第3引数を使う
エラーを消したいと考えると、次のような式を作りたくなることがあります。
=IFERROR(FILTER(A2:C20,B2:B20=E2),"")しかし、「条件に一致する行が0件」という正常なケースを処理したいだけなら、まずFILTERの第3引数を使う方が原因を切り分けやすくなります。
=FILTER(A2:C20,B2:B20=E2,"")FILTERでは、0件以外にも条件式そのもののエラーや参照先の問題などが発生する可能性があります。すべてを同じ表示に置き換えると、本来修正すべき数式の問題まで見えにくくなります。
0件は第3引数で処理し、それ以外のエラーは原因を確認するという分け方が実務では扱いやすい方法です。
第3引数を設定しても直らない場合に確認すること
FILTER関数で発生するエラーが、すべて「該当データが0件」という意味ではありません。
第3引数に""や"該当なし"を指定しても直らない場合は、別の原因を確認します。
| 症状 | 主な確認ポイント | 第3引数で解決するか |
|---|---|---|
0件のとき#CALC! | [if_empty]が省略されていないか | する |
include側にエラーがある | 条件式や参照セルを確認 | しない |
#SPILL! | 結果を展開するセルが塞がっていないか | しない |
別ブック参照で#REF! | 参照元ブックが閉じていないか | しない |
| FILTERが利用できない | Excelの対応バージョンを確認 | しない |
| 条件範囲がおかしい | arrayとincludeの大きさを確認 | しない |
「該当なし」と表示されるべきケースと、数式自体に問題があるケースを分けて考えるのがポイントです。
includeの大きさが抽出対象と合っているか確認する
FILTER関数のincludeには、抽出対象の配列に対応したTRUE/FALSEの配列を指定します。
Microsoftの構文説明では、includeの高さまたは幅は、対象となる配列と一致する必要があります。
たとえば、
=FILTER(A2:C20,B2:B20=E2,"該当なし")であれば、
抽出対象：A2:C20
条件範囲：B2:B20となり、どちらも2行目から20行目までを対象としています。
一方で、次のように条件範囲だけ短くすると、正しい条件配列になりません。
=FILTER(A2:C20,B2:B19=E2,"該当なし")このような問題は「条件に合う行が0件」とは別です。
"該当なし"を指定しているのに期待どおり動かない場合は、まずarrayとincludeの開始行・終了行を見比べてください。
includeの条件自体がエラーなら「該当なし」にはならない
もう一つ注意したいのが、includeとして使用している数式やセルにエラーが含まれている場合です。
Microsoftは、includeの値に#N/Aや#VALUE!などのエラーが含まれていたり、TRUE/FALSEに変換できなかったりする場合、FILTER関数もエラーを返すと説明しています。
たとえば、
=FILTER(A2:C20,B2:B20=E2,"該当なし")という式であっても、条件判定に使うデータ側にエラーがあれば、第3引数による「0件処理」とは別に原因を調べる必要があります。
第3引数は、
「正常に条件判定した結果、一致するものがなかった」
というケースを処理するためのものと考えると整理しやすくなります。
#SPILL!は「該当なし」とは別のエラー
FILTER関数は、条件に一致した複数の結果を周囲のセルへ自動的に展開する「動的配列」の仕組みを利用します。
そのため、結果を表示する予定のセルに既存の値などがあり、展開できない場合には#SPILL!が発生します。Microsoftも、動的配列の出力範囲が他のデータで塞がれている場合は#SPILL!になると説明しています。
この場合、
=FILTER(A2:C20,B2:B20=E2,"該当なし")と第3引数を設定しても解決しません。
FILTER関数を入力したセルの下や右側など、結果が展開される範囲に不要な文字や数式がないか確認します。
別ブックを参照しているFILTERでは#REF!にも注意
FILTER関数を別のExcelブックのデータに対して使用している場合は、さらに注意が必要です。
Microsoftによると、ブック間での動的配列の利用には制限があり、この方法は参照元と参照先の両方のブックが開いている場合にサポートされます。参照元のブックを閉じた状態でリンクされた動的配列数式を更新すると、#REF!になることがあります。
そのため、
「昨日まではFILTERで表示できたのに、今日はエラーになった」
という場合に、すぐ「該当するデータがなくなった」と判断しないことが重要です。
別ブックを参照している場合は、まず参照元ファイルが開いているかを確認してください。
FILTER関数を利用できるExcelか確認する
MicrosoftのFILTER関数のサポート情報では、Windows版ではExcel for Microsoft 365、Excel 2024、Excel 2021などが対象として案内されています。
そのため、他のPCで作成したFILTER関数が動かない場合は、数式だけでなく使用しているExcelのバージョンも確認が必要です。
第3引数の[if_empty]はFILTER関数が正常に利用できる環境での設定なので、FILTER関数自体に対応していない環境を解決するものではありません。
FILTERで0件だけエラーになるときの確認順序
FILTER関数で問題が起きた場合は、次の順序で確認すると原因を絞り込みやすくなります。
- 条件に一致する行があるときは正常に表示されるか確認する
- 0件のときだけ
#CALC!なら、第3引数を追加する - 空欄にしたい場合は
""を指定する - 0件であることを知らせたい場合は
"該当なし"などを指定する - 直らなければ
arrayとincludeの範囲を確認する - 条件に使っているセルや数式にエラーがないか確認する
#SPILL!なら出力先のセルを確認する#REF!かつ別ブック参照なら参照元ブックの状態を確認する
単に「エラーを消す」というより、0件なのか、それとも数式や参照に問題があるのかを切り分けることが重要です。
まとめ
FILTER関数で、条件に一致するデータがないときだけ#CALC!になる場合は、第3引数[if_empty]を指定します。
説明文を表示するなら、
=FILTER(A2:C20,B2:B20=E2,"該当なし")何も表示したくないなら、
=FILTER(A2:C20,B2:B20=E2,"")とします。
まず試すべきなのはIFERRORでエラー全体を隠すことではなく、FILTER本来の第3引数を使って「正常な0件」を処理することです。
それでも解決しない場合は、includeの範囲や条件式のエラー、#SPILL!、別ブック参照による#REF!などを順番に確認してください。こうして原因を分けておけば、「該当データなし」と「数式の不具合」を混同せずに運用できます。
コメント