COUNTIF関数で#VALUE!エラー！？｜原因と対処方法
エクセルのCOUNTIF関数で数式は間違っていないはずなのに「#VALUE!エラー」になってしまうことありませんか？
COUNTIF関数は、実はちょっと思いがけない理由で#VALUE!エラーになってしまい、原因も推測しづらいものになっています。
ここではそんなCOUNTIF関数の、#VALUE!エラーが起こる原因と対処方法を解説します。
１．＃VALUE!エラーはなぜ起こる？
COUNTIF関数で#VALUE!エラーはなぜ起こるのでしょうか？
そもそも＃VALUE!エラーは「入力した数式か参照先のセルに問題がある」時に起こるものです。
では、次の数式には何か問題があるのでしょうか？
上の例の数式は、範囲が「B3:B7」、条件が「〇」となっていますが、一つ特徴的なのは「『範囲』で他のエクセルファイルのセルを参照している」ことです。
数式的には特に問題ないように見えますが、結果は「#VALUE!エラー」になっています。
この場合、エラーの原因は「参照先」にあります。
COUNTIF関数は、参照先のファイルが閉じられているとその中身を見ることができず、結果値が取得できなくて＃VALUE!エラーになってしまうです。
逆に言うと、参照先のファイルを開けばその時点でエラーが解消されます。
２．#VALUE!エラー解消のための対処方法
しかしその都度参照先のファイルも開くのは、とても面倒ですよね。できれば開かなくてもエラーにならないようにしたいものです。
そこで、他のファイルにある表から条件に合うセルの数をカウントしてもエラーにならない方法を２つ紹介します。
２－１．COUNTIF関数とは別の関数を使う
エクセルの関数で、条件に合うセルの数を数えられるのはCOUNTIF関数だけではありません。そして中には「参照先のファイルが開かれていなくても中の値を取得できる」ものもあります。
それが「SUMPRODUCT関数」です。
COUNTIF関数の数式の形は、
=COUNTIF(範囲,検索条件)
ですが、SUMPRODUCT関数は同じことをしようとした場合、
=SUMPRODUCT((範囲=検索条件)*1)
となります。
具体的には、次のようになります。
=COUNTIF([ブックのパス]$B$3:$B$7,"〇")
=SUMPRODUCT(([ブックのパス]$B$3:$B$7="〇")*1)
SUMPRODUCT関数はかっこの多さと「*1」の意味が分かりづらいですが、「こういうもんだと思って使えば意外と簡単です。これが一つ目の対処方法です。
２－２．結果表示用ファイルに参照元の値をコピーする
もう一つ方法があります。それは「結果表示用ファイルに参照元の値をコピーし、それに対してCOUNTIF関数を使う」ことです。
具体的には次の手順で行います。
① 結果を表示させたいファイルに「シートを新規作成」する
② 作成したシートに「＝」を使って参照元にある値を表示させる
③ 出来上がったシートに対してCOUNTIF関数を使う
例えば上の表は、Book1のシート1の表をBook2のシート2にコピーしたものです。
セルB3の値は「＝」で「Book1のシート1のセルB3」を参照しています。
このような参照元とリンクした表を作り、これに対してCOUNTIF関数を使うのです。
この「リンクした作業用シート」を挟むことで、閉じたファイルの値が取得できるようになり、結果＃VALUE!エラーが発生しなくなります。
ちなみにこの方法は、例えば両方のファイルを閉じ、Book1のセルの値を更新し保存・閉じた後Book2を開いても、更新後の正しい結果を取得できます。
この方法のデメリットは「ファイルの容量が大きくなること」と「作業用のシートが必要になること」ですが、参照元がよほど大きなデータ量ではない限り、大きな問題点にはならないでしょう。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】
COUNTIF関数に関連したページは、次の通りです。
- COUNTIF関数 使いこなすためのポイントはこちら