エラーの数を調べる

  1. Home
  2. Excel
  3. Excel Tips

一般的なExcelのトレーニングでは、「数式をエラーにしない方法」や「数式のエラーを隠す方法」などを学びます。もちろん、それが正攻法的な考えですけど、実務は一筋縄ではいきません。苦労して作った"長～い数式"に、さらにIFERROR関数を組み込むのは大変ですし、複数人で使っているリストに対して、勝手に条件付き書式を設定できないケースもあります。ああ、そういえば「取引先から送られてくるデータには、毎回必ずいくつかの数式エラーがあって困る」という話を聞いたこともあります。つまり実務では、そうした"数式のエラー"と上手に付き合っていかなければなりません。前置きが長くなりましたけど、今回は「数式のエラー」をカウントするという話です。最後に、驚きの結果もありますので、どうぞお楽しみに。

すべてのエラーをカウントする

上図のC列には、エラーになっている数式が9種類入力されています。それぞれのエラーが1つずつありますので、エラーの総数は9個です。検証のために、エラーではない数式も2つ入れておきました。5行目と10行目です。さて、このC列にある「エラーになっている数式の個数」をカウントしてみましょう。

データの数をカウントするといえば、最もよく使われるのがCOUNTA関数です。COUNTA関数は、何らかのデータや数式が入力されているセル、つまり"ブランクセルではない"セルの個数を返します。

エラーになっているセルには数式が入力されているのですからブランクセルではありません。なので、エラーになっているセルもCOUNTA関数でカウントできました。カウント系の関数の中で、これまた実務では必須の関数といえばCOUNTIF関数です。COUNTIF関数は、特定のデータだけをカウントする関数です。たとえば、上図のデータでしたら「COUNTIF(A2:A10,32)」という数式で、A列に入力されている数値のうち、"32"の個数だけをカウントできます。

特定のデータだけカウントできるのでしたら、これを使って「エラーになっているセルだけをカウント」したいと考える方もいるでしょうけど、それはできません。たとえば「エラーはすべて#で始まるのだから、ワイルドカードを使って"#*"みたくすればカウントできるのでは？」って思いませんか？私も最初はそう思いました。結果は次のとおりです。

エラーになっているセルは、セルに「#NULL!」や「#NAME?」などの文字列(値)が入力されているのではありません。COUNTIF関数の条件にワイルドカードを指定したとき、そのワイルドカードは"入力されている値"に対してのみ有効に働きます。要するに今回は、この考えを使えないということです。エラーの数をカウントするには、別の発想が必要です。

最も簡単な方法のひとつは、作業セルを使うことです。ExcelにはISERROR関数という、セルの数式がエラーになっているかどうかを判定する関数があります。

D列に、あらかじめISERROR関数を入れておき、D列の中でTRUEの数を数えればいいです。ただし、TRUEやFALSEなどの論理値は、そのまま計算したりカウントするのが、ちょこっとだけ難しいので、次のように変換してから処理するのが簡単です。

TUREやFALSEに1をかけて数値に変換しました。数値になってしまえば、SUM関数で合計するだけですね。スピルが使えるProPlusでしたら、作業セルを使わずに一発で計算できます。

あるいは、スピルが使えるということはFILTER関数も使えるはずですから、次のように「エラーで絞り込み、その結果をカウントする」という手もあります。

