Excel

エラーとなっているセルの数の数え方


Excelを使った業務をしていると、あるセル範囲の中にエラーを含むセルがあるか確認したい(または、ほぼ同じことだが、エラーを含むセルが0であることを確認したい)、ということが、よくあると思う。

この場合、目で確認する、という最も原始的なやり方を除けば、すぐに思いつくのは、オートフィルタをかけて確認する、というものがある。(オートフィルタをかければデータの重複がない形でドロップダウンリストに表示されるので、確認がしやすくなる。)

 

ただ、このやり方だと

  • 1列だけ確認すれば良いのなら簡単だが、複数の列について確認するとなると面倒
  • (実務上ではレアなケースだろうが)列方向ではなく行方向に並んだデータの場合はオートフィルタがかけられない

という問題点がある。

実務上は「エラーとなっているセルがないことを確認したい」ということが大半だろうから、対象となるセル範囲の中でエラーとなっているセルの数を表示できればよい。

答えとしては
=SUMPRODUCT(ISERROR(対象となるセル範囲)*1)
というような関数の組み合わせで、エラーとなっているセルの数を確認できる。

まずは、ISERROR関数だが、これはテスト対象として指定したセルが、エラー値 (#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または #NULL! のいずれか) であった時に TRUE を返す。一つのセルだけを指定するのが一般的な使い方だが、単一のセルでなく、セル範囲も対象として指定できる。

次にSUMPRODUCT関数。Excelのヘルプを見ると、

構文(書式)は、
SUMPRODUCT(配列 1, [配列 2], [配列 3], ...)

説明には、
「引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。」とある。

この場合、配列にはセル範囲を指定する。

例えば以下のようなデータがあり、セルのD10に
=SUMPRODUCT(B2:B4,C2:C4)
と入れると、10と計算される。この場合、1*3+2*2+3*1という計算が行われている訳だ。

(なお、英語のproductには、「生産物」とか「製品」という意味の他に「積」とか「掛け算の答え」という意味がある(英辞郎Proより))