ExcelのCOUNTIFS関数は、1つまたは複数の範囲内で指定された条件または基準を満たすセルの数を取得するために使用されます。
COUNTIFS関数が期待どおりに動作せず、#VALUE ！ 0が 返されることがあります。これは、数式にエラーがあるか、予期しない値が返されることを意味します。
Microsoft ExcelでCOUNTIFS関数が期待通りに動作しない理由は以下のとおりです：
- 数式が閉じているワークブックの範囲を参照している場合。
- 他のセルの値を、使用する論理演算子に連結せずに使用する。
- テキスト条件を逆カンマ（” “）で囲まない。
- 後続の範囲は、最初の条件の範囲と同じ数の列と行を持たない。
- 数値条件と論理演算子をコンマ (” “) で囲まない。
- OR ロジックを使用して数値を取得しようとしています。
- データセットに後続の変更が加えられています。
- 式のエラー。
このチュートリアルでは、関数が求める正しい数値を返すように、これらの原因を解決する方法を説明します。
1. 数式が、閉じている別のワークブックの範囲を参照している。
COUNTIFS関数が閉じている別のワークブックの範囲を参照している場合、期待されるセル数ではなく#VALUE！エラーが返されます。
次の例では、数式が赤色の製品の数を返すのではなく、#VALUE!エラーが発生しました。これは、数式が閉じている別のワークブック内の範囲を参照しているためです。
解決方法
このシナリオで勘定科目が機能しない場合の解決策は、閉じているワークブックを開き、キーボードのF9 キーを押して、数式が正しいパラメータを再計算するようにすることです。次の例に示すように、正しい勘定科目が得られます。
2. セル参照を論理演算子に連結せずに、別のセルの値を使用する。
COUNTIFS関数の条件として論理演算子とセル参照を数式で使用し、セル参照の前に&を 挿入してセル参照を論理演算子に連結しなかった場合、関数は期待される数値ではなく0を 返します。
次の例では、=COUNTIFS(G2:G6,”>M2″)という数式が使われています。
解答
次の例では、セルI3に正しい数式=COUNTIFS(G2:G6,”> ” &M2) を入力する必要があります。ENTERを 押すと、正しい数4が得られる。
正しい数式では、論理演算子 greater than (>) のみが逆カンマ (” “) で囲まれ、連結演算子&がセルM2 への参照の前に置かれている。
3. テキスト基準を逆カンマ( ” “)で囲まない。
もしテキスト条件が逆カンマで囲まれていない場合、COUNTIFS関数は以下の例に示すように、期待する正しい数値ではなく、0という値を返します。
セルJ2の数式は=COUNTIFS(C2:C6,Red)です。Redの基準が逆カンマ（” “）で囲まれていないため、 数式は0を 返します。
機能しない勘定科目の問題の解決法
以下の例に示すように、数式内のテキスト基準をコンマで囲む必要があります。
4. 次の基準のセットは、最初の基準のセットと同じ形をしていません。
COUNTIFS 関数で複数の条件を使用し、追加のオプション条件範囲が最初の条件範囲と一致しない場合、#VALUEエラーが発生します。
次の例では、セルG3 に数式=COUNTIFS(B2:B13, “Red”,C2:C10, “Microsoft”) が入力され、Microsoft が供給する赤色製品の数を取得しようとしています。
Enterを押すと、下図のように#VALUE!
これは、条件C2:C 10の2番目の範囲が、条件B2:B13の1番目の範囲と同じ数のセルを持たないためです。
解決方法
この問題を解決するには、下図のように、2番目の基準範囲が1番目の基準範囲と同じサイズであることを確認する必要がある：
ENTERを押すと、下図のように正しい数「2」が表示される：
5. 数値条件と論理演算子を逆カンマ（” “）で囲まない。
等しい(=)、より大きい(>)、より小さい(<)、等しくない(<>)といった論理演算子を数式で使用する際、数値基準と論理演算子を同じ逆カンマで囲まなかった場合、Excelは数式にエラーが含まれていることを示すメッセージを表示します。
次の例では、論理演算子と数値基準をコンマで囲んでいません。論理演算子だけがコンマで囲まれています。
ENTERを押すと、次のようなメッセージが表示されます：
解答
以下のように、論理演算子と数値基準の両方を同じ転置コンマで囲む必要があります：
ENTERを押すと、以下のように正しい数5が表示される：
6. OR論理を使って数を求める
COUNTIFS関数はANDロジックで正しい数を生成できますが、ORロジックで計算しようとすると0しか生成されません。
次の例では、OR ロジックを使用して赤と青の商品カテゴリの数を取得しようとしています。
ENTERを押すと、下図のように予想外の値0が得られます。これはCOUNTIFS関数がOR論理を使って計算できないためです。
解決方法
COUNTIFS関数とSUM関数を併用することで、このORロジックの問題を回避することができます。
次の例では、セルG3に=SUM(COUNTIFS(B2:B13,{“Red”, “Blue”}))という数式を入力しています。
ENTERを押すと、下図のように6という正しい値が得られます。これは、COUNTIFS関数が条件範囲B2:B13から3つの赤と3つの青を返し、SUM関数がその値を足し合わせて6を出したからです。
7. データセットへのその後の変更
データセットに変更を加えると，関数COUNTIFSが期待した値を生成しなくなることがある．例えば，次のデータセットでは，関数COUNTIFは正しく働き，正しい数の青色製品を返す．
しかし、データに変更があった場合、例えばこの場合、colorという単語をBlueに追加すると、COUNTIFS関数は正しくない値0を表示します。
解決方法
この状況は、COUNTIF関数でワイルドカードを使用することで解決できます。
以下の例では、条件にアスタリスク文字（*）を使用しています。COUNTIF関数は、条件の範囲内で部分一致を検索できるようになりました。
8. 式のエラー
条件範囲と論理演算子の間に区切りカンマを忘れるなど、計算式にエラーがあるためにCOUNTIF関数が動作しないことがあります。
次の例では、条件範囲D2:D13と論理演算子greater than (>)の間にカンマがありません。
ENTERを押すと、数式にエラーがあることを知らせるメッセージボックスが表示されます。
解決方法
数式を記述するときは、構文が正しいことを特に注意する必要があります。次の例では、基準範囲と演算子 greater than (>) の間にカンマを挿入することで問題が解決し、正しい数 5 が得られます。
結論
COUNTIFSは、特定の条件を満たすデータ範囲のセルの数を生成するために使用できる、非常に強力な関数です。
このチュートリアルでは、この関数が常に期待通りに動作しない様々な理由と、それを解決する方法を見てきました。