「ウチはまだProPlusじゃないからな…(´･ω･`)」という方に朗報です。Excel 2010以降でしたらAGGREGATE関数が使えます。AGGREGATE関数は、SUBTOTAL関数の拡張版みたいなやつで、なんと「エラーのセルを除外して計算」することができます。AGGREGATE関数に関しては、YouTubeの動画で詳しく解説していますので、ご存じなかった方は、ぜひ下の動画をご覧ください。

ちなみに、こんな感じです。

上図では、AGGREGATE関数を使って「エラーではないセルの個数」をカウントしています。「エラーの個数」とは、全体の個数から「エラーではないセルの個数」を引いた数ですから、次のように計算できます。

エラーの種類ごとに調べる

ここまでは、どんなエラーであっても、とにかく何らかのエラーが起きているセルの個数を調べてきました。ここからは、エラーの種類ごとにカウントしてみます。これには、COUNTIF関数が使えます。勘違いしないでくださいね。上記で「COUNTIF(C2:C12,"#*"」という方法が使えなかったのは、ワイルドカードを使っているからです。ワイルドカードを使わずに、エラー値をそのまま指定すれば、正しくカウントされます。

ちなみに、上図で指定している「?」は、ワイルドカードではなく、単なる文字列としての「?」です。さて、このようにCOUNTIF関数を使えるのですから、この発想でやってみましょう。下図のE列には、文字列の表示形式を設定して「#NULL!」などを文字列として入力しました。

あれ？上図をよく見てください。最後の2つ「#SPILL!」と「#CALC!」がカウントされていません。

実は、今までのExcelには数式のエラーが7種類ありました。

  • #NULL!
  • #DIV/0!
  • #VALUE!
  • #REF!
  • #NAME?
  • #NUM!
  • #N/A

しかし、ProPlusでスピルが実装されてから、数式エラーの種類が増えたんです。

  • #NULL!
  • #DIV/0!
  • #VALUE!
  • #REF!
  • #NAME?
  • #NUM!
  • #N/A
  • #GETTING_DATA
  • #SPILL!
  • #CONNECT!
  • #BLOCKED!
  • #UNKNOWN!
  • #FIELD!
  • #CALC!

「#SPILL!」と「#CALC!」に関しては、Microsoftの公式Webページに説明がありました。「#SPILL!」はスピルに失敗したとき、「#CALC!」はスピルを使ったFILTER関数などがエラーになったとき起こります。それ以外の「#GETTING_DATA」「#CONNECT!」「#BLOCKED!」「#UNKNOWN!」「#FIELD!」に関しては、本稿執筆時点(2020年12月)で公式な解説を見つけられませんでした。ただ、名称を見ると、いくつかは何となく想像できますね。「#GETTING_DATA」はデータを取得できなかったというエラーでしょう。「#CONNECT!」も同様に、接続できなかったよという意味ですね。このへんは、取得と変換(PowerQuery)に関連して起きるような気がします。「#BLOCKED!」と「#FIELD!」は、どちらも何となくデータベースっぽい感じがしますから、これも取得と変換(PowerQuery)がらみかもしれません。最後の「#UNKNOWN!」に関しては、予期しないエラーみたいなものでしょうか。この中から、簡単に再現できる「#SPILL!」と「#CALC!」を今回含めて解説していました。

さて、話を戻しますが。COUNTIF関数の条件に、エラー値を文字列形式で指定すると、エラーの数をカウントできました。ただし、その方法でカウントできるのは、従来まであった7種類のエラー値だけであり、スピルと共に実装されたであろう新しい7個のエラー値は、COUNTIF関数でカウントできない、ということです。うーむ、なぜでしょう。ここからは想像です。そもそもCOUNTIF関数は「セルに入力されている値」または「数式の計算結果」を調べる関数です。"数式のエラー"というのは、どちらでもありません。「#NULL!」という文字列が入力されているわけではありませんし、数式がエラーということは、数式が正しい計算を完了できなかったということですから、「#NULL!」などのエラー値は、決して「数式の計算結果」ではありません。ということは、本来なら、エラー値をCOUNTIF関数で調べることはできないはずですが、それだとユーザーが不自由します。そこでExcelは、裏でゴニョゴニョやっているのではないかと。そして、今回新しい7つのエラーを追加したとき、そのゴニョゴニョを忘れたのではないかなぁ～と。そんな気がします。

では、エラーの種類ごとに調べることはできないのかと。実はできます。ほとんどのExcelユーザーが、その存在すらもご存じないであろうERROR.TYPE関数を使います。このERROR.TYPE関数は、私がむかし書いた「Excel関数 実践講座(秀和システム, 2000年1月発行)」の「DGET関数を使って重複データの有無を調べる」というネタで使いました。もう20年も前なんですね～(遠い目)。ちなみに、それ以来ただの一度も使っていません。それくらい使う機会がない関数です。このERROR.TYPE関数は、引数に指定した"数式エラー"の種類を識別番号で返す関数です。

エラーの種類 識別番号
#NULL!1
#DIV/0!2
#VALUE!3
#REF!4
#NAME?5
#NUM!6
#N/A7
#GETTING_DATA8
#SPILL!9
#CONNECT!10
#BLOCKED!11
#UNKNOWN!12
#FIELD!13
#CALC!14

実は、B列の1～14は、この識別番号でした。このERROR.TYPE関数を使えば、数式のエラーを区別してカウントできます。ただし、少しだけ注意が必要です。ERROR.TYPE関数は、引数に「エラーになってない数式」を指定するとエラーになります。

なので、下図のようにIFERROR関数と組み合わせてやります。下図は「#SPILL!」の個数をカウントしています。