条件の一致するセルの個数をカウントできるCOUNTIF系関数は、それだけでもとても便利に使えますが、IF関数と組み合わせて真価を発揮する重要なツールでもあります。
条件分岐を行う「IF関数」は、通常、部分一致を判定できません。
しかし、実務的にはよく○○を含むセルには□□の処理、それ以外は△△の処理、など部分一致による分岐はよく使用します。
COUNTIF系関数は比較演算子をはじめ、ワイルドカードを組み合わせた条件指定を行うことで、部分一致検索ができるため、IF関数などの用途を拡張するという役割もあります。
ワイルドカードについては別のページで紹介していますのでぜひ参考にしてみてください。
構成確認
=COUNTIF(検索範囲,検索条件)
＝CUNTIFS(検索範囲1,検索条件1,検索範囲2,検索条件2,…)
見てのとおりですが、COUNTIFは検索範囲と検索条件を1セットしか指定できないのに対し、COUNTIFSでは複数セットの指定ができます。
COUNTIFSでの複数条件指定は最大127セットまでで、AND条件となります。
OR条件を指定する方法はいくつかありますが、COUNTIF同士を＋（プラス）でつなぎ、重複がある場合をそれを－（マイナス）するという手順が基本です。
=COUNTIF(検索範囲1,検索条件1)＋COUNTIF(検索範囲1,検索条件2)
検索範囲が同じ場合は重複が発生しないため＋（プラス）でつなぐだけ。
=COUNTIF(検索範囲1,検索条件1)＋COUNTIF(検索範囲2,検索条件2)－COUNTIFS(検索範囲1,検索条件1,検索範囲2,検索条件2)
検索範囲が異なる場合は重複が発生するため、同じ条件のAND算出したもの、つまりCOUNTIFSで重複を算出して－（マイナス）する必要がある。
COUNTIF系関数の使用例
基本的な使用例
上の販売記録をベースに、条件に一致するデータ数をカウントするシンプルな使用例をいくつか記載します。
| 条件と数式例 | 結果 |
|---|---|
| 【条件】「みかん」のセル数 =COUNTIF(C5:C13,”みかん”) | 2 |
| 【条件】「りんご」を含むセル数 =COUNTIF(C5:C13,”*りんご*”) | 4 |
| 【条件】4月3日以前の行数 =COUNTIF(B5:B13,”<=”&B8) または =COUNTIF(B5:B13,”<=”&DATEVALUE(“2021/4/3”)) | 6 |
| 【条件】売上が150より大きい行数 =COUNTIF(D5:D13,”>”&150) | 5 |
| 【条件】「りんご」を含み、かつ売上が200以上の行数 =COUNTIFS(C5:C13,”*りんご*”,D5:D13,”>=”&200) | 3 |
| 【条件】「ばなな」のうち、4月3日以前の行数 =COUNTIFS(C5:C13,”ばなな”,B5:B13,”<=”&B8) | 2 |
○○を含む、という条件指定の場合はワイルドカードを使用します。
“りんご”という条件で”りんご（袋）”もカウントした場合は”りんご*”のように後ろに「＊」を付けておけばOKです。
なお、この表の場合は”りんご”の前に何らかの文字列が付いているデータはないため”りんご*”で大丈夫ですが、「含む」指定をするときは念のため”*りんご*”のように両端にワイルドカードを付けておくのが無難です。
また、日付は少しややこしく、比較する場合は数値化する必要があります。
数値化する手段はいろいろありますが、一般的なのはシリアル値として扱う方法です。
詳細は省きますが、シリアル値は１日を１としてカウントしているので、日付をシリアル値化して、比較演算子で条件を指定することができるのです。
そして、日付セルの書式設定が「日付」になっていれば、すでにそのセルは日付をシリアル値として認識しているため、条件日付となるセル番地を比較演算子と一緒に指定してやれば大丈夫です。
また、数式の中に直接日付を指定したい場合は、例のようにDATEVALUE関数に文字列日付を指定してやることでシリアル値として扱うことができます。（DATEVALUE関数は文字列日付をシリアル値とに変換してくれるものです。）
数式をオートフィルでコピーする場合
右欄の赤枠に、品目ごとのデータ数（行数）をカウントします。
このように品目名がセルに入力されている場合は、そのセル値を利用してオートフィルなどで数式コピーができるように絶対参照と相対参照を使い分けて指定します。
G5セルに次の数式を入れてG7までオートフィル
=COUNTIF($C$5:$C$13,””& $F5 &””)
品目の中で、”りんご”は部分指定となるため、条件指定にワイルドカードを使用します。
F5の行番号だけ相対参照にし、ほかはすべて絶対参照にしています。
なお、オートフィルなどをしない場合でも、絶対参照と相対参照を適切に使い分けて指定するクセを付けておくことをおすすめします。（数式は少し長くなりますが。）
OR条件の使用例
品目名が「みかん」か「ばなな」のデータ数
=COUNTIF(C5:C13,”みかん”)+COUNTIF(C5:C13,”ばなな”)
この例はどちらの条件も検索範囲が同じになるため重複が発生しません。
そのため、2つの条件を単純に足してやればOKです。
しかし、次の例のように検索範囲が異なる場合は重複が発生するため、重複分を差し引かないといけません。
日付が2021/4/4以前か、品目名が"りんご"を含むデータ数
=COUNTIF(B5:B13,”<=”&DATEVALUE(“2021/4/4″))+COUNTIF(C5:C13,”りんご“)-COUNTIFS(B5:B13,”<=”&DATEVALUE(“2021/4/4″),C5:C13,”りんご“)
日付をDATEVALUEで指定しているせいでもありますが、うんざりする長さですね。
一応、要素を分解して解説すると
| 要素 | 結果 |
|---|---|
| 日付が2021/4/4以前 COUNTIF(B5:B13,”<=”&DATEVALUE(“2021/4/4”)) | 7 |
| 品目名が”りんご”を含む COUNTIF(C5:C13,”*りんご*”) | 4 |
| 上記2つの重複 COUNTIFS(B5:B13,”<=”&DATEVALUE(“2021/4/4″),C5:C13,”*りんご*”) | 3 |
つまり、「7＋4－3＝８」という計算をしているわけです。
IF関数の条件としての使用例
冒頭や他のページでも紹介していますが、条件分岐を行うIF関数では直接的に部分一致の条件を指定することができません。
直接的に、というのは、例えばA1セルに「ふじりんご（袋）」が入力されていたとして、
=IF(A1=”りんご”,TRUE,FALSE)では「FALSE」となります。
IFの条件に直接的に「＊」を指定しても、ワイルドカードではなく文字列の「＊」と認識するからです。
その場合、COUNTIFをIFの条件として使って、=IF(COUNTIF(A1,”りんご“),TRUE,FALSE)のようにするわけです。
これなら「TRUE」が返ります。
なお、厳密には
=IF(COUNTIF(A1,”りんご”),TRUE,FALSE)は、
=IF(COUNTIF(A1,”りんご”)>=1,TRUE,FALSE)と同じ意味です。
比較演算子を省略した場合は、自動的に「1以上」（>=1）という条件になります。
もし、「りんご」を含むセルが3個以上ある場合だけTRUEを返した場合は、
=IF(COUNTIF(A1,”りんご”)>=3,TRUE,FALSE)と指定します。
まとめ
COUNTIF・COUNTIFSはデータ処理を行う上で必須とも言える重要な関数です。
この関数を使いこなせればデータ処理に関しては大幅な時間短縮に繋がることと思います。
なお、押さえておくと便利なポイントをまとめておきます。
- 合計範囲と、条件の検索範囲の指定は基本的に絶対参照にしておく。（オートフィルなどでの数式コピーを前提とするため。）
- 条件の部分一致にはワイルドカードを使用する。（「”りんご*”」、「$F5&”*”」、「”*”&$F5&”*”」など）
- 日付を条件とする場合はシリアル値を比較演算子で指定する。また、日付のセルの書式設定が「文字列」になっていないか注意する。
- 【COUNTIFSに関しては】複数条件の検索範囲は行範囲（配列範囲）をすべて一致させておく。（今回の例では「$B$5:$B$13」と「$C$5:$C$13」など5行目から13行目という部分を一致させておかないとエラーになる。）
どれも基本的ながら、実務的なポイントです。エラーが出る場合などはチェックしてみてください。
また、COUNTIFSはSUMIFS同様にExcel2007から追加された関数で、すでにほとんどの端末で利用できるくらい普及しています。
ファイルを他の方に渡すような場合でも、互換性を気にせずに使っても大丈夫かと思います。
COUNTIFSは条件の複数指定ができること以外、COUNTIFと構成が同じなので、SUMIFSのように引数の位置の違いで混乱することもないでしょう。
以上になります。ぜひ参考にしてください。
コメント