この記事について
この記事は、生成AIを活用した自動生成フローで作成しています。Microsoft公式のFILTER関数と#CALC!の説明を確認し、架空の注文表で該当0件と1件の違いを試す方法を整理しました。

検証ステータス：📘 公式仕様確認済み・Excel実機での再現は未実施

ExcelのFILTER関数で0件になったら？#CALC!と空文字の違いを試す

「未処理の注文だけを表示する」一覧をExcelのFILTER関数で作ったのに、仕事が片付いた日に限って#CALC!が表示される。これは必ずしも数式が壊れたわけではありません。該当する行が0件で、第三引数を指定していない場合、FILTERが空配列を返せないために発生する現象です。第三引数に""を指定すると見た目を空白にできますが、0行の表を作れるようになるわけではありません。

この小さな違いは、結果を人が読むだけなら気にならなくても、別の数式で件数を数えたり、別シートに結果を引き渡したりするときに問題になります。今回は実際の顧客データを使わず、4件の架空の注文で「0件」「1件」「表示用の代替値」を比べます。記事内の表示例は公式仕様に基づく期待結果であり、筆者によるExcel実機での実行結果ではありません。

対象環境と安全な準備

A1からC5に、次の表を入力してください。A1:C1は見出し、A2:C5が4件の注文です。既存の業務ファイルを直接編集する必要はありません。Excelの動的配列は結果を隣接セルへ展開するため、E2からG10付近は空けておきます。

ここで注目するのは、状態がすべて「完了」であり、「未処理」が一件もないことです。結果が0件であることを先に人間が確認できるので、後で表示されたエラーが何を意味するのかを切り分けやすくなります。検証用のシートは保存せず閉じてもよく、元の業務データには影響しません。

まず第三引数を付けずに試す

E2に次の数式を入力します。FILTERの第一引数は返したい表の範囲、第二引数は採用する行を判定する条件です。C列が「未処理」の行だけを、A列からC列までまとめて返す指定になっています。

=FILTER(A2:C5,C2:C5="未処理")

期待される表示は#CALC!です。条件に一致する行がないため、結果の配列に含める行が一つもありません。MicrosoftはFILTERの公式説明で、空の配列をサポートしないため、第三引数を省略して結果が空になるとこのエラーが発生すると案内しています。

ここで大事なのは、#CALC!という文字だけで原因を決めつけないことです。このエラーには、入れ子になった配列やLAMBDAの使い方など、別の原因もあります。今回0件が原因だと判断できるのは、入力表の4行と「未処理」という条件を実際に照合できるからです。業務ファイルで同じ表示を見たら、最初に条件に一致する行が存在するかを調べましょう。

第三引数を追加すると何が変わるか

E2の数式を次のものに置き換えます。最後に追加した""は空文字、つまり文字数0の文字列です。FILTERの第三引数は、抽出結果が空だったときに返す代替値を指定する場所です。

=FILTER(A2:C5,C2:C5="未処理","")

期待される結果は、E2が空白のように見える表示です。エラーが見えなくなったので解決したように思えますが、空の3列の表が生成されたわけではありません。一致する行がなかった場合の代わりとして、空文字が返っています。Microsoft公式のFILTER関数の例でも、第三引数に空文字を指定して該当なしのときに返す方法が示されています。

この違いを見落とすと、後続の式で「スピルした結果の行数」を件数として数えたときに、代替値の入った一つのセルを数えてしまう設計になり得ます。画面に何も見えないことと、データとして行が0件であることは別です。利用者に優しい見た目と、正しい件数管理を同じ数式に任せない方がよい場面があります。

一か所だけ変えて、0件と1件を比較する

次はC3だけを「完了」から「未処理」に変更します。数式は変更しません。これで注文A002の行だけが条件を満たします。期待結果はE2:G2に「A002」「ペン」「未処理」が横に並ぶ一行三列です。先ほどの空文字ではなく、元データから抽出された実際の行が返るはずです。

比較後にC3を「完了」に戻すと、再び該当0件となり、第三引数の空文字が使われます。ここで観察したいのは、同じ数式でも入力条件によって戻り値の形が変わることです。1件以上あれば表の行が返り、0件なら指定した代替値が返ります。第三引数は抽出結果にいつも追加される文字列ではありません。

さらに第三引数を"対象なし"にすると、0件のときだけE2に説明文を表示できます。担当者が毎朝確認する一覧では便利ですが、その結果を別の数式が注文IDとして処理する場合は、説明文がデータに混ざる点に注意が必要です。表示専用の場所か、機械的な集計に使う場所かで設計を分けましょう。

