Excelで#N/AやDIV/0、#REF!などエラーの数をカウントする

ここでは、「#N/A」や「DIV/0」、「#REF!」などのエラーの数を簡単に数える方法を紹介します。

これで「エラーのカウント」の他、「エラー以外の数」を出すこともできるようになります。

始めに、やりたいことを例を含めて確認します。

１－１．やりたいことは？

やりたいことは、「範囲内でエラーがいくつあるかカウントする」ことです。

そして、エラーには「#N/A」や「DIV/0」、「#REF!」などいくつか種類がありますが、対象となるエラーは、「すべて」です。

１－２．例で確認

では、エラーの数をカウントした例を見てみましょう。

上の表は、仕入値を仕入数で割って仕入単価を出す「仕入単価確認表」です。

表では、セルB7とF4の2ヶ所にエラーが出ています。

例えば、この仕入れ単価確認表の中でエラーがいくつあるかを、数式でカウントすることができます。

上の例では、「２」が返ってきます。

この数式のメリットは、「エラーの有無が一目で分かるようになる」ことです。

エラーにすぐに気づければ、エラーへの対処が速やかにできます。

次に、数式の形と作り方を見てみます。

２－１．数式の形は

まず、数式の形は次の通りです。

「範囲」に「エラーをカウントしたい範囲」を指定すればOKです。

２－２．数式の作り方

数式の簡単な作り方は、「上の数式をコピーして貼り付け、『範囲』の文字を消して指定する」です。

なお、先ほどの例だと数式は次のようになります。

=SUMPRODUCT(ISERROR(B3:F7)*1)

最後に、なぜエラーの数をカウントできるのか？です。

まず、ISERROR関数はエラーだとTRUE、そうでない場合にはFALSEを返します。

そしてエクセルの仕様上、TRUEやFALSEに「１」を掛けると、TRUEは数字の「１」に、FALSEは「０」に置き換えられます。

視覚的に表すと上のような形です。

このようにエラーの所は「１」、エラーでない所は「０」となります。

そして、SUMPRODUCT関数が範囲内を合計してくれます。

結果、エラーの数が分かるということです。

エクセル関数には、条件に合うセルの数をカウントできる有名な関数に「COUNTIF関数やCOUNTIFS関数」があります。

これらの関数ではダメなのでしょうか？

結論から言うと、「COUNTIF(S)関数ではエラーのカウントはできません」。

例えば数式を、

=COUNTIFS(範囲,#N/A,B3:F7,#VALUE!,範囲,#REF!,範囲,#DIV/0!,範囲,#NUM!,範囲,#NAME?,範囲,#NULL!)

としてもうまくカウントしてくれません。

ですからエラーをカウントする際には、SUMPRODUCT関数とIFERROR関数の組み合わせを使いましょう。

最後に「エラー以外のセルの数をカウントする方法」についてです。

エラー以外をカウントする場合、数式は「セル全部の個数－エラーセルの個数」とすればOKです。

セルの数の出し方は「縦のセル数×横のセル数」ですが、関数で出すこともできます。

→「指定した範囲のセルの数をエクセル関数の組み合わせで数える方法」を知りたい場合にはこちら

これで「エラー以外のセルの数」を求めることができます。

以上、参考になれば幸いです。

関数の問題解決記事一覧へはこちら