COUNTIF関数で別ファイルを開かず参照しカウントする方法

ここでは、エクセルの「COUNTIF関数で別ファイルを開かずに参照しカウントする方法」を解説します。

そのまま使うと#VALUE!エラーになってしまうのを防ぐ方法の説明です。

【目次】

本記事はアフィリエイト広告を含みます

１．別ファイルの参照は#VALUE!エラーになる

始めに、COUNTIF関数で「別ファイルの参照がエラーになる」例から確認してみましょう。

上は、テストを行った結果を〇、△、×で記載した、Book1のシート1にある表です。

続いて上は、Book1の〇、△、×の数をカウント結果を表示させるためのBook2ののシート1にある表です。

そして、Book1の表を範囲としてBook2にCOUNTIF関数を使ってカウント結果を表示させようとすると、数式を入れた直後は次のようになります。

Book1の表の〇の数のカウント結果が、正しくBook2のセルB3に表示されていることが分かると思います。

しかし、この後この値が#VALUE!エラーになるのです。具体的には「Book1、Book2を保存して閉じる」→「Book2を開く」ことでエラーになり、次の通りです。

原因は「COUNTIF関数が開いていないブックのデータを参照できない」ためで、このエラーはBook1を開くと解消しますが、毎回参照元のファイルを開くのは正直めんどうですよね。

COUNTIF関数はこのような「別ファイルを参照できない」ことが、とても残念なポイントとなっています。

しかし、参照元のブックを開かなくてもカウントできる方法はあります。２つあるので順に見てみましょう。

一つ目は「SUMPRODUCT関数を使う」ことです。

SUMPRODUCT関数は条件に合うセルの数をカウントできる、COUNTIF関数に似た関数ですが、COUNTIF関数と違って閉じたブックの値を参照でき、エラーを起こさずカウントできます。

SUMPRODUCT関数の数式の形は、条件が一つの場合が、

=SUMPRODUCT((範囲=条件値)*1)

で、二つの場合が、

=SUMPRODUCT((範囲1=条件値1)*(範囲2=条件値2))

で、三つの場合が、

=SUMPRODUCT((範囲1=条件値1)*(範囲2=条件値2)*(範囲3=条件値3))

となります。

これで、参照元のファイルを開かなくてもカウントが可能になります。

しかし、この方法のネックは「SUMPRODUCT関数の知名度が低い」ことで、一般的にはCOUNTIF関数のことは知っていてもSUMPRODUCT関数のことは知らない、という人が多く、数式を見たときに意味を読み取れないケースが出てくる可能性があります。

二つ目は「ブック内のシートにデータを転記し参照元にする」方法です。

カウントにはCOUNTIF関数を使いますが、結果を表示させたいファイルにシートを1つ作り、そのシートにカウントしたい別ファイルのデータを、「＝」を使ってまるまるコピーして、そのデータを参照してカウントするのです。

以下手順です。

（１）Book2にシートを新規作成する

（２）罫線など様式を整えながら、「=」を使ってBoo1の表をコピーする

（３）作成したシートを参照元にしてCOUNTIF関数を使った数式を入力すれば完了

補足すると（２）の「Book1の表をコピーする」とは、「Book2に『=Book1!セル番号』という数式を入れて、Book1とリンクしたシートを作る」ということです。

ちなみにこの方法は、例えば両方のファイルを閉じた後、Book1のセルの値を更新し保存・閉じた後Book2を開いても、更新後の正しい結果を取得できます。

この方法のデメリットは「作業用のシートが必要になること」ですが、そこまで大きな問題点にはならないでしょう。

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

【口コミ・評判のいいExcel本の紹介】

リンク

COUNTIF関数に関連したページは、次の通りです。