図で理解する：FILTERの結果が変わる分岐

次の図はExcel内部の実装ではなく、公式仕様から読み取れる結果の分岐を整理したものです。左から条件を評価し、該当行の有無と第三引数の指定によって戻り値が決まる流れを表しています。

flowchart TD
    A["FILTERで各行の条件を判定"] --> B{"一致する行がある?"}
    B -->|ある| C["一致した行の配列を返す"]
    B -->|ない| D{"第三引数を指定した?"}
    D -->|はい| E["指定した代替値を返す"]
    D -->|いいえ| F["空配列を返せずCALCエラー"]

図の下側に進むときが今回の落とし穴です。エラー表示を消すために第三引数を加える操作は、結果がない場合の返り値そのものを変えています。代替値を見た目の都合で決める前に、後続処理がその値をどう扱うかまで考えると、集計の不具合を避けやすくなります。

件数の判断はCOUNTIFに分ける

表示用のFILTERとは別に、J2へ次の数式を入力します。これで状態列に「未処理」が何件あるかを直接数えられます。元の表では期待結果は0、C3だけを「未処理」に変更したときは1です。

=COUNTIF(C2:C5,"未処理")

J2が0なら未処理の注文は0件、1なら1件です。E2のFILTERには空文字や「対象なし」を返させておき、業務判断にはJ2の件数を使います。この分離によって、利用者に見せる文言を変更しても、件数判定の数式を変えずに済みます。例えば「対象なし」を「本日の未処理はありません」に変えても、J2の意味は変わりません。

ただし、COUNTIFが0だからといって、入力データが完全に正しいとは限りません。状態欄に余分な空白や別の表記があれば、想定した条件に一致しないことがあります。入力値を検査せず、0件を「処理完了」と自動判定する仕組みは危険です。重要な業務では状態の入力規則を設け、例外値を別途点検する運用が適しています。

#CALC!と#SPILL!をどう切り分けるか

FILTERの結果が表示されないときは、まずエラー名を確認します。#CALC!は今回のような空配列のほか、別の計算上の問題でも発生します。一方、#SPILL!は結果を展開したい範囲に別の値が入っているなど、展開先の問題を示します。1件以上の結果が返る状態でE2の右側に既存データがあれば、結果を置けないことがあります。

業務シートで展開先が埋まっているからといって、周囲のセルを無条件に削除してはいけません。まず新しい空のシートで同じ式を試し、問題が条件判定なのか配置なのかを分けます。また、抽出範囲A2:C5は4行なのに、条件範囲をC2:C4の3行にした場合は、配列の大きさが一致しません。第三引数を指定しても、このような式の構造的な誤りは解決できません。

もう一つの境界は別ブックへの参照です。Microsoftは動的配列のブック間連携について、両方のブックが開いている場合などの制限を案内しています。別ブックを閉じた状態で更新すると#REF!になることがあり、0件時の代替値とは別問題です。今回の例は同じシート内で完結させ、外部ブックへの依存を増やさずに理解する構成にしています。

仕事で使うときの設計判断

毎朝見る未処理一覧なら、FILTERの第三引数に「対象なし」を指定すると利用者は状況を理解しやすくなります。一方、件数をもとにメール通知や処理の分岐を行う場合は、COUNTIFなどで独立して判定し、表示文言を機械的な入力にしない方が安全です。0件を正常とするのか、入力漏れの疑いとして確認するのかも、業務のルールで決める必要があります。

最初から複数条件を組み合わせるより、まず今回のように条件一つで0件・1件を確認し、次に日付や担当者などを一条件ずつ追加する方法が分かりやすくなります。新しい条件を追加した直後に件数が0へ変わったら、元データがないのか条件が厳しすぎるのかを切り分けられます。数式を短くすることより、結果の意味を説明できることが大切です。

まとめ

FILTERの第三引数を省略して抽出0件になると、Excelは空配列を返せず#CALC!になります。空文字を指定すると表示を穏やかにできますが、0行の表が生成されるわけではありません。1件の入力を変える実験で違いを確かめ、表示用のFILTERと件数管理用のCOUNTIFを分けると、後続の集計でも意味を取り違えにくくなります。今回のExcel表示は期待結果であり、実機での再現確認は未実施です。

公式資料

• Microsoft サポート：FILTER 関数

• Microsoft サポート：#CALC! エラーの修正