Excel関数のCALCエラーの対処法をお探しですね。

広告

ExcelのFILTER関数で「#CALC!」エラーが出たときの対処法

ExcelでFILTER関数を使って条件に合うデータを抽出したとき、結果が1件もないと「#CALC!」というエラーが表示されることがあります。

数式が間違っているように見えて不安になりますが、実はFILTER関数ではよくある現象なんです。

この記事では、#CALC!エラーの意味と、抽出結果がゼロ件だった場合にエラーを出さず見やすく処理する方法を、初心者の方にも分かりやすく解説します。

#CALC!エラーって何?

Excelの「#CALC!」エラーは、計算そのものができないときや、計算結果をうまく表示できないときに出るエラーです。

「CALC」は英語の「Calculation(計算)」から来ています。

Excel 365やExcel 2021以降で使える新しい関数、特にFILTER関数やBYROW関数、MAP関数などでよく見かけます。

昔のExcelではあまり出なかったエラーなので、初めて見ると「何これ?」ってなりやすいんですよね。

FILTER関数で#CALC!が出る一番よくあるパターンは、「条件に一致するデータが1件もない」ときです。

例えば、売上一覧から担当者が「田中」さんの行だけを抽出する数式を作ったけど、元データに田中さんのデータが1件もない場合、Excelは「返すものがないよ!」という状態になります。

FILTER関数は複数の行や列をまとめて返す関数なんですが、Excelは「空っぽの結果」をそのまま表示できないので、代わりに#CALC!を出して教えてくれるんです。

ここで大事なのは、#CALC!が必ずしも「数式が間違ってる」という意味じゃないことです。

もちろん、関数の書き方や参照範囲が間違っている場合もありますが、FILTER関数では「該当するデータがなかった」という正常な検索結果でも#CALC!が出ます。

検索条件が厳しすぎたり、データがまだ入力されていなかったり、対象の期間にデータがなかったりしても出るんです。

だから、エラーを見て焦るんじゃなくて、「Excelが空っぽの結果を表示できなくて困ってるんだな」と理解すると、原因が分かりやすくなりますよ。

なんで抽出結果がゼロ件だと#CALC!が出るの?

FILTER関数の基本的な書き方は「=FILTER(配列, 条件, 空の場合)」です。

最初の「配列」には抽出したい元データの範囲を、2番目の「条件」には抽出する条件を指定します。

そして3番目の「空の場合」は、条件に合うデータがないときに表示する内容を指定する場所です。

この3番目を書かないまま抽出結果がゼロ件になると、Excelは表示する値を用意できなくて、#CALC!エラーを返すんです。

例えば、A2からD100に売上一覧があって、B列に担当者名が入っているとします。

担当者が「佐藤」さんのデータだけを抽出したいときは、こんな数式を使います。

“`excel
=FILTER(A2:D100,B2:B100=”佐藤”)
“`

この数式自体は正しい書き方です。

でも、B2からB100の中に「佐藤」が1件もない場合、FILTER関数は返す行がありません。

Excelは「該当する行はありません」という状態を空っぽの配列として扱いたいんですが、空っぽの配列はセルに表示できないので、#CALC!を出すんです。

これはFILTER関数の仕様みたいなもので、条件や元データ次第で普通に起こることなんですね。

あと、見た目では同じ文字に見えても、実際には条件に合ってないこともあります。

例えば、セルの最後に余計なスペースが入っていたり、全角と半角が混ざっていたり、日付が文字として入力されていたり、数字に見える値が実は文字列になっていたり。

こういう場合、「データはあるはずなのに!」と思っても、Excel上では一致してないので抽出結果がゼロ件になって、#CALC!が表示されます。

3番目の引数を追加するだけじゃなくて、条件に使ってる列のデータ形式や余計な空白もチェックすると、原因が見つけやすくなりますよ。

#CALC!を出さないようにする基本の方法

FILTER関数で抽出結果がゼロ件になる可能性がある場合は、3番目の引数「空の場合」を指定するのが一番基本的で安全な方法です。

3番目の引数には、該当データがないときに表示したい文字や数字を指定できます。

さっきの例なら、こう書けば#CALC!の代わりに「該当データなし」と表示できます。

“`excel
=FILTER(A2:D100,B2:B100=”佐藤”,”該当データなし”)
“`

こうしておくと、条件に合うデータがあるときは普通に一覧が抽出されて、1件もないときだけ指定したメッセージが表示されます。

エラー表示が消えるので、ファイルを共有した相手にも状況が伝わりやすくなります。

特に、月別レポートや担当者別一覧、在庫検索表みたいに、検索条件によって結果がゼロ件になることが珍しくないシートでは、最初から3番目の引数を入れておくのがおすすめです。

3番目の引数には空白を指定することもできます。

エラーもメッセージも表示したくない場合は、こんな風に「””」を指定します。

“`excel
=FILTER(A2:D100,B2:B100=”佐藤”,””)
“`

ただし、空白に見せる方法には注意点もあります。

見た目は何も表示されませんが、数式のセルには空白の文字列が返ってるので、後で使う関数や集計で思わぬ動きをすることがあるんです。

例えば、抽出結果をCOUNTA関数で数える場合、完全な空白じゃなくて文字列として扱われる可能性があります。

人が見る一覧表では「該当データなし」、印刷用の帳票では「””」みたいに、シートの目的に合わせて使い分けるといいですよ。

IFERRORとの違いと実際に使いやすい方法

CALC!エラーを消す方法として、IFERROR関数でFILTER関数全体を包む方法もあります。

例えば「=IFERROR(FILTER(…), “該当データなし”)」みたいに書けば、FILTER関数でエラーが出たときに指定した文字を表示できます。

便利そうに見えますが、抽出結果がゼロ件になるケースに限って言えば、3番目の引数を使う方が原因が明確でおすすめです。

IFERRORは#CALC!だけじゃなくて、#VALUE!や#REF!など別のエラーもまとめて隠しちゃうので、本当は直すべき参照ミスや範囲指定ミスに気づきにくくなっちゃうんです。

実際の仕事では、まずFILTER関数の3番目の引数で「ゼロ件だった場合の表示」を決めて、それでも別のエラー対策が必要な場合だけIFERRORを検討するのが安全です。

例えば、抽出条件のセルが未入力のときは一覧を表示しない、条件に合わないときは「該当データなし」と表示する、みたいな設計にしておくと、使う人にとって分かりやすい検索シートになります。

条件を入力するセルがF1にある場合は、こんな風にIF関数と組み合わせる方法もあります。

“`excel
=IF(F1=””,””,FILTER(A2:D100,B2:B100=F1,”該当データなし”))
“`

この数式だと、F1が空欄なら何も表示せず、F1に検索する言葉が入力されたときだけFILTER関数が動きます。

検索フォームみたいなシートを作る場合、何も入力してないのに「該当データなし」って出るより自然な見た目になりますよね。

あと、条件を日付や数値にする場合は、入力した値と元データの形式が合ってるかも確認してください。

日付条件で抽出できない場合は日付がちゃんと日付として認識されてるか、数値条件で抽出できない場合は文字列になってないかをチェックすることが大事です。

ちなみに、#CALC!と似たタイミングで見かけるエラーに「#SPILL!」があります。

#SPILL!は、抽出結果を表示しようとした場所に既にデータがあるなど、結果を展開できないときに出るエラーです。

一方、FILTER関数でゼロ件のときに出る#CALC!は、そもそも表示する抽出結果がないことが主な原因です。

エラー名が違えば対処も変わるので、#CALC!なら3番目の引数や条件の見直し、#SPILL!なら出力先の空きセル確認というように切り分けると、トラブル対応が早くなります。

FILTER関数を安定して使うには、正しい数式を書くことに加えて、「該当なし」の状態をあらかじめ想定しておくことが大切です。

最初は難しく感じるかもしれませんが、慣れてくると「念のため3番目の引数を入れておこう」って自然に思えるようになりますよ。

